/// <summary>
/// 第一行第一列 可能为null
/// </summary>
/// <param name="pDbHelpParam">DbHelp 接收参数专用类</param>
/// <returns></returns>
public static object Scalar(IDbHelpParam pDbHelpParam)
{
IDbConnection conn = pDbHelpParam.DbProvider.NewConnection();
try
{
using (IDbCommand cmd = conn.CreateCommand())
{
SetCmd(pDbHelpParam, cmd, conn);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
cmd.Dispose();
return(val);
}
}
catch (Exception ex)
{
pDbHelpParam.DbProvider.Dispose(conn);
throw new Exception(ex.Message + "Scalar sql:" + pDbHelpParam.SQL);
}
finally
{
pDbHelpParam.DbProvider.Dispose(conn);
}
}
/// <summary>
///
/// </summary>
/// <param name="pDbHelpParam">DbHelp 接收参数专用类</param>
/// <returns></returns>
public static DataTable GetDataTable(IDbHelpParam pDbHelpParam)
{
IDbConnection conn = pDbHelpParam.DbProvider.NewConnection();
try
{
using (IDbCommand cmd = conn.CreateCommand())
{
DataTable dt = new DataTable();
DbDataAdapter sda;
SetCmd(pDbHelpParam, cmd, conn);
sda = pDbHelpParam.DbProvider.NewDataAdapter();
sda.SelectCommand = (DbCommand)cmd;
sda.Fill(dt);
//HttpContext.Current.Response.Write(((System.Data.SqlClient.SqlCommand)cmd).Parameters["@PageCount"].Value.ToString());
cmd.Parameters.Clear();
return(dt);
}
}
catch (Exception ex)
{
pDbHelpParam.DbProvider.Dispose(conn);
throw new Exception(ex.Message + "GetDataTable sql:" + pDbHelpParam.SQL);
}
finally
{
pDbHelpParam.DbProvider.Dispose(conn);
}
}
/// <summary>
///
/// </summary>
/// <param name="pDbHelpParam">DbHelp 接收参数专用类</param>
/// <returns></returns>
public static IDataReader Read(IDbHelpParam pDbHelpParam)
{
IDbConnection conn = pDbHelpParam.DbProvider.NewConnection();
try
{
using (IDbCommand cmd = conn.CreateCommand())
{
SetCmd(pDbHelpParam, cmd, conn);
IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
cmd.Dispose();
return(rdr);
}
}
catch (Exception ex)
{
pDbHelpParam.DbProvider.Dispose(conn);
throw new Exception(ex.Message + "Read sql:" + pDbHelpParam.SQL);
}
finally
{
}
}
/// <summary>
///
/// </summary>
/// <param name="pDbHelpParam">DbHelp 接收参数专用类</param>
/// <returns></returns>
public static DataSet GetDataSet(IDbHelpParam pDbHelpParam)
{
IDbConnection conn = pDbHelpParam.DbProvider.NewConnection();
try
{
using (IDbCommand cmd = conn.CreateCommand())
{
DataSet ds = new DataSet();
DbDataAdapter sda;
SetCmd(pDbHelpParam, cmd, conn);
sda = pDbHelpParam.DbProvider.NewDataAdapter();
sda.SelectCommand = (DbCommand)cmd;
sda.Fill(ds);
cmd.Parameters.Clear();
cmd.Dispose();
return(ds);
}
}
catch (Exception ex)
{
pDbHelpParam.DbProvider.Dispose(conn);
throw new Exception(ex.Message + "GetDataSet sql:" + pDbHelpParam.SQL);
}
finally
{
pDbHelpParam.DbProvider.Dispose(conn);
}
}
/// <summary>
/// 事务处理
/// </summary>
/// <param name="pDbHelpParam">DbHelp 接收参数专用类</param>
/// <param name="pTrands">要处理事务的方法</param>
/// <param name="pObj">传参数</param>
public static void ExecuteTrans(IDbHelpParam pDbHelpParam, ExecuteTransHandler pTrands, ITransM pObj)
{
IDbConnection conn = pDbHelpParam.DbProvider.NewConnection();
try
{
using (IDbCommand cmd = conn.CreateCommand())
{
IDbTransaction trans;
SetCmd(pDbHelpParam, cmd, conn);
trans = conn.BeginTransaction();
cmd.Transaction = trans;
try
{
int returnTrands = pTrands(new TransHelp(cmd), pObj);//执行业务逻辑
if (returnTrands == 1)
{
trans.Commit();//提交事务
}
else
{
trans.Rollback();//回滚事务
}
}
catch (Exception ex)
{
try
{
trans.Rollback();//回滚事务
pObj.returnValue = "1:" + ex.Message;
}
catch (Exception exTrans)
{
pObj.returnValue = "2" + ex.Message + " " + exTrans.Message;
throw new Exception(ex.Message + " " + exTrans.Message + ":回滚事务出错");
}
}
cmd.Parameters.Clear();
}
}
catch (Exception ex)
{
pDbHelpParam.DbProvider.Dispose(conn);
throw new Exception(ex.Message + "Update sql:" + pDbHelpParam.SQL);
}
finally
{
pDbHelpParam.DbProvider.Dispose(conn);
}
}
public object Scalar(IDbHelpParam pDbHelpParam)
{
try
{
SetCmd(pDbHelpParam);
object val = cmd.ExecuteScalar();
return(val);
}
catch (Exception ex)
{
throw new Exception(ex.Message + "Scalar sql:" + pDbHelpParam.SQL);
}
}
public int Update(IDbHelpParam pDbHelpParam)
{
try
{
SetCmd(pDbHelpParam);
int val = cmd.ExecuteNonQuery();
return(val);
}
catch (Exception ex)
{
throw new Exception(ex.Message + "trans_Update sql:" + pDbHelpParam.SQL);
}
}
public IDataReader Read(IDbHelpParam pDbHelpParam)
{
try
{
SetCmd(pDbHelpParam);
IDataReader rdr = cmd.ExecuteReader();
return(rdr);
}
catch (Exception ex)
{
throw new Exception(ex.Message + "Read sql:" + pDbHelpParam.SQL);
}
}
private void SetCmd(IDbHelpParam pDbHelpParam)
{
cmd.Parameters.Clear();
cmd.CommandText = pDbHelpParam.SQL;
cmd.CommandType = pDbHelpParam.CmdType;
if (pDbHelpParam.ArrParm != null)
{
foreach (IDataParameter dp in pDbHelpParam.ArrParm)
{
cmd.Parameters.Add(dp);
}
}
}
public DataTable GetDataTable(IDbHelpParam pDbHelpParam)
{
try
{
DataTable dt = new DataTable();
DbDataAdapter sda;
SetCmd(pDbHelpParam);
sda = dbProvider.NewDataAdapter();
sda.SelectCommand = (DbCommand)cmd;
sda.Fill(dt);
return(dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message + "trans_GetDataTable sql:" + pDbHelpParam.SQL);
}
}
/// <summary>
/// 设置 DbCommand
/// </summary>
/// <param name="pDbHelpParam">DbHelp 接收参数专用类</param>
/// <param name="pCommand">IDbCommand</param>
/// <param name="pConn">IDbConnection</param>
private static void SetCmd(IDbHelpParam pDbHelpParam, IDbCommand pCommand, IDbConnection pConn)
{
if (pConn.State != ConnectionState.Open)
{
pConn.Open();
}
pCommand.Connection = pConn;
pCommand.CommandText = pDbHelpParam.SQL;
pCommand.CommandType = pDbHelpParam.CmdType;
if (pDbHelpParam.ArrParm != null)
{
foreach (IDataParameter dp in pDbHelpParam.ArrParm)
{
pCommand.Parameters.Add(dp);
}
}
}
public string First(IDbHelpParam pDbHelpParam, string pReturn)
{
string str;
using (IDataReader dr = Read(pDbHelpParam))
{
if (dr.Read())
{
str = dr[0].ToString();
}
else
{
str = pReturn;
}
if (str.Length == 0)
{
str = pReturn;
}
return(str);
}
}
/// <summary>
/// 用于分页 返回当前页的DataTable
/// </summary>
/// <param name="pDbHelpParam">DbHelp 接收参数专用类</param>
/// <param name="pPageIndex">当前页码</param>
/// <param name="pPageSize">每页记录数</param>
/// <returns></returns>
public static DataTable GetDataTable_Paging(IDbHelpParam pDbHelpParam, int pPageIndex, int pPageSize)
{
IDbConnection conn = pDbHelpParam.DbProvider.NewConnection();
try
{
using (IDbCommand cmd = conn.CreateCommand())
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
DbDataAdapter sda;
SetCmd(pDbHelpParam, cmd, conn);
sda = pDbHelpParam.DbProvider.NewDataAdapter();
sda.SelectCommand = (DbCommand)cmd;
if (pPageIndex < 1)
{
pPageIndex = 1;
}
int startRecord = (pPageIndex - 1) * pPageSize;
sda.Fill(ds, startRecord, pPageSize, "0");
dt = ds.Tables[0];
cmd.Parameters.Clear();
cmd.Dispose();
return(dt);
}
}
catch (Exception ex)
{
pDbHelpParam.DbProvider.Dispose(conn);
throw new Exception(ex.Message + "GetDataTable_Paging sql:" + pDbHelpParam.SQL);
}
finally
{
pDbHelpParam.DbProvider.Dispose(conn);
}
}
/// <summary>
/// 插入,更新或删除
/// </summary>
/// <param name="pDbHelpParam">DbHelp 接收参数专用类</param>
/// <returns></returns>
public int Update(IDbHelpParam pDbHelpParam)
{
IDbConnection conn = pDbHelpParam.DbProvider.NewConnection();
try
{
using (IDbCommand cmd = conn.CreateCommand())
{
SetCmd(pDbHelpParam, cmd, conn);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return(val);
}
}
catch (Exception ex)
{
pDbHelpParam.DbProvider.Dispose(conn);
throw new Exception(ex.Message + "Update sql:" + pDbHelpParam.SQL);
}
finally
{
pDbHelpParam.DbProvider.Dispose(conn);
}
}
public void ExecuteTrans(IDbHelpParam pDbHelpParam, DbHelp.ExecuteTransHandler pTrands, WZ.Common.DbHelp.ITransM pObj)
{
throw new NotImplementedException("事务不能嵌套使用");
}
public SqlDataSelect(IDbHelpParam param, IDbHelp thelp)
{
this.dtInfo = thelp.GetDataTable(param);
}
public DataTable GetDataTable_Paging(IDbHelpParam pDbHelpParam, int pPageIndex, int pPageSize)
{
throw new NotImplementedException("事务中无分页功能");
}
