private static string connString=ConfigurationManager.ConnStrings["connString"].ToString(); //在app.config中configuration添加相應的數據庫連接配置文件
1.格式化sql語句
//執行增刪改查
public static int Update(string sql)
{
SqlConnection conn=new SqlConnection(connString);
SqlCommand cmd=new SqlCommand(sql,conn);
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
//單一結果查詢
public static object GetSingleResult(string sql)
{
SqlConnection conn=new SqlConnection(connString);
SqlCommand cmd=new SqlCommand(sql,conn);
try
{
conn.Open();
return cmd.ExecuteScalar();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
//只讀結果集查詢
public static SqlDataReader GetReader(string sql)
{
SqlConnection conn=new SqlConnection(connString);
SqlCommand cmd=new SqlCommand(sql,conn);
try
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(Exception ex)
{
conn.Close();
throw ex;
}
}
//DataSet數據集查詢
public static DataSet GetDataSet(string sql)
{
SqlConnection conn=new SqlConnection(connString);
SqlCommand cmd=new SqlCommand(sql,conn);
DataSet ds=new DataSet();
SqlDataAdapter da=new SqlDataAdapter();
try
{
conn.Open();
da.Fill(ds);
return ds;
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
2.帶參數的sql語句
//執行增刪改查
public static int Update(string sql,SqlParameter[] param)
{
SqlConnection conn=new SqlConnection(connString);
SqlCommand cmd=new SqlCommand(sql,conn);
try
{
conn.Open();
cmd.Parameters.AddRange(param);
return cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
//查詢單一結果
public static object GetSingleResult(string sql,SqlParameter[] param)
{
SqlConnection conn=new SqlConnection(connString);
SqlCommand cmd=new SqlCommand(sql,conn);
try
{
conn.Open();
cmd.Parameters.AddRange(param);
retrurn cmd.ExecuteScalar();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
//查詢只讀結果集
public static SqlDataReader GetReader(string sql,SqlParameter[] param)
{
SqlConnection conn=new SqlConnection(connString);
SqlCommand cmd=new SqlCommand(sql,conn);
try
{
conn.Open();
cmd.Parameters.AddRange(param);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(Exception ex)
{
conn.Close();
throw ex;
}
}
3.帶參數的存儲過程
//執行增刪改查
public static int UpdateByProcedure(string procedureName,SqlParameter[] param)
{
SqlConnection conn=new SqlConnection(connString);
SqlCommand cmd=new SqlCommand();
cmd.Connection=conn;
try
{
conn.Open();
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText=procedureName;
cmd.Parameters.AddRange(param);
return cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
//查詢單一結果
public static object GetSingleResultByProcedure(string procedureName,SqlParameter[] param)
{
SqlConnection conn=new SqlConnection(connString);
SqlCommand cmd=new SqlCommand();
cmd.Connection=conn;
try
{
conn.Open();
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText=procedureName;
cmd.Parameters.AddRange(param);
retrurn cmd.ExecuteScalar();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
//查詢只讀結果集
public static SqlDataReader GetReaderByProcedure(string procedureName,SqlParameter[] param)
{
SqlConnection conn=new SqlConnection(connString);
SqlCommand cmd=new SqlCommand();
cmd.Connection=conn;
try
{
conn.Open();
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText=procedureName;
cmd.Parameters.AddRange(param);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(Exception ex)
{
conn.Close();
throw ex;
}
}
4.使用事務更新
public static bool UpdateByTracsaction(List<string> sqlList)
{
SqlConnection conn=new SqlConnection(connString);
SqlCommand cmd=new SqlCommand();
cmd.Connection=conn;
try
{
conn.Open();
cmd.Transaction=conn.BeginTransaction();//開啟事務
foreach(string sql in sqlList)
{
cmd.CommandText=sql;
cmd.ExecuteNonQuery();
}
cmd.Transaction.Commit();//提交事務
return true;
}
catch(Exception ex)
{
if(cmd.Transaction!=null)
{
cmd.Transaction.Rollback();//回滾事務
}
throw ex;
}
finally
{
if(cmd.Transaction!=null)
{
cmd.Transaction=null;//清空事務
}
conn.Close();
}
}
