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();
