服务器之家

服务器之家 > 正文

asp.net 数据库连接类代码(SQL)

时间:2019-07-15 09:49     来源/作者:服务器之家

代码如下:


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; 

相关文章

热门资讯

抖音撒撒累累是什么歌 撒撒累累张艺兴歌曲名字
抖音撒撒累累是什么歌 撒撒累累张艺兴歌曲名字 2019-06-05
叠猫猫队长可以退出吗 叠猫猫队长怎么换队伍
叠猫猫队长可以退出吗 叠猫猫队长怎么换队伍 2019-06-05
淘宝合合卡可以送好友吗 淘宝合合卡快速获得方式
淘宝合合卡可以送好友吗 淘宝合合卡快速获得方式 2019-06-13
叠猫猫可以邀请多少人 叠猫猫大挑战一个队最多多少人
叠猫猫可以邀请多少人 叠猫猫大挑战一个队最多多少人 2019-06-05
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
返回顶部