.net常用通用数据访问类(带数组参数方法，新增调用数据库存储过程方法)

一、App.config一般配置文件编写

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6" />
    </startup>

//以上为.net自动生成框架代码，一般情况下不做修改，以下为所要访问的数据库

  <connectionStrings>
    <add name="sqlcon" connectionString="Server=LAPTOP-I6FTHU4E\SQLEXPRESS;DataBase=SMDB;Uid=sa;Pwd=mrc36286823.;"/>
  </connectionStrings>
</configuration>

二、通用数据库访问类代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace DAL.Helper
{
    public class SQLHelper
    {
        private static string connString = ConfigurationManager.ConnectionStrings["sqlcon"].ToString();

        #region 程序编写程度简单，程序执行低效，容易被“注入式攻击”的通用数据访问类
        /// <summary>
        /// 增、删、改
        /// </summary>
        /// <param name="sql语句"></param>
        /// <returns></returns>
        public static int Updata(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();
            }
        }
        /// <summary>
        /// 返回单一结果查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        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();
            }
        }
        /// <summary>
        /// 返回结果集查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);

            try
            {
                conn.Open();
                SqlDataReader objReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return objReader;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        #endregion

        #region 带数组参数的通用数据访问类，能有效防止“注入式攻击”
        /// <summary>
        /// 带数组参数的通用Updata方法
        /// </summary>
        /// <param name="sql语句"></param>
        /// <param name="数组封装参数"></param>
        /// <returns></returns>
        public static int UpdataByPara(string sql, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region 调用数据库存储过程的通用数据访问类，程序执行高效，很好的防止“注入式攻击”
        /// <summary>
        /// 调用数据库存储过程的通用Updata方法
        /// </summary>
        /// <param name="存储过程名称"></param>
        /// <param name="数组封装参数"></param>
        /// <returns></returns>
        public static int UpdataByProcedure(string procedureName, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                conn.Open();
                cmd.Connection = conn;
                //声明当前调用的是存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                //存储过程名称
                cmd.CommandText = procedureName;
                //添加输入参数
                cmd.Parameters.AddRange(parameters);
                int result = cmd.ExecuteNonQuery();
                return result;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 调用数据库存储过程的通用GetSingleResult方法
        /// </summary>
        /// <param name="存储过程名称"></param>
        /// <param name="数组封装参数"></param>
        /// <returns></returns>
        public static object GetSingleResultByProcedure(string procedureName, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                conn.Open();
                cmd.Connection = conn;
                //声明当前调用的是存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                //存储过程名称
                cmd.CommandText = procedureName;
                //添加输入参数
                cmd.Parameters.AddRange(parameters);
                object result = cmd.ExecuteScalar();
                return result;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 调用数据库存储过程的通用GetReader方法
        /// </summary>
        /// <param name="存储过程名称"></param>
        /// <param name="数组封装参数"></param>
        /// <returns></returns>
        public static SqlDataReader GetReaderByProcedure(string procedureName, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                conn.Open();
                cmd.Connection = conn;
                //声明当前调用的是存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                //存储过程名称
                cmd.CommandText = procedureName;
                //添加输入参数
                cmd.Parameters.AddRange(parameters);

                SqlDataReader objReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return objReader;
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
        }
        #endregion
    }
}