public void Begin(IsolationLevel level)
{
if (_transaction != null)
{
_transaction.Rollback();
}
_transaction = ((SqlCeConnection)_connection).BeginTransaction(level);
}
public void addColumn()
{
if (!columnExists(table, column))
{
SqlCeConnection conn = new SqlCeConnection(Opcije.ConnectionString);
string errMsg = "Neuspesna promena baze.";
SqlCeTransaction tr = null;
try
{
conn.Open();
tr = conn.BeginTransaction();
addColumnCommand.Connection = conn;
addColumnCommand.Transaction = tr;
addColumnCommand.ExecuteNonQuery();
updateColumnCommand.Connection = conn;
updateColumnCommand.Transaction = tr;
updateColumnCommand.ExecuteNonQuery();
tr.Commit();
}
catch (SqlCeException e)
{
// in Open()
if (tr != null)
{
tr.Rollback(); // TODO: this can throw Exception and InvalidOperationException
}
throw new DatabaseException(errMsg, e);
}
catch (InvalidOperationException e)
{
// in ExecuteNonQuery(), ExecureScalar()
if (tr != null)
{
tr.Rollback();
}
throw new DatabaseException(errMsg, e);
}
// za svaki slucaj
catch (Exception)
{
if (tr != null)
{
tr.Rollback();
}
throw;
}
finally
{
conn.Close();
}
}
}
public static void updateDatabaseVersionNumber(int newVersion)
{
string tableName = "verzija_baze";
string sql =
String.Format("UPDATE {0} SET broj_verzije = {1} WHERE verzija_id = 1", tableName, newVersion);
SqlCeCommand cmd = new SqlCeCommand(sql);
SqlCeConnection conn = new SqlCeConnection(ConfigurationParameters.ConnectionString);
string errMsg = "Neuspesna promena baze.";
SqlCeTransaction tr = null;
// TODO: Ovaj kod se ponavlja na vise mesta u fajlu.
try
{
conn.Open();
tr = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = tr;
int result = cmd.ExecuteNonQuery();
tr.Commit();
}
catch (SqlCeException e)
{
// in Open()
if (tr != null)
{
tr.Rollback(); // TODO: this can throw Exception and InvalidOperationException
}
throw new InfrastructureException(errMsg, e);
}
catch (InvalidOperationException e)
{
// in ExecuteNonQuery(), ExecureScalar()
if (tr != null)
{
tr.Rollback();
}
throw new InfrastructureException(errMsg, e);
}
// za svaki slucaj
catch (Exception)
{
if (tr != null)
{
tr.Rollback();
}
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Execute database structure creating script.
/// </summary>
/// <param name="sqlCmd">SQL command.</param>
/// <param name="script">Script text.</param>
private void _ExecuteScript(SqlCeCommand sqlCmd, string script)
{
Debug.Assert(sqlCmd != null);
Debug.Assert(script != null);
SqlCeConnection conn = sqlCmd.Connection;
string[] commands = Regex.Split(script, CMD_DELIMITER);
SqlCeTransaction trans = conn.BeginTransaction();
try
{
foreach (string cmdStr in commands)
{
// If command exists - execute it.
if (cmdStr.Trim().Length > 0)
{
sqlCmd.CommandText = cmdStr;
sqlCmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
//сохраняет лог
public void SaveLog(ref ScanLog slog)
{
string insertCommandText = @" INSERT INTO ScanLog(ArtCode, id_gamma, Dt, Qty, Barcode, ActionType)
VALUES(@artcode, @id_gamma, @Dt, @Qty, @Barcode, @ActionType) ";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
SqlCeTransaction tx = connect.BeginTransaction();
SqlCeCommand command = connect.CreateCommand();
command.CommandText = insertCommandText;
command.Parameters.Add("artcode", SqlDbType.Int).Value = slog.ArtCode;
command.Parameters.Add("id_gamma", SqlDbType.Int).Value = GlobalArea.CurrentEmployee.GammaID;
command.Parameters.Add("Dt", SqlDbType.NVarChar).Value = GlobalArea.CurrentDateSQLStr;
command.Parameters.Add("Qty", SqlDbType.Int).Value = slog.Qty;
command.Parameters.Add("Barcode", SqlDbType.NVarChar).Value = slog.Barcode;
command.Parameters.Add("ActionType", SqlDbType.Int).Value = (int)slog.ActType;
command.Transaction = tx;
try
{
int rowsAffected = command.ExecuteNonQuery();
tx.Commit();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
tx.Rollback();
}
}
}
protected override void AfterEachExample()
{
transaction.Rollback();
transaction.Dispose();
dbConnection.Close();
dbConnection.Dispose();
}
/// <summary>
/// Upgrades the database to the current version.
/// </summary>
/// <param name="user">The user.</param>
/// <param name="currentVersion">The current version of the database.</param>
/// <returns>
/// [true] if success.
/// </returns>
/// <remarks>
/// Documented by AAB, 30.04.2009.
/// </remarks>
internal static bool UpgradeDatabase(IUser user, Version currentVersion)
{
using (SqlCeConnection connection = GetConnection(user))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
SqlCeTransaction upgradeTransaction = connection.BeginTransaction();
try
{
if (currentVersion == new Version(1, 0, 0))
{
UpgradeDatabase_100_101(connection);
currentVersion = new Version(1, 0, 1);
}
if (currentVersion == new Version(1, 0, 1))
{
UpgradeDatabase_101_102(connection);
currentVersion = new Version(1, 0, 2);
}
ApplyIndicesToDatabase(connection);
upgradeTransaction.Commit(CommitMode.Immediate);
return(true);
}
catch (Exception)
{
upgradeTransaction.Rollback();
}
}
return(false);
}
protected override int SaveImpl(string commandString, params T[] models)
{
int count = 0;
using (SqlCeConnection connection = (SqlCeConnection)GetConnection())
{
connection.Open();
using (SqlCeTransaction transaction = connection.BeginTransaction())
using (SqlCeCommand command = new SqlCeCommand("", connection, transaction))
{
try
{
models.ForEach(m =>
{
SaveModel(command, m);
count++;
});
transaction.Commit();
}
catch
{
if (transaction != null && connection.State != System.Data.ConnectionState.Closed)
{
count = 0;
transaction.Rollback();
}
throw;
}
}
}
return(count);
}
public bool Action(int id)
{
bool ret = false;
SqlCeTransaction tx = typeorder.DBase.Connection.BeginTransaction();
typeorder.DBase.Transaction = tx;
try
{
// Удаляем все поля
pole.DBase.CommandText = "DELETE " + pole.TableName + " Where IdTypeOrder=@IdTypeOrder;\r\n";
pole.DBase.Parameters["IdTypeOrder"].Value = id;
int i = pole.DBase.ExecuteNonQuery();
// Удаляем запись типа заказа
typeorder.DBase.CommandText = "DELETE " + typeorder.TableName + " Where Id=@IdType;\r\n";
typeorder.DBase.Parameters["IdType"].Value = id;
i = typeorder.DBase.ExecuteNonQuery();
ret = true;
tx.Commit();
}
catch (Exception e)
{
tx.Rollback();
MessageBox.Show(e.Message, "Внимание!");
}
finally
{
typeorder.DBase.Transaction = null;
}
return(ret);
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>Boolean</returns>
public Boolean ExecuteCommand(ExecuteObject sender)
{
Boolean flag;
using (SqlCeConnection connection = new SqlCeConnection(this.ConnectionString))
{
using (SqlCeCommand command = new SqlCeCommand())
{
CreateCommand(command, sender);
command.Connection = connection;
connection.Open();
using (SqlCeTransaction transaction = command.Connection.BeginTransaction())
{
try
{
command.Transaction = transaction;
int num = command.ExecuteNonQuery();
transaction.Commit();
flag = num >= 0;
}
catch (Exception ex)
{
transaction.Rollback();
throw new ApplicationException(ex.Message);
}
finally { }
}
}
}
return(flag);
}
/// <summary>
/// 添加一条数据
/// </summary>
/// <param name="sql">执行sql</param>
public void addOneData(String sql)
{
if (sql == null || sql == "")
{
return;
}
con.Open();
SqlCeTransaction tx = con.BeginTransaction();
SqlCeCommand cmd = con.CreateCommand();
try
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
tx.Commit();
}
catch (Exception)
{
tx.Rollback();
}
finally
{
cmd.Dispose();
con.Close();
}
}
private void UpdateExistingCompanies(List <Company> toUpdate)
{
SqlCeConnection connection = database_.GetConnection();
using (var cmd = new SqlCeCommand("UPDATE CompanyInfo SET TaxAuthority = @tax WHERE CompanyID = @id", connection))
{
cmd.Parameters.Add("@tax", SqlDbType.NVarChar);
cmd.Parameters.Add("@id", SqlDbType.NVarChar);
SqlCeTransaction transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
try
{
foreach (Company item in toUpdate)
{
cmd.Parameters[0].Value = item.tax;
cmd.Parameters[1].Value = item.id;
cmd.ExecuteNonQuery();
}
transaction.Commit();
textBoxMessage.AppendText("\n成功更新" + toUpdate.Count + "条已有公司信息");
}
catch (Exception ex)
{
transaction.Rollback();
textBoxMessage.AppendText("\n更新已有公司信息失败。详细信息：" + ex.Message);
}
}
}
private void InsertNewCompanies(List <Company> toInsert)
{
SqlCeConnection connection = database_.GetConnection();
using (var cmd = new SqlCeCommand("INSERT INTO CompanyInfo VALUES (?, ?, ?, ?, ?, ?)", connection))
{
cmd.Parameters.Add("@id", SqlDbType.NVarChar);
cmd.Parameters.Add("@name", SqlDbType.NVarChar);
cmd.Parameters.Add("@tax", SqlDbType.NVarChar);
cmd.Parameters.AddWithValue("@union", DBNull.Value);
cmd.Parameters.AddWithValue("@system", DBNull.Value);
cmd.Parameters.AddWithValue("@industry", DBNull.Value);
SqlCeTransaction transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
try
{
foreach (Company item in toInsert)
{
cmd.Parameters[0].Value = item.id;
cmd.Parameters[1].Value = item.name;
cmd.Parameters[2].Value = item.tax;
cmd.ExecuteNonQuery();
}
transaction.Commit();
textBoxMessage.Text = "成功添加" + toInsert.Count + "条新公司信息";
}
catch (Exception ex)
{
transaction.Rollback();
textBoxMessage.Text = "添加新公司信息失败。详细信息：" + ex.Message;
}
}
}
public void SupprimerContenuTable(String table)
{
SqlCeTransaction transaction = connection.BeginTransaction();
SqlCeCommand myCommand_delete = connection.CreateCommand();
myCommand_delete.Transaction = transaction;
try
{
myCommand_delete.CommandText = "DELETE FROM " + table;
myCommand_delete.ExecuteNonQuery();
//myCommand_delete.CommandText = "DBCC CHECKIDENT (" + table + ", reseed, 1)";
//myCommand_delete.ExecuteNonQuery();
//myCommand_delete.CommandText = "ALTER TABLE " + table + " AUTO_INCREMENT=0";
//myCommand_delete.ExecuteNonQuery();
transaction.Commit();
}
catch
{
transaction.Rollback();
}
}
public bool Action(Order objorder)
{
bool ret = false;
SqlCeTransaction tx = order.DBase.Connection.BeginTransaction(); //Добавлять уровень блокировок и транзакции при чтении
order.DBase.Transaction = tx;
//order.DBase.Transaction.
///IsolationLevel.
try
{
// Изменить запись Заказа
order.DBase.CommandText = "UPDATE " + order.TableName + " Set NDock=@NDockOrder, Summ=@SummOrder, Status=@StatusOrder," +
" NameType=@NameTypeInOrder, Perc=@PercOrder " +
" where Id=@IdOrder; \r\n";
order.DBase.Parameters["IdOrder"].Value = objorder.Id;
order.DBase.Parameters["NDockOrder"].Value = objorder.NuberDockument;
order.DBase.Parameters["SummOrder"].Value = objorder.Summ;
order.DBase.Parameters["StatusOrder"].Value = objorder.Status;
order.DBase.Parameters["NameTypeInOrder"].Value = objorder.NameTypeOrder;
order.DBase.Parameters["PercOrder"].Value = objorder.Percent;
int n = order.DBase.ExecuteNonQuery();
// Удалить все старые поля
pole.DBase.CommandText = "DELETE " + pole.TableName + " Where IdOrder=@IdOrderOrderPole;\r\n";
pole.DBase.Parameters["IdOrderOrderPole"].Value = objorder.Id;
n = pole.DBase.ExecuteNonQuery();
// Сохранить новые поля
for (int i = 0; i < objorder.ListPole.Count; i++)
{
objorder.ListPole[i].IdOrder = objorder.Id;
pole.DBase.CommandText = "INSERT INTO " + pole.TableName + " (IdOrder,Number,NamePole,Text)" +
" VALUES (@IdOrderOrderPole,@NumberOrderPole,@NamePoleOrderPole,@TextPoleOrderPole);\r\n";
pole.DBase.Parameters["IdOrderOrderPole"].Value = objorder.ListPole[i].IdOrder;
pole.DBase.Parameters["NumberOrderPole"].Value = objorder.ListPole[i].Number;
pole.DBase.Parameters["NamePoleOrderPole"].Value = objorder.ListPole[i].NamePole;
pole.DBase.Parameters["TextPoleOrderPole"].Value = objorder.ListPole[i].Text;
n = pole.DBase.ExecuteNonQuery();
pole.DBase.CommandText = "SELECT @@IDENTITY;";
int r = Convert.ToInt32(pole.DBase.ExecuteScalar());
objorder.ListPole[i].Id = r;
}
ret = true;
tx.Commit();
}
catch (Exception e)
{
tx.Rollback();
MessageBox.Show(e.Message, "Внимание!");
}
finally
{
order.DBase.Transaction = null;
}
return(ret);
}
public void RecordIsUpdatedWhenUsingCommandTextAndCommandTypeIBeforeRollback()
{
string commandString = "Update Items Set Price = 5000 where ItemID = 2";
SqlCeConnection conn = (SqlCeConnection)db.CreateConnection();
conn.Open();
SqlCeTransaction trans = conn.BeginTransaction();
DataSet dsActualResult = db.ExecuteDataSet(trans, CommandType.Text, commandString);
commandString = "Select ItemDescription, Price from Items order by ItemID";
dsActualResult = db.ExecuteDataSet(trans, CommandType.Text, commandString);
Assert.AreEqual(3, dsActualResult.Tables[0].Rows.Count, "Mismatch in number of rows in the returned dataset. Problem with the test data or with the execute dataset.");
Assert.AreEqual("Digital Image Pro", dsActualResult.Tables[0].Rows[0][0].ToString().Trim());
Assert.AreEqual("38.95", dsActualResult.Tables[0].Rows[0][1].ToString().Trim());
Assert.AreEqual("Excel 2003", dsActualResult.Tables[0].Rows[1][0].ToString().Trim());
Assert.AreEqual("5000", dsActualResult.Tables[0].Rows[1][1].ToString().Trim());
Assert.AreEqual("Infopath", dsActualResult.Tables[0].Rows[2][0].ToString().Trim());
Assert.AreEqual("89", dsActualResult.Tables[0].Rows[2][1].ToString().Trim());
trans.Rollback();
conn.Close();
trans.Dispose();
conn.Dispose();
}
// обновляет orderItem | возвращает количество затронутіх записей
public int UpdateScan(Scan scan)
{
string updateCommandText = @" UPDATE scan SET Qty = @qty, EndDt = @EndDt WHERE ArtCode = @artcode and id_gamma = @id_gamma ";
int rowsAffected = 0;
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
SqlCeTransaction tx = connect.BeginTransaction();
SqlCeCommand command = connect.CreateCommand();
command.CommandText = updateCommandText;
command.Parameters.Add("artcode", SqlDbType.Int).Value = scan.ArtCode;
command.Parameters.Add("id_gamma", SqlDbType.Int).Value = GlobalArea.CurrentEmployee.GammaID;
command.Parameters.Add("qty", SqlDbType.Int).Value = scan.Qty;
command.Parameters.Add("EndDt", SqlDbType.NVarChar).Value = GlobalArea.CurrentDateSQLStr;
command.Transaction = tx;
try
{
rowsAffected = command.ExecuteNonQuery();
tx.Commit();
}
catch (Exception)
{
tx.Rollback();
return(0);
}
}
return(rowsAffected);
}
public void Delete()
{
SqlCeTransaction tx = null;
try
{
using (var conn = new SqlCeConnection(Store.ConnectionString))
{
conn.Open();
string sql = "DELETE FROM " + tableName;
tx = conn.BeginTransaction();
var cmd = new SqlCeCommand(sql, conn);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}
private static void DeleteConfigurationDB()
{
string connectionString = ConfigurationManager.AppSettings.Get("StorageProviderConnection");
string commandText = "Delete tConfiguration";
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
connection.Open();
using (SqlCeTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
SqlCeCommand command = new SqlCeCommand(commandText, connection, transaction);
command.CommandType = CommandType.Text;
command.Prepare();
command.ExecuteNonQuery();
transaction.Commit();
}
catch (SqlCeException exception)
{
transaction.Rollback();
throw exception;
}
finally
{
connection.Close();
}
}
}
}
public void FillBinCode(String aBinCode)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("UPDATE ActivityLine SET BinCode = @aBinCode " +
"WHERE BinCode = N''", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add(new SqlCeParameter("@aBinCode", aBinCode));
sqlCommand.ExecuteNonQuery();
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}
public void InsertDocumentNumbers(XmlResponseGetDocNumbersResult aResultGetDocNumbers)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO WarehouseReceipt(No) " +
"VALUES (@No)", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
SqlCeParameter param = sqlCommand.CreateParameter();
param.ParameterName = "@No";
sqlCommand.Parameters.Add(param);
for (Int32 k = 0; k < aResultGetDocNumbers.Documents.Count; k++)
{
sqlCommand.Parameters["@No"].Value = aResultGetDocNumbers.Documents.Document(k).No;
sqlCommand.ExecuteNonQuery();
}
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}
public int ExecuteSQL(string[] sql)
{
try
{
ConcurrentBag <int> rowCount = new ConcurrentBag <int>();
using (SqlCeTransaction trans = conn.BeginTransaction())
{
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.Transaction = trans;
try
{
foreach (var item in sql)
{
cmd.CommandText = item;
int count = cmd.ExecuteNonQuery();
rowCount.Add(count);
}
trans.Commit();
}
catch
{
trans.Rollback();
}
}
}
return(rowCount.Sum());
}
catch { return(-1); }
}
public override void RollbackTransaction()
{
if (_transaction != null)
{
_transaction.Rollback();
}
GetConnectionBase().EndUse();
}
/// <summary>
/// Rools back the currently active Transaction (if any).
/// </summary>
public void RollbackTran()
{
if ((oConn.State == ConnectionState.Open) && blnTranActive)
{
oTran.Rollback();
blnTranActive = false;
}
}
private bool saveData(SqlCeConnection connection)
{
SqlCeTransaction transaction = null;
try
{
using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandText = "Items";
command.CommandType = CommandType.TableDirect;
using (SqlCeDataAdapter adapter = new SqlCeDataAdapter(command))
{
adapter.InsertCommand = getInsertCommand(connection);
adapter.UpdateCommand = getUpdateCommand(connection);
adapter.DeleteCommand = getDeleteCommand(connection);
adapter.AcceptChangesDuringUpdate = false;
DataTable table = (DataTable)itemsGrid.DataSource;
transaction = connection.BeginTransaction();
adapter.InsertCommand.Transaction = transaction;
adapter.UpdateCommand.Transaction = transaction;
adapter.DeleteCommand.Transaction = transaction;
adapter.Update(table);
transaction.Commit();
table.AcceptChanges();
}
}
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
string message = "An error occurred in saving the data. \n" +
"The error text is as follows:\n" + Global.getExceptionText(ex);
SystemSounds.Exclamation.Play();
Cursor.Current = Cursors.Default;
MessageBox.Show(message, "Error in Saving Data", MessageBoxButtons.OK,
MessageBoxIcon.Error);
ErrorLogger.LogError(ex);
return(false);
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
}
return(true);
}
public bool deleteDocCheckProduct(string _DCode)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" delete from DocCheckProducts where DCode ='"+_DCode+"'");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
sb = new StringBuilder();
sb.Append(" delete from DocCheckProductDetails where DCode ='" + _DCode + "'");
string sqldelete;
sqldelete = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqldelete;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public int TransactionRollback()
{
if (_dbTransaction != null)
{
_dbTransaction.Rollback();
}
_dbTransaction = null;
return(0);
}
public void FillBinContent(XmlResponseGetBinContentResult aXmlResponseResultGetBinContent)
{
using (SqlCeConnection sqlConnection = new SqlCeConnection(MyClass.ConnectionString))
{
sqlConnection.Open();
using (SqlCeTransaction sqlTran = sqlConnection.BeginTransaction())
{
try
{
using (SqlCeCommand sqlCommand = new SqlCeCommand("DELETE FROM BinContent", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
using (SqlCeCommand sqlCommand = new SqlCeCommand("INSERT INTO BinContent (ItemNo, ItemNo2, BinCode, Quantity, [Fixed], [Default]) " +
"VALUES (@ItemNo, @ItemNo2, @BinCode, @Quantity, @Fixed, @Default)", sqlConnection, sqlTran))
{
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add(new SqlCeParameter("@ItemNo", aXmlResponseResultGetBinContent.BinContent.ItemNo));
sqlCommand.Parameters.Add(new SqlCeParameter("@ItemNo2", aXmlResponseResultGetBinContent.BinContent.ItemNo2));
SqlCeParameter param = sqlCommand.CreateParameter();
param.ParameterName = "@BinCode";
sqlCommand.Parameters.Add(param);
param = sqlCommand.CreateParameter();
param.ParameterName = "@Quantity";
sqlCommand.Parameters.Add(param);
param = sqlCommand.CreateParameter();
param.ParameterName = "@Fixed";
sqlCommand.Parameters.Add(param);
param = sqlCommand.CreateParameter();
param.ParameterName = "@Default";
sqlCommand.Parameters.Add(param);
for (Int32 k = 0; k < aXmlResponseResultGetBinContent.BinContent.Count; k++)
{
sqlCommand.Parameters["@BinCode"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).BinCode;
sqlCommand.Parameters["@Quantity"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).Quantity;
sqlCommand.Parameters["@Fixed"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).Fixed;
sqlCommand.Parameters["@Default"].Value = aXmlResponseResultGetBinContent.BinContent.Item(k).Default;
sqlCommand.ExecuteNonQuery();
}
}
sqlTran.Commit();
}
catch
{
sqlTran.Rollback();
throw;
}
}
}
}
public static Boolean RollbackTransaction(String inSelectStmt)
{
String curMethodName = "DataAccess:RollbackTransaction";
Stopwatch stopwatch = null;
Boolean curReturnValue = false;
if (DataAccessTraceActive)
{
stopwatch = new Stopwatch();
stopwatch.Start();
}
try {
if (DataAccessTransaction == null)
{
Log.WriteFile(curMethodName + ":No active transaction. Unable to Rollback Transaction");
MessageBox.Show(curMethodName + ":No active transaction. Unable to Rollback Transaction");
curReturnValue = false;
DataAccessEmbeddedTransactionCount = 0;
}
else
{
// if we have more than 1 in the count, then we have embedded Transactions and should not commit yet
if (DataAccessEmbeddedTransactionCount > 1)
{
DataAccessEmbeddedTransactionCount--;
}
else
{
DataAccessTransaction.Rollback();
DataAccessEmbeddedTransactionCount = 0;
DataAccessTransaction = null;
}
}
} catch (Exception ex) {
String ExcpMsg = ex.Message;
if (inSelectStmt != null)
{
ExcpMsg += "\nSQL=" + inSelectStmt;
}
Log.WriteFile(curMethodName + ":Exception Performing SQL operation:" + ExcpMsg);
MessageBox.Show(curMethodName + "\nException Performing SQL operation" + "\n\nError: " + ExcpMsg);
DataAccessEmbeddedTransactionCount = 0;
DataAccessTransaction = null;
curReturnValue = false;
}
if (DataAccessTraceActive && stopwatch != null)
{
stopwatch.Stop();
Log.WriteFile(curMethodName + ":Statement Performance: Time elapsed=" + stopwatch.Elapsed + ": SQL=" + inSelectStmt);
}
return(curReturnValue);
}
/* 把公司信息表格中的内容写入数据库
* private void Company_Button_Click(object sender, RoutedEventArgs e)
* {
* if (filenameCompanyInfo_ == null)
* {
* textBoxMessage.Text = "请先用浏览键选择一个包含公司信息的Excel文件";
* return;
* }
*
* ExcelInterface excel = new ExcelInterface(filenameCompanyInfo_, 'r');
* Excel.Range range = excel.GetWorksheet().UsedRange;
* int row = range.Rows.Count;
* SqlCeConnection connection = database_.GetConnection();
* using (var cmd = new SqlCeCommand("DELETE CompanyInfo", connection))
* {
* cmd.ExecuteNonQuery();
* }
*
* using (var cmd = new SqlCeCommand("INSERT INTO CompanyInfo VALUES (?, ?, ?, ?, ?, ?)", connection))
* {
* cmd.Parameters.Add("@id", SqlDbType.NVarChar);
* cmd.Parameters.Add("@name", SqlDbType.NVarChar);
* cmd.Parameters.Add("@tax", SqlDbType.NVarChar);
* cmd.Parameters.Add("@union", SqlDbType.NVarChar);
* cmd.Parameters.Add("@system", SqlDbType.NVarChar);
* cmd.Parameters.Add("@industry", SqlDbType.NVarChar);
*
* SqlCeTransaction transaction = connection.BeginTransaction();
* cmd.Transaction = transaction;
* try
* {
* for (int rCnt = 2; rCnt <= row; rCnt++)
* {
* for (int cCnt = 1; cCnt <= 6; cCnt++)
* {
* // 如果excel cell为空，则把dbnull.value写入数据库
* cmd.Parameters[cCnt - 1].Value =
* (range.Cells[rCnt, cCnt] as Excel.Range).Value ?? DBNull.Value;
* }
* cmd.ExecuteNonQuery();
* }
* transaction.Commit();
* UpdateCategories();
* textBoxMessage.Text = "更新成功，数据库现有" + (row - 1) + "条公司信息";
* }
* catch (Exception ex)
* {
* transaction.Rollback();
* textBoxMessage.Text = "更新失败，提供的Excel文件存在问题。详细信息：" + ex.Message;
* }
* }
* excel.Close();
* }*/
// 把经费信息表格中的内容写入数据库
private void Add_Funds_Button_Click(object sender, RoutedEventArgs e)
{
if (filenameFunds_ == null)
{
textBoxMessage.Text = "请先用浏览键选择一个包含经费信息的Excel文件";
return;
}
ExcelInterface excel = new ExcelInterface(filenameFunds_, 'r');
Excel.Range range = GetSortedRange(excel);
int row = range.Rows.Count;
SqlCeConnection connection = database_.GetConnection();
DateTime time = GetTimeFromComboBox(comboBoxInputYear, comboBoxInputMonth);
using (var cmd = new SqlCeCommand("DELETE FROM Funds WHERE Time = @time", connection))
{
cmd.Parameters.AddWithValue("@time", time);
cmd.ExecuteNonQuery();
}
using (var cmd = new SqlCeCommand("INSERT INTO Funds VALUES (?, ?, ?)", connection))
{
cmd.Parameters.Add("@id", SqlDbType.NVarChar);
cmd.Parameters.Add("@time", SqlDbType.DateTime);
cmd.Parameters.Add("@received", SqlDbType.Float);
SqlCeTransaction transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
try
{
float totalReceived = 0; // 相邻行如果是同一公司则把几个经费数字加起来
for (int rCnt = 2; rCnt <= row; rCnt++)
{
string id = (string)(range.Cells[rCnt, 1] as Excel.Range).Value;
totalReceived += (float)(range.Cells[rCnt, 5] as Excel.Range).Value;
if (rCnt == row || id != (string)(range.Cells[rCnt + 1, 1] as Excel.Range).Value)
{
cmd.Parameters[0].Value = id;
cmd.Parameters[1].Value = time;
cmd.Parameters[2].Value = totalReceived;
cmd.ExecuteNonQuery();
totalReceived = 0;
}
}
transaction.Commit();
textBoxMessage.Text = "成功添加" + time.ToString(timeFormat_) + (row - 1) + "条经费信息";
}
catch (Exception ex)
{
transaction.Rollback();
textBoxMessage.Text = "添加失败，提供的Excel文件存在问题，请确保文件最后的合计行之后没有多余的内容。详细信息：" + ex.Message;
}
}
excel.Close();
}
public void RollbackTransaction()
{
try
{
Trans.Rollback();
}
catch (Exception)
{
throw;
}
}
private bool atualizaTabelaItemPedido(SqlCeTransaction dbTrans, Produto p)
{
int rf;
//Atualiza o estoque
string sqlPedidoOriginalQuantidade = @"
select
quantidade
from
item_pedido
where id_pedido='" + D.Pedido.Id + "' and id_produto=" + p.Id;
int quantidadeOriginal = D.Bd.I(sqlPedidoOriginalQuantidade, dbTrans);
string sqlUpdateEstoque = @"
update
produto
set
estoque=estoque + " + quantidadeOriginal + " - " + p.QuantidadeRequerida + @"
where
id_produto = " + p.Id;
D.Bd.ExecuteNonQuery(sqlUpdateEstoque, dbTrans);
string sqlUpdateItem = "";
sqlUpdateItem = @"
update item_pedido
set
quantidade=" + p.QuantidadeRequerida + "," +
"id_funcionario=" + D.Funcionario.Id + "," +
"valor_unitario=" + Bd.RealPtParaBd(p.PrecoUtilizado.ToString()) + "," +
"desconto=" + p.Desconto +
" where id_pedido='" + D.Pedido.Id + "' and id_produto=" + p.Id;
rf = D.Bd.ExecuteNonQuery(sqlUpdateItem, dbTrans);
if (rf < 1)
{
dbTrans.Rollback();
return false;
}
//Verifica se o pedido tem grade e atualiza
if (p.IdGrade.Iniciada)
{
rf = p.Grade.Atualizar(dbTrans);
if (rf < 1)
{
dbTrans.Rollback();
return false;
}
}
return true;
}
//da
/// <summary>
/// Não é permitido editar pedidos com status S
/// </summary>
/// <param name="dbTrans"></param>
/// <returns></returns>
private bool atualizaTabelaPedido(SqlCeTransaction dbTrans)
{
//Iniciar transação
int rf;
string sqlInsertPedido = @"
update pedido
set
id_cliente_pocket='" + IdClientePocket + "'," +
"id_cliente_store=" + Bd.SN(IdClienteStore) + "," +
"data=" + Bd.TimeStampParaBd(Data) + "," +
"id_funcionario=" + Bd.S(D.Funcionario.Id) + "," +
"id_tabela_preco=" + Bd.S(IdTabelaPreco) + "," +
"id_forma_pagamento=" + Bd.S(IdFormaPagamento) + "," +
"status='N'" + "," +
"valor=" + Bd.RealPtParaBd(Bd.S(Valor)) + "," +
"observacao=" + Bd.SN(Observacao) + "," +
"desconto=" + Bd.Numerico(desconto) + "," +
"bdi=" + Bd.Numerico(bdi) +
" where id_pedido='" + Id + "'";
rf = D.Bd.ExecuteNonQuery(sqlInsertPedido, dbTrans);
if (rf < 1)
{
dbTrans.Rollback();
return false;
}
return true;
}
private bool inserirItemPedido(SqlCeTransaction dbTrans, Produto p)
{
int rf;
//Dá baixa no estoque:
string sqlUpdateEstoque = @"
update
produto
set
estoque=estoque - " + p.QuantidadeRequerida +
@" where
id_produto = " + p.Id;
rf = D.Bd.ExecuteNonQuery(sqlUpdateEstoque, dbTrans);
if (rf < 1)
{
dbTrans.Rollback();
return false;
}
string sqlInsertItem = "";
sqlInsertItem = @"
insert into
item_pedido
(id_pedido,
id_funcionario,
id_produto,
quantidade,
valor_unitario,
desconto)
values ('" +
D.Pedido.Id + "'," +
D.Funcionario.Id + "," +
p.Id + "," +
p.QuantidadeRequerida + "," +
Bd.RealPtParaBd(p.PrecoUtilizado.ToString()) + "," +
Bd.Numerico(Fcn.RoundComputing(p.Desconto, Parametro.ProdutoPrecoCasasDecimais)) + ")";
rf = D.Bd.ExecuteNonQuery(sqlInsertItem, dbTrans);
if (rf < 1)
{
dbTrans.Rollback();
return false;
}
//Verifica se o pedido tem grade e insere
p.IdItemPedido.V = D.Bd.I("SELECT @@IDENTITY AS UltimaIdentity", dbTrans);
if (p.IdGrade.Iniciada)
{
rf = p.Grade.Inserir(dbTrans);
if (rf < 1)
{
try
{
dbTrans.Rollback();
}
catch { }
return false;
}
}
return true;
}
public bool updateProduct(Product product)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" update Products ");
sb.Append(" set Name ='" + product.Name + "',");
sb.Append(" Code ='" + product.Code + "',");
sb.Append(" BarCode ='" + product.BarCode + "',");
sb.Append(" Unit ='" + product.Unit + "',");
sb.Append(" Price1 ='" + product.Price1 + "',");
sb.Append(" Price3 ='" + product.Price3 + "',");
sb.Append(" Price4 ='" + product.Price4 + "',");
sb.Append(" CreateDate ='" + DatetimeUtil.convertToDateThai2(product.CreateDate.ToString("dd/MM/yyyy")) + "'");
sb.Append(" where BarCode ='" + product.BarCode + "'");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public bool saveProduct(Product product)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("INSERT INTO Products(Name,Code,BarCode,Unit,Price1,Price3,Price4,CreateDate)");
sb.Append(" VALUES (@Name,@Code,@BarCode,@Unit,@Price1,@Price3,@Price4,@CreateDate)");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@Name", SqlDbType.NVarChar).Value = product.Name;
com.Parameters.Add("@Code", SqlDbType.NVarChar).Value = product.Code;
com.Parameters.Add("@BarCode", SqlDbType.NVarChar).Value = product.BarCode;
com.Parameters.Add("@Unit", SqlDbType.NVarChar).Value = product.Unit;
com.Parameters.Add("@Price1", SqlDbType.NVarChar).Value = product.Price1;
com.Parameters.Add("@Price3", SqlDbType.NVarChar).Value = product.Price3;
com.Parameters.Add("@Price4", SqlDbType.NVarChar).Value = product.Price4;
com.Parameters.Add("@CreateDate", SqlDbType.NVarChar).Value = product.CreateDate;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public bool saveDocCheckProductDetail(DocCheckProductDetail docCheckProductDetail)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("INSERT INTO DocCheckProductDetails(DCode,PCode,PName,PUnit,NumProduct,CreateDate)");
sb.Append(" VALUES (@DCode,@PCode,@PName,@PUnit,@NumProduct,@CreateDate)");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@DCode", SqlDbType.NVarChar).Value = docCheckProductDetail.DocCheckProduct.Code;
com.Parameters.Add("@PCode", SqlDbType.NVarChar).Value = docCheckProductDetail.Product.BarCode;
com.Parameters.Add("@PName", SqlDbType.NVarChar).Value = docCheckProductDetail.Product.Name;
com.Parameters.Add("@PUnit", SqlDbType.NVarChar).Value = docCheckProductDetail.Product.Unit;
com.Parameters.Add("@NumProduct", SqlDbType.NVarChar).Value = docCheckProductDetail.NumProduct;
com.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = DateTime.Now;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public bool saveDocCheckProduct(DocCheckProduct docCheckProduct)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("INSERT INTO DocCheckProducts(DCode,Warehouse,Location,CreateDate)");
sb.Append(" VALUES (@DCode,@Warehouse,@Location,@CreateDate)");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.Parameters.Clear();
com.Parameters.Add("@DCode", SqlDbType.NVarChar).Value = docCheckProduct.Code;
com.Parameters.Add("@Warehouse", SqlDbType.NVarChar).Value = docCheckProduct.Warehouse;
com.Parameters.Add("@Location", SqlDbType.NVarChar).Value = docCheckProduct.Location;
com.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = DateTime.Now;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
//da
private bool inserirTabelaPedido(SqlCeTransaction dbTrans)
{
int rf;
string sqlInsertPedido = @"
insert into
pedido
(id_pedido,
id_cliente_pocket,
id_cliente_store,
data,
id_funcionario,
id_tabela_preco,
id_forma_pagamento,
status,
valor,
observacao,
desconto,
bdi)
values ('" +
Id + "','" +
IdClientePocket + "'," +
Bd.SZ(IdClienteStore) + "," +
Bd.TimeStampParaBd(Data) + "," +
Bd.S(D.Funcionario.Id) + "," +
Bd.S(IdTabelaPreco) + "," +
Bd.S(IdFormaPagamento) + "," +
"'N'," +
Bd.RealPtParaBd(Bd.S(Valor)) + "," +
Bd.SN(Observacao) + "," +
Bd.Numerico(Fcn.RoundComputing(desconto, Parametro.ProdutoPrecoCasasDecimais)) + "," +
Bd.Numerico(bdi) +
")";
rf = D.Bd.ExecuteNonQuery(sqlInsertPedido, dbTrans);
if (rf < 1)
{
dbTrans.Rollback();
return false;
}
//No longer required code since after Guid deployment
// Globals.Pedido.Id = Globals.Bd.Guid("SELECT @@IDENTITY AS UltimaIdentity", dbTrans);
return true;
}
public bool editNumberCheckProduct(string _id,string _dcode,string _pcode,int numberedit)
{
bool result = false;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" update DocCheckProductDetails ");
sb.Append(" set NumProduct ="+numberedit+"");
sb.Append(" where DCode ='"+_dcode+"'");
sb.Append(" and PCode='"+_pcode+"'");
sb.Append(" and ID='" + _id + "'");
string sqlAdd;
sqlAdd = sb.ToString();
tr = Conn.BeginTransaction();
com = new SqlCeCommand();
com.CommandText = sqlAdd;
com.CommandType = CommandType.Text;
com.Connection = Conn;
com.Transaction = tr;
com.ExecuteNonQuery();
tr.Commit();
result = true;
}
catch (Exception ex)
{
tr.Rollback();
Conn.Close();
result = false;
Console.WriteLine(ex.Message);
}
finally
{
Conn.Close();
}
return result;
}
public int Inserir(SqlCeTransaction dbTrans)
{
int rf;
//Código comentado por estar afetando a performance
//verifica se existe na tabela saldo estoque
string sqlExisteSaldoEstoque = @" select count(*) "+
@"from saldo_grade" +
@" where
id_produto = " + Grade.Produto.Id +
@" and id_grade = " + Grade.Produto.IdGrade.V +
@" and id_item_grade = " + IdItemGrade +
@" and id_atributo = " + IdAtributo +
@" and id_item_atributo = " + IdItemAtributo;
if (D.Bd.I(sqlExisteSaldoEstoque) > 0)
{
//Dá baixa no estoque:
string sqlUpdateEstoque = @"
update
saldo_grade
set
estoque = estoque - " + Quantidade +
@" where
id_produto = " + Grade.Produto.Id +
@" and id_grade = " + Grade.Produto.IdGrade.V +
@" and id_item_grade = " + IdItemGrade +
@" and id_atributo = " + IdAtributo +
@" and id_item_atributo = " + IdItemAtributo;
rf = D.Bd.ExecuteNonQuery(sqlUpdateEstoque, dbTrans);
if (rf < 1)
{
dbTrans.Rollback();
return -1;
}
}
string sqlInsertItemGrade = @"
insert into
item_pedido_grade
(id_produto,
id_funcionario,
id_atributo,
id_item_atributo,
id_item_grade,
id_pedido,
id_grade,
quantidade)
values (" +
Grade.Produto.Id + "," +
D.Funcionario.Id + "," +
IdAtributo + "," +
IdItemAtributo + "," +
IdItemGrade + ",'" +
Grade.Produto.Pedido.Id + "'," +
Grade.Produto.IdGrade.V + "," +
Quantidade + ")";
rf = D.Bd.ExecuteNonQuery(sqlInsertItemGrade, dbTrans);
return rf;
}