private static async Task <IEnumerable <TReturn> > MultiMapAsync <TReturn>(this IDbConnection cnn, CommandDefinition command, Type[] types, Func <object[], TReturn> map, string splitOn)
{
if (types.Length < 1)
{
throw new ArgumentException("you must provide at least one type to deserialize");
}
object param = command.Parameters;
var identity = new Identity(command.CommandText, command.CommandType, cnn, types[0], param?.GetType(), types);
var info = GetCacheInfo(identity, param, command.AddToCache);
bool wasClosed = cnn.State == ConnectionState.Closed;
try {
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync().ConfigureAwait(false);
}
using (var cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
using (var reader = await cmd.ExecuteReaderAsync(command.CancellationToken).ConfigureAwait(false)) {
var results = MultiMapImpl <TReturn>(null, default(CommandDefinition), types, map, splitOn, reader, identity, true);
return(command.Buffered ? results.ToList() : results);
}
}
finally {
if (wasClosed)
{
cnn.Close();
}
}
}
private static async Task <IEnumerable <TReturn> > MultiMapAsync <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(this IDbConnection cnn, CommandDefinition command, Delegate map, string splitOn)
{
object param = command.Parameters;
var identity = new Identity(command.CommandText, command.CommandType, cnn, typeof(TFirst), param?.GetType(), new[] { typeof(TFirst), typeof(TSecond), typeof(TThird), typeof(TFourth), typeof(TFifth), typeof(TSixth), typeof(TSeventh) });
var info = GetCacheInfo(identity, param, command.AddToCache);
bool wasClosed = cnn.State == ConnectionState.Closed;
try
{
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false);
}
using (var cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
using (var reader = await cmd.ExecuteReaderAsync(wasClosed ? CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess : CommandBehavior.SequentialAccess, command.CancellationToken).ConfigureAwait(false))
{
if (!command.Buffered)
{
wasClosed = false; // handing back open reader; rely on command-behavior
}
var results = MultiMapImpl <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(null, CommandDefinition.ForCallback(command.Parameters), map, splitOn, reader, identity, true);
return(command.Buffered ? results.ToList() : results);
}
} finally
{
if (wasClosed)
{
cnn.Close();
}
}
}
public bool DeleteProduct(int exchangeId, int productId)
{
bool result = false;
using (System.Data.Common.DbConnection dbConnection = this.database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("delete from Hishop_PointExChange_Products where [exChangeId]= @exChangeId and [ProductId] = @ProductId ");
this.database.AddInParameter(sqlStringCommand, "exChangeId", System.Data.DbType.Int32, exchangeId);
this.database.AddInParameter(sqlStringCommand, "ProductId", System.Data.DbType.Int32, productId);
System.Data.Common.DbCommand sqlStringCommand2 = this.database.GetSqlStringCommand("update Hishop_PointExChange_PointExChanges set ProductNumber=ProductNumber-1 where Id=@Id");
this.database.AddInParameter(sqlStringCommand2, "Id", System.Data.DbType.Int32, exchangeId);
try
{
this.database.ExecuteNonQuery(sqlStringCommand, dbTransaction);
this.database.ExecuteNonQuery(sqlStringCommand2, dbTransaction);
dbTransaction.Commit();
result = true;
}
catch
{
dbTransaction.Rollback();
result = false;
}
finally
{
if (dbTransaction.Connection != null)
{
dbConnection.Close();
}
}
}
return(result);
}
public bool DeleteProducts(int actId, string ProductIds)
{
bool result = false;
using (System.Data.Common.DbConnection dbConnection = this.database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("Delete from Hishop_PointExChange_Products WHERE exChangeId ={0} and ProductId in ( {1} )", actId, ProductIds.ReplaceSingleQuoteMark()));
System.Data.Common.DbCommand sqlStringCommand2 = this.database.GetSqlStringCommand("update Hishop_PointExChange_PointExChanges set ProductNumber=ProductNumber-1 where Id=@Id");
this.database.AddInParameter(sqlStringCommand2, "Id", System.Data.DbType.Int32, actId);
try
{
this.database.ExecuteNonQuery(sqlStringCommand, dbTransaction);
this.database.ExecuteNonQuery(sqlStringCommand2, dbTransaction);
dbTransaction.Commit();
result = true;
}
catch
{
dbTransaction.Rollback();
result = false;
}
finally
{
if (dbTransaction.Connection != null)
{
dbConnection.Close();
}
}
}
return(result);
}
public void ReleaseConnection(DbConnection connection)
{
if (connection != null)
{
connection.Close();
}
}
/// <summary>
/// De ngat ke noi den Database
/// </summary>
public static void DB_CloseConnection()
{
if (DbConnection.State == ConnectionState.Open)
{
DbConnection.Close();
}
}
private static async Task <int> ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, object param)
{
var identity = new Identity(command.CommandText, command.CommandType, cnn, null, param?.GetType(), null);
var info = GetCacheInfo(identity, param, command.AddToCache);
bool wasClosed = cnn.State == ConnectionState.Closed;
using (var cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
{
try
{
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false);
}
var result = await cmd.ExecuteNonQueryAsync(command.CancellationToken).ConfigureAwait(false);
command.OnCompleted();
return(result);
}
finally
{
if (wasClosed)
{
cnn.Close();
}
}
}
}
private static async Task <IDataReader> ExecuteReaderImplAsync(IDbConnection cnn, CommandDefinition command)
{
Action <IDbCommand, object> paramReader = GetParameterReader(cnn, ref command);
DbCommand cmd = null;
bool wasClosed = cnn.State == ConnectionState.Closed;
try
{
cmd = (DbCommand)command.SetupCommand(cnn, paramReader);
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false);
}
var reader = await cmd.ExecuteReaderAsync(wasClosed?CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess : CommandBehavior.SequentialAccess, command.CancellationToken).ConfigureAwait(false);
wasClosed = false;
return(reader);
}
finally
{
if (wasClosed)
{
cnn.Close();
}
cmd?.Dispose();
}
}
/// <summary>
///
/// </summary>
/// <param name="cmd"></param>
/// <param name="comType"></param>
/// <param name="parameters"></param>
/// <param name="strError"></param>
/// <param name="intEffected"></param>
/// <returns></returns>
public DataSet GetDataSet(DbCommand cmd, CommandType comType, Dictionary <string, object> parameters, out string strError, out int intEffected)
{
intEffected = -1;
strError = "";
try
{
conn = cmd.Connection;
conn.Open();
}
catch (Exception ex)
{
strError = ex.Message;
return(null);
}
DataSet dataSet = new DataSet();
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
try
{
//System.Data.Common.DbCommand
DbDataAdapter dbAdapter = factory.CreateDataAdapter();
cmd.CommandType = comType;
if ((parameters != null))
{
cmd.Parameters.Clear();
if (parameters.Count > 0)
{
SetCmd(ref cmd, parameters);
}
}
dbAdapter.SelectCommand = cmd;
dbAdapter.FillLoadOption = LoadOption.PreserveChanges;
intEffected = dbAdapter.Fill(dataSet);
conn.Close();
}
catch (Exception ex)
{
strError = ex.Message;
conn.Close();
}
return(dataSet);
}
public static bool UpdateVote(VoteInfo vote)
{
Database database = DatabaseFactory.CreateDatabase();
bool result;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
VoteDao voteDao = new VoteDao();
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
if (!voteDao.UpdateVote(vote, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!voteDao.DeleteVoteItem(vote.VoteId, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
int num = 0;
if (vote.VoteItems != null)
{
foreach (VoteItemInfo current in vote.VoteItems)
{
current.VoteId = vote.VoteId;
current.ItemCount = 0;
num += voteDao.CreateVoteItem(current, dbTransaction);
}
if (num < vote.VoteItems.Count)
{
dbTransaction.Rollback();
result = false;
return(result);
}
}
dbTransaction.Commit();
result = true;
}
}
}
catch
{
dbTransaction.Rollback();
result = false;
}
finally
{
dbConnection.Close();
}
}
return(result);
}
//stap 2: connectie sluiten met de database
public static void RelaseConnection(DbConnection con)
{
if (con != null)
{
con.Close();
con = null;
}
}
public bool TestConnection(out string strError)
{
strError = "";
try
{
// conn = CreateDbConnection(out strError); Connection has been created if you run public DbCommand commandDB(out String strError, String connectionName = "")
conn.Open();
conn.Close();
return(true);
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
}
/// <summary>
/// closes the underlying stream
/// </summary>
public bool Close()
{
if (IsOpen())
{
_conn.Close();
}
return(true);
}
public static string UpdateAdjustCommssions(string orderId, string itemid, decimal commssionmoney, decimal adjustcommssion)
{
string text = string.Empty;
Database database = DatabaseFactory.CreateDatabase();
string result;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
OrderInfo orderInfo = ShoppingProcessor.GetOrderInfo(orderId);
if (orderId == null)
{
result = "订单编号不合法";
return(result);
}
int userId = DistributorsBrower.GetCurrentDistributors(true).UserId;
if (orderInfo.ReferralUserId != userId || orderInfo.OrderStatus != OrderStatus.WaitBuyerPay)
{
result = "不是您的订单";
return(result);
}
LineItemInfo lineItemInfo = orderInfo.LineItems[itemid];
if (lineItemInfo == null || lineItemInfo.ItemsCommission < adjustcommssion)
{
result = "修改金额过大";
return(result);
}
lineItemInfo.ItemAdjustedCommssion = adjustcommssion;
lineItemInfo.IsAdminModify = false;
if (!new LineItemDao().UpdateLineItem(orderId, lineItemInfo, dbTransaction))
{
dbTransaction.Rollback();
}
if (!new OrderDao().UpdateOrder(orderInfo, dbTransaction))
{
dbTransaction.Rollback();
result = "更新订单信息失败";
return(result);
}
dbTransaction.Commit();
text = "1";
}
catch (Exception ex)
{
text = ex.ToString();
dbTransaction.Rollback();
}
finally
{
dbConnection.Close();
}
result = text;
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="cmd"></param>
/// <param name="comType"></param>
/// <param name="parameters"></param>
/// <param name="strError"></param>
/// <returns></returns>
public int ExecuteSqlNonQuery(DbCommand cmd, CommandType comType, Dictionary <string, object> parameters, out string strError)
{
int intEffected = -1;
strError = "";
try
{
conn = cmd.Connection;
conn.Open();
}
catch (Exception ex)
{
strError = ex.Message;
return(intEffected);
}
try
{
cmd.CommandType = comType;
if ((parameters != null))
{
cmd.Parameters.Clear();
if (parameters.Count > 0)
{
SetCmd(ref cmd, parameters);
}
}
intEffected = cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
strError = ex.Message;
intEffected = -1;
}
finally
{
conn.Close();
}
return(intEffected);
}
public void closeConnection() // Use it if you need to brake long RUN;
{
try
{
conn.Close();
}
catch
{
}
}
private bool CreateAnonymous(out string errorMsg)
{
System.Data.Common.DbConnection dbConnection = null;
bool result;
try
{
System.Data.Common.DbConnection dbConnection2;
dbConnection = (dbConnection2 = new System.Data.SqlClient.SqlConnection(this.GetConnectionString()));
try
{
System.Data.Common.DbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
dbCommand.Connection = dbConnection;
dbCommand.CommandType = System.Data.CommandType.Text;
dbCommand.CommandText = "INSERT INTO aspnet_Users (UserName, LoweredUserName, IsAnonymous, UserRole, LastActivityDate, Password, PasswordFormat, PasswordSalt, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart) VALUES ('Anonymous', LOWER('Anonymous'), 1, @UserRole, @CreateDate, 'DVZTktxeMzDtXR7eik7Cdw==', 0, '', 1, 0, @CreateDate, @CreateDate, @CreateDate, CONVERT( datetime, '17540101', 112 ), 0, CONVERT( datetime, '17540101', 112 ), 0, CONVERT( datetime, '17540101', 112 ))";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserRole", UserRole.Anonymous));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CreateDate", System.DateTime.Now));
dbConnection.Open();
dbCommand.ExecuteNonQuery();
dbConnection.Close();
}
finally
{
if (dbConnection2 != null)
{
((System.IDisposable)dbConnection2).Dispose();
}
}
errorMsg = null;
result = true;
}
catch (System.Data.SqlClient.SqlException ex)
{
errorMsg = ex.Message;
if (dbConnection != null && dbConnection.State != System.Data.ConnectionState.Closed)
{
dbConnection.Close();
dbConnection.Dispose();
}
result = false;
}
return(result);
}
public bool AddPO(PO po)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_PO_Insert);
db.AddInParameter(dbCommand, "@sPOCode", DbType.String, po.POCode);
db.AddInParameter(dbCommand, "@mPOAmount", DbType.Currency, po.POAmount);
db.AddInParameter(dbCommand, "@mBalanceAmount", DbType.Currency, po.BalanceAmount);
db.AddInParameter(dbCommand, "@iPOCreatedUser", DbType.Int32, po.POCreatedUser);
db.AddInParameter(dbCommand, "@iSupId", DbType.Int32, po.SupId);
db.AddInParameter(dbCommand, "@dtPODate", DbType.DateTime, po.PODate);
db.AddInParameter(dbCommand, "@iRequestedBy", DbType.Int32, po.RequestedBy.Value);
db.AddInParameter(dbCommand, "@sPOComment", DbType.String, po.POComment);
db.AddOutParameter(dbCommand, "@iPOId", DbType.Int32, 4);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int32 newPOID = (Int32)db.GetParameterValue(dbCommand, "@iPOId");
if (newPOID > 0)
{
po.POId = newPOID;
if (this.UpdatePOItems(po, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool AddPO(PO po)");
transaction.Rollback();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public bool CreateReturn(ReturnsInfo returnInfo)//创建退回单
{
bool result;
if (returnInfo == null)
{
result = false;
}
else
{
Database database = DatabaseFactory.CreateDatabase();
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
if (!UpdateOrderStatusBeApplyForReturns(returnInfo.OrderId, dbTransaction))
{
dbTransaction.Rollback();
result = false;
return(result);
}
returnInfo.ReturnsId = AddReturnInfo(returnInfo, dbTransaction);
if (returnInfo.ReturnsId <= 0)
{
dbTransaction.Rollback();
result = false;
return(result);
}
if (returnInfo.ReturnsLineItem.Count > 0)
{
if (!AddReturnsItem(returnInfo, dbTransaction))
{
dbTransaction.Rollback();
result = false;
return(result);
}
}
dbTransaction.Commit();
result = true;
}
catch
{
dbTransaction.Rollback();
result = false;
}
finally
{
dbConnection.Close();
}
}
}
return(result);
}
/// <summary>
/// 关闭一个数据库连接
/// </summary>
/// <param name="dbConn">数据库连接对象</param>
public static void CloseConnection(DbConnection dbConn)
{
if (dbConn == null || dbConn.State != ConnectionState.Open)
{
return;
}
dbConn.Close();
dbConn.Dispose();
dbConn = null;
}
public bool AddPR(PurchaseReturns pr)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_PR_Insert);
db.AddInParameter(dbCommand, "@iGRNId", DbType.Int64, pr.GRNId);
db.AddInParameter(dbCommand, "@iCreatedBy", DbType.Int32, pr.CreatedBy);
db.AddInParameter(dbCommand, "@mTotalReturns", DbType.Currency, pr.TotalReturns);
db.AddInParameter(dbCommand, "@sComment", DbType.String, pr.Comment);
db.AddInParameter(dbCommand, "@dReturnDate", DbType.Date, pr.ReturnDate);
db.AddOutParameter(dbCommand, "@iPRId", DbType.Int32, 4);
db.AddOutParameter(dbCommand, "@sPRCode", DbType.String, 10);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int32 newPRID = (Int32)db.GetParameterValue(dbCommand, "@iPRId");
string prCode = (string)db.GetParameterValue(dbCommand, "@sPRCode");
if (newPRID > 0)
{
pr.PRId = newPRID;
pr.PRCode = prCode;
if (this.UpdatePRItems(pr, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool AddPR(PurchaseReturns pr)");
transaction.Rollback();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public void SinglePhaseCommit(SinglePhaseEnlistment en)
{
if (en == null)
{
throw new ArgumentNullException("en");
}
//
// Wait until IPendingWork members have completed (WinOE bugs 17580 and 13395)
try
{
handle.WaitOne();
}
catch (ObjectDisposedException)
{
// If an ObjectDisposedException is thrown because
// the WaitHandle has already closed, nothing to worry
// about. Move on.
}
lock (syncRoot)
{
try
{
this.transaction.Commit();
en.Committed();
}
catch (Exception e)
{
en.Aborted(e);
}
finally
{
if ((null != connection) && (ConnectionState.Closed != connection.State))
{
connection.Close();
connection = null;
}
}
}
}
/// <summary>
/// Closes the given connection if not null
/// </summary>
/// <param name="connection">the connection to be closed</param>
public static void CloseConnection(DbConnection connection)
{
if (connection == null)
{
return;
}
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
public bool Fun_CloseDB()
{
try
{
DBConn.Close();
return(true);
}
catch (Exception)
{
return(false);
}
}
public static bool UpdateGroupBuy(GroupBuyInfo groupBuy)
{
Globals.EntityCoding(groupBuy, true);
Database database = DatabaseFactory.CreateDatabase();
bool result;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
GroupBuyDao groupBuyDao = new GroupBuyDao();
if (!groupBuyDao.UpdateGroupBuy(groupBuy, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!groupBuyDao.DeleteGroupBuyCondition(groupBuy.GroupBuyId, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!groupBuyDao.AddGroupBuyCondition(groupBuy.GroupBuyId, groupBuy.GroupBuyConditions, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
dbTransaction.Commit();
result = true;
}
}
}
}
catch (Exception var_4_99)
{
dbTransaction.Rollback();
result = false;
}
finally
{
dbConnection.Close();
}
}
return(result);
}
public static bool UpdateLineItem(string string_0, OrderInfo order, int quantity)
{
ManagerHelper.CheckPrivilege(Privilege.EditOrders);
Database database = DatabaseFactory.CreateDatabase();
bool result;
bool flag;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
SalesProvider salesProvider = SalesProvider.Instance();
order.LineItems[string_0].Quantity = quantity;
order.LineItems[string_0].ShipmentQuantity = quantity;
order.LineItems[string_0].ItemAdjustedPrice = order.LineItems[string_0].ItemListPrice;
if (!salesProvider.UpdateLineItem(order.OrderId, order.LineItems[string_0], dbTransaction))
{
dbTransaction.Rollback();
result = false;
return(result);
}
if (!salesProvider.UpdateOrderAmount(order, dbTransaction))
{
dbTransaction.Rollback();
result = false;
return(result);
}
dbTransaction.Commit();
flag = true;
}
catch (Exception)
{
dbTransaction.Rollback();
flag = false;
}
finally
{
dbConnection.Close();
}
}
if (flag)
{
EventLogs.WriteOperationLog(Privilege.EditOrders, string.Format(CultureInfo.InvariantCulture, "修改了订单号为\"{0}\"的订单商品数量", new object[]
{
order.OrderId
}));
}
result = flag;
return(result);
}
public bool AddGetPass(GetPass getPass)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand cmd = db.GetStoredProcCommand(Constant.SP_GetPass_Add);
db.AddInParameter(cmd, "@sGPCode", DbType.String, getPass.GPCode);
db.AddInParameter(cmd, "@iInvoiceId", DbType.Int32, getPass.InvoiceId);
db.AddInParameter(cmd, "@iCreatedBy", DbType.Int32, getPass.CreatedBy);
db.AddOutParameter(cmd, "@biGPId", DbType.Int64, 8);
if (db.ExecuteNonQuery(cmd, transaction) > 0)
{
Int32 newGatePassID = Convert.ToInt32(db.GetParameterValue(cmd, "@biGPId"));
if (newGatePassID > 0)
{
getPass.GPId = newGatePassID;
if (this.UpdateGetPassDetails(getPass, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
transaction.Rollback();
result = false;
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
/// <summary>
/// Creates the cheque book and saves the cheques in tha dataset dsCheques property
/// </summary>
/// <param name="chqBook"></param>
/// <returns></returns>
public bool Create(ChequeBook chqBook)
{
bool success = true;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Cheque_Insert);
db.AddInParameter(dbCommand, "@iNoOfCheques", DbType.Int32, chqBook.NoOfCheques);
db.AddInParameter(dbCommand, "@biFirstChqNo", DbType.Int64, chqBook.FirstChqNo);
db.AddInParameter(dbCommand, "@biLastChqNo", DbType.Int64, chqBook.LastChqNo);
db.AddInParameter(dbCommand, "@iCreatedBy", DbType.Int32, chqBook.CreatedBy);
db.AddInParameter(dbCommand, "@sBankName", DbType.String, chqBook.BankName);
db.AddInParameter(dbCommand, "@sBankBranch", DbType.String, chqBook.BankBranch);
db.AddOutParameter(dbCommand, "@iChqBookId", DbType.Int32, 4);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int32 newChqBookId = Convert.ToInt32(db.GetParameterValue(dbCommand, "@iChqBookId"));
if (newChqBookId > 0)
{
chqBook.ChqBookId = newChqBookId;
if (this.UpdateCheques(chqBook, db, transaction))
{
transaction.Commit();
success = true;
}
}
}
}
catch (Exception ex)
{
success = false;
transaction.Rollback();
ex.Data.Add("BusinessLayerException", GetType().ToString() + Constant.Error_Seperator + "public bool Create(ChequeBook chqBook)");
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return success;
}
public void Add(Order order)
{
owner = GetOwner();
vendor = GetVendor(order.VendorID);
using (cn = dbContext.Database.Connection)
{
try
{
cn.Open();
tran = cn.BeginTransaction();
DbCommand cmd = cn.CreateCommand();
cmd.Transaction = tran;
cmd.CommandText = @"INSERT INTO tblSO (SOCode, SODate, BranchId, CurrencyId, EmployeeId, VendorID, TermID, TaxID, SOGrossAmt, SONetAmt, StatusID, Operator, Computer, LastUpdate)
VALUES (@SOCode, @SODate, @BranchId, @CurrencyId, @EmployeeId, @VendorId, @TermId, 0, @SOGrossAmt, @SONetAmt, 0, @Operator, @Computer, @LastUpdate); SELECT IDENT_CURRENT('tblSO');";
cmd.Parameters.Add(new SqlParameter("@SOCode", order.SOCode));
cmd.Parameters.Add(new SqlParameter("@SODate", order.SODate));
cmd.Parameters.Add(new SqlParameter("@BranchId", order.BranchID));
cmd.Parameters.Add(new SqlParameter("@CurrencyId", order.CurrencyId));
cmd.Parameters.Add(new SqlParameter("@EmployeeId", owner.EmployeeId));
cmd.Parameters.Add(new SqlParameter("@VendorId", vendor.Id));
cmd.Parameters.Add(new SqlParameter("@TermId", owner.DefTerm));
cmd.Parameters.Add(new SqlParameter("@SOGrossAmt", order.SOGrossAmt));
cmd.Parameters.Add(new SqlParameter("@SONetAmt", order.SONetAmt));
cmd.Parameters.Add(new SqlParameter("@Operator", order.Username));
cmd.Parameters.Add(new SqlParameter("@Computer", System.Environment.MachineName));
cmd.Parameters.Add(new SqlParameter("@LastUpdate", DateTime.Now));
soId = Convert.ToInt32(cmd.ExecuteScalar());
foreach (OrderItem item in order.Items)
{
AddOrderItem(item);
}
tran.Commit();
}
catch (Exception ex)
{
if (tran != null)
tran.Rollback();
throw ex;
}
finally
{
cn.Close();
}
}
}
public static bool AddSiteSettings(SiteSettings siteSettings, int requestId, int siteQty)
{
Database database = DatabaseFactory.CreateDatabase();
bool result;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
DistributorProvider distributorProvider = DistributorProvider.Instance();
if (!distributorProvider.AcceptSiteRequest(siteQty, requestId, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!distributorProvider.AddSiteSettings(siteSettings, requestId, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!DistributorProvider.Instance().AddInitData(siteSettings.UserId.Value, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
dbTransaction.Commit();
result = true;
}
}
}
}
catch
{
dbTransaction.Rollback();
result = false;
}
finally
{
dbConnection.Close();
}
}
return(result);
}
public static bool UpdateBundlingProduct(BundlingInfo bundlingInfo)
{
Database database = DatabaseFactory.CreateDatabase();
bool result;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
BundlingDao bundlingDao = new BundlingDao();
if (!bundlingDao.UpdateBundlingProduct(bundlingInfo, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!bundlingDao.DeleteBundlingByID(bundlingInfo.BundlingID, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!bundlingDao.AddBundlingProductItems(bundlingInfo.BundlingID, bundlingInfo.BundlingItemInfos, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
dbTransaction.Commit();
result = true;
}
}
}
}
catch (Exception var_4_91)
{
dbTransaction.Rollback();
result = false;
}
finally
{
dbConnection.Close();
}
}
return(result);
}
/// <summary>
/// Fechado a Conexão com o Banco de Dados
/// </summary>
/// <param name="Conn">Informe a Conexão</param>
private void CloseDB(System.Data.Common.DbConnection Conn)
{
try
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
}
catch (Exception ex)
{
throw new Exception("Falha ao Desconectar do Banco de Dados. Exception: " + ex.Message);
}
}
public void CloseConnection(DbConnection conn)
{
if (conn == null)
return;
try
{
if (conn.State != ConnectionState.Open)
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
public static bool ClearOrderGifts(OrderInfo order)
{
ManagerHelper.CheckPrivilege(Privilege.EditOrders);
Database database = DatabaseFactory.CreateDatabase();
bool result;
bool flag;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
SalesProvider salesProvider = SalesProvider.Instance();
order.Gifts.Clear();
if (!salesProvider.ClearOrderGifts(order.OrderId, dbTransaction))
{
dbTransaction.Rollback();
result = false;
return(result);
}
if (!salesProvider.UpdateOrderAmount(order, dbTransaction))
{
dbTransaction.Rollback();
result = false;
return(result);
}
dbTransaction.Commit();
flag = true;
}
catch
{
dbTransaction.Rollback();
flag = false;
}
finally
{
dbConnection.Close();
}
}
if (flag)
{
EventLogs.WriteOperationLog(Privilege.EditOrders, string.Format(CultureInfo.InvariantCulture, "清空了订单号为\"{0}\"的订单礼品", new object[]
{
order.OrderId
}));
}
result = flag;
return(result);
}
public static bool UpdateGroupBuy(GroupBuyInfo groupBuy)
{
Database database = DatabaseFactory.CreateDatabase();
bool result;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
if (!SubsitePromotionsProvider.Instance().UpdateGroupBuy(groupBuy, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!SubsitePromotionsProvider.Instance().DeleteGroupBuyCondition(groupBuy.GroupBuyId, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!SubsitePromotionsProvider.Instance().AddGroupBuyCondition(groupBuy.GroupBuyId, groupBuy.GroupBuyConditions, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
dbTransaction.Commit();
result = true;
}
}
}
}
catch (Exception)
{
dbTransaction.Rollback();
result = false;
}
finally
{
dbConnection.Close();
}
}
return(result);
}
public override void PrepareEngine(string connectionString, DbConnection connection)
{
try {
connection.Open();
}
catch {
SqlCeEngine e = new SqlCeEngine(connectionString);
e.Upgrade();
}
finally
{
connection.Close();
}
}
public static bool DeleteOrderGift(OrderInfo order, int giftId)
{
ManagerHelper.CheckPrivilege(Privilege.EditOrders);
Database database = DatabaseFactory.CreateDatabase();
bool result;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
SalesProvider salesProvider = SalesProvider.Instance();
OrderGiftInfo orderGift = salesProvider.GetOrderGift(giftId, order.OrderId);
order.Gifts.Remove(orderGift);
if (!salesProvider.DeleteOrderGift(order.OrderId, orderGift.GiftId, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!salesProvider.UpdateOrderAmount(order, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
dbTransaction.Commit();
EventLogs.WriteOperationLog(Privilege.EditOrders, string.Format(CultureInfo.InvariantCulture, "删除了订单号为\"{0}\"的订单礼品", new object[]
{
order.OrderId
}));
result = true;
}
}
}
catch
{
dbTransaction.Rollback();
result = false;
}
finally
{
dbConnection.Close();
}
}
return(result);
}
public bool AddGroups(Group group)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Groups_Insert);
db.AddInParameter(dbCommand, "@sGroupCode", DbType.String, group.GroupCode);
db.AddInParameter(dbCommand, "@sGroupName", DbType.String, group.GroupName);
db.AddInParameter(dbCommand, "@sDescription", DbType.String, group.Description);
db.AddInParameter(dbCommand, "@tiItemCount", DbType.Int16, group.ItemCount);
db.AddInParameter(dbCommand, "@bIsActive", DbType.Boolean, group.IsActive);
db.AddOutParameter(dbCommand, "@tiGroupId", DbType.Int16, 4);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int16 newGroupId = Convert.ToInt16(db.GetParameterValue(dbCommand, "@tiGroupId"));
if (newGroupId > 0)
{
group.GroupId = newGroupId;
if (this.UpdateGroupItems(group, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public bool AddItemTransfer(ItemTransfer itemTransfer)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Item_Transfer_Insert);
db.AddInParameter(dbCommand, "@iInvoiceId", DbType.Int32, DBNull.Value);
db.AddInParameter(dbCommand, "@iBranchFrom", DbType.Int32, itemTransfer.BranchFrom);
db.AddInParameter(dbCommand, "@iBranchTo", DbType.Int32, itemTransfer.BranchTo);
db.AddInParameter(dbCommand, "@iTransferQty", DbType.Int32, itemTransfer.TransferQty);
db.AddInParameter(dbCommand, "@iTransferBy", DbType.Int32, itemTransfer.TransferBy);
db.AddOutParameter(dbCommand, "@iTransferId", DbType.Int32, 4);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int32 newTransferID = Convert.ToInt32(db.GetParameterValue(dbCommand, "@iTransferId"));
if (newTransferID > 0)
{
itemTransfer.TransferId = newTransferID;
if (this.AddItemTransferInvoice(itemTransfer, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public virtual void ReleaseConnection(DbConnection connection)
{
CheckDisposed();
if (connection != null && connection.State == ConnectionState.Open)
{
try
{
connection.Close();
}
catch (Exception ex)
{
Log.Warn("Error closing database connection", ex);
}
}
}
private static void CloseDBConnection(DbConnection conn)
{
try
{
if (conn == null)
return;
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch (Exception ex)
{
Logger.Info("CloseDBConnection:" + ex.ToString());
}
}
public bool TestConnection(string providerName, string connectionString, out string strError)
{
strError = "";
try
{
conn = CreateDbConnection(providerName, connectionString, out strError);
conn.Open();
conn.Close();
return(true);
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
}
/// <summary>
/// Execute a command that returns multiple result sets, and access each in turn
/// </summary>
public static async Task <GridReader> QueryMultipleAsync(this IDbConnection cnn, CommandDefinition command)
{
object param = command.Parameters;
Identity identity = new Identity(command.CommandText, command.CommandType, cnn, typeof(GridReader), param?.GetType(), null);
CacheInfo info = GetCacheInfo(identity, param, command.AddToCache);
DbCommand cmd = null;
IDataReader reader = null;
bool wasClosed = cnn.State == ConnectionState.Closed;
try
{
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false);
}
cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader);
reader = await cmd.ExecuteReaderAsync(wasClosed?CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess : CommandBehavior.SequentialAccess, command.CancellationToken).ConfigureAwait(false);
var result = new GridReader(cmd, reader, identity, command.Parameters as DynamicParameters, command.AddToCache, command.CancellationToken);
wasClosed = false; // *if* the connection was closed and we got this far, then we now have a reader
// with the CloseConnection flag, so the reader will deal with the connection; we
// still need something in the "finally" to ensure that broken SQL still results
// in the connection closing itself
return(result);
}
catch
{
if (reader != null)
{
if (!reader.IsClosed)
{
try
{ cmd.Cancel(); }
catch
{ /* don't spoil the existing exception */ }
}
reader.Dispose();
}
cmd?.Dispose();
if (wasClosed)
{
cnn.Close();
}
throw;
}
}
private static void InitializeData(DbConnection connection)
{
var context =
new FeatureDbContext(
connection,
CompiledModels.GetModel<NumberFieldEntity>());
context.NumberFieldEntities.Add(
new NumberFieldEntity {
Value8 = 0x0f,
Value16 = 0x0f,
Value32 = 0x0f,
Value64 = 0x0f});
context.SaveChanges();
connection.Close();
}
public bool AddBrands(Brand brand)
{
bool result = false;
try
{
Database db = DatabaseFactory.CreateDatabase(Constant.Database_Connection_Name);
connection = db.CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
DbCommand dbCommand = db.GetStoredProcCommand(Constant.SP_Brands_Insert);
db.AddInParameter(dbCommand, "@sBrandName", DbType.String, brand.BrandName);
db.AddOutParameter(dbCommand, "@iBrandId", DbType.Int32, 4);
if (db.ExecuteNonQuery(dbCommand, transaction) > 0)
{
Int32 newBrandID = Convert.ToInt32(db.GetParameterValue(dbCommand, "@iBrandId"));
if (newBrandID > 0)
{
brand.BrandId = newBrandID;
if (this.UpdateBrandCategories(brand, db, transaction))
{
transaction.Commit();
result = true;
}
}
}
}
catch (System.Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return result;
}
public bool UpdateDiscountStatus(int Id, DiscountStatus status)
{
bool result = false;
using (System.Data.Common.DbConnection dbConnection = this.database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("update Hishop_LimitedTimeDiscount set Status=@Status WHERE LimitedTimeDiscountId = @Id");
this.database.AddInParameter(sqlStringCommand, "Id", System.Data.DbType.Int32, Id);
this.database.AddInParameter(sqlStringCommand, "Status", System.Data.DbType.Int32, (int)status);
System.Data.Common.DbCommand sqlStringCommand2;
if (DiscountStatus.Delete == status)
{
sqlStringCommand2 = this.database.GetSqlStringCommand("delete Hishop_LimitedTimeDiscountProduct WHERE LimitedTimeDiscountId = @Id");
this.database.AddInParameter(sqlStringCommand2, "Id", System.Data.DbType.Int32, Id);
}
else
{
sqlStringCommand2 = this.database.GetSqlStringCommand("update Hishop_LimitedTimeDiscountProduct set Status=@Status WHERE LimitedTimeDiscountId = @Id");
this.database.AddInParameter(sqlStringCommand2, "Id", System.Data.DbType.Int32, Id);
this.database.AddInParameter(sqlStringCommand2, "Status", System.Data.DbType.Int32, (int)status);
}
try
{
this.database.ExecuteNonQuery(sqlStringCommand, dbTransaction);
this.database.ExecuteNonQuery(sqlStringCommand2, dbTransaction);
dbTransaction.Commit();
result = true;
}
catch
{
dbTransaction.Rollback();
result = false;
}
finally
{
if (dbTransaction.Connection != null)
{
dbConnection.Close();
}
}
}
return(result);
}
public static bool UpdateAdjustCommssions(string orderId, string itemid, decimal adjustcommssion)
{
bool flag = false;
Database database = DatabaseFactory.CreateDatabase();
bool result;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
OrderInfo orderInfo = OrderHelper.GetOrderInfo(orderId);
if (orderInfo == null)
{
result = false;
return(result);
}
LineItemInfo lineItemInfo = orderInfo.LineItems[itemid];
lineItemInfo.ItemAdjustedCommssion = adjustcommssion;
if (!new LineItemDao().UpdateLineItem(orderId, lineItemInfo, dbTransaction))
{
dbTransaction.Rollback();
}
if (!new OrderDao().UpdateOrder(orderInfo, dbTransaction))
{
dbTransaction.Rollback();
result = false;
return(result);
}
dbTransaction.Commit();
flag = true;
}
catch (Exception var_6_9F)
{
dbTransaction.Rollback();
}
finally
{
dbConnection.Close();
}
result = flag;
}
return(result);
}
internal static void ExecuteNonQuery(string command, DbConnection conn, SQLiteFactory factory)
{
DbTransaction trans = conn.BeginTransaction(System.Data.IsolationLevel.Serializable);
try
{
conn.Open();
ExecuteNonQuery(command, conn, factory, trans);
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
}
finally
{
conn.Close();
}
}
public virtual bool Exists(DbConnection connection, int? commandTimeout, Lazy<StoreItemCollection> storeItemCollection)
{
DebugCheck.NotNull(connection);
if (connection.State == ConnectionState.Open)
{
return true;
}
try
{
return DbProviderServices.GetProviderServices(connection)
.DatabaseExists(connection, commandTimeout, storeItemCollection);
}
catch
{
// In situations where the user does not have access to the master database
// the above DatabaseExists call fails and throws an exception. Rather than
// just let that exception escape to the caller we instead try a different
// approach to see if the database really does exist or not. The approach
// is to try to open a connection to the database. If this succeeds then
// we know that the database exists. If it fails then the database may
// not exist or there may be some other issue connecting to it. In either
// case for the purpose of this call we assume that it does not exist and
// return false since this functionally gives the best experience in most
// scenarios.
try
{
connection.Open();
return true;
}
catch (Exception)
{
return false;
}
finally
{
connection.Close();
}
}
}
//методы отправки запросов
public DataTable sQuery(string QueryString)
{
DataTable _curDataTable = new DataTable();
using (_curConnection = _curDataObjFactory.CreateConnection())
{
_curConnection.ConnectionString = _connectionString;
_curConnection.Open();
using (DbCommand curCommand = _curDataObjFactory.CreateCommand())
{
curCommand.Connection = _curConnection;
curCommand.CommandText = QueryString;
//исполенение запроса
using (DbDataReader curDataReader = curCommand.ExecuteReader())
{
_curDataTable.Load(curDataReader);
curDataReader.Close();
}
}
_curConnection.Close();
}
return _curDataTable;
}
protected override string GetDbProviderManifestToken(DbConnection connection)
{
if (connection == null)
throw new ArgumentNullException("connection");
// TODO: used to use connection.ServerVersion
// that doesn't work with a closed connection.
bool openedConnection = false;
try
{
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
openedConnection = true;
}
return connection.ServerVersion;
}
finally
{
if (openedConnection)
{
connection.Close();
}
}
}
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <remarks>
/// 如果是BaseDbHelper打开连接,当连接关闭DataReader也将关闭.
/// 如果是调用都打开连接,DataReader由调用都管理.
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="transaction">一个有效的事务,或者为 'null'</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或SQL语句</param>
/// <param name="commandParameters">DbParameters参数数组,如果没有参数则为'null'</param>
/// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由BaseDbHelper提供</param>
/// <returns>返回包含结果集的DbDataReader</returns>
private static DbDataReader ExecuteReader(DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, DbParameter[] commandParameters, DbConnectionOwnership connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("connection");
//connection.Close();
connection.ConnectionString = GetRealConnectionString(commandText);
connection.Open();
bool mustCloseConnection = false;
// 创建命令
DbCommand cmd = Factory.CreateCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建数据阅读器
DbDataReader dataReader;
#if DEBUG
DateTime dt1 = DateTime.Now;
#endif
if (connectionOwnership == DbConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
#if DEBUG
DateTime dt2 = DateTime.Now;
m_querydetail += GetQueryDetail(cmd.CommandText, dt1, dt2, commandParameters);
#endif
m_querycount++;
// 清除参数,以便再次使用..
bool canClear = true;
foreach (DbParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
//cmd.Dispose();
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
public void insert(DbConnection conn)
{
if (conn.State != ConnectionState.Open)
conn.Open();
try
{
DbCommand dbCommand = conn.CreateCommand();
string insertQuery = MARKETDATA_SYMBOL_INFO_Table_DAO.InsertQuery_;
insertQuery = insertQuery.Replace("@SYMBOLVALUE@", SYMBOL_);
insertQuery = insertQuery.Replace("@NAME_ENGVALUE@", NAME_ENG_);
insertQuery = insertQuery.Replace("@NAME_KORVALUE@", NAME_KOR_);
insertQuery = insertQuery.Replace("@SYMBOL_TYPEVALUE@", SYMBOL_TYPE_);
insertQuery = insertQuery.Replace("@PROVIDERVALUE@", PROVIDER_);
insertQuery = insertQuery.Replace("@DESCRIPTIONVALUE@", DESCRIPTION_);
insertQuery = insertQuery.Replace("@TENORVALUE@", TENOR_);
insertQuery = insertQuery.Replace("@SUB_TYPEVALUE@", SUB_TYPE_);
insertQuery = insertQuery.Replace("@LINKEDCURVEVALUE@", LINKEDCURVE_);
insertQuery = insertQuery.Replace("@RATE_TYPEVALUE@", RATE_TYPE_);
insertQuery = insertQuery.Replace("@LEG_TENORVALUE@", LEG_TENOR_);
insertQuery = insertQuery.Replace("@CURRENCYVALUE@", CURRENCY_);
dbCommand.CommandText = insertQuery;
dbCommand.ExecuteNonQuery();
conn.Close();
}
catch (Exception)
{
conn.Close();
throw;
}
}
public void selectNAME_KOR(DbConnection conn)
{
if (conn.State != ConnectionState.Open)
conn.Open();
try
{
DataTable tb = new DataTable();
DbCommand dbCommand = conn.CreateCommand();
string selectNAME_KORQuery = MARKETDATA_SYMBOL_INFO_Table_DAO.SelectNAME_KORQuery_;
selectNAME_KORQuery = selectNAME_KORQuery.Replace("@NAME_KORValue@", this.NAME_KOR_);
dbCommand.CommandText = selectNAME_KORQuery;
//DbDataAdapter dataAdapter = new DbDataAdapter(dbCommand, conn);
DbDataAdapter dataAdapter = DataBaseConnectManager.CreateDataAdapter(dbCommand, conn);
dataAdapter.Fill(tb);
DataRow[] dr = tb.Select();
int rowCount = dr.Length;
if (!(rowCount == 0 || rowCount == 1))
{ throw new Exception(); }
foreach (DataRow item in dr)
{
this.SYMBOL_ = item[0].ToString();
this.NAME_ENG_ = item[1].ToString();
this.NAME_KOR_ = item[2].ToString();
this.SYMBOL_TYPE_ = item[3].ToString();
this.PROVIDER_ = item[4].ToString();
this.DESCRIPTION_ = item[5].ToString();
this.TENOR_ = item[6].ToString();
this.SUB_TYPE_ = item[7].ToString();
this.LINKEDCURVE_ = item[8].ToString();
this.RATE_TYPE_ = item[9].ToString();
this.LEG_TENOR_ = item[10].ToString();
this.CURRENCY_ = item[11].ToString();
}
conn.Close();
}
catch (Exception)
{
conn.Close();
throw;
}
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
/// </summary>
/// <remarks>
/// 示例:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new DbParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或SQL语句</param>
/// <param name="commandParameters">SqlParamter参数数组</param>
/// <returns>返回一个包含结果集的DataSet</returns>
public static DataSet ExecuteDataset(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// connection.Close();
connection.ConnectionString = GetRealConnectionString(commandText);
connection.Open();
// 预处理
DbCommand cmd = Factory.CreateCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (DbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建DbDataAdapter和DataSet.
using (DbDataAdapter da = Factory.CreateDataAdapter())
{
da.SelectCommand = cmd;
DataSet ds = new DataSet();
#if DEBUG
DateTime dt1 = DateTime.Now;
#endif
// 填充DataSet.
da.Fill(ds);
#if DEBUG
DateTime dt2 = DateTime.Now;
m_querydetail += GetQueryDetail(cmd.CommandText, dt1, dt2, commandParameters);
#endif
m_querycount++;
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return ds;
}
}
/// <summary>
/// 执行指定数据库连接对象的命令
/// </summary>
/// <remarks>
/// 示例:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new DbParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
/// <param name="commandText">T存储过程名称或SQL语句</param>
/// <param name="commandParameters">SqlParamter参数数组</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(out int id, DbConnection connection, CommandType commandType, string commandText, params DbParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (Provider.GetLastIdSql().Trim() == "") throw new ArgumentNullException("GetLastIdSql is \"\"");
// 创建DbCommand命令,并进行预处理
DbCommand cmd = Factory.CreateCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (DbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行命令
int retval = cmd.ExecuteNonQuery();
// 清除参数,以便再次使用.
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = Provider.GetLastIdSql();
#if DEBUG
DateTime dt1 = DateTime.Now;
#endif
id = TypeConverter.ObjectToInt(cmd.ExecuteScalar());
#if DEBUG
DateTime dt2 = DateTime.Now;
m_querydetail += GetQueryDetail(cmd.CommandText, dt1, dt2, commandParameters);
#endif
m_querycount++;
if (mustCloseConnection)
{
connection.Close();
}
return retval;
}
/// <summary>
/// 执行指定数据库连接对象的命令
/// </summary>
/// <remarks>
/// 示例:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new DbParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
/// <param name="commandText">T存储过程名称或SQL语句</param>
/// <param name="commandParameters">SqlParamter参数数组</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQuery(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// 创建DbCommand命令,并进行预处理
DbCommand cmd = Factory.CreateCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (DbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
#if DEBUG
DateTime dt1 = DateTime.Now;
#endif
// 执行DbCommand命令,并返回结果.
int retval = cmd.ExecuteNonQuery();
#if DEBUG
DateTime dt2 = DateTime.Now;
m_querydetail += GetQueryDetail(cmd.CommandText, dt1, dt2, commandParameters);
m_querycount++;
#endif
// 清除参数,以便再次使用.
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
/// <summary>
/// 探索运行时的存储过程,返回DbParameter参数数组.
/// 初始化参数值为 DBNull.Value.
/// </summary>
/// <param name="connection">一个有效的数据库连接</param>
/// <param name="spName">存储过程名称</param>
/// <param name="includeReturnValueParameter">是否包含返回值参数</param>
/// <returns>返回DbParameter参数数组</returns>
private static DbParameter[] DiscoverSpParameterSet(DbConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
if (connection.State == ConnectionState.Open)
connection.Close();
connection.ConnectionString = GetRealConnectionString(spName);
connection.Open();
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
// 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中.
Provider.DeriveParameters(cmd);
connection.Close();
// 如果不包含返回值参数,将参数集中的每一个参数删除.
if (!includeReturnValueParameter)
cmd.Parameters.RemoveAt(0);
// 创建参数数组
DbParameter[] discoveredParameters = new DbParameter[cmd.Parameters.Count];
// 将cmd的Parameters参数集复制到discoveredParameters数组.
cmd.Parameters.CopyTo(discoveredParameters, 0);
// 初始化参数值为 DBNull.Value.
foreach (DbParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}
/// <summary>
/// 执行指定数据库事务的命令,映射数据表并填充数据集,指定存储过程参数值.
/// </summary>
/// <remarks>
/// 此方法不提供访问存储过程输出参数和返回值参数.
///
/// 示例:
/// FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);
/// </remarks>
/// <param name="transaction">一个有效的连接事务</param>
/// <param name="spName">存储过程名称</param>
/// <param name="dataSet">要填充结果集的DataSet实例</param>
/// <param name="tableNames">表映射的数据表数组
/// 用户定义的表名 (可有是实际的表名.)
/// </param>
/// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
//public static void FillDataset(DbTransaction transaction, string spName, DataSet dataSet, string[] tableNames, params object[] parameterValues)
//{
// if (transaction == null) throw new ArgumentNullException("transaction");
// if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// if (dataSet == null) throw new ArgumentNullException("dataSet");
// if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// // 如果有参数值
// if ((parameterValues != null) && (parameterValues.Length > 0))
// {
// // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. ()
// DbParameter[] commandParameters = GetSpParameterSet(transaction.Connection, spName);
// // 给存储过程参数赋值
// AssignParameterValues(commandParameters, parameterValues);
// // 调用重载方法
// FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
// }
// else
// {
// // 没有参数值
// FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
// }
//}
/// <summary>
/// [私有方法][内部调用]执行指定数据库连接对象/事务的命令,映射数据表并填充数据集,DataSet/TableNames/DbParameters.
/// </summary>
/// <remarks>
/// 示例:
/// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new DbParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="transaction">一个有效的连接事务</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名称或SQL语句</param>
/// <param name="dataSet">要填充结果集的DataSet实例</param>
/// <param name="tableNames">表映射的数据表数组
/// 用户定义的表名 (可有是实际的表名.)
/// </param>
/// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>
private static void FillDataset(DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params DbParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// 创建DbCommand命令,并进行预处理
DbCommand command = Factory.CreateCommand();
bool mustCloseConnection = false;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行命令
using (DbDataAdapter dataAdapter = Factory.CreateDataAdapter())
{
dataAdapter.SelectCommand = command;
// 追加表映射
if (tableNames != null && tableNames.Length > 0)
{
string tableName = "Table";
for (int index = 0; index < tableNames.Length; index++)
{
if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
dataAdapter.TableMappings.Add(tableName, tableNames[index]);
tableName += (index + 1).ToString();
}
}
// 填充数据集使用默认表名称
dataAdapter.Fill(dataSet);
// 清除参数,以便再次使用.
command.Parameters.Clear();
}
if (mustCloseConnection)
connection.Close();
}
/// <summary>
/// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
/// </summary>
/// <remarks>
/// 示例:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new DbParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名称或SQL语句</param>
/// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object ExecuteScalar(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
//connection.Close();
connection.ConnectionString = GetRealConnectionString(commandText);
connection.Open();
// 创建DbCommand命令,并进行预处理
DbCommand cmd = Factory.CreateCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (DbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// 执行DbCommand命令,并返回结果.
object retval = cmd.ExecuteScalar();
// 清除参数,以便再次使用.
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
