//--------------------------------------------------------------------------------// 文件描述：数据库操作助手// 文件作者：品浩// 创建日期：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());            }        }        //==============================================================================================================================    }}