ADO.NET通用类库

using System.Data;using System.Data.SqlClient;namespace DataService{ ??public class SQLHelper ???{ ???????public static readonly string connString = "Server=127.0.0.1;Database=WinFormDemo;uid=sa;pwd=123"; ???????#region ExecuteNonQuery ???????/// <summary> ???????/// 不带参数的ExcuteNonQuery ???????/// </summary> ???????public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText) ???????{ ???????????SqlCommand cmd = new SqlCommand(); ???????????cmd.CommandTimeout = 480; ???????????using (SqlConnection conn = new SqlConnection(connString)) ???????????{ ???????????????PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, (SqlParameter[])null); ???????????????int val = cmd.ExecuteNonQuery(); ???????????????return val; ???????????} ???????} ???????/// <summary> ???????/// 带参数的ExcuteNonQuery ???????/// </summary> ???????public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) ???????{ ???????????SqlCommand cmd = new SqlCommand(); ???????????cmd.CommandTimeout = 120; ???????????using (SqlConnection conn = new SqlConnection(connString)) ???????????{ ???????????????PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, cmdParms); ???????????????int val = cmd.ExecuteNonQuery(); ???????????????cmd.Parameters.Clear(); ???????????????return val; ???????????} ???????} ???????#endregion ExecuteNonQuery ???????#region ExecuteScalar ???????/// <summary> ???????/// 不带参数的ExecuteScalar ???????/// </summary> ???????public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText) ???????{ ???????????SqlCommand cmd = new SqlCommand(); ???????????cmd.CommandTimeout = 120; ???????????using (SqlConnection conn = new SqlConnection(connString)) ???????????{ ???????????????PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, (SqlParameter[])null); ???????????????object val = cmd.ExecuteScalar(); ???????????????return val; ???????????} ???????} ???????/// <summary> ???????/// 带参数的ExecuteScalar ???????/// </summary> ???????public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) ???????{ ???????????SqlCommand cmd = new SqlCommand(); ???????????cmd.CommandTimeout = 120; ???????????using (SqlConnection conn = new SqlConnection(connString)) ???????????{ ???????????????PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, cmdParms); ???????????????object val = cmd.ExecuteScalar(); ???????????????cmd.Parameters.Clear(); ???????????????return val; ???????????} ???????} ???????#endregion ExecuteScalar ???????#region ExecuteReader ???????/// <summary> ???????/// 不带参数的ExecuteReader ???????/// </summary> ???????public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText) ???????{ ???????????SqlCommand cmd = new SqlCommand(); ???????????SqlConnection conn = new SqlConnection(connString); ???????????cmd.CommandTimeout = 120; ???????????try ???????????{ ???????????????PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, (SqlParameter[])null); ???????????????SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); ???????????????return rdr; ???????????} ???????????catch ???????????{ ???????????????conn.Close(); ???????????????throw; ???????????} ???????} ???????/// <summary> ???????/// 带参数的ExecuteReader ???????/// </summary> ???????public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) ???????{ ???????????SqlCommand cmd = new SqlCommand(); ???????????SqlConnection conn = new SqlConnection(connString); ???????????cmd.CommandTimeout = 120; ???????????try ???????????{ ???????????????PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, cmdParms); ???????????????SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); ???????????????cmd.Parameters.Clear(); ???????????????return rdr; ???????????} ???????????catch ???????????{ ???????????????conn.Close(); ???????????????throw; ???????????} ???????} ???????#endregion ExecuteReader ???????#region ExecuteDataSet ???????/// <summary> ???????/// 不带参数的ExecuteDataSet,可以分页 ???????/// </summary> ???????public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText) ???????{ ???????????SqlCommand cmd = new SqlCommand(); ???????????cmd.CommandTimeout = 120; ???????????using (SqlConnection conn = new SqlConnection(connString)) ???????????{ ???????????????PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, (SqlParameter[])null); ???????????????SqlDataAdapter da = new SqlDataAdapter(cmd); ???????????????DataSet ds = new DataSet(); ???????????????da.Fill(ds, "dtable"); ???????????????cmd.Parameters.Clear(); ???????????????return ds; ???????????} ???????} ???????/// <summary> ???????/// 带参数的ExecuteDataSet,可以分页 ???????/// </summary> ???????public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) ???????{ ???????????SqlCommand cmd = new SqlCommand(); ???????????cmd.CommandTimeout = 120; ???????????using (SqlConnection conn = new SqlConnection(connString)) ???????????{ ???????????????PrepareCommand(cmd, conn, (SqlTransaction)null, cmdType, cmdText, cmdParms); ???????????????SqlDataAdapter da = new SqlDataAdapter(cmd); ???????????????DataSet ds = new DataSet(); ???????????????da.Fill(ds, "dtable"); ???????????????cmd.Parameters.Clear(); ???????????????return ds; ???????????} ???????} ???????#endregion ExecuteDataSet ???????#region PrepareCommand ???????/// <summary> ???????/// 初始化工作 ???????/// </summary> ???????private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) ???????{ ???????????if (conn.State != ConnectionState.Open) ???????????????conn.Open(); ???????????cmd.Connection = conn; ???????????cmd.CommandText = cmdText; ???????????if (trans != null) ???????????????cmd.Transaction = trans; ???????????cmd.CommandType = cmdType; ???????????if (cmdParms != null) ???????????{ ???????????????foreach (SqlParameter parm in cmdParms) ???????????????????cmd.Parameters.Add(parm); ???????????} ???????} ???????#endregion PrepareCommand ???}}