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 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);
}
}
}
/// <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();
}
}
}
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 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 CreateProduct(Product newProduct)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbProduct(PCode,PBarCode,PName,CategoryCode,UCode,Status)");
sb.Append(" VALUES (@PCode,@PBarCode,@PName,@CategoryCode,@UCode,@Status)");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@PCode", OleDbType.VarChar).Value = newProduct.PCode;
com.Parameters.Add("@PBarCode", OleDbType.VarChar).Value = newProduct.PBarCode;
com.Parameters.Add("@PName", OleDbType.VarChar).Value = newProduct.PName;
com.Parameters.Add("@CategoryCode", OleDbType.VarChar).Value = newProduct.CategoryCode;
com.Parameters.Add("@UCode", OleDbType.VarChar).Value = newProduct.UCode;
com.Parameters.Add("@Status", OleDbType.VarChar).Value = newProduct.Status;
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public int CreateProductOnWareHouse(ProductOnWareHouse newProductOnWareHouse)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbProductOnWareHouse(PCode,WHCode,UCode,QtyWithUnit,RealQty)");
sb.Append(" VALUES (@PCode,@WHCode,@UCode,@QtyWithUnit,@RealQty )");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@PCode", OleDbType.VarChar).Value = newProductOnWareHouse.PCode;
com.Parameters.Add("@WHCode", OleDbType.VarChar).Value = newProductOnWareHouse.WHCode;
com.Parameters.Add("@UCode", OleDbType.VarChar).Value = newProductOnWareHouse.UCode;
com.Parameters.Add("@QtyWithUnit", OleDbType.VarChar).Value = newProductOnWareHouse.QtyWithUnit;
com.Parameters.Add("@RealQty", OleDbType.VarChar).Value = newProductOnWareHouse.RealQty;
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public int CreateProductOnSupiler(ProductOnSupiler newProductOnSupiler)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbProductOnSupiler(PCode,SPCode,Cost,RecordDate)");
sb.Append(" VALUES (@PCode,@SPCode,@Cost,@RecordDate)");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@PCode", OleDbType.VarChar).Value = newProductOnSupiler.PCode;
com.Parameters.Add("@SPCode", OleDbType.VarChar).Value = newProductOnSupiler.SPCode;
com.Parameters.Add("@Cost", OleDbType.VarChar).Value = newProductOnSupiler.Cost;
string dateRecord = String.Format("{0:dd/MM/yyyy}", newProductOnSupiler.RecordDate);
com.Parameters.Add("@RecordDate", OleDbType.Date).Value = dateRecord;
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public int CreateUnit(Unit newUnit)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbUnit(UCode,UName,QtyUnit)");
sb.Append(" VALUES (@UCode,@UName,@QtyUnit)");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@UCode", OleDbType.VarChar).Value = newUnit.UCode;
com.Parameters.Add("@UName", OleDbType.VarChar).Value = newUnit.UName;
com.Parameters.Add("@QtyUnit", OleDbType.BigInt).Value = newUnit.QtyUnit;
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
private void buttonCommit_Click(object sender, EventArgs e)
{
try
{
trnNew.Commit();
MessageBox.Show("Committed");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
//trnNew.Rollback();
}
finally
{
this.oleDbConnection.Close();
}
}
public int CreateWareHouse(WareHouse newWareHouse)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbWareHouse(WHCode,WHName,WHLocation)");
sb.Append(" VALUES (@WHCode,@WHName,@WHLocation)");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@WHCode", OleDbType.VarChar).Value = newWareHouse.WHCode;
com.Parameters.Add("@WHName", OleDbType.VarChar).Value = newWareHouse.WHName;
com.Parameters.Add("@WHLocation", OleDbType.VarChar).Value = newWareHouse.WHLocation;
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public int CreateStUser(StUser newStUser)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbUser(SUsername,SPassword,SPriority)");
sb.Append(" VALUES ('"+newStUser.Username+"','"+newStUser.Password +"','"+newStUser.Priority+"')");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
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);
}
//执行SQL 无返回
public static void AExecuteNonQuerySql(String sql)
{
try
{
Console.WriteLine(sql);
conn = new OleDbConnection(constrA);
conn.Open();
OTrans = conn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand(sql, conn, OTrans);
cmd.ExecuteNonQuery();
OTrans.Commit();
}
catch (Exception e1)
{
OTrans.Rollback();
MessageBox.Show("" + e1, "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
finally
{
conn.Close();
}
}
public int CreateRent(Rent newRent,string _whCode)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbRent(RentCode,PRCode,PCompany,PAddress,PPhone,PObjective,DateRent,DateReturn)");
sb.Append(" VALUES (@RentCode,@PRCode,@PCompany,@PAddress,@PPhone,@PObjective,@DateRent,@DateReturn)");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@RentCode", OleDbType.VarChar).Value = newRent.RentCode;
com.Parameters.Add("@PRCode", OleDbType.VarChar).Value = newRent.PRCode;
com.Parameters.Add("@PCompany", OleDbType.VarChar).Value = newRent.PCompany;
com.Parameters.Add("@PAddress", OleDbType.VarChar).Value = newRent.PAddress;
com.Parameters.Add("@PPhone", OleDbType.VarChar).Value = newRent.PPhone;
com.Parameters.Add("@PObjective", OleDbType.VarChar).Value = newRent.PObjective;
string rentDate = String.Format("{0:dd/MM/yyyy}", newRent.DateRent);
string returnDate = String.Format("{0:dd/MM/yyyy}", newRent.DateReturn);
com.Parameters.Add("@DateRent", OleDbType.Date).Value = rentDate;
com.Parameters.Add("@DateReturn", OleDbType.Date).Value = returnDate;
com.ExecuteNonQuery();
foreach (RentDetail r in newRent.rentDetails)
{
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbRentDetail(RentCode,PCode,PName,UCode,UName,NumberRent,NumberReturn,Physical,Penalty)");
sb.Append(" VALUES (@RentCode,@PCode,@PName,@UCode,@UName,@NumberRent,@NumberReturn,@Physical,@Penalty)");
string sqlSaveDetail;
sqlSaveDetail = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSaveDetail;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@RentCode", OleDbType.VarChar).Value = r.RentCode;
com.Parameters.Add("@PCode", OleDbType.VarChar).Value = r.PCode;
com.Parameters.Add("@PName", OleDbType.VarChar).Value = r.PName;
com.Parameters.Add("@UCode", OleDbType.VarChar).Value = r.UCode;
com.Parameters.Add("@UName", OleDbType.VarChar).Value = r.UName;
com.Parameters.Add("@NumberRent", OleDbType.VarChar).Value = r.NumberRent;
com.Parameters.Add("@NumberReturn", OleDbType.VarChar).Value = "0";
com.Parameters.Add("@Physical", OleDbType.VarChar).Value = "";
com.Parameters.Add("@Penalty", OleDbType.VarChar).Value = "0";
com.ExecuteNonQuery();
ProductOnWareHouse pOnwarehouse = serviceProductOnWarhose.getByPCodeAndWHCode(r.PCode, _whCode);
if (pOnwarehouse != null) {
Product p = serviceProduct.getByCode(r.PCode);
pOnwarehouse.UCode = p.UCode;
Unit u = serviceUnit.getByCode(p.UCode);
pOnwarehouse.QtyWithUnit = u.QtyUnit;
int oldblance = pOnwarehouse.RealQty;
int newbalnce = oldblance - r.NumberRent;
pOnwarehouse.RealQty = newbalnce;
int tmp = serviceProductOnWarhose.UpdateProductOnWareHouse(pOnwarehouse);
if (tmp > 0)
{
Console.WriteLine("update pOnwarehouse Complate");
}
}
}
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public Boolean UpdateTable(string pTable, DataRow pRow, string pListColumn, object[] pValueKey)
{
Boolean flag = false;
string Parameter = "";
string sql;
int i, count;
string[] ListColumn = pListColumn.Split(';');
if (pRow != null)
{
count = pRow.Table.Columns.Count;
for (i = 0; i < count - 1; i++)
{
Parameter = Parameter + "[" + pRow.Table.Columns[i].ColumnName + "] = ?,";
}
Parameter = Parameter + pRow.Table.Columns[i].ColumnName + " = ?";
string sWhere = "";
for (i = 0; i < ListColumn.Length; i++)
{
sWhere += "[" + ListColumn[i] + "] = ? and ";
}
sWhere = sWhere.Substring(0, sWhere.Length - 4);
sql = "Update " + pTable + " Set " + Parameter + " Where " + sWhere;
try
{
OpenConnect();
glbTransaction = glbConnection.BeginTransaction();
glbCommand.Transaction = glbTransaction;
glbCommand.Connection = glbConnection;
glbCommand.CommandType = CommandType.Text;
glbCommand.CommandText = sql;
glbCommand.Parameters.Clear();
for (i = 0; i < count; i++)
{
glbCommand.Parameters.AddWithValue("@Par" + i.ToString(), pRow[i]);
}
for (i = 0; i < pValueKey.Length; i++)
{
glbCommand.Parameters.AddWithValue("@ValueKey0" + i.ToString(), pValueKey[i]);
}
glbCommand.ExecuteNonQuery();
glbTransaction.Commit();
CloseConnect();
flag = true;
}
catch
{
glbTransaction.Rollback();
CloseConnect();
}
}
return flag;
}
public int CreateSale(Sale newSale, string _whCode)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbSale(SaleCode,CCode,PQutation,DateSale,SaleBy,NetDc,NetVat,NetTotal)");
sb.Append(" VALUES (@SaleCode,@CCode,@PQutation,@DateSale,@SaleBy,@NetDc,@NetVat,@NetTotal)");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@SaleCode", OleDbType.VarChar).Value = newSale.SaleCode;
com.Parameters.Add("@CCode", OleDbType.VarChar).Value = newSale.CustomerForSale.CCode;
com.Parameters.Add("@PQutation", OleDbType.VarChar).Value = newSale.PQutation;
string dateSale = String.Format("{0:dd/MM/yyyy}", newSale.DateSale);
com.Parameters.Add("@DateSale", OleDbType.Date).Value = dateSale;
com.Parameters.Add("@SaleBy", OleDbType.VarChar).Value = newSale.SaleBy;
com.Parameters.Add("@NetDc", OleDbType.VarChar).Value = newSale.NetDc;
com.Parameters.Add("@NetVat", OleDbType.VarChar).Value = newSale.NetVat;
com.Parameters.Add("@NetTotal", OleDbType.VarChar).Value = newSale.NetTotal;
com.ExecuteNonQuery();
foreach (SaleDetail r in newSale.saleDetails)
{
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbSaleDetail(SaleCode,PCode,PName,UCode,UName,Amount,SalePrice,NetTotal)");
sb.Append(" VALUES (@SaleCode,@PCode,@PName,@UCode,@UName,@Amount,@SalePrice,@NetTotal)");
string sqlSaveDetail;
sqlSaveDetail = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSaveDetail;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@SaleCode", OleDbType.VarChar).Value = r.SaleCode;
com.Parameters.Add("@PCode", OleDbType.VarChar).Value = r.PCode;
com.Parameters.Add("@PName", OleDbType.VarChar).Value = r.PName;
com.Parameters.Add("@UCode", OleDbType.VarChar).Value = r.UCode;
com.Parameters.Add("@UName", OleDbType.VarChar).Value = r.UName;
com.Parameters.Add("@Amount", OleDbType.VarChar).Value = r.Amount;
com.Parameters.Add("@SalePrice", OleDbType.VarChar).Value = r.SalePrice;
com.Parameters.Add("@NetTotal", OleDbType.VarChar).Value = r.NetTotal;
com.ExecuteNonQuery();
ProductOnWareHouse pOnwarehouse = serviceProductOnWarhose.getByPCodeAndWHCode(r.PCode, _whCode);
if (pOnwarehouse != null)
{
Product p = serviceProduct.getByCode(r.PCode);
pOnwarehouse.UCode = p.UCode;
Unit u = serviceUnit.getByCode(p.UCode);
pOnwarehouse.QtyWithUnit = u.QtyUnit;
int oldblance = pOnwarehouse.RealQty;
int newbalnce = oldblance - r.Amount;
pOnwarehouse.RealQty = newbalnce;
int tmp = serviceProductOnWarhose.UpdateProductOnWareHouse(pOnwarehouse);
if (tmp > 0)
{
Console.WriteLine("update pOnwarehouse Complate");
}
}
}
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public Boolean InsertTable(string pTable, DataRow pRow, string pIdentityColumn)
{
Boolean flag = false;
string mColumn = "";
string Parameter = "";
string sql;
int i, count;
if (pRow != null)
{
count = pRow.Table.Columns.Count;
for (i = 0; i < count - 1; i++)
{
if (pIdentityColumn.ToUpper() != pRow.Table.Columns[i].ColumnName.ToUpper())
{
mColumn = mColumn + "[" + pRow.Table.Columns[i].ColumnName + "],";
Parameter = Parameter + "?,";
}
}
mColumn = mColumn + "[" + pRow.Table.Columns[i].ColumnName + "]";
Parameter = Parameter + "?";
sql = "Insert into " + pTable + " (" + mColumn + ") values (" + Parameter + ")";
try
{
OpenConnect();
glbTransaction = glbConnection.BeginTransaction();
glbCommand.Transaction = glbTransaction;
glbCommand.Connection = glbConnection;
glbCommand.CommandType = CommandType.Text;
glbCommand.CommandText = sql;
glbCommand.Parameters.Clear();
for (i = 0; i < count; i++)
{
if (pIdentityColumn.ToUpper() != pRow.Table.Columns[i].ColumnName.ToUpper())
{
glbCommand.Parameters.AddWithValue("@Par" + i.ToString(), pRow[i]);
}
}
glbCommand.ExecuteNonQuery();
glbTransaction.Commit();
CloseConnect();
flag = true;
}
catch
{
glbTransaction.Rollback();
CloseConnect();
}
}
return flag;
}
public Boolean UpdateTable(string pTable, DataRow pRow, string pColumn, object pValueKey, bool pIsUpdateKey)
{
Boolean flag = false;
string Parameter = "";
string sql;
int i, count;
if (pRow != null)
{
count = pRow.Table.Columns.Count;
for (i = 0; i < count - 1; i++)
{
if (pIsUpdateKey == true)
{
Parameter = Parameter + "[" + pRow.Table.Columns[i].ColumnName + "] = ?,";
}
else
{
if (pColumn.ToUpper() != pRow.Table.Columns[i].ColumnName.ToUpper())
{
Parameter = Parameter + "[" + pRow.Table.Columns[i].ColumnName + "] = ?,";
}
}
}
Parameter = Parameter + pRow.Table.Columns[i].ColumnName + " = ?";
sql = "Update " + pTable + " Set " + Parameter + " Where [" + pColumn + "] = ?";
try
{
OpenConnect();
glbTransaction = glbConnection.BeginTransaction();
glbCommand.Transaction = glbTransaction;
glbCommand.Connection = glbConnection;
glbCommand.CommandType = CommandType.Text;
glbCommand.CommandText = sql;
glbCommand.Parameters.Clear();
for (i = 0; i < count; i++)
{
if (pIsUpdateKey == true)
{
glbCommand.Parameters.AddWithValue("@Par" + i.ToString(), pRow[i]);
}
else
{
if (pColumn.ToUpper() != pRow.Table.Columns[i].ColumnName.ToUpper())
{
glbCommand.Parameters.AddWithValue("@Par" + i.ToString(), pRow[i]);
}
}
}
glbCommand.Parameters.AddWithValue("@ValueKey", pValueKey);
glbCommand.ExecuteNonQuery();
glbTransaction.Commit();
CloseConnect();
flag = true;
}
catch
{
glbTransaction.Rollback();
CloseConnect();
}
}
return flag;
}
public bool CreateQuestion(Question _question)
{
bool result = false;
try
{
Conn = db.openConn();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbQuestion(ProgramID,Subject,Created,MemberType,UserName)");
sb.Append(" VALUES (");
sb.Append(" '" + _question.ProgramID + "',");
sb.Append(" '" + _question .Subject + "',");
sb.Append(" now(),");
sb.Append(" '" + _question.MemberType + "',");
sb.Append(" '" + _question.UserName + "')");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
/// <summary>
/// 执行SQL返回表第一行第一列
/// </summary>
/// <param name="sql">SQL语句</param>
public static object ExecuteScalarSql(String sql)
{
try
{
Console.WriteLine(sql);
conn = new OleDbConnection(constr);
conn.Open();
OTrans = conn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand(sql, conn, OTrans);
object ores = cmd.ExecuteScalar();
OTrans.Commit();
return ores;
}
catch (Exception ex)
{
OTrans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}
public static bool ImportReportsFromDB(string fileName)
{
if (File.Exists(fileName))// Open the input file for input
{
System.Data.OleDb.OleDbConnection connImport = new System.Data.OleDb.OleDbConnection(VWA4Common.VWACommon.GetConnectionString(fileName));
System.Data.OleDb.OleDbConnection connTransaction = new System.Data.OleDb.OleDbConnection(VWA4Common.AppContext.WasteConnectionString);
connTransaction.Open();
System.Data.OleDb.OleDbTransaction transaction = connTransaction.BeginTransaction();
try
{
Hashtable OldToNewReports = new Hashtable();
// import all reports
DataTable dtReports = VWA4Common.DB.Retrieve("SELECT * FROM ReportMemorized", connImport, null);
if (dtReports.Rows.Count > 0)
{
foreach (DataRow rowReport in dtReports.Rows)
{
string date_time = VWA4Common.VWACommon.DateToString(DateTime.Now);
string oldReportID = rowReport["ID"].ToString();
string title = rowReport["Title"].ToString();
if (VWA4Common.DB.Retrieve("SELECT * FROM ReportMemorized WHERE Title = '" + title + "'").Rows.Count > 0)
{
title = "Imported " + date_time + " " + title;
}
string newReportID = VWA4Common.DB.Insert("INSERT INTO ReportMemorized(Title, ReportType, ConfigXML, CreatedDate, ModifiedDate) " +
" VALUES('" + title + "','" + rowReport["ReportType"].ToString() + "','" + rowReport["ConfigXML"].ToString() + "', #" +
date_time + "#, #" + date_time + "#)", connTransaction, transaction).ToString();
OldToNewReports[oldReportID] = newReportID;
//System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
//cmd.CommandText = "INSERT INTO ReportMemorized (ConfigXML, ReportType, Title, CreatedDate, ModifiedDate) " +
// "VALUES(@ConfigXML, @ReportType, @Title, @CreatedDate, @ModifiedDate)";
//cmd.Parameters.Add("@ConfigXML", OleDbType.Binary);
//cmd.Parameters.Add("@ReportType", OleDbType.VarChar, 50, "ReportType");
//cmd.Parameters.Add("@Title", OleDbType.VarChar, 255, "Title");
//cmd.Parameters.Add("@CreatedDate", OleDbType.Date, 50, "CreatedDate");
//cmd.Parameters.Add("@ModifiedDate", OleDbType.Date, 50, "ModifiedDate");
//cmd.Parameters["@ConfigXML"].Value = System.Text.Encoding.UTF8.GetBytes(arr);
//cmd.Parameters["@ReportType"].Value = reportType;
//cmd.Parameters["@Title"].Value = name;
//cmd.Parameters["@CreatedDate"].Value = DateTime.Now;
//cmd.Parameters["@ModifiedDate"].Value = DateTime.Now;
//cmd.Connection = conn;
//if (cmd.ExecuteNonQuery() <= 0)
// MessageBox.Show(null, "Error saving report - report was not saved", "Error saving report", MessageBoxButtons.OK, MessageBoxIcon.Error);
//if (isNew)
// cmd.CommandText = "SELECT @@Identity";
//else
// cmd.CommandText = "SELECT ID FROM ReportMemorized WHERE ReportMemorized.Title = '" + name + "'";
//id = (int)cmd.ExecuteScalar();
// import all report parameters
DataTable dtReportParameters = VWA4Common.DB.Retrieve("SELECT * FROM ReportParam WHERE ReportMemorized = " + oldReportID, connImport, null);
if (dtReportParameters.Rows.Count > 0)
{
foreach (DataRow rowReportParam in dtReportParameters.Rows)
{
VWA4Common.DB.Insert("INSERT INTO ReportParam(ParamName, ParamValue, ParamDisplayValue, ParamType, " +
"ParamValueType, AssignType, GlobalName, ReportMemorized) " +
" VALUES('" + rowReportParam["ParamName"].ToString().Replace("'", "''") + "', '" +
rowReportParam["ParamValue"].ToString().Replace("'", "''") + "', '" +
rowReportParam["ParamDisplayValue"].ToString().Replace("'", "''") + "', '" +
rowReportParam["ParamType"].ToString().Replace("'", "''") + "', '" +
rowReportParam["ParamValueType"].ToString().Replace("'", "''") + "', '" +
rowReportParam["AssignType"].ToString().Replace("'", "''") + "', '" +
rowReportParam["GlobalName"].ToString().Replace("'", "''") + "', " +
newReportID + ")",
connTransaction, transaction).ToString();
}
}
}
}
// import all series
DataTable dtSeries = VWA4Common.DB.Retrieve("SELECT * FROM ReportSeries", connImport, null);
if (dtSeries.Rows.Count > 0)
{
foreach (DataRow rowSerie in dtSeries.Rows)
{
string date_time = VWA4Common.VWACommon.DateToString(DateTime.Now);
string oldSerieID = rowSerie["ID"].ToString();
// Mila todo: what to do with SiteID?
string newSerieID = VWA4Common.DB.Insert("INSERT INTO ReportSeries(SerieName, SiteID, CreatedDate, ModifiedDate) " +
" VALUES('" + rowSerie["SerieName"].ToString() + "', " + rowSerie["SiteID"].ToString() + ", #" +
date_time + "#, #" + date_time + "#)",
connTransaction, transaction).ToString();
// import all report sets
DataTable dtReportSets = VWA4Common.DB.Retrieve("SELECT * FROM ReportSet WHERE SerieID = " + oldSerieID, connImport, null);
if (dtReportSets.Rows.Count > 0)
{
foreach (DataRow rowReportSet in dtReportSets.Rows)
{
string oldReportSetID = rowSerie["ID"].ToString();
string newReportSetID = VWA4Common.DB.Insert("INSERT INTO ReportSet(ReportMemorized, [Order], Expression, SerieID) " +
" VALUES(" + OldToNewReports[rowReportSet["ReportMemorized"].ToString()] + ", " +
rowReportSet["Order"] + ", '" + rowReportSet["Expression"].ToString().Replace("'", "''") + "', " + rowReportSet["SerieID"] + ")",
connTransaction, transaction).ToString();
}
}
}
}
transaction.Commit();
return(true);
}
catch (Exception ex)
{
MessageBox.Show("Error occured during importing Reports with message : " + ex.Message, "VWA Import Reports Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
transaction.Rollback();
}
finally
{
if (connTransaction != null && connTransaction.State != ConnectionState.Closed)
{
connTransaction.Close();
}
}
}
return(false);
}
public int UpdateSupiler(Supiler updateSupiler)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" UPDATE tbSupiler ");
sb.Append(" SET SPName='" + updateSupiler.SPName +"',");
sb.Append(" Address='" + updateSupiler.Address + "',");
sb.Append(" PersonContact='" + updateSupiler.PersonContact + "',");
sb.Append(" Phone='" + updateSupiler.Phone + "',");
sb.Append(" Fax='" + updateSupiler.Fax + "'");
sb.Append(" WHERE (SPCode='" + updateSupiler.SPCode + "')");
string sqlUpdate;
sqlUpdate = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlUpdate;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public static void CommitTransaction()
{
Transaction.Commit();
Transaction = null;
}
public int UpdateUnit(Unit updateUnit)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" UPDATE tbUnit ");
sb.Append(" SET UName='" + updateUnit.UName + "',QtyUnit='" + updateUnit.QtyUnit + "'");
sb.Append(" WHERE (UCode='" + updateUnit.UCode + "')");
string sqlUpdate;
sqlUpdate = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlUpdate;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public void ExecuteBatch(ArrayList sqlQueryList)
{
string[] queryParts = null;
string[] separator = { "GO", "Go", "gO", "go" };
try
{
Connect();
foreach (string query in sqlQueryList)
{
queryParts = query.Split(separator, 2, StringSplitOptions.RemoveEmptyEntries);
_dbTransacao = _dbConexao.BeginTransaction();
try
{
foreach (string queryPart in queryParts)
{
_dbComando.Transaction = _dbTransacao;
_dbComando.CommandText = queryPart;
Execute(query);
}
_dbTransacao.Commit();
}
catch (Exception e) //debug
{
_dbTransacao.Rollback();
}
}
Disconnect();
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 执行SQL 无返回
/// </summary>
/// <param name="sql">SQL语句</param>
public static void ExecuteNonQuerySql(String sql)
{
try
{
Console.WriteLine(sql);
conn = new OleDbConnection(constr);
conn.Open();
OTrans = conn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand(sql, conn, OTrans);
cmd.ExecuteNonQuery();
OTrans.Commit();
}
catch (Exception ex)
{
OTrans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}
public bool CreateViewWebSite(ViewWebSite _view)
{
bool result = false;
try
{
Conn = db.openConn();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbView(IP,UserName,Page,VDate)");
sb.Append(" VALUES (");
sb.Append(" '" + _view.IP + "',");
sb.Append(" '" + _view.User + "',");
sb.Append(" '" + _view.Page + "',");
sb.Append(" now() )");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public int UpdateRent(IList<RentDetail> updateRentDetails,string _whCode)
{
int result = -1;
try
{
Conn = db.openConnAccess();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
foreach (RentDetail r in updateRentDetails) {
sb.Remove(0, sb.Length);
sb.Append(" UPDATE tbRentDetail ");
sb.Append(" SET NumberReturn='" + r.NumberReturn + "',");
sb.Append(" Physical='" + r.Physical + "',");
sb.Append(" Penalty='" + r.Penalty + "'");
sb.Append(" WHERE (RentCode='" + r.RentCode + "')");
sb.Append(" AND (PCode='" + r.PCode + "')");
string sqlUpdate;
sqlUpdate = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlUpdate;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
ProductOnWareHouse pOnwarehouse = serviceProductOnWarhose.getByPCodeAndWHCode(r.PCode, _whCode);
if (pOnwarehouse != null)
{
Product p = serviceProduct.getByCode(r.PCode);
pOnwarehouse.UCode = p.UCode;
Unit u = serviceUnit.getByCode(p.UCode);
pOnwarehouse.QtyWithUnit = u.QtyUnit;
int oldblance = pOnwarehouse.RealQty;
int newbalnce = oldblance + r.NumberRent;
pOnwarehouse.RealQty = newbalnce;
int tmp = serviceProductOnWarhose.UpdateProductOnWareHouse(pOnwarehouse);
if (tmp > 0)
{
Console.WriteLine("update pOnwarehouse Complate");
}
}
}
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
public Boolean DeleteTable(string pTable, string pColumn, object pValueKey, ref string ErrorCode)
{
Boolean flag = false;
string sql;
try
{
sql = "Delete From [" + pTable + "] where [" + pColumn + "] = ?";
OpenConnect();
glbTransaction = glbConnection.BeginTransaction();
glbCommand.Transaction = glbTransaction;
glbCommand.Connection = glbConnection;
glbCommand.CommandType = CommandType.Text;
glbCommand.CommandText = sql;
glbCommand.Parameters.Clear();
glbCommand.Parameters.AddWithValue("@Par01", pValueKey);
glbCommand.ExecuteNonQuery();
glbTransaction.Commit();
CloseConnect();
flag = true;
}
catch (OleDbException ex)
{
ErrorCode = ex.ErrorCode.ToString();
glbTransaction.Rollback();
CloseConnect();
}
return flag;
}
public bool CreateMember(Member _member)
{
bool result = false;
try
{
Conn = db.openConn();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbMember(UserName,PasswordMember,Email,MemberType)");
sb.Append(" VALUES (");
sb.Append(" '" + _member.UserName + "',");
sb.Append(" '" + _member.Password + "',");
sb.Append(" '" + _member.Email + "',");
sb.Append(" '" + _member.MemmerType + "')");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally {
Conn.Close();
}
return result;
}
public Boolean DeleteTable(string pTable, string pListColumn, object[] pValues, ref string ErrorCode)
{
Boolean flag = false;
string sql = "";
try
{
string[] ListColumn = pListColumn.Split(';');
for (int i = 0; i < pValues.Length; i++)
{
sql += "[" + ListColumn[i] + "] = ? and ";
}
sql = sql.Substring(0, sql.Length - 4);
sql = "Delete From [" + pTable + "] where " + sql;
OpenConnect();
glbTransaction = glbConnection.BeginTransaction();
glbCommand.Transaction = glbTransaction;
glbCommand.Connection = glbConnection;
glbCommand.CommandType = CommandType.Text;
glbCommand.CommandText = sql;
glbCommand.Parameters.Clear();
for (int i = 0; i < pValues.Length; i++)
{
glbCommand.Parameters.AddWithValue("@Par0" + i.ToString(), pValues[i]);
}
glbCommand.ExecuteNonQuery();
glbTransaction.Commit();
CloseConnect();
flag = true;
}
catch (OleDbException ex)
{
ErrorCode = ex.ErrorCode.ToString();
glbTransaction.Rollback();
CloseConnect();
}
return flag;
}
public bool CreateQuestionDetail(QuestionDetail _questionDetail)
{
bool result = false;
try
{
Conn = db.openConn();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbQuestionDetail(QID,AnsQus,AnsDate,AnsBy)");
sb.Append(" VALUES (");
sb.Append(" '" + _questionDetail.QID + "',");
sb.Append(" '" + _questionDetail.AnsQus + "',");
sb.Append(" now(),");
sb.Append(" '" + _questionDetail.AnsBy + "')");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
/// <summary>
/// 执行存储过程返回参数集合,只输入存储过程名即可不能带EXEC
/// </summary>
/// <param name="sql">存储过程名</param>
/// <param name="pars">参数数组</param>
/// <returns>存储过程参数集合</returns>
public static OleDbParameterCollection ExecuteScalarProcedure(String sql, OleDbParameter[] pars)
{
try
{
Console.WriteLine(sql);
conn = new OleDbConnection(constr);
conn.Open();
OTrans = conn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand(sql, conn, OTrans);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(pars);
cmd.ExecuteNonQuery();
OTrans.Commit();
return cmd.Parameters;
}
catch (Exception ex)
{
OTrans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}
public bool CreateComment(Comment _comment)
{
bool result = false;
try
{
Conn = db.openConn();
tr = Conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbComment(UserType,IP,Subject,CommentDecription)");
sb.Append(" VALUES (");
sb.Append(" '" + _comment.UserType + "',");
sb.Append(" '" + _comment.IP + "',");
sb.Append(" '" + _comment.Subject + "',");
sb.Append(" '" + _comment.CommentDecription + "')");
string sqlSave;
sqlSave = sb.ToString();
com = new OleDbCommand();
com.Connection = Conn;
com.CommandText = sqlSave;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
return result;
throw ex;
}
finally
{
Conn.Close();
}
return result;
}
/// <summary>
/// 执行SQL返回OleDbDataReader
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>返回OleDbDataReader</returns>
public static OleDbDataReader DrFillSql(String sql)
{
try
{
Console.WriteLine(sql);
conn = new OleDbConnection(constr);
conn.Open();
OTrans = conn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand(sql, conn, OTrans);
OleDbDataReader dr = cmd.ExecuteReader();
OTrans.Commit();
return dr;
}
catch (Exception ex)
{
OTrans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}
