[SQL]通用的分页存储过程

-- ----------------------------------------------------------
--
-- 通用的分页存储过程
--
-- -----------------------------------------------------------
CREATE   PROCEDURE   [ dbo ] . [ PageList ]
(
@tblName       nvarchar ( 200 ),         -- --要显示的表或多个表的连接
@fldName       nvarchar ( 500 )  =   ' * ' ,     -- --要显示的字段列表
@pageSize      int   =   1 ,         -- --每页显示的记录个数
@page          int   =   10 ,         -- --要显示那一页的记录
@fldSort      nvarchar ( 200 )  =   null ,     -- --排序字段列表或条件
@Sort          bit   =   0 ,         -- --排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
@strCondition      nvarchar ( 1000 )  =   null ,     -- --查询条件,不需where,以And开始
@ID          nvarchar ( 150 ),         -- --主表的主键
@Dist                   bit   =   0  ,           -- --是否添加查询字段的 DISTINCT 默认0不添加/1添加
@pageCount      int   =   1  output,             -- --查询结果分页后的总页数
@Counts      int   =   1  output,                 -- --查询到的记录数
@strSql            nvarchar ( 1000 )  =   ''  output   -- ---最后返回的SQL语句
)
AS
SET  NOCOUNT  ON
Declare   @sqlTmp   nvarchar ( 1000 )         -- --存放动态生成的SQL语句
Declare   @strTmp   nvarchar ( 1000 )         -- --存放取得查询结果总数的查询语句
Declare   @strID       nvarchar ( 1000 )         -- --存放取得查询开头或结尾ID的查询语句
Declare   @strSortType   nvarchar ( 10 )     -- --数据排序规则A
Declare   @strFSortType   nvarchar ( 10 )     -- --数据排序规则B
Declare   @SqlSelect   nvarchar ( 50 )          -- --对含有DISTINCT的查询进行SQL构造
Declare   @SqlCounts   nvarchar ( 50 )           -- --对含有DISTINCT的总数查询进行SQL构造
if   @Dist    =   0
begin
     set   @SqlSelect   =   ' select  '
     set   @SqlCounts   =   ' Count(*) '
end
else
begin
     set   @SqlSelect   =   ' select distinct  '
     set   @SqlCounts   =   ' Count(DISTINCT  ' + @ID + ' ) '
end
if   @Sort = 0
begin
     set   @strFSortType = '  ASC  '
     set   @strSortType = '  DESC  '
end
else
begin
     set   @strFSortType = '  DESC  '
     set   @strSortType = '  ASC  '
end
-- ------生成查询语句--------
-- 此处@strTmp为取得查询结果数量的语句
if   @strCondition   is   null   or   @strCondition = ''       -- 没有设置显示条件
begin
     set   @sqlTmp   =    @fldName   +   '  From  '   +   @tblName
     set   @strTmp   =   @SqlSelect + '  @Counts= ' + @SqlCounts + '  FROM  ' + @tblName
     set   @strID   =   '  From  '   +   @tblName
end
else
begin
     set   @sqlTmp   =   +   @fldName   +   ' From  '   +   @tblName   +   '  where (1>0)  '   +   @strCondition
     set   @strTmp   =   @SqlSelect + '  @Counts= ' + @SqlCounts + '  FROM  ' + @tblName   +   '  where (1>0)  '   +   @strCondition
     set   @strID   =   '  From  '   +   @tblName   +   '  where (1>0)  '   +   @strCondition
end
-- --取得查询结果总数量-----
exec  sp_executesql  @strTmp ,N ' @Counts int out  ' , @Counts  out
declare   @tmpCounts   int
if   @Counts   =   0
     set   @tmpCounts   =   1
else
     set   @tmpCounts   =   @Counts
     -- 取得分页总数
     set   @pageCount = ( @tmpCounts + @pageSize - 1 ) / @pageSize
     /**/ /**/ /**/ /**当前页大于总页数 取最后一页**/
     if   @page > @pageCount
         set   @page = @pageCount
     -- /*-----数据分页2分处理-------*/
     declare   @pageIndex   int   -- 总数/页大小
     declare   @lastcount   int   -- 总数%页大小 
     set   @pageIndex   =   @tmpCounts / @pageSize
     set   @lastcount   =   @tmpCounts % @pageSize
     if   @lastcount   >   0
         set   @pageIndex   =   @pageIndex   +   1
     else
         set   @lastcount   =   @pagesize
     -- //***显示分页
     if   @strCondition   is   null   or   @strCondition = ''       -- 没有设置显示条件
     begin
         if   @pageIndex < 2   or   @page <= @pageIndex   /   2   +   @pageIndex   %   2     -- 前半部分数据处理
             begin  
                 set   @strTmp = @SqlSelect + '  top  ' +   CAST ( @pageSize   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName
                         + '  where  ' + @ID + '  not in( ' +   @SqlSelect + '  top  ' +   CAST ( @pageSize * ( @page - 1 )  as   Varchar ( 20 ))  + '   ' +   @ID   + '  from  ' + @tblName
                         + '  order by  ' +   @fldSort   + '   ' +   @strFSortType + ' ) '
                         + '  order by  ' +   @fldSort   + '   ' +   @strFSortType  
             end
         else
             begin
             set   @page   =   @pageIndex - @page + 1   -- 后半部分数据处理
                 if   @page   <=   1   -- 最后一页数据显示
                     set   @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +   CAST ( @lastcount   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName
                         + '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) AS TempTB ' + '  order by  ' +   @fldSort   + '   ' +   @strFSortType  
                 else                 
                     set   @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +   CAST ( @pageSize   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName
                         + '  where  ' + @ID + '  not in( ' +   @SqlSelect + '  top  ' +   CAST ( @pageSize * ( @page - 2 ) + @lastcount   as   Varchar ( 20 ))  + '   ' +   @ID   + '  from  ' + @tblName
                         + '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) '
                         + '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) AS TempTB ' + '  order by  ' +   @fldSort   + '   ' +   @strFSortType  
             end
     end
     else   -- 有查询条件
     begin
         if   @pageIndex < 2   or   @page <= @pageIndex   /   2   +   @pageIndex   %   2     -- 前半部分数据处理
         begin  
                 set   @strTmp = @SqlSelect + '  top  ' +   CAST ( @pageSize   as   VARCHAR ( 4 )) + '   ' +   @fldName   + '  from   ' + @tblName
                     + '  where  ' + @ID + '  not in( ' +   @SqlSelect + '  top  ' +   CAST ( @pageSize * ( @page - 1 )  as   Varchar ( 20 ))  + '   ' +   @ID   + '  from  ' + @tblName
                     + '  Where (1>0)  '   +   @strCondition   +   '  order by  ' +   @fldSort   + '   ' +   @strFSortType + ' ) '
                     + '   '   +   @strCondition   +   '  order by  ' +   @fldSort   + '   ' +   @strFSortType                  
         end
         else
         begin  
             set   @page   =   @pageIndex - @page + 1   -- 后半部分数据处理
             if   @page   <=   1   -- 最后一页数据显示
                     set   @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +   CAST ( @lastcount   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName
                         + '  where (1>0)  ' +   @strCondition   + '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) AS TempTB ' + '  order by  ' +   @fldSort   + '   ' +   @strFSortType
             else
                     set   @strTmp = @SqlSelect + '  * from ( ' + @SqlSelect + '  top  ' +   CAST ( @pageSize   as   VARCHAR ( 4 )) + '   ' +   @fldName + '  from  ' + @tblName
                         + '  where  ' + @ID + '  not in( ' +   @SqlSelect + '  top  ' +   CAST ( @pageSize * ( @page - 2 ) + @lastcount   as   Varchar ( 20 ))  + '   ' +   @ID   + '  from  ' + @tblName
                         + '  where (1>0)  ' +   @strCondition   + '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) '
                         +   @strCondition   + '  order by  ' +   @fldSort   + '   ' +   @strSortType + ' ) AS TempTB ' + '  order by  ' +   @fldSort   + '   ' +   @strFSortType  
         end     
     end
-- ----返回查询结果-----
set   @strSql   =   @strTmp
exec  sp_executesql  @strTmp
-- print @strTmp

 

 数据库基类

 

// ------------------------------------------------------------------------------
//  创建标识: Copyright (C) 2007 Socansoft.com 版权所有
//  创建描述: SocanCode代码生成器自动创建
//
//  功能描述: 数据库操作基类
//
//  修改标识:
//  修改描述:
// ------------------------------------------------------------------------------

using  System;
using  System.Collections.Generic;
using  System.Collections.Specialized;
using  System.Data;
using  System.Text;
using  System.Data.SqlClient;
using  System.Configuration;
using  System.Collections;
using  System.Runtime.Serialization.Formatters.Binary;
using  System.IO;
using  System.Xml;
using  System.Reflection;


namespace  DBUtility
... {
    /**//// <summary>
    /// 数据访问基础类(基于SQLServer)
    /// </summary>
    public abstract class SqlHelper
    ...{
        //数据库连接字符串(web.config来配置)
        public static readonly string LocalSqlServer = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;

        /**//// <summary>
        /// 通用分页存储过程
        /// </summary>
        /// <param name="connectionString">连接</param>
        /// <param name="tblName">要显示的表或多个表的连接</param>
        /// <param name="fldName">要显示的字段列表,可为Null,表示*</param>
        /// <param name="pageSize">每页显示的记录个数</param>
        /// <param name="pageIndex">要显示那一页的记录</param>
        /// <param name="fldSort">排序字段列表或条件</param>
        /// <param name="Sort">排序方法，False为升序，True为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')</param>
        /// <param name="strCondition">查询条件,不需where,以And开始,可为Null,表示""</param>
        /// <param name="ID">主表的主键</param>
        /// <param name="Disk">是否添加查询字段的 DISTINCT 默认False不添加/True添加</param>
        /// <param name="pageCount">查询结果分页后的总页数</param>
        /// <param name="Counts">查询到的记录数</param>
        /// <param name="strSql">最后返回的SQL语句</param>
        /// <returns>查询当前页的数据集</returns>
        public static DataSet PageList(string connectionString, string tblName, string fldName, int pageSize, int pageIndex,
            string fldSort, bool Sort, string strCondition, string ID, bool Dist,
            out int pageCount, out int Counts, out string strSql)
        ...{
            SqlParameter[] parameters =...{ new SqlParameter("@tblName",SqlDbType.NVarChar,200),
                new SqlParameter("@fldName",SqlDbType.NVarChar,500),
                new SqlParameter("@pageSize",SqlDbType.Int),
                new SqlParameter("@page",SqlDbType.Int),
                new SqlParameter("@fldSort",SqlDbType.NVarChar,200),
                new SqlParameter("@Sort",SqlDbType.Bit),
                new SqlParameter("@strCondition",SqlDbType.NVarChar,1000),
                new SqlParameter("@ID",SqlDbType.NVarChar,150),
                new SqlParameter("@Dist",SqlDbType.Bit),
                new SqlParameter("@pageCount",SqlDbType.Int),
                new SqlParameter("@Counts",SqlDbType.Int),
                new SqlParameter("@strSql",SqlDbType.NVarChar,1000)};

            parameters[0].Value = tblName;
            parameters[1].Value = (fldName == null) ? "*" : fldName;
            parameters[2].Value = (pageSize == 0) ? int.Parse(ConfigurationManager.AppSettings["PageSize"]) : pageSize;
            parameters[3].Value = pageIndex;
            parameters[4].Value = fldSort;
            parameters[5].Value = Sort;
            parameters[6].Value = strCondition == null ? "" : strCondition;
            parameters[7].Value = ID;
            parameters[8].Value = Dist;
            parameters[9].Direction = ParameterDirection.Output;
            parameters[10].Direction = ParameterDirection.Output;
            parameters[11].Direction = ParameterDirection.Output;

            DataSet ds = RunProcedure(connectionString, "PageList", parameters, "ds");

            pageCount = (int)parameters[9].Value;
            Counts = (int)parameters[10].Value;
            strSql = parameters[11].Value.ToString();
            return ds;
        }

        执行简单SQL语句#region 执行简单SQL语句
        /**//// <summary>
        /// 获取表某个字段的最大值
        /// </summary>
        /// <param name="FieldName"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static int GetMaxID(string connectionString, string FieldName, string TableName)
        ...{
            string strSql = "select max(" + FieldName + ") from " + TableName;
            DataSet ds = Query(connectionString, strSql);
            if (ds.Tables[0].Rows[0][0] != DBNull.Value)
                return int.Parse(ds.Tables[0].Rows[0][0].ToString());
            else
                return 0;
        }

        /**//// <summary>
        ///  检测一个记录是否存在(SqlParameter语句方式)
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static bool Exists(string connectionString, string strSql, params SqlParameter[] cmdParms)
        ...{
            DataSet ds = Query(connectionString, strSql, cmdParms);
            return int.Parse(ds.Tables[0].Rows[0][0].ToString()) > 0;
        }

        /**//// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string connectionString, string SQLString)
        ...{
            using (SqlConnection connection = new SqlConnection(connectionString))
            ...{
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                ...{
                    try
                    ...{
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    ...{
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }
        /**//// <summary>
        /// 执行SQL语句，返回记录的个数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteCountSql(string connectionString, string SQLString)
        ...{
            using (SqlConnection connection = new SqlConnection(connectionString))
            ...{
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                ...{
                    try
                    ...{
                        connection.Open();
                        SqlDataReader dr = cmd.ExecuteReader();
                        dr.Read();
                        int count = int.Parse(dr[0].ToString());
                        return count;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    ...{
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /**//// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public static void ExecuteSqlTran(string connectionString, List<string> SQLStringList)
        ...{
            using (SqlConnection conn = new SqlConnection(connectionString))
            ...{
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                ...{
                    for (int n = 0; n < SQLStringList.Count; n++)
                    ...{
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        ...{
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                ...{
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

        /**//// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string connectionString, string SQLString, string content)
        ...{
            using (SqlConnection connection = new SqlConnection(connectionString))
            ...{
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                ...{
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                ...{
                    throw new Exception(E.Message);
                }
                finally
                ...{
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /**//// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string connectionString, string strSQL, byte[] fs)
        ...{
            using (SqlConnection connection = new SqlConnection(connectionString))
            ...{
                SqlCommand cmd = new SqlCommand(strSQL, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                ...{
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                ...{
                    throw new Exception(E.Message);
                }
                finally
                ...{
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /**//// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string connectionString, string SQLString)
        ...{
            using (SqlConnection connection = new SqlConnection(connectionString))
            ...{
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                ...{
                    try
                    ...{
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        ...{
                            return null;
                        }
                        else
                        ...{
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    ...{
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /**//// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string connectionString, string strSQL)
        ...{
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            ...{
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            ...{
                throw new Exception(e.Message);
            }
        }

        /**//// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string connectionString, string SQLString)
        ...{
            if (SQLString != null && SQLString.Trim() != "")
            ...{
                using (SqlConnection connection = new SqlConnection(connectionString))
                ...{
                    DataSet ds = new DataSet();
                    try
                    ...{
                        connection.Open();
                        SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                        command.Fill(ds, "ds");
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    ...{
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
            else
            ...{
                return null;
            }
        }

        #endregion 执行简单SQL语句

        执行带参数的SQL语句#region 执行带参数的SQL语句

        /**//// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string connectionString, string SQLString, params SqlParameter[] cmdParms)
        ...{
            using (SqlConnection connection = new SqlConnection(connectionString))
            ...{
                using (SqlCommand cmd = new SqlCommand())
                ...{
                    try
                    ...{
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    ...{
                        throw new Exception(E.Message);
                    }
                }
            }
        }


        /**//// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(string connectionString, Hashtable SQLStringList)
        ...{
            using (SqlConnection conn = new SqlConnection(connectionString))
            ...{
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                ...{
                    SqlCommand cmd = new SqlCommand();
                    try
                    ...{
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        ...{
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            trans.Commit();
                        }
                    }
                    catch
                    ...{
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /**//// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string connectionString, string SQLString, params SqlParameter[] cmdParms)
        ...{
            using (SqlConnection connection = new SqlConnection(connectionString))
            ...{
                using (SqlCommand cmd = new SqlCommand())
                ...{
                    try
                    ...{
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        ...{
                            return null;
                        }
                        else
                        ...{
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    ...{
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /**//// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string connectionString, string SQLString, params SqlParameter[] cmdParms)
        ...{
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            ...{
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            ...{
                throw new Exception(e.Message);
            }

        }

        /**//// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string connectionString, string SQLString, params SqlParameter[] cmdParms)
        ...{
            using (SqlConnection connection = new SqlConnection(connectionString))
            ...{
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                ...{
                    DataSet ds = new DataSet();
                    try
                    ...{
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    ...{
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, 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 = CommandType.Text;//cmdType;
            if (cmdParms != null)
            ...{
                foreach (SqlParameter parm in cmdParms)
                ...{
                    if (parm.SqlDbType == SqlDbType.DateTime)
                    ...{
                        if ((DateTime)parm.Value == DateTime.MinValue)
                            parm.Value = System.DBNull.Value;
                    }
                    cmd.Parameters.Add(parm);
                }
            }
        }

        #endregion 执行带参数的SQL语句

        存储过程操作#region 存储过程操作

        /**//// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string connectionString, string storedProcName, IDataParameter[] parameters)
        ...{
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader();
            return returnReader;
        }

        /**//// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string connectionString, string storedProcName, IDataParameter[] parameters, string tableName)
        ...{
            using (SqlConnection connection = new SqlConnection(connectionString))
            ...{
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        /**//// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        ...{
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            ...{
                foreach (SqlParameter parameter in parameters)
                ...{
                    if (parameter.SqlDbType == SqlDbType.DateTime)
                    ...{
                        if ((DateTime)parameter.Value == DateTime.MinValue)
                            parameter.Value = System.DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        /**//// <summary>
        /// 执行存储过程，返回影响的行数        
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string connectionString, string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        ...{
            using (SqlConnection connection = new SqlConnection(connectionString))
            ...{
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                connection.Close();
                return result;
            }
        }

        /**//// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)    
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        ...{
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        #endregion 存储过程操作

        构造语句常用类#region 构造语句常用类
        /**//// <summary>
        /// Make input param.
        /// </summary>
        /// <param name="ParamName">Name of param.</param>
        /// <param name="DbType">Param type.</param>
        /// <param name="Size">Param size.</param>
        /// <param name="Value">Param value.</param>
        /// <returns>New parameter.</returns>
        public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        ...{
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }
        public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, object Value)
        ...{
            return MakeParam(ParamName, DbType, 0, ParameterDirection.Input, Value);
        }

        /**//// <summary>
        /// Make input param.
        /// </summary>
        /// <param name="ParamName">Name of param.</param>
        /// <param name="DbType">Param type.</param>
        /// <param name="Size">Param size.</param>
        /// <returns>New parameter.</returns>
        public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
        ...{
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }

        /**//// <summary>
        /// Make stored procedure param.
        /// </summary>
        /// <param name="ParamName">Name of param.</param>
        /// <param name="DbType">Param type.</param>
        /// <param name="Size">Param size.</param>
        /// <param name="Direction">Parm direction.</param>
        /// <param name="Value">Param value.</param>
        /// <returns>New parameter.</returns>
        public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        ...{
            SqlParameter param;

            if (Size > 0)
                param = new SqlParameter(ParamName, DbType, Size);
            else
                param = new SqlParameter(ParamName, DbType);

            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;

            return param;
        }
        #endregion 构造语句常用类

        由Object取值#region 由Object取值
        /**//// <summary>
        /// 取得Int值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static int GetInt(object obj)
        ...{
            if (obj.ToString() != "")
                return int.Parse(obj.ToString());
            else
                return 0;
        }

        /**//// <summary>
        /// 获得Long值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static long GetLong(object obj)
        ...{
            if (obj.ToString() != "")
                return long.Parse(obj.ToString());
            else
                return 0;
        }

        /**//// <summary>
        /// 取得Decimal值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static decimal GetDecimal(object obj)
        ...{
            if (obj.ToString() != "")
                return decimal.Parse(obj.ToString());
            else
                return 0;
        }

        /**//// <summary>
        /// 取得Guid值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static Guid GetGuid(object obj)
        ...{
            if (obj.ToString() != "")
                return new Guid(obj.ToString());
            else
                return Guid.Empty;
        }

        /**//// <summary>
        /// 取得DateTime值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static DateTime GetDateTime(object obj)
        ...{
            if (obj.ToString() != "")
                return DateTime.Parse(obj.ToString());
            else
                return DateTime.MinValue;
        }

        /**//// <summary>
        /// 取得bool值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static bool GetBool(object obj)
        ...{
            if (obj.ToString() == "1" || obj.ToString().ToLower() == "true")
                return true;
            else
                return false;
        }

        /**//// <summary>
        /// 取得byte[]
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static Byte[] GetByte(object obj)
        ...{
            if (obj.ToString() != "")
            ...{
                return (Byte[])obj;
            }
            else
                return null;
        }

        /**//// <summary>
        /// 取得string值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string GetString(object obj)
        ...{
            return obj.ToString();
        }
        #endregion

        序列化与反序列化#region 序列化与反序列化
        /**//// <summary>
        /// 序列化对象
        /// </summary>
        /// <param name="obj">要序列化的对象</param>
        /// <returns>返回二进制</returns>
        public static byte[] SerializeModel(Object obj)
        ...{
            if (obj != null)
            ...{
                BinaryFormatter binaryFormatter = new BinaryFormatter();
                MemoryStream ms = new MemoryStream();
                byte[] b;
                binaryFormatter.Serialize(ms, obj);
                ms.Position = 0;
                b = new Byte[ms.Length];
                ms.Read(b, 0, b.Length);
                ms.Close();
                return b;
            }
            else
                return new byte[0];
        }

        /**//// <summary>
        /// 反序列化对象
        /// </summary>
        /// <param name="b">要反序列化的二进制</param>
        /// <returns>返回对象</returns>
        public static object DeserializeModel(byte[] b, object SampleModel)
        ...{
            if (b == null || b.Length == 0)
                return SampleModel;
            else
            ...{
                object result = new object();
                BinaryFormatter binaryFormatter = new BinaryFormatter();
                MemoryStream ms = new MemoryStream();
                try
                ...{
                    ms.Write(b, 0, b.Length);
                    ms.Position = 0;
                    result = binaryFormatter.Deserialize(ms);
                    ms.Close();
                }
                catch ...{ }
                return result;
            }
        }
        #endregion

        Model与XML互相转换#region Model与XML互相转换
        /**//// <summary>
        /// Model转化为XML的方法
        /// </summary>
        /// <param name="model">要转化的Model</param>
        /// <returns></returns>
        public static string ModelToXML(object model)
        ...{
            XmlDocument xmldoc = new XmlDocument();
            XmlElement ModelNode = xmldoc.CreateElement("Model");
            xmldoc.AppendChild(ModelNode);

            if (model != null)
            ...{
                foreach (PropertyInfo property in model.GetType().GetProperties())
                ...{
                    XmlElement attribute = xmldoc.CreateElement(property.Name);
                    if (property.GetValue(model, null) != null)
                        attribute.InnerText = property.GetValue(model, null).ToString();
                    else
                        attribute.InnerText = "[Null]";
                    ModelNode.AppendChild(attribute);
                }
            }

            return xmldoc.OuterXml;
        }

        /**//// <summary>
        /// XML转化为Model的方法
        /// </summary>
        /// <param name="xml">要转化的XML</param>
        /// <param name="SampleModel">Model的实体示例，New一个出来即可</param>
        /// <returns></returns>
        public static object XMLToModel(string xml, object SampleModel)
        ...{
            if (string.IsNullOrEmpty(xml))
                return SampleModel;
            else
            ...{
                XmlDocument xmldoc = new XmlDocument();
                xmldoc.LoadXml(xml);

                XmlNodeList attributes = xmldoc.SelectSingleNode("Model").ChildNodes;
                foreach (XmlNode node in attributes)
                ...{
                    foreach (PropertyInfo property in SampleModel.GetType().GetProperties())
                    ...{
                        if (node.Name == property.Name)
                        ...{
                            if (node.InnerText != "[Null]")
                            ...{
                                if (property.PropertyType == typeof(System.Guid))
                                    property.SetValue(SampleModel, new Guid(node.InnerText), null);
                                else
                                    property.SetValue(SampleModel, Convert.ChangeType(node.InnerText, property.PropertyType), null);
                            }
                            else
                                property.SetValue(SampleModel, null, null);
                        }
                    }
                }
                return SampleModel;
            }
        }
        #endregion
    }
}