总结的C#中SQL操作帮助类

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

using System.Data;
using System.Data.SqlClient;
namespace U8Work1
{
    public class DB
    {
        private static string connectionString;
        public static string ConncetionString
        {
            get { return connectionString; }
            set
            {
                connectionString = value;
            }

        }
        /// <summary>
        /// 默认的构造函数
        /// </summary>
        public DB() { }
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns>返回SqlConnection对象</returns>
        private static SqlConnection CreateConnection()
        {
            string connStr = "";
            if (string.IsNullOrEmpty(connStr))
            {
                try
                {
                    connStr = connectionString;

                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
            }
            if (!string.IsNullOrEmpty(connStr))
                return new SqlConnection(connStr);
            return null;
        }
        /// <summary>
        /// 创建SqlCommand对象
        /// </summary>
        /// <param name="Sqlstring">SQL语句</param>
        /// <returns>返回的SqlCommand</returns>
        private static SqlCommand CreateSqlCommand(string Sqlstring)
        {
            ///创建数据库连接
            SqlConnection conn = CreateConnection();
            //打开数据库连接
            if (conn == null)
                return null;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            //设置SqlCommand参数
            SqlCommand cmd = new SqlCommand(Sqlstring, conn);
            cmd.CommandType = CommandType.Text;
            //添加存储过程参数
           
            return cmd;
        }
       /// <summary>
       /// 返回SqlDataAdapter
       /// </summary>
       /// <param name="Sqlstring">SQL语句</param>
       /// <returns>返回的SqlDataAdapter</returns>
        private static SqlDataAdapter CreateSqlDataAdapter(string Sqlstring)
        {
            //创建数据库连接
            SqlConnection conn = CreateConnection();
            //打开数据库连接
            if (conn == null)
                return null;
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            //设置SqlDataAdapter属性
            SqlDataAdapter da = new SqlDataAdapter(Sqlstring, conn);
            return da;

        }
       
        /// <summary>
        /// 返回查询结果的第一行第一列
        /// </summary>
        /// <param name="Sqlstring">SQL语句</param>
        /// <returns>返回的值</returns>
        public static string RunProcScalar(string Sqlstring)
        {
            SqlCommand cmd = CreateSqlCommand(Sqlstring);
            if (cmd == null)
                return string.Empty;
            string result;
            try
            {
                result = cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();
            }
            return result;
        }
       
       /// <summary>
       ///  返回DataReader
       /// </summary>
       /// <param name="Sqlstring">SQL语句</param>
       /// <param name="dr">返回的DataReader</param>
        public static void RunProcReader(string Sqlstring, out SqlDataReader dr)
        {
            SqlCommand cmd = CreateSqlCommand(Sqlstring);
            if (cmd == null)
            {
                dr = null;
                return;
            }
            try
            {
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                dr = null;
                throw new Exception(ex.Message, ex);
            }
        }
        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="Sqlstring">SQL语句</param>
        /// <param name="ds">返回的DataSet</param>
        public static void RunProcDataSet(string Sqlstring, ref DataSet ds)
        {
            if (ds == null)
                ds = new DataSet();
            SqlDataAdapter da = CreateSqlDataAdapter(Sqlstring);
            if (da == null)
                return;
            try
            {
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                if (da.SelectCommand.Connection.State == ConnectionState.Open)
                    da.SelectCommand.Connection.Close();
            }
        }
       
        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="Sqlstring">SQL语句</param>
        /// <param name="dt">返回的DataTable</param>
        public static void RunProcDataTable(string Sqlstring, ref DataTable dt)
        {
            if (dt == null)
                dt = new DataTable();
            SqlDataAdapter da = CreateSqlDataAdapter(Sqlstring);
            if (da == null)
                return;
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                if (da.SelectCommand.Connection.State == ConnectionState.Open)
                    da.SelectCommand.Connection.Close();
            }
            dt = ds.Tables[0];
        }
      
        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="Sqlstring">SQL语句</param>
        /// <param name="dt"></param>
        /// <param name="start">开始索引</param>
        /// <param name="max">结束索引</param>
        public static void RunProcDataTable(string Sqlstring, ref DataTable dt, int start, int max)
        {
            if (dt == null)
                dt = new DataTable();
            SqlDataAdapter da = CreateSqlDataAdapter(Sqlstring);
            if (da == null)
                return;
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds, start, max, "DataTable");
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                if (da.SelectCommand.Connection.State == ConnectionState.Open)
                    da.SelectCommand.Connection.Close();
            }
            dt = ds.Tables["DataTable"];
        }
       
       /// <summary>
       /// 执行增,删,改操作
       /// </summary>
       /// <param name="Sqlstring">SQL语句</param>
       /// <returns></returns>
        public static int RunProcNonQuery(string Sqlstring)
        {
            SqlCommand cmd = CreateSqlCommand(Sqlstring);
            if (cmd == null)
                return -1;
            int result;
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();
            }
            return result;
        }
      

    }
}