.net MVC(存储过程+SQLHelper)

连接数据库帮助类：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;
            }
        }

 

用法案例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;
        }

 

百度云盘完整案例源码：

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