using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Com.LibLayer.Helper
{
/// <summary>
/// 数据 帮助类
/// </summary>
public class SqlHelper
{
#region 单例访问
/// <summary>
/// 单例访问器
/// </summary>
private static SqlHelper _instance;
public static SqlHelper Instance
{
get
{
if (_instance == null)
{
_instance = new SqlHelper();
}
return _instance;
}
}
private void hhh()
{
}
#endregion
#region 字段属性
//private static string _conStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
private static string _conStr = System.Configuration.ConfigurationManager.AppSettings["ConnString"].ToString();
#endregion
#region 公共方法
/// <summary>
/// 执行并返回分页数据（DataTable）
/// </summary>
/// <param name="args"></param>
/// <param name="totalPageCount"></param>
/// <param name="totalRecordCount"></param>
/// <returns></returns>
public DataTable ExecutePagedDataTable(ListSearchArgs args, out int totalPageCount, out int totalRecordCount)
{
SqlConnection sqlConn = new SqlConnection(_conStr);
if (sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
try
{
//创建一个新的 OracleCommand 对象
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "QueryPagedData";
cmd.Parameters.Clear();
cmd.Parameters.Add(new SqlParameter("@TableName", SqlDbType.VarChar, 200, ParameterDirection.Input, true, 0, 0, string.Empty, DataRowVersion.Default, args.TableName));
cmd.Parameters.Add(new SqlParameter("@TableColumns", SqlDbType.VarChar, 500, ParameterDirection.Input, true, 0, 0, string.Empty, DataRowVersion.Default, args.TableColumns));
cmd.Parameters.Add(new SqlParameter("@SqlCondition", SqlDbType.VarChar, 1000, ParameterDirection.Input, true, 0, 0, string.Empty, DataRowVersion.Default, args.SqlCondition));
cmd.Parameters.Add(new SqlParameter("@SqlSort", SqlDbType.VarChar, 500, ParameterDirection.Input, true, 0, 0, string.Empty, DataRowVersion.Default, args.SqlSort));
cmd.Parameters.Add(new SqlParameter("@PageIndex", SqlDbType.Int, 4, ParameterDirection.Input, true, 0, 0, string.Empty, DataRowVersion.Default, args.PageIndex));
cmd.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int, 4, ParameterDirection.Input, true, 0, 0, string.Empty, DataRowVersion.Default, args.PageSize));
cmd.Parameters.Add(new SqlParameter("@TotalPageCount", SqlDbType.Int, 8, ParameterDirection.Output, true, 0, 0, string.Empty, DataRowVersion.Default, null));
cmd.Parameters.Add(new SqlParameter("@TotalRecordCount", SqlDbType.Int, 8, ParameterDirection.Output, true, 0, 0, string.Empty, DataRowVersion.Default, null));
//创建一个新的数据适配器
SqlDataAdapter custDA = new SqlDataAdapter();
custDA.SelectCommand = cmd;
DataTable dt = new DataTable();
custDA.Fill(dt);
totalPageCount = Convert.ToInt32(cmd.Parameters[6].Value);
totalRecordCount = Convert.ToInt32(cmd.Parameters[7].Value);
return dt;
}
catch (Exception e1)
{
throw new Exception("获取分页DataTable时发生异常:" + e1.Message);
}
finally
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
}
/// <summary>
/// 执行并返回全部数据（DataTable）
/// </summary>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(CommandType cmdType, string cmdText, IDataParameter[] cmdParms)
{
SqlConnection sqlConn = new SqlConnection(_conStr);
if (sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
try
{
//创建一个新的数据适配器
SqlDataAdapter custDA = new SqlDataAdapter();
//创建一个新的 OracleCommand 对象
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
custDA.SelectCommand = cmd;
DataTable dt = new DataTable();
custDA.Fill(dt);
cmd.Parameters.Clear();
return dt;
}
catch (Exception e1)
{
throw new Exception("GetDataTable时发生异常:Sql=" + cmdText + e1.Message);
}
finally
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
}
/// <summary>
/// 执行并返回受影响数据的条数（int）
/// </summary>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns>受影响的行数</returns>
public int ExecuteNonQuery(CommandType cmdType, string cmdText, IDataParameter[] cmdParms)
{
SqlConnection sqlConn = new SqlConnection(_conStr);
if (sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
catch (Exception ex)
{
throw new Exception("ExecuteNonQuery时发生异常:Sql=" + cmdText + ex.Message);
}
finally
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
}
/// <summary>
/// 执行并返回查询的第一行第一列数据（object）
/// 一般用于count，max等
/// </summary>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns>受影响的行数</returns>
public object ExecuteScalar(CommandType cmdType, string cmdText, IDataParameter[] cmdParms)
{
SqlConnection sqlConn = new SqlConnection(_conStr);
if (sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
catch (Exception ex)
{
throw new Exception("ExecuteNonQuery时发生异常:Sql=" + cmdText + ex.Message);
}
finally
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
}
#endregion
}
/// <summary>
/// 通过级联操作数据类
/// </summary>
public class Query
{
#region 字段属性
// 查询字段
private string _selectFileds = "*";
// 是否启用分页
private bool _isPaged = false;
// 查询当前页
private int _pageIndex = 0;
// 查询每页记录数
private int _pageSize = 10;
// 查询排序
private string _order = string.Empty;
// 查询分组
private string _group = string.Empty;
// 插入或更新值
Dictionary<string, object> _values;
// 条件
List<string> _wheres;
/// <summary>
/// 查询表名
/// </summary>
private string _tableName = string.Empty;
public string TableName
{
get { return this._tableName; }
set { this._tableName = value; }
}
/// <summary>
/// 操作类型
/// </summary>
private OperType _otype = OperType.UnKnown;
public OperType Otype
{
get { return this._otype; }
set { this._otype = value; }
}
#endregion
#region 构造函数
/// <summary>
/// 构造函数
/// </summary>
public Query()
{
this._wheres = new List<string>();
this._values = new Dictionary<string, object>();
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="otype">操作类型</param>
public Query(OperType otype)
{
this.Otype = otype;
this._wheres = new List<string>();
this._values = new Dictionary<string, object>();
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="otype">操作类型</param>
/// <param name="tableNames">数据表名</param>
public Query(OperType otype, string tableNames)
{
this.Otype = otype;
this.TableName = tableNames;
this._wheres = new List<string>();
this._values = new Dictionary<string, object>();
}
#endregion
# region 级联方法
/// <summary>
/// 获取 sql语句的查询表名
/// </summary>
/// <param name="tableNames">查询的表名集合</param>
/// <returns></returns>
public Query SetTables(string tableNames)
{
this.TableName = tableNames;
return this;
}
/// <summary>
/// 设置 sql语句Where条件
/// </summary>
/// <param name="expression">条件表达式</param>
/// <returns></returns>
public Query SetWheres(string format, params object[] args)
{
string expression = string.Format(format, args);
if (!string.IsNullOrEmpty(expression))
{
this._wheres.Add(expression);
}
return this;
}
/// <summary>
/// 添加 sql语句Where条件
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public Query Add(string format, params object[] args)
{
string expression = string.Format(format, args);
if (!string.IsNullOrEmpty(expression))
{
this._wheres.Add(expression);
}
return this;
}
/// <summary>
/// 设置 InSert|Update语句的字段
/// </summary>
/// <param name="field">数据字段</param>
/// <param name="value">待插入的值</param>
/// <returns></returns>
public Query SetValues(string field, object value)
{
this._values.Add(field, value);
return this;
}
/// <summary>
/// 添加 InSert|Update语句的字段
/// </summary>
/// <param name="field"></param>
/// <param name="value"></param>
/// <returns></returns>
public Query Add(string field, object value)
{
this._values.Add(field, value);
return this;
}
/// <summary>
/// 获取 Select语句的查询字段
/// </summary>
/// <param name="expression">选择字段</param>
/// <returns></returns>
public Query SetFileds(string expression)
{
this._selectFileds = expression;
return this;
}
/// <summary>
/// 添加 Select语句排序
/// </summary>
/// <param name="expression">排序表达式</param>
/// <returns></returns>
public Query SetOrderBy(string expression)
{
this._order = expression;
return this;
}
/// <summary>
/// 添加 Select语句分组
/// </summary>
/// <param name="expression">分组表达式</param>
/// <returns></returns>
public Query SetGroupBy(string expression)
{
this._group = expression;
return this;
}
/// <summary>
/// 添加 Select语句分页设置
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public Query SetPaged(int pageIndex, int pageSize)
{
this._pageIndex = pageIndex;
this._pageSize = pageSize;
this._isPaged = true;
return this;
}
/// <summary>
/// 判断参数的类型
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
private string GetVaule(object value)
{
switch (value.GetType().Name)
{
case "Int32":
return value.ToString();
case "Boolean":
return (bool)value ? "1" : "0";
default:
return string.Format("'{0}'", value.ToString().Replace("'", ""));
}
}
/// <summary>
/// 输出
/// </summary>
/// <returns></returns>
public override string ToString()
{
string output = string.Empty;
switch (this.Otype)
{
case OperType.InSert:
{
#region InSert（插入）
StringBuilder stb = new StringBuilder();
stb.AppendFormat("insert into {0} (", this.TableName);
foreach (var vkey in this._values.Keys)
{
stb.AppendFormat("{0},", vkey);
}
stb.Append(") values ( ");
foreach (object val in this._values.Values)
{
stb.AppendFormat("{0},", this.GetVaule(val));
}
stb.Append(")");
output = stb.ToString().Replace(",)", ")");
#endregion
break;
}
case OperType.Update:
{
#region Update（更新）
StringBuilder stb = new StringBuilder();
stb.AppendFormat("update {0} set ", this.TableName);
foreach (var val in this._values.Keys)
{
stb.AppendFormat(" {0}={1},", val, this.GetVaule(this._values[val]));
}
stb.Append("where 1=1 ");
foreach (string wh in this._wheres)
{
stb.AppendFormat(" {0} ", wh);
}
output = stb.ToString().Replace(",where", " where");
#endregion
break;
}
case OperType.Delete:
{
#region Delete（删除）
StringBuilder stb = new StringBuilder();
stb.AppendFormat("delete {0} where 1=1 ", this.TableName);
foreach (string wh in this._wheres)
{
stb.AppendFormat(" {0} ", wh);
}
output = stb.ToString();
#endregion
break;
}
case OperType.Select:
{
if (!this._isPaged)
{
#region Select（一般的sql语句）
StringBuilder stb = new StringBuilder();
stb.AppendFormat("select {0} from {1} where 1=1 ", this._selectFileds, this.TableName);
foreach (string wh in this._wheres)
{
stb.AppendFormat(" {0} ", wh);
}
stb.AppendFormat(" {0}", string.IsNullOrEmpty(this._group) ? "" : " group by " + this._group);
stb.AppendFormat(" {0}", string.IsNullOrEmpty(this._order) ? "" : " order by " + this._order);
output = stb.ToString();
#endregion
}
else
{
output = "调用存储过程";
}
break;
}
default:
{
throw new Exception("当前操作出错！");
}
}
return output;
}
/// <summary>
/// 执行并返回分页数据（DataTable）
/// </summary>
/// <returns></returns>
public DataTable ExecutePagedDataTable(out int pageCount, out int recordCount)
{
#region Select（调用存储过程）
StringBuilder stb = new StringBuilder();
foreach (string wh in this._wheres)
{
stb.AppendFormat(" {0} ", wh);
}
stb.AppendFormat(" {0}", string.IsNullOrEmpty(this._group) ? "" : " group by " + this._group);
ListSearchArgs args = new ListSearchArgs();
args.TableName = this.TableName;
args.TableColumns = this._selectFileds;
args.SqlCondition = stb.ToString();
args.SqlSort = this._order;
args.PageIndex = this._pageIndex;
args.PageSize = this._pageSize;
#endregion
int pc = 1;
int rc = 0;
DataTable dt = SqlHelper.Instance.ExecutePagedDataTable(args, out pc, out rc);
pageCount = pc;
recordCount = rc;
return dt;
}
/// <summary>
/// 执行并返回全部数据（DataTable）
/// </summary>
/// <returns></returns>
public DataTable ExecuteDataTable()
{
return SqlHelper.Instance.ExecuteDataTable(CommandType.Text, this.ToString(), null);
}
/// <summary>
/// 执行并返回受影响数据的条数（int）
/// </summary>
/// <returns></returns>
public int ExecuteNonQuery()
{
return SqlHelper.Instance.ExecuteNonQuery(CommandType.Text, this.ToString(), null);
}
/// <summary>
/// 执行并返回查询的第一行第一列数据（object）
/// </summary>
/// <returns></returns>
public object ExecuteScalar()
{
return SqlHelper.Instance.ExecuteScalar(CommandType.Text, this.ToString(), null);
}
#endregion
}
/// <summary>
/// 判断当前连接执行如何操作
/// </summary>
public enum OperType
{
UnKnown,
InSert,
Update,
Delete,
Select
}
/// <summary>
/// 分页查询 参数类
/// </summary>
public class ListSearchArgs
{
//待查询表名
private string _tableName;
public string TableName
{
get { return this._tableName; }
set { this._tableName = value; }
}
//待显示字段
private string _tableColumns = "*";
public string TableColumns
{
get { return this._tableColumns; }
set { this._tableColumns = value; }
}
//查询条件;不需where
private string _sqlCondition = null;
public string SqlCondition
{
get { return this._sqlCondition; }
set { this._sqlCondition = value; }
}
//排序字段，不需order by
private string _sqlSort = null;
public string SqlSort
{
get { return this._sqlSort; }
set { this._sqlSort = value; }
}
//当前页
private int _pageIndex;
public int PageIndex
{
get { return this._pageIndex; }
set { this._pageIndex = value; }
}
//每页显示的记录数
private int _pageSize;
public int PageSize
{
get { return this._pageSize; }
set { this._pageSize = value; }
}
}
#region 存储过程
/*
* ********分页功能对应的存储过程*********
*
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: jbzhang
-- Create date: 2009年09月04日
-- Description: 采用Row_number()给数据加上索引，专为SQL server 2005及以上使用
-- =============================================
create PROCEDURE QueryPagedData
(
@TableName VARCHAR(200), ----待查询表名
@TableColumns nvarchar(500) = '*', ----待显示字段
@SqlCondition nvarchar(1000) = null, ----查询条件,不需where
@SqlSort nvarchar(500) = null, ----排序字段，不需order by
@PageIndex int, ----当前页
@PageSize int, ----每页显示的记录数
@TotalRecordCount int = 1 OUTPUT, ----返回总记录数
@TotalPageCount int = 1 OUTPUT ----返回总页数
)
AS
BEGIN
SET NOCOUNT ON;
Declare @sqlCountTmp nvarchar(1000) ----sql语句，查询数据总量
Declare @sqlDataTmp nvarchar(1000) ----sql语句，查询分页数据
----排序字段是否存在
if @SqlSort is null or @SqlSort=''
set @SqlSort = ' ObjectId ASC '
----查询条件是否存在
if @SqlCondition is null or @SqlCondition=''
set @SqlCondition = ' '
else
set @SqlCondition = ' where (1=1) ' + @SqlCondition
----获取记录总数
set @sqlCountTmp = ' select @TotalRecordCount=Count(*) FROM '+@TableName + @SqlCondition
exec sp_executesql @sqlCountTmp,N'@TotalRecordCount int out ',@TotalRecordCount out
----获取分页总数
declare @tmpCounts int
if @TotalRecordCount = 0
set @tmpCounts = 1
else
set @tmpCounts = @TotalRecordCount
set @TotalPageCount=(@tmpCounts+@PageSize-1)/@PageSize
if @PageIndex>@TotalPageCount
set @PageIndex=@TotalPageCount
----返回数据查询
set @sqlDataTmp=' select '+@TableColumns+ ' from (select *,Row_number() over(order by '+@SqlSort+') as sqlRowIndex from '+ @TableName + @SqlCondition+') as TableWithRowIndex where sqlRowIndex>'+ cast(@PageSize*@PageIndex as Varchar(20))+' and sqlRowIndex<'+ cast((@PageSize*(@PageIndex+1)+1) as Varchar(20))
exec sp_executesql @sqlDataTmp
END
GO
*/
#endregion
}
