// Run_Transaction_Non_Query using string Cmd public bool Run_Transaction_Non_Query(string Cmd_String) { if (Is_Connected()) { Open_Con(); SqlTransaction Sqltransaction = SqlCon.BeginTransaction(); try { using (SqlCommand SqlCmd = new SqlCommand(Cmd_String, SqlCon, Sqltransaction)) { SqlCmd.ExecuteNonQuery(); Sqltransaction.Commit(); return(true); }; } catch { Sqltransaction.Rollback(); return(false); } finally { Sqltransaction.Dispose(); Close_Con(); } } return(false); }
/// <summary> /// Constructs an instance of an atom transaction scope. /// </summary> /// <param name="kind">Type of transaction scope.</param> /// <param name="conn">Connection to use for the transaction scope.</param> protected internal SqlStoreTransactionScope(StoreTransactionScopeKind kind, SqlConnection conn) { this.Kind = kind; _conn = conn; switch (this.Kind) { case StoreTransactionScopeKind.ReadOnly: SqlUtils.WithSqlExceptionHandling(() => { _tran = conn.BeginTransaction(IsolationLevel.ReadCommitted); }); break; case StoreTransactionScopeKind.ReadWrite: SqlUtils.WithSqlExceptionHandling(() => { _tran = conn.BeginTransaction(IsolationLevel.RepeatableRead); }); break; default: // Do not start any transaction. Debug.Assert(this.Kind == StoreTransactionScopeKind.NonTransactional); break; } }
public int NonQuery(string sqlstr) { int result = 0; string msg = ""; if (CONN.State != ConnectionState.Open) { CONN.Open(); } CMD.Connection = CONN; CMD.CommandText = sqlstr; CMD.CommandType = CommandType.Text; SqlTransaction trans = CONN.BeginTransaction(); CMD.Transaction = trans; try { result = CMD.ExecuteNonQuery(); trans.Commit(); CONN.Close(); } catch (Exception ex) { trans.Rollback(); result = 0; msg = ex.Message; } finally { if (CONN.State == ConnectionState.Open) { // trans.Commit(); CONN.Close(); } trans.Dispose(); } try //log用另外的try防止檔案被咬 { if (msg != "") { WriteCMD(); WriteLog("[" + DateTime.Now.ToLongTimeString() + "][Main.NonQuery.Error]:" + msg + sqlstr); } else { if (LogStat >= 1) { WriteCMD(); } WriteLog(sqlstr); } } catch { } return(result); }
static void Main(string[] args) { using (var sqlConnection = new SqlConnection("connectionString")) { sqlConnection.Open(); // Create the Service Broker Service and Queue if they don't exist. using (var sqlTransaction = sqlConnection.BeginTransaction()) { ServiceBrokerWrapper.CreateServiceAndQueue(sqlTransaction, @"[\\Example\Service]", "ExampleServiceQueue"); sqlTransaction.Commit(); } // Send a single message to a Service endpoint and immediately // end this side of the conversation using (var sqlTransaction = sqlConnection.BeginTransaction()) { var messageData = new byte[1000]; var conversationHandle = ServiceBrokerWrapper.SendOne( sqlTransaction , @"[\\Example\Service2]" , @"[\\Example\Service]" , "MessageContractName" , "MessageType" , messageData); sqlTransaction.Commit(); } // Wait for a message to be available on the queue. We will // wait for some number of milliseconds. If the timeout expires // then the method returns "null". Otherwise it will contain // the message received from the queue. using (var sqlTransaction = sqlConnection.BeginTransaction()) { Message message = null; while (message == null) { message = ServiceBrokerWrapper.WaitAndReceive(sqlTransaction, "ExampleServiceQueue", 60 * 60 * 1000); } // ...handle message... // If we rollback the transaction then the message will // return to the queue to be handled again. // If we commit the transaction then we're done. sqlTransaction.Commit(); } } }
public void updateProduct(int productID, Products product) { SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True"); SqlCommand command = con.CreateCommand(); con.Open(); SqlTransaction transaction; // Start a local transaction. transaction = con.BeginTransaction(); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = con; command.Transaction = transaction; command.CommandText = "UPDATE productTable SET name = @name, price = @price, inStock = @inStock, min = @min, max = @max WHERE productID = @productID;"; command.Parameters.AddWithValue("@productID", productID); command.Parameters.AddWithValue("@name", product.getName()); command.Parameters.AddWithValue("@price", product.getPrice()); command.Parameters.AddWithValue("@inStock", product.getInStock()); command.Parameters.AddWithValue("@min", product.getProductMin()); command.Parameters.AddWithValue("@max", product.getMax()); var affected = command.ExecuteNonQuery(); transaction.Commit(); con.Close(); }
public void addProduct(Products product) { SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True"); SqlCommand command = con.CreateCommand(); con.Open(); SqlTransaction transaction; // Start a local transaction. transaction = con.BeginTransaction(); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = con; command.Transaction = transaction; command.CommandText = "INSERT INTO [dbo].[productTable] ([productID], [name], [price], [inStock], [min], [max]) VALUES (@productID, @name, @price, @inStock, @min, @max); SELECT productID, name, price, inStock, min, max FROM productTable WHERE(productID = @productID)"; command.Parameters.AddWithValue("@productID", product.getProductID()); command.Parameters.AddWithValue("@name", product.getName()); command.Parameters.AddWithValue("@price", product.getPrice()); command.Parameters.AddWithValue("@inStock", product.getInStock()); command.Parameters.AddWithValue("@min", product.getProductMin()); command.Parameters.AddWithValue("@max", product.getMax()); command.ExecuteNonQuery(); transaction.Commit(); con.Close(); }
public void GuardarOmniPack() { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); try { if (ChoferID == 0) { Config.Conexion.EjecutarSinResultados(transaccion, "ChoferOMNIINS", Utiles.BaseDatos.StrToSql(ChoferEmpresaSITT), Utiles.BaseDatos.StrToSql(LegajoSITT), Utiles.BaseDatos.StrToSql(Nombre), Utiles.BaseDatos.IntToSql(UnidadNegocioID)); } else { Config.Conexion.EjecutarSinResultados(transaccion, "ChoferOMNIUPD", Utiles.BaseDatos.IntToSql(ChoferID), Utiles.BaseDatos.StrToSql(ChoferEmpresaSITT), Utiles.BaseDatos.StrToSql(LegajoSITT), Utiles.BaseDatos.StrToSql(Nombre), Utiles.BaseDatos.IntToSql(UnidadNegocioID)); } transaccion.Commit(); } catch (Exception ex) { transaccion.Rollback(); throw ex; } }
private bool disposed = false; //Disposeを何度も呼び出させない public MyModel() { // 接続文字列を生成する string connectString = "Data Source = " + Settings.DatabaseServer + ";Initial Catalog = " + Settings.DatabaseName + ";User ID = " + Settings.DatabaseUser + ";Password = "******"データベース接続エラー " + ex.Message, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error); sqlConn.Close(); Application.Exit(); } }
public void ExecuteSqlTran(Hashtable SQLStringList) { using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { sqlConnection.Open(); using (System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction()) { System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(); try { foreach (DictionaryEntry dictionaryEntry in SQLStringList) { string cmdText = dictionaryEntry.Key.ToString(); System.Data.SqlClient.SqlParameter[] cmdParms = (System.Data.SqlClient.SqlParameter[])dictionaryEntry.Value; DbHelperSQLP.PrepareCommand(sqlCommand, sqlConnection, sqlTransaction, cmdText, cmdParms); int num = sqlCommand.ExecuteNonQuery(); sqlCommand.Parameters.Clear(); } sqlTransaction.Commit(); } catch { sqlTransaction.Rollback(); throw; } } } }
public int ExecuteSqlTran(List <string> SQLStringList) { int result; using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { sqlConnection.Open(); System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(); sqlCommand.Connection = sqlConnection; System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction(); sqlCommand.Transaction = sqlTransaction; try { int num = 0; for (int i = 0; i < SQLStringList.Count; i++) { string text = SQLStringList[i]; if (text.Trim().Length > 1) { sqlCommand.CommandText = text; num += sqlCommand.ExecuteNonQuery(); } } sqlTransaction.Commit(); result = num; } catch { sqlTransaction.Rollback(); result = 0; } } return(result); }
public static int InsertBySQL(List <string> SQL) { System.Data.SqlClient.SqlConnection oracleConnection = new System.Data.SqlClient.SqlConnection(constr); System.Data.SqlClient.SqlCommand oracleCommand = new System.Data.SqlClient.SqlCommand(); oracleCommand.Connection = oracleConnection; int rowsAffected = 0; oracleConnection.Open(); System.Data.SqlClient.SqlTransaction tran = oracleConnection.BeginTransaction(); try { foreach (string s in SQL) { oracleCommand.Transaction = tran; oracleCommand.CommandText = s; rowsAffected += oracleCommand.ExecuteNonQuery(); } tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw new NotSupportedException(ex.Message); } finally { oracleConnection.Close(); } return(rowsAffected); }
public void GuardarPlanilla(DsPlanillaRedespacho ds) { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); try { SqlParameter pAgenciaID = new SqlParameter("@AgenciaID", Utiles.BaseDatos.IntToSql(AgenciaID)); SqlParameter pUsuarioID = new SqlParameter("@UsuarioID", Utiles.BaseDatos.IntToSql(UsuarioID)); PlanillaRedespachoID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico("PlanillaRedespachoCabeceraINS", AgenciaID, UsuarioID)); if (PlanillaRedespachoID > 0) { GuardarDetallePlanilla(transaccion, ds); transaccion.Commit(); } else { transaccion.Rollback(); Exception ex = new Exception("No se pudo guardar la planilla"); throw ex; } } catch (Exception ex) { transaccion.Rollback(); throw ex; } }
public bool GuardarCliente(int guia) { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); try { // actualizo el estado de la Solicitud // Config.Conexion.EjecutarResultadoUnico(transaccion, "ClienteEventualGuiaINS",guia); transaccion.Commit(); return(true); } catch (Exception ex) { transaccion.Rollback(); throw ex; } finally { conexion.Close(); } }
public bool Guardar(int usuarioID) { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); try { this.CotizacionClienteUVentaDistribucionID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "CotizacionClienteUVentaDistribucionINS", Utiles.BaseDatos.IntToSql(CotizacionClienteUVentaID), PagaSeguro, Utiles.BaseDatos.DoubleToSql(SeguroTantoPorMil), Utiles.BaseDatos.DoubleToSql(ValorDeclaradoMin), Utiles.BaseDatos.DoubleToSql(ValorDeclaradoMax), usuarioID, Utiles.BaseDatos.DoubleToSql(ValorConversionM3), FechaVencimientoSeguro)); transaccion.Commit(); return(true); } catch (Exception ex) { transaccion.Rollback(); throw new Exception(ex.Message, ex); } finally { conexion.Close(); } }
public bool deletePart(Part part) { SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True"); SqlCommand command = con.CreateCommand(); con.Open(); SqlTransaction transaction; // Start a local transaction. transaction = con.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = con; command.Transaction = transaction; command.CommandText = "DELETE FROM[dbo].[partTable] WHERE(([partID] = @partID) AND([price] = @price) AND ([inStock] = @inStock) AND([min] = @min) AND([max] = @max))"; command.Parameters.AddWithValue("@partID", part.getParttID()); command.Parameters.AddWithValue("@price", part.getPrice()); command.Parameters.AddWithValue("@inStock", part.getInStock()); command.Parameters.AddWithValue("@min", part.getMin()); command.Parameters.AddWithValue("@max", part.getMax()); command.ExecuteNonQuery(); transaction.Commit(); con.Close(); return(true); }
protected void ButInput_Click(object sender, System.EventArgs e) { //���浽���ݿ� int i=0; string strConn=ConfigurationSettings.AppSettings["strConn"]; SqlConnection ObjConn =new SqlConnection(strConn); ObjConn.Open(); SqlTransaction ObjTran=ObjConn.BeginTransaction(); SqlCommand ObjCmd=new SqlCommand(); ObjCmd.Transaction=ObjTran; ObjCmd.Connection=ObjConn; try { ObjCmd.CommandText="Update UserInfo set DeptID=0 where DeptID="+intDeptID+""; ObjCmd.ExecuteNonQuery(); for(i=0;i<LBSelected.Items.Count;i++) { ObjCmd.CommandText="Update UserInfo set DeptID="+intDeptID+" where UserID="+LBSelected.Items[i].Value+""; ObjCmd.ExecuteNonQuery(); } ObjTran.Commit(); } catch { ObjTran.Rollback(); } finally { ObjConn.Close(); ObjConn.Dispose(); } this.RegisterStartupScript("newWindow","<script language='javascript'>window.close();</script>"); }
public void AddMemberInfo(List<MemberInfo> list) { //创建连接对象 using (SqlConnection con = new SqlConnection(SqlHelper.str)) { con.Open(); SqlTransaction sqlTran = con.BeginTransaction();//开启 try { for (int i = 0; i < list.Count; i++) { MemberInfo member = list[i]; string sql = "insert into MemberInfo( MemName, MemMobilePhone, DelFlag)values( @MemName, @MemMobilePhone, @DelFlag)"; SqlParameter[] ps = { new SqlParameter("@MemName",member.MemName), new SqlParameter("@MemMobilePhone",member.MemMobilePhone), new SqlParameter("@DelFlag",member.DelFlag) }; SqlHelper.ExecuteNonQuery(con, sqlTran, sql, ps); } sqlTran.Commit();//提交 } catch (Exception ex) { sqlTran.Rollback();//回滚 throw; } } }
public static void BulkCopy(DataTable table, string connectionString) { using (var connection = new SqlConnection(connectionString)) { SqlTransaction transaction = null; connection.Open(); try { transaction = connection.BeginTransaction(); using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction)) { sqlBulkCopy.BatchSize = table.Rows.Count; sqlBulkCopy.DestinationTableName = table.TableName; sqlBulkCopy.MapColumns(table); sqlBulkCopy.WriteToServer(table); } transaction.Commit(); } catch { transaction?.Rollback(); throw; } } }
public int Guardar() { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); try { SqlParameter pObservacionID = new SqlParameter("@ObservacionID", Utiles.BaseDatos.IntToSql(ObservacionID)); SqlParameter pCodigo = new SqlParameter("@Codigo", Utiles.BaseDatos.IntToSql(Codigo)); SqlParameter pObsDescrip = new SqlParameter("@ObsDescrip", Utiles.BaseDatos.StrToSql(ObsDescrip)); SqlParameter pTipoObservacionID = new SqlParameter("@TipoObservacionID", Utiles.BaseDatos.IntToSql(TipoObservacionID)); SqlParameter pHoras = new SqlParameter("@Horas", Utiles.BaseDatos.DoubleToSql(Horas)); SqlParameter pImpactoID = new SqlParameter("@ImpactoID", Utiles.BaseDatos.IntToSql(ImpactoID)); SqlParameter pNoTomarFallo = new SqlParameter("@NoTomarFallo", NoTomarFallo); if (ObservacionID > 0) { Config.Conexion.EjecutarSinResultados(System.Data.CommandType.StoredProcedure, "ObservacionUPD", pObservacionID, pCodigo, pObsDescrip, pTipoObservacionID, pHoras, pImpactoID, pNoTomarFallo); } else { ObservacionID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(System.Data.CommandType.StoredProcedure, "ObservacionINS", pCodigo, pObsDescrip, pTipoObservacionID, pHoras, pImpactoID, pNoTomarFallo)); } transaccion.Commit(); return ObservacionID; } catch (Exception ex) { transaccion.Rollback(); throw ex; } }
/// <summary> /// Creates new ability in database. /// </summary> /// <param name="abilityname">Name of ability.</param> /// <returns>ID of new database record.</returns> public override int CreateAbility(string abilityname) { int id = -1; using (SqlConnection connection = new SqlConnection(this.ConnectionString)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); SqlCommand command = connection.CreateCommand(); command.Transaction = transaction; command.CommandText = string.Format( "INSERT INTO {0} (Name) VALUES (@Ability) SELECT @@IDENTITY", DBAbilityTableName); command.Parameters.Add("@Ability", SqlDbType.NVarChar).Value = abilityname; try { id = Convert.ToInt32(ExecuteScalar(command)); transaction.Commit(); } catch { transaction.Rollback(); } } return id; }
public static void SetUp() { Console.WriteLine("Assembly Setup beginning..."); if (ConfigurationManager.AppSettings["connectionStringName"] == "subtextExpress") { //For use with SQL Express. If you use "subtextData", we assume you already have the database created. DatabaseHelper.CreateAndInstallDatabase(Config.ConnectionString, Config.ConnectionString.Database, "App_Data"); } else { using (var connection = new SqlConnection(Config.ConnectionString)) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { try { ScriptHelper.ExecuteScript("StoredProcedures.sql", transaction); transaction.Commit(); } catch (Exception) { transaction.Rollback(); } } } } }
public void Write(IEnumerable<Row> rows) { var sql = _context.SqlUpdateCalculatedFields(_original); var temp = new List<Field> { _context.Entity.Fields.First(f => f.Name == Constants.TflKey) }; temp.AddRange(_context.Entity.CalculatedFields.Where(f => f.Output && f.Name != Constants.TflHashCode)); var fields = temp.ToArray(); var count = 0; using (var cn = new SqlConnection(_context.Connection.GetConnectionString())) { cn.Open(); foreach (var batch in rows.Partition(_context.Entity.UpdateSize)) { var trans = cn.BeginTransaction(); var batchCount = cn.Execute( sql, batch.Select(r => r.ToExpandoObject(fields)), trans, _context.Connection.Timeout, CommandType.Text ); trans.Commit(); count += batchCount; _context.Increment(batchCount); } _context.Info("{0} to {1}", count, _context.Connection.Name); } _context.Entity.Updates += count; }
private void saveData(DataTable dt) { using (SqlConnection c = new SqlConnection(csRemoto)) { c.Open(); SqlTransaction t = c.BeginTransaction(IsolationLevel.RepeatableRead); SqlCommand cmdEliminar = new SqlCommand("delete from " + tableName, c, t); cmdEliminar.ExecuteNonQuery(); SqlCommand cmdInsertar = new SqlCommand(sqlRemoto, c, t); SqlParameter[] pConsulta = new SqlParameter[pRemoto.Length]; for (int i = 0; i < pRemoto.Length; i++) { pConsulta[i] = cmdInsertar.Parameters.Add(pRemoto[i].ParameterName, pRemoto[i].SqlDbType); } foreach (DataRow r in dt.Rows) { foreach (SqlParameter p in pConsulta) { p.Value = r[p.ParameterName]; } cmdInsertar.ExecuteNonQuery(); } t.Commit(); c.Close(); } }
protected void Button1_Click(object sender, EventArgs e) { SqlTransaction myTransaction = null; { try { SqlConnection conn = new SqlConnection(@"Data Source=ajjpsqlserverdb.db.4338448.hostedresource.com; database=ajjpsqlserverdb; User ID=ajjpsqlserverdb; Password= Devry2010;"); conn.Open(); SqlCommand command = conn.CreateCommand(); string strSQL; string txtBoxText = TextBox1.Text; txtBoxText = txtBoxText.Replace("'", "''"); myTransaction = conn.BeginTransaction(); command.Transaction = myTransaction; strSQL = "UPDATE aspnet_Membership SET Password = '******' WHERE UserID = '" + DropDownList1.SelectedValue + "'"; command.CommandType = System.Data.CommandType.Text; command.CommandText = strSQL; command.ExecuteNonQuery(); myTransaction.Commit(); command.Connection.Close(); Response.Redirect("~/User/Main.aspx"); } catch (Exception ex) { lblErr.Text = ex.Message; } } }
public static int ExecuteNonQuery(string cmdText, CommandType type, params SqlParameter[] pars) { SqlConnection conn = new SqlConnection(connStr); SqlTransaction tran = null; try { using (SqlCommand cmd = new SqlCommand(cmdText, conn, tran)) { cmd.CommandType = type; cmd.Parameters.AddRange(pars); conn.Open(); tran = conn.BeginTransaction(); tran.Commit(); return cmd.ExecuteNonQuery(); } } catch (Exception) { tran.Rollback(); throw; } finally { conn.Close(); } }
public void Import(string connectionString, StatbookModel statbook, bool assumeATeams) { _connection = new SqlConnection(connectionString); try { _connection.Open(); _transaction = _connection.BeginTransaction(); // insert leagues LeagueGateway leagueGateway = new LeagueGateway(_connection, _transaction); var leagues = leagueGateway.GetAllLeagues(); League homeLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.HomeTeam.LeagueName.ToLower()); League awayLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.AwayTeam.LeagueName.ToLower()); int maxID = leagues.Select(l => l.ID).Max(); if(homeLeague == null) { homeLeague = leagueGateway.GetLeague(maxID + 1, statbook.HomeTeam.LeagueName, statbook.Date, false); maxID++; } if(awayLeague == null) { awayLeague = leagueGateway.GetLeague(maxID + 1, statbook.AwayTeam.LeagueName, statbook.Date, false); maxID++; } // insert teams TeamGateway teamGateway = new TeamGateway(_connection, _transaction); Team homeTeam, awayTeam; if (assumeATeams) { homeTeam = teamGateway.GetATeam(homeLeague.ID); awayTeam = teamGateway.GetATeam(awayLeague.ID); } else { homeTeam = teamGateway.GetTeam(statbook.HomeTeam.Name, homeLeague.ID, "A", false); awayTeam = teamGateway.GetTeam(statbook.AwayTeam.Name, awayLeague.ID, "A", false); } // insert bout BoutGateway boutGateway = new BoutGateway(_connection, _transaction); if(!boutGateway.DoesBoutExist(homeTeam.ID, awayTeam.ID, statbook.Date)) { Bout bout = boutGateway.GetBout(homeTeam.ID, awayTeam.ID, statbook.Date); BoutDataImport(statbook, bout, homeTeam, awayTeam); } else { // bout already exists Console.WriteLine(string.Format("Bout between {0} and {1} on {2} already exists.", homeTeam.Name, awayTeam.Name, statbook.Date)); } _transaction.Commit(); } finally { _connection.Close(); } }
public static bool AddTaskReplyAttachments(string taskid, string replyid, List<Attachment> attachments,string userid,string clientid) { SqlConnection conn = new SqlConnection(TaskDAL.ConnectionString); conn.Open(); SqlTransaction tran = conn.BeginTransaction(); foreach (var attachment in attachments) { if (!TaskDAL.BaseProvider.AddTaskReplyAttachment(taskid,replyid,attachment.Type, attachment.ServerUrl,attachment.FilePath,attachment.FileName,attachment.OriginalName,attachment.ThumbnailName,attachment.Size, userid,clientid, tran)) { tran.Rollback(); conn.Dispose(); return false; } } tran.Commit(); conn.Dispose(); return true; }
public bool removeAssociatedPart(int removeID) { SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True"); SqlCommand command = con.CreateCommand(); con.Open(); SqlTransaction transaction; // Start a local transaction. transaction = con.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = con; command.Transaction = transaction; command.CommandText = "DELETE FROM[dbo].[associatedParts] WHERE productID = @productID and partID = @partID"; command.Parameters.AddWithValue("@productID", getProductID()); command.Parameters.AddWithValue("@partID", removeID); command.ExecuteNonQuery(); transaction.Commit(); con.Close(); return(true); }
public bool Guardar(IConvenioRedespachoDetalle detalle) { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); try { this.ConvenioReDespachoID = Convert.ToInt32( Config.Conexion.EjecutarResultadoUnico(transaccion, "ConvenioRedespachoINS", this.EmpresaProveedoraID)); detalle.ConvenioReDespachoID = this.ConvenioReDespachoID; detalle.Guardar(transaccion); transaccion.Commit(); return(true); } catch (Exception ex) { transaccion.Rollback(); throw ex; } finally { conexion.Close(); } }
public Transaction BeginTransaction() { System.Data.SqlClient.SqlTransaction tr = Conn.BeginTransaction(); Transaction result = new Transaction(tr); return(result); }
public void ActualizarOrdenMasivo(int PedidoID, int OrdenID) { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); try { // actualizo el estado de la Solicitud Config.Conexion.EjecutarResultadoUnico(transaccion, "UPDATEOrdenCliente", OrdenID, PedidoID); transaccion.Commit(); } catch (Exception ex) { transaccion.Rollback(); throw ex; } finally { conexion.Close(); } }
public void CambiarEstadoOrdenHRR() { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); try { // actualizo el estado de la Solicitud Config.Conexion.EjecutarResultadoUnico(transaccion, "CambiarEstadoOrdenHRR", this.OrdenRetiroID); transaccion.Commit(); } catch (Exception ex) { transaccion.Rollback(); throw ex; } finally { conexion.Close(); } }
private void BtnUpdate_Click(object sender, EventArgs e) { using (SqlConnection Con = new SqlConnection(FXFW.SqlDB.SqlConStr)) { SqlCommand Cmd = new SqlCommand("", Con); SqlTransaction trn = null; try { Con.Open(); trn = Con.BeginTransaction(); Cmd.Transaction = trn; Cmd.CommandText = string.Format("Delete From UserRoles Where UserId = {0}", LUEItems.EditValue); Cmd.ExecuteNonQuery(); foreach (DataRow row in RolesTbl.Rows) { if (row["Active"].ToString() == "1") { Cmd.CommandText = String.Format("INSERT INTO UserRoles (UserId, RoleId) VALUES ({0}, {1})", LUEItems.EditValue, row["RoleID"].ToString()); Cmd.ExecuteNonQuery(); } } trn.Commit(); LoadRoles(LUEItems.EditValue.ToString()); Program.ShowMsg("تم التعديل ..", false, this); Program.Logger.LogThis("تم التعديل ..", Text, FXFW.Logger.OpType.success, null, null, this); } catch (Exception ex) { trn.Rollback(); MessageBox.Show(ex.Message); Program.Logger.LogThis(null, Text, FXFW.Logger.OpType.fail, ex, null, this); } } }
private void InsertWikiPages(DataTable table) { using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("WikiDumpDB"))) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction)) { try { bulkCopy.DestinationTableName = "WikiPage"; bulkCopy.WriteToServer(table); transaction.Commit(); } catch (Exception e) { Console.WriteLine("Commit Exception Type: {0}", e.GetType()); Console.WriteLine(" Message: {0}", e.Message); try { transaction.Rollback(); } catch (Exception ex) { Console.WriteLine("Rollback Exception Type", ex.ToString()); Console.WriteLine(" Message: {0}", ex.Message); } } } } } }
/// <summary> /// 运行SQL语句 /// </summary> /// <param name="sql">SQL语句</param> /// <returns></returns> public string RunSql(string sql) { if (!string.IsNullOrWhiteSpace(sql)) { SqlConnection conn = new SqlConnection(RDBSHelper.ConnectionString); conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { string[] sqlList = StringHelper.SplitString(sql, "-sqlseparator-"); foreach (string item in sqlList) { if (!string.IsNullOrWhiteSpace(item)) { try { RDBSHelper.ExecuteNonQuery(CommandType.Text, item); trans.Commit(); } catch (Exception ex) { trans.Rollback(); return ex.Message; } } } } conn.Close(); } return string.Empty; }
public void TestInitializer() { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.DataSource = @".\SQLEXPRESS"; builder.IntegratedSecurity = true; builder.InitialCatalog = "DBBlogs"; SqlConnection connection1 = new SqlConnection(builder.ConnectionString); connection1.Open(); _currentTransaction1 = connection1.BeginTransaction(); SqlConnection connection2 = new SqlConnection(builder.ConnectionString); connection2.Open(); _currentTransaction2 = connection2.BeginTransaction(); MetaDataStore metaDataStore = new MetaDataStore(); metaDataStore.BuildTableInfoFor<Blog>(); var identityMap1 = new IdentityMap(); var identityMap2 = new IdentityMap(); _blogMapper1 = new BlogMapper(connection1, _currentTransaction1, metaDataStore, new EntityHydrater(metaDataStore, identityMap1), identityMap1); _blogMapper2 = new BlogMapper(connection2, _currentTransaction2, metaDataStore, new EntityHydrater(metaDataStore, identityMap2), identityMap2); }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(ArrayList SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection=conn; SqlTransaction tx=conn.BeginTransaction(); cmd.Transaction=tx; try { for(int n=0;n<SQLStringList.Count;n++) { string strsql=SQLStringList[n].ToString(); if (strsql.Trim().Length>1) { cmd.CommandText=strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch(System.Data.SqlClient.SqlException E) { tx.Rollback(); throw new Exception(E.Message); } } }
private void OpenConnection() { connection = new SqlConnection(AppSettings.AdminConnectionString); connection.Open(); transaction = connection.BeginTransaction(); }
public void GuardarDatosConf(System.Collections.Hashtable hCotizacionCliente, bool habilita) { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); try { // Recorro los seleccionados y los marco como que sí requieren actualización foreach (int cotizacionClienteID in hCotizacionCliente.Keys) { // Seteo a todas las unidades de venta del cliente como que no requieren datos de conformación Config.Conexion.EjecutarSinResultados("CotizacionClienteDatosConfDEL", cotizacionClienteID); if (habilita) { Config.Conexion.EjecutarSinResultados("CotizacionClienteDatosConfUPD", cotizacionClienteID); } } } catch (Exception ex) { transaccion.Rollback(); throw new Exception(ex.Message, ex); } finally { conexion.Close(); } }
public void GuardarObservacionFT(int observacionFTID, string observacionFTCodigo, string observacionFTDescrip, int observacionID, int estadoGuiaID, int tipoEstadoGuiaID) { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); try { SqlParameter pObservacionFTID = new SqlParameter("@ObservacionFTID", Utiles.BaseDatos.IntToSql(observacionFTID)); SqlParameter pObservacionFTCodigo = new SqlParameter("@ObservacionFTCodigo", Utiles.BaseDatos.StrToSql(observacionFTCodigo)); SqlParameter pObservacionFTDescrip = new SqlParameter("@ObservacionFTDescrip", Utiles.BaseDatos.StrToSql(observacionFTDescrip)); SqlParameter pObservacionID = new SqlParameter("@ObservacionID", Utiles.BaseDatos.IntToSql(observacionID)); SqlParameter pEstadoGuiaID = new SqlParameter("@EstadoGuiaID", Utiles.BaseDatos.IntToSql(estadoGuiaID)); SqlParameter pTipoEstadoGuiaID = new SqlParameter("@TipoEstadoGuiaID", Utiles.BaseDatos.IntToSql(tipoEstadoGuiaID)); if (observacionFTID > 0) { Config.Conexion.EjecutarSinResultados(System.Data.CommandType.StoredProcedure, "ObservacionFTUPD", pObservacionFTID, pObservacionFTCodigo, pObservacionFTDescrip, pObservacionID, pEstadoGuiaID, pTipoEstadoGuiaID); } else { Config.Conexion.EjecutarSinResultados(System.Data.CommandType.StoredProcedure, "ObservacionFTINS", pObservacionFTCodigo, pObservacionFTDescrip, pObservacionID, pEstadoGuiaID, pTipoEstadoGuiaID); } transaccion.Commit(); } catch (Exception ex) { transaccion.Rollback(); throw ex; } }
public SqlServerProcessor(SqlConnection connection, IMigrationGenerator generator, IAnnouncer announcer, IMigrationProcessorOptions options) : base(generator, announcer, options) { Connection = connection; connection.Open(); Transaction = connection.BeginTransaction(); }
private void BtnDelete_Click(object sender, EventArgs e) { if (MessageBox.Show("هل ترغب في الاستمرار؟", "تأكــــيد", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == System.Windows.Forms.DialogResult.No) { return; } SqlTransaction Trn = null; using (SqlConnection Con = new SqlConnection(FXFW.SqlDB.SqlConStr)) { SqlCommand Cmd = new SqlCommand(String.Format("DELETE FROM CustomersSuppliers WHERE (PERSONID = {0})", LUEItems.EditValue), Con); try { Con.Open(); Trn = Con.BeginTransaction(); Cmd.Transaction = Trn; Cmd.ExecuteNonQuery(); if (CustomerTbl.Rows[LUEItems.ItemIndex]["AccountId"].ToString() != string.Empty) { Cmd.CommandText = "DELETE FROM TBL_Accountes Where AccountId = " + CustomerTbl.Rows[LUEItems.ItemIndex]["AccountId"].ToString(); Cmd.ExecuteNonQuery(); } Trn.Commit(); LoadData(); GetNewAccountID(); LUEItems.ItemIndex = -1; MessageBox.Show("تم الحـــذف ", "حـــــذف", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { Trn.Rollback(); MessageBox.Show(ex.Message); } } }
public AssignmentUnitOfWork(IAssignmentRepository assignmentRepository) { _AssignmentRepository = assignmentRepository; connection = new SqlConnection(DBHelper.ConnectionString); connection.Open(); _AssignmentRepository.DbContext = new SqlDbContext() { Transaction = connection.BeginTransaction() }; }
private IList<Project> getUserProjects(int userID, String condition) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); SqlCommand command = connection.CreateCommand(); command.Transaction = transaction; String subQuery = String.IsNullOrEmpty(condition) ? String.Empty : String.Format(" AND {0}", condition); StringBuilder query = new StringBuilder(); query.AppendFormat(" SELECT {0}.ID, {0}.Name, {0}.Description", DBProjectsTableName); query.AppendFormat(" FROM {0} INNER JOIN {1} ON {0}.ID = {1}.ProjectID", DBProjectsTableName, DBProjectsUserTableName); query.AppendFormat(" WHERE {0}.UserID = @userID {1}", DBProjectsUserTableName, subQuery); query.AppendFormat(" ORDER BY {0}.Name", DBProjectsTableName); command.CommandText = query.ToString(); command.Parameters.Add("@userID", SqlDbType.Int).Value = userID; command.CommandText = query.ToString(); using (IDataReader reader = ExecuteReader(command)) { return getAllProjectsDataFromReader(reader); } } }
public Boolean fnDelT_Material_Empaque(String psCodigo_Material_Empaque) { Boolean lbResultado=true; //conexion SqlTransaction loTx ; SqlConnection loCn = new SqlConnection(); loCn.ConnectionString = ClassCadena.fnConexion(); //parametros SqlParameter[] par = new SqlParameter[1]; //cambiar aca: par[0] = new SqlParameter("@Codigo_Material_Empaque",System.Data.SqlDbType.VarChar,10); par[0].Value= psCodigo_Material_Empaque; loCn.Open(); loTx = loCn.BeginTransaction(); try { SqlHelper.ExecuteNonQuery(loTx, CommandType.StoredProcedure, "usp_DelT_Material_Empaque", par); loTx.Commit(); } catch (Exception e) { lbResultado=false; loTx.Rollback(); throw e; } finally { loCn.Close(); loCn.Dispose(); } return lbResultado; }
protected void btnGuardar_Click(object sender, EventArgs e) { if (!currentUser.ValidarPrivilegio("Insertar Faltas")) return; bool exitoooooooo = true; SqlConnection con = new SqlConnection(connectionString); con.Open(); SqlTransaction trans = con.BeginTransaction(); try { guardarAlumno(con,trans); guargarFalta(con,trans); guargarEvidencias(con, trans); trans.Commit(); limpiar(); } catch (Exception ex) { exitoooooooo = false; trans.Rollback(); Session["error"] = ex.StackTrace; } con.Close(); if(!exitoooooooo) Response.Redirect("~/Seguridad/Error.aspx"); else Response.Redirect("ListaFaltas.aspx"); }
public void CalculateDurationEstimates() { SqlConnection connection = new SqlConnection(_connectionString); connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); if (_jams == null) { _jams = new JamGateway(connection, transaction).GetAllJams(); } if (_penaltyGroups == null) { _penaltyGroups = new PenaltyGroupGateway(connection, transaction).GetAllPenaltyGroups(); } var jamBoutMap = _jams.ToDictionary(j => j.ID, j => j.BoutID); var penaltyGroupMap = _penaltyGroups.GroupBy(pg => jamBoutMap[pg.BoxTimes.First().JamID]).ToDictionary(gp => gp.Key); var boutJams = _jams.GroupBy(j => j.BoutID); var jamEstimateMap = CalculateJamDurationLimits(connection, transaction); foreach (IGrouping<int, Jam> boutJamSet in boutJams) { ProcessBout(boutJamSet, penaltyGroupMap[boutJamSet.Key], jamEstimateMap); } var boxTimeEstimates = CalculateBoxTimeEstimates(jamEstimateMap); new JamTimeLimitGateway(connection, transaction).InsertJamTimeEstimates(jamEstimateMap.Values); new BoxTimeEstimateGateway(connection, transaction).InsertBoxTimeEstimates(boxTimeEstimates); transaction.Commit(); connection.Close(); }
public async Task<IReadOnlyCollection<Entry>> Query() { using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var transaction = connection.BeginTransaction()) { using (var command = new SqlCommand(@" SELECT [Owner], [Type], [Value] FROM [Data] WHERE [Owner] <> @Owner ", connection, transaction)) { command.Parameters.AddWithValue("Owner", owner).DbType = DbType.AnsiString; var results = new List<Entry>(); using (var reader = await command.ExecuteReaderAsync()) { while (reader.Read()) { results.Add(new Entry((string)reader[0], (string)reader[1], (string)reader[2])); } } return results; } } } }
private static void InsertValues(string tableName, string columnName, string[] values, SqlConnection connection) { const string insertStatementPattern = "INSERT INTO [{0}] ({1}) VALUES ('{2}'); "; string query = string.Empty; foreach (var version in values) { query += string.Format(insertStatementPattern, tableName, columnName, version); } if (string.IsNullOrEmpty(query)) return; using (var transaction = connection.BeginTransaction()) { using (var command = connection.CreateCommand()) { command.CommandText = query; command.Transaction = transaction; command.ExecuteNonQuery(); } transaction.Commit(); } }
public bool AddProduct(ProductsDto productsDto) { var cn = new SqlConnection(GetConnection()); SqlTransaction trx = null; var isInsert = false; try { cn.Open(); trx = cn.BeginTransaction(); string cmdText = " insert into Products(ProductName,ProductCategory,MemberAnaylst,OrganizationName,TenureId,StyleResearchId,StrategyId,FrequencyCall,FrequencyCallType,Logo,CreateUserId,CreateTimeStamp,ModifiedUserId,ModifiedTimeStamp) " + "values('" + productsDto.ProductName + "'," + productsDto.ProductCategory + "," + productsDto.MemberAnaylst + ",'" + productsDto.OrganizationName + "'," + productsDto.TenureId + "," + productsDto.StyleResearchId + "," + productsDto.StrategyId + "," + productsDto.FrequencyCall + ",'" + productsDto.FrequencyCallType + "','" + productsDto.Logo + "'," + productsDto.CreateUserId + ", '" + DateTime.Now.ToString("yyyy-MM-dd") + "'," + productsDto.ModifiedUserId + ",'" + DateTime.Now.ToString("yyyy-MM-dd") + "') select Scope_Identity();"; var cmd = new SqlCommand(cmdText, cn) { Transaction = trx }; var productId = Convert.ToInt32(cmd.ExecuteScalar()); foreach (var documents in productsDto.ProductDocumentDtos) (new SqlCommand("insert into ProductDocument(ProductId,FileName,DocumentName) values( " + productId + ",'" + documents.FileName + "','" + documents.DocumentName + "')", cn) { Transaction = trx }).ExecuteNonQuery(); trx.Commit(); isInsert = true; cn.Close(); } catch (Exception) { if (trx != null) trx.Rollback(); cn.Close(); } return isInsert; }
/// <summary> /// Importars the datos. /// </summary> private void ImportarDatos() { //La transacción va a este nivel para poder hacer el rollback en el cath (si se produce error) SqlTransaction transaccion = null; try { //Al utillizar el "using" me aseguro que los recursos se liberen cuando termina el bloque SqlConnection conexion = new SqlConnection() { ConnectionString = ConnectionString }; { //objeto que voy a utilizar para obtener los datos (BD Transaccional) objDAObtenerDatos = new DAObtenerDatos(objConfiguracion.valor); //abre la conexión a la bd conexion.Open(); //le indica al objeto transaccion que va a iniciar una transacción transaccion = conexion.BeginTransaction(); objDAImportarDatos.GrabarTipoAsistencia(objDAObtenerDatos.obtenerTipoAsistenciaBDTransaccional(objConfiguracion), transaccion); objDAImportarDatos.GrabarAsistencia(objDAObtenerDatos.obtenerAsistenciaBDTransaccional(objConfiguracion), transaccion); } //si la importación de los objetos fue exitosa, entonces confirmo las modificaciones. transaccion.Commit(); conexion.Close(); } catch (Exception ex) { //Valido que la transacción no sea nula, sino daría error al intentar el rollback if (transaccion != null) transaccion.Rollback(); //mando la excepción para arriba throw ex; } }
/// <summary> /// 删除记录(string类型id) /// </summary> /// <param name="id"></param> /// <returns></returns> public bool Delete(string id) { bool result = true; StringBuilder sql = new StringBuilder(); SqlConnection conn = new SqlConnection(SQLHelper.ConnString); conn.Open(); SqlTransaction trans = conn.BeginTransaction(); string[] idArr = id.Split(new char[] { ',' }); try { for (int i = 0; i < idArr.Length; i++) { sql.Append("delete from BigDog_Admin where Id=@Id"); SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@Id",SqlDbType.NVarChar,50) }; parms[0].Value = idArr[i]; SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sql.ToString(), parms); sql.Clear(); } trans.Commit(); } catch (Exception e) { trans.Rollback(); result = false; throw new ApplicationException(e.Message); } finally { conn.Close(); } return result; }
public SqlTransaction GetTrans() { if (myTrans == null) { OpenCon(); myTrans = mySqlCon.BeginTransaction(); } return(myTrans); }
public async Task BatchExcuteAsync(BulkCopyModel bulkCopyModel, params SqlCommand[] commands) //IContextInfo contextInfo, { if (commands?.Length < 1) { return; } using (var connection = new sql.SqlConnection(_connectionString)) { //connection.StateChange += new System.Data.StateChangeEventHandler((sender, e) => //{ // if (e.CurrentState == System.Data.ConnectionState.Open) // setContextInfo(connection, contextInfo); //}); connection.TryOpen(); using (var tran = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)) { try { foreach (var command in commands) { command.Connection = connection; command.Transaction = tran; await command.ExecuteNonQueryAsync(); } using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran)) { bulkCopy.BatchSize = bulkCopyModel.BatchSize; // ~ 100 bulkCopy.DestinationTableName = bulkCopyModel.TableName; if (bulkCopyModel.ColumnMappings != null) { bulkCopyModel.ColumnMappings.ForEach(c => bulkCopy.ColumnMappings.Add(c)); } var dataTable = bulkCopyModel.DataTable; bulkCopy.BulkCopyTimeout = bulkCopyModel.Timeout; await bulkCopy.WriteToServerAsync(dataTable); } tran.Commit(); } catch (Exception e) { tran.Rollback(); throw e; } finally { //clearContextInfo(connection, contextInfo); connection.TryClose(); } } } }
public returnval merge(returnval val) { if (val.rows == null && val.childs == null) { SD.DataTable DataTable; using (SDSc.SqlConnection SqlConnection = new SDSc.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["moodleConnectionString1"].ConnectionString)) { DataTable = new SD.DataTable(); (new SDSc.SqlDataAdapter(new SDSc.SqlCommand() { CommandText = string.Format( "SELECT {0}{1}{2};" , selectcols.Replace("@currentuser", "1") , dbname == null ? "" : string.Format(" FROM {0}", dbname) , whereparam == null ? "" : string.Format(" WHERE {0}", whereparam .Replace("@currentuser", "1") .Replace("@currentlogin", "1")) ), Connection = SqlConnection })).Fill(DataTable); }; val.rows = new List <object[]>(); foreach (SD.DataRow DataRow in DataTable.Rows) { val.rows.Add(DataRow.ItemArray); } } else { using (SDSc.SqlConnection SqlConnection = new SDSc.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["moodleConnectionString1"].ConnectionString)) { SqlConnection.Open(); SDSc.SqlTransaction SqlTransaction = SqlConnection.BeginTransaction(); try { merge(val, SqlTransaction); if (val.childs != null) { foreach (returnval subtable in val.childs) { thistables.table(subtable.name).merge(subtable, SqlTransaction); if (subtable.childs != null) { foreach (returnval subtable2 in subtable.childs) { thistables.table(subtable2.name).merge(subtable2, SqlTransaction); } } } } SqlTransaction.Commit(); } catch (Exception e) { SqlTransaction.Rollback(); throw e; } } } return(val); }
public bool Guardar() { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); try { if (this.PromocionServicioTransporteID == 0) { // guardo la promocion this.PromocionServicioTransporteID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion , "PromocionServicioTransporteINS", Utiles.BaseDatos.IntToSql(this.AgenciaOrigenID), Utiles.BaseDatos.IntToSql(this.AgenciaDestinoID), Utiles.BaseDatos.IntToSql(this.UnidadVentaID), this.Nombre, this.PorcentajeBonificacion, this.FechaInicioVigencia, this.FechaFinVigencia, this.ServicioTransporteID)); } else { this.PromocionServicioTransporteID = Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion , "PromocionServicioTransporteUPD", this.PromocionServicioTransporteID, Utiles.BaseDatos.IntToSql(this.AgenciaOrigenID), Utiles.BaseDatos.IntToSql(this.AgenciaDestinoID), Utiles.BaseDatos.IntToSql(this.UnidadVentaID), this.Nombre, this.PorcentajeBonificacion, this.FechaInicioVigencia, this.FechaFinVigencia, this.ServicioTransporteID)); } // elimino las agencias asociadas anteriormente Config.Conexion.EjecutarSinResultados(transaccion, "PromoAgenciaUVentaOrigenDestinoDEL", this.PromocionServicioTransporteID); // guardo las agencias a las que se aplica for (int i = 0; i <= this.Agencias.Count - 1; i++) { Convert.ToInt32(Config.Conexion.EjecutarResultadoUnico(transaccion, "PromoAgenciaUVentaOrigenDestinoINS", this.PromocionServicioTransporteID, this.Agencias[i])); } transaccion.Commit(); return(true); } catch (Exception ex) { transaccion.Rollback(); throw ex; } finally { conexion.Close(); } }
/// <summary>執行 Transaction</summary> /// <param name="listSQL">欲執行交易的 ArrayList (內含 SQL 指令)</param> /// <param name="sqlConn">連線物件</param> /// <returns>Transaction是否成功</returns> /// <remarks></remarks> public static bool raiseTransaction(ArrayList listSQL, System.Data.SqlClient.SqlConnection sqlConn) { if (listSQL == null) { return(true); } if (listSQL.Count == 0) { return(true); } if (sqlConn == null) { sqlConn = createConnection(); } System.Data.SqlClient.SqlTransaction sqlTrans = null; if (!(sqlConn.State == ConnectionState.Open)) { sqlConn.Open(); } System.Data.SqlClient.SqlCommand cmd = sqlConn.CreateCommand(); StringBuilder strSQL = new StringBuilder(""); sqlTrans = sqlConn.BeginTransaction(); try { cmd.Transaction = sqlTrans; for (int i = 0; i <= listSQL.Count - 1; i++) { if (!string.IsNullOrEmpty(listSQL[i].ToString())) { strSQL.AppendLine(listSQL[i].ToString()); } } cmd.CommandText = strSQL.ToString(); cmd.ExecuteNonQuery(); sqlTrans.Commit(); return(true); } catch (Exception Ex) { if ((sqlTrans != null)) { sqlTrans.Rollback(); } //Message.alertMessage("C0002", null, Ex.Message.ToString(), null); return(false); } finally { if (!(sqlConn.State == ConnectionState.Closed)) { sqlConn.Close(); } } }
public void addAssociatedPart(Part part) { int associatedPartID; //first get assoicatedPartID SqlConnection con2 = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True"); con2.Open(); SqlCommand cmd2 = new SqlCommand(); cmd2.Connection = con2; cmd2.CommandType = CommandType.Text; cmd2.CommandText = "SELECT MAX(associatedPartID) FROM associatedParts;"; try { associatedPartID = (int)cmd2.ExecuteScalar(); associatedPartID = associatedPartID + 1; } catch { associatedPartID = 0; } con2.Close(); SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True"); SqlCommand command = con.CreateCommand(); con.Open(); SqlTransaction transaction; // Start a local transaction. transaction = con.BeginTransaction(); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = con; command.Transaction = transaction; command.CommandText = "INSERT INTO [dbo].[associatedParts] ([productID], [partID], [name], [price], [inStock], [associatedPartID]) VALUES (@productID, @partID, @name, @price, @inStock , @associatedPartID);"; command.Parameters.AddWithValue("@productID", getProductID()); Console.WriteLine("Inserting Part " + part.getParttID() + " " + "for productID " + getProductID()); command.Parameters.AddWithValue("@partID", part.getParttID()); command.Parameters.AddWithValue("@name", part.getName()); command.Parameters.AddWithValue("@price", part.getPrice()); command.Parameters.AddWithValue("@inStock", part.getInStock()); command.Parameters.AddWithValue("@associatedPartID", associatedPartID); Console.WriteLine("associatePartID " + associatedPartID); command.ExecuteNonQuery(); transaction.Commit(); con.Close(); }
public bool BeginTransaction() { if (SqlConn != null && SqlConn.State == ConnectionState.Closed && Transaction == null) { Open(); Transaction = SqlConn.BeginTransaction(); return(true); } return(false); }
public bool AsignarRangoCodigoBarrasAgencia() { System.Data.SqlClient.SqlConnection conexion = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlTransaction transaccion; conexion.ConnectionString = Config.ConnectionString; conexion.Open(); transaccion = conexion.BeginTransaction(); CodigoBarras codigoBarras = new CodigoBarras(); try { //reviso que la agencia a la que se le quiere asignar el rango de codigos de barras, //sea emisora IAgencia agencia = new Agencia(); agencia.AgenciaID = this.AgenciaID; agencia.Consultar(); if (agencia.EsEmisora) { rangoCodigoBarrasID = this.Guardar(transaccion); codigoBarras = new CodigoBarras(); codigoBarras.UnidadVentaID = this.UnidadVentaID; codigoBarras.ServicioID = this.ServicioID; codigoBarras.ModalidadEntregaID = this.ModalidadEntregaID; codigoBarras.AgenciaID = this.AgenciaID; codigoBarras.VendedorID = this.VendedorID; codigoBarras.ClienteID = this.ClienteID; codigoBarras.EstadoCodigoBarrasID = this.EstadoCodigoBarrasID; codigoBarras.FechaAlta = DateTime.Now; codigoBarras.TarifaPreVentaID = this.TarifaPreVentaID; codigoBarras.TarifarioFleteID = TarifarioFleteID; codigoBarras.ConceptoFacturacionID = ConceptoFacturacionID; codigoBarras.ActualizarCodigoBarras(transaccion, this.CodigoBarrasInicial, this.CodigoBarrasFinal); transaccion.Commit(); return(true); } else { throw new Exception("Errores.Invalidos.AgenciaNoVendedora"); } } catch (Exception ex) { transaccion.Rollback(); throw new Exception(ex.Message, ex); } finally { conexion.Close(); } }