绝对收藏的SQLHelper

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
}

 

转载于:https://www.cnblogs.com/gyweiUSTC/articles/1894848.html