【下载文档:  asp.net 数据库连接类代码(SQL).txt 】

---

asp.net 数据库连接类代码(SQL)
复制代码 代码如下:public class SqlOperation { #region 属性 ///

/// 保存在Web.config中的连接字符串 ///

protected static string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["hao"].ConnectionString; ///

/// SqlConnection对象 ///

protected static SqlConnection conn = new SqlConnection(); ///

/// SqlCommand对象 ///

protected static SqlCommand comm = new SqlCommand(); #endregion #region 内部函数 ///

/// 打开数据库连接 ///

private static void ConnectionOpen() { if (conn.State != ConnectionState.Open) { conn.Close(); conn.ConnectionString = connectionstring; comm.Connection = conn; try { conn.Open(); } catch (Exception ex) { throw new Exception(ex.Message); } } } ///

/// 关闭数据库连接 ///

private static void ConnectionClose() { conn.Close(); conn.Dispose(); comm.Dispose(); } #endregion ///

/// 执行SQL语句 ///

/// 要执行的SQL语句 public static void ExecuteSQL(string SqlString) { try { ConnectionOpen(); comm.CommandType = CommandType.Text; comm.CommandText = SqlString; comm.ExecuteNonQuery(); } catch (Exception ex) { try { ConnectionClose(); } catch (Exception e) { throw new Exception(e.Message); } throw new Exception(ex.Message); } finally { ConnectionClose(); } } ///

/// 执行存储过程 ///

/// 存储过程名称 /// 存储过程需要的参数集合 public static void ExecuteProcedure(string ProcedureName, params SqlParameter[] coll) { try { ConnectionOpen(); comm.CommandType = CommandType.StoredProcedure; comm.CommandText = ProcedureName; comm.Parameters.Clear(); for (int i = 0; i < coll.Length; i++) { comm.Parameters.Add(coll[i]); } comm.ExecuteNonQuery(); } catch (Exception ex) { try { ConnectionClose(); } catch (Exception e) { throw new Exception(e.Message); } throw new Exception(ex.Message); } finally { ConnectionClose(); } } ///

/// 执行Sql查询并返回第一行的第一条记录，返回object，使用时需要拆箱 -> unbox ///

/// 传入的Sql语句 /// 返回object类型的第一行第一条记录 public static object ExecuteScalar(string SqlString) { object obj = new object(); try { ConnectionOpen(); comm.CommandType = CommandType.Text; comm.CommandText = SqlString; obj = comm.ExecuteScalar(); } catch (Exception ex) { try { ConnectionClose(); } catch (Exception e) { throw new Exception(e.Message); } throw new Exception(ex.Message); } finally { ConnectionClose(); } return obj; } ///

/// 执行SQL语句,同时进行事务处理 ///

/// 要执行的SQL语句 public static void ExecuteTransactionSQL(string SqlString) { SqlTransaction trans; trans = conn.BeginTransaction(); comm.Transaction = trans; try { ConnectionOpen(); comm.CommandType = CommandType.Text; comm.CommandText = SqlString; comm.ExecuteNonQuery(); trans.Commit(); } catch (Exception ex) { try { ConnectionClose(); } catch (Exception e) { throw new Exception(e.Message); } throw new Exception(ex.Message); } finally { ConnectionClose(); } } ///

/// 执行指定SQL查询，返回DataSet ///

/// 要执行的SQL语句 /// DataSet public static DataSet GetDataSetBySQL(string SqlString) { SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); try { ConnectionOpen(); comm.CommandType = CommandType.Text; comm.CommandText = SqlString; da.SelectCommand = comm; da.Fill(ds); } catch (Exception ex) { try { ConnectionClose(); } catch (Exception e) { throw new Exception(e.Message); } throw new Exception(ex.Message); } finally { ConnectionClose(); } return ds; } ///

/// 通过存储过程返回DataSet ///

/// 存储过程名称 /// SqlParameter集合 /// DataSet public static DataSet GetDataSetByProcedure(string ProcedureName, params SqlParameter[] coll) { SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); try { ConnectionOpen(); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Clear(); for (int i = 0; i < coll.Length; i++) { comm.Parameters.Add(coll[i]); } comm.CommandText = ProcedureName; da.SelectCommand = comm; da.Fill(ds); } catch (Exception ex) { try { ConnectionClose(); } catch (Exception e) { throw new Exception(e.Message); } throw new Exception(ex.Message); } finally { ConnectionClose(); } return ds; } ///

/// 通过存储过程返回DataSet ///

/// 存储过程名称 /// DataSet public static DataSet GetDataSetByProcedure(string ProcedureName) { SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); try { ConnectionOpen(); comm.CommandType = CommandType.StoredProcedure; comm.CommandText = ProcedureName; comm.Parameters.Clear(); da.SelectCommand = comm; da.Fill(ds); } catch (Exception ex) { try { ConnectionClose(); } catch (Exception e) { throw new Exception(e.Message); } throw new Exception(ex.Message); } finally { ConnectionClose(); } return ds; } ///

/// 返回指定sql语句的DataTable ///

/// 传入的Sql语句 /// DataTable public static DataTable GetDataTableBySQL(string SqlString) { SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); try { ConnectionOpen(); comm.CommandType = CommandType.Text; comm.CommandText = SqlString; da.SelectCommand = comm; da.Fill(dt); } catch (Exception ex) { try { ConnectionClose(); } catch (Exception e) { throw new Exception(e.Message); } throw new Exception(ex.Message); } finally { ConnectionClose(); } return dt; } ///

/// 根据存储过程返回DataTable ///

/// 存储过程名 /// SqlParameter集合 /// DataTable public static DataTable GetDataTableByProcedure(string ProcedureName, params SqlParameter[] coll) { SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); try { ConnectionOpen(); comm.Parameters.Clear(); comm.CommandType = CommandType.StoredProcedure; comm.CommandText = ProcedureName; for (int i = 0; i < coll.Length; i++) { comm.Parameters.Add(coll[i]); } da.SelectCommand = comm; da.Fill(dt); } catch (Exception ex) { try { ConnectionClose(); } catch (Exception e) { throw new Exception(e.Message); } throw new Exception(ex.Message); } finally { ConnectionClose(); } return dt; } ///

/// 根据存储过程返回DataTable ///

/// 存储过程名称 /// DataTable public static DataTable GetDataTableByProcedure(string ProcedureName) { SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); try { ConnectionOpen(); comm.Parameters.Clear(); comm.CommandType = CommandType.StoredProcedure; comm.CommandText = ProcedureName; da.SelectCommand = comm; da.Fill(dt); } catch (Exception ex) { try { ConnectionClose(); } catch (Exception e) { throw new Exception(e.Message); } throw new Exception(ex.Message); } finally { ConnectionClose(); } return dt; } }