net数据连接公共方法，有分页

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

using System.Collections;
using Tscc.Model;

namespace Maticsoft.DBUtility
{
   public class CommonSqlData
    {
        //配置文件连接字符串
       public static string connectionString = PubConstant.ConnectionString;  
       //public static readonly string connectionString = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
       //<add name="SQLConnString" connectionString="Data Source=192.168.1.18;Initial Catalog=TemperatureDB;User ID=sa;Password=tscc" providerName="System.Data.SqlClient"/>
        //连接字符串
        //public static string connectionStr;// = "Data Source=192.168.1.16;Initial Catalog=BJRL;Integrated Security=False;Pooling=False;uid=sa;pwd=tscc";
       //配置文件连接字符串
       //public static readonly string connectionString = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
       public static readonly string connectionStringHeatSale = ConfigurationManager.ConnectionStrings["SQLConnStringHeatSale"].ConnectionString;
       public static readonly string SQLConnStringBJRL = ConfigurationManager.ConnectionStrings["SQLConnStringBJRL"].ConnectionString;
       public CommonSqlData()
       {
       }

        
        //*******************************************************************************************************************************************
        /// <summary>
        /// 预备执行命令
        /// </summary>
        /// <param name="cmd">命令</param>
        /// <param name="conn">连接</param>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">类型</param>
        /// <param name="cmdText">命令语句</param>
        /// <param name="cmdParms">命令参数</param>
        public static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
           
            
             if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            //给命令设置参数
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            //使用事务
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            //命令类型
            cmd.CommandType = cmdType;
            //给命令参数赋值
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }

        //****************************************************************************************************************************************
        /// <summary>
        /// 执行命令，返回SqlDataReader集合
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader( CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            //创建SqlCommand对象
            SqlCommand cmd = new SqlCommand();
            //建立连接
            SqlConnection conn = new SqlConnection(connectionString);
            try
            {
                //预备执行命令
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                //执行命令
                SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                //清空命令参数
                cmd.Parameters.Clear();
                return dataReader;
            }
            catch
            {
                //出现异常关闭连接
                conn.Close();
                throw;
            }
        }

        //****************************************************************************************************************************************
        /// <summary>
        /// 执行命令，返回影响行数
        /// </summary>
        /// <param name="comnnectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery( CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
          
            //创建Sqlcommand对象
            SqlCommand cmd = new SqlCommand();
            //使用using控制连接
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                //预备执行命令
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                //执行命令
                int row = cmd.ExecuteNonQuery();
                //清空命令参数
                cmd.Parameters.Clear();
                return row;
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing sql transaction</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
        //****************************************************************************************************************************************
        /// <summary>
        /// 执行命令，返回一个（结果集中第一行第一列）对象
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static object ExecuteScalar( CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            //创建SqlCommand对象
            SqlCommand cmd = new SqlCommand();
            //使用using控制连接
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                //预备执行命令
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                //执行命令
                object val = cmd.ExecuteScalar();
                //清空命令参数
                cmd.Parameters.Clear();
                return val;
            }
        }

        //****************************************************************************************************************************************
        /// <summary>
        /// 执行命令，返回DataSet
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdparms"></param>
        /// <returns></returns>
        public static DataSet ExecuteSet( CommandType cmdType, string cmdText, params SqlParameter[] cmdparms)
        {
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();
            //ds.Tables.Add();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                //预备执行命令
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdparms);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                //将数据填充到DateSet
                sda.Fill(ds);
                if (ds.Tables.Count == 0)
                {
                    ds.Tables.Add();
                }
                return ds;
            }
        }
        //****************************************************************************************************************************************
        /// <summary>
        /// 执行命令，返回DataTable
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdparms"></param>
        /// <returns></returns>
        public static DataTable ExecuteTable(CommandType cmdType, string cmdText, params SqlParameter[] cmdparms)
        {
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                //预备执行命令
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdparms);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                //将数据填充到DateSet
                sda.Fill(dt);
                return dt;
            }
        }
        //****************************************************************************************************************************************
       /// <summary>
       /// 增删改 方法
       /// </summary>
       /// <param name="sqlStr"></param>
       /// <returns></returns>
        public static bool Execute_Row(string sqlStr,params SqlParameter[] parms)
        {
            bool ret = false;
            try
            {
                int row = CommonSqlData.ExecuteNonQuery(CommandType.Text, sqlStr,parms);
                if (row > 0)
                {
                    ret = true;
                }
            }
            catch (Exception)
            {

                //throw;
            }
            return ret;
        }
        //*****************************************************************************************************************************************
        // Hashtable to store cached parameters
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
        /// <summary>
        /// add parameter array to the cache
        /// </summary>
        /// <param name="cacheKey">Key to the parameter cache</param>
        /// <param name="cmdParms">an array of SqlParamters to be cached</param>
        public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// Retrieve cached parameters
        /// </summary>
        /// <param name="cacheKey">key used to lookup parameters</param>
        /// <returns>Cached SqlParamters array</returns>
        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }
       //*****************************************************************************************************************************************
       /// <summary>
       /// 调用分页
       /// </summary>
       /// <param name="cmdType"></param>
       /// <param name="cmdText"></param>
       /// <param name="parms"></param>
       /// <param name="model"></param>
       /// <param name="count"></param>
       /// <returns></returns>
        public static DataTable GetPageData(CommandType cmdType, string cmdText, SqlParameter[] parms, PageModel model,out int count)
        {
            //string declare = " declare @table1 varchar(4000), @index1 int,  @pageSize1 int, @orderBy1 varchar(100),@ascOrDesc1  int, @strWhere1  varchar(1500),@ReturnCount1 int ";
            Array.Resize<SqlParameter>(ref parms, parms.Length + 6);
            //parms[parms.Length - 7] = new SqlParameter("@table1", SqlDbType.VarChar, 4000);
            //parms[parms.Length - 7].Value = model.Table;
            parms[parms.Length - 6] = new SqlParameter("@index1", SqlDbType.Int, 20);
            parms[parms.Length - 6].Value = model.Index;
            parms[parms.Length - 5] = new SqlParameter("@pageSize1", SqlDbType.Int, 20);
            parms[parms.Length - 5].Value = model.PageSize;
            parms[parms.Length - 4] = new SqlParameter("@orderBy1", SqlDbType.VarChar, 100);
            parms[parms.Length - 4].Value = model.SortField;
            parms[parms.Length - 3] = new SqlParameter("@ascOrDesc1", SqlDbType.Int, 20);
            parms[parms.Length - 3].Value = model.Direction;
            parms[parms.Length - 2] = new SqlParameter("@strWhere1", SqlDbType.VarChar, 500);
            parms[parms.Length - 2].Value = model.WhereStr;
            parms[parms.Length - 1] = new SqlParameter("@ReturnCount1", SqlDbType.Int, 20);
            //parms[parms.Length - 1].Value = stationType;
            parms[parms.Length - 1].Direction = ParameterDirection.Output;
            DataTable dt = CommonSqlData.ExecuteTable(cmdType, cmdText, parms);
            count =Convert.ToInt32( parms[parms.Length - 1].Value);
            return dt;
        }
        /// <summary>
        /// 调用分页
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="page"></param>
        /// <returns></returns>
        public static DataTable GetPageData(string cmdText, PageModel page)
        {
            SqlParameter[] parms = { };
            Array.Resize<SqlParameter>(ref parms, parms.Length + 6);
            parms[parms.Length - 6] = new SqlParameter("@index1", SqlDbType.Int, 20);
            parms[parms.Length - 6].Value = page.Index;
            parms[parms.Length - 5] = new SqlParameter("@pageSize1", SqlDbType.Int, 20);
            parms[parms.Length - 5].Value = page.PageSize;
            parms[parms.Length - 4] = new SqlParameter("@orderBy1", SqlDbType.VarChar, 100);
            parms[parms.Length - 4].Value = page.SortField;
            parms[parms.Length - 3] = new SqlParameter("@ascOrDesc1", SqlDbType.Int, 20);
            parms[parms.Length - 3].Value = page.Direction;
            parms[parms.Length - 2] = new SqlParameter("@strWhere1", SqlDbType.VarChar, 500);
            parms[parms.Length - 2].Value = page.WhereStr;
            parms[parms.Length - 1] = new SqlParameter("@ReturnCount1", SqlDbType.Int, 20);
            //parms[parms.Length - 1].Value = stationType;
            parms[parms.Length - 1].Direction = ParameterDirection.Output;
            string sqlStr = (" exec Z_proc_page @table= '" + cmdText + "'," +
                "@index=@index1,@pageSize=@pageSize1,@orderBy=@orderBy1,@ascOrDesc=@ascOrDesc1,@ReturnCount=@ReturnCount1 output");

            DataTable dt = CommonSqlData.ExecuteTable(CommandType.Text, sqlStr, parms);
            page.ReturnCount = parms[parms.Length - 1].Value.ToString()==""?0: Convert.ToInt32(parms[parms.Length - 1].Value.ToString());
            return dt;
        }
        /// <summary>
        /// 调用分页(存储过程)
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="page"></param>
        /// <returns></returns>
        public static DataTable GetPageDataByProc(string cmdText, PageModel page)
        {
            SqlParameter[] parms = { };
            Array.Resize<SqlParameter>(ref parms, parms.Length + 6);
            parms[parms.Length - 6] = new SqlParameter("@index1", SqlDbType.Int, 20);
            parms[parms.Length - 6].Value = page.Index;
            parms[parms.Length - 5] = new SqlParameter("@pageSize1", SqlDbType.Int, 20);
            parms[parms.Length - 5].Value = page.PageSize;
            parms[parms.Length - 4] = new SqlParameter("@orderBy1", SqlDbType.VarChar, 100);
            parms[parms.Length - 4].Value = page.SortField;
            parms[parms.Length - 3] = new SqlParameter("@ascOrDesc1", SqlDbType.Int, 20);
            parms[parms.Length - 3].Value = page.Direction;
            parms[parms.Length - 2] = new SqlParameter("@strWhere1", SqlDbType.VarChar, 500);
            parms[parms.Length - 2].Value = page.WhereStr;
            parms[parms.Length - 1] = new SqlParameter("@ReturnCount1", SqlDbType.Int, 20);
            //parms[parms.Length - 1].Value = stationType;
            parms[parms.Length - 1].Direction = ParameterDirection.Output;
            cmdText = Convert.ToString(CommonSqlData.ExecuteScalar(CommandType.Text, cmdText));
            string sqlStr = (" exec Z_proc_page1 @table= '" + cmdText + "'," +
                "@index=@index1,@pageSize=@pageSize1,@orderBy=@orderBy1,@ascOrDesc=@ascOrDesc1,@ReturnCount=@ReturnCount1 output");

            DataTable dt = CommonSqlData.ExecuteTable(CommandType.Text, sqlStr, parms);
            page.ReturnCount = parms[parms.Length - 1].Value.ToString() == "" ? 0 : Convert.ToInt32(parms[parms.Length - 1].Value.ToString());
            return dt;
        }
    }
}