public void BeginTransaction()
{
this.Open();
this.cmd = this.connection.CreateCommand();
this.transaction = this.connection.BeginTransaction();
this.cmd.Transaction = this.transaction;
}
/// <summary>
/// ²éÕÒ²»´æÔÚµÄͼƬ²¢É¾³ýÒÑɾ³ýµÄͼƬ¼°Êý¾Ý
/// </summary>
public void DeleteList(OleDbConnection conn, OleDbTransaction trans, List<Model.article_albums> models, int article_id)
{
StringBuilder idList = new StringBuilder();
if (models != null)
{
foreach (Model.article_albums modelt in models)
{
if (modelt.id > 0)
{
idList.Append(modelt.id + ",");
}
}
}
string id_list = Utils.DelLastChar(idList.ToString(), ",");
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,big_img,small_img from dt_article_albums where article_id=" + article_id);
if (!string.IsNullOrEmpty(id_list))
{
strSql.Append(" and id not in(" + id_list + ")");
}
DataSet ds = DbHelperOleDb.Query(conn, trans, strSql.ToString());
foreach (DataRow dr in ds.Tables[0].Rows)
{
int rows = DbHelperOleDb.ExecuteSql(conn, trans, "delete from dt_article_albums where id=" + dr["id"].ToString()); //ɾ³ýÊý¾Ý¿â
if (rows > 0)
{
Utils.DeleteFile(dr["big_img"].ToString()); //ɾ³ýÔͼ
Utils.DeleteFile(dr["small_img"].ToString()); //ɾ³ýËõÂÔͼ
}
}
}
public void TestBeginTransactionChaos() {
DataBaseServer dbType = ConnectedDataProvider.GetDbType(con);
// not supported on DB2 and Oracle and Sybase
if (dbType != DataBaseServer.Oracle && dbType != DataBaseServer.DB2 && dbType != DataBaseServer.Sybase) {
con.Close();
con.Open();
try {
BeginCase("BeginTransaction - IsolationLevel Chaos");
tran = con.BeginTransaction(IsolationLevel.Chaos);
Compare(tran == null, false);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
}
/* not supported by MSSQL,DB2,Oracle
con.Close();
con.Open();
try
{
BeginCase("BeginTransaction - IsolationLevel Unspecified");
tran = con.BeginTransaction(IsolationLevel.Unspecified );
Compare(tran == null, false);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
*/
}
/// <summary>
/// 查找不存在的图片并删除已删除的图片及数据
/// </summary>
public void DeleteList(OleDbConnection conn, OleDbTransaction trans, List<Model.article_albums> models, int article_id)
{
StringBuilder idList = new StringBuilder();
if (models != null)
{
foreach (Model.article_albums modelt in models)
{
if (modelt.id > 0)
{
idList.Append(modelt.id + ",");
}
}
}
string id_list = Utils.DelLastChar(idList.ToString(), ",");
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,thumb_path,original_path from " + databaseprefix + "article_albums where article_id=" + article_id);
if (!string.IsNullOrEmpty(id_list))
{
strSql.Append(" and id not in(" + id_list + ")");
}
DataSet ds = DbHelperOleDb.Query(conn, trans, strSql.ToString());
foreach (DataRow dr in ds.Tables[0].Rows)
{
int rows = DbHelperOleDb.ExecuteSql(conn, trans, "delete from " + databaseprefix + "article_albums where id=" + dr["id"].ToString()); //删除数据库
if (rows > 0)
{
Utils.DeleteFile(dr["thumb_path"].ToString()); //删除缩略图
Utils.DeleteFile(dr["original_path"].ToString()); //删除原图
}
}
}
/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
/// <param name="command">the OleDbCommand to be prepared</param>
/// <param name="connection">a valid OleDbConnection, on which to execute this command</param>
/// <param name="transaction">a valid OleDbTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OleDbParameters to be associated with the command or 'null' if no parameters are required</param>
private static void PrepareCommand( OleDbCommand command, OleDbConnection connection, OleDbTransaction transaction, CommandType commandType, string commandText, OleDbParameter[] commandParameters )
{
//if the provided connection is not open, we will open it
if( connection.State != ConnectionState.Open )
{
connection.Open();
}
//associate the connection with the command
command.Connection = connection;
//set the command text (stored procedure name or OleDb statement)
command.CommandText = commandText;
//if we were provided a transaction, assign it.
if( transaction != null )
{
command.Transaction = transaction;
}
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provided
if( commandParameters != null )
{
AttachParameters( command, commandParameters );
}
return;
}
/// <summary>
/// Конструктор
/// </summary>
/// <param name="aConnectionString">строка соединения</param>
public WorkDB(string aConnectionString)
{
mCn = new OleDbConnection(aConnectionString);
mErr = new Error();
mTxn = null;
mIsInternalTransaction = false;
}
private void Init(JetProcessor processor, IEnumerable<string> columnDefinitions)
{
Connection = processor.Connection;
Transaction = processor.Transaction;
Create(columnDefinitions);
}
public DBBase(IDbConnection conn)
{
this._connection = null;
this._transaction = null;
this.DB_Driver_Type = "OleDb";
this._connection = (OleDbConnection) conn;
}
/// <summary>
/// Generic method for issuing simple Insert commands to the current VWA4 database,
/// based on provided connection.
/// </summary>
/// <param name="sql">SQL query string to execute.</param>
/// <param name="conn">OleDbConnection to desired database.</param>
/// <param name="trans"></param>
/// <returns>PK/ID of inserted record.</returns>
public static int Insert(string sql, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
{
// Connect up to the database
int id = -1;
try
{
// Tee up the command
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
//
// Issue the command
//
if (trans != null)
{
cmd.Transaction = trans;
}
int iSqlStatus = cmd.ExecuteNonQuery();
//Now check the status
if (iSqlStatus <= 0)
{
//DO your failed messaging here
MessageBox.Show(null, "VWA4.NET INSERT ERROR: Query Failed to insert with status: " + iSqlStatus, "Insert Error");
}
cmd.CommandText = "SELECT @@Identity";
id = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
MessageBox.Show(null, "VWA4.NET INSERT Exception: Error " + ex.Message + "!", "Insert Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(id);
}
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
oleDbConnection.Open();
using (System.Data.OleDb.OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction())
{
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();
try
{
foreach (DictionaryEntry dictionaryEntry in SQLStringList)
{
string cmdText = dictionaryEntry.Key.ToString();
System.Data.OleDb.OleDbParameter[] cmdParms = (System.Data.OleDb.OleDbParameter[])dictionaryEntry.Value;
DbHelperOleDb.PrepareCommand(oleDbCommand, oleDbConnection, oleDbTransaction, cmdText, cmdParms);
int num = oleDbCommand.ExecuteNonQuery();
oleDbCommand.Parameters.Clear();
oleDbTransaction.Commit();
}
}
catch
{
oleDbTransaction.Rollback();
throw;
}
}
}
}
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString))
{
oleDbConnection.Open();
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();
oleDbCommand.Connection = oleDbConnection;
System.Data.OleDb.OleDbTransaction oleDbTransaction = oleDbConnection.BeginTransaction();
oleDbCommand.Transaction = oleDbTransaction;
try
{
for (int i = 0; i < SQLStringList.Count; i++)
{
string text = SQLStringList[i].ToString();
if (text.Trim().Length > 1)
{
oleDbCommand.CommandText = text;
oleDbCommand.ExecuteNonQuery();
}
}
oleDbTransaction.Commit();
}
catch (System.Data.OleDb.OleDbException ex)
{
oleDbTransaction.Rollback();
throw new Exception(ex.Message);
}
}
}
public void Commit()
{
if (connection==null) throw new PersistException("No current connection");
if (transaction==null) throw new PersistException("No transaction to commit");
transaction.Commit();
transaction=null;
}
public int product_service(string service_id, string product_id, int customer_id)
{
int row=0;
cn = new OleDbConnection("Provider=MSDAORA;Data Source=192.168.0.217/orcl;Persist Security Info=True;User ID=hr;Password=hr;");
cmd = cn.CreateCommand();
try
{
cn.Open();
string s = "update product_services set product_id='" + product_id + "' where service_id=(select service_id from customer_services where customer_id=" + customer_id + " and service_id='" + service_id + "')";
Trans = cn.BeginTransaction();
cmd = new OleDbCommand(s, cn,Trans);
cmd.Transaction = Trans;
row= cmd.ExecuteNonQuery();
Trans.Commit();
}
catch (Exception e)
{
string s = e.Message;
Trans.Rollback();
}
finally
{
cn.Close();
}
return row;
}
public OleDbCommand CreateAddProductCommand(OleDbConnection conn, OleDbTransaction transaction)
{
var cmd = new OleDbCommand(ConvertToOleDbQuery(_sql), conn, transaction);
CreateParameters(cmd);
return cmd;
}
/// <summary>
/// Initializes a new instance.
/// </summary>
/// <param name="connectionString">OleDB Connection string</param>
/// <param name="useTransaction">True if you want to use a transaction</param>
public SqlHelper(string connectionString, bool useTransaction) {
_connection = new OleDbConnection(connectionString);
_connection.Open();
if (useTransaction) {
_transaction = _connection.BeginTransaction();
}
}
/// <summary>
/// Конструктор
/// </summary>
/// <param name="aCn">соединение</param>
public WorkDB(OleDbConnection aCn)
{
mCn = aCn;
mErr = new Error();
//pQP = null;
mTxn = null;
mIsInternalTransaction = false;
}
/// <summary>
/// Starts a Transaction session on the current connection.
/// </summary>
public void BeginTran()
{
if (oConn.State == ConnectionState.Open)
{
oTran = oConn.BeginTransaction();
blnTranActive = true;
}
}
public IDbTransaction BeginTran()
{
if (this._connection != null)
{
this._transaction = this._connection.BeginTransaction();
return this._transaction;
}
return null;
}
public override void BeginTransaction()
{
if (Transaction != null) return;
EnsureConnectionIsOpen();
Announcer.Say("Beginning Transaction");
Transaction = Connection.BeginTransaction();
}
public override void CommitTransaction()
{
if (Transaction == null) return;
Announcer.Say("Committing Transaction");
Transaction.Commit();
WasCommitted = true;
Transaction = null;
}
/// <summary>
/// Returns a data source wrapped around the transaction.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="transaction">The transaction.</param>
/// <returns>SqlServerOpenDataSource.</returns>
/// <exception cref="ArgumentNullException"></exception>
public static AccessOpenDataSource AsDataSource(this OleDbConnection connection, OleDbTransaction transaction)
{
if (connection == null)
throw new ArgumentNullException(nameof(connection), $"{nameof(connection)} is null.");
if (connection.State == ConnectionState.Closed)
connection.Open();
var dataSourceBase = s_CachedDataSources.GetOrAdd(connection.ConnectionString, cs => new AccessDataSource(cs));
return new AccessOpenDataSource(dataSourceBase, connection, transaction);
}
public static int ExecuteNoQuery(CommandType commType, string commText, OleDbParameter[] param, OleDbTransaction tran)
{
int result = 0;
using (OleDbConnection conn = new OleDbConnection(connectString))
{
PrepareCommand(conn, comm, commType, commText, param, tran);
result = comm.ExecuteNonQuery();
}
return result;
}
public OleDbCustomCommandBuilder( DataTable dataTable, OleDbConnection connection, ArrayList columnOleDbTypes, OleDbTransaction transaction)
{
if (dataTable.Columns.Count != columnOleDbTypes.Count){
throw new ArgumentException("Number of OleDbTypes does not match number of columns.");
}
this.dataTable = dataTable;
this.connection = connection;
this.columnOleDbTypes = columnOleDbTypes;
this.transaction = transaction;
}
/// <summary>執行 Transaction</summary>
/// <param name="alSQL">欲執行交易的 ArrayList (內含 SQL 指令)</param>
/// <param name="OleDbConn">OleDbConnection連線物件</param>
/// <returns>Transaction是否成功</returns>
/// <remarks></remarks>
public static bool raiseOleDbTransaction(ArrayList alSQL, System.Data.OleDb.OleDbConnection OleDbConn)
{
if (alSQL == null)
{
return(true);
}
if (alSQL.Count == 0)
{
return(true);
}
if (OleDbConn == null)
{
OleDbConn = createOleDbConnection();
}
System.Data.OleDb.OleDbTransaction OleDbTrans = null;
if (!(OleDbConn.State == ConnectionState.Open))
{
OleDbConn.Open();
}
System.Data.OleDb.OleDbCommand cmd = OleDbConn.CreateCommand();
StringBuilder strSQL = new StringBuilder("");
OleDbTrans = OleDbConn.BeginTransaction();
try
{
cmd.Transaction = OleDbTrans;
for (int i = 0; i <= alSQL.Count - 1; i++)
{
if (!string.IsNullOrEmpty(alSQL[i].ToString()))
{
strSQL.AppendLine(alSQL[i].ToString());
}
}
cmd.CommandText = strSQL.ToString();
cmd.ExecuteNonQuery();
OleDbTrans.Commit();
return(true);
}
catch (Exception Ex)
{
if ((OleDbTrans != null))
{
OleDbTrans.Rollback();
}
//Message.alertMessage("C0002", null, Ex.Message.ToString(), null);
return(false);
}
finally
{
if (!(OleDbConn.State == ConnectionState.Closed))
{
OleDbConn.Close();
}
}
}
void GenerateDay(OleDbConnection connection, OleDbTransaction transaction, int n, DateTime day) {
double totalDays = day.Subtract(minRequiredDate).TotalDays;
int salesPerDay = random.Next(180, (int)(200 * (1 + (totalDays * dailySalesGrowth) / 10)));
int[] generateIntervals = GenerateTimeIntervals(salesPerDay);
DateTime start = new DateTime(day.Year, day.Month, day.Day, startTime, 0, 0);
for(int d = 0; d < generateIntervals.Length; d++) {
for(int x = 0; x < generateIntervals[d]; x++)
GenerateSale(connection, transaction, start);
start = start.AddMinutes(timeInterval);
}
}
/// <summary>
/// トランザクション開始
/// </summary>
/// <remarks></remarks>
public void BeginTransaction()
{
try
{
_trn = _conn.BeginTransaction();
}
catch (Exception ex)
{
throw new Exception("BeginTransaction Error", ex);
}
}
public void BeginTransaction()
{
try
{
if (myTx == null)
myTx = GetConnection(true).BeginTransaction();
}
catch (OleDbException ex)
{
throw new ApplicationException("Erro BD", ex);
}
}
/// <summary>
/// 得到最大ID
/// </summary>
private int GetMaxId(OleDbConnection conn, OleDbTransaction trans)
{
string strSql = "select top 1 id from dt_sys_channel order by id desc";
object obj = DbHelperOleDb.GetSingle(conn, trans, strSql);
if (obj == null)
{
return 0;
}
else
{
return int.Parse(obj.ToString());
}
}
private static int GetTablesCount(string tableName, OleDbConnection conn, OleDbTransaction transaction)
{
int count = 0;
string sql = "select count(*) from db_class where class_name = '" + tableName + "'";
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
cmd.Transaction = transaction;
count = (int)cmd.ExecuteScalar();
}
return count;
}
public void BeginTrans()
{
if (_conn != null)
{
if (_trans != null)
{
throw new Exception("Transition already began! Please commit it before begin a new one.");
}
_trans = _conn.BeginTransaction();
_isInTransaction = true;
}
}
public OleDbCommand ()
{
commandText = String.Empty;
timeout = 30; // default timeout per .NET
commandType = CommandType.Text;
connection = null;
parameters = new OleDbParameterCollection ();
transaction = null;
designTimeVisible = false;
dataReader = null;
behavior = CommandBehavior.Default;
gdaCommand = IntPtr.Zero;
}
/// <summary>
/// opens a new connection then starts transaction on that connection
/// </summary>
public bool BeginNewTransaction()
{
try
{
_conn = getNewConnection();
_tran = _conn.BeginTransaction();
return true;
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
return false;
}
}
private void buttonBegin_Click(object sender, EventArgs e)
{
try
{
this.oleDbConnection.Open();
trnNew = oleDbConnection.BeginTransaction();
MessageBox.Show("Isolation Level: "
+ trnNew.IsolationLevel.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public int CreateSupiler(Supiler newSupiler)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbSupiler(SPCode,SPName,Address,PersonContact,Phone,Fax)");
sb.Append(" VALUES (@SPCode,@SPName,@Address,@PersonContact,@Phone,@Fax)");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@SPCode", OleDbType.VarChar).Value = newSupiler.SPCode;
com.Parameters.Add("@SPName", OleDbType.VarChar).Value = newSupiler.SPName;
com.Parameters.Add("@Address", OleDbType.VarChar).Value = newSupiler.Address;
com.Parameters.Add("@PersonContact", OleDbType.VarChar).Value = newSupiler.PersonContact;
com.Parameters.Add("@Phone", OleDbType.VarChar).Value = newSupiler.Phone;
com.Parameters.Add("@Fax", OleDbType.VarChar).Value = newSupiler.Fax;
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public int CreateCustomer(Customer newCustomer)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbCustomer(CCode,CName,Address,PRVID,Phone,Discount)");
sb.Append(" VALUES (@CCode,@CName,@Address,@PRVID,@Phone,@Discount)");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@CCode", OleDbType.VarChar).Value = newCustomer.CCode;
com.Parameters.Add("@CName", OleDbType.VarChar).Value = newCustomer.CName;
com.Parameters.Add("@Address", OleDbType.VarChar).Value = newCustomer.Address;
com.Parameters.Add("@PRVID", OleDbType.VarChar).Value = newCustomer.PRVID;
com.Parameters.Add("@Phone", OleDbType.VarChar).Value = newCustomer.Phone;
com.Parameters.Add("@Discount", OleDbType.VarChar).Value = newCustomer.Discount;
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public static OleDbDataReader fetch(string str, ref System.Data.OleDb.OleDbTransaction t)
{
if (dbcon.State == ConnectionState.Closed)
{
dbcon.Open();
}
OleDbCommand Cmd_fetch = null;
OleDbDataReader Fetch_dr = null;
Cmd_fetch = new OleDbCommand(str, dbcon);
Cmd_fetch.Transaction = t;
Fetch_dr = Cmd_fetch.ExecuteReader();
Cmd_fetch.Dispose();
return(Fetch_dr);
}
public static void loginSaveToDB2(string str, System.Data.OleDb.OleDbTransaction t)
{
try
{
OleDbCommand Cmd_fetch = null;
//Dim Fetch_dr As OleDbDataReader
Cmd_fetch = new OleDbCommand(str, lgCon);
Cmd_fetch.Transaction = t;
Cmd_fetch.ExecuteNonQuery();
}
catch (Exception ex)
{
string err = null;
err = "ERROR::Module1::loginSaveToDB2():" + ex.Message;
MessageBox.Show(err);
LoggingManager.WriteToLog(0, "Error : LoginSaveToDB() : " + err);
}
}
public static bool ExecuteNonQuery(System.Data.OleDb.OleDbCommand[] sqlCmdArray)
{
if (Connection1.State == ConnectionState.Closed)
{
Connection1.Open();
}
Transaction = Connection.BeginTransaction();
for (int i = 0; i < sqlCmdArray.Length; i++)
{
if (!ExecuteNonQuery(sqlCmdArray[i]))
{
Transaction.Rollback();
Connection1.Close();
return(false);
}
}
Transaction.Commit();
Connection1.Close();
return(true);
}
public static void saveToDB2(string str, System.Data.OleDb.OleDbTransaction t, OleDbConnection connection)
{
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
OleDbCommand Cmd_fetch = null;
Cmd_fetch = new OleDbCommand(str, connection);
Cmd_fetch.Transaction = t;
Cmd_fetch.ExecuteNonQuery();
}
catch (Exception ex)
{
string err = null;
err = "ERROR::Module1::saveToDB2():" + ex.Message;
MessageBox.Show(err);
LoggingManager.WriteToLog(0, "Error : saveToDB2() : " + err);
throw new Exception("Rollback Transaction");
}
}
public OleDbCommand(string cmdText, OleDbConnection connection,
OleDbTransaction transaction) : this(cmdText, connection)
{
this.transaction = transaction;
}
public static string AutoCreateBEOID(string sBEONum, DateTime transferDate, int nTypeCatalogID, ref string sError, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
{
DataTable dt;
string sBEOID = "";
dt = VWA4Common.DB.Retrieve("SELECT TypeID FROM BEOSubTypes " +
" WHERE TypeCatalogID = " + nTypeCatalogID +
" AND Enabled = False " +
" AND TypeID IN (SELECT TypeID FROM BEOType WHERE BEONumber = '" + sBEONum + "')", conn, trans);
if (nTypeCatalogID != 0 && dt.Rows.Count > 0)// BEO ID Exists but disabled
{
sBEOID = dt.Rows[0][0].ToString();
VWA4Common.DB.Update("UPDATE BEOSubTypes SET Enabled = True WHERE TypeID = '" + sBEOID + "'", conn, trans);
sError = (sError == "" ? "" : sError + "\n") + "EO Sub Type was Enabled for this EO Number\n";
if (dt.Rows.Count > 1)
{
sError = (sError == "" ? "" : sError + "\n") + "More than 1 EO with such EO number exists!\n";
}
}
else
{
dt = VWA4Common.DB.Retrieve("SELECT TypeID FROM BEOType " +
" WHERE BEONumber = '" + sBEONum + "'", conn, trans);
if (dt.Rows.Count > 0) // if BEONumber exists only for master, never happens in correct DB
{
sBEOID = dt.Rows[0][0].ToString();
VWA4Common.DB.Insert("INSERT INTO BEOSubTypes (TypeCatalogID, TypeID, Enabled)" +
" VALUES (" + nTypeCatalogID + "'" + dt.Rows[0].ItemArray[0].ToString() + "', True)", conn, trans);
sError = (sError == "" ? "" : sError + "\n") + "This BEONumber existed only in master!";
}
else
{
sBEOID = GetNextKey("BEOType", conn, trans);
int catID = VWA4Common.Utilities.GetQuickAddCategory("BEOCategory", "(Tracker Generated Event Orders)");
// create BEOID in master catalog
VWA4Common.DB.Insert("INSERT INTO BEOType (TypeID, TypeName, ReportTypeName, EventDate, CatID, BEONumber, ModifiedDate)" +
" VALUES ('" + sBEOID + "', 'AutoGenerated" + sBEONum + "', 'AutoGenerated" + sBEONum + "', #" + VWACommon.DateToString(transferDate) + "#, " +
catID + ", '" +
sBEONum + "', #" + VWACommon.DateToString(DateTime.Now) + "#)", conn, trans);
dt = VWA4Common.DB.Retrieve("SELECT ID FROM TypeCatalogs;", conn, trans);
string[] arr = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
string sEnabled = "False";
int currID = int.Parse(dt.Rows[0].ItemArray[0].ToString());
if (currID == nTypeCatalogID)
{
sEnabled = "True";
}
// create BEOID in SubTypes
VWA4Common.DB.Insert("INSERT INTO BEOSubTypes (TypeCatalogID, TypeID, Enabled)" +
" VALUES (" + currID + ", '" + sBEOID + "', " + sEnabled + ")", conn, trans);
}
}
}
return(sBEOID);
}
//Get the next key value for a specified table name (return as function value)
public static string GetNextKey(string type, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
{
string stNextKey = "";
try
{
DataTable dt;
dt = VWA4Common.DB.Retrieve("SELECT NextKeyValue FROM KeyTable WHERE TableName = '" + type + "'", conn, trans);
// Handle the case where there is no existing record, i.e. no new auto-generated
// keys have been added yet for that table
if (dt.Rows.Count == 0)
{ // no auto-generate keys yet for this type - initialize
switch (type)
{
case "FoodType":
stNextKey = "ZFT_900000001"; // 5th character must be non-zero numeric
break;
case "LossType":
stNextKey = "ZLT_900000001"; // 5th character must be non-zero numeric
break;
case "ContainerType":
stNextKey = "ZCT_900000001"; // 5th character must be non-zero numeric
break;
case "UserType":
stNextKey = "ZUS_900000001"; // 5th character must be non-zero numeric
break;
case "StationType":
stNextKey = "ZST_900000001"; // 5th character must be non-zero numeric
break;
case "DispositionType":
stNextKey = "ZDS_900000001"; // 5th character must be non-zero numeric
break;
case "DaypartType":
stNextKey = "ZDP_900000001"; // 5th character must be non-zero numeric
break;
case "BEOType":
stNextKey = "ZBE_900000001"; // 5th character must be non-zero numeric
break;
default:
stNextKey = type + "_900000001";
break;
}
VWA4Common.DB.Insert("INSERT INTO KeyTable (TableName, KeyFieldName, KeyFieldValue) VALUES ( '" + type + "', 'TypeID', '" + stNextKey + "'", conn, trans);
}
else
{ // Just use the next (properly structured) key and generate a new next one
stNextKey = dt.Rows[0].ItemArray[0].ToString();
int num = int.Parse(stNextKey.Substring(stNextKey.Length - 9, 9)) + 1;
stNextKey = stNextKey.Substring(0, stNextKey.Length - 9) + string.Format("{0:000000000}", num);
VWA4Common.DB.Update("UPDATE KeyTable SET NextKeyValue = '" + stNextKey + "' WHERE TableName = '" + type + "'", conn, trans);
}
}
catch (Exception ex)
{
MessageBox.Show(null, "Error occured! Error raised, with message : " + ex.Message, "VWA Import File Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return("");
}
return(stNextKey);
}
/// <summary>
/// Generic method for issuing simple Delete commands to the current VWA4 database,
/// based on provided connection.
/// </summary>
/// <param name="sql">SQL query string to execute.</param>
/// <param name="conn">OleDbConnection to desired database.</param>
/// <param name="trans"></param>
/// <returns>true if successful.</returns>
public static bool Delete(string sql, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
{
return(Update(sql, conn, trans));
}
/// <summary>
/// Retrieve data from database, based on the supplied OleDbConnection.
/// </summary>
/// <param name="sql">SQL query string to execute.</param>
/// <param name="conn">OleDbConnection to desired database.</param>
/// <param name="trans"></param>
/// <returns></returns>
public static DataTable Retrieve(string sql, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
{
DataTable dataTable = new DataTable();
try
{
// Connect up to the database
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
// Tee up the command
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
if (trans != null)
{
cmd.Transaction = trans;
}
//
// Issue the command
//
da.SelectCommand = cmd;
da.Fill(dataTable);
}
catch (Exception ex)
{
MessageBox.Show(null, "VWA4.NET SELECT Exception: Error " + ex.Message + "!", "Select Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(dataTable);
}
public static int ExecuteNonQuery(this OleDbConnection connection, string sql, OleDbTransaction transaction, params OleDbParameter[] parameters)
{
return(ExecuteNonQuery(connection, sql, CommandType.Text, transaction, parameters));
}
/// <summary>
/// Generic method for issuing simple Update commands to the current VWA4 database,
/// based on provided connection.
/// </summary>
/// <param name="sql">SQL query string to execute.</param>
/// <param name="conn">OleDbConnection to desired database.</param>
/// <param name="trans"></param>
/// <returns>true if successful.</returns>
public static bool Update(string sql, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
{
// Connect up to the database
int iSqlStatus;
try
{
// Tee up the command
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, conn);
if (trans != null)
{
cmd.Transaction = trans;
}
//
// Issue the command
//
iSqlStatus = cmd.ExecuteNonQuery();
//Now check the status
if (iSqlStatus < 0)
{
//DO your failed messaging here
MessageBox.Show(null, "VWA4.NET UPDATE ERROR: Query failed to update with status: " + iSqlStatus, "Update Error");
}
}
catch (Exception ex)
{
MessageBox.Show(null, "VWA4.NET UPDATE Exception: Error " + ex.Message + "!", "Update Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
return(iSqlStatus == 0);
}
public static int GetProducedID(string sProducedLotNum, ref string sError, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
{
DataTable dt;
dt = VWA4Common.DB.Retrieve("SELECT ID FROM WeightsProduced WHERE LotNumber = '" + sProducedLotNum + "'", conn, trans);
if (dt.Rows.Count == 0)
{
sError = (sError == "" ? "" : sError + "\n") + "Produced data with such lot number doesn't exist! \n";
// todo: Mila Insert Produced data and return generated ID
//return VWA4Common.DB.Insert("INSERT INTO WeightsProduced(LotNumber, [Timestamp]) " +
// "VALUES('" + sProducedLotNum + "', #" + VWA4Common.VWACommon.DateToString(DateTime.Now) + "#)",
// conn, trans);
return(-1);
}
else
{
if (dt.Rows.Count > 1)
{
sError = (sError == "" ? "" : sError + "\n") + "More than 1 Produced data with such Lot number exists!";
}
return(int.Parse(dt.Rows[0][0].ToString()));
}
}
public static string GetBEOID(string sBEONum, DateTime transferDate, int nTypeCatalog, ref string sError, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans)
{
DataTable dt;
if (nTypeCatalog == 0)
{
dt = VWA4Common.DB.Retrieve("SELECT * FROM BEOType WHERE BEONumber = '" + sBEONum + "'", conn, trans);
}
else
{
dt = VWA4Common.DB.Retrieve("SELECT TypeID FROM BEOSubTypes " +
" WHERE TypeCatalogID = " + nTypeCatalog +
" AND Enabled = True " +
" AND TypeID IN (SELECT TypeID FROM BEOType WHERE BEONumber = '" + sBEONum + "')", conn, trans);
}
if (dt.Rows.Count == 0)
{
sError = (sError == "" ? "" : sError + "\n") + "EO with such EO number did not exist! EO was added automatically! \n";
// todo: Mila Insert BEO and return generated ID
return(AutoCreateBEOID(sBEONum, transferDate, nTypeCatalog, ref sError, conn, trans));
}
else
{
if (dt.Rows.Count > 1)
{
sError = (sError == "" ? "" : sError + "\n") + "More than 1 EO with such EO number exists!";
}
return(dt.Rows[0].ItemArray[0].ToString());
}
}
public OleDbCommand(string cmdText, OleDbConnection connection, OleDbTransaction transaction) : this()
{
CommandText = cmdText;
Connection = connection;
Transaction = transaction;
}
public static Nullable <T> ExecuteScalar <T>(this OleDbConnection connection, string sql, OleDbTransaction transaction, params OleDbParameter[] parameters) where T : struct
{
return(ExecuteScalar <T>(connection, sql, CommandType.Text, transaction, parameters));
}
public static T ExecuteQuery <T>(this OleDbConnection connection, string sql, CommandType commandType, OleDbTransaction transaction, params OleDbParameter[] parameters) where T : DataSet, new()
{
using (OleDbCommand command = connection.CreateCommand())
{
command.Connection = connection;
command.CommandText = sql;
command.CommandType = commandType;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
connection.Open();
T datasource = new T();
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(datasource);
return(datasource);
}
}
private static void PrepareCommand(System.Data.OleDb.OleDbCommand cmd, System.Data.OleDb.OleDbConnection conn, System.Data.OleDb.OleDbTransaction trans, string cmdText, System.Data.OleDb.OleDbParameter[] cmdParms)
{
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = System.Data.CommandType.Text;
if (cmdParms != null)
{
for (int i = 0; i < cmdParms.Length; i++)
{
System.Data.OleDb.OleDbParameter value = cmdParms[i];
cmd.Parameters.Add(value);
}
}
}
internal OleDbTransaction ValidateTransaction(OleDbTransaction transaction, string method)
{
return(this.GetOpenConnection().ValidateTransaction(transaction, method));
}
public static void RollbackTransaction()
{
Transaction.Rollback();
Transaction = null;
}
public static int ExecuteNonQuery(this OleDbConnection connection, string sql, CommandType commandType, OleDbTransaction transaction, params OleDbParameter[] parameters)
{
using (OleDbCommand command = connection.CreateCommand())
{
command.Connection = connection;
command.CommandText = sql;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
connection.Open();
return(command.ExecuteNonQuery());
}
}
private void ValidateConnectionAndTransaction(string method)
{
ValidateConnection(method);
_transaction = _connection.ValidateTransaction(Transaction, method);
this.canceling = false;
}
public static void CommitTransaction()
{
Transaction.Commit();
Transaction = null;
}
public static T ExecuteQuery <T>(this OleDbConnection connection, string sql, OleDbTransaction transaction, params OleDbParameter[] parameters) where T : DataSet, new()
{
return(ExecuteQuery <T>(connection, sql, CommandType.Text, transaction, parameters));
}
public static Nullable <T> ExecuteScalar <T>(this OleDbConnection connection, string sql, CommandType commandType, OleDbTransaction transaction, params OleDbParameter[] parameters) where T : struct
{
using (OleDbCommand command = connection.CreateCommand())
{
command.Connection = connection;
command.CommandText = sql;
command.CommandType = commandType;
if (transaction != null)
{
command.Transaction = transaction;
}
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
connection.Open();
//object value =
//NullableConverter converter = new NullableConverter(typeof(Nullable<T>));
//Nullable<T> dateTimevalue = converter.ConvertFromString(value.ToString());
return((Nullable <T>)command.ExecuteScalar());
}
}
public static void BeginTransaction()
{
Transaction = Connection.BeginTransaction();
}