Combining Transactions with Strongly Typed DataSets

I was thinking down the lines of using a transactionScope to achieve what I wanted but it just did not fit my needs. In my projects I create a presentation layer, a Business Object Layer and also a Data Access Layer which is a Strongly Typed DataSet.

In some procedures I need to make three different updates using different table adapters and if one fails say the last, I don't really want the other data to have worked. I want everything to have worked or nothing.

The solution I found to it was here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237800&SiteID=1

All I want to do here is give you a C# version of it which I have done and which also solved my problem. Here is the code:

public void AssignTransaction(ref System.Data.SqlClient.SqlTransaction transaction)
{
            Adapter.InsertCommand.Transaction = transaction;
            Adapter.InsertCommand.Connection = transaction.Connection;
            Adapter.UpdateCommand.Transaction = transaction;
            Adapter.UpdateCommand.Connection = transaction.Connection;
            Adapter.DeleteCommand.Transaction = transaction;
            Adapter.DeleteCommand.Connection = transaction.Connection;
}

We pass in the transaction by reference so all use the same copy of the object and in turn committing all the actions to one transaction.

One donwside I see to this is the following. Adapter is private, and also, every times your dataset changes your code disappears, as it recompiles its own code, thus losing yours. And going back to this, the way is simple.

Add a codefile to your project and add the relevant namespace which your TableAdapter resides inside. You then extend the partial class inside this code file to give you the extra functionality. e.g.

namespace DatasetTableAdapters
{
    public partial class TableAdapter
    {
        public void AssignTransaction(ref System.Data.SqlClient.SqlTransaction transaction)
        {
            Adapter.InsertCommand.Transaction = transaction;
            Adapter.InsertCommand.Connection = transaction.Connection;
            Adapter.UpdateCommand.Transaction = transaction;
            Adapter.UpdateCommand.Connection = transaction.Connection;
            Adapter.DeleteCommand.Transaction = transaction;
            Adapter.DeleteCommand.Connection = transaction.Connection;
        }
    }
}

Cross Thread Safety with anonymous methods

When working with multithreading and async operations you will no doubt get a message that you have tried to execute a cross thread operation which is not allowed. Times like this I would make a delagte reflection of the method I wish to use, and then invoke the target control, passing in the arguments the function requires.

A little easier way which requires only one delegate is the following. Create you delegate for example:


private delegate void InvokeDel();

Then in my sample project for example, I have a small network app and when a client connects I add the remote end point or IPAddress:PortNo to the list view. Because I am doing this with Async this needs to be cross thread safe so I did the following:

private void AddClient(Socket _client)
{
 
 this.Invoke(new InvokeDel(delegate()
 { 
  listView1.Items.Add(new ListViewItem(new string[] { _client.RemoteEndPoint.ToString(), _client.SocketType.ToString() }));
 }));
}

This works find and uses anonymous methods.

ADO.NET & "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

I realise you may find other solutions for this on other blogposts but I feel I have out a bit more how to debug this very unhelpgul error. Yes it does involve check the DataTable or DataRow HasErrors. If true you then drill down the the Rows in Error and further to the columns in error through the row.

When I was debuging a certain block of my code I founf that the HasErrors did not trigger if I tried to populate my DataTable with the GetDataBy() method. BTW I am using Strongly Typed Datasets here. So for example, I declared a datatable at the top of the code:

private YourDataset.EmployeesDataTable EmployeeDT;

Then in the block where I will fill the datatable I did the following. I instantiated the datatable outside of the try catch statement. Inside the try block I attempt to populate the DataTable with the FillBy() method and then in the catch block I read whether or not the DataTable HasErrors. If it does I display the information in the exception which will then give me more direction in debugging the problem.

EmployeeDT = new YourDataset.EmployeesDataTable();

try
{
 EmployeeTableAdapter.FillById(EmployeeDT,23456);




}
catch
{
 String s = null;
 if (EmployeeDT.HasErrors)
 {
  foreach (DataRow dr in EmployeeDT.GetErrors())
  {
   foreach (DataColumn dc in dr.GetColumnsInError())
   {
    s += dc.ColumnName + ",";
   }
  }
  throw new Exception("Yes Errors Encountered, Columns in error: " + s);
 }
}

Another thing on this, on my last encounter with this error it was due to me overlooking the fact that the TableAdapter field length of the a field was not long enough to accomodate a certain value which was i the database. I had stored the field length in the database as nvarchar(MAX) yet I must have neglected to update the DataSet which for that field had a MAX length of 50.

When you have your DataSet in design view or if you want to do this in code view, you can set it to AllowDBNull and when it encounters a nnull value, depending on the datatype to do one of three things: Throw and Exception, Insert Null, Or insert an empty string.

Cheers

Andrew