一个帮助类
using System; using System.Data; using System.Data.SqlClient; namespace 后台管理系统 { /// <summary> /// CDataAccess 的摘要说明。 /// <description>数据处理基类,调用方式: /// CDataAccess.DataSet((string)sqlstr); /// 或者 /// CDataAccess.DataSet((string)sqlstr,ref DataSet ds); </description> /// </summary> public class CDataAccess { private static string ConnectionString; public CDataAccess() { } public CDataAccess(string ConnectionString_f) { ConnectionString = ConnectionString_f; } protected static SqlConnection conn = new SqlConnection(); protected static SqlCommand comm = new SqlCommand(); /// <summary> /// 打开数据库连接 /// </summary> private static void openConnection() { if (conn.State == ConnectionState.Closed) { //SysConfig.ConnectionString 为系统配置类中连接字符串,如:"server=localhost;database=databasename;uid=sa;pwd=;" //if (ConnectionString.Length = 0) mbox; ConnectionString = "Data Source=HostPC,PcPort;Initial Catalog=Engineer;User ID=sa;PWD=USERPWD"; conn.ConnectionString = 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(); } /// <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 int ExecuteSqlWithTransaction(string sqlstr) { openConnection(); SqlTransaction trans; trans = conn.BeginTransaction(); comm.Transaction = trans; try { comm.CommandType = CommandType.Text; //string[] st = sqlstr.Split(';'); // for (int i = 0; i < st.Length; i++) // { // comm.CommandText = st[i]; comm.CommandText = sqlstr; comm.ExecuteNonQuery(); // } trans.Commit(); return 0; } catch { trans.Rollback(); return 1; } 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 + " // " + sqlstr); } 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; } /// <summary> /// Datagridview 控件直接调用 /// 如: dataGridView1.DataSource = CDataAccess.dataView("select * from 用户信息表"); /// </summary> /// <param name="sqlstr">要查询的SQL语句</param> /// <returns>数据源DataSourse</returns> 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; } } }