/// <summary>
/// 检查是否存在 (count)
/// </summary>
public static bool Exists(this StringBuilder sql, ParCollection prams)
{
using (DBDalOperate dal = new DBDalOperate())
{
return(dal.Exists(sql, prams));
}
}
/// <summary>
/// 执行查询存储过程[带输入参数]
/// </summary>
/// <param name="procName"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static DataSet SelectProc(string procName, ParCollection parms = null)
{
using (DBDalOperate dal = new DBDalOperate())
{
return(dal.ExecProcQuery(procName, parms));
}
}
/// <summary>
/// 获取第一个行第一列结果
/// </summary>
public static object GetSingle(this StringBuilder sql, ParCollection prams)
{
using (DBDalOperate dal = new DBDalOperate())
{
return(dal.GetSingle(sql, prams));
}
}
/// <summary>
/// 执行查询的sql语句[带输入参数]
/// </summary>
/// <param name="sql"></param>
/// <param name="prams"></param>
/// <returns></returns>
public static DataSet Select(this StringBuilder sql, ParCollection prams = null)
{
using (DBDalOperate dal = new DBDalOperate())
{
return(dal.ExecSqlQuery(sql, prams));
}
}
/// <summary>
/// 执行数据更新的sql语句[带输入参数]
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public static bool Sql(this StringBuilder sql, ParCollection prams = null)
{
using (DBDalOperate dal = new DBDalOperate())
{
return(dal.ExecSqlNoQuery(sql, prams) > 0 ? true : false);
}
}
/// <summary>
/// 执行查询的存储过程[带输入参数]
/// </summary>
/// <param name="storeProcName"></param>
/// <param name="prams"></param>
/// <returns></returns>
public DataSet ExecProcQuery(string storeProcName, ParCollection prams = null)
{
try
{
base.myAdapter = new SqlDataAdapter(storeProcName, DBDalBase.myConnectionString);
base.myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
base.myAdapter.SelectCommand.Parameters.Add(parameter);
}
}
}
base.myDataSet = new DataSet();
base.myAdapter.Fill(base.myDataSet);
}
catch (Exception ex)
{
using (LogMethod lM = new LogMethod())
{
lM.WriteLog(ex.Message, ex);
}
throw new Exception(ex.Message, ex);
}
finally
{
base.DataAdapterClose();
}
return(base.myDataSet);
}
/// <summary>
/// 获取对象集合
/// </summary>
public static List <T> GetList <T>(this StringBuilder sql, ParCollection prams = null)
{
using (DBDalOperate dal = new DBDalOperate())
{
return(dal.GetList <T>(sql, prams));
}
}
/// <summary>
/// 执行查询的存储过程[带输入参数]
/// </summary>
/// <param name="storeProcName"></param>
/// <param name="prams"></param>
/// <returns></returns>
public void ExecProcQueryNoReutrn(string storeProcName, ParCollection prams, string paramName, out string msg)
{
try
{
base.myAdapter = new SqlDataAdapter(storeProcName, DBDalBase.myConnectionString);
base.myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
if (prams != null || prams.Count > 0)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
base.myAdapter.SelectCommand.Parameters.Add(parameter);
}
}
}
base.myAdapter.SelectCommand.Parameters.Add(new SqlParameter(paramName, SqlDbType.NVarChar, 5000, ParameterDirection.Output, true, 0, 0, string.Empty, DataRowVersion.Default, DBNull.Value));
base.myDataSet = new DataSet();
base.myAdapter.Fill(base.myDataSet);
msg = (string)base.myAdapter.SelectCommand.Parameters[paramName].Value;
}
catch (Exception ex)
{
using (LogMethod lM = new LogMethod())
{
lM.WriteLog(ex.Message, ex);
}
throw new Exception(ex.Message, ex);
}
finally
{
base.DataAdapterClose();
}
}
public static List <T> GetProcList <T>(string Proc_Name, ParCollection prams = null)
{
using (DBDalOperate dal = new DBDalOperate())
{
return(dal.GetProcList <T>(Proc_Name, prams));
}
}
/// <summary>
/// 访问信息数据信息
/// 拓展
/// 记录当前设备、IP、时间、即微信号等相关信息
/// </summary>
/// <param name="ChannelID"></param>
/// <param name="AccountName"></param>
/// <returns></returns>
public DBResultInfo Request(string ChannelID)
{
ParCollection par = new ParCollection();
par.Add("@ChannelID", ChannelID);
return(DBHelper.GetProcModel <DBResultInfo>("adv_Save_Request", par));
}
public static List <T> GetProcList <T>(string Proc_Name, ParCollection prams, string outPutParamName, out string OutPutValue)
{
using (DBDalOperate dal = new DBDalOperate())
{
return(dal.GetProcList <T>(Proc_Name, prams, outPutParamName, out OutPutValue));
}
}
/// <summary>
/// 执行查询的存储过程[带输入参数],[带int型输出参数]
/// </summary>
/// <param name="storeProcName"></param>
/// <param name="prams"></param>
/// <param name="OutPutValue"></param>
/// <returns></returns>
public DataSet ExecProcQuery(string storeProcName, ParCollection prams, out int OutPutValue)
{
string strOutPutValue = string.Empty;
DataSet myData = ExecProcQuery(storeProcName, prams, "@outputvalue", out strOutPutValue);
OutPutValue = Convert.ToInt32(strOutPutValue);
return(myData);
}
/// <summary>
/// 提交复制数据信息
/// 拓展
/// 记录当前设备、IP、时间、即微信号等相关信息
/// </summary>
/// <param name="ChannelID"></param>
/// <param name="AccountName"></param>
/// <returns></returns>
public DBResultInfo PushData(string ChannelID, string AccountName)
{
ParCollection par = new ParCollection();
par.Add("@ChannelID", ChannelID)
.Add("@AccountName", AccountName);
return(DBHelper.GetProcModel <DBResultInfo>("adv_Save_PushData", par));
}
public static List <T> GetProcList <T>(string Proc_Name, out int OutPutValue, ParCollection prams = null)
{
using (DBDalOperate dal = new DBDalOperate())
{
return(dal.GetProcList <T>(Proc_Name, out OutPutValue, prams));
}
//ExecProcQuery(Proc_Name, prams, out OutPutValue).GetList<T>();
}
public void Add(int order, StringBuilder sql, ParCollection pars = null)
{
base.Add(new TranMdl()
{
Order = order,
Sql = sql,
Pars = pars
});
}
public AutoPar(List <PropertyInfo> plist = null, object o = null, string str = "")
{
strSql = new StringBuilder();
strSql.Append(str);
if (plist == null)
{
plist = new List <PropertyInfo>();
}
proList = plist;
oMdl = o;
pars = new ParCollection();
}
/// <summary>
/// 检查是否存在
/// </summary>
/// <param name="sql"></param>
/// <param name="prams"></param>
/// <returns></returns>
public bool Exists(StringBuilder sql, ParCollection prams)
{
object objResult = "";
try
{
base.ConnectionOpen();
base.myCommand = new SqlCommand(sql.ToString(), base.myConnection);
if (prams != null)
{
foreach (SqlParameter paremeter in prams)
{
if (paremeter != null)
{
base.myCommand.Parameters.Add(paremeter);
}
}
}
objResult = base.myCommand.ExecuteScalar();
}
catch (Exception ex)
{
using (LogMethod lM = new LogMethod())
{
lM.WriteLog(ex.Message, ex);
}
throw new Exception(ex.Message, ex);
}
finally
{
base.CommandClose();
base.ConnectionClose();
}
int cmdresult;
if ((Object.Equals(objResult, null)) || (Object.Equals(objResult, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(objResult.ToString());
}
if (cmdresult == 0)
{
return(false);
}
else
{
return(true);
}
}
public int GetSingleForCount(StringBuilder sql, ParCollection prams)
{
var obj = GetSingle(sql, prams);
if (obj != null && !string.IsNullOrEmpty(obj.ToString()))
{
return(int.Parse(obj.ToString()));
}
else
{
return(0);
}
}
/// <summary>
/// 执行查询的存储过程[带输入参数],[输出参数自定义]
/// </summary>
/// <param name="storeProcName">存储过程名称</param>
/// <param name="prams">参数集合</param>
/// <param name="outPubParamName">输出参数名称，必须带@</param>
/// <param name="OutPutValue">输出参数</param>
/// <returns></returns>
public DataSet ExecProcQuery(string storeProcName, ParCollection prams, string outPubParamName, out string OutPutValue)
{
try
{
base.myAdapter = new SqlDataAdapter(storeProcName, DBDalBase.myConnectionString);
base.myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
base.myAdapter.SelectCommand.Parameters.Add(parameter);
}
}
}
base.myAdapter.SelectCommand.Parameters.Add(new SqlParameter(outPubParamName, SqlDbType.NVarChar, 500, ParameterDirection.Output, true, 0, 0, string.Empty, DataRowVersion.Default, DBNull.Value));
base.myDataSet = new DataSet();
base.myAdapter.Fill(base.myDataSet);
object objParamVal = base.myAdapter.SelectCommand.Parameters[outPubParamName].Value;
OutPutValue = string.Empty;
if (objParamVal != DBNull.Value)
{
OutPutValue = objParamVal.ToString();
}
}
catch (Exception ex)
{
using (LogMethod lM = new LogMethod())
{
lM.WriteLog(ex.Message, ex);
}
throw new Exception(ex.Message, ex);
}
finally
{
base.DataAdapterClose();
}
return(base.myDataSet);
}
/// <summary>
/// 执行查询的sql语句[带输入参数]
/// </summary>
/// <param name="sql"></param>
/// <param name="prams"></param>
/// <returns></returns>
public DataSet ExecSqlQuery(StringBuilder sql, ParCollection prams = null)
{
try
{
base.myAdapter = new SqlDataAdapter(sql.ToString(), DBDalBase.myConnectionString);
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
base.myAdapter.SelectCommand.Parameters.Add(parameter);
}
}
}
base.myDataSet = new DataSet();
base.myAdapter.Fill(base.myDataSet);
}
catch (Exception ex)
{
using (LogMethod lM = new LogMethod())
{
lM.WriteLog(ex.Message, ex);
}
throw new Exception(ex.Message, ex);
}
finally
{
base.DataAdapterClose();
}
if (base.myDataSet != null && base.myDataSet.Tables[0].Rows.Count == 0)
{
base.myDataSet.Dispose();
return(null);
}
return(base.myDataSet);
}
/// <summary>
/// 执行数据更新的sql语句[带输入参数]
/// </summary>
/// <param name="sql"></param>
/// <param name="parms"></param>
/// <returns></returns>
public int ExecSqlNoQuery(StringBuilder sql, ParCollection prams = null)
{
int intResult = 0;
try
{
base.ConnectionOpen();
base.myCommand = new SqlCommand(sql.ToString(), base.myConnection);
if (prams != null)
{
foreach (SqlParameter paremeter in prams)
{
if (paremeter != null)
{
base.myCommand.Parameters.Add(paremeter);
}
}
}
intResult = base.myCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
intResult = -1000;
using (LogMethod lM = new LogMethod())
{
lM.WriteLog(ex.Message, ex);
}
throw new Exception(ex.Message, ex);
}
finally
{
base.CommandClose();
base.ConnectionClose();
}
return(intResult);
}
/// <summary>
/// 分页查询 返回总数
/// </summary>
public static DataSet Paging(this StringBuilder strSql, string strOrderby, int iPageIndex, int iPageSize, ParCollection ltEx, out int iCount)
{
using (DBDalOperate dal = new DBDalOperate())
{
return(dal.Paging(strSql.ToString(), strOrderby, iPageIndex, iPageSize, out iCount, ltEx, true));
}
}
/// <summary>
/// 存储过程获取对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Proc_Name"></param>
/// <param name="prams"></param>
/// <returns></returns>
public T GetProcModel <T>(string Proc_Name, ParCollection prams = null)
{
return(ExecProcQuery(Proc_Name, prams).GetModel <T>());
}
public List <T> GetProcList <T>(string Proc_Name, ParCollection prams = null)
{
return(ExecProcQuery(Proc_Name, prams).GetList <T>());
}
public DataSet Paging(StringBuilder strSql, string strOrderby, int iPageIndex, int iPageSize, ParCollection ltEx = null)
{
int iCount = 0;
return(Paging(strSql.ToString(), strOrderby, iPageIndex, iPageSize, out iCount, ltEx, false));
}
public AutoPar AddPars(ParCollection pars)
{
this.pars.AddRange(pars);
return(this);
}
public DataSet Paging(StringBuilder strSql, string strOrderby, int iPageIndex, int iPageSize, ParCollection ltEx, out int iCount)
{
return(Paging(strSql.ToString(), strOrderby, iPageIndex, iPageSize, out iCount, ltEx, true));
}
public List <T> GetList <T>(StringBuilder sql, ParCollection prams = null)
{
return(ExecSqlQuery(sql, prams).GetList <T>());
}
public List <T> GetProcList <T>(string Proc_Name, ParCollection prams, string outPutParamName, out string OutPutValue)
{
return(ExecProcQuery(Proc_Name, prams, outPutParamName, out OutPutValue).GetList <T>());
}
public DataSet Paging(string strSql, string strOrderby, int iPageIndex, int iPageSize, out int iCount, ParCollection ltEx = null, bool isGetCount = true)
{
try
{
iCount = 0;
int iBegin = (iPageIndex - 1) * iPageSize + 1;
int iEnd = iPageIndex * iPageSize;
//去除select
var hasSelect = strSql.IndexOf("select", 0, 20, StringComparison.CurrentCultureIgnoreCase);
if (hasSelect != -1)
{
strSql = strSql.Remove(hasSelect, 6);
}
StringBuilder strCount = new StringBuilder();
if (isGetCount)
{
//统计总数
strCount.AppendFormat(" Select @OutputCount=Count(*) {0} ;", strSql.Substring(strSql.IndexOf(" from", StringComparison.CurrentCultureIgnoreCase)));
}
StringBuilder strSelect = new StringBuilder();
strSelect.AppendFormat(SQL_PAGE, strOrderby, strSql);
List <SqlParameter> ltpar = new List <SqlParameter>();
ltpar.Add(new SqlParameter("@Begin", SqlDbType.Int)
{
Value = iBegin
});
ltpar.Add(new SqlParameter("@End", SqlDbType.Int)
{
Value = iEnd
});
if (ltEx != null)
{
ltpar.AddRange(ltEx);
}
if (isGetCount)
{
ltpar.Add(new SqlParameter("@OutputCount", SqlDbType.Int)
{
Direction = ParameterDirection.Output
});
}
base.myAdapter = new SqlDataAdapter(strSelect.Append(strCount).ToString(), DBDalBase.myConnectionString);
base.myAdapter.SelectCommand.CommandType = CommandType.Text;
base.myAdapter.SelectCommand.Parameters.AddRange(ltpar.ToArray());
base.myDataSet = new DataSet();
base.myAdapter.Fill(base.myDataSet);
iCount = isGetCount ? (int)base.myAdapter.SelectCommand.Parameters["@OutputCount"].Value : 0;
}
catch (Exception ex)
{
using (LogMethod lM = new LogMethod())
{
lM.WriteLog(ex.Message, ex);
}
throw new Exception(ex.Message, ex);
}
finally
{
base.DataAdapterClose();
}
return(base.myDataSet);
}
