一个简短的SqlHelper 小程序必备

一个简短的SqlHelper 小程序必备

SqlHelper.cs：

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace PROJECT
{
    public class SqlHelper
    {
        public static readonly string ConnStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
        private static SqlCommand cmd = null;
        private static SqlConnection conn = new SqlConnection(ConnStr);
        private static SqlDataReader sdr = null;

        private static SqlConnection Getconn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }
        /// <summary>
        /// 不带参数-增删改
        /// INSERT|DELETE|UPDATE
        /// </summary>
        public static int ExecuteNonQuery(String sqlStr)
        {
            int res;
            cmd = new SqlCommand(sqlStr, Getconn());
            res = cmd.ExecuteNonQuery();
            return res;
        }
        /// <summary>
        /// 带参数-增删改
        /// INSERT|DELETE|UPDATE
        /// </summary>
        public static int ExecuteNonQuery(string sqlStr, SqlParameter[] sqlparas)
        {
            int res;
            cmd = new SqlCommand(sqlStr, Getconn());
            cmd.Parameters.AddRange(sqlparas);
            res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }
        //执行查询，并返回查询所返回的结果集中第一行的第一列。 忽略其他列或行。
        //insert一条数据，返回自增的id值：insert into table([NAME])values ('张三');Select @@Identity;
        public static object ExecuteScalar(string sqlStr, SqlParameter[] sqlparas)
        {
            object res;
            cmd = new SqlCommand(sqlStr, Getconn());
            cmd.Parameters.AddRange(sqlparas);
            res = cmd.ExecuteScalar();
            conn.Close();
            return res;
        }

        /// <summary>
        /// 不带参数-取数据
        /// SELECT
        /// </summary>
        public static DataTable ExecuteReader(string sqlStr)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(sqlStr, Getconn());
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
        /// <summary>
        /// 带参数-取数据
        /// SELECT
        /// </summary>
        public static DataTable ExecuteReader(string sqlStr, SqlParameter[] sqlparas)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(sqlStr, Getconn());
            cmd.Parameters.AddRange(sqlparas);//和不带SqlParameter的方法就差这一行
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
    }
}

app.config:

  <connectionStrings>
    <add name="ConStr" connectionString="server=.\;database=test;uid=sa;pwd=123456"/>   
  </connectionStrings>

使用1：

     string sql = "Select * FROM [T_USER]";
     var dt = SqlHelper.ExecuteNonQuery(sql);

使用2：

           string sql = "Select * FROM [T_USER] WHERE [UserId]=@ID AND [UserName]=@Name";           
           SqlParameter[] paras = new SqlParameter[]{ 
              new SqlParameter("@ID",System.Data.SqlDbType.Int,123),
              new SqlParameter("@Name","张三"), 
           };
           var dt = SqlHelper.ExecuteNonQuery(sql,paras);