|
asp.net数据库连接类(SQL) 代码,需要的朋友可以参考下。
复制代码 代码如下: public class SqlOperation { #region 属性 /// <summary> /// 保存在Web.config中的连接字符串 /// </summary> protected static string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["hao"].ConnectionString; /// <summary> /// SqlConnection对象 /// </summary> protected static SqlConnection conn = new SqlConnection(); /// <summary> /// SqlCommand对象 /// </summary> protected static SqlCommand comm = new SqlCommand(); #endregion
#region 内部函数 /// <summary> /// 打开数据库连接 /// </summary> 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); } } }
/// <summary> /// 关闭数据库连接 /// </summary> private static void ConnectionClose() { conn.Close(); conn.Dispose(); comm.Dispose(); }
#endregion
/// <summary> /// 执行SQL语句 /// </summary> /// <param name="SqlString">要执行的SQL语句</param> 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(); } }
/// <summary> /// 执行存储过程 /// </summary> /// <param name="ProcedureName">存储过程名称</param> /// <param name="coll">存储过程需要的参数集合</param> 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(); } }
/// <summary> /// 执行Sql查询并返回第一行的第一条记录,返回object,使用时需要拆箱 -> unbox /// </summary> /// <param name="sqlstr">传入的Sql语句</param> /// <returns>返回object类型的第一行第一条记录</returns> 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; }
/// <summary> /// 执行SQL语句,同时进行事务处理 /// </summary> /// <param name="sqlstr">要执行的SQL语句</param> 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(); } }
/// <summary> /// 执行指定SQL查询,返回DataSet /// </summary> /// <param name="sqlstr">要执行的SQL语句</param> /// <returns>DataSet</returns> 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; }
/// <summary> /// 通过存储过程返回DataSet /// </summary> /// <param name="ProcedureName">存储过程名称</param> /// <param name="coll">SqlParameter集合</param> /// <returns>DataSet</returns> 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; }
/// <summary> /// 通过存储过程返回DataSet /// </summary> /// <param name="ProcedureName">存储过程名称</param> /// <returns>DataSet</returns> 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; }
/// <summary> /// 返回指定sql语句的DataTable /// </summary> /// <param name="sqlstr">传入的Sql语句</param> /// <returns>DataTable</returns> 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; }
/// <summary> /// 根据存储过程返回DataTable /// </summary> /// <param name="ProcedureName">存储过程名</param> /// <param name="coll">SqlParameter集合</param> /// <returns>DataTable</returns> 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; }
/// <summary> /// 根据存储过程返回DataTable /// </summary> /// <param name="ProcedureName">存储过程名称</param> /// <returns>DataTable</returns> 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; } }
|
|