SQLHelper

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

namespace Com.LibLayer.Helper
{
    /// <summary>
    /// 数据 帮助类
    /// </summary>
    publicclass SqlHelper
    {
        #region 单例访问

        /// <summary>
        /// 单例访问器
        /// </summary>
        privatestatic SqlHelper _instance;
        publicstatic SqlHelper Instance
        {
            get
            {
                if (_instance== null)
                {
                    _instance =new SqlHelper();
                }
                return _instance;
            }
        }
        privatevoid hhh()
        {
        }
        #endregion

        #region 字段属性

        //private static string _conStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        privatestatic string _conStr = System.Configuration.ConfigurationManager.AppSettings["ConnString"].ToString();
        #endregion

        #region 公共方法

        /// <summary>
        /// 执行并返回分页数据（DataTable）
        /// </summary>
        /// <param name="args"></param>
        /// <param name="totalPageCount"></param>
        /// <param name="totalRecordCount"></param>
        /// <returns></returns>
        public DataTable ExecutePagedDataTable(ListSearchArgs args,out int totalPageCount,out int totalRecordCount)
        {
            SqlConnection sqlConn =new SqlConnection(_conStr);
            if (sqlConn.State== ConnectionState.Closed)
            {
                sqlConn.Open();
            }
            try
            {
                //创建一个新的 OracleCommand 对象
                SqlCommand cmd =new SqlCommand();

                cmd.Connection = sqlConn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText ="QueryPagedData";
                cmd.Parameters.Clear();
                cmd.Parameters.Add(new SqlParameter("@TableName", SqlDbType.VarChar, 200, ParameterDirection.Input,true, 0, 0,string.Empty, DataRowVersion.Default, args.TableName));
                cmd.Parameters.Add(new SqlParameter("@TableColumns", SqlDbType.VarChar, 500, ParameterDirection.Input,true, 0, 0,string.Empty, DataRowVersion.Default, args.TableColumns));
                cmd.Parameters.Add(new SqlParameter("@SqlCondition", SqlDbType.VarChar, 1000, ParameterDirection.Input,true, 0, 0,string.Empty, DataRowVersion.Default, args.SqlCondition));
                cmd.Parameters.Add(new SqlParameter("@SqlSort", SqlDbType.VarChar, 500, ParameterDirection.Input,true, 0, 0,string.Empty, DataRowVersion.Default, args.SqlSort));
                cmd.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int, 4, ParameterDirection.Input,true, 0, 0,string.Empty, DataRowVersion.Default, args.PageIndex));
                cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int, 4, ParameterDirection.Input,true, 0, 0,string.Empty, DataRowVersion.Default, args.PageSize));

                cmd.Parameters.Add(new SqlParameter("@TotalPageCount", SqlDbType.Int, 8, ParameterDirection.Output,true, 0, 0,string.Empty, DataRowVersion.Default,null));
                cmd.Parameters.Add(new SqlParameter("@TotalRecordCount", SqlDbType.Int, 8, ParameterDirection.Output,true, 0, 0,string.Empty, DataRowVersion.Default,null));
                //创建一个新的数据适配器
                SqlDataAdapter custDA =new SqlDataAdapter();
                custDA.SelectCommand = cmd;
                DataTable dt =new DataTable();
                custDA.Fill(dt);
                totalPageCount = Convert.ToInt32(cmd.Parameters[6].Value);
                totalRecordCount = Convert.ToInt32(cmd.Parameters[7].Value);
                return dt;
            }
            catch (Exception e1)
            {
                thrownew Exception("获取分页DataTable时发生异常:"+ e1.Message);
            }
            finally
            {
                if (sqlConn.State== ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
        }

        /// <summary>
        /// 执行并返回全部数据（DataTable）
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(CommandType cmdType,string cmdText, IDataParameter[] cmdParms)
        {
            SqlConnection sqlConn =new SqlConnection(_conStr);
            if (sqlConn.State== ConnectionState.Closed)
            {
                sqlConn.Open();
            }
            try
            {
                //创建一个新的数据适配器
                SqlDataAdapter custDA =new SqlDataAdapter();

                //创建一个新的 OracleCommand 对象
                SqlCommand cmd =new SqlCommand();
                cmd.Connection = sqlConn;
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                if (cmdParms!= null)
                {
                    foreach (SqlParameter parmin cmdParms)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
                custDA.SelectCommand = cmd;
                DataTable dt =new DataTable();
                custDA.Fill(dt);

                cmd.Parameters.Clear();
                return dt;
            }
            catch (Exception e1)
            {
                thrownew Exception("GetDataTable时发生异常:Sql="+ cmdText + e1.Message);
            }
            finally
            {
                if (sqlConn.State== ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }

        }

        /// <summary>
        /// 执行并返回受影响数据的条数（int）
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns>受影响的行数</returns>
        publicint ExecuteNonQuery(CommandType cmdType,string cmdText, IDataParameter[] cmdParms)
        {
            SqlConnection sqlConn =new SqlConnection(_conStr);
            if (sqlConn.State== ConnectionState.Closed)
            {
                sqlConn.Open();
            }
            try
            {
                SqlCommand cmd =new SqlCommand();
                cmd.Connection = sqlConn;
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;

                if (cmdParms!= null)
                {
                    foreach (SqlParameter parmin cmdParms)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }

                int val= cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
            catch (Exception ex)
            {
                thrownew Exception("ExecuteNonQuery时发生异常:Sql="+ cmdText + ex.Message);
            }
            finally
            {
                if (sqlConn.State== ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
        }

        /// <summary>
        /// 执行并返回查询的第一行第一列数据（object）
        /// 一般用于count，max等
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns>受影响的行数</returns>
        publicobject ExecuteScalar(CommandType cmdType,string cmdText, IDataParameter[] cmdParms)
        {
            SqlConnection sqlConn =new SqlConnection(_conStr);
            if (sqlConn.State== ConnectionState.Closed)
            {
                sqlConn.Open();
            }
            try
            {
                SqlCommand cmd =new SqlCommand();
                cmd.Connection = sqlConn;
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;

                if (cmdParms!= null)
                {
                    foreach (SqlParameter parmin cmdParms)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }

                object val= cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
            catch (Exception ex)
            {
                thrownew Exception("ExecuteNonQuery时发生异常:Sql="+ cmdText + ex.Message);
            }
            finally
            {
                if (sqlConn.State== ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
        }

        #endregion
    }

    /// <summary>
    /// 通过级联操作数据类
    /// </summary>
    publicclass Query
    {
        #region 字段属性

        // 查询字段
        privatestring _selectFileds= "*";
        // 是否启用分页
        privatebool _isPaged= false;
        // 查询当前页
        privateint _pageIndex= 0;
        // 查询每页记录数
        privateint _pageSize= 10;
        // 查询排序
        privatestring _order= string.Empty;
        // 查询分组
        privatestring _group= string.Empty;
        // 插入或更新值
        Dictionary<string,object> _values;
        // 条件
        List<string> _wheres;

        /// <summary>
        /// 查询表名
        /// </summary>
        privatestring _tableName= string.Empty;
        publicstring TableName
        {
            get {return this._tableName; }
            set {this._tableName= value; }
        }
        /// <summary>
        /// 操作类型
        /// </summary>
        private OperType _otype= OperType.UnKnown;
        public OperType Otype
        {
            get {return this._otype; }
            set {this._otype = value; }
        }

        #endregion

        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        public Query()
        {
            this._wheres= new List<string>();
            this._values= new Dictionary<string,object>();
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="otype">操作类型</param>
        public Query(OperType otype)
        {
            this.Otype= otype;
            this._wheres= new List<string>();
            this._values= new Dictionary<string,object>();
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="otype">操作类型</param>
        /// <param name="tableNames">数据表名</param>
        public Query(OperType otype,string tableNames)
        {
            this.Otype= otype;
            this.TableName= tableNames;
            this._wheres= new List<string>();
            this._values= new Dictionary<string,object>();
        }

        #endregion

        # region 级联方法

        /// <summary>
        /// 获取 sql语句的查询表名
        /// </summary>
        /// <param name="tableNames">查询的表名集合</param>
        /// <returns></returns>
        public Query SetTables(string tableNames)
        {
            this.TableName= tableNames;
            returnthis;
        }
        /// <summary>
        /// 设置 sql语句Where条件
        /// </summary>
        /// <param name="expression">条件表达式</param>
        /// <returns></returns>
        public Query SetWheres(string format,params object[] args)
        {
            string expression= string.Format(format, args);
            if (!string.IsNullOrEmpty(expression))
            {
                this._wheres.Add(expression);
            }
            returnthis;
        }
        /// <summary>
        /// 添加 sql语句Where条件
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public Query Add(string format,params object[] args)
        {
            string expression= string.Format(format, args);
            if (!string.IsNullOrEmpty(expression))
            {
                this._wheres.Add(expression);
            }
            returnthis;
        }
        /// <summary>
        /// 设置 InSert|Update语句的字段
        /// </summary>
        /// <param name="field">数据字段</param>
        /// <param name="value">待插入的值</param>
        /// <returns></returns>
        public Query SetValues(string field,object value)
        {
            this._values.Add(field, value);
            returnthis;
        }
        /// <summary>
        /// 添加 InSert|Update语句的字段
        /// </summary>
        /// <param name="field"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public Query Add(string field,object value)
        {
            this._values.Add(field, value);
            returnthis;
        }
        /// <summary>
        /// 获取 Select语句的查询字段
        /// </summary>
        /// <param name="expression">选择字段</param>
        /// <returns></returns>
        public Query SetFileds(string expression)
        {
            this._selectFileds= expression;
            returnthis;
        }
        /// <summary>
        /// 添加 Select语句排序
        /// </summary>
        /// <param name="expression">排序表达式</param>
        /// <returns></returns>
        public Query SetOrderBy(string expression)
        {
            this._order= expression;
            returnthis;
        }
        /// <summary>
        /// 添加 Select语句分组
        /// </summary>
        /// <param name="expression">分组表达式</param>
        /// <returns></returns>
        public Query SetGroupBy(string expression)
        {
            this._group= expression;
            returnthis;
        }
        /// <summary>
        /// 添加 Select语句分页设置
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public Query SetPaged(int pageIndex,int pageSize)
        {
            this._pageIndex= pageIndex;
            this._pageSize= pageSize;
            this._isPaged= true;
            returnthis;
        }

        /// <summary>
        /// 判断参数的类型
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        privatestring GetVaule(object value)
        {
            switch (value.GetType().Name)
            {
                case"Int32":
                    return value.ToString();
                case"Boolean":
                    return (bool)value? "1" :"0";
                default:
                    returnstring.Format("'{0}'", value.ToString().Replace("'",""));
            }
        }

        /// <summary>
        /// 输出
        /// </summary>
        /// <returns></returns>
        publicoverride string ToString()
        {
            string output= string.Empty;
            switch (this.Otype)
            {
                case OperType.InSert:
                    {
                        #region InSert（插入）

                        StringBuilder stb =new StringBuilder();
                        stb.AppendFormat("insert into {0} (",this.TableName);
                        foreach (var vkeyin this._values.Keys)
                        {
                            stb.AppendFormat("{0},", vkey);
                        }
                        stb.Append(") values (");
                        foreach (object valin this._values.Values)
                        {
                            stb.AppendFormat("{0},",this.GetVaule(val));
                        }
                        stb.Append(")");
                        output = stb.ToString().Replace(",)",")");

                        #endregion
                        break;
                    }
                case OperType.Update:
                    {
                        #region Update（更新）

                        StringBuilder stb =new StringBuilder();
                        stb.AppendFormat("update {0} set", this.TableName);

                        foreach (var valin this._values.Keys)
                        {
                            stb.AppendFormat(" {0}={1},", val,this.GetVaule(this._values[val]));
                        }
                        stb.Append("where 1=1");
                        foreach (string whin this._wheres)
                        {
                            stb.AppendFormat(" {0}", wh);
                        }
                        output = stb.ToString().Replace(",where"," where");

                        #endregion
                        break;
                    }
                case OperType.Delete:
                    {
                        #region Delete（删除）

                        StringBuilder stb =new StringBuilder();
                        stb.AppendFormat("delete {0} where 1=1", this.TableName);

                        foreach (string whin this._wheres)
                        {
                            stb.AppendFormat(" {0}", wh);
                        }
                        output = stb.ToString();

                        #endregion
                        break;
                    }
                case OperType.Select:
                    {
                        if (!this._isPaged)
                        {
                            #region Select（一般的sql语句）

                            StringBuilder stb =new StringBuilder();
                            stb.AppendFormat("select {0} from {1} where 1=1", this._selectFileds,this.TableName);
                            foreach (string whin this._wheres)
                            {
                                stb.AppendFormat(" {0}", wh);
                            }
                            stb.AppendFormat(" {0}",string.IsNullOrEmpty(this._group)? "" :" group by " +this._group);
                            stb.AppendFormat(" {0}",string.IsNullOrEmpty(this._order)? "" :" order by " +this._order);
                            output = stb.ToString();
                            #endregion
                        }
                        else
                        {
                            output ="调用存储过程";
                        }
                        break;
                    }
                default:
                    {
                        thrownew Exception("当前操作出错！");
                    }
            }
            return output;
        }

        /// <summary>
        /// 执行并返回分页数据（DataTable）
        /// </summary>
        /// <returns></returns>
        public DataTable ExecutePagedDataTable(outint pageCount,out int recordCount)
        {
            #region Select（调用存储过程）

            StringBuilder stb =new StringBuilder();
            foreach (string whin this._wheres)
            {
                stb.AppendFormat(" {0}", wh);
            }
            stb.AppendFormat(" {0}",string.IsNullOrEmpty(this._group)? "" :" group by " +this._group);
            ListSearchArgs args =new ListSearchArgs();
            args.TableName =this.TableName;
            args.TableColumns =this._selectFileds;
            args.SqlCondition = stb.ToString();
            args.SqlSort =this._order;
            args.PageIndex =this._pageIndex;
            args.PageSize =this._pageSize;

            #endregion

            int pc= 1;
            int rc= 0;
            DataTable dt = SqlHelper.Instance.ExecutePagedDataTable(args,out pc, out rc);
            pageCount = pc;
            recordCount = rc;
            return dt;
        }

        /// <summary>
        ///  执行并返回全部数据（DataTable）
        /// </summary>
        /// <returns></returns>
        public DataTable ExecuteDataTable()
        {
            return SqlHelper.Instance.ExecuteDataTable(CommandType.Text,this.ToString(),null);
        }

        /// <summary>
        /// 执行并返回受影响数据的条数（int）
        /// </summary>
        /// <returns></returns>
        publicint ExecuteNonQuery()
        {
            return SqlHelper.Instance.ExecuteNonQuery(CommandType.Text,this.ToString(),null);
        }

        /// <summary>
        /// 执行并返回查询的第一行第一列数据（object）
        /// </summary>
        /// <returns></returns>
        publicobject ExecuteScalar()
        {
            return SqlHelper.Instance.ExecuteScalar(CommandType.Text,this.ToString(),null);
        }

        #endregion
    }

    /// <summary>
    /// 判断当前连接执行如何操作
    /// </summary>
    publicenum OperType
    {
        UnKnown,
        InSert,
        Update,
        Delete,
        Select
    }

    /// <summary>
    /// 分页查询 参数类
    /// </summary>
    publicclass ListSearchArgs
    {
        //待查询表名
        privatestring _tableName;
        publicstring TableName
        {
            get {return this._tableName; }
            set {this._tableName= value; }
        }

        //待显示字段
        privatestring _tableColumns= "*";
        publicstring TableColumns
        {
            get {return this._tableColumns; }
            set {this._tableColumns= value; }
        }

        //查询条件;不需where
        privatestring _sqlCondition= null;
        publicstring SqlCondition
        {
            get {return this._sqlCondition; }
            set {this._sqlCondition= value; }
        }

        //排序字段，不需order by
        privatestring _sqlSort= null;
        publicstring SqlSort
        {
            get {return this._sqlSort; }
            set {this._sqlSort= value; }
        }

        //当前页
        privateint _pageIndex;
        publicint PageIndex
        {
            get {return this._pageIndex; }
            set {this._pageIndex= value; }
        }

        //每页显示的记录数
        privateint _pageSize;
        publicint PageSize
        {
            get {return this._pageSize; }
            set {this._pageSize= value; }
        }
    }

#region 存储过程

    /*
     * ********分页功能对应的存储过程*********
     *
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        jbzhang
    -- Create date: 2009年09月04日
    -- Description:    采用Row_number()给数据加上索引，专为SQL server 2005及以上使用
    -- =============================================
    create PROCEDURE QueryPagedData
    (
        @TableName VARCHAR(200),              ----待查询表名
        @TableColumns nvarchar(500) = '*',    ----待显示字段
        @SqlCondition nvarchar(1000) = null,  ----查询条件,不需where
        @SqlSort nvarchar(500) = null,        ----排序字段，不需order by
        @PageIndex int,                       ----当前页
        @PageSize int,                        ----每页显示的记录数
        @TotalRecordCount int = 1 OUTPUT,      ----返回总记录数
        @TotalPageCount int = 1 OUTPUT        ----返回总页数
    )
    AS
    BEGIN
        SET NOCOUNT ON;
        Declare @sqlCountTmp nvarchar(1000)   ----sql语句，查询数据总量
        Declare @sqlDataTmp nvarchar(1000)    ----sql语句，查询分页数据 

        ----排序字段是否存在
        if @SqlSort is null or @SqlSort=''
            set @SqlSort = ' ObjectId ASC '

        ----查询条件是否存在
        if @SqlCondition is null or @SqlCondition=''
            set @SqlCondition = ' '
        else
            set @SqlCondition = ' where (1=1) ' + @SqlCondition

        ----获取记录总数
        set @sqlCountTmp = ' select @TotalRecordCount=Count(*) FROM '+@TableName + @SqlCondition
        exec sp_executesql @sqlCountTmp,N'@TotalRecordCount int out ',@TotalRecordCount out

        ----获取分页总数
        declare @tmpCounts int
        if @TotalRecordCount = 0
            set @tmpCounts = 1
        else
            set @tmpCounts = @TotalRecordCount
        set @TotalPageCount=(@tmpCounts+@PageSize-1)/@PageSize
        if @PageIndex>@TotalPageCount 
            set @PageIndex=@TotalPageCount

        ----返回数据查询
        set @sqlDataTmp=' select '+@TableColumns+ ' from (select *,Row_number() over(order by '+@SqlSort+') as sqlRowIndex from '+ @TableName + @SqlCondition+') as TableWithRowIndex where sqlRowIndex>'+ cast(@PageSize*@PageIndex as Varchar(20))+' and sqlRowIndex<'+ cast((@PageSize*(@PageIndex+1)+1) as Varchar(20))
        exec sp_executesql @sqlDataTmp
    END
    GO
     */

    #endregion
}