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