using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Collections;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace WebApplication1
{
public abstract class DbHelper
{
public static void SetTimeoutDefault()
{
Timeout = 30;
}
public static int Timeout = 30;
public static string CONN_STR = "";
public static SqlConnection Conn = null;
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
cmd.CommandTimeout = Timeout;
if (sqlParas != null)
{
foreach (SqlParameter parm in sqlParas)
if (parm != null)
cmd.Parameters.Add(parm);
}
}
public static DataTable FillTable(string sql, List<SqlParameter> sqlParas)
{
return DbHelper.FillTable(CONN_STR, CommandType.Text, sql, sqlParas);
}
public static DataTable FillTable(CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
return DbHelper.FillTable(CONN_STR, cmdType, cmdText, sqlParas);
}
public static DataTable FillTable(string connectionString, CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection())
{
try
{
connection.ConnectionString = connectionString;
DbHelper.PrepareCommand(cmd, connection, null, cmdType, cmdText, sqlParas);
SqlDataAdapter ap = new SqlDataAdapter();
ap.SelectCommand = cmd;
DataTable result = new DataTable();
ap.Fill(result);
cmd.Parameters.Clear();
return result;
}
catch
{
throw;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Dispose();
}
}
}
public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
return ExecuteReader(CONN_STR, cmdType, cmdText, sqlParas);
}
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = connectionString;
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
DbHelper.PrepareCommand(cmd, conn, null, cmdType, cmdText, sqlParas);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
DbHelper.Conn = conn;
return rdr;
}
catch
{
throw;
}
finally
{
//conn.Close();
//conn.Dispose();
//conn = null;
}
}
public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
SqlCommand cmd = new SqlCommand();
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
DbHelper.PrepareCommand(cmd, connection, null, cmdType, cmdText, sqlParas);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
public static int ExecuteNonQuery(CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
return ExecuteNonQuery(CONN_STR, cmdType, cmdText, sqlParas);
}
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection())
{
try
{
conn.ConnectionString = connectionString;
PrepareCommand(cmd, conn, null, cmdType, cmdText, sqlParas);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
catch
{
throw;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
}
}
}
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, sqlParas);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
DbHelper.Conn = connection;
return val;
}
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, sqlParas);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
DbHelper.Conn = trans.Connection;
return val;
}
public static object ExecuteScalar(CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
return ExecuteScalar(CONN_STR, cmdType, cmdText, sqlParas);
}
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection())
{
try
{
connection.ConnectionString = connectionString;
PrepareCommand(cmd, connection, null, cmdType, cmdText, sqlParas);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
//DbHelper.Conn = connection;
return val;
}
catch
{
throw;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
connection.Dispose();
}
}
}
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, sqlParas);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
DbHelper.Conn = connection;
return val;
}
public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, List<SqlParameter> sqlParas)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, sqlParas);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
DbHelper.Conn = trans.Connection;
return val;
}
/// <summary>
/// 执行对默认数据库有自定义排序的分页的查询
/// </summary>
/// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
/// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
/// <param name="IndexField">用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id</param>
/// <param name="OrderFields">排序字段以及方式如：a.OrderID desc,CnName desc</param>
/// <param name="PageIndex">当前页的页码</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
/// <param name="PageCount">输出参数，返回查询的总页数</param>
/// <param name="sqlParas">sqlParas</param>
/// <returns>返回查询结果</returns>
public static DataTable ExecutePage(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize,
out int RecordCount, out int PageCount, List<SqlParameter> sqlParas)
{
return ExecutePage(CONN_STR, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, sqlParas);
}
/// <summary>
/// 执行有自定义排序的分页的查询
/// </summary>
/// <param name="connectionString">SQL数据库连接字符串</param>
/// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
/// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
/// <param name="IndexField">用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id</param>
/// <param name="OrderFields">排序字段以及方式如：a.OrderID desc,CnName desc</OrderFields>
/// <param name="PageIndex">当前页的页码</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
/// <param name="PageCount">输出参数，返回查询的总页数</param>
/// <returns>返回查询结果</returns>
public static DataTable ExecutePage(string connectionString, string SqlAllFields, string SqlTablesAndWhere,
string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, List<SqlParameter> sqlParas)
{
using (SqlConnection connection = new SqlConnection())
{
try
{
connection.ConnectionString = connectionString;
connection.Open();
return ExecutePage(connection, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, sqlParas);
}
catch
{
throw;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
}
}
}
/// <summary>
/// 执行有自定义排序的分页的查询
/// </summary>
/// <param name="connection">SQL数据库连接对象</param>
/// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
/// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
/// <param name="IndexField">用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id</param>
/// <param name="OrderFields">排序字段以及方式如：a.OrderID desc,CnName desc</OrderFields>
/// <param name="PageIndex">当前页的页码</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
/// <param name="PageCount">输出参数，返回查询的总页数</param>
/// <returns>返回查询结果</returns>
public static DataTable ExecutePage(SqlConnection connection, string SqlAllFields, string SqlTablesAndWhere,
string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, List<SqlParameter> sqlParas)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, CommandType.Text, "", sqlParas);
string Sql = GetPageSql(cmd, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount);
cmd.CommandText = Sql;
SqlDataAdapter ap = new SqlDataAdapter();
ap.SelectCommand = cmd;
DataSet st = new DataSet();
ap.Fill(st, "PageResult");
cmd.Parameters.Clear();
DbHelper.Conn = connection;
return st.Tables["PageResult"];
}
/// <summary>
/// 取得分页的SQL语句
/// </summary>
/// <param name="cmd">SqlCommand</param>
/// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
/// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，
/// 如:students a inner join achievement b on a.... where ....</param>
/// <param name="IndexField">用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id</param>
/// <param name="OrderFields">排序字段以及方式如：a.OrderID desc,CnName desc</param>
/// <param name="PageIndex">当前页的页码</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
/// <param name="PageCount">输出参数，返回查询的总页数</param>
/// <returns>返回查询结果</returns>
private static string GetPageSql(SqlCommand cmd,
string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize,
out int RecordCount, out int PageCount)
{
RecordCount = 0;
PageCount = 0;
if (PageSize <= 0)
{
PageSize = 10;
}
string SqlCount = "select count(" + IndexField + ") from " + SqlTablesAndWhere;
cmd.CommandText = SqlCount;
RecordCount = (int)cmd.ExecuteScalar();
if (RecordCount % PageSize == 0)
{
PageCount = RecordCount / PageSize;
}
else
{
PageCount = RecordCount / PageSize + 1;
}
if (PageIndex > PageCount)
{
PageIndex = PageCount;
}
if (PageIndex < 1)
{
PageIndex = 1;
}
string Sql = null;
if (PageIndex == 1)
{
Sql = "select top " + PageSize + " " + SqlAllFields + " from " + SqlTablesAndWhere + " " + OrderFields;
}
else
{
Sql = "select top " + PageSize + " " + SqlAllFields + " from ";
if (SqlTablesAndWhere.ToLower().IndexOf(" where ") > 0)
{
string _where = Regex.Replace(SqlTablesAndWhere, @"\ where\ ", " where (", RegexOptions.IgnoreCase | RegexOptions.Compiled);
Sql += _where + ") and (";
}
else
{
Sql += SqlTablesAndWhere + " where (";
}
Sql += IndexField + " not in (select top " + (PageIndex - 1) * PageSize + " " + IndexField + " from " + SqlTablesAndWhere + " " + OrderFields;
Sql += ")) " + OrderFields;
}
return Sql;
}
#region SQL2005分页方法
/// <summary>
/// 执行对默认数据库有自定义排序的分页的查询
/// </summary>
/// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
/// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
/// <param name="OrderFields">排序字段以及方式如：a.OrderID desc,CnName desc</param>
/// <param name="PageIndex">当前页的页码</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
/// <param name="PageCount">输出参数，返回查询的总页数</param>
/// <param name="sqlParas">sqlParas</param>
/// <returns>返回查询结果</returns>
public static DataTable ExecutePage2(string SqlAllFields, string SqlTablesAndWhere, string OrderFields, int PageIndex, int PageSize,
out int RecordCount, out int PageCount, List<SqlParameter> sqlParas)
{
return ExecutePage2(CONN_STR, SqlAllFields, SqlTablesAndWhere, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, sqlParas);
}
/// <summary>
/// 执行有自定义排序的分页的查询
/// </summary>
/// <param name="connectionString">SQL数据库连接字符串</param>
/// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
/// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
/// <param name="OrderFields">排序字段以及方式如：order by a.OrderID desc,order by CnName desc</param>
/// <param name="PageIndex">当前页的页码</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
/// <param name="PageCount">输出参数，返回查询的总页数</param>
/// <param name="sqlParas"></param>
/// <returns>返回查询结果</returns>
public static DataTable ExecutePage2(string connectionString, string SqlAllFields, string SqlTablesAndWhere,
string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, List<SqlParameter> sqlParas)
{
using (SqlConnection connection = new SqlConnection())
{
try
{
connection.ConnectionString = connectionString;
connection.Open();
return ExecutePage2(connection, SqlAllFields, SqlTablesAndWhere, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, sqlParas);
}
catch
{
throw;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
}
}
}
/// <summary>
/// 执行有自定义排序的分页的查询
/// </summary>
/// <param name="connection">SQL数据库连接对象</param>
/// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
/// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
/// <param name="OrderFields">排序字段以及方式如：a.OrderID desc,CnName desc</OrderFields>
/// <param name="PageIndex">当前页的页码</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
/// <param name="PageCount">输出参数，返回查询的总页数</param>
/// <returns>返回查询结果</returns>
public static DataTable ExecutePage2(SqlConnection connection, string SqlAllFields, string SqlTablesAndWhere,
string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, List<SqlParameter> sqlParas)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, CommandType.Text, "", sqlParas);
string Sql = GetPageSql2(cmd, SqlAllFields, SqlTablesAndWhere, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount);
cmd.CommandText = Sql;
SqlDataAdapter ap = new SqlDataAdapter();
ap.SelectCommand = cmd;
DataSet st = new DataSet();
ap.Fill(st, "PageResult");
cmd.Parameters.Clear();
DbHelper.Conn = connection;
return st.Tables["PageResult"];
}
/// <summary>
/// 取得分页的SQL语句
/// </summary>
/// <param name="cmd">SqlCommand</param>
/// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
/// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，
/// 如:students a inner join achievement b on a.... where ....</param>
/// <param name="OrderFields">排序字段以及方式如：a.OrderID desc,CnName desc</param>
/// <param name="PageIndex">当前页的页码</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
/// <param name="PageCount">输出参数，返回查询的总页数</param>
/// <returns>返回查询结果</returns>
private static string GetPageSql2(SqlCommand cmd,
string SqlAllFields, string SqlTablesAndWhere, string OrderFields, int PageIndex, int PageSize,
out int RecordCount, out int PageCount)
{
RecordCount = 0;
PageCount = 0;
if (PageSize <= 0)
{
PageSize = 10;
}
string SqlCount = "select count(*) from " + SqlTablesAndWhere;
cmd.CommandText = SqlCount;
RecordCount = (int)cmd.ExecuteScalar();
if (RecordCount % PageSize == 0)
{
PageCount = RecordCount / PageSize;
}
else
{
PageCount = RecordCount / PageSize + 1;
}
if (PageIndex > PageCount)
{
PageIndex = PageCount;
}
if (PageIndex < 1)
{
PageIndex = 1;
}
string Sql = null;
if (PageIndex == 1)
{
Sql = "select top " + PageSize + " " + SqlAllFields + " from " + SqlTablesAndWhere + " " + OrderFields;
}
else
{
//SELECT *
//FROM
//( SELECT ROW_NUMBER() OVER (ORDER BY uid asc )AS Row, UserName,TrueName,Email FROM wawa_user ) as tempTable
//WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
//Sql = "select " + SqlAllFields + " from " + SqlTablesAndWhere + " " + OrderFields;
int start = (PageIndex - 1) * PageSize + 1;
int end = start + PageSize - 1;
Sql =
@"select * from " +
"(select row_number() over (" + OrderFields + ") as RowNumber," + SqlAllFields + " from " + SqlTablesAndWhere + ") as temptable " +
"where RowNumber between " + start + " and " + end + "";
}
return Sql;
}
#endregion
}
}
评论表单加载中...