sqlhelper

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

namespace DotNet.Utilities
{
    public class SqlHelper
    {
        public static string connectionString = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;

        // 连接和事务计数
        private static int connCount;
        private static int tranCount;

        // 连接和事务对象
        private static SqlConnection _conn;
        private static SqlTransaction _tran;

        #region 打开/关闭数据库连接

        /// <summary>
        /// 打开数据连接
        /// </summary>
        static public void OpenConnection()
        {

            connCount++;
            if (connCount == 1)
            {
                _conn = new SqlConnection(connectionString);
                _conn.Open();
            }
        }

        /// <summary>
        /// 关闭数据连接
        /// </summary>
        static public void CloseConnection()
        {
            connCount--;
            if (connCount <= 0)
            {
                _conn.Close();
            }
        }

        #endregion

        #region 事务处理

        /// <summary>
        /// 开启事务
        /// </summary>
        static public void BeginTransaction()
        {
            tranCount++;
            if (tranCount == 1)
            {
                OpenConnection();
                _tran = _conn.BeginTransaction();
            }
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        static public void RollbackTransaction()
        {
            tranCount--;
            if (tranCount <= 0)
            {
                tranCount = 0;
                _tran.Rollback();
                CloseConnection();
            }
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        static public void CommitTransaction()
        {
            tranCount--;
            if (tranCount <= 0)
            {
                tranCount = 0;
                _tran.Commit();
                CloseConnection();
            }
        }

        #endregion

        #region execute方法,执行操作的方法

        /// <summary>
        /// 执行指定的 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        static public int ExecuteNonQuery(string strCmd)
        {
            return ExecuteNonQuery(strCmd, CommandType.Text, null);
        }

        /// <summary>
        /// 执行指定的 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        static public int ExecuteNonQuery(string strCmd, params SqlParameter[] parameters)
        {
            return ExecuteNonQuery(strCmd, CommandType.Text, parameters);
        }

        /// <summary>
        /// 执行指定的 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="strCmd"></param>
        /// <returns></returns>
        static public int ExecuteNonQuery(string strCmd, CommandType type, params SqlParameter[] parameters)
        {
            try
            {
                OpenConnection();
                SqlCommand cmd = CreateCommandAll(strCmd, type, parameters);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        static public object ExecuteScalar(string strCmd)
        {
            return ExecuteScalar(strCmd, CommandType.Text, null);
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        static public object ExecuteScalar(string strCmd, params SqlParameter[] parameters)
        {
            return ExecuteScalar(strCmd, CommandType.Text, parameters);
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行。
        /// </summary>
        /// <param name="strCmd"></param>
        /// <returns></returns>
        static public object ExecuteScalar(string strCmd, CommandType type, params SqlParameter[] parameters)
        {
            try
            {
                OpenConnection();
                SqlCommand cmd = CreateCommandAll(strCmd, type, parameters);
                return cmd.ExecuteScalar();
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 执行查询，生成一个DataReader返回数据(此函数内部不会调用OpenConnection和CloseConnection，所以必须在调用OpenConnection方法后使用)
        /// </summary>
        static public SqlDataReader ExecuteReader(string strCmd)
        {
            return ExecuteReader(strCmd, CommandType.Text, null);
        }

        /// <summary>
        /// 执行查询，生成一个DataReader返回数据(此函数内部不会调用OpenConnection和CloseConnection，所以必须在调用OpenConnection方法后使用)
        /// </summary>
        static public SqlDataReader ExecuteReader(string strCmd, params SqlParameter[] parameters)
        {
            return ExecuteReader(strCmd, CommandType.Text, parameters);
        }

        /// <summary>
        /// 执行查询，生成一个DataReader返回数据(此函数内部不会调用OpenConnection和CloseConnection)
        /// </summary>
        /// <param name="strCmd"></param>
        /// <returns></returns>
        static public SqlDataReader ExecuteReader(string strCmd, CommandType type, params SqlParameter[] parameters)
        {
            SqlCommand cmd = CreateCommandAll(strCmd, type, parameters);
            return cmd.ExecuteReader();
        }

        /// <summary>
        /// 执行查询，并返回一个DataSet对象
        /// </summary>
        static public DataSet ExecuteDataSet(string strCmd)
        {
            return ExecuteDataSet(strCmd, CommandType.Text, null);
        }

        /// <summary>
        /// 执行查询，并返回一个DataSet对象
        /// </summary>
        static public DataSet ExecuteDataSet(string strCmd, params SqlParameter[] parameters)
        {
            return ExecuteDataSet(strCmd, CommandType.Text, parameters);
        }

        /// <summary>
        /// 执行查询，并返回一个DataSet对象
        /// </summary>
        /// <param name="strCmd"></param>
        /// <returns></returns>
        static public DataSet ExecuteDataSet(string strCmd, CommandType type, params SqlParameter[] parameters)
        {
            try
            {
                OpenConnection();

                SqlCommand cmd = CreateCommandAll(strCmd, type, parameters);

                SqlDataAdapter adapter = new SqlDataAdapter(strCmd, _conn);
                adapter.SelectCommand = cmd;

                DataSet ds = new DataSet();
                adapter.Fill(ds);

                return ds;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 执行查询，并返回一个DataTable对象
        /// </summary>
        static public DataTable ExecuteDataTable(string strCmd)
        {
            return ExecuteDataTable(strCmd, CommandType.Text, null);
        }

        /// <summary>
        /// 执行查询，并返回一个DataTable对象
        /// </summary>
        static public DataTable ExecuteDataTable(string strCmd, params SqlParameter[] parameters)
        {
            return ExecuteDataTable(strCmd, CommandType.Text, parameters);
        }

        /// <summary>
        /// 执行查询，并返回一个DataTable对象
        /// </summary>
        /// <param name="strCmd"></param>
        /// <returns></returns>
        static public DataTable ExecuteDataTable(string strCmd, CommandType type, params SqlParameter[] parameters)
        {
            try
            {
                OpenConnection();

                SqlCommand cmd = CreateCommandAll(strCmd, type, parameters);

                SqlDataAdapter adapter = new SqlDataAdapter(strCmd, _conn);
                adapter.SelectCommand = cmd;

                DataSet ds = new DataSet();
                adapter.Fill(ds);

                return ds.Tables[0];
            }
            finally
            {
                CloseConnection();
            }
        }

        #endregion

        #region 方法

        static private SqlCommand CreateCommandAll(string sql, CommandType type, SqlParameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = type;
            cmd.Connection = _conn;
            cmd.Transaction = _tran;
            cmd.CommandText = sql;
            if (parameters != null)
            {
                foreach (SqlParameter param in parameters)
                {
                    cmd.Parameters.Add(param);
                }
            }
            return cmd;
        }

        #endregion

        #region 过滤

        static public string FilterChar(string text)
        {
            //string[] filterKeyList = { "select;from", "select;into", "delete;from", "drop;table", "drop;database", "update;set", "truncate;table", "create;table", "exists;select", "insert;into", "asc(", "xp_cmdshell", "declare;@", "exec;master", "chr(", "ch(", "waitfor;delay" };
            string[] filterKeyList = { "select;into", "delete;from", "drop;table", "drop;database", "update;set", "truncate;table", "create;table", "exists;select", "insert;into", "asc(", "xp_cmdshell", "declare;@", "exec;master", "chr(", "ch(", "waitfor;delay" };
            text = text.Replace("--", "").Replace(";", "").Replace("&", "").Replace("*", "").Replace("||", "");
            string[] items = text.Split(' ');
            if (items.Length == 1 && text.Length > 30)
            {
                if (text.IndexOf("%20") > -1)
                {
                    return string.Empty;
                }
            }
            string lowerText = text.ToLower();
            items = lowerText.Split(' ');

            int keyIndex = -1;
            bool isOK = false;
            string tempKey = string.Empty;
            string filterKey = string.Empty;
            string[] filterSpitItems = null;
            for (int i = 0; i < filterKeyList.Length; i++)
            {
                filterSpitItems = filterKeyList[i].Split(';');//分隔
                filterKey = filterSpitItems[0];//取第一个为关键词
                if (filterSpitItems.Length > 2)
                {
                    continue;
                }
                else if (filterSpitItems.Length == 2) // 如果是两个词的。
                {
                    keyIndex = Math.Min(lowerText.IndexOf(filterKey), lowerText.IndexOf(filterSpitItems[1]));
                }
                else
                {
                    keyIndex = lowerText.IndexOf(filterKey);//过滤的关键词或词组
                }
                if (keyIndex > -1)
                {
                    foreach (string item in items) // 用户传进来的每一个单独的词
                    {
                        tempKey = item.Trim('\'', ')', '|', '!', '%', '^', '(');
                        if (tempKey.IndexOf(filterKey) > -1 && tempKey.Length > filterKey.Length)
                        {
                            isOK = true;
                            break;
                        }
                    }
                    if (!isOK)
                    {
                        return string.Empty;
                    }
                    else
                    {
                        isOK = false;
                    }
                }
            }
            return text;
        }

        #endregion

        #region 分页sql

        /// <summary>
        /// 构建sql2005以上分页语句
        /// </summary>
        /// <param name="PageIndex">当前页码</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="TableName">表名</param>
        /// <param name="ShowColumns">字段</param>
        /// <param name="OrderByColumnd">排序字段</param>
        /// <param name="TableQuery">查询条件</param>
        /// <returns></returns>
        public static string PageSql(int PageIndex, int PageSize, string TableName, string ShowColumns, string OrderBy, string TableQuery)
        {
            int intStart = (PageIndex - 1) * PageSize + 1;
            int intEnd = intStart + PageSize - 1;
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from(select " + ShowColumns + ",ROW_NUMBER() OVER(ORDER BY ");
            sb.Append(OrderBy);
            sb.Append(") as row from ");
            sb.Append(TableName);
            //查询条件
            if (TableQuery.Length > 0)
            {
                sb.Append(" where ");
                sb.Append(FilterChar(TableQuery));
            }
            sb.Append(") as a where row between " + intStart + " and " + intEnd);
            return sb.ToString();
        }

        #endregion

    }
}

 使用：

SqlHelper.ExecuteNonQuery("sql");

// 允许事务
SqlHelper.BeginTransaction();
SqlHelper.ExecuteNonQuery("sql");
SqlHelper.ExecuteNonQuery("sql");
SqlHelper.CommitTransaction();