Record Locking and Transactions in Strongly Typed Datasets

Windows Forms Data Controls and Databinding

SiN!

Hi,

I have a form with 4 tables displayed in different tabs, all coming from the same dataset. Here's part of the Form.Load event so you know what I mean:


Code Snippet
Me.taPaymentsApplied.Fill(Me.DsPaymentProcessing.PAYMENT_APPLIED)
Me.taNewPayment.Fill(Me.DsPaymentProcessing.PAYMENT, Me.txtCardID.Text)
Me.taExistingPayment.Fill(Me.DsPaymentProcessing.EXISTING_PAYMENT, Me.txtCardID.Text)
Me.taInvoices.Fill(Me.DsPaymentProcessing.INVOICE, Me.txtCardID.Text)

What I need to do is lock all the records that are opened in the latter 3 lines and begin a transaction. Then allow the user with the lock to add records to the first table and modify contents of the next 3. I'd prefer that other users simply not be allowed to even view these locked records.

Saving needs to be in a transaction, meaning either save ALL 4 tables or fail on all.

I can do this is I do everything programmatically, but I'm a bit lost when it comes to using objects from my Datasource window.

I'd appreciate any help.

Thanks

Kia
Top

Re: Windows Forms Data Controls and Databinding Record Locking and Transactions in Strongly Typed Datasets

Kiavash

Anyone

My latest attempt follows:

I tried to begin a transaction from the TableAdapter's connection first, which seems to do its thing without error. But then, openning the table and filling the TA returns an error.

Me.taNewPayment.Connection.Open()

Me.taNewPayment.Connection.BeginTransaction(IsolationLevel.Serializable)

'**************************

Me.taNewPayment.Fill(Me.DsPaymentProcessing.PAYMENT, Me.txtCardID.Text)

The following error is raised:

InvalidOperationException: ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

Can anyone tell me what this means




Top

Re: Windows Forms Data Controls and Databinding Record Locking and Transactions in Strongly Typed Datasets

Rong-Chun Zhang - MSFT

Hi Kiavash,

It is because that you have set the transaction for the connection, but you did not set the transaction for SelectCommand of this TableAdapter. Unfortunately, you can get access to the SelectCommand. So you can not use it that way. Try to use the transaction like this:

Code Snippet

connection.Open();

SqlCommand command = connection.CreateCommand();

SqlTransaction transaction;

// Start a local transaction.

transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

// Must assign both transaction object and connection

// to Command object for a pending local transaction

command.Connection = connection;

command.Transaction = transaction;

Hope this helps.

Regards.





Top

Re: Windows Forms Data Controls and Databinding Record Locking and Transactions in Strongly Typed Datasets

hrubesh

A methodic way to takle this problem is that i create a partial class to the adapter. namespace and class name should be same as the adapter's.

then see how i pass the transaction.

Namespace CostingDataSetTableAdapters

Partial Class InvoiceTableAdapter

Public Sub setMyTransactions(ByVal t As SqlClient.SqlTransaction)

Me.Adapter.InsertCommand.Transaction = t

Me.Adapter.UpdateCommand.Transaction = t

Me.Adapter.DeleteCommand.Transaction = t

Me.Adapter.SelectCommand.Transaction = t

End Sub

End Class

End Namespace

so in a program if i have to use 2 adapters in one transaction, i create a transaction, and call the 2 adapters.setMyTransactions(t) and then call any update/fill methods.





Top

Re: Windows Forms Data Controls and Databinding Record Locking and Transactions in Strongly Typed Datasets

Rong-Chun Zhang - MSFT

Hi kia,

Yes, change the source code of 'xxxDataSet.Disgner.cs' manually will make it work.

Hope this helps.

Regards.





Top

Re: Windows Forms Data Controls and Databinding Record Locking and Transactions in Strongly Typed Datasets

Kiavash

Thankyou both. I'll try it out right now.

What exactly do you mean to change in the designer The Private to Public for the transaction class

Also, if I make modifications to the Designer file, is there not a risk of it getting over written again by VS

Thanks again


Top

Re: Windows Forms Data Controls and Databinding Record Locking and Transactions in Strongly Typed Datasets

Rong-Chun Zhang - MSFT

Hi Kiavash,

Yes, it might be written by VS again. So I recommand you do not change the 'xxxDataSet.Designer.cs'. Just use it manually.

Hope this helps.

Regards





Top

Re: Windows Forms Data Controls and Databinding Record Locking and Transactions in Strongly Typed Datasets

Kiavash

Rong-Chun Zhang - MSFT wrote:

It is because that you have set the transaction for the connection, but you did not set the transaction for SelectCommand of this TableAdapter. Unfortunately, you can get access to the SelectCommand. So you can not use it that way. Try to use the transaction like this:

Code Snippet

connection.Open();

SqlCommand command = connection.CreateCommand();

SqlTransaction transaction;

// Start a local transaction.

transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

// Must assign both transaction object and connection

// to Command object for a pending local transaction

command.Connection = connection;

command.Transaction = transaction;

I tried this, but still no good, but not sure if I'm using it right.. The next line is

myTableAdapter.Connection = connection

Followed by the "Fill" method. I still get the same error. I'm not sure what exactly to do with your code!


Top

Re: Windows Forms Data Controls and Databinding Record Locking and Transactions in Strongly Typed Datasets

hrubesh

You should not go about editing the dataset.designer.cs

I am currently doing it like this, and my next move is i am thinking about concurrency...

at least this can help you .. note that i have inherited from my dataset in this case... and for the table adapter below, it is the partial class .

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

namespace BLInvoicing

{

public class SaveInvoices : DALInvoicing.DSInvoices

{

public virtual void SaveInvoice_Charges()

{

using (TAInvoice.Connection)

{

TAInvoice.Connection.Open();

TAInvoiceCharge.Connection = TAInvoice.Connection;

SqlTransaction transac = TAInvoice.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

transac = TAInvoice.BeginTransaction( TAInvoice.Connection , transac);

transac = TAInvoiceCharge.BeginTransaction(TAInvoiceCharge.Connection, transac);

try

{

this.TAInvoice.Update(this.Invoice);

this.TAInvoiceCharge.Update(this.InvoiceCharges);

transac.Commit();

}

catch(Exception Ex)

{

transac.Rollback();

throw Ex;

}

finally

{

TAInvoice.Connection.Close();

TAInvoice.Connection = null;

}

}

}

}

}

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

namespace DALInvoicing.DSInvoicesTableAdapters

{

public partial class InvoiceTableAdapter

{

public SqlDataAdapter InnerAdapter

{

get { return Adapter; }

}

public SqlTransaction BeginTransaction(SqlConnection Conn, SqlTransaction Tran)

{

if (Conn.State == ConnectionState.Closed)

{

throw new ArgumentException("1 - Closed Connection");

}

Connection = Conn;

this._adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

if (Tran == null)

{

Tran = Conn.BeginTransaction();

}

foreach (SqlCommand cmd in CommandCollection)

{

cmd.Transaction = Tran;

}

if (Adapter.InsertCommand != null)

{

Adapter.InsertCommand.Transaction = Tran;

}

if (Adapter.UpdateCommand != null)

{

Adapter.UpdateCommand.Transaction = Tran;

}

if (Adapter.SelectCommand != null)

{

Adapter.SelectCommand.Transaction = Tran;

}

if (Adapter.DeleteCommand != null)

{

Adapter.DeleteCommand.Transaction = Tran;

}

return Tran;

}

}

}





Top

Re: Windows Forms Data Controls and Databinding Record Locking and Transactions in Strongly Typed Datasets

Rong-Chun Zhang - MSFT

Hi Kiavash,

You still didn't get the main point. I mean you should set the transaction to your TableAdapter's SelectCommand. Some thing like that:

Code Snippet

myTableAdapter.SelectCommand.Tansaction = transaction

Unfortunately, you can not get access to the SelectCommand of a TableAdatapter. You can add the following method to your 'xxxDataSet.Designer.cs'. This allow you to get access to the SelectCommand.

Code Snippet

public System.Data.SqlClient.SqlCommand SelectCommand

{

get

{

return this.CommandCollection[0];

}

}

In the Form's code you may use this like:

Code Snippet

this.myTableAdapter.Connection.Open();

SqlTransaction transaction = this.myTableAdapter.Connection.BeginTransaction(IsolationLevel.Serializable);

this.myTableAdapter.SelectCommand.Transaction = transaction;

this.myTableAdapter.Fill(this.northwindDataSet.Employees);

transaction.Commit();

Hope this helps.

Regards





Top