/// <summary> /// Test OdbcTransaction class /// </summary> public static void Test_Transaction() { using (OdbcConnection conn = new OdbcConnection()) { OdbcTransaction transaction = null; conn.ConnectionString = TestCases.connString; conn.Open(); TestCases.ExecuteSQL("drop table if exists t", conn); transaction = conn.BeginTransaction(); string sql = "create table t(idx integer)"; using (OdbcCommand command = new OdbcCommand(sql, conn)) { command.Transaction = transaction; command.ExecuteNonQuery(); } int tablesCount = GetTablesCount("t", conn, transaction); Assert.IsTrue(tablesCount == 1); transaction.Rollback(); //Verify the table does not exist tablesCount = GetTablesCount("t", conn, transaction); Assert.IsTrue(tablesCount == 0); transaction = conn.BeginTransaction(); sql = "create table t(idx integer)"; using (OdbcCommand command = new OdbcCommand(sql, conn)) { command.Transaction = transaction; command.ExecuteNonQuery(); } tablesCount = GetTablesCount("t", conn, transaction); Assert.IsTrue(tablesCount == 1); transaction.Commit(); tablesCount = GetTablesCount("t", conn, transaction); Assert.IsTrue(tablesCount == 1); transaction = conn.BeginTransaction(); TestCases.ExecuteSQL("drop table t", conn, transaction); transaction.Commit(); tablesCount = GetTablesCount("t", conn); Assert.IsTrue(tablesCount == 0); } }
public override IEnumerable<Row> Execute(IEnumerable<Row> rows) { string s = ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString; OdbcConnection myConnection = new OdbcConnection(s); myConnection.Open(); OdbcTransaction tran = myConnection.BeginTransaction(); string command = "SELECT * FROM WREV.PEDTFRM WHERE FRM_NUMCLIEN = '00930481'"; //Strange behaviour - low values are properly transformed only if this constructor is used //If one of parameter are added later, connection, transaction it won't work properly OdbcCommand cmd = new OdbcCommand(command, myConnection, tran); List<Row> result = new List<Row>(); OdbcDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Row row = new Row(); for (int i = 0; i < reader.FieldCount; i++) { row[reader.GetName(i)] = reader.GetValue(i); } result.Add(row); } reader.Close(); tran.Commit(); myConnection.Close(); return result; }
private void bntGuardar_Click(object sender, RoutedEventArgs e) { try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; System.Data.Odbc.OdbcConnection myConn = new System.Data.Odbc.OdbcConnection(); myConn.ConnectionString = ((Menu)this.Owner).sConexion; myConn.Open(); //Elimina string myNonQuery = "DELETE FROM HHc_Version Where CODIGO_AGENCIA = ? AND VERSION = ? "; OdbcCommand myCommand = new OdbcCommand(myNonQuery, myConn); myCommand.Parameters.Add("CODIGO_AGENCIA", OdbcType.Int).Value = Int32.Parse((string)cmbAgencia.SelectedValue); myCommand.Parameters.Add("VERSION", OdbcType.VarChar).Value = (string)cmbVersion.SelectedValue; myCommand.ExecuteNonQuery(); OdbcTransaction myTrans = myConn.BeginTransaction(IsolationLevel.ReadCommitted); try { //Insertar for (int i = 0; i < lstAgregados.Items.Count; i++) { myNonQuery = "INSERT INTO HHc_Version VALUES (?, ?, ?)"; myCommand = new OdbcCommand(myNonQuery, myConn); myCommand.Transaction = myTrans; myCommand.Parameters.Add("CODIGO_AGENCIA", OdbcType.Int).Value = Int32.Parse((string)cmbAgencia.SelectedValue); myCommand.Parameters.Add("ID_RUTA", OdbcType.VarChar).Value = (string)((ListBoxItem)lstAgregados.Items.GetItemAt(i)).Content; myCommand.Parameters.Add("VERSION", OdbcType.VarChar).Value = (string)cmbVersion.SelectedValue; myCommand.ExecuteNonQuery(); } myTrans.Commit(); } catch (Exception ex) { ITCMessageBox.Show((Window)this, ex.Message, "Error!"); myTrans.Rollback(); } ITCMessageBox.Show(this, "Modificacion de rutas exitosa", "Mensaje!"); myConn.Close(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } catch (Exception ex) { ITCMessageBox.Show(this, "Error al intentar ligar las rutas", "Error!"); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } }
private void Enviar() { System.Data.Odbc.OdbcConnection myConn = new System.Data.Odbc.OdbcConnection(); string sVer = (string)txtVersion.Text; string sAge = (string)txtAgencia.Text; string sDis = (string)cmbDispo.SelectedValue; int iEsImpar = 0; string sArchivo = string.Empty; // VALIDA QUE NO EXISTAN REGISTROS CON ESA AGENCIA, VERSION Y RUTA System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; try { myConn.ConnectionString = sConexion; myConn.Open(); OdbcTransaction myTrans = myConn.BeginTransaction(); string myNonQuery2 = "set textsize 2147483647"; OdbcCommand myCommand2 = new OdbcCommand(myNonQuery2, myConn); myCommand2.Transaction = myTrans; i = myCommand2.ExecuteNonQuery(); string myQuery = "SELECT ORDEN FROM HHc_ArchivosVersion WHERE CODIGO_AGENCIA = ? AND VERSION = ? AND TIPO_DISPOSITIVO = ?"; OdbcCommand myOdbcCommand = new OdbcCommand(myQuery); myOdbcCommand.Connection = myConn; myOdbcCommand.Parameters.Add("CODIGO_AGENCIA", OdbcType.Int).Value = Int32.Parse(sAge); myOdbcCommand.Parameters.Add("VERSION", OdbcType.VarChar).Value = sVer; myOdbcCommand.Parameters.Add("TIPO_DISPOSITIVO", OdbcType.VarChar).Value = sDis; myOdbcCommand.Transaction = myTrans; OdbcDataReader myReader = myOdbcCommand.ExecuteReader(CommandBehavior.CloseConnection); if (myReader.Read()) { string sMensaje = "ERROR!!! \nLa Agencia \"" + sAge + "\" \nLa Version \"" + sVer + "\"\nY el Dispositivo \"" + (string)cmbDispo.SelectedValue + "\" \nLA VVERSION YA EXISTE EN LA BD"; ITCMessageBox.Show((Window)this, sMensaje, "Error!"); return; } //INSERTA LA VERSION int orden = 1; foreach (Archivo aFile in lArchivos) { byte[] bFile = null; System.IO.FileStream fLoad = null; string sFname = System.IO.Path.GetFileName(aFile.sName); if ((sFname != "") && (System.IO.Path.GetExtension(sFname) != "")) { fLoad = System.IO.File.Open(aFile.sName, FileMode.Open, FileAccess.Read); bFile = new byte[fLoad.Length]; fLoad.Read(bFile, 0, (int)fLoad.Length); fLoad.Close(); sArchivo = BitConverter.ToString(bFile); sArchivo = sArchivo.Replace("-", ""); sArchivo = "0x" + sArchivo; if ((bFile.Length % 2) == 1) { sArchivo += "00"; iEsImpar = 1; } } else { sArchivo = "NULL"; } string myNonQuery = "INSERT INTO HHc_ArchivosVersion VALUES (?, ?, ?, ?, ?, ?, ?, &, ?)"; myNonQuery = myNonQuery.Replace("&", sArchivo); OdbcCommand myCommand = new OdbcCommand(myNonQuery, myConn); myCommand.Parameters.Add("CODIGO_AGENCIA", OdbcType.Int).Value = Int32.Parse(sAge); myCommand.Parameters.Add("VERSION", OdbcType.VarChar).Value = sVer; myCommand.Parameters.Add("TIPO_DISPOSITIVO", OdbcType.VarChar).Value = sDis; myCommand.Parameters.Add("ORDEN", OdbcType.Int).Value = orden; myCommand.Parameters.Add("NOMBRE", OdbcType.VarChar).Value = aFile.sShort; myCommand.Parameters.Add("PATH_DESTINO", OdbcType.VarChar).Value = aFile.sDestino; myCommand.Parameters.Add("ACCION", OdbcType.Int).Value = aFile.option; //myCommand.Parameters.Add("CONTENIDO", OdbcType.Binary).Value = bFile; myCommand.Parameters.Add("ESIMPAR", OdbcType.Int).Value = iEsImpar; myCommand.Transaction = myTrans; i = myCommand.ExecuteNonQuery(); orden++; } myTrans.Commit(); myConn.Close(); i = 1; System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } catch (Exception ex) { string m = ex.Message; i = 0; System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } }
static void RunOdbcTest () { if (Environment.GetEnvironmentVariable ("MONO_TESTS_ODBC") == null) return; OdbcConnection conn = new OdbcConnection (CreateOdbcConnectionString ()); conn.Open (); OdbcCommand cmd = new OdbcCommand (drop_table, conn); cmd.ExecuteNonQuery (); try { using (OdbcTransaction trans = conn.BeginTransaction ()) { cmd = new OdbcCommand (create_table, conn, trans); cmd.ExecuteNonQuery (); trans.Commit (); Assert.IsNull (trans.Connection, "Connection"); try { trans.Commit (); Assert.Fail ("#A1"); } catch (InvalidOperationException ex) { Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2"); Assert.IsNull (ex.InnerException, "#A3"); Assert.IsNotNull (ex.Message, "#A4"); Assert.AreEqual ("This OdbcTransaction has completed; it is no longer usable.", ex.Message, "#A5"); } try { trans.Rollback (); Assert.Fail ("#B1"); } catch (InvalidOperationException ex) { Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2"); Assert.IsNull (ex.InnerException, "#B3"); Assert.IsNotNull (ex.Message, "#B4"); Assert.AreEqual ("This OdbcTransaction has completed; it is no longer usable.", ex.Message, "#B5"); } try { Assert.Fail ("#C1: " + trans.IsolationLevel); } catch (InvalidOperationException ex) { Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#C2"); Assert.IsNull (ex.InnerException, "#C3"); Assert.IsNotNull (ex.Message, "#C4"); Assert.AreEqual ("This OdbcTransaction has completed; it is no longer usable.", ex.Message, "#C5"); } } cmd = new OdbcCommand (insert_data, conn, null); cmd.ExecuteNonQuery (); } finally { cmd = new OdbcCommand (drop_table, conn); cmd.ExecuteNonQuery (); } try { using (OdbcTransaction trans = conn.BeginTransaction ()) { cmd = new OdbcCommand (create_table, conn, trans); cmd.ExecuteNonQuery (); trans.Rollback (); Assert.IsNull (trans.Connection, "Connection"); try { trans.Rollback (); Assert.Fail ("#D1"); } catch (InvalidOperationException ex) { Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#D2"); Assert.IsNull (ex.InnerException, "#D3"); Assert.IsNotNull (ex.Message, "#D4"); Assert.AreEqual ("This OdbcTransaction has completed; it is no longer usable.", ex.Message, "#D5"); } try { trans.Commit (); Assert.Fail ("#E1"); } catch (InvalidOperationException ex) { Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#E2"); Assert.IsNull (ex.InnerException, "#E3"); Assert.IsNotNull (ex.Message, "#E4"); Assert.AreEqual ("This OdbcTransaction has completed; it is no longer usable.", ex.Message, "#E5"); } try { Assert.Fail ("#F1: " + trans.IsolationLevel); } catch (InvalidOperationException ex) { Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#F2"); Assert.IsNull (ex.InnerException, "#F3"); Assert.IsNotNull (ex.Message, "#F4"); Assert.AreEqual ("This OdbcTransaction has completed; it is no longer usable.", ex.Message, "#F5"); } } cmd = new OdbcCommand (create_table, conn, null); cmd.ExecuteNonQuery (); } finally { cmd = new OdbcCommand (drop_table, conn); cmd.ExecuteNonQuery (); } try { OdbcTransaction trans = conn.BeginTransaction (); cmd = new OdbcCommand (create_table, conn, trans); cmd.ExecuteNonQuery (); ((IDisposable) trans).Dispose (); ((IDisposable) trans).Dispose (); Assert.IsNull (trans.Connection, "Connection"); cmd = new OdbcCommand (create_table, conn, null); cmd.ExecuteNonQuery (); try { trans.Rollback (); Assert.Fail ("#G1"); } catch (InvalidOperationException ex) { Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#G2"); Assert.IsNull (ex.InnerException, "#G3"); Assert.IsNotNull (ex.Message, "#G4"); Assert.AreEqual ("This OdbcTransaction has completed; it is no longer usable.", ex.Message, "#G5"); } try { trans.Commit (); Assert.Fail ("#H1"); } catch (InvalidOperationException ex) { Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#H2"); Assert.IsNull (ex.InnerException, "#H3"); Assert.IsNotNull (ex.Message, "#H4"); Assert.AreEqual ("This OdbcTransaction has completed; it is no longer usable.", ex.Message, "#H5"); } try { Assert.Fail ("#I1: " + trans.IsolationLevel); } catch (InvalidOperationException ex) { Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#I2"); Assert.IsNull (ex.InnerException, "#I3"); Assert.IsNotNull (ex.Message, "#I4"); Assert.AreEqual ("This OdbcTransaction has completed; it is no longer usable.", ex.Message, "#I5"); } } finally { cmd = new OdbcCommand (drop_table, conn); cmd.ExecuteNonQuery (); } conn.Dispose (); }
// // System.Web.Security.RoleProvider methods. // // // RoleProvider.AddUsersToRoles // public override void AddUsersToRoles(string[] usernames, string[] rolenames) { foreach (string rolename in rolenames) { if (!RoleExists(rolename)) { throw new ProviderException("Role name not found."); } } foreach (string username in usernames) { if (username.Contains(",")) { throw new ArgumentException("User names cannot contain commas."); } foreach (string rolename in rolenames) { if (IsUserInRole(username, rolename)) { throw new ProviderException("User is already in role."); } } } OdbcConnection conn = new OdbcConnection(connectionString); OdbcCommand cmd = new OdbcCommand("INSERT INTO UsersInRoles " + " (Username, Rolename, ApplicationName) " + " Values(?, ?, ?)", conn); OdbcParameter userParm = cmd.Parameters.Add("@Username", OdbcType.VarChar, 255); OdbcParameter roleParm = cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255); cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName; OdbcTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Transaction = tran; foreach (string username in usernames) { foreach (string rolename in rolenames) { userParm.Value = username; roleParm.Value = rolename; cmd.ExecuteNonQuery(); } } tran.Commit(); } catch (OdbcException e) { try { tran.Rollback(); } catch { } if (WriteExceptionsToEventLog) { WriteToEventLog(e, "AddUsersToRoles"); } else { throw e; } } finally { conn.Close(); } }
// // RoleProvider.DeleteRole // public override bool DeleteRole(string rolename, bool throwOnPopulatedRole) { if (!RoleExists(rolename)) { throw new ProviderException("Role does not exist."); } if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0) { throw new ProviderException("Cannot delete a populated role."); } OdbcConnection conn = new OdbcConnection(connectionString); OdbcCommand cmd = new OdbcCommand("DELETE FROM Roles " + " WHERE Rolename = ? AND ApplicationName = ?", conn); cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename; cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName; OdbcCommand cmd2 = new OdbcCommand("DELETE FROM UsersInRoles " + " WHERE Rolename = ? AND ApplicationName = ?", conn); cmd2.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename; cmd2.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName; OdbcTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Transaction = tran; cmd2.Transaction = tran; cmd2.ExecuteNonQuery(); cmd.ExecuteNonQuery(); tran.Commit(); } catch (OdbcException e) { try { tran.Rollback(); } catch { } if (WriteExceptionsToEventLog) { WriteToEventLog(e, "DeleteRole"); return false; } else { throw e; } } finally { conn.Close(); } return true; }
private void btnAgregar_Click(object sender, RoutedEventArgs e) { int iTotal = lstArchivos.Items.Count; int iSelec = lstArchivos.SelectedIndex + 1; int iEsImpar = 0; Archivos view = new Archivos(); view.Owner = this; view.bMain = true; view.WindowStartupLocation = System.Windows.WindowStartupLocation.CenterOwner; view.ShowDialog(); if (!bArchivos) { ITCMessageBox.Show(this,"No agregaste Archivos","Error!"); return; } int iTadd = lArchivos.Count; OdbcTransaction myTrans = null; this.IsEnabled = false; System.Threading.Thread t = new System.Threading.Thread(Sending); t.SetApartmentState(System.Threading.ApartmentState.STA); t.Start(getBase()); try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; System.Data.Odbc.OdbcConnection myConn = new System.Data.Odbc.OdbcConnection(); string sVer = (string)cmbVersion.SelectedValue; string sAge = (string)cmbAgencia.SelectedValue; string sDis = (string)cmbDispo.SelectedValue; string sArchivo = string.Empty; myConn.ConnectionString = ((Menu)this.Owner).sConexion; myConn.Open(); string myNonQuery; OdbcCommand myCommand; myTrans = myConn.BeginTransaction(); for (int i = iTotal; i >= iSelec; i--) { myNonQuery = "UPDATE HHc_ArchivosVersion SET ORDEN = ? WHERE CODIGO_AGENCIA = ? AND VERSION = ? AND TIPO_DISPOSITIVO = ? AND ORDEN = ?"; myCommand = new OdbcCommand(myNonQuery, myConn); myCommand.Parameters.Add("ORDEN", OdbcType.Int).Value = i + iTadd; myCommand.Parameters.Add("CODIGO_AGENCIA", OdbcType.Int).Value = Int32.Parse(sAge); myCommand.Parameters.Add("VERSION", OdbcType.VarChar).Value = sVer; myCommand.Parameters.Add("TIPO_DISPOSITIVO", OdbcType.VarChar).Value = sDis; myCommand.Parameters.Add("ORDEN", OdbcType.Int).Value = i; myCommand.Transaction = myTrans; myCommand.ExecuteNonQuery(); } int orden = iSelec; foreach (Archivo aFile in lArchivos) { byte[] bFile = null; System.IO.FileStream fLoad = null; string sFname = System.IO.Path.GetFileName(aFile.sName); if ((sFname != "") && (System.IO.Path.GetExtension(sFname) != "")) { fLoad = System.IO.File.Open(aFile.sName, FileMode.Open, FileAccess.Read); bFile = new byte[fLoad.Length]; fLoad.Read(bFile, 0, (int)fLoad.Length); fLoad.Close(); sArchivo = BitConverter.ToString(bFile); sArchivo = sArchivo.Replace("-", ""); sArchivo = "0x" + sArchivo; if ((bFile.Length % 2) == 1) { sArchivo += "00"; iEsImpar = 1; } } else { sArchivo = "NULL"; } myNonQuery = "INSERT INTO HHc_ArchivosVersion VALUES (?, ?, ?, ?, ?, ?, ?, &, ?)"; myNonQuery = myNonQuery.Replace("&", sArchivo); myCommand = new OdbcCommand(myNonQuery, myConn); myCommand.Parameters.Add("CODIGO_AGENCIA", OdbcType.Int).Value = Int32.Parse(sAge); myCommand.Parameters.Add("VERSION", OdbcType.VarChar).Value = sVer; myCommand.Parameters.Add("TIPO_DISPOSITIVO", OdbcType.VarChar).Value = (string)cmbDispo.SelectedValue; myCommand.Parameters.Add("ORDEN", OdbcType.Int).Value = orden; myCommand.Parameters.Add("NOMBRE", OdbcType.VarChar).Value = aFile.sShort; myCommand.Parameters.Add("PATH_DESTINO", OdbcType.VarChar).Value = aFile.sDestino; myCommand.Parameters.Add("ACCION", OdbcType.Int).Value = aFile.option; //myCommand.Parameters.Add("CONTENIDO", OdbcType.Binary).Value = bFile; myCommand.Parameters.Add("ESIMPAR", OdbcType.Int).Value = iEsImpar; myCommand.Transaction = myTrans; myCommand.ExecuteNonQuery(); lstArchivos.Items.Insert(orden - 1, aFile); orden++; } myTrans.Commit(); myConn.Close(); t.Abort(); ITCMessageBox.Show(this, "Archivos modificados", "Mensaje!"); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } catch (Exception ex) { myTrans.Rollback(); t.Abort(); ITCMessageBox.Show(this, "Error al intentar modificar los archivos", "Error!"); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } this.IsEnabled = true; }
/// <summary> /// 事务 /// </summary> /// <param name="transt">事务SQL语句和参数对应建构体</param> /// <param name="conn">SQL连接</param> /// <returns></returns> public bool Transaction(List<OdbcTransCollection> transt, OdbcConnection conn) { OdbcTransaction tran = conn.BeginTransaction(); int count = 0; try { foreach (OdbcTransCollection tc in transt) { using (OdbcCommand comm = conn.CreateCommand()) { comm.CommandText = tc.StrSQL; comm.CommandTimeout = this.CommandTimeout; comm.Transaction = tran; foreach (OdbcParameter parameter in tc.Parameters) { comm.Parameters.Add(parameter); } count += comm.ExecuteNonQuery(); } } tran.Commit(); return count > 0 ? true : false; } catch (Exception err) { tran.Rollback(); throw err; } }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的OdbcParameter[])</param> public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { using (OdbcConnection conn = new OdbcConnection(connectionString)) { conn.Open(); using (OdbcTransaction trans = conn.BeginTransaction()) { OdbcCommand cmd = new OdbcCommand(); try { int indentity = 0; //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); OdbcParameter[] cmdParms = (OdbcParameter[])myDE.Value; foreach (OdbcParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (OdbcParameter q in cmdParms) { if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public void ExecuteSqlTran(ArrayList SQLStringList) { //using (SqlConnection conn = new SqlConnection(connectionString)) using (OdbcConnection conn = new OdbcConnection(connectionString)) { conn.Open(); //SqlCommand cmd = new SqlCommand(); OdbcCommand cmd = new OdbcCommand(); cmd.Connection=conn; //SqlTransaction tx=conn.BeginTransaction(); OdbcTransaction 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); } } }
/// <summary> /// Inserta el socio en BD DBISAM. /// </summary> /// <param name="id"></param> /// <param name="nombre"></param> public static void InsertSociosDBISAM(string id, string nombre) { OdbcTransaction transaction = null; string errorMsg = ""; string connectionString = ""; bool activo = true; int zero = 0; string parameterChar = ""; string queryStringCliente = ""; string queryStringProveedor = ""; string queryCliente = ""; string queryProveedor = ""; try { connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["A2DBISAM"].ConnectionString; parameterChar = System.Configuration.ConfigurationManager.AppSettings["A2DBISAM_ParameterChar"]; queryStringCliente = System.Configuration.ConfigurationManager.AppSettings["A2DBISAM_InsertarCliente"]; queryStringProveedor = System.Configuration.ConfigurationManager.AppSettings["A2DBISAM_InsertarProveedor"]; queryCliente = string.Format(queryStringCliente, parameterChar); queryProveedor = string.Format(queryStringProveedor, parameterChar); } catch (Exception ex) { errorMsg = string.Format("Error al preparar conexion con DBISAM. ConnectionString: {0} - SQLInsertCliente: {1} - SQLInsertProveedor: {2}", connectionString, queryCliente, queryProveedor); log.Error(errorMsg, ex); return; } try { using (OdbcConnection connection = new OdbcConnection(connectionString)) { OdbcCommand myCommandCliente = new OdbcCommand(queryCliente, connection); myCommandCliente.Parameters.Clear(); myCommandCliente.Parameters.Add(new OdbcParameter { Value = id, DbType = DbType.String, Direction = ParameterDirection.Input }); myCommandCliente.Parameters.Add(new OdbcParameter { Value = nombre, DbType = DbType.String, Direction = ParameterDirection.Input }); myCommandCliente.Parameters.Add(new OdbcParameter { Value = activo, DbType = DbType.Boolean, Direction = ParameterDirection.Input }); myCommandCliente.Parameters.Add(new OdbcParameter { Value = zero, DbType = DbType.String, Direction = ParameterDirection.Input }); myCommandCliente.Parameters.Add(new OdbcParameter { Value = id, DbType = DbType.String, Direction = ParameterDirection.Input }); myCommandCliente.Parameters.Add(new OdbcParameter { Value = id, DbType = DbType.String, Direction = ParameterDirection.Input }); OdbcCommand myCommandProveedor = new OdbcCommand(queryProveedor, connection); myCommandProveedor.Parameters.Clear(); myCommandProveedor.Parameters.Add(new OdbcParameter { Value = id, DbType = DbType.String, Direction = ParameterDirection.Input }); myCommandProveedor.Parameters.Add(new OdbcParameter { Value = nombre, DbType = DbType.String, Direction = ParameterDirection.Input }); myCommandProveedor.Parameters.Add(new OdbcParameter { Value = activo, DbType = DbType.Boolean, Direction = ParameterDirection.Input }); myCommandProveedor.Parameters.Add(new OdbcParameter { Value = zero, DbType = DbType.String, Direction = ParameterDirection.Input }); myCommandProveedor.Parameters.Add(new OdbcParameter { Value = id, DbType = DbType.String, Direction = ParameterDirection.Input }); myCommandProveedor.Parameters.Add(new OdbcParameter { Value = id, DbType = DbType.String, Direction = ParameterDirection.Input }); connection.Open(); transaction = connection.BeginTransaction(); myCommandCliente.Transaction = transaction; myCommandProveedor.Transaction = transaction; myCommandCliente.ExecuteNonQuery(); myCommandProveedor.ExecuteNonQuery(); transaction.Commit(); } } catch (Exception ex) { if (transaction != null) transaction.Rollback(); errorMsg = string.Format("Error al obtener socios desde DBISAM. ConnectionString: {0} - SQLInsertCliente: {1} - SQLInsertProveedor: {2}", connectionString, queryCliente, queryProveedor); log.Error(errorMsg, ex); } }
public static void SaveQuote(BusinessObjects.Quote q) { string sql=string.Empty; if (q.ID <= 0) { sql = @"Insert into QUOTES_DAT ([SHIPPER_NAME],[SHIPPER],[SHIPPER_CONTACT],[SHIPPER_PHONE],[SHIPPER_FAX] ,[P_O_NUMBER],[PLR],[SUPPLIER_STATE],[SUPPLIER_ZIP_CODE],[POL],[POD] ,[FinalDestino],[CONSIGNEE_ZIP_CODE],[CONSIGNEE_COUNTRY],[MOVE_TYPE],[PAYMENT],[BUS_LINE],[Incoterms] ,[INCOTermsLocation],[EFFECTIVE_DATE],[EXPIRATION_DATE],[QuotedBy],[CONTROLLER],[CARRIER] ,[Voyage],[Vessel],[ContainerHandeling]) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; } else { sql = string.Format(@"Update QUOTES_DAT SET [SHIPPER_NAME]=?,[SHIPPER]=?,[SHIPPER_CONTACT]=?,[SHIPPER_PHONE]=?,[SHIPPER_FAX]=? ,[P_O_NUMBER]=?,[PLR]=?,[SUPPLIER_STATE]=?,[SUPPLIER_ZIP_CODE]=?,[POL]=?,[POD]=? ,[FinalDestino]=?,[CONSIGNEE_ZIP_CODE]=?,[CONSIGNEE_COUNTRY]=?,[MOVE_TYPE]=?,[PAYMENT]=?,[BUS_LINE]=?,[INCOTerms]=? ,[INCOTermsLocation]=?,[EFFECTIVE_DATE]=?,[EXPIRATION_DATE]=?,[QuotedBy]=?,[CONTROLLER]=?,[CARRIER]=? ,[Voyage]=?,[Vessel]=?,[ContainerHandeling]=? Where ID={0}", q.ID); } using (OdbcConnection conn = new OdbcConnection(Properties.Settings.Default.IESDBConn)) { OdbcCommand cmd = new OdbcCommand(sql,conn); cmd.Parameters.Add(new OdbcParameter("@Customer", q.Customer)); cmd.Parameters.Add(new OdbcParameter("@CustomerCode", q.CustomerCode)); cmd.Parameters.Add(new OdbcParameter("@CustomerContact", q.CustomerContact)); cmd.Parameters.Add(new OdbcParameter("@CustomerPhone", q.CustomerPhone)); cmd.Parameters.Add(new OdbcParameter("@CustomerFax", q.CustomerFax)); cmd.Parameters.Add(new OdbcParameter("@CustomerReferenceNo", q.CustomerReferenceNo)); cmd.Parameters.Add(new OdbcParameter("@PickupCity", q.PickupCity)); cmd.Parameters.Add(new OdbcParameter("@CustomerState", q.CustomerState)); cmd.Parameters.Add(new OdbcParameter("@CustomerZip", q.CustomerZip)); cmd.Parameters.Add(new OdbcParameter("@PortOfLoad", q.PortOfLoad)); cmd.Parameters.Add(new OdbcParameter("@PortOfDispatch", q.PortOfDispatch)); cmd.Parameters.Add(new OdbcParameter("@FinalDestination", q.FinalDestination)); cmd.Parameters.Add(new OdbcParameter("@DestinationZip", q.DestinationZip)); cmd.Parameters.Add(new OdbcParameter("@DestinationCounty", q.DestinationCountry)); cmd.Parameters.Add(new OdbcParameter("@TypeOfMove", q.TypeOfMove)); cmd.Parameters.Add(new OdbcParameter("@Division", q.Division)); cmd.Parameters.Add(new OdbcParameter("@Businessline", q.Businessline)); cmd.Parameters.Add(new OdbcParameter("@INCOTerms", q.INCOTerms)); cmd.Parameters.Add(new OdbcParameter("@INCOTermsLocation", q.INCOTermsLocation)); cmd.Parameters.Add(new OdbcParameter("@Effectivdate", q.Effectivdate)); cmd.Parameters.Add(new OdbcParameter("@ExpirationDate", q.ExpirationDate)); cmd.Parameters.Add(new OdbcParameter("@QuotedBy", q.QuotedBy)); cmd.Parameters.Add(new OdbcParameter("@SalesRep", q.SalesRep)); cmd.Parameters.Add(new OdbcParameter("@PreferredCarrier", q.PreferredCarrier)); cmd.Parameters.Add(new OdbcParameter("@Voyage", q.Voyage)); cmd.Parameters.Add(new OdbcParameter("@Vessel", q.Vessel)); cmd.Parameters.Add(new OdbcParameter("@ContainerHandling", q.ContainerHandling)); conn.Open(); OdbcTransaction trans = conn.BeginTransaction(); cmd.Transaction = trans; try { cmd.ExecuteNonQuery(); foreach (BusinessObjects.Container c in q.Containers) { SaveContainer(cmd, c, q.QuoteNumber); } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } } }
public static void SaveClient(BusinessObjects.Client c) { string sql = string.Empty; using (OdbcConnection conn = new OdbcConnection(Properties.Settings.Default.IESDBConn)) { sql = string.Format(@"select 1 from Client_DAT where ID='{0}'", c.ClientID); OdbcCommand cmd = new OdbcCommand(sql, conn); conn.Open(); if (cmd.ExecuteScalar()!=null) { sql = string.Format(@"Update QUOTES_DAT SET [ID]=?,[Type]=?,[Name]=?,[Contact]=?,[Phone]=?,[Fax]=?,[ReferenceNo]=? Where ID={0}", c.ClientID); } else { sql = @"Insert into Client_DAT ([ID],[Type],[Name],[Contact],[Phone],[Fax],[ReferenceNo]) values (?,?,?,?,?,?,?)"; } cmd.CommandText = sql; cmd.Parameters.Add(new OdbcParameter("@ID", c.ClientID)); cmd.Parameters.Add(new OdbcParameter("@Type", c.Type)); cmd.Parameters.Add(new OdbcParameter("@Name", c.Name)); cmd.Parameters.Add(new OdbcParameter("@Contact", c.Contact)); cmd.Parameters.Add(new OdbcParameter("@Phone", c.Phone)); cmd.Parameters.Add(new OdbcParameter("@Fax", c.Fax)); cmd.Parameters.Add(new OdbcParameter("@ReferenceNo", c.ReferenceNo)); OdbcTransaction trans = conn.BeginTransaction(); cmd.Transaction = trans; try { cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } } }
static int Main () { if (Environment.GetEnvironmentVariable ("MONO_TESTS_ODBC") == null) return 0; OdbcConnection conn = new OdbcConnection (CreateConnectionString ()); conn.Open (); #if !MONO IsolationLevel level = GetIsolationLevel (conn, null); Assert.AreEqual (IsolationLevel.ReadCommitted, level, "#A"); #endif ChangeIsolationLevel (conn, null, "SERIALIZABLE"); #if !MONO level = GetIsolationLevel (conn, null); Assert.AreEqual (IsolationLevel.Serializable, level, "#B"); #endif OdbcTransaction trans = conn.BeginTransaction (); #if !MONO level = GetIsolationLevel (conn, trans); #if NET_2_0 Assert.AreEqual (IsolationLevel.Serializable, level, "#C1"); #else Assert.AreEqual (IsolationLevel.ReadCommitted, level, "#C1"); #endif #endif Assert.AreEqual (IsolationLevel.ReadCommitted, trans.IsolationLevel, "#C2"); ChangeIsolationLevel (conn, trans, "REPEATABLE READ"); #if !MONO level = GetIsolationLevel (conn, trans); Assert.AreEqual (IsolationLevel.RepeatableRead, level, "#C3"); #endif Assert.AreEqual (IsolationLevel.ReadCommitted, trans.IsolationLevel, "#C4"); trans.Rollback (); #if !MONO level = GetIsolationLevel (conn, null); Assert.AreEqual (IsolationLevel.RepeatableRead, level, "#D"); #endif trans = conn.BeginTransaction (IsolationLevel.Unspecified); #if !MONO level = GetIsolationLevel (conn, trans); Assert.AreEqual (IsolationLevel.RepeatableRead, level, "#E1"); #endif Assert.AreEqual (IsolationLevel.ReadCommitted, trans.IsolationLevel, "#E2"); ChangeIsolationLevel (conn, trans, "SERIALIZABLE"); #if !MONO level = GetIsolationLevel (conn, trans); Assert.AreEqual (IsolationLevel.Serializable, level, "#E3"); #endif Assert.AreEqual (IsolationLevel.ReadCommitted, trans.IsolationLevel, "#E4"); trans.Rollback (); ChangeIsolationLevel (conn, null, "REPEATABLE READ"); trans = conn.BeginTransaction (IsolationLevel.Unspecified); #if !MONO level = GetIsolationLevel (conn, trans); Assert.AreEqual (IsolationLevel.RepeatableRead, level, "#F1"); #endif ChangeIsolationLevel (conn, trans, "READ UNCOMMITTED"); #if !MONO level = GetIsolationLevel (conn, trans); Assert.AreEqual (IsolationLevel.ReadUncommitted, level, "#F3"); #endif Assert.AreEqual (IsolationLevel.ReadCommitted, trans.IsolationLevel, "#F4"); trans.Rollback (); #if !MONO level = GetIsolationLevel (conn, null); Assert.AreEqual (IsolationLevel.ReadUncommitted, level, "#G"); #endif trans = conn.BeginTransaction (IsolationLevel.RepeatableRead); #if !MONO level = GetIsolationLevel (conn, trans); Assert.AreEqual (IsolationLevel.RepeatableRead, level, "#H1"); #endif Assert.AreEqual (IsolationLevel.RepeatableRead, trans.IsolationLevel, "#H2"); ChangeIsolationLevel (conn, trans, "SERIALIZABLE"); #if !MONO level = GetIsolationLevel (conn, trans); Assert.AreEqual (IsolationLevel.Serializable, level, "#H3"); #endif Assert.AreEqual (IsolationLevel.RepeatableRead, trans.IsolationLevel, "#H4"); trans.Rollback (); #if !MONO level = GetIsolationLevel (conn, null); Assert.AreEqual (IsolationLevel.Serializable, level, "#I"); #endif // Snapshot is badly broken on MS.NET 2.0: //https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=305736 #if NET_2_0 && MONO trans = conn.BeginTransaction (IsolationLevel.Snapshot); #if !MONO level = GetIsolationLevel (conn, trans); Assert.AreEqual (IsolationLevel.Snapshot, level, "#J1"); #endif Assert.AreEqual (IsolationLevel.Snapshot, trans.IsolationLevel, "#J2"); ChangeIsolationLevel (conn, trans, "SERIALIZABLE"); #if !MONO level = GetIsolationLevel (conn, trans); Assert.AreEqual (IsolationLevel.Serializable, level, "#J3"); #endif Assert.AreEqual (IsolationLevel.Snapshot, trans.IsolationLevel, "#J4"); trans.Rollback (); #endif try { conn.BeginTransaction (IsolationLevel.Chaos); Assert.Fail ("#K1"); #if NET_2_0 } catch (ArgumentOutOfRangeException ex) { Assert.AreEqual (typeof (ArgumentOutOfRangeException), ex.GetType (), "#K2"); Assert.IsNull (ex.InnerException, "#K3"); Assert.IsNotNull (ex.Message, "#K4"); Assert.AreEqual (string.Format (CultureInfo.InvariantCulture, "The IsolationLevel enumeration value, 16, is " + "not supported by the .Net Framework Odbc Data " + "Provider.{0}Parameter name: IsolationLevel", Environment.NewLine), ex.Message, "#K5"); Assert.IsNotNull (ex.ParamName, "#K6"); Assert.AreEqual ("IsolationLevel", ex.ParamName, "#K7"); } #else } catch (ArgumentException ex) {
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的OdbcParameter[])</param> public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList) { using (OdbcConnection conn = new OdbcConnection(connectionString)) { conn.Open(); using (OdbcTransaction trans = conn.BeginTransaction()) { OdbcCommand cmd = new OdbcCommand(); try { int count = 0; //循环 foreach (CommandInfo myDE in cmdList) { string cmdText = myDE.CommandText; OdbcParameter[] cmdParms = (OdbcParameter[])myDE.Parameters; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) { if (myDE.CommandText.ToLower().IndexOf("count(") == -1) { trans.Rollback(); return 0; } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { trans.Rollback(); return 0; } if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { trans.Rollback(); return 0; } continue; } int val = cmd.ExecuteNonQuery(); count += val; if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) { trans.Rollback(); return 0; } cmd.Parameters.Clear(); } trans.Commit(); return count; } catch { trans.Rollback(); throw; } } } }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的OdbcParameter[])</param> public static int ExecuteSqlTrans(Hashtable SQLStringList) { int val = 0; using (OdbcConnection conn = new OdbcConnection(connectionString)) { conn.Open(); using (OdbcTransaction trans = conn.BeginTransaction()) { OdbcCommand cmd = new OdbcCommand(); try { //循环 foreach (DictionaryEntry myDE in SQLStringList) { string cmdText = myDE.Key.ToString(); OdbcParameter[] cmdParms = (OdbcParameter[])myDE.Value; PrepareCommand(cmd, conn, trans, cmdText, cmdParms); val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } return val; }
// // SetStockSymbols // Inserts stock symbol values into the database during // the call to SetPropertyValues. // private void SetStockSymbols(int uniqueID, ArrayList stocks) { OdbcConnection conn = new OdbcConnection(connectionString); OdbcCommand cmd = new OdbcCommand("DELETE FROM StockSymbols WHERE UniqueID = ?", conn); cmd.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID; OdbcCommand cmd2 = new OdbcCommand("INSERT INTO StockSymbols (UniqueID, StockSymbol) " + "Values(?, ?)", conn); cmd2.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID; cmd2.Parameters.Add("@StockSymbol", OdbcType.VarChar, 10); OdbcTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Transaction = tran; cmd2.Transaction = tran; // Delete any existing values; cmd.ExecuteNonQuery(); foreach (object o in stocks) { cmd2.Parameters["@StockSymbol"].Value = o.ToString(); cmd2.ExecuteNonQuery(); } tran.Commit(); } catch (OdbcException e) { try { tran.Rollback(); } catch { } if (WriteExceptionsToEventLog) { WriteToEventLog(e, "SetStockSymbols"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } }
/// <summary> /// 事务 /// </summary> /// <param name="strSQL">SQL语句组</param> /// <param name="conn">已打开的SQL连接</param> /// <returns></returns> public bool Transaction(List<string> strSQL, OdbcConnection conn) { OdbcTransaction tran = conn.BeginTransaction(); OdbcCommand comm = conn.CreateCommand(); comm.Transaction = tran; comm.CommandTimeout = this.CommandTimeout; try { int count = 0; foreach (string sql in strSQL) { comm.CommandText = sql; count += comm.ExecuteNonQuery(); } tran.Commit(); return count > 0 ? true : false; } catch (Exception err) { tran.Rollback(); throw err; } }
// // SetZipCode // Inserts the zip code value into the database during // the call to SetPropertyValues. // private void SetZipCode(int uniqueID, string zipCode) { if (zipCode == null) { zipCode = String.Empty; } OdbcConnection conn = new OdbcConnection(connectionString); OdbcCommand cmd = new OdbcCommand("DELETE FROM ProfileData WHERE UniqueID = ?", conn); cmd.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID; OdbcCommand cmd2 = new OdbcCommand("INSERT INTO ProfileData (UniqueID, ZipCode) " + "Values(?, ?)", conn); cmd2.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID; cmd2.Parameters.Add("@ZipCode", OdbcType.VarChar, 10).Value = zipCode; OdbcTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Transaction = tran; cmd2.Transaction = tran; // Delete any existing values. cmd.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); tran.Commit(); } catch (OdbcException e) { try { tran.Rollback(); } catch { } if (WriteExceptionsToEventLog) { WriteToEventLog(e, "SetZipCode"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } }
private void btnDescargar_Click(object sender, RoutedEventArgs e) { OdbcTransaction myTrans = null; try { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; if (lstArchivos.SelectedIndex != -1) { string selectedFolder = string.Empty; FolderBrowserDialog selectFolderDialog = new FolderBrowserDialog(); selectFolderDialog.ShowNewFolderButton = true; selectFolderDialog.ShowDialog(); selectedFolder = selectFolderDialog.SelectedPath; if (selectedFolder == string.Empty) { return; } System.Data.Odbc.OdbcConnection myConn = new System.Data.Odbc.OdbcConnection(); myConn.ConnectionString = ((Menu)this.Owner).sConexion; myConn.Open(); myTrans = myConn.BeginTransaction(); string myNonQuery2 = "set textsize 2147483647"; OdbcCommand myCommand2 = new OdbcCommand(myNonQuery2, myConn); string myQuery = "SELECT CONTENIDO FROM HHc_ArchivosVersion Where CODIGO_AGENCIA = ? AND VERSION = ? AND TIPO_DISPOSITIVO = ? AND ORDEN = ?"; OdbcCommand myOdbcCommand = new OdbcCommand(myQuery); myOdbcCommand.Connection = myConn; myOdbcCommand.Parameters.Add("CODIGO_AGENCIA", OdbcType.Int).Value = Int32.Parse((string)cmbAgencia.SelectedValue); myOdbcCommand.Parameters.Add("VERSION", OdbcType.VarChar).Value = (string)cmbVersion.SelectedValue; myOdbcCommand.Parameters.Add("TIPO_DISPOSITIVO", OdbcType.VarChar).Value = (string)cmbDispo.SelectedValue; myOdbcCommand.Parameters.Add("ORDEN", OdbcType.Int).Value = lstArchivos.SelectedIndex + 1; myOdbcCommand.Transaction = myTrans; myCommand2.Transaction = myTrans; myCommand2.ExecuteNonQuery(); OdbcDataReader myReader = myOdbcCommand.ExecuteReader(CommandBehavior.CloseConnection); while (myReader.Read()) { byte[] bFile = null; bFile = (byte[])myReader.GetValue(0); Archivo tmp = (Archivo)lstArchivos.SelectedItem; FileStream fileStream = new FileStream(selectedFolder + "\\" + tmp.sShort, FileMode.OpenOrCreate); fileStream.Write(bFile, 0, bFile.GetLength(0)); fileStream.Close(); } ITCMessageBox.Show(this, "Archivo descargado", "Mensaje!"); myConn.Close(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } } catch (Exception ex) { myTrans.Rollback(); ITCMessageBox.Show(this, "Error al intentar descargar el archivo archivos", "Error!"); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; } }
// // ProfileProvider.DeleteProfiles(String[]) // public override int DeleteProfiles(String[] usernames) { int deleteCount = 0; OdbcConnection conn = new OdbcConnection(connectionString); OdbcTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); foreach (string user in usernames) { if (DeleteProfile(user, conn, tran)) deleteCount++; } tran.Commit(); } catch (Exception e) { try { tran.Rollback(); } catch { } if (WriteExceptionsToEventLog) { WriteToEventLog(e, "DeleteProfiles(String())"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } return deleteCount; }
public static IEntity RedeemGift(long giftId, string username) { // move the record from redeenable_gift table to redeemed_gift table IDbConnection connection = null; IDbCommand command = null; IDataReader reader = null; IEntity gift = null; try { connection = new OdbcConnection( ConnectionString ); connection.Open( ); command = connection.CreateCommand( ); //get the gift type by selecting redeemable_gift table using id command.CommandText = String.Format("SELECT type_id,donate_time,paypal_txn_id FROM redeemable_gift WHERE id='{0}' AND account_name='{1}'", giftId, username); reader = command.ExecuteReader(); int typeId; int donateTime; string paypalTxnId = string.Empty; if (reader.Read()) { typeId = System.Convert.ToInt32(reader["type_id"]); donateTime = System.Convert.ToInt32(reader["donate_time"]); paypalTxnId = (string)reader["paypal_txn_id"]; } else { Console.WriteLine(String.Format("[Redeem Donation Gift] No such Gift(ID:{0}) for Account Name: {1}", giftId, username)); return null; } reader.Close(); command.Dispose(); // insert record to redeemed_gift first command = connection.CreateCommand( ); IDbTransaction transaction = connection.BeginTransaction(); command.Connection = connection; command.Transaction = transaction; DateTime currTime = DateTime.Now; string classConstructString = GetClassNameByType(typeId); gift = getGiftInstance(classConstructString); if ( gift == null) { Console.WriteLine(String.Format("[Redeem Donation Gift] Unable to finished the process. Gift(ID:{0}) for Account Name: {1}", giftId, username)); } //get the Serial from its instance Serial serial = gift.Serial.Value; //update the serial to database for your later tracking command.CommandText = String.Format("INSERT INTO redeemed_gift (id,type_id,account_name,donate_time,redeem_time,serial,paypal_txn_id) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", giftId, typeId, username, donateTime, Convert.ToInt32(ToUnixTimestamp(currTime)), serial.ToString(), paypalTxnId); if (command.ExecuteNonQuery() != 1) { Console.WriteLine(String.Format("[Redeem Donation Gift] (insert record to redeemed_gift) SQL Error. Unable to finished the process. Gift(ID:{0}) for Account Name: {1}", giftId, username)); transaction.Rollback(); return null; } //remove record from redeemable_gift command.CommandText = String.Format("DELETE FROM redeemable_gift WHERE id='{0}' AND account_name='{1}'", giftId, username); if (command.ExecuteNonQuery() != 1) { Console.WriteLine(String.Format("[Redeem Donation Gift] (remove record from redeemable_gift) SQL Error. Unable to finished the process. Gift(ID:{0}) for Account Name: {1}", giftId, username)); transaction.Rollback(); return null; } transaction.Commit(); } catch( Exception e ) { Console.WriteLine( "[Redeem Donation Gift] Error..." ); Console.WriteLine( e ); } finally { if (reader != null && !reader.IsClosed) reader.Close(); if (command != null && connection != null) { command.Dispose(); connection.Close(); connection.Dispose(); } } return gift; }
public OdbcTransaction BeginTransaction(string connectionString) { OdbcConnection dbConnection = new OdbcConnection(connectionString); dbConnection.Open(); OdbcTransaction dbTransaction = dbConnection.BeginTransaction(); return dbTransaction; }
// // RoleProvider.RemoveUsersFromRoles // public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames) { foreach (string rolename in rolenames) { if (!RoleExists(rolename)) { throw new ProviderException("Role name not found."); } } foreach (string username in usernames) { foreach (string rolename in rolenames) { if (!IsUserInRole(username, rolename)) { throw new ProviderException("User is not in role."); } } } OdbcConnection conn = new OdbcConnection(connectionString); OdbcCommand cmd = new OdbcCommand("DELETE FROM UsersInRoles " + " WHERE Username = ? AND Rolename = ? AND ApplicationName = ?", conn); OdbcParameter userParm = cmd.Parameters.Add("@Username", OdbcType.VarChar, 255); OdbcParameter roleParm = cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255); cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName; OdbcTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); cmd.Transaction = tran; foreach (string username in usernames) { foreach (string rolename in rolenames) { userParm.Value = username; roleParm.Value = rolename; cmd.ExecuteNonQuery(); } } tran.Commit(); } catch (OdbcException e) { try { tran.Rollback(); } catch { } if (WriteExceptionsToEventLog) { WriteToEventLog(e, "RemoveUsersFromRoles"); } else { throw e; } } finally { conn.Close(); } }
public void BeginTransaction_Connection_Closed () { OdbcConnection cn = new OdbcConnection (); try { cn.BeginTransaction (); Assert.Fail ("#A1"); } catch (InvalidOperationException ex) { // Invalid operation. The connection is closed Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2"); Assert.IsNull (ex.InnerException, "#A3"); Assert.IsNotNull (ex.Message, "#A4"); } try { cn.BeginTransaction ((IsolationLevel) 666); Assert.Fail ("#B1"); } catch (InvalidOperationException ex) { // Invalid operation. The connection is closed Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2"); Assert.IsNull (ex.InnerException, "#B3"); Assert.IsNotNull (ex.Message, "#B4"); } try { cn.BeginTransaction (IsolationLevel.Serializable); Assert.Fail ("#C1"); } catch (InvalidOperationException ex) { // Invalid operation. The connection is closed Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#C2"); Assert.IsNull (ex.InnerException, "#C3"); Assert.IsNotNull (ex.Message, "#C4"); } }
/// <summary> /// 演示 事务处理的部分。 /// </summary> /// <param name="conn"></param> private void Transaction(OdbcConnection conn) { Console.WriteLine("开始事务处理操作!"); // 开始创建一个事务. OdbcTransaction t = conn.BeginTransaction(); try { Console.WriteLine("模拟插入2条相同数据,造成主键冲突的异常!"); InsertData(conn, t); InsertData(conn, t); // 提交事务. t.Commit(); } catch (Exception ex) { Console.WriteLine(ex.Message); Console.WriteLine(ex.StackTrace); // 回滚事务. t.Rollback(); } if (ExistData(conn)) { Console.WriteLine("数据库中依然存在着主键为{0} 与 {1} 的的数据!", TEST_SALE_DATE, TEST_SALE_ITEM); } else { Console.WriteLine("数据库中已检索不到主键为{0} 与 {1} 的的数据!", TEST_SALE_DATE, TEST_SALE_ITEM); } }
private static void odbctest() { string s = ConfigurationManager.ConnectionStrings["odbc"].ConnectionString; string sql = ConfigurationManager.ConnectionStrings["sql"].ConnectionString; OdbcConnection myConnection = new OdbcConnection(s); SqlConnection cn = new SqlConnection(sql); try { myConnection.Open(); cn.Open(); if (myConnection.State == ConnectionState.Open) { OdbcTransaction tran = myConnection.BeginTransaction(); Console.WriteLine("Connection opened successfully!"); string command = "SELECT * FROM UREV.BGDTPRO ORDER BY PRO_VENCIMIENTO DESC FETCH FIRST 10 ROWS ONLY"; OdbcCommand cmd = new OdbcCommand(command, myConnection, tran); //OdbcDataReader reader = cmd.ExecuteReader(); OdbcDataAdapter da = new OdbcDataAdapter(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); da.SelectCommand = cmd; da.Fill(ds, "BGDTPRO"); dt = ds.Tables["BGDTPRO"]; /* DataSet CustomersDataSet = new DataSet(); SqlDataAdapter da_local; SqlCommandBuilder cmdBuilder; //Initialize the SqlDataAdapter object by specifying a Select command //that retrieves data from the sample table. da_local = new SqlDataAdapter("select * from dbo.PEDTFRM order by FRM_NUMCLIEN", cn); //Initialize the SqlCommandBuilder object to automatically generate and initialize //the UpdateCommand, InsertCommand, and DeleteCommand properties of the SqlDataAdapter. cmdBuilder = new SqlCommandBuilder(da_local); //Display the Update, Insert, and Delete commands that were automatically generated //by the SqlCommandBuilder object. Console.WriteLine("Update command Generated by the Command Builder : "); Console.WriteLine("=================================================="); Console.WriteLine(cmdBuilder.GetUpdateCommand().CommandText); Console.WriteLine(" "); Console.WriteLine("Insert command Generated by the Command Builder : "); Console.WriteLine("=================================================="); Console.WriteLine(cmdBuilder.GetInsertCommand().CommandText); Console.WriteLine(" "); Console.WriteLine("Delete command Generated by the Command Builder : "); Console.WriteLine("=================================================="); Console.WriteLine(cmdBuilder.GetDeleteCommand().CommandText); Console.WriteLine(" "); //Populate the DataSet by running the Fill method of the SqlDataAdapter. da_local.Fill(CustomersDataSet, "PEDTFRM"); //Modify the value of the CustName field. foreach (DataRow sourceRow in dt.Rows) { var desRow = CustomersDataSet.Tables["PEDTFRM"].NewRow(); foreach (DataColumn dc in dt.Columns) { if (dc.ColumnName != "FRM_TIMESTAMP") desRow[dc.ColumnName] = sourceRow[dc.ColumnName]; } //CustomersDataSet.Tables["PEDTFRM"].Rows.Add(desRow); } //Post the data modification to the database. da.Update(CustomersDataSet, "PEDTFRM"); //Close the database connection. //sql //while (reader.Read()) //{ //string row = reader.GetString(0); // decimal dec = reader.GetDecimal(1); // DateTime dt = reader.GetDateTime(2); //results.Add(row); // Console.WriteLine(row); //} //reader.Close(); //string insertcommand = "INSERT INTO APFR.TESTHEX (CLA_NUMCLIEN) VALUES (?)"; //foreach (string r in results) //{ // //wiersze 19,20 - OdbcType.VarChar // //13,14 - OleDbType.NVarChar // //17,18 - OleDbType.LongVarChar // OdbcCommand insCmd = myConnection.CreateCommand(); // insCmd.Parameters.Add("@clinum", OdbcType.NVarChar, r.Length).Value = r; // insCmd.CommandText = insertcommand; // insCmd.Transaction = tran; // int rowsInsererd = insCmd.ExecuteNonQuery(); //} */ tran.Commit(); cn.Close(); myConnection.Close(); } else Console.WriteLine("Connection could not be established"); } catch (Exception ex) { Console.WriteLine(ex.Message.ToString()); } finally { myConnection.Close(); } }
private void Thread_Start() { bool connected = false; OdbcConnection connection = null; OdbcCommand command = null; OdbcTransaction transact = null; DateTime start = DateTime.Now; bool shouldWriteException = true; while (true) { this.m_Sync.WaitOne(); while (this.m_Queue.Count > 0) { try { object obj = this.m_Queue.Dequeue(); if (obj == null) { if (connected) { if (transact != null) { try { transact.Commit(); } catch (Exception commitException) { Console.WriteLine("MyRunUO: Exception caught when committing transaction"); Console.WriteLine(commitException); try { transact.Rollback(); Console.WriteLine("MyRunUO: Transaction has been rolled back"); } catch (Exception rollbackException) { Console.WriteLine("MyRunUO: Exception caught when rolling back transaction"); Console.WriteLine(rollbackException); } } } try { connection.Close(); } catch { } try { connection.Dispose(); } catch { } try { command.Dispose(); } catch { } try { this.m_Sync.Close(); } catch { } Console.WriteLine(this.m_CompletionString, (DateTime.Now - start).TotalSeconds); this.m_HasCompleted = true; return; } else { try { connected = true; connection = new OdbcConnection(this.m_ConnectionString); connection.Open(); command = connection.CreateCommand(); if (Config.UseTransactions) { transact = connection.BeginTransaction(); command.Transaction = transact; } } catch (Exception e) { try { if (transact != null) transact.Rollback(); } catch { } try { if (connection != null) connection.Close(); } catch { } try { if (connection != null) connection.Dispose(); } catch { } try { if (command != null) command.Dispose(); } catch { } try { this.m_Sync.Close(); } catch { } Console.WriteLine("MyRunUO: Unable to connect to the database"); Console.WriteLine(e); this.m_HasCompleted = true; return; } } } else if (obj is string) { command.CommandText = (string)obj; command.ExecuteNonQuery(); } else { string[] parms = (string[])obj; command.CommandText = parms[0]; if (command.ExecuteScalar() == null) { command.CommandText = parms[1]; command.ExecuteNonQuery(); } } } catch (Exception e) { if (shouldWriteException) { Console.WriteLine("MyRunUO: Exception caught in database thread"); Console.WriteLine(e); shouldWriteException = false; } } } lock (this.m_Queue.SyncRoot) { if (this.m_Queue.Count == 0) this.m_Sync.Reset(); } } }
///// <summary> ///// 执行Sql和Oracle滴混合事务 ///// </summary> ///// <param name="list">SQL命令行列表</param> ///// <param name="oracleCmdSqlList">Oracle命令行列表</param> ///// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns> //public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList) //{ // using (OdbcConnection conn = new OdbcConnection(connectionString)) // { // conn.Open(); // OdbcCommand cmd = new OdbcCommand(); // cmd.Connection = conn; // OdbcTransaction tx = conn.BeginTransaction(); // cmd.Transaction = tx; // try // { // foreach (CommandInfo myDE in list) // { // string cmdText = myDE.CommandText; // OdbcParameter[] cmdParms = (OdbcParameter[])myDE.Parameters; // PrepareCommand(cmd, conn, tx, cmdText, cmdParms); // if (myDE.EffentNextType == EffentNextType.SolicitationEvent) // { // if (myDE.CommandText.ToLower().IndexOf("count(") == -1) // { // tx.Rollback(); // throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式"); // //return 0; // } // object obj = cmd.ExecuteScalar(); // bool isHave = false; // if (obj == null && obj == DBNull.Value) // { // isHave = false; // } // isHave = Convert.ToInt32(obj) > 0; // if (isHave) // { // //引发事件 // myDE.OnSolicitationEvent(); // } // } // if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) // { // if (myDE.CommandText.ToLower().IndexOf("count(") == -1) // { // tx.Rollback(); // throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式"); // //return 0; // } // object obj = cmd.ExecuteScalar(); // bool isHave = false; // if (obj == null && obj == DBNull.Value) // { // isHave = false; // } // isHave = Convert.ToInt32(obj) > 0; // if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) // { // tx.Rollback(); // throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0"); // //return 0; // } // if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) // { // tx.Rollback(); // throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0"); // //return 0; // } // continue; // } // int val = cmd.ExecuteNonQuery(); // if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) // { // tx.Rollback(); // throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行"); // //return 0; // } // cmd.Parameters.Clear(); // } // //string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC"); // //bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList); // //if (!res) // //{ // // tx.Rollback(); // // throw new Exception("Oracle执行失败"); // // // return -1; // //} // //tx.Commit(); // return 1; // } // catch (OdbcException e) // { // tx.Rollback(); // throw e; // } // catch (Exception e) // { // tx.Rollback(); // throw e; // } // } //} /// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static int ExecuteSqlTran(List<String> SQLStringList) { using (OdbcConnection conn = new OdbcConnection(connectionString)) { conn.Open(); OdbcCommand cmd = new OdbcCommand(); cmd.Connection = conn; OdbcTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); return count; } catch { tx.Rollback(); return 0; } } }