public void UpdatedRecordIsSavedWhenTransactionCommitted()
{
string commandString = "Update Items Set Price = 5000 where ItemID = 2";
SqlCeCommand cmd = (SqlCeCommand)db.GetSqlStringCommand(commandString);
SqlCeConnection conn = (SqlCeConnection)db.CreateConnection();
conn.Open();
SqlCeTransaction trans = conn.BeginTransaction();
DataSet dsActualResult = db.ExecuteDataSet(cmd, trans);
commandString = "Select ItemDescription, Price from Items order by ItemID";
cmd = (SqlCeCommand)db.GetSqlStringCommand(commandString);
dsActualResult = db.ExecuteDataSet(cmd, trans);
trans.Commit();
conn.Close();
trans.Dispose();
conn.Dispose();
Assert.AreEqual<int>(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());
}
protected override void AfterEachExample()
{
transaction.Rollback();
transaction.Dispose();
dbConnection.Close();
dbConnection.Dispose();
}
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();
}
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 void Commit()
{
if (ConnectionType == ConnectionType.SqlClient)
{
_SqlTrans.Commit();
_SqlTrans.Dispose();
_SqlTrans = null;
if (SqlConn.State == ConnectionState.Open)
{
SqlConn.Close();
}
}
else if (ConnectionType == ConnectionType.OleDb)
{
_OleTrans.Commit();
_OleTrans.Dispose();
_OleTrans = null;
if (OleConn.State == ConnectionState.Open)
{
OleConn.Close();
}
}
else if (ConnectionType == ConnectionType.MySqlClient)
{
_MySqlTrans.Commit();
_MySqlTrans.Dispose();
_MySqlTrans = null;
if (MySqlConn.State == ConnectionState.Open)
{
MySqlConn.Close();
}
}
else if (ConnectionType == ConnectionType.SqlCE)
{
_CETrans.Commit();
_CETrans.Dispose();
_CETrans = null;
if (CEConn.State == ConnectionState.Open)
{
CEConn.Close();
}
}
}
/// <summary>
/// Release resources owned by this instance
/// </summary>
/// <param name="disposing"></param>
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (_ownsConnection && _conn != null)
{
_conn.Dispose();
}
if (_ownsTransaction && _trans != null)
{
_trans.Dispose();
}
}
}
/// <summary>
/// Inserts a gallery with the specified <paramref name="galleryId" />.
/// </summary>
/// <param name="galleryId">The gallery ID.</param>
/// <exception cref="SqlCeException">Thrown when a record for the gallery already exists.</exception>
private static void Create(int galleryId)
{
SqlCeTransaction tran = null;
SqlCeConnection cn = Util.GetDbConnectionForGallery();
cn.Open();
try
{
tran = cn.BeginTransaction();
DataUtility.SetIdentityInsert("gs_Gallery", true, cn, tran);
using (SqlCeCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandText = @"
INSERT INTO [gs_Gallery] (GalleryId, Description, DateAdded)
VALUES (@GalleryId, 'My Gallery', GETDATE());";
cmd.Parameters.AddWithValue("@GalleryId", galleryId);
cmd.ExecuteNonQuery();
}
DataUtility.SetIdentityInsert("gs_Gallery", false, cn, tran);
tran.Commit();
}
catch
{
if (tran != null)
{
tran.Rollback();
}
throw;
}
finally
{
if (tran != null)
{
tran.Dispose();
}
cn.Close();
}
}
public void RecordIsNotUpdatedWhenTransactionNotCommitted()
{
string commandString = "Update Items Set Price = 5000 where ItemID = 2";
SqlCeCommand cmd = (SqlCeCommand)db.GetSqlStringCommand(commandString);
SqlCeConnection conn = (SqlCeConnection)db.CreateConnection();
conn.Open();
SqlCeTransaction trans = conn.BeginTransaction();
DataSet dsActualResult;
try
{
dsActualResult = db.ExecuteDataSet(cmd, trans);
commandString = "Update Items Set QtyinHand = 50000000000000000 where ItemID = 3";
cmd = (SqlCeCommand)db.GetSqlStringCommand(commandString);
dsActualResult = db.ExecuteDataSet(cmd, trans);
Assert.Fail("Exception should have been thrown on update");
trans.Commit();
}
catch { }
conn.Close();
trans.Dispose();
conn.Dispose();
commandString = "Select QtyinHand, Price from Items order by ItemID";
dsActualResult = db.ExecuteDataSetSql(commandString);
Assert.AreEqual <int>(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("25", dsActualResult.Tables[0].Rows[0][0].ToString().Trim());
Assert.AreEqual("38.95", dsActualResult.Tables[0].Rows[0][1].ToString().Trim());
Assert.AreEqual("95", dsActualResult.Tables[0].Rows[1][0].ToString().Trim());
Assert.AreEqual("98.95", dsActualResult.Tables[0].Rows[1][1].ToString().Trim());
Assert.AreEqual("34", dsActualResult.Tables[0].Rows[2][0].ToString().Trim());
Assert.AreEqual("89", dsActualResult.Tables[0].Rows[2][1].ToString().Trim());
}
public void Dispose()
{
try
{
if (Trans != null)
{
Trans.Dispose();
}
if (Conn != null)
{
if (Conn.State == System.Data.ConnectionState.Open)
{
Conn.Close();
}
Conn.Dispose();
}
}
catch (Exception)
{
throw;
}
}
private bool saveData(out int billID)
{
string errorText;
SqlCeConnection connection = Global.getDatabaseConnection(out errorText);
SqlCeTransaction transaction = null;
try
{
using (SqlCeCommand command = connection.CreateCommand())
{
if (billToEdit.HasValue)
{
configureBillMasterUpdateCommand(command);
}
else
{
configureBillMasterInsertCommand(command);
}
transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.ExecuteNonQuery(); //save bill master data
if (!billToEdit.HasValue)
{
command.CommandText = "SELECT @@IDENTITY AS BillID";
command.Parameters.Clear();
decimal ID = (decimal)(command.ExecuteScalar());
billID = decimal.ToInt32(ID);
}
else
{
//delete bill details for the existing bill
command.CommandText = "DELETE FROM BILLDETAILS WHERE BILLID = " + billToEdit.Value;
command.ExecuteNonQuery();
billID = billToEdit.Value;
}
saveBillDetails(command, decimal.ToInt32(billID));
transaction.Commit();
}
}
catch (Exception ex)
{
if (transaction != null)
{
transaction.Rollback();
}
string message = "An error occurred in saving the bill data in the database." +
"\nThe error text is as follows:\n" + Global.getExceptionText(ex);
SystemSounds.Hand.Play();
Cursor.Current = Cursors.Default;
MessageBox.Show(message, "Error in Saving Data", MessageBoxButtons.OK,
MessageBoxIcon.Hand);
billID = 0;
ErrorLogger.LogError(ex);
return(false);
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
}
return(true);
}
public static bool TransferData(string sourceDatabasePath, string targetDatabasePath)
{
string sourceConnectionString = "Data Source = " + sourceDatabasePath + "; Password = "******"Data Source = " + targetDatabasePath + "; Password = "******"FirmDetails", "UnitOfMeasurement", "Items", "Customers" };
sourceConnection.Open();
targetConnection.Open();
foreach (string tableName in tableNames)
{
transaction = targetConnection.BeginTransaction();
transferTableData(sourceConnection, targetConnection, transaction, tableName);
transaction.Commit(CommitMode.Immediate);
transaction.Dispose();
}
transaction = targetConnection.BeginTransaction();
transferCustomerBalance(sourceConnection, targetConnection, transaction);
transaction.Commit();
}
}
catch (Exception ex)
{
if (transaction != null)
{
try
{
transaction.Rollback();
}
catch (Exception exception)
{
ErrorLogger.LogError(exception);
}
}
string message = "An error occurred in transferring data from the previous financial year to the new " +
"financial year.\n The error text is as follows:\n" + Global.getExceptionText(ex);
System.Media.SystemSounds.Hand.Play();
System.Windows.Forms.MessageBox.Show(message, "Error Occurred", System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Error);
ErrorLogger.LogError(ex);
return(false);
}
finally
{
if (transaction != null)
{
transaction.Dispose();
}
if (targetConnection != null)
{
targetConnection.Close();
targetConnection.Dispose();
}
}
return(true);
}
private void WriteToServer(ISqlCeBulkCopyInsertAdapter adapter)
{
CheckDestination();
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
GetAndDropConstraints();
List <KeyValuePair <int, int> > map;
int totalRows = 0;
SqlCeTransaction localTrans = _trans ?? _conn.BeginTransaction();
if (ColumnMappings.Count > 0)
{
//mapping are set, and should be validated
map = ColumnMappings.ValidateCollection(_conn, localTrans, adapter, _keepNulls, _destination);
}
else
{
//create default column mappings
map = SqlCeBulkCopyColumnMappingCollection.Create(_conn, localTrans, adapter, _keepNulls, _destination);
}
using (var cmd = new SqlCeCommand(_destination, _conn, localTrans))
{
cmd.CommandType = CommandType.TableDirect;
using (var rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
{
var idOrdinal = SqlCeBulkCopyTableHelpers.IdentityOrdinal(_conn, localTrans, _destination);
var rec = rs.CreateRecord();
var rowCounter = 0;
IdInsertOn(localTrans, idOrdinal);
//Converting to an array removed the perf issue of a list and foreach statement.
var cm = map.ToArray();
while (adapter.Read())
{
if (adapter.SkipRow())
{
continue;
}
for (var i = 0; i < cm.Length; i++)
{
//caching the values this way do not cause a perf issue.
var sourceIndex = cm[i].Key;
var destIndex = cm[i].Value;
// Let the destination assign identity values
if (!_keepIdentity && destIndex == idOrdinal)
{
continue;
}
//determine if we should ever allow this in the map.
if (sourceIndex < 0)
{
continue;
}
var value = sourceIndex > -1 ? adapter.Get(sourceIndex) : null;
if (value != null && value.GetType() != DbNullType)
{
rec.SetValue(destIndex, value);
}
else
{
//we can't write to an auto number column so continue
if (_keepNulls && destIndex == idOrdinal)
{
continue;
}
if (_keepNulls)
{
rec.SetValue(destIndex, DBNull.Value);
}
else
{
rec.SetDefault(destIndex);
}
}
}
rowCounter++;
totalRows++;
try
{
rs.Insert(rec);
}
catch (SqlCeException ex)
{
if (ex.NativeError == 25016 && _ignoreDuplicateErrors) //A duplicate value cannot be inserted into a unique index.
{
System.Diagnostics.Trace.TraceWarning("SqlCeBulkCopy: Duplicate value error was ignored");
continue;
}
else
{
throw;
}
}
// Fire event if needed
if (RowsCopied != null && _notifyAfter > 0 && rowCounter == _notifyAfter)
{
FireRowsCopiedEvent(totalRows);
rowCounter = 0;
}
}
IdInsertOff(localTrans, idOrdinal);
if (RowsCopied != null)
{
FireRowsCopiedEvent(totalRows);
}
}
}
//if we have our own transaction, we will commit it
if (_trans == null)
{
localTrans.Commit(CommitMode.Immediate);
localTrans.Dispose();
}
ResetSeed(totalRows);
RestoreConstraints();
}
private void DisposeTransaction()
{
m_Transaction.Dispose();
m_Transaction = null;
}