連接數據庫幫助類：SqlHelper.cs（用的時候只需調用其中的方法名，作用：可避免在DAL層重復地寫連接數據庫的代碼）

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; using System.Configuration; using System.Data; using System.Collections;namespace DAL {public class SqlHelper{private static string connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString();public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters){SqlCommand cmd = new SqlCommand();using (SqlConnection conn = new SqlConnection(connectionString)){PrepareCommand(cmd, commandType, conn, commandText, commandParameters);int val = cmd.ExecuteNonQuery();return val;}}public static SqlDataReader GetReader(string sql){SqlConnection conn = new SqlConnection(connectionString);SqlCommand cmd = new SqlCommand(sql, conn);try{conn.Open();return cmd.ExecuteReader(CommandBehavior.CloseConnection);}catch (Exception ex){throw ex;}}public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues){using (SqlConnection conn = new SqlConnection(connectionString)){SqlCommand cmd = new SqlCommand();PrepareCommand(cmd, conn, spName, parameterValues);int val = cmd.ExecuteNonQuery();return val;}}public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters){SqlConnection conn = new SqlConnection(connectionString);try{SqlCommand cmd = new SqlCommand();PrepareCommand(cmd, commandType, conn, commandText, commandParameters);SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);return rdr;}catch{conn.Close();throw;}}public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues){SqlConnection conn = new SqlConnection(connectionString);try{SqlCommand cmd = new SqlCommand();PrepareCommand(cmd, conn, spName, parameterValues);SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);return rdr;}catch{conn.Close();throw;}}public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues){using (SqlConnection conn = new SqlConnection(connectionString)){SqlCommand cmd = new SqlCommand();PrepareCommand(cmd, conn, spName, parameterValues);using (SqlDataAdapter da = new SqlDataAdapter(cmd)){DataSet ds = new DataSet();da.Fill(ds);return ds;}}}public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters){using (SqlConnection conn = new SqlConnection(connectionString)){SqlCommand cmd = new SqlCommand();PrepareCommand(cmd, commandType, conn, commandText, commandParameters);using (SqlDataAdapter da = new SqlDataAdapter(cmd)){DataSet ds = new DataSet();da.Fill(ds);return ds;}}}public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters){SqlCommand cmd = new SqlCommand();using (SqlConnection conn = new SqlConnection(connectionString)){PrepareCommand(cmd, commandType, conn, commandText, commandParameters);object val = cmd.ExecuteScalar();return val;}}public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues){SqlCommand cmd = new SqlCommand();using (SqlConnection conn = new SqlConnection(connectionString)){PrepareCommand(cmd, conn, spName, parameterValues);object val = cmd.ExecuteScalar();return val;}}public static void PrepareCommand(SqlCommand cmd, CommandType commandType, SqlConnection conn, string commandText, SqlParameter[] cmdParms){//打開連接if (conn.State != ConnectionState.Open)conn.Open();//設置SqlCommand對象cmd.Connection = conn;cmd.CommandText = commandText;cmd.CommandType = commandType;if (cmdParms != null){foreach (SqlParameter parm in cmdParms)cmd.Parameters.Add(parm);}}public static void PrepareCommand(SqlCommand cmd, SqlConnection conn, string spName, params object[] parameterValues){//打開連接if (conn.State != ConnectionState.Open)conn.Open();//設置SqlCommand對象cmd.Connection = conn;cmd.CommandText = spName;cmd.CommandType = CommandType.StoredProcedure;//獲取存儲過程的參數 SqlCommandBuilder.DeriveParameters(cmd);//移除Return_Value 參數cmd.Parameters.RemoveAt(0);//設置參數值if (parameterValues != null){for (int i = 0; i < cmd.Parameters.Count; i++){cmd.Parameters[i].Value = parameterValues[i];}}}}}

?

?

用法案例1：

//增加數據public bool CreateUserBySqlbool(User userAdd){SqlParameter[] cmdParms = new SqlParameter[]{new SqlParameter("@UserNum",userAdd.UserNum),new SqlParameter("@UserName ", userAdd.UserName),new SqlParameter("@UserPassword ", userAdd.UserPassword),new SqlParameter("@UserPower",userAdd.UserPower),new SqlParameter("@UserSex",userAdd.UserSex),new SqlParameter("@UserAge",userAdd.UserAge),new SqlParameter("@UserIDCardNum",userAdd.UserIDCardNum),new SqlParameter("@UserPhone", userAdd.UserPhone ),new SqlParameter("@UserEmail", userAdd.UserEmail),new SqlParameter("@UserPhoto", userAdd.UserPhoto),new SqlParameter("@UserAddress", userAdd.UserAddress),new SqlParameter("@Remarks", userAdd.Remarks)};string proc = "spCreateUserBySql";int result = SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, proc, cmdParms);if (result > 0){return true;}else{return false;}} DAL層的增加數據

?

用法案例2：將執行的結果強行轉換為int型

?int result = (int)SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, proc, cmdParms);

?

用法案例3：返回 DataSet列表

關鍵代碼：?DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, 存儲過程名稱, cmdPara);

//關鍵字查詢public List<User> GetUserBySql(string UserName){SqlParameter[] cmdPara = new SqlParameter[]{new SqlParameter("@UserName", UserName)}; string spname = "spGetUserBySql";try{DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spname, cmdPara);if (ds.Tables.Count > 0){DataTable dt = ds.Tables[0];foreach (DataRow row in dt.Rows){User user = new User();user.UserId = (int)row["UserID"];user.UserNum = (int)row["UserNum"];user.UserName = (string)row["UserName"];user.UserPassword = (string)row["UserPassword"];user.UserPower = (string)row["UserPower"];user.UserSex = (string)row["UserSex"];user.UserAge = (int)row["UserAge"];user.UserIDCardNum = (string)row["UserIDCardNum"];user.UserPhone = (string)row["UserPhone"];user.UserEmail = (string)row["UserEmail"];user.UserPhoto = (string)row["UserPhoto"];user.UserAddress = (string)row["UserAddress"];if (DBNull.Value == row["Remarks"]) //兼容oracle，sqlserver，mysql數據庫對null和''(空)的不同處理的解決方法 {user.Remarks = "無";}else{user.Remarks = (string)row["Remarks"];}list.Add(user);}}} catch (SqlException ex){foreach (SqlError sqlError in ex.Errors){Console.WriteLine(sqlError.Message);}}return list;} 返回 DataSet列表

?

百度云盤完整案例源碼：

鏈接：https://pan.baidu.com/s/1-PiXiSrNwy5b8c08q7B9Rw
提取碼：bt2z

?

轉載于:https://www.cnblogs.com/fzqm-lwz/p/10046653.html

總結

以上是生活随笔為你收集整理的.net MVC(存储过程+SQLHelper)的全部內容，希望文章能夠幫你解決所遇到的問題。

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