.Net数据库相关操作
来源:互联网 发布:淘宝公益宝贝有什么好处 编辑:程序博客网 时间:2024/06/06 02:32
using System;
using System.Data;
using System.Data.SqlClient;
namespace SysClassLibrary
{
/// <summary>
/// CDataAccess 的摘要说明。
///<description>数据处理基类,调用方式:CDataAccess.DataSet((string)sqlstr);或者CDataAccess.DataSet((string)sqlstr,ref DataSet ds); </description>
/// </summary>
public class CDataAccess
{
#region 属性
protected static SqlConnection conn = new SqlConnection();
protected static SqlCommand comm = new SqlCommand();
#endregion
public CDataAccess()
{
//init();
}
#region 内部函数 静态方法中不会执行CDataAccess()构造函数
/// <summary>
/// 打开数据库连接
/// </summary>
private static void openConnection()
{
if (conn.State == ConnectionState.Closed)
{
//SysConfig.ConnectionString 为系统配置类中连接字符串,如:"server=localhost;database=databasename;uid=sa;pwd=;"
conn.ConnectionString = "";// SysConfig.ConnectionString;
comm.Connection = conn;
try
{
conn.Open();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
/// <summary>
/// 关闭当前数据库连接
/// </summary>
private static void closeConnection()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
comm.Dispose();
}
#endregion
/// <summary>
/// 执行Sql查询语句
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
public static void ExecuteSql(string sqlstr)
{
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
comm.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="coll">SqlParameters 集合</param>
public static void ExecutePorcedure(string procName, SqlParameter[] coll)
{
try
{
openConnection();
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = procName;
comm.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
comm.Parameters.Clear();
closeConnection();
}
}
/// <summary>
/// 执行存储过程并返回数据集
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="coll">SqlParameter集合</param>
/// <param name="ds">DataSet </param>
public static void ExecutePorcedure(string procName, SqlParameter[] coll, ref DataSet ds)
{
try
{
SqlDataAdapter da = new SqlDataAdapter();
openConnection();
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = procName;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
comm.Parameters.Clear();
closeConnection();
}
}
/// <summary>
/// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>object 返回值 </returns>
public static object ExecuteScalar(string sqlstr)
{
object obj = new object();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
obj = comm.ExecuteScalar();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return obj;
}
/// <summary>
/// 执行Sql查询语句,同时进行事务处理
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
public static void ExecuteSqlWithTransaction(string sqlstr)
{
SqlTransaction trans;
trans = conn.BeginTransaction();
comm.Transaction = trans;
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
comm.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
closeConnection();
}
}
/// <summary>
/// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>SqlDataReader对象</returns>
public static SqlDataReader dataReader(string sqlstr)
{
SqlDataReader dr = null;
try
{
openConnection();
comm.CommandText = sqlstr;
comm.CommandType = CommandType.Text;
dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
try
{
dr.Close();
closeConnection();
}
catch
{
}
}
return dr;
}
/// <summary>
/// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <param name="dr">传入的ref DataReader 对象</param>
public static void dataReader(string sqlstr, ref SqlDataReader dr)
{
try
{
openConnection();
comm.CommandText = sqlstr;
comm.CommandType = CommandType.Text;
dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
try
{
if (dr != null && !dr.IsClosed)
dr.Close();
}
catch
{
}
finally
{
closeConnection();
}
}
}
/// <summary>
/// 返回指定Sql语句的DataSet
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>DataSet</returns>
public static DataSet dataSet(string sqlstr)
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return ds;
}
/// <summary>
/// 返回指定Sql语句的DataSet
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <param name="ds">传入的引用DataSet对象</param>
public static void dataSet(string sqlstr, ref DataSet ds)
{
SqlDataAdapter da = new SqlDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}
/// <summary>
/// 返回指定Sql语句的DataTable
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>DataTable</returns>
public static DataTable dataTable(string sqlstr)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable datatable = new DataTable();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(datatable);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return datatable;
}
/// <summary>
/// 执行指定Sql语句,同时给传入DataTable进行赋值
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <param name="dt">ref DataTable dt </param>
public static void dataTable(string sqlstr, ref DataTable dt)
{
SqlDataAdapter da = new SqlDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}
/// <summary>
/// 执行带参数存储过程并返回数据集合
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="parameters">SqlParameterCollection 输入参数</param>
/// <returns></returns>
public static DataTable dataTable(string procName, SqlParameterCollection parameters)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable datatable = new DataTable();
try
{
openConnection();
comm.Parameters.Clear();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = procName;
foreach (SqlParameter para in parameters)
{
SqlParameter p = (SqlParameter)para;
comm.Parameters.Add(p);
}
da.SelectCommand = comm;
da.Fill(datatable);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return datatable;
}
public static DataView dataView(string sqlstr)
{
SqlDataAdapter da = new SqlDataAdapter();
DataView dv = new DataView();
DataSet ds = new DataSet();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(ds);
dv = ds.Tables[0].DefaultView;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return dv;
}
}
}
using System.Data;
using System.Data.SqlClient;
namespace SysClassLibrary
{
/// <summary>
/// CDataAccess 的摘要说明。
///<description>数据处理基类,调用方式:CDataAccess.DataSet((string)sqlstr);或者CDataAccess.DataSet((string)sqlstr,ref DataSet ds); </description>
/// </summary>
public class CDataAccess
{
#region 属性
protected static SqlConnection conn = new SqlConnection();
protected static SqlCommand comm = new SqlCommand();
#endregion
public CDataAccess()
{
//init();
}
#region 内部函数 静态方法中不会执行CDataAccess()构造函数
/// <summary>
/// 打开数据库连接
/// </summary>
private static void openConnection()
{
if (conn.State == ConnectionState.Closed)
{
//SysConfig.ConnectionString 为系统配置类中连接字符串,如:"server=localhost;database=databasename;uid=sa;pwd=;"
conn.ConnectionString = "";// SysConfig.ConnectionString;
comm.Connection = conn;
try
{
conn.Open();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
/// <summary>
/// 关闭当前数据库连接
/// </summary>
private static void closeConnection()
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
comm.Dispose();
}
#endregion
/// <summary>
/// 执行Sql查询语句
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
public static void ExecuteSql(string sqlstr)
{
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
comm.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="coll">SqlParameters 集合</param>
public static void ExecutePorcedure(string procName, SqlParameter[] coll)
{
try
{
openConnection();
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = procName;
comm.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
comm.Parameters.Clear();
closeConnection();
}
}
/// <summary>
/// 执行存储过程并返回数据集
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="coll">SqlParameter集合</param>
/// <param name="ds">DataSet </param>
public static void ExecutePorcedure(string procName, SqlParameter[] coll, ref DataSet ds)
{
try
{
SqlDataAdapter da = new SqlDataAdapter();
openConnection();
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = procName;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
comm.Parameters.Clear();
closeConnection();
}
}
/// <summary>
/// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>object 返回值 </returns>
public static object ExecuteScalar(string sqlstr)
{
object obj = new object();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
obj = comm.ExecuteScalar();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return obj;
}
/// <summary>
/// 执行Sql查询语句,同时进行事务处理
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
public static void ExecuteSqlWithTransaction(string sqlstr)
{
SqlTransaction trans;
trans = conn.BeginTransaction();
comm.Transaction = trans;
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
comm.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
closeConnection();
}
}
/// <summary>
/// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>SqlDataReader对象</returns>
public static SqlDataReader dataReader(string sqlstr)
{
SqlDataReader dr = null;
try
{
openConnection();
comm.CommandText = sqlstr;
comm.CommandType = CommandType.Text;
dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
try
{
dr.Close();
closeConnection();
}
catch
{
}
}
return dr;
}
/// <summary>
/// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <param name="dr">传入的ref DataReader 对象</param>
public static void dataReader(string sqlstr, ref SqlDataReader dr)
{
try
{
openConnection();
comm.CommandText = sqlstr;
comm.CommandType = CommandType.Text;
dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
try
{
if (dr != null && !dr.IsClosed)
dr.Close();
}
catch
{
}
finally
{
closeConnection();
}
}
}
/// <summary>
/// 返回指定Sql语句的DataSet
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>DataSet</returns>
public static DataSet dataSet(string sqlstr)
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return ds;
}
/// <summary>
/// 返回指定Sql语句的DataSet
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <param name="ds">传入的引用DataSet对象</param>
public static void dataSet(string sqlstr, ref DataSet ds)
{
SqlDataAdapter da = new SqlDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}
/// <summary>
/// 返回指定Sql语句的DataTable
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>DataTable</returns>
public static DataTable dataTable(string sqlstr)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable datatable = new DataTable();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(datatable);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return datatable;
}
/// <summary>
/// 执行指定Sql语句,同时给传入DataTable进行赋值
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <param name="dt">ref DataTable dt </param>
public static void dataTable(string sqlstr, ref DataTable dt)
{
SqlDataAdapter da = new SqlDataAdapter();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}
/// <summary>
/// 执行带参数存储过程并返回数据集合
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="parameters">SqlParameterCollection 输入参数</param>
/// <returns></returns>
public static DataTable dataTable(string procName, SqlParameterCollection parameters)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable datatable = new DataTable();
try
{
openConnection();
comm.Parameters.Clear();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = procName;
foreach (SqlParameter para in parameters)
{
SqlParameter p = (SqlParameter)para;
comm.Parameters.Add(p);
}
da.SelectCommand = comm;
da.Fill(datatable);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return datatable;
}
public static DataView dataView(string sqlstr)
{
SqlDataAdapter da = new SqlDataAdapter();
DataView dv = new DataView();
DataSet ds = new DataSet();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
da.SelectCommand = comm;
da.Fill(ds);
dv = ds.Tables[0].DefaultView;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return dv;
}
}
}
- .Net数据库相关操作
- .net操作数据库相关基础资料
- ADO.NET相关操作
- 数据库的相关操作
- 数据库的相关操作
- SQl 数据库相关操作
- 数据库相关操作封装
- 数据库相关的操作
- 数据库相关操作工具
- mysql 数据库相关操作
- YII 数据库相关操作
- Sqlite数据库相关操作
- uchome数据库相关操作
- java操作数据库相关
- SQLite数据库相关操作
- Web_PHP_php数据库相关操作;
- Ibatis数据库相关操作
- PDO 数据库相关操作
- Windows组件0Day漏洞 通杀IE6与IE7浏览器
- CSS语法手册(二)文本属性
- 攻击木马--远程控制软件自身的漏洞
- SqlServer2000中用户自定义数据类型的使用
- 终于可以不用任何控件作出XP风格的程序了
- .Net数据库相关操作
- ATAPI(磁盘端口驱动)级文件保护简单实现
- CSS语法手册(三)文本填充,边框,边界和位置属性(一)
- 使用perl通过adsi接口导出AD帐户列
- 教你如何在Linux/Unix平台安装MySQL
- 判断Windows系统补丁
- Delphi 6 XML映象工具
- C++中有了malloc/free,为什么还要用new/delete
- CSS语法手册(四)文本填充,边框,边界和位置属性(二)