数据库助手（供ORM上层访问）

//--------------------------------------------------------------------------------
// 文件描述：数据库操作助手
// 文件作者：品浩
// 创建日期：2013/5/23
// 修改记录：上层web应用尽量参考查找类似http上下文 HttpContext.Current.Items["类对象"]  创造线程上下文来辅助加速系统，如线程内存槽
//--------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace lph.FrameWork
{

    /// <summary>
    /// 1.对于需要在页面直接传sql执行的，请自行过滤容易引起攻击的字符
    /// 2.此类是系统常用操作方法，能满足大部分需要了
    /// 3.不提倡使用存储过程，数据库尽量只做仓库的作用
    /// </summary>
    public abstract class DBHelper
    {
        //============================================================================================================================

        #region 判断存在

        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public static bool ColumnExists(string tableName, string columnName)
        {
            string sql = "select count(1) from syscolumns where [id]=object_id(‘" + tableName + "‘) and [name]=‘" + columnName + "‘";
            object res = ExecuteScalar(sql);
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }


        /// <summary>
        /// 传sql语句检测记录是否存在,如select count(*)
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static bool Exists(string strSql)
        {
            object obj = ExecuteScalar(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 查看表是否存在于数据库
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static bool ExistsTable(string TableName)
        {
            string strsql = "select count(*) from sysobjects where id = object_id(N‘[" + TableName + "]‘) and OBJECTPROPERTY(id, N‘IsUserTable‘) = 1";
            object obj = ExecuteScalar(strsql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        #endregion


        #region sql语句操作

        /// <summary>
        /// 执行SQL语句,根据受影响行数返回是否成功，用于增、删、改
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns>操作成功返回true，否则返回false</returns>
        public static bool ExecuteSql(string strSql)
        {
            SqlConnection connection = SqlConnectionPool.GetInstance();
            bool result = false;
            using (SqlCommand cmd = new SqlCommand(strSql, connection))
            {
                cmd.CommandTimeout = 100;
                if (cmd.ExecuteNonQuery().ToBoolean())
                    result = true;
            }
            SqlConnectionPool.PutInstance(connection);
            connection = null;
            return result;
        }
        public static bool ExecuteSql(string strSql, Parameters parms)
        {
            SqlConnection connection = SqlConnectionPool.GetInstance();
            bool result = false;
            using (SqlCommand cmd = new SqlCommand(strSql, connection))
            {
                foreach (SqlParameter parameter in parms.Entries)
                {
                    if (parameter != null)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(parameter);
                    }
                }
                cmd.CommandTimeout = 100;
                if (cmd.ExecuteNonQuery().ToBoolean())
                    result = true;
            }
            SqlConnectionPool.PutInstance(connection);
            connection = null;
            return result;
        }

        /// <summary>
        /// 执行SQL语句，从数据库中检索单个值
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns>返回结果（object）</returns>
        public static object ExecuteScalar(string strSql)
        {
            SqlConnection connection = SqlConnectionPool.GetInstance();
            object obj;
            using (SqlCommand cmd = new SqlCommand(strSql, connection))
            {
                cmd.CommandTimeout = 100;
                obj = cmd.ExecuteScalar();
                if (Object.Equals(obj, null) || Object.Equals(obj, System.DBNull.Value))
                {
                    obj = null;
                }
            }
            SqlConnectionPool.PutInstance(connection);
            connection = null;
            return obj;
        }

        /// <summary>
        /// 执行Sql语句，返回输出参数的值
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static object ExecuteOutput(string strSql, Parameters parms)
        {
            SqlConnection connection = SqlConnectionPool.GetInstance();
            object obj;
            using (SqlCommand cmd = new SqlCommand(strSql, connection))
            {
                foreach (SqlParameter parameter in parms.Entries)
                {
                    if (parameter != null)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(parameter);
                    }
                }
                cmd.CommandTimeout = 100;
                obj = cmd.ExecuteScalar();
                if (!string.IsNullOrEmpty(parms.OutPut))
                {
                    obj = cmd.Parameters[parms.OutPut].Value;
                }
            }
            SqlConnectionPool.PutInstance(connection);
            connection = null;
            return obj;
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSql)
        {
            SqlConnection connection = SqlConnectionPool.GetInstance();
            using (SqlCommand cmd = new SqlCommand(strSql, connection))
            {
                cmd.CommandTimeout = 100;
                SqlDataReader Reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                connection = null;
                return Reader;
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string strSql, Parameters parms, CommandType type)
        {
            SqlConnection connection = SqlConnectionPool.GetInstance();
            using (SqlCommand cmd = new SqlCommand(strSql, connection))
            {
                if (type == CommandType.StoredProcedure)
                {
                    cmd.CommandType = type;
                }
                cmd.CommandTimeout = 100;
                foreach (SqlParameter parameter in parms.Entries)
                {
                    if (parameter != null)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(parameter);
                    }
                }
                SqlDataReader Reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                connection = null;
                return Reader;
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string strSql, Parameters parms)
        {
            return ExecuteReader(strSql, parms, CommandType.Text);
        }
        /// <summary>
        /// 执行SQL语句，返回DataSet
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataSet(string strSql)
        {
            SqlConnection connection = SqlConnectionPool.GetInstance();
            DataSet ds = new DataSet();
            SqlDataAdapter Adapter = new SqlDataAdapter(strSql, connection);
            Adapter.Fill(ds);
            SqlConnectionPool.PutInstance(connection);
            connection = null;
            return ds;
        }

        #endregion

        //============================================================================================================================

        #region 过滤及检测

        /// <summary>
        /// 过滤掉字符串中非数字、非分隔符的其他字符，返回数字列表字符串
        /// </summary>
        /// <param name="input">输入的数字序列字符串</param>
        /// <returns>数字列表字符串</returns>
        public static string ToValidId(string input)
        {
            if (string.IsNullOrEmpty(input))
            {
                return "-999";
            }

            string[] arrinput = input.Split(new char[] { ‘,‘ });
            string newinput = string.Empty;
            for (int i = 0; i < arrinput.GetLength(0); i++)
            {
                if (IsNumber(arrinput[i]))
                {
                    newinput = newinput + arrinput[i] + ",";
                }
            }

            if (newinput.Length > 0)
            {
                newinput = newinput.Substring(0, newinput.Length - 1);
            }
            else
            {
                return "-999";
            }

            return newinput;
        }

        /// <summary>
        /// 将字符串转换为数字字符串
        /// </summary>
        /// <param name="input">输入的字符串</param>
        /// <returns>数字字符串</returns>
        public static string ToNumber(string input)
        {
            if (string.IsNullOrEmpty(input))
            {
                return "0";
            }

            if (!Regex.IsMatch(input, "^[+-]?[0-9]+[.]?[0-9]*$"))
            {
                return "0";
            }

            return input;
        }

        /// <summary>
        /// 检查字符串是否为数字字符串
        /// </summary>
        /// <param name="input">输入的字符串</param>
        /// <returns>如果是数字字符串返回true，否则返回false</returns>
        public static bool IsNumber(string input)
        {
            if (string.IsNullOrEmpty(input))
            {
                return false;
            }

            return input.IsNumberSign();
        }

        /// <summary>
        /// 过滤掉字符串中会引起注入攻击的字符
        /// </summary>
        /// <param name="strchar">要过滤的字符串</param>
        /// <returns>已过滤的字符串</returns>
        public static string FilterBadChar(string strchar)
        {
            string newstrChar = string.Empty;
            if (string.IsNullOrEmpty(strchar))
            {
                newstrChar = string.Empty;
            }
            else
            {
                newstrChar = strchar.Replace("‘", string.Empty);
            }

            return newstrChar;
        }

        /// <summary>
        /// 过滤表字段
        /// </summary>
        /// <param name="fieldname">字段名</param>
        /// <returns>安全的字段名</returns>
        public static string FilterField(string fieldname)
        {
            if (string.IsNullOrEmpty(fieldname))
            {
                return string.Empty;
            }
            else
            {
                return Regex.Replace(fieldname, @"[^\w]", string.Empty);
            }
        }

        /// <summary>
        /// 过滤 In 类型 如： ‘xxx‘,‘xx‘
        /// </summary>
        /// <param name="inString">SQL语句的In子句中的字符串</param>
        /// <returns>过滤后的字符串</returns>
        public static string FilterInString(string inString)
        {
            if (string.IsNullOrEmpty(inString))
            {
                return "‘‘";
            }

            string[] arrinput = Regex.Split(inString, "‘,‘", RegexOptions.IgnoreCase);
            string newinput = string.Empty;
            for (int i = 0; i < arrinput.GetLength(0); i++)
            {
                newinput += "‘" + FilterBadChar(arrinput[i]) + "‘,";
            }

            if (newinput.Length > 0)
            {
                newinput = newinput.Substring(0, newinput.Length - 1);
            }
            else
            {
                return "‘‘";
            }

            return newinput;
        }

        #endregion


        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="conn"></param>
        public static void OpenSql(SqlConnection conn)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="conn"></param>
        public static void CloseSql(SqlConnection conn)
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            return;
        }

        /// <summary>
        /// 获取某表某列最大ID
        /// </summary>
        /// <param name="FieldName"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = ExecuteScalar(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        //==============================================================================================================================
    }
}