DBHepler通用类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Schema;
using System.Xml.Serialization;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace DAL
{
    public class DBHelper
    { 
        private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }
       
       
        public static int ExecuteCommand(string safeSql)
        {            
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            cmd.CommandText = safeSql;
            cmd.CommandType = CommandType.StoredProcedure;            
            int result = cmd.ExecuteNonQuery();
            return result;
        }

        
        public static int ExecuteCommand(string safeSql, params SqlParameter[] values)
        {            
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            cmd.CommandText = safeSql;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }

        /// 

        /// 大批量添加数据ds.Tables[0](数据库表名称)
        /// 
        /// 
        /// 
        /// 
   
   
    
    受影响的行数
   
   
        public static int SqlBulkInsert(string sql,DataTable dt)
        {             
            SqlCommand myCommand = new SqlCommand(sql, Connection);
            SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
            SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
            myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
            foreach (DataRow dr in dt.Rows)
            {
                if (dr.RowState != DataRowState.Added)
                    dr.SetAdded();
            }
            return myAdapter.Update(dt);
        }
        
        public static int GetScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public static int GetScalarBySql(string safeSql,params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            cmd.Parameters.AddRange(values);
            cmd.CommandType = CommandType.StoredProcedure;
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        
        public static int GetScalar(String safeSql,params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }


       
        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

       
        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        
        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

       
        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        ///

        /// AspNetPager分页
        ///
        ///已存在连接对象
        ///执行查询语句
        ///当前页码
        ///每页记录条数
        ///输出表名
        ///
   
   
        public static DataSet GetCurrentPage(string sqlStr, int pageIndex, int pageSize, string outtable)
        {
            DataSet dataset = new DataSet();
            SqlCommand cmd = new SqlCommand(sqlStr, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(dataset, pageIndex, pageSize, outtable);
            dataAdapter.Dispose();
            return dataset;
        }

        ///

        /// AspNetPager分页(重载)
        ///
        ///已存在连接对象
        ///执行查询语句
        ///当前页码
        ///每页记录条数
        ///输出表名
        ///
   
   
        public static DataSet GetCurrentPage(string sqlStr, int pageIndex, int pageSize, string outtable, params SqlParameter[] values)
        {
            DataSet dataset = new DataSet();
            SqlCommand cmd = new SqlCommand(sqlStr, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            cmd.Parameters.AddRange(values);
            dataAdapter.Fill(dataset, pageIndex, pageSize, outtable);
            dataAdapter.Dispose();
            return dataset;
        }
        #region 绑定DropDownList控件的数据源
        /// 

        /// 绑定DropDownList控件的数据源
        /// 
        /// ddl为DropDownList控件的ID
        /// 为存储过程
        public static void DataBindDropDownList(DropDownList ddl, string sqlStr)
        {
            DataSet dataset = new DataSet();
            SqlCommand cmd = new SqlCommand(sqlStr, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(dataset);
            ddl.DataSource = dataset;
            ddl.DataBind();
            dataAdapter.Dispose();
        }
        /// 

        /// 绑定DropDownList控件的数据源
        /// 
        /// ddl为DropDownList控件的ID
        /// 为存储过程
        /// 为绑定到该控件的数据表的字段
        public static void DataBindDropDownList(DropDownList ddl, string sqlStr, string dvf)
        {
            DataSet dataset = new DataSet();
            SqlCommand cmd = new SqlCommand(sqlStr, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(dataset);
            ddl.DataSource = dataset;
            ddl.DataValueField = dvf;
            ddl.DataBind();
            dataAdapter.Dispose();
        }
        /// 

        /// 绑定DropDownList控件的数据源
        /// 
        /// ddl为DropDownList控件的ID
        /// 为存储过程
        /// 为绑定到该控件的数据表的字段
        /// /// 为绑定到该控件的数据表的字段
        public static void DataBindDropDownList(DropDownList ddl, string sqlStr, string dtf, string dvf)
        {
            DataSet dataset = new DataSet();
            SqlCommand cmd = new SqlCommand(sqlStr, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(dataset);
            ddl.DataSource = dataset;
            ddl.DataTextField = dtf;
            ddl.DataValueField = dvf;
            ddl.DataBind();
            dataAdapter.Dispose();
        }
        /// 

        /// ListBox控件绑定所有的用户的登录帐号和用户名
        /// 
        /// ddl为ListBox控件的ID
        /// 为存储过程
        /// 为绑定到该控件的数据表的字段
        /// /// 为绑定到该控件的数据表的字段
        public static void DataBindListBox(ListBox lbo, string sqlStr, string dtf, string dvf)
        {
            DataSet dataset = new DataSet();
            SqlCommand cmd = new SqlCommand(sqlStr, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(dataset);
            lbo.DataSource  = dataset;
            lbo.DataTextField = dtf;
            lbo.DataValueField = dvf;
            lbo.DataBind();
            dataAdapter.Dispose();
        }
        /// 

        /// ListBox控件绑定所有的用户的登录帐号和用户名
        /// 
        /// ddl为ListBox控件的ID
        /// 为存储过程
        /// 为绑定到该控件的数据表的字段
        /// /// 为绑定到该控件的数据表的字段
        public static void DataBindListBox(ListBox lbo, string sqlStr, string dtf, string dvf, params SqlParameter[] values)
        {
            DataSet dataset = new DataSet();
            SqlCommand cmd = new SqlCommand(sqlStr, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(values);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(dataset);
            lbo.DataSource = dataset;
            lbo.DataTextField = dtf;
            lbo.DataValueField = dvf;
            lbo.DataBind();
            dataAdapter.Dispose();
        }
        #endregion

        #region 绑定GridView控件的数据源
        /// 

        /// 绑定GridView控件的数据源
        /// 
        /// gv为GridView控件的ID名
        /// 为存储过程
        public static void DataBindGridView(GridView gv, string sqlStr)
        {
            DataSet dataset = new DataSet();
            SqlCommand cmd = new SqlCommand(sqlStr, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(dataset);
            gv.DataSource = dataset;
            gv.DataBind();
            dataAdapter.Dispose();
        }
        /// 

        /// 绑定GridView控件的数据源
        /// 
        /// gv为GridView控件的ID名
        /// 为存储过程
        /// 主键字段属性
        /// 主键字段值
        public static void DataBindGridView(GridView gv, string sqlStr, string DNK)
        {
            DataSet dataset = new DataSet();
            SqlCommand cmd = new SqlCommand(sqlStr, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(dataset);
            gv.DataSource = dataset;
            gv.DataKeyNames = new string[] { DNK };
            gv.DataBind();
            dataAdapter.Dispose();
        }
        #endregion
        /// 

        /// 通过ADO.NET执行FOR XML查询
        /// 将一个表的数据转换为一个XMl文件
        /// 
        /// 
        /// 
   
   
        public static XmlReader GetXmlReader(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            XmlReader reader1 = cmd.ExecuteXmlReader();
            return reader1;
        }
        /// 

        /// 截取字符串
        /// 
        /// 传入将要格式化的字符串
        /// 要截取字符串的实际长度
        /// 
   
   
    
    sNewStr
   
   
        public static string SubString(string strs, int sLength)
        {
            if (strs.Length <= sLength)
            {
                return strs;
            }
            int nStrLength = sLength - 1;
            string sNewStr = strs.Substring(0, sLength);
            sNewStr = sNewStr + "Mores...";
            return sNewStr;
        }
    }
}