C#常用类——DBHelp-CSDN博客

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

namespace TestPrj                 //空间名字 TestPrj

{
     public class DBHelper         //类名 DBHelper
     {

         //获取数据库连接字符串
         private static string connStr = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;

         private static SqlConnection conn;

         public static SqlConnection Conn
         {
             get
             {
                 if (conn == null)
                 {
                     conn = new SqlConnection(connStr);
                     conn.Open();
                 }
                 if (conn != null && conn.State != ConnectionState.Closed)
                 {
                     conn.Close();
                     conn.Open();
                 }
                 else
                 {
                     conn.Open();
                 }
                 return DBHelper.conn;
             }
         }

         /// <summary>
         /// 关闭连接
         /// </summary>

public static void closeConn()
         {
             if (Conn != null && Conn.State != ConnectionState.Closed)
             {
                 Conn.Close();
             }
         }

         /// <summary>
         /// 查询操作
         /// </summary>
         /// <param name="sql">传入以select打头的sql语句或存储过程</param>
         /// <param name="type">命令类型</param>
         /// <param name="pars">数组</param>
         /// <returns></returns>

         public static SqlDataReader getReader(string sql, CommandType type, params SqlParameter[] pars)
         {
             SqlCommand cmd = new SqlCommand(sql, Conn);
             cmd.CommandType = type;
             cmd.Parameters.AddRange(pars);
             return cmd.ExecuteReader();
         }
         public static SqlDataReader getReader(string sql, CommandType type, params SqlParameter[] pars)
         {
             SqlCommand cmd = new SqlCommand(sql, Conn);
             cmd.CommandType = type;
             cmd.Parameters.AddRange(pars);
             return cmd.ExecuteReader();
         }

         /// <summary>
         /// 执行非查询操作(执行增、删、改操作的)，不支持事务
         /// </summary>
         /// <param name="sql">传入以insert|update|delete打头的sql语句或存储过程</param>
         /// <param name="type">命令类型</param>
         /// <param name="pars">数组</param>
         /// <returns></returns>

        public static int ExecuteNoneQuery(string sql, CommandType type, params SqlParameter[] pars)
         {
             int result = 0;

             SqlCommand cmd = new SqlCommand(sql, Conn);
             cmd.CommandType = type;
             if (pars != null)
             {
                 cmd.Parameters.AddRange(pars);
             }

             result = cmd.ExecuteNonQuery();

             //关闭连接对象
             CloseConn();

             return result;
         }
        public static int ExecuteNoneQuery(string sql, CommandType type, params SqlParameter[] pars)
         {
             int result = 0;

             SqlCommand cmd = new SqlCommand(sql, Conn);
             cmd.CommandType = type;
             if (pars != null)
             {
                 cmd.Parameters.AddRange(pars);
             }

             result = cmd.ExecuteNonQuery();

             //关闭连接对象
             CloseConn();

             return result;
         }

         /// <summary>
         /// 非查询操作(执行增、删、改操作的)，支持事务，进行批量操作
         /// </summary>
         /// <param name="sql">传入以insert|update|delete打头的sql语句或存储过程</param>
         /// <param name="conn">数据库连接</param>
         /// <param name="type">命令类型</param>
         /// <param name="pars">数组</param>
         /// <returns></returns>

        public static int ExecuteNoneQuery(string sql, SqlConnection conn, CommandType type, params SqlParameter[] pars)
         {
             int result = 0;

             //接受外部提供的数据连接对象，并且此连接在本次操作后不要关闭
             SqlCommand cmd = new SqlCommand(sql, conn);
             cmd.CommandType = type;
             cmd.Parameters.AddRange(pars);

             result = cmd.ExecuteNonQuery();

             return result;
         }
        public static int ExecuteNoneQuery(string sql, SqlConnection conn, CommandType type, params SqlParameter[] pars)
         {
             int result = 0;

             //接受外部提供的数据连接对象，并且此连接在本次操作后不要关闭
             SqlCommand cmd = new SqlCommand(sql, conn);
             cmd.CommandType = type;
             cmd.Parameters.AddRange(pars);

             result = cmd.ExecuteNonQuery();

             return result;
         }

         /// <summary>
         /// 获取单个值，返回首行首列
         /// </summary>
         /// <param name="sql">传入以select打头的sql语句</param>
         /// <param name="type">命令类型</param>
         /// <param name="pars">数组</param>
         /// <returns></returns>

         public static object GetScalar(string sql, CommandType type, params SqlParameter[] pars)
         {
             object obj = null;

             SqlCommand cmd = new SqlCommand(sql, Conn);
             cmd.CommandType = type;
             cmd.Parameters.AddRange(pars);

             obj = cmd.ExecuteScalar();

             //关闭连接对象
             CloseConn();

             return obj;
         }
     }
}