using VueWebApi.Tools;
|
using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Web;
|
|
namespace VueWebApi.Tools
|
{
|
public class DBHelper
|
{
|
#region DataTable GetTable(string sql) 获取DataTable
|
/// <summary>
|
/// 获取DataTable
|
/// </summary>
|
/// <param name="sql">传入SQL语句</param>
|
/// <returns>返回DataTable</returns>
|
public static DataTable GetTable(string sql)
|
{
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.getTable(sql);
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
//throw (ex);
|
return null;
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
|
/// <summary>
|
/// 获取DataTable
|
/// </summary>
|
/// <param name="sql">传入SQL语句</param>
|
/// <param name="parameters">SqlParameter 参数</param>
|
/// <returns>返回DataTable</returns>
|
public static DataTable GetTable(string sql, params SqlParameter[] parameters)
|
{
|
|
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.getTable(sql, parameters);
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw (ex);
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
#endregion
|
|
/// <summary>
|
/// Dataset获取数据表
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="parameters"></param>
|
/// <returns></returns>
|
#region
|
public static DataSet GetData(string sql, params SqlParameter[] parameters)
|
{
|
|
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.getdate(sql, parameters);
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
//throw (ex);
|
//创建日志记录组件实例
|
return null;
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region GetCount(string sql) 获取数据行数,如果出错返回"-1"
|
/// <summary>
|
/// 获取数据行数,如果出错返回"-1"
|
/// </summary>
|
/// <param name="sql">传入SQL语句</param>
|
/// <returns>如果出错返回"-1"</returns>
|
public static int GetCount(string sql)
|
{
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.getCount(sql);
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
//throw (ex);
|
//创建日志记录组件实例
|
return 0;
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
|
/// <summary>
|
/// 执行多条sql语句的事物
|
/// </summary>
|
/// <param name="list"></param>
|
/// <returns></returns>
|
public static int Executesqltran(List<string> list, params SqlParameter[] parameters)
|
{
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.ExecuteSqlTran(list, parameters);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.WriteLog(ex);
|
throw (ex);
|
//创建日志记录组件实例
|
return 0;
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
|
/// <summary>
|
/// 获取数据行数,如果出错返回"-1"
|
/// </summary>
|
/// <param name="sql">传入SQL语句</param>
|
/// <param name="parameters">SqlParameter 参数</param>
|
/// <returns>如果出错返回"-1"</returns>
|
public static int GetCount(string sql, params SqlParameter[] parameters)
|
{
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.getCount(sql, parameters);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.WriteLog(ex);
|
throw (ex);
|
//创建日志记录组件实例
|
return 0;
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region GetObject(string sql) 获取表格第一行第一列
|
/// <summary>
|
/// 获取表格第一行第一列
|
/// </summary>
|
/// <param name="sql">传入SQL语句</param>
|
/// <returns>如果出错返回"null"</returns>
|
public static object GetObject(string sql)
|
{
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.getObject(sql);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.WriteLog(ex);
|
throw (ex);
|
//创建日志记录组件实例
|
return null;
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
|
/// <summary>
|
/// 获取表格第一行第一列
|
/// </summary>
|
/// <param name="sql">传入SQL语句</param>
|
/// <param name="parameters">SqlParameter 参数</param>
|
/// <returns>返回Object</returns>
|
public static object GetObject(string sql, params SqlParameter[] parameters)
|
{
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.getObject(sql, parameters);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.WriteLog(ex);
|
throw (ex);
|
//创建日志记录组件实例
|
return null;
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region ExecuteSql(string sql) 执行SQL语句返回是否成功
|
/// <summary>
|
/// 执行SQL语句返回是否成功
|
/// </summary>
|
/// <param name="sql">传入SQL语句</param>
|
/// <returns>返回是否成功</returns>
|
public static bool ExecuteSql(string sql)
|
{
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.executeSql(sql);
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
//throw (ex);
|
//创建日志记录组件实例
|
return false;
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
|
/// <summary>
|
/// 执行SQL语句返回是否成功
|
/// </summary>
|
/// <param name="sql">传入SQL语句</param>
|
/// <param name="parameters">SqlParameter 参数</param>
|
/// <returns>返回是否成功</returns>
|
public static bool ExecuteSql(string sql, params SqlParameter[] parameters)
|
{
|
DataOperator dbo = null;
|
//ParamCollections pc = new ParamCollections();
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.executeSql(sql, parameters);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.WriteLog(ex);
|
throw (ex);
|
//创建日志记录组件实例
|
return false;
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region ExecuteProduct(string productName, params SqlParameter[] parameters) 执行存储过程返回返回值
|
/// <summary>
|
/// 执行存储过程返回返回值
|
/// </summary>
|
/// <param name="productName">存储过程名字</param>
|
/// <param name="parameters">SqlParameter 参数</param>
|
/// <returns>返回值Hash表</returns>
|
public static Hashtable ExecuteProduct(string productName, params SqlParameter[] parameters)
|
{
|
DataOperator dbo = null;
|
//ParamCollections pc = new ParamCollections();
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.executeProduct(productName, parameters);
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
//throw (ex);
|
//创建日志记录组件实例
|
return new Hashtable();
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region ExecuteProductData(string productName, params SqlParameter[] parameters) 执行存储过程返回DataTable
|
/// <summary>
|
/// 执行存储过程返回返回值
|
/// </summary>
|
/// <param name="productName">存储过程名字</param>
|
/// <param name="parameters">SqlParameter 参数</param>
|
/// <returns>返回值Hash表</returns>
|
public static DataTable ExecuteProductData(string productName, params SqlParameter[] parameters)
|
{
|
DataOperator dbo = null;
|
//ParamCollections pc = new ParamCollections();
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.ExecuteProductData(productName, parameters);
|
}
|
catch (Exception ex)
|
{
|
//创建日志记录组件实例
|
LogHelper.WriteLog(ex);
|
throw new Exception(ex.Message);
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region ExecuteProductData(string productName, params SqlParameter[] parameters) 执行存储过程返回List<DataTable>
|
/// <summary>
|
/// 执行存储过程返回返回值
|
/// </summary>
|
/// <param name="productName">存储过程名字</param>
|
/// <param name="parameters">SqlParameter 参数</param>
|
/// <returns>返回值Hash表</returns>
|
public static List<DataTable> ExecuteProductDataList(string productName, params SqlParameter[] parameters)
|
{
|
DataOperator dbo = null;
|
//ParamCollections pc = new ParamCollections();
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.ExecuteProductDataList(productName, parameters);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.WriteLog(ex);
|
throw new Exception(ex.Message);
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region ExecuteProductData(string productName, params SqlParameter[] parameters) 执行存储过程返回ArrayList
|
/// <summary>
|
/// 执行存储过程返回返回值
|
/// </summary>
|
/// <param name="productName">存储过程名字</param>
|
/// <param name="parameters">SqlParameter 参数</param>
|
/// <returns>返回值List</returns>
|
public static ArrayList executeProductList(string produreName, bool returnDt, params SqlParameter[] parameters)
|
{
|
DataOperator dbo = null;
|
//ParamCollections pc = new ParamCollections();
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.executeProduct(produreName, parameters, returnDt);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.WriteLog(ex);
|
throw new Exception(ex.Message);
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region ExecuteProductData(string productName) 执行存储过程返回bool
|
/// <summary>
|
/// 执行存储过程返回返回值
|
/// </summary>
|
/// <param name="productName">存储过程名字</param>
|
/// <param name="parameters">SqlParameter 参数</param>
|
/// <returns>返回值DataTable表</returns>
|
public static bool ExecuteProductbool(string productName, params SqlParameter[] parameters)
|
{
|
DataOperator dbo = null;
|
//ParamCollections pc = new ParamCollections();
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
return dbo.ExecuteProduct(productName, parameters);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.WriteLog(ex);
|
throw (ex);
|
//创建日志记录组件实例
|
|
}
|
finally
|
{
|
if (dbo != null)
|
{
|
dbo.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region GetDataByPage() 分页查询
|
/// <summary>
|
/// 分页查询
|
/// </summary>
|
/// <param name="filename">表名</param>
|
/// <param name="filename">需要返回的列</param>
|
/// <param name="sortfilename">排序字段名</param>
|
/// <param name="PageSize">页尺寸</param>
|
/// <param name="PageIndex">页码</param>
|
/// <param name="OrderType">设置排序类型, 非 0 值则降序</param>
|
/// <param name="strWhere">查询条件 (注意: 不要加 where)</param>
|
/// <param name="RecordCount">总记录</param>
|
/// <returns></returns>
|
public static DataTable GetDataByPage(string tablename, string filename, string sortfilename, int PageSize, int PageIndex, int OrderType, string strWhere, out int RecordCount)
|
{
|
RecordCount = 0;
|
//创建参数
|
SqlParameter[] param = {
|
new SqlParameter("@tblName", SqlDbType.VarChar, 50),
|
new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),
|
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
|
new SqlParameter("@PageSize", SqlDbType.Int),
|
new SqlParameter("@PageIndex", SqlDbType.Int),
|
new SqlParameter("@OrderType", SqlDbType.Bit,1),
|
new SqlParameter("@strWhere", SqlDbType.VarChar,500),
|
new SqlParameter("@RecordCount", SqlDbType.Int)
|
};
|
param[0].Value = tablename;
|
param[1].Value = filename;
|
param[2].Value = sortfilename;
|
param[3].Value = PageSize;
|
param[4].Value = PageIndex;
|
param[5].Value = OrderType;
|
param[6].Value = strWhere;
|
param[7].Direction = ParameterDirection.Output;
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
DataTable dt = dbo.ExecuteProductData("GetDataByPage", param);
|
RecordCount = (int)param[7].Value;
|
return dt;
|
}
|
catch (Exception ex)
|
{
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
if (null != dbo)
|
{
|
dbo.Close();
|
dbo = null;
|
}
|
}
|
}
|
/// <summary>
|
/// 分页查询(支持JOIN)
|
/// </summary>
|
/// <param name="filename">表名</param>
|
/// <param name="filename">需要返回的列</param>
|
/// <param name="sortfilename">排序字段名(默认升序，降序需在排序字段名后面DESC关键字：sortField Desc)</param>
|
/// <param name="PageSize">页尺寸</param>
|
/// <param name="PageIndex">页码</param>
|
/// <param name="strWhere">查询条件 (注意: 不要加 where)</param>
|
/// <param name="RecordCount">总记录</param>
|
/// <returns></returns>
|
public static DataTable GetDataByPage_V2(string tablename, string filename, string sortfilename, int PageSize, int PageIndex, string strWhere, out int RecordCount)
|
{
|
RecordCount = 0;
|
//创建参数
|
SqlParameter[] param = {
|
new SqlParameter("@tblName", SqlDbType.VarChar, 200),
|
new SqlParameter("@strGetFields", SqlDbType.VarChar, 500),
|
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
|
new SqlParameter("@PageSize", SqlDbType.Int),
|
new SqlParameter("@PageIndex", SqlDbType.Int),
|
new SqlParameter("@strWhere", SqlDbType.VarChar,500),
|
new SqlParameter("@RecordCount", SqlDbType.Int),
|
new SqlParameter("@TotalPage", SqlDbType.Int)
|
};
|
param[0].Value = tablename;//表名
|
param[1].Value = filename;//需要返回的列
|
param[2].Value = sortfilename;//排序字段名
|
param[3].Value = PageSize;//页尺寸
|
param[4].Value = PageIndex;//页码
|
param[5].Value = strWhere;//查询条件
|
param[6].Direction = ParameterDirection.ReturnValue;//总记录
|
param[7].Direction = ParameterDirection.Output;//总页数
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
DataTable dt = dbo.ExecuteProductData("GetDataByPage_V2", param);
|
RecordCount = (int)param[6].Value;
|
return dt;
|
}
|
catch (Exception ex)
|
{
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
if (null != dbo)
|
{
|
dbo.Close();
|
dbo = null;
|
}
|
}
|
}
|
/// <summary>
|
/// 分页查询(支持JOIN)
|
/// </summary>
|
/// <param name="filename">表名</param>
|
/// <param name="filename">需要返回的列</param>
|
/// <param name="sortfilename">排序字段名(默认升序，降序需在排序字段名后面DESC关键字：sortField Desc)</param>
|
/// <param name="PageSize">页尺寸</param>
|
/// <param name="PageIndex">页码</param>
|
/// <param name="strWhere">查询条件 (注意: 不要加 where)</param>
|
/// <param name="RecordCount">总记录</param>
|
/// <returns></returns>
|
public static DataTable GetDataByPage_V2_Join(string tablename, string filename, string sortfilename, int PageSize, int PageIndex, string strWhere, out int RecordCount)
|
{
|
RecordCount = 0;
|
//创建参数
|
SqlParameter[] param = {
|
new SqlParameter("@tblName", SqlDbType.VarChar, 200),
|
new SqlParameter("@strGetFields", SqlDbType.VarChar, 500),
|
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
|
new SqlParameter("@PageSize", SqlDbType.Int),
|
new SqlParameter("@PageIndex", SqlDbType.Int),
|
new SqlParameter("@strWhere", SqlDbType.VarChar,500),
|
new SqlParameter("@RecordCount", SqlDbType.Int),
|
new SqlParameter("@TotalPage", SqlDbType.Int)
|
};
|
param[0].Value = tablename;//表名
|
param[1].Value = filename;//需要返回的列
|
param[2].Value = sortfilename;//排序字段名
|
param[3].Value = PageSize;//页尺寸
|
param[4].Value = PageIndex;//页码
|
param[5].Value = strWhere;//查询条件
|
param[6].Direction = ParameterDirection.ReturnValue;//总记录
|
param[7].Direction = ParameterDirection.Output;//总页数
|
DataOperator dbo = null;
|
try
|
{
|
if (dbo == null)
|
{
|
dbo = new DataOperator();
|
}
|
DataTable dt = dbo.ExecuteProductData("GetDataByPage_V2_Join", param);
|
RecordCount = (int)param[6].Value;
|
return dt;
|
}
|
catch (Exception ex)
|
{
|
LogHelper.WriteLog(ex);
|
throw ex;
|
}
|
finally
|
{
|
if (null != dbo)
|
{
|
dbo.Close();
|
dbo = null;
|
}
|
}
|
}
|
#endregion
|
|
#region ParametersStrGet(SqlParameter[] parameters) [用于日志文本输出] 将SqlParameter数组参数转成字符串.
|
/// <summary>
|
/// [用于日志文本输出] 将SqlParameter数组参数转成字符串.
|
/// </summary>
|
/// <param name="parameters">参数数组</param>
|
/// <returns></returns>
|
private static string ParametersStrGet(SqlParameter[] parameters)
|
{
|
string parametersHeadStr = @"参数:{{{0}}}";
|
string parametersContentStr = "";
|
int count = 0;
|
foreach (SqlParameter sp in parameters)
|
{
|
if (count++ > 0)
|
parametersContentStr += @",";
|
parametersContentStr += string.Format("{0}:{1}", sp.ParameterName, sp.Value);
|
}
|
return string.Format(parametersHeadStr, parametersContentStr);
|
}
|
#endregion
|
|
|
}
|
}
|
