数据库访问通用类（SQL server）

 

 

 

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

namespace DBHelper
{
    public class sqlServerDBHelper
    {
        //<connectionStrings>
        //<add name = "connStr" connectionString="server=.;database=TestBase;uid=lyc;pwd=123456" providerName="System.Data.SqlClient"/>
        //</connectionStrings>
        private static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;


        //1、执行增删改查任务，Insert、Delete、Update
        //2、执行返回一个结果的查询
        //3、执行查询，返回sqldatareader
        //4、执行查询，返回dataset
        //5、执行查询，返回datatable

        /// <summary>
        /// 执行T-SQL ，返回受影响的行数  Insert Update Delete
        /// </summary>
        /// <param name="sql">slq语句</param>
        /// <param name="cmdType">sql命令类型，1=sql语句，2=存储过程</param>
        /// <param name="paras">sql参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, int cmdType, params SqlParameter[] paras)
        {
            int count = 0;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure;
                if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras);
                conn.Open();
                count = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
            }
            return count;
        }

        /// <summary>
        /// 执行查询，返回结果集第一行第一列的值，忽略其他行或列 object
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, int cmdType, params SqlParameter[] paras)
        {
            object result = null;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure;
                if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras);
                conn.Open();
                result = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                conn.Close();
            }
            return result;
        }

        /// <summary>
        /// 执行查询，生成SqlDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static SqlDataReader ExecuteReader(string sql, int cmdType, params SqlParameter[] paras)
        {
            SqlDataReader result = null;
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure;
            if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras);
            try
            {
                conn.Open();
                result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
            }
            catch (SqlException ex)
            {
                conn.Close();
                throw new Exception("查询异常", ex);
            }

            return result;
        }

        /// <summary>
        /// 填充DataSet  一个或多个结果集 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql, int cmdType, params SqlParameter[] paras)
        {
            DataSet dt = new DataSet();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                if(cmdType == 2) cmd.CommandType = CommandType.StoredProcedure;
                if(paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                conn.Open();
                adapter.Fill(dt);
                cmd.Parameters.Clear();
                conn.Close();
            }
            return dt;
        }

        public static DataTable GetDataTable(string sql ,int cmdType,params SqlParameter[] paras)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand (sql, conn);
                if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure;
                if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras);
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                cmd.Parameters.Clear();
                conn.Close();
            }
            return dt;
        }
    }
}