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; }