sqlhelper不知道哪里不对劲，烦请指点
--------------------编程问答--------------------
/// <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
}
}
补充：.NET技术 , ASP.NET
