C# 对数据库操作的帮助类SQLHelper.cs

在操作数据库的时候,制作一个SqlHelper类封装常用的方法,减少重复代码.

在配置文件App.config中加入数据库的连接信息例如

<connectionStrings>
    <add name="Test" connectionString="Data Source=(local);Initial Catalog=Test;User ID=sa;Password=123456"/>
  </connectionStrings>

引用:

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

从配置文件App.config中获得连接字符串

        private static readonly string connStr = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;

常用方法:

1.执行insert/update/delete,返回受影响的行数

 public static int ExecuteNonQuery(string sql,CommandType cmdType,params SqlParameter[] pms)
        {
            using (SqlConnection con=new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    //设置当前执行的是单参数的sql语句还是存储过程
                    cmd.CommandType = cmdType;
                    if (pms!=null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    //打开数据库连接
                    con.Open();
                    int res = cmd.ExecuteNonQuery();
                    con.Close();
                    con.Dispose();
                    //返回受影响行数
                    return res;
                }                
            }            
        }

2.执行语句返回结果集首行首列(单个值)的方法

public static object ExecuteScalar(string sql,CommandType cmdType,params SqlParameter[] pms)
{
    using (SqlConnection con=new SqlConnection(connStr))
    {
        using (SqlCommand cmd=new SqlCommand(sql,con))
        {
            cmd.CommandType = cmdType;
            if (pms!=null)
            {
                cmd.Parameters.AddRange(pms);
            }
            con.Open();
            object obj = cmd.ExecuteScalar();
            con.Close();
            con.Dispose();
            return obj;
        }
    }
}

3.返回sqlDataReader

public static SqlDataReader ExecuteReader(string sql,CommandType cmdType,params SqlParameter[] pms)
{
    SqlConnection con = new SqlConnection(connStr);
    using (SqlCommand cmd = new SqlCommand(sql,con))
    {
        cmd.CommandType = cmdType;
        if (pms!=null)
        {
            cmd.Parameters.AddRange(pms);
        }
        try
        {
            con.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            con.Close();
            con.Dispose();
            throw;
        }
    }

}

4.返回DataTable

public static DataTable ExecuteDataTable(string sql,CommandType cmdType,params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            using (SqlDataAdapter adapter=new SqlDataAdapter(sql,connStr))
            {
                adapter.SelectCommand.CommandType = cmdType;
                if (pms!=null)
                {
                    adapter.SelectCommand.Parameters.AddRange(pms);
                }
                adapter.Fill(dt);
                return dt;
            }
        }