/// <summary>/// SqlHelper類 by zy 2016-3-11/// </summary>public sealed class SqlHelper{//如果項目中只連接了一個數據庫 那么可以在此定義一個固定的連接字符串private static string connectionStr = ConfigurationManager.ConnectionStrings["SQLConnString1"].ConnectionString;#region ExecuteNonQuery/// <summary>/// 執行sql語句 返回受影響行數/// </summary>/// <param name="connectionString">連接字符串</param>/// <param name="cmdType">command類型</param>/// <param name="cmdText">sql語句</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters){SqlCommand cmd = new SqlCommand();using (SqlConnection conn = new SqlConnection(connectionString)){PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return val;}}/// <summary>/// 執行sql語句 返回當前插入的記錄id(其實是ExecuteScalar轉換成id返回)/// </summary>/// <param name="connectionString">連接字符串</param>/// <param name="cmdType">command類型</param>/// <param name="cmdText">sql語句</param>/// <param name="returnID">返回id</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, out int returnID, params SqlParameter[] commandParameters){SqlCommand cmd = new SqlCommand();using (SqlConnection conn = new SqlConnection(connectionString)){returnID = 0;PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);returnID = (Int32)cmd.ExecuteScalar();cmd.Parameters.Clear();return returnID;}}/// <summary>/// 執行sql語句 返回受影響行數(數據庫連接SqlConnection)/// </summary>/// <param name="connection">SqlConnection連接</param>/// <param name="cmdType">command類型</param>/// <param name="cmdText">sql語句</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters){SqlCommand cmd = new SqlCommand();PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return val;}/// <summary>/// 執行sql語句 返回受影響行數(事務SqlTransaction)/// </summary>/// <param name="trans">SqlTransaction事務</param>/// <param name="cmdType">command類型</param>/// <param name="cmdText">sql語句</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters){SqlCommand cmd = new SqlCommand();PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return val;}#endregion#region ExecuteScalar/// <summary>/// 執行sql語句 返回結果集第一行第一列/// </summary>/// <param name="connectionString">連接字符串</param>/// <param name="cmdType">Command類型</param>/// <param name="cmdText">sql語句</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters){SqlCommand cmd = new SqlCommand();using (SqlConnection connection = new SqlConnection(connectionString)){PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);object val = cmd.ExecuteScalar();cmd.Parameters.Clear();return val;}}/// <summary>/// 執行sql語句 返回結果集第一行第一列(事務)/// </summary>/// <param name="trans">SqlTransaction事務</param>/// <param name="cmdType">Command類型</param>/// <param name="cmdText">sql語句</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters){SqlCommand cmd = new SqlCommand();PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);object val = cmd.ExecuteScalar();cmd.Parameters.Clear();return val;}/// <summary>/// 執行sql語句 返回結果集第一行第一列(數據庫連接)/// </summary>/// <param name="connection">SqlConnection連接</param>/// <param name="cmdType">Command類型</param>/// <param name="cmdText">sql語句</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters){SqlCommand cmd = new SqlCommand();PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);object val = cmd.ExecuteScalar();cmd.Parameters.Clear();return val;}#endregion#region ExecuteReader/// <summary>/// 執行sql語句 返回reader/// </summary>/// <param name="connectionString">連接字符串</param>/// <param name="cmdType">command類型</param>/// <param name="cmdText">sql語句</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters){SqlCommand cmd = new SqlCommand();SqlConnection conn = new SqlConnection(connectionString);try{PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);cmd.Parameters.Clear();return rdr;}catch{conn.Close();throw;}}#endregion#region DataSet/// <summary>/// 執行sql語句 返回DataSet結果集(無參數)/// </summary>/// <param name="connectionString">連接字符串</param>/// <param name="commandType">command類型</param>/// <param name="commandText">sql語句</param>/// <returns></returns>public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText){return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);}/// <summary>/// 執行sql語句 返回DataSet結果集/// </summary>/// <param name="connectionString">連接字符串</param>/// <param name="commandType">command類型</param>/// <param name="commandText">sql語句</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters){//創建數據庫連接 完成后disposeusing (SqlConnection cn = new SqlConnection(connectionString)){cn.Open();//根據數據庫連接 調用具體方法return ExecuteDataset(cn, commandType, commandText, commandParameters);}}/// <summary>/// 執行sql語句 返回DataSet結果集(數據庫連接SqlConnection)/// </summary>/// <param name="connection">SqlConnection連接</param>/// <param name="commandType">command類型</param>/// <param name="commandText">sql語句</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters){//創建commandSqlCommand cmd = new SqlCommand();//準備command對象PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);//創建SqlDataAdapter和DataSetSqlDataAdapter da = new SqlDataAdapter(cmd);DataSet ds = new DataSet();//把結果集裝進dataset da.Fill(ds);//返回return ds;}#endregion DataSet#region DataTable/// <summary>/// 執行sql語句 返回DataTable結果集(無參數)/// </summary>/// <param name="connectionString">連接字符串</param>/// <param name="commandType">command類型</param>/// <param name="commandText">sql語句</param>/// <returns></returns>public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText){return ExecuteDataTable(connectionString, commandType, commandText, (SqlParameter[])null);}/// <summary>/// 執行sql語句 返回DataTable結果集/// </summary>/// <param name="connectionString">連接字符串</param>/// <param name="commandType">command類型</param>/// <param name="commandText">sql語句</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters){using (SqlConnection cn = new SqlConnection(connectionString)){cn.Open();return ExecuteDataTable(cn, commandType, commandText, commandParameters);}}/// <summary>/// 執行sql語句 返回DataTable結果集/// </summary>/// <param name="connection">SqlConnection連接</param>/// <param name="commandType">command類型</param>/// <param name="commandText">sql語句</param>/// <param name="commandParameters">參數</param>/// <returns></returns>public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters){SqlCommand cmd = new SqlCommand();PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);SqlDataAdapter da = new SqlDataAdapter(cmd);DataTable dt = new DataTable();da.Fill(dt);return dt;}#endregion DataTable#region PrepareCommand/// <summary>/// 準備Command對象/// </summary>/// <param name="cmd">SqlCommand 對象</param>/// <param name="conn">SqlConnection 對象</param>/// <param name="trans">SqlTransaction 對象</param>/// <param name="cmdType">Command 類型</param>/// <param name="cmdText">sql語句</param>/// <param name="cmdParms">參數</param>private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms){//打開SqlConnection連接if (conn.State != ConnectionState.Open)conn.Open();//給Command的Connection CommandText設置值cmd.Connection = conn;cmd.CommandText = cmdText;//如果是事務 設置事務if (trans != null)cmd.Transaction = trans;//Command類型cmd.CommandType = cmdType;//Command參數if (cmdParms != null){foreach (SqlParameter parm in cmdParms){//在此可以操作參數 比如把參數值里的英文逗號全部改為中文逗號 或者其他操作//switch (parm.SqlDbType)//{// case SqlDbType.Char:// case SqlDbType.NChar:// case SqlDbType.NText:// case SqlDbType.NVarChar:// case SqlDbType.Text:// case SqlDbType.VarChar:// if (parm.Value != null && parm.Value != DBNull.Value && parm.ParameterName != "@SQLClause")// {// string tmp = parm.Value.ToString();// tmp = tmp.Replace(",", "，");// tmp = tmp.Replace("'", "＇");// parm.Value = tmp;// }// break;//}//把參數添加到Command中的Parameters中 cmd.Parameters.Add(parm);}}}#endregion}

?

轉載于:https://www.cnblogs.com/blazeZzz/p/7381756.html

總結

以上是生活随笔為你收集整理的整理出一个比较实用的SqlHelper类 满足大多数情况的使用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。