sql通用类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

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

namespace DAL
{
    public class SqlHelper
    {
        //定义字段
        private string connectionString;

        /// <summary>
        /// 设置数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            set { connectionString = value; }
        }

        /// <summary>
        /// 空参的构造函数
        /// </summary>
        public SqlHelper()
        {
            //注意:须在web.config中配置"conn"的数据库连接字符串
            connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public SqlHelper(string connectionString)
        {
            this.connectionString = connectionString;//后面的是构造函数的参数
        }

        /// <summary>
        /// 执行一个查询,并返回结果集
        /// </summary>
        /// <param name="sql">要执行的查询SQL文本命令</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, CommandType.Text, null);
        }

        /// <summary>
        /// 执行一个查询,并返回查询结果
        /// </summary>
        /// <param name="sql">要执行的查询SQL语句</param>
        /// <param name="type">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, CommandType type)
        {
            return ExecuteDataTable(sql, type, null);
        }

        /// <summary>
        /// 执行一个查询,并返回结果集
        /// </summary>
        /// <param name="sql">要执行的查询SQL文本命令</param>
        /// <param name="type">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
        /// <param name="param">Transact-SQL语句或存储过程的参数数组</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, CommandType type, SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = type;
                    if (param != null)
                    {
                        foreach (SqlParameter par in param)
                        {
                            cmd.Parameters.Add(par);
                        }
                    }
                    //通过包含查询SQL的SqlCommand的实例来实例化SqlDataAdapter
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);//填充DataTable
                }
            }
            return dt;
        }

        /// <summary>
        /// 返回一个SqlDataReader对象的实例
        /// </summary>
        /// <param name="sql">要执行的查询SQL语句</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, CommandType.Text, null);
        }

        /// <summary>
        /// 返回一个SqlDataReader对象的实例
        /// </summary>
        /// <param name="sql">要执行的查询SQL语句</param>
        /// <param name="type">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql, CommandType type)
        {
            return ExecuteReader(sql, type, null);
        }

        /// <summary>
        /// 返回一个SqlDataReader对象的实例
        /// </summary>
        /// <param name="sql">要执行的查询SQL文本命令</param>
        /// <param name="type">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
        /// <param name="param">Transact-SQL语句或存储过程的参数数组</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql, CommandType type, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (param != null)
            {
                foreach (SqlParameter par in param)
                {
                    cmd.Parameters.Add(par);
                }
            }
            conn.Open();
            //CommandBehavior.CloseConnection //参数提示关闭Reader对象时关闭与其关联的Connection对象
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行一个查询,返回查询结果集的第一行第一列,忽略其他行和列
        /// </summary>
        /// <param name="sql">要执行的查询SQL语句</param>
        /// <returns></returns>
        public Object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, CommandType.Text, null);
        }

        /// <summary>
        ///  执行一个查询,返回查询结果集的第一行第一列,忽略其他行和列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public Object ExecuteScalar(string sql, CommandType type)
        {
            return ExecuteScalar(sql, type, null);
        }

        /// <summary>
        ///  执行一个查询,返回查询结果集的第一行第一列,忽略其他行和列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public Object ExecuteScalar(string sql, CommandType type, SqlParameter[] param)
        {
            object result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = type;
                    if (param != null)
                    {
                        foreach (SqlParameter par in param)
                        {
                            cmd.Parameters.Add(par);
                        }
                    }
                    conn.Open();
                    result = cmd.ExecuteScalar();
                }
            }
            return result;
        }

        /// <summary>
        /// 对数据库执行增删改操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, CommandType.Text, null);
        }

        /// <summary>
        /// 对数据库执行增删改操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public int ExecteNonQuery(string sql, CommandType type)
        {
            return ExecuteNonQuery(sql, type, null);
        }

        /// <summary>
        /// 对数据库执行增删改操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type">要执行的查询语句的类型,如存储过程或者SQL文本命令</param>
        /// <param name="param">Transact-sql语句或者存储过程的参数数组</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, CommandType type, SqlParameter[] param)
        {
            int count = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandType = type;//设置cmd的CommandType为指定的type
                    if (param != null)
                    {
                        foreach (SqlParameter par in param)
                        {
                            cmd.Parameters.Add(par);
                        }
                    }
                    conn.Open();//打开数据库连接
                    count = cmd.ExecuteNonQuery();
                }
            }
            return count;
        }

        /// <summary>
        /// 返回当前连接的数据库所有由用户创建的数据库
        /// </summary>
        /// <returns></returns>
        public DataTable GetTables()
        {
            DataTable dt = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                dt = conn.GetSchema("Tables");
            }
            return dt;
        }
    }
}