/// <summary>
/// 删除指定角色编号的角色权限关系
/// </summary>
/// <param name="roleID">
/// 角色编号
/// </param>
/// <param name="transaction">
/// 事务对象
/// </param>
public void DeleteByID(int roleID, out SqlTransaction transaction)
{
if (roleID <= 0)
{
throw new ArgumentNullException("roleID");
}
var parameters = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"RoleID",
SqlDbType.Int,
roleID,
ParameterDirection.Input)
};
try
{
this.SqlServer.BeginTransaction();
transaction = this.SqlServer.Transaction;
this.SqlServer.ExecuteNonQuery(CommandType.StoredProcedure, "sp_System_Role_Permission_DeleteRow", parameters, transaction);
}
catch (Exception exception)
{
throw new Exception(exception.Message, exception);
}
}
/// <summary>
/// 批量插入订单商品促销信息
/// </summary>
/// <param name="productPromotes">促销信息列表</param>
/// <param name="transaction">数据库事务</param>
/// <returns>插入的记录数</returns>
public int BatchInsert(List<Order_Product_Promote> productPromotes, SqlTransaction transaction)
{
if (productPromotes != null && productPromotes.Count > 0)
{
var dt = this.BuildDataTable(productPromotes);
var paramsList = new List<SqlParameter>
{
new SqlParameter("OPP", SqlDbType.Structured)
{
TypeName =
"[dbo].OrderProductPromoteTable",
Value = dt,
Direction =
ParameterDirection
.Input
},
this.SqlServer.CreateSqlParameter(
"Count",
SqlDbType.Int,
null,
ParameterDirection.Output)
};
this.SqlServer.ExecuteNonQuery(
CommandType.StoredProcedure,
"sp_Order_Product_Promote_BatchInsert",
paramsList,
transaction);
return (int)paramsList.Find(parameter => parameter.ParameterName == "Count").Value;
}
throw new NotImplementedException();
}
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] parms)
{
if (connection.State != ConnectionState.Open) connection.Open();
command.Connection = connection;
command.CommandTimeout = CommandTimeOut;
// 设置命令文本(存储过程名或SQL语句)
command.CommandText = commandText;
// 分配事务
if (transaction != null)
{
command.Transaction = transaction;
}
// 设置命令类型.
command.CommandType = commandType;
if (parms != null && parms.Length > 0)
{
//预处理SqlParameter参数数组，将为NULL的参数赋值为DBNull.Value;
foreach (SqlParameter parameter in parms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
}
command.Parameters.AddRange(parms);
}
}
/// <summary>
/// 添加满额优惠活动范围.
/// </summary>
/// <param name="promoteMeetMoneyScope">
/// Promote_MeetMoney_Scope 的对象实例.
/// </param>
/// <param name="transaction">
/// 数据事务.
/// </param>
/// <returns>
/// 满额优惠活动范围编号.
/// </returns>
public int Insert(Promote_MeetMoney_Scope promoteMeetMoneyScope, SqlTransaction transaction)
{
if (promoteMeetMoneyScope == null)
{
throw new ArgumentNullException("promoteMeetMoneyScope");
}
var parameters = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"MeetMoneyID",
SqlDbType.Int,
promoteMeetMoneyScope.MeetMoneyID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"Scope",
SqlDbType.VarChar,
promoteMeetMoneyScope.Scope,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"ReferenceID",
SqlDbType.Int,
null,
ParameterDirection.Output)
};
this.SqlServer.ExecuteNonQuery(
CommandType.StoredProcedure,
"sp_Promote_MeetMoney_Scope_Insert",
parameters,
null);
return (int)parameters.Find(parameter => parameter.ParameterName == "ReferenceID").Value;
}
public bool BeginTransaction()
{
try
{
if (m_trnTransaction == null)
{
if (m_conConnection == null)
{
m_conConnection = new SqlConnection(DataSupport.NewConnection());
}
if (m_conConnection.State == ConnectionState.Closed)
{
m_conConnection.Open();
}
m_trnTransaction = m_conConnection.BeginTransaction();
}
return true;
}
catch (SqlException)
{
return false;
}
catch (Exception)
{
return false;
}
}
public DataTable getVector(string SQL)
{
DataTable dt = new DataTable();
try
{
if (_DBType.Equals("SQL"))
{
tx_Sql = cn_Sql.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn_Sql;
cmd.CommandText = SQL;
cmd.CommandType = CommandType.Text;
cmd.Transaction = tx_Sql;
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
tx_Sql.Commit();
return dt;
}
}
catch (Exception e)
{
}
return null;
}
protected void BtnRegistro_Click(object sender, EventArgs e)
{
using (SqlCommand reg = new SqlCommand())
{
try
{
conexion.Open();
transacion = conexion.BeginTransaction();
reg.Connection = conexion;
reg.CommandText = "insert into dbo.Usuario values (NIF,nombre,apellidos,direccion,localidad,provincia)";
reg.Parameters.Add("NIF", System.Data.SqlDbType.NChar, 10);
reg.Parameters["NIF"].Value = TBNIF.Text;
reg.Parameters.Add("nombre", System.Data.SqlDbType.VarChar, 50);
reg.Parameters["nombre"].Value = TBNombre.Text;
reg.Parameters.Add("apellidos", System.Data.SqlDbType.VarChar, 100);
reg.Parameters["apellidos"].Value = TBApe.Text;
reg.Parameters.Add("localidad", System.Data.SqlDbType.VarChar, 50);
reg.Parameters["localidad"].Value = TBLoc.Text;
reg.Parameters.Add("direccion", System.Data.SqlDbType.VarChar, 100);
reg.Parameters["direccion"].Value = TBDir.Text;
reg.Parameters.Add("provincia", System.Data.SqlDbType.VarChar, 50);
reg.Parameters["provincia"].Value = TBProv.Text;
reg.Transaction = transacion;
reg.ExecuteNonQuery();
}
catch (SqlException ex) {
}
finally
{
conexion.Close();
}
}
}
/// <summary>
/// 插入订单商品促销信息
/// </summary>
/// <param name="orderProductPromote">插入的对象</param>
/// <param name="transaction">数据库事务</param>
/// <returns>返回新增的数据编码</returns>
public int Insert(Order_Product_Promote orderProductPromote, SqlTransaction transaction)
{
/*
[OrderID]
,[OrderProductID]
,[PromoteType]
,[PromoteID]
,[Remark]
,[ExtField]
,[CreateTime]
*/
var paras = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"OrderID",
SqlDbType.Int,
orderProductPromote.OrderID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"OrderProductID",
SqlDbType.Int,
orderProductPromote.OrderProductID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"PromoteType",
SqlDbType.Int,
orderProductPromote.PromoteType,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"PromoteID",
SqlDbType.Int,
orderProductPromote.PromoteID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"Remark",
SqlDbType.NVarChar,
orderProductPromote.Remark,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"ExtField",
SqlDbType.NVarChar,
orderProductPromote.ExtField,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"CreateTime",
SqlDbType.DateTime,
DateTime.Now,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"ReferenceID",
SqlDbType.Int,
null,
ParameterDirection.Output)
};
this.SqlServer.ExecuteNonQuery(CommandType.StoredProcedure, "sp_Order_Product_Promote_Insert", paras, transaction);
return (int)paras.Find(p => p.ParameterName == "ReferenceID").Value;
}
/// <summary>
/// 插入一条订单状态跟踪信息
/// </summary>
/// <param name="orderStatusTracking">
/// 订单状态跟踪对象
/// </param>
/// <param name="transaction">
/// 数据库事务对象
/// </param>
/// <returns>
/// 新增的记录编码
/// </returns>
public int Insert(Order_Status_Tracking orderStatusTracking, SqlTransaction transaction)
{
var paras = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"EmployeeID",
SqlDbType.Int,
orderStatusTracking.EmployeeID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"UserID",
SqlDbType.Int,
orderStatusTracking.UserID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"OrderID",
SqlDbType.Int,
orderStatusTracking.OrderID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"Remark",
SqlDbType.NVarChar,
orderStatusTracking.Remark,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"ExpressNumber",
SqlDbType.NVarChar,
orderStatusTracking.ExpressNumber,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"MailNo",
SqlDbType.NVarChar,
orderStatusTracking.MailNo,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"Status",
SqlDbType.Int,
orderStatusTracking.Status,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"CreateTime",
SqlDbType.DateTime,
DateTime.Now,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"ReferenceID",
SqlDbType.Int,
null,
ParameterDirection.Output)
};
this.SqlServer.ExecuteNonQuery(
CommandType.StoredProcedure,
"sp_Order_Status_Tracking_Insert",
paras,
transaction);
return (int)paras.Find(parameter => parameter.ParameterName == "ReferenceID").Value;
}
/// <summary>
/// 插入一条CPS记录
/// </summary>
/// <param name="linkRecord">记录对象</param>
/// <param name="transaction"></param>
/// <returns>新增的ID</returns>
public int Insert(Cps_LinkRecord linkRecord, SqlTransaction transaction)
{
//Create Procedure sp_Cps_LinkRecord_Insert
// @CpsID int,
// @URL nvarchar(1024),
// @TargetURL nvarchar(4000),
// @CreateTime datetime,
// @IsDelete int=default,
// @ExtField nvarchar(50)=default,
// @ReferenceID int output
//As
var paras = new List<SqlParameter>
{
this.sqlServer.CreateSqlParameter(
"CpsID",
SqlDbType.Int,
linkRecord.CpsID,
ParameterDirection.Input),
this.sqlServer.CreateSqlParameter(
"URL",
SqlDbType.NVarChar,
linkRecord.URL,
ParameterDirection.Input),
this.sqlServer.CreateSqlParameter(
"TargetURL",
SqlDbType.NVarChar,
linkRecord.TargetURL,
ParameterDirection.Input),
this.sqlServer.CreateSqlParameter(
"CreateTime",
SqlDbType.DateTime,
DateTime.Now,
ParameterDirection.Input),
this.sqlServer.CreateSqlParameter(
"IsDelete",
SqlDbType.Int,
0,
ParameterDirection.Input),
this.sqlServer.CreateSqlParameter(
"ExtField",
SqlDbType.NVarChar,
linkRecord.ExtField,
ParameterDirection.Input),
this.sqlServer.CreateSqlParameter(
"ReferenceID",
SqlDbType.Int,
null,
ParameterDirection.Output)
};
this.sqlServer.ExecuteNonQuery(CommandType.StoredProcedure, "sp_Cps_LinkRecord_Insert", paras, transaction);
return (int)paras.Find(p => p.ParameterName == "ReferenceID").Value;
}
/// <summary>
/// 新增一条订单状态日志
/// </summary>
/// <param name="orderStatusLog">
/// 订单状态日志
/// </param>
/// <param name="transaction">
/// 数据库事务对象
/// </param>
/// <returns>
/// 新增的订单状态日子编码
/// </returns>
public int Insert(Order_Status_Log orderStatusLog, SqlTransaction transaction)
{
/*
Create Procedure sp_Order_Status_Log_Insert
@OrderID int,
@EmployeeID int,
@Status int,
@Remark nvarchar(512),
@CreateTime datetime,
@ReferenceID int output
As
*/
var paras = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"OrderID",
SqlDbType.Int,
orderStatusLog.OrderID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"EmployeeID",
SqlDbType.Int,
orderStatusLog.EmployeeID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"Status",
SqlDbType.Int,
orderStatusLog.Status,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"Remark",
SqlDbType.NVarChar,
orderStatusLog.Remark,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"CreateTime",
SqlDbType.DateTime,
DateTime.Now,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"ReferenceID",
SqlDbType.Int,
null,
ParameterDirection.Output)
};
this.SqlServer.ExecuteNonQuery(CommandType.StoredProcedure, "sp_Order_Status_Log_Insert", paras, transaction);
return (int)paras.Find(e => e.ParameterName == "ReferenceID").Value;
}
/// <summary>
/// 写入HWERP 回写日志信息
/// </summary>
/// <param name="log"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public int InertHwUpdateLog(Hw_Log log, SqlTransaction transaction)
{
/*@Number nvarchar(50),
@Content ntext,
@State tinyint,
@CreateTime datetime,
@ExtField nvarchar(50),
*/
var paras = new List<SqlParameter>
{
this.sqlServer.CreateSqlParameter(
"Number",
SqlDbType.VarChar,
log.Number,
ParameterDirection.Input),
this.sqlServer.CreateSqlParameter(
"Content",
SqlDbType.NVarChar,
log.Content,
ParameterDirection.Input),
this.sqlServer.CreateSqlParameter(
"State",
SqlDbType.Int,
log.State,
ParameterDirection.Input),
this.sqlServer.CreateSqlParameter(
"ExtField",
SqlDbType.NVarChar,
log.ExtField,
ParameterDirection.Input),
this.sqlServer.CreateSqlParameter(
"CreateTime",
SqlDbType.DateTime,
DateTime.Now.ToLocalTime(),
ParameterDirection.Input),
this.sqlServer.CreateSqlParameter(
"ReferenceID",
SqlDbType.Int,
null,
ParameterDirection.Output)
};
this.sqlServer.ExecuteNonQuery(CommandType.StoredProcedure, "sp_hw_Log_Insert", paras, transaction);
return (int)paras.Find(p => p.ParameterName == "ReferenceID").Value;
}
/// <summary>
/// 删除指定的促销规则.
/// </summary>
/// <param name="meetAmountID">
/// 满件优惠活动促销规则编号.
/// </param>
/// <param name="transaction">
/// 数据事务.
/// </param>
public void DeleteByMeetAmountID(int meetAmountID, SqlTransaction transaction)
{
var parameters = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"PromoteMeetAmountID",
SqlDbType.Int,
meetAmountID,
ParameterDirection.Input)
};
this.SqlServer.ExecuteNonQuery(
CommandType.StoredProcedure,
"sp_Promote_MeetAmount_Rule_Brand_Delete",
parameters,
transaction);
}
/// <summary>
/// 删除指定的促销规则.
/// </summary>
/// <param name="id">
/// 满件优惠活动促销规则编号.
/// </param>
/// <param name="transaction">
/// 数据事务.
/// </param>
public void Delete(int id, SqlTransaction transaction)
{
var parameters = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"ID",
SqlDbType.Int,
id,
ParameterDirection.Input)
};
this.SqlServer.ExecuteNonQuery(
CommandType.StoredProcedure,
"sp_Promote_MeetAmount_Rule_Full_DeleteRow",
parameters,
transaction);
}
/// <summary>
/// 删除满足条件减现金促销规则.
/// </summary>
/// <param name="ruleID">
/// 满就送活动规则编号.
/// </param>
/// <param name="transaction">
/// 数据事务.
/// </param>
public void DeleteByRuleID(int ruleID, SqlTransaction transaction)
{
var parameters = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"MeetRuleID",
SqlDbType.Int,
ruleID,
ParameterDirection.Input)
};
this.SqlServer.ExecuteNonQuery(
CommandType.StoredProcedure,
"sp_Promote_Meet_DecreaseCash_Delete",
parameters,
transaction);
}
internal SqlInternalTransaction(SqlInternalConnection innerConnection, TransactionType type, SqlTransaction outerTransaction, long transactionId) {
Bid.PoolerTrace("<sc.SqlInternalTransaction.ctor|RES|CPOOL> %d#, Created for connection %d#, outer transaction %d#, Type %d\n",
ObjectID,
innerConnection.ObjectID,
(null != outerTransaction) ? outerTransaction.ObjectID : -1,
(int)type);
_innerConnection = innerConnection;
_transactionType = type;
if (null != outerTransaction) {
_parent = new WeakReference(outerTransaction);
}
_transactionId = transactionId;
RestoreBrokenConnection = false;
ConnectionHasBeenRestored = false;
}
/// <summary>
/// 新增订单发票
/// </summary>
/// <param name="orderInvoice">
/// 订单发票
/// </param>
/// <param name="transaction">
/// 事务对象
/// </param>
/// <returns>
/// 新增的订单编码
/// </returns>
public int Insert(Order_Invoice orderInvoice, SqlTransaction transaction)
{
var paras = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"OrderID",
SqlDbType.Int,
orderInvoice.OrderID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"InvoiceTypeID",
SqlDbType.Int,
orderInvoice.InvoiceTypeID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"InvoiceContentID",
SqlDbType.Int,
orderInvoice.InvoiceContentID,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"InvoiceTitle",
SqlDbType.NVarChar,
orderInvoice.InvoiceTitle,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"InvoiceCost",
SqlDbType.Float,
orderInvoice.InvoiceCost,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"CreateTime",
SqlDbType.DateTime,
DateTime.Now,
ParameterDirection.Input),
this.SqlServer.CreateSqlParameter(
"ReferenceID",
SqlDbType.Int,
null,
ParameterDirection.Output)
};
this.SqlServer.ExecuteNonQuery(CommandType.StoredProcedure, "sp_Order_Invoice_Insert", paras, transaction);
return (int)paras.Find(e => e.ParameterName == "ReferenceID").Value;
}
protected void BtnModificar_Click(object sender, EventArgs e)
{
using (SqlCommand miscomandos = new SqlCommand())
{
try
{
conexion.Open();
transacion = conexion.BeginTransaction();
miscomandos.CommandText = "Select nif from usuarios where nif=@nif";
miscomandos.Parameters.Add("NIF", System.Data.SqlDbType.NChar, 10);
miscomandos.Parameters["NIF"].Value = TBNIF.Text;
miscomandos.Connection = conexion;
miscomandos.Transaction = transacion;
SqlDataReader lecor = miscomandos.ExecuteReader();
if (lecor.HasRows)
{
lecor.Close();
miscomandos.CommandText = "update usuarios set nombre=@nombre,apellidos=@apellidos,localidad=@localidad,direccion=@direccion,provincia=@provincia";
miscomandos.Parameters.Add("nombre", System.Data.SqlDbType.VarChar, 50);
miscomandos.Parameters["nombre"].Value = TBNombre.Text;
miscomandos.Parameters.Add("apellidos", System.Data.SqlDbType.VarChar, 100);
miscomandos.Parameters["apellidos"].Value = TBApe.Text;
miscomandos.Parameters.Add("localidad", System.Data.SqlDbType.VarChar, 50);
miscomandos.Parameters["localidad"].Value = TBLoc.Text;
miscomandos.Parameters.Add("direccion", System.Data.SqlDbType.VarChar, 100);
miscomandos.Parameters["direccion"].Value = TBDir.Text;
miscomandos.Parameters.Add("provincia", System.Data.SqlDbType.VarChar, 50);
miscomandos.Parameters["provincia"].Value = TBProv.Text;
miscomandos.ExecuteNonQuery();
}
transacion.Commit();
}
catch (SqlException ex)
{
transacion.Rollback();
}
finally
{
this.conexion.Close();
}
}
}
/// <summary>
/// 删除会员促销活动.
/// </summary>
/// <param name="id">
/// 活动编号.
/// </param>
/// <param name="transaction">
/// 数据库事务.
/// </param>
public void Delete(int id, out SqlTransaction transaction)
{
if (id <= 0)
{
throw new ArgumentNullException("id");
}
this.SqlServer.BeginTransaction(IsolationLevel.ReadCommitted);
transaction = this.SqlServer.Transaction;
var parameters = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"ID",
SqlDbType.Int,
id,
ParameterDirection.Input)
};
this.SqlServer.ExecuteNonQuery(CommandType.StoredProcedure, "sp_Promote_Vip_DeleteRow", parameters, transaction);
}
/// <summary>
/// 批量添加订单商品
/// </summary>
/// <param name="orderProducts">
/// 订单商品列表
/// </param>
/// <param name="orderId">
/// The order Id.
/// </param>
/// <param name="transaction">
/// 事务对象
/// </param>
/// <returns>
/// 成功添加的订单商品记录数量
/// </returns>
public int BatchInsertOrderProduct(List<Order_Product> orderProducts, int cpsId, int orderId, SqlTransaction transaction)
{
if (orderProducts != null && orderProducts.Count > 0)
{
var dt = this.BuildOrderProductDataTable(orderProducts, orderId);
var paramsList = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"CpsId",
SqlDbType.Int,
cpsId,
ParameterDirection.Input),
new SqlParameter("OP", SqlDbType.Structured)
{
TypeName =
"[dbo].OrderProductTable",
Value = dt,
Direction =
ParameterDirection
.Input
},
this.SqlServer.CreateSqlParameter(
"RowCount",
SqlDbType.Int,
null,
ParameterDirection.Output)
};
this.SqlServer.ExecuteNonQuery(
CommandType.StoredProcedure,
"sp_Order_Product_BatchInsert",
paramsList,
transaction);
return (int)paramsList.Find(parameter => parameter.ParameterName == "RowCount").Value;
}
return 0;
}
/// <summary>
/// 删除指定活动商品.
/// </summary>
/// <param name="id">
/// 活动商品编号.
/// </param>
/// <param name="transaction">
/// 数据事务.
/// </param>
public void Delete(int id, SqlTransaction transaction)
{
if (id <= 0)
{
throw new ArgumentNullException("id");
}
var parameters = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"PromoteVipID",
SqlDbType.Int,
id,
ParameterDirection.Input)
};
this.SqlServer.ExecuteNonQuery(
CommandType.StoredProcedure,
"sp_Promote_Vip_Scope_Delete",
parameters,
transaction);
}
public int executeUpdate(string SQL)
{
int affected = -1;
try
{
if (_DBType.Equals("SQL"))
{
tx_Sql = cn_Sql.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn_Sql;
cmd.CommandText = SQL;
cmd.CommandType = CommandType.Text;
cmd.Transaction = tx_Sql;
affected = cmd.ExecuteNonQuery();
tx_Sql.Commit();
}
}
catch (Exception e)
{
}
return affected;
}
/// <summary>
/// The delete.
/// </summary>
/// <param name="productID">
/// The product id.
/// </param>
/// <param name="transaction">
/// The transaction.
/// </param>
public void Delete(int productID, SqlTransaction transaction)
{
if (productID <= 0)
{
throw new ArgumentNullException("productID");
}
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
var parameters = new List<SqlParameter>
{
this.SqlServer.CreateSqlParameter(
"ProductID",
SqlDbType.Int,
productID,
ParameterDirection.Input)
};
this.SqlServer.ExecuteNonQuery(CommandType.StoredProcedure, "sp_Product_AttributeValueSet_DeleteByProductID", parameters, transaction);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.forum_board model)
{
using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
//先判断选中的父节点是否被包含
if (IsContainNode(model.id, model.parent_id))
{
//查找旧数据
Model.forum_board oldModel = GetModel(model.id);
//查找旧父节点数据
string class_list = "," + model.parent_id + ",";
int class_layer = 1;
if (oldModel.parent_id > 0)
{
Model.forum_board oldParentModel = GetModel(conn, trans, oldModel.parent_id); //带事务
class_list = oldParentModel.class_list + model.parent_id + ",";
class_layer = oldParentModel.class_layer + 1;
}
//先提升选中的父节点
DbHelperSQL.ExecuteSql(conn, trans, "update " + databaseprefix + "forum_board set parent_id=" + oldModel.parent_id + ",class_list='" + class_list + "', class_layer=" + class_layer + " where id=" + model.parent_id); //带事务
UpdateChilds(conn, trans, model.parent_id); //带事务
}
//更新子节点
if (model.parent_id > 0)
{
Model.forum_board model2 = GetModel(conn, trans, model.parent_id); //带事务
model.class_list = model2.class_list + model.id + ",";
model.class_layer = model2.class_layer + 1;
}
else
{
model.class_list = "," + model.id + ",";
model.class_layer = 1;
}
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "forum_board set ");
strSql.Append("boardname=@boardname,");
strSql.Append("parent_id=@parent_id,");
strSql.Append("class_list=@class_list,");
strSql.Append("class_layer=@class_layer,");
strSql.Append("sort_id=@sort_id,");
strSql.Append("img_url=@img_url,");
strSql.Append("content=@content,");
strSql.Append("is_lock=@is_lock,");
strSql.Append("allow_usergroupid_list=@allow_usergroupid_list,");
strSql.Append("moderator_list=@moderator_list,");
strSql.Append("subject_count=@subject_count,");
strSql.Append("post_count=@post_count");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@boardname", SqlDbType.NVarChar, 100),
new SqlParameter("@parent_id", SqlDbType.Int, 4),
new SqlParameter("@class_list", SqlDbType.NVarChar, 500),
new SqlParameter("@class_layer", SqlDbType.Int, 4),
new SqlParameter("@sort_id", SqlDbType.Int, 4),
new SqlParameter("@img_url", SqlDbType.NVarChar, 255),
new SqlParameter("@content", SqlDbType.NText),
new SqlParameter("@is_lock", SqlDbType.Int, 4),
new SqlParameter("@allow_usergroupid_list", SqlDbType.NVarChar, 255),
new SqlParameter("@moderator_list", SqlDbType.NVarChar, 255),
new SqlParameter("@subject_count", SqlDbType.Int, 4),
new SqlParameter("@post_count", SqlDbType.Int, 4),
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = model.boardname;
parameters[1].Value = model.parent_id;
parameters[2].Value = model.class_list;
parameters[3].Value = model.class_layer;
parameters[4].Value = model.sort_id;
parameters[5].Value = model.img_url;
parameters[6].Value = model.content;
parameters[7].Value = model.is_lock;
parameters[8].Value = model.allow_usergroupid_list;
parameters[9].Value = model.moderator_list;
parameters[10].Value = model.subject_count;
parameters[11].Value = model.post_count;
parameters[12].Value = model.id;
DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//更新子节点
UpdateChilds(conn, trans, model.id);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
/// <summary>
/// Persists the object.
/// </summary>
public virtual void Persist(PropertyRightsType persistObject, SqlTransaction sqlTrans)
{
// create local variable array for the sql parameters
SqlParameterHash sqlparams = null;
// Create a local variable for the connection
SqlConnection cnn = null;
// Use the parameter overload or create a new instance
if (sqlTrans == null)
cnn = new SqlConnection(PropertyRightsType.GetConnectionString());
try
{
// discover the parameters
if (persistObject.Persisted)
sqlparams = SqlHelperParameterCache.GetSpParameterSet(PropertyRightsType.GetConnectionString(), "gsp_UpdatePropertyRightsType");
else
sqlparams = SqlHelperParameterCache.GetSpParameterSet(PropertyRightsType.GetConnectionString(), "gsp_CreatePropertyRightsType");
// Store the parameter for the Name attribute.
sqlparams["@name"].Value = persistObject.Name;
// Store the parameter for the EnumeratedName attribute.
sqlparams["@enumeratedName"].Value = persistObject.EnumeratedName;
// Store the parameter for the Description attribute.
if (!persistObject.DescriptionIsNull)
sqlparams["@description"].Value = persistObject.Description;
// Store the parameter for the Id attribute.
sqlparams["@id"].Value = persistObject.Id;
if (!persistObject.Persisted)
{
// store the create only (historical fixed) values
}
else
{
// store the update only (historical changeable) values
}
if (sqlTrans == null)
{
// Process using the isolated connection
using (cnn)
{
// open the connection
cnn.Open();
if (!persistObject.Persisted)
{
SqlHelper.ExecuteNonQuery(cnn, CommandType.StoredProcedure, "gsp_CreatePropertyRightsType", sqlparams);
}
else
{
SqlHelper.ExecuteNonQuery(cnn, CommandType.StoredProcedure, "gsp_UpdatePropertyRightsType", sqlparams);
}
// close the connection after usage
cnn.Close();
}
// nullify the connection var
cnn = null;
}
else
{
// Process using the shared transaction
if (!persistObject.Persisted)
{
SqlHelper.ExecuteNonQuery(sqlTrans, CommandType.StoredProcedure, "gsp_CreatePropertyRightsType", sqlparams);
}
else
{
SqlHelper.ExecuteNonQuery(sqlTrans, CommandType.StoredProcedure, "gsp_UpdatePropertyRightsType", sqlparams);
}
}
}
catch (SqlException sqlex)
{
throw sqlex;
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.forum_board model)
{
using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "forum_board(");
strSql.Append("boardname,parent_id,class_list,class_layer,sort_id,img_url,content,is_lock,allow_usergroupid_list,moderator_list,subject_count,post_count)");
strSql.Append(" values (");
strSql.Append("@boardname,@parent_id,@class_list,@class_layer,@sort_id,@img_url,@content,@is_lock,@allow_usergroupid_list,@moderator_list,@subject_count,@post_count)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@boardname", SqlDbType.NVarChar, 100),
new SqlParameter("@parent_id", SqlDbType.Int, 4),
new SqlParameter("@class_list", SqlDbType.NVarChar, 500),
new SqlParameter("@class_layer", SqlDbType.Int, 4),
new SqlParameter("@sort_id", SqlDbType.Int, 4),
new SqlParameter("@img_url", SqlDbType.NVarChar, 255),
new SqlParameter("@content", SqlDbType.NText),
new SqlParameter("@is_lock", SqlDbType.Int, 4),
new SqlParameter("@allow_usergroupid_list", SqlDbType.NVarChar, 255),
new SqlParameter("@moderator_list", SqlDbType.NVarChar, 255),
new SqlParameter("@subject_count", SqlDbType.Int, 4),
new SqlParameter("@post_count", SqlDbType.Int, 4)
};
parameters[0].Value = model.boardname;
parameters[1].Value = model.parent_id;
parameters[2].Value = model.class_list;
parameters[3].Value = model.class_layer;
parameters[4].Value = model.sort_id;
parameters[5].Value = model.img_url;
parameters[6].Value = model.content;
parameters[7].Value = model.is_lock;
parameters[8].Value = model.allow_usergroupid_list;
parameters[9].Value = model.moderator_list;
parameters[10].Value = model.subject_count;
parameters[11].Value = model.post_count;
object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), parameters); //带事务
model.id = Convert.ToInt32(obj);
if (model.parent_id > 0)
{
Model.forum_board model2 = GetModel(conn, trans, model.parent_id); //带事务
model.class_list = model2.class_list + model.id + ",";
model.class_layer = model2.class_layer + 1;
}
else
{
model.class_list = "," + model.id + ",";
model.class_layer = 1;
}
//修改节点列表和深度
DbHelperSQL.ExecuteSql(conn, trans, "update " + databaseprefix + "forum_board set class_list='" + model.class_list + "', class_layer=" + model.class_layer + " where id=" + model.id); //带事务
trans.Commit();
}
catch
{
trans.Rollback();
return(0);
}
}
}
return(model.id);
}
static SQLConnector()
{
_connection = new SqlConnection();
ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];
_transaction = null;
}
/// <summary>
/// End a transaction.
/// </summary>
public void EndTransaction()
{
SqlTransaction.Commit();
Close();
}
public YF_AssetMainRecordModel GetModel(int Code, SqlTransaction Transaction)
{
YF_AssetMainRecordDAL ddal = new YF_AssetMainRecordDAL(Transaction);
return(ddal.GetModel(Code));
}
private static int RunCommand(String cmd)
{
var connectionString =
"Data Source=localhost,56434;Initial Catalog=UserProfiles;Timeout=60000;Encrypt=False;";
Console.WriteLine("Executing: " + cmd);
switch (cmd)
{
case "execute-transaction":
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction("Transaction1");
SqlCommand command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = "insert into Table (Name) VALUES ('Bob')";
command.ExecuteNonQuery();
command.CommandText = "insert into Table (Name) VALUES ('Joe')";
command.ExecuteNonQuery();
transaction.Commit();
}
break;
}
case "execute-reader-with-parameters":
{
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "dbo.GetAllUsers";
command.CommandType = CommandType.StoredProcedure;
var p = new SqlParameter("name", SqlDbType.NVarChar, 255);
p.Value = "test-test";
command.Parameters.Add(p);
p = new SqlParameter("name2", SqlDbType.NVarChar, 255);
p.Value = "test-test2";
command.Parameters.Add(p);
conn.Open();
using (SqlDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
if (rdr.GetInt32(0) != 1)
{
return(1);
}
if (rdr.GetString(1) != "Bob")
{
return(2);
}
}
}
}
break;
}
case "execute-scalar":
{
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "dbo.GetAllUsers";
command.CommandType = CommandType.StoredProcedure;
conn.Open();
var reader = command.ExecuteScalar();
var number = Int32.Parse(reader.ToString());
Console.WriteLine("Result is - " + number);
if (number != 101)
{
return(5);
}
}
break;
}
case "execute-non-query":
{
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "dbo.GetAllUsers";
command.CommandType = CommandType.StoredProcedure;
conn.Open();
var reader = command.ExecuteNonQuery();
var number = Int32.Parse(reader.ToString());
Console.WriteLine("Result is - " + number);
if (number != 101)
{
return(5);
}
}
break;
}
case "user-profile":
{
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "select * from user-profiles";
conn.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (reader.GetInt32(0) != 1)
{
return(1);
}
if (reader.GetString(1) != "Bob")
{
return(2);
}
}
}
break;
}
default:
return(111111);
}
return(0);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public int ExecuteSqlTran(System.Collections.Generic.List <CommandInfo> cmdList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{ int count = 0;
//循环
foreach (CommandInfo myDE in cmdList)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
return(0);
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();
return(0);
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
trans.Rollback();
return(0);
}
continue;
}
int val = cmd.ExecuteNonQuery();
count += val;
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();
return(0);
}
cmd.Parameters.Clear();
}
trans.Commit();
return(count); }
catch
{
trans.Rollback();
throw;
}
}
}
}
public FakeDataManager(SqlConnection connection, SqlTransaction transactionOpt)
: base(connection, transactionOpt)
{
}
SqlDataAdapter buildSelectSessionRequestsAdapter(SqlConnection conn, SqlTransaction tx)
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand();
adapter.SelectCommand.Connection = conn;
adapter.SelectCommand.Transaction = tx;
adapter.SelectCommand.CommandText = "SELECT * FROM dbo.MdwsSessionRequests;";
return adapter;
}
//*********************************************************************
//
// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
// to the provided command.
//
// param name="command" the SqlCommand to be prepared
// param name="connection" a valid SqlConnection, on which to execute this command
// param name="transaction" a valid SqlTransaction, or 'null'
// param name="commandType" the CommandType (stored procedure, text, etc.)
// param name="commandText" the stored procedure name or T-SQL command
// param name="commandParameters" an array of SqlParameters to be associated with the command or 'null' if no parameters are required
//
//*********************************************************************
public static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
//associate the connection with the command
command.Connection = connection;
// qianwt 2007-8-20 设置执行超时时间跟连接超时时间一致
command.CommandTimeout = connection.ConnectionTimeout;
//set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;
//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
/// <summary>
/// This is going to be commented out because it becomes unusably slow after a few hundred thousand unique sessions
/// are created. Use the getSessions(int howMany) function instead to retrieve the latest howMany number of sessions
/// </summary>
/// <returns></returns>
//public ApplicationSessionsTO getAllSessions()
//{
// ApplicationSessionsTO result = new ApplicationSessionsTO();
// SqlConnection conn = new SqlConnection();
// SqlTransaction tx = null;
// try
// {
// conn = getConnection();
// tx = conn.BeginTransaction();
// SqlDataAdapter adapter = buildSelectSessionsAdapter(conn, tx);
// DataSet sessionData = new DataSet();
// int sessionCount = adapter.Fill(sessionData);
// adapter = buildSelectSessionRequestsAdapter(conn, tx);
// DataSet requestsData = new DataSet();
// int requestsCount = adapter.Fill(requestsData);
// tx.Commit();
// Dictionary<string, ApplicationSession> sessions = getApplicationSessions(sessionData);
// Stack<ApplicationRequest> requests = getApplicationRequests(requestsData);
// return new ApplicationSessionsTO(addRequestsToSessions(sessions, requests));
// }
// catch (Exception exc)
// {
// if (tx != null)
// {
// tx.Rollback();
// }
// result.fault = new FaultTO(exc);
// return null;
// }
// finally
// {
// conn.Close();
// if (tx != null)
// {
// tx.Dispose();
// }
// }
//}
SqlDataAdapter buildInsertRequestAdapter(ApplicationRequest request, SqlConnection conn, SqlTransaction tx)
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand();
adapter.InsertCommand.Connection = conn;
adapter.InsertCommand.Transaction = tx;
adapter.InsertCommand.CommandText = "INSERT INTO dbo.MdwsSessionRequests " +
"([ASP.NET_SessionId], URI, RequestTimestamp, ResponseTimestamp, RequestBody, ResponseBody) VALUES ('" +
request.AspNetSessionId + "', '" +
request.Uri.LocalPath + "', " +
"@requestTimestamp, @responseTimestamp, @RequestBody, @ResponseBody);";
SqlParameter requestTimestampParam = new SqlParameter("@requestTimestamp", SqlDbType.DateTime);
requestTimestampParam.Value = request.RequestTimestamp;
SqlParameter responseTimestampParam = new SqlParameter("@responseTimestamp", SqlDbType.DateTime);
responseTimestampParam.Value = request.ResponseTimestamp;
SqlParameter reqestBodyParam = new SqlParameter("@RequestBody", SqlDbType.Text);
reqestBodyParam.Value = String.IsNullOrEmpty(request.RequestBody) ?
(object)DBNull.Value : (object)request.RequestBody;
SqlParameter responseBodyParam = new SqlParameter("@ResponseBody", SqlDbType.Text);
responseBodyParam.Value = String.IsNullOrEmpty(request.ResponseBody) ?
(object)DBNull.Value : (object)request.ResponseBody;
adapter.InsertCommand.Parameters.Add(requestTimestampParam);
adapter.InsertCommand.Parameters.Add(responseTimestampParam);
adapter.InsertCommand.Parameters.Add(reqestBodyParam);
adapter.InsertCommand.Parameters.Add(responseBodyParam);
return adapter;
}
private SqlCommand(string commandText, SqlConnection connection, SqlTransaction transaction, CommandType commandType, UpdateRowSource updatedRowSource, bool designTimeVisible, int commandTimeout, SqlParameterCollection parameters)
{
this.commandText = commandText;
this.connection = connection;
this.transaction = transaction;
this.commandType = commandType;
this.updatedRowSource = updatedRowSource;
this.designTimeVisible = designTimeVisible;
this.commandTimeout = commandTimeout;
this.parameters = new SqlParameterCollection(this);
for (int i = 0;i < parameters.Count;i++)
this.parameters.Add(((ICloneable)parameters[i]).Clone());
}
public List <YF_AssetMainRecordModel> GetModels(SqlTransaction Transaction)
{
YF_AssetMainRecordDAL ddal = new YF_AssetMainRecordDAL(Transaction);
return(ddal.Select());
}
/// <summary>
/// 添加退换货信息以及清单信息
/// </summary>
/// <param name="refundInfo">退换货信息</param>
/// <param name="refundDetailList">清单信息</param>
/// <returns></returns>
public bool InsertCheckRefundAndDetailList(CheckRefundInfo refundInfo, IList<CheckRefundDetailInfo> refundDetailList)
{
bool flag;
try
{
using (SqlConnection conn = Databases.GetSqlConnection(GlobalConfig.ERP_DB_NAME, false))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
flag = conn.Execute(SQL_INSERT_CHECKREFUND_SERVER, new
{
RefundId = refundInfo.RefundId,
RefundNo = refundInfo.RefundNo,
OrderId = refundInfo.OrderId,
ExpressNo = refundInfo.ExpressNo,
ExpressName = refundInfo.ExpressName,
CreateDate = refundInfo.CreateTime,
CheckState = refundInfo.CheckState,
Remark = refundInfo.Remark,
WarehouseId = refundInfo.WarehouseId,
ReStartReason = refundInfo.ReStartReason,
OrderNo = refundInfo.OrderNo,
Consignee = refundInfo.Consignee,
SaleFilialeId = refundInfo.SaleFilialeId,
SalePlatformId = refundInfo.SalePlatformId,
}, trans) > 0;
if (flag)
{
foreach (var detailInfo in refundDetailList)
{
if (!flag) continue;
flag = conn.Execute(SQL_INSERT_CHECKREFUNDDETAIL_SERVER, new
{
Id = detailInfo.Id,
RefundId = detailInfo.RefundId,
GoodsId = detailInfo.GoodsId,
RealGoodsId = detailInfo.RealGoodsId,
GoodsCode = detailInfo.GoodsCode ?? string.Empty,
GoodsName = detailInfo.GoodsName ?? string.Empty,
Specification = detailInfo.Specification ?? string.Empty,
Quantity = detailInfo.Quantity,
SellPrice = detailInfo.SellPrice,
ReturnCount = detailInfo.ReturnCount,
ReturnType = detailInfo.ReturnType,
ReturnReason = detailInfo.ReturnReason ?? string.Empty,
}, trans) > 0;
}
if (flag)
trans.Commit();
else
trans.Rollback();
}
}
}
}
catch (Exception ex)
{
throw new ApplicationException("添加退换货商品检查失败!", ex);
}
return flag;
}
/// <summary>
/// If a transaction is failed call it.
/// </summary>
public void Rollback()
{
SqlTransaction.Rollback();
}
private void DeleteUser(HttpContext context)
{
SqlParameter p1 = DataAccessLayer.CreateSqlParameter("UserId", DbType.Int32, context.Request.Params["Id"].ToString());
SqlParameter p2 = DataAccessLayer.CreateSqlParameter("Status", DbType.Int16, 0, 0, ParameterDirection.Output);
SqlParameter p3 = DataAccessLayer.CreateSqlParameter("Op", DbType.Int32, 1);
SqlParameter[] ps = new SqlParameter[] { p1, p2, p3 };
using (SqlConnection connection = DataAccessLayer.Connection)
{
if (connection.State != ConnectionState.Open)
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
DataAccessLayer.ExecuteCommandWithTransaction("Sp_UpdateDeleteUser", transaction, ps);
int status;
if (p2.Value != null && int.TryParse(p2.Value.ToString(), out status))
{
if (status == 1)
{
transaction.Commit();
context.Response.Write(JsonConvert.SerializeObject(new
{
text = "User deleted successfully!",
type = "success",
layout = "topCenter",
timeout = true
}, jsonSetting));
}
else
{
transaction.Rollback();
context.Response.Write(JsonConvert.SerializeObject(new
{
text = "User could not deleted!",
type = "warning",
layout = "topCenter"
}, jsonSetting));
}
}
else
{
transaction.Rollback();
context.Response.Write(JsonConvert.SerializeObject(new
{
text = "Some error occurred while deleting!",
type = "error",
layout = "topCenter"
}, jsonSetting));
}
}
catch (Exception exp)
{
transaction.Rollback();
context.Response.Write(JsonConvert.SerializeObject(new
{
text = exp.Message,
type = "error",
layout = "topCenter"
}, jsonSetting));
}
}
}
public int Delete(int Code, SqlTransaction Transaction)
{
YF_AssetMainRecordDAL ddal = new YF_AssetMainRecordDAL(Transaction);
return(ddal.Delete(Code));
}
/// <summary>
/// A SqlConnection extension method that executes the scalar operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>An object.</returns>
public static object ExecuteScalar(this SqlConnection @this, string cmdText, CommandType commandType, SqlTransaction transaction)
{
return(@this.ExecuteScalar(cmdText, null, commandType, transaction));
}
/// <summary>
/// 执行Sql和Oracle滴混合事务
/// </summary>
/// <param name="list">SQL命令行列表</param>
/// <param name="oracleCmdSqlList">Oracle命令行列表</param>
/// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
public static int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo myDE in list)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (isHave)
{
//引发事件
myDE.OnSolicitationEvent();
}
}
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
//return 0;
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
//return 0;
}
continue;
}
int val = cmd.ExecuteNonQuery();
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
//return 0;
}
cmd.Parameters.Clear();
}
string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
if (!res)
{
tx.Rollback();
throw new Exception("Oracle执行失败");
// return -1;
}
tx.Commit();
return(1);
}
catch (System.Data.SqlClient.SqlException e)
{
tx.Rollback();
throw e;
}
catch (Exception e)
{
tx.Rollback();
throw e;
}
}
}
/// <summary>
/// A SqlConnection extension method that executes the scalar operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="commandType">Type of the command.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>An object.</returns>
public static object ExecuteScalar(this SqlConnection @this, string cmdText, SqlParameter[] parameters, CommandType commandType, SqlTransaction transaction)
{
using (SqlCommand command = @this.CreateCommand())
{
command.CommandText = cmdText;
command.CommandType = commandType;
command.Transaction = transaction;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
return(command.ExecuteScalar());
}
}
public static DataSet LoadByParams(object intPageTypeID, object strDescription, object strCode, SqlConnection sqlConn, SqlTransaction sqlTran)
{
SqlConnection conn = sqlConn;
SqlCommand cmd = DBHelper.getSprocCmd("sprocPageTypesSelectByParams", conn);
cmd.Transaction = sqlTran;
SqlParameter param;
param = new SqlParameter("@PageTypeID", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = (intPageTypeID == null ? intPageTypeID : (int)intPageTypeID);
cmd.Parameters.Add(param);
param = new SqlParameter("@Description", SqlDbType.NVarChar);
param.Direction = ParameterDirection.Input;
param.Value = (strDescription == null ? strDescription : (string)strDescription);
cmd.Parameters.Add(param);
param = new SqlParameter("@Code", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;
param.Value = (strCode == null ? strCode : (string)strCode);
cmd.Parameters.Add(param);
// open connection
conn.Open();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return(ds);
}
public SqlTransaction BeginTransaction()
{
currentTransaction = connection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
return(currentTransaction);
}
public void UpdateFormReimburseMoney(int FormID, SystemEnums.FormStatus StatusID, SystemEnums.FormType FormTypeID,
string AttachedFileName, string RealAttachedFileName, string Remark, string FlowTemplate)
{
SqlTransaction transaction = null;
try {
//事务开始
transaction = TableAdapterHelper.BeginTransaction(this.TAForm);
TableAdapterHelper.SetTransaction(this.TAFormReimburse, transaction);
TableAdapterHelper.SetTransaction(this.TAFormReimburseDetail, transaction);
TableAdapterHelper.SetTransaction(this.TAFormReimburseInvoice, transaction);
TableAdapterHelper.SetTransaction(this.TAPKRecord, transaction);
FormDS.FormRow formRow = this.TAForm.GetDataByID(FormID)[0];
FormDS.FormReimburseRow FormReimburseRow = this.TAFormReimburse.GetDataByID(FormID)[0];
//处理单据的内容
UtilityBLL utility = new UtilityBLL();
if (StatusID == SystemEnums.FormStatus.Awaiting)
{
string formTypeString = utility.GetFormTypeString((int)FormTypeID);
formRow.FormNo = utility.GetFormNo(formTypeString);
}
else
{
formRow.SetFormNoNull();
}
formRow.StatusID = (int)StatusID;
formRow.SubmitDate = DateTime.Now;
formRow.LastModified = DateTime.Now;
this.TAForm.Update(formRow);
//处理FormReimburseRow的内容
if (AttachedFileName != null && AttachedFileName != string.Empty)
{
FormReimburseRow.AttachedFileName = AttachedFileName;
}
else
{
FormReimburseRow.SetAttachedFileNameNull();
}
if (RealAttachedFileName != null && RealAttachedFileName != string.Empty)
{
FormReimburseRow.RealAttachedFileName = RealAttachedFileName;
}
else
{
FormReimburseRow.SetRealAttachedFileNameNull();
}
if (Remark != null && Remark != string.Empty)
{
FormReimburseRow.Remark = Remark;
}
this.TAFormReimburse.Update(FormReimburseRow);
//发票
foreach (FormDS.FormReimburseInvoiceRow invoiceRow in this.FormDataSet.FormReimburseInvoice)
{
// 与父表绑定
if (invoiceRow.RowState != DataRowState.Deleted)
{
invoiceRow.FormReimburseID = FormReimburseRow.FormReimburseID;
}
}
this.TAFormReimburseInvoice.Update(this.FormDataSet.FormReimburseInvoice);
//处理明细
decimal totalAmount = 0;
decimal totalTaxAmount = 0;//总税金
foreach (FormDS.FormReimburseDetailRow detailRow in this.FormDataSet.FormReimburseDetail)
{
if (detailRow.RowState != DataRowState.Deleted)
{
totalAmount += detailRow.Amount;
totalTaxAmount += detailRow.IsTaxAmountNull() ? 0 : detailRow.TaxAmount;
detailRow.FormReimburseID = FormReimburseRow.FormReimburseID;
}
}
this.TAFormReimburseDetail.Update(this.FormDataSet.FormReimburseDetail);
FormReimburseRow.Amount = totalAmount;
FormReimburseRow.TaxAmount = totalTaxAmount;
this.TAFormReimburse.Update(FormReimburseRow);
// 正式提交
if (StatusID == SystemEnums.FormStatus.Awaiting)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
dic["Apply_Amount"] = totalAmount + totalTaxAmount;
//dic["Dept"] = new AuthorizationDSTableAdapters.OrganizationUnitTableAdapter().GetOrganizationUnitCodeByOrganizationUnitID(formRow.OrganizationUnitID)[0].OrganizationUnitCode;
dic["Expense_Category"] = getExpenseCategoryIDByFormID(int.Parse(FormReimburseRow.FormApplyIds.Split(',')[0])).ToString();//此处待改动
APHelper AP = new APHelper();
new APFlowBLL().ApplyForm(AP, TAForm, null, formRow, formRow.OrganizationUnitID, FlowTemplate, StatusID, dic);
}
transaction.Commit();
} catch (Exception ex) {
transaction.Rollback();
throw new ApplicationException("Save Fail!" + ex.ToString());
} finally {
transaction.Dispose();
}
}
/// <summary>
/// A SqlConnection extension method that executes the scalar operation.
/// </summary>
/// <param name="this">The @this to act on.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="parameters">Options for controlling the operation.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>An object.</returns>
public static object ExecuteScalar(this SqlConnection @this, string cmdText, SqlParameter[] parameters, SqlTransaction transaction)
{
return(@this.ExecuteScalar(cmdText, parameters, CommandType.Text, transaction));
}
public void AddFormReimburseGoods(int?RejectedFormID, int UserID, int?ProxyUserID, int?ProxyPositionID, int OrganizationUnitID, int PositionID, SystemEnums.FormType FormTypeID,
SystemEnums.FormStatus StatusID, int CustomerID, int PaymentTypeID, string AttachedFileName, string RealAttachedFileName, string Remark, string FormApplyIds, string FormApplyNos, string FlowTemplate)
{
SqlTransaction transaction = null;
try {
transaction = TableAdapterHelper.BeginTransaction(this.TAForm);
TableAdapterHelper.SetTransaction(this.TAFormReimburse, transaction);
TableAdapterHelper.SetTransaction(this.TAFormReimburseDetail, transaction);
TableAdapterHelper.SetTransaction(this.TAFormReimburseSKUDetail, transaction);
FormDS.FormRow formRow = this.FormDataSet.Form.NewFormRow();
if (RejectedFormID != null)
{
formRow.RejectedFormID = RejectedFormID.GetValueOrDefault();
}
formRow.UserID = UserID;
UtilityBLL utility = new UtilityBLL();
if (StatusID == SystemEnums.FormStatus.Awaiting)
{
string formTypeString = utility.GetFormTypeString((int)FormTypeID);
formRow.FormNo = utility.GetFormNo(formTypeString);
}
else
{
formRow.SetFormNoNull();
}
if (ProxyUserID != null)
{
formRow.ProxyUserID = ProxyUserID.GetValueOrDefault();
}
if (ProxyPositionID != null)
{
formRow.ProxyPositionID = ProxyPositionID.GetValueOrDefault();
}
formRow.OrganizationUnitID = OrganizationUnitID;
formRow.PositionID = PositionID;
formRow.FormTypeID = (int)FormTypeID;
formRow.StatusID = (int)StatusID;
formRow.SubmitDate = DateTime.Now;
formRow.LastModified = DateTime.Now;
formRow.InTurnUserIds = "P"; //待改动
formRow.InTurnPositionIds = "P"; //待改动
formRow.PageType = (int)SystemEnums.PageType.ReimburseGoodsApply;
this.FormDataSet.Form.AddFormRow(formRow);
this.TAForm.Update(formRow);
//处理申请表的内容
FormDS.FormReimburseRow FormReimburseRow = this.FormDataSet.FormReimburse.NewFormReimburseRow();
FormReimburseRow.FormReimburseID = formRow.FormID;
FormReimburseRow.CustomerID = CustomerID;
FormReimburseRow.PaymentTypeID = PaymentTypeID;
FormReimburseRow.Amount = 0;//默认值
if (AttachedFileName != null && AttachedFileName != string.Empty)
{
FormReimburseRow.AttachedFileName = AttachedFileName;
}
if (RealAttachedFileName != null && RealAttachedFileName != string.Empty)
{
FormReimburseRow.RealAttachedFileName = RealAttachedFileName;
}
if (Remark != null && Remark != string.Empty)
{
FormReimburseRow.Remark = Remark;
}
FormReimburseRow.FormApplyIds = FormApplyIds;
FormReimburseRow.FormApplyNos = FormApplyNos;
FormReimburseRow.IsDeliveryComplete = false;
this.FormDataSet.FormReimburse.AddFormReimburseRow(FormReimburseRow);
this.TAFormReimburse.Update(FormReimburseRow);
//处理free goods明细
if (RejectedFormID != null)
{
FormDS.FormReimburseSKUDetailDataTable newFGTable = new FormDS.FormReimburseSKUDetailDataTable();
foreach (FormDS.FormReimburseSKUDetailRow detailFGRow in this.FormDataSet.FormReimburseSKUDetail)
{
// 与父表绑定
if (detailFGRow.RowState != DataRowState.Deleted)
{
FormDS.FormReimburseSKUDetailRow newDetailFGRow = newFGTable.NewFormReimburseSKUDetailRow();
newDetailFGRow.FormReimburseID = FormReimburseRow.FormReimburseID;
newDetailFGRow.SKUID = detailFGRow.SKUID;
newDetailFGRow.PackageQuantity = detailFGRow.PackageQuantity;
newDetailFGRow.UnitPrice = detailFGRow.UnitPrice;
newDetailFGRow.Quantity = detailFGRow.Quantity;
newDetailFGRow.Amount = detailFGRow.Amount;
if (!detailFGRow.IsRemarkNull())
{
newDetailFGRow.Remark = detailFGRow.Remark;
}
newFGTable.AddFormReimburseSKUDetailRow(newDetailFGRow);
}
}
this.TAFormReimburseSKUDetail.Update(newFGTable);
}
else
{
foreach (FormDS.FormReimburseSKUDetailRow detailFGRow in this.FormDataSet.FormReimburseSKUDetail)
{
// 与父表绑定
if (detailFGRow.RowState != DataRowState.Deleted)
{
detailFGRow.FormReimburseID = FormReimburseRow.FormReimburseID;
}
}
this.TAFormReimburseSKUDetail.Update(this.FormDataSet.FormReimburseSKUDetail);
}
//明细表
decimal totalAmount = 0;//计算总申请金额
FormDS.FormReimburseDetailDataTable newDetailTable = new FormDS.FormReimburseDetailDataTable();
foreach (FormDS.FormReimburseDetailRow detailRow in this.FormDataSet.FormReimburseDetail)
{
// 与父表绑定
if (detailRow.RowState != DataRowState.Deleted)
{
totalAmount += detailRow.Amount;
FormDS.FormReimburseDetailRow newDetailRow = newDetailTable.NewFormReimburseDetailRow();
newDetailRow.FormReimburseID = FormReimburseRow.FormReimburseID;
newDetailRow.FormApplyExpenseDetailID = detailRow.FormApplyExpenseDetailID;
newDetailRow.ApplyFormNo = detailRow.ApplyFormNo;
newDetailRow.ApplyPeriod = detailRow.ApplyPeriod;
newDetailRow.ShopID = detailRow.ShopID;
newDetailRow.SKUID = detailRow.SKUID;
newDetailRow.ExpenseItemID = detailRow.ExpenseItemID;
newDetailRow.ApplyAmount = detailRow.ApplyAmount;
newDetailRow.RemainAmount = detailRow.RemainAmount;
newDetailRow.Amount = detailRow.Amount;
newDetailRow.ApplyPaymentTypeID = detailRow.ApplyPaymentTypeID;
newDetailRow.FormApplyID = detailRow.FormApplyID;
newDetailRow.AccruedAmount = detailRow.IsAccruedAmountNull() ? 0 : detailRow.AccruedAmount;
newDetailTable.AddFormReimburseDetailRow(newDetailRow);
}
}
this.TAFormReimburseDetail.Update(newDetailTable);
FormReimburseRow.Amount = totalAmount;
this.TAFormReimburse.Update(FormReimburseRow);
//作废之前的单据
if (RejectedFormID != null)
{
FormDS.FormRow oldRow = this.TAForm.GetDataByID(RejectedFormID.GetValueOrDefault())[0];
if (oldRow.StatusID == (int)SystemEnums.FormStatus.Rejected)
{
oldRow.StatusID = (int)SystemEnums.FormStatus.Scrap;
this.TAForm.Update(oldRow);
}
}
// 正式提交或草稿
if (StatusID == SystemEnums.FormStatus.Awaiting)
{
Dictionary <string, object> dic = new Dictionary <string, object>();
dic["Apply_Amount"] = totalAmount;
//dic["Dept"] = new AuthorizationDSTableAdapters.OrganizationUnitTableAdapter().GetOrganizationUnitCodeByOrganizationUnitID(formRow.OrganizationUnitID)[0].OrganizationUnitCode;
dic["Expense_Category"] = getExpenseCategoryIDByFormID(int.Parse(FormApplyIds.Split(',')[0])).ToString();//此处待改动
APHelper AP = new APHelper();
new APFlowBLL().ApplyForm(AP, TAForm, RejectedFormID, formRow, OrganizationUnitID, FlowTemplate, StatusID, dic);
}
transaction.Commit();
} catch (Exception ex) {
transaction.Rollback();
throw new ApplicationException("Save Fail!" + ex.ToString());
} finally {
transaction.Dispose();
}
}
void StokButonGruplariVeStokButonlariniGetir(SqlConnection Baglanti, SqlTransaction Tr, int TeraziID)
{
StokAramaIslemleri.StokButonGruplariniGetir(Baglanti, Tr, TeraziID);
StokAramaIslemleri.StokButonlariniGetir(Baglanti, Tr, TeraziID);
}
/// <summary>
/// 初始化一个SqlCommand对象
/// </summary>
/// <param name="command">需要初始化的SqlCommand对象</param>
/// <param name="connection">一个SQL连接对象</param>
/// <param name="transaction">一个事务对象</param>
/// <param name="commandtype">SQL命令的类型，COMMANDTYPE枚举类型</param>
/// <param name="commandText">SQL命令</param>
/// <param name="commandParameter">参数集合数组</param>
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandtype, string commandText, SqlParameter[] commandParameter)
{
try
{
//判断连接状态,如果未打开连接，则打开
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
//设置COMMAND的连接属性
command.Connection = connection;
//设置COMMANDTEXT属性
command.CommandText = commandText;
//设置COMMANDtype属性
command.CommandType = commandtype;
//如果事务对象可用，则设置COMMAND的事务属性
if (transaction != null)
{
command.Transaction = transaction;
}
//设置COMMAND的参数集合
if (commandParameter != null)
{
AttachParameters(command, commandParameter);
}
}
catch (Exception ex)
{
throw ex;
}
return;
}
public List <YF_AssetMainRecordModel> GetModels(YF_AssetMainRecordQueryModel ObjQueryModel, SqlTransaction Transaction)
{
YF_AssetMainRecordDAL ddal = new YF_AssetMainRecordDAL(Transaction);
return(ddal.Select(ObjQueryModel));
}
/// <summary>
/// 更新日售票数
/// </summary>
/// <param name="tickets"></param>
/// <param name="connection"></param>
/// <param name="transaction"></param>
public void UpdateTicket(List <Tbl_Ticket> tickets, SqlConnection connection, SqlTransaction transaction)
{
_ticketRepository.UpdateTicket(tickets, connection, transaction);
}
SqlDataAdapter buildDeleteSessionAdapter(string sessionId, SqlConnection conn, SqlTransaction tx)
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.DeleteCommand = new SqlCommand();
adapter.DeleteCommand.Connection = conn;
adapter.DeleteCommand.Transaction = tx;
adapter.DeleteCommand.CommandText = "DELETE FROM dbo.MdwsSessionRequests WHERE [ASP.NET_SessionId]=@SessionId; " +
"DELETE FROM dbo.MdwsSessions WHERE [ASP.NET_SessionId]=@SessionId;";
SqlParameter sessionIdParam = new SqlParameter("@SessionId", SqlDbType.Char, 24);
sessionIdParam.Value = sessionId;
adapter.DeleteCommand.Parameters.Add(sessionIdParam);
return adapter;
}
public int Insert(YF_AssetMainRecordModel ObjModel, SqlTransaction Transaction)
{
YF_AssetMainRecordDAL ddal = new YF_AssetMainRecordDAL(Transaction);
return(ddal.Insert(ObjModel));
}
SqlDataAdapter buildInsertSessionAdapter(ApplicationSession session, SqlConnection conn, SqlTransaction tx)
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand();
adapter.InsertCommand.Connection = conn;
adapter.InsertCommand.Transaction = tx;
adapter.InsertCommand.CommandText = "INSERT INTO dbo.MdwsSessions " +
"([ASP.NET_SessionId], IP, Start, [End], LocalhostName) VALUES ('" +
session.AspNetSessionId + "', '" + session.RequestingIP + "', '" +
session.Start.ToString() + "', '" + session.End.ToString() + "', '" +
session.LocalhostName + "');";
return adapter;
}
public int Update(YF_AssetMainRecordModel ObjModel, SqlTransaction Transaction)
{
YF_AssetMainRecordDAL ddal = new YF_AssetMainRecordDAL(Transaction);
return(ddal.Update(ObjModel));
}
/// <summary>
/// Build SQL adapter for selecting all MDWS sessions saved to database that where started between a given date range
/// </summary>
/// <param name="start">Start date</param>
/// <param name="end">End date</param>
/// <param name="conn">SQL Connection</param>
/// <param name="tx">SQL Transaction</param>
/// <returns>SQL adapter</returns>
SqlDataAdapter buildSelectSessionsAdapter(DateTime start, DateTime end, SqlConnection conn, SqlTransaction tx)
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand();
adapter.SelectCommand.Connection = conn;
adapter.SelectCommand.Transaction = tx;
adapter.SelectCommand.CommandText = "SELECT " +
"MDWS.dbo.MdwsSessions.[ASP.NET_SessionId], " +
"MDWS.dbo.MdwsSessions.IP, " +
"MDWS.dbo.MdwsSessions.Start, " +
"MDWS.dbo.MdwsSessions.[End], " +
"MDWS.dbo.MdwsSessions.LocalhostName, " +
"MDWS.dbo.MdwsSessionRequests.URI, " +
"MDWS.dbo.MdwsSessionRequests.RequestTimestamp, " +
"MDWS.dbo.MdwsSessionRequests.ResponseTimestamp, " +
"MDWS.dbo.MdwsSessionRequests.RequestBody, " +
"MDWS.dbo.MdwsSessionRequests.ResponseBody " +
"FROM MDWS.dbo.MdwsSessions " +
"LEFT JOIN dbo.MdwsSessionRequests ON dbo.MdwsSessions.[ASP.NET_SessionID]=dbo.MdwsSessionRequests.[ASP.NET_SessionId] " +
"WHERE dbo.MdwsSessions.Start BETWEEN @Start AND @End;";
SqlParameter startParam = new SqlParameter("@Start", SqlDbType.DateTime);
startParam.Value = start;
SqlParameter endParam = new SqlParameter("@End", SqlDbType.DateTime);
endParam.Value = end;
adapter.SelectCommand.Parameters.Add(startParam);
adapter.SelectCommand.Parameters.Add(endParam);
return adapter;
}
private Task <T> ExecuteAsync <T>(string queryString, Func <SqlCommand, Task <T> > func, SqlTransaction transaction)
{
var command = new SqlCommand(queryString, transaction.Connection);
command.Transaction = transaction;
return(func(command));
}
