[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1470
  • Last Modified:

Typed Dataset Transaction support thru partial class

I have created a namespace collision while I'm trying to implement transaction support using a partial class for a strongly typed dataset (tableadapter).

I have a dataset (Northwind.xsd) with a table adapter EmployeesTableAdapter

I created a partial class for the EmployeesTableAdapter object (see code below)

I have the following errors:

'PessimisticConcurrencyExample.EmployeesTableAdapter' does not contain a definition for 'GetEmployees' and no extension method 'GetEmployees' accepting a first argument of type 'PessimisticConcurrencyExample.EmployeesTableAdapter' could be found (are you missing a using directive or an assembly reference?

'PessimisticConcurrencyExample.EmployeesTableAdapter' does not contain a definition for 'CommandCollection' and no extension method 'CommandCollection' accepting a first argument of type 'PessimisticConcurrencyExample.EmployeesTableAdapter' could be found (are you missing a using directive or an assembly reference?

'PessimisticConcurrencyExample.EmployeesTableAdapter' does not contain a definition for 'adapter' and no extension method 'adapter' accepting a first argument of type 'PessimisticConcurrencyExample.EmployeesTableAdapter' could be found (are you missing a using directive or an assembly reference?

'PessimisticConcurrencyExample.EmployeesTableAdapter' does not contain a definition for 'adapter' and no extension method 'adapter' accepting a first argument of type 'PessimisticConcurrencyExample.EmployeesTableAdapter' could be found (are you missing a using directive or an assembly reference?

'PessimisticConcurrencyExample.EmployeesTableAdapter' does not contain a definition for 'adapter' and no extension method 'adapter' accepting a first argument of type 'PessimisticConcurrencyExample.EmployeesTableAdapter' could be found (are you missing a using directive or an assembly reference?

I'm not very familiar with partial classes and I'm trying to understand where my namespace error is occuring.

Everything was working fine before I tried to create the partial class.

Can anyone help explain what I have done wrong?
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 
namespace PessimisticConcurrencyExample
{
    public partial class EmployeesTableAdapter
    {
        private SqlTransaction _transaction;
 
        private SqlTransaction Transaction
        {
            get
            {
                return this._transaction;
            }
            set
            {
                this._transaction = value;
            }
        }
 
        public void BeginTransaction()
        {
            if (this._transaction.Connection.State != ConnectionState.Open)
            {
                this._transaction.Connection.Open();
            }
 
            this.Transaction = this._transaction.Connection.BeginTransaction();
 
            foreach (SqlCommand command in this.CommandCollection)
            {
                command.Transaction = _transaction;
            }
 
            this.adapter.InsertCommand.Transaction = this.Transaction;
            this.adapter.UpdateCommand.Transaction = this.Transaction;
            this.adapter.DeleteCommand.Transaction = this.Transaction;
 
        }
 
        public void CommitTransaction()
        {
            this.Transaction.Commit();
            this._transaction.Connection.Close();
        }
 
        public void RollbackTransaction()
        {
            this.Transaction.Rollback();
            this._transaction.Connection.Close();
        }
    }
}
 
 
Here is the employee class file:
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PessimisticConcurrencyExample.NorthwindTableAdapters;
using System.Data;
using System.Data.SqlClient;
 
namespace PessimisticConcurrencyExample
{
    class Employee
    {
        private EmployeesTableAdapter _adapter = null;
 
        public EmployeesTableAdapter adapter
        {
            get
            {
                if (_adapter == null)
                {
                    _adapter = new EmployeesTableAdapter();
                }
                return _adapter;
            }
        }
 
        public Northwind.EmployeesDataTable GetEmployees()
        {
            return adapter.GetEmployees();
        }
    }
}

Open in new window

0
-Dman100-
Asked:
-Dman100-
  • 13
  • 8
1 Solution
 
wht1986Commented:
Hmm, im not sure but inside the BeginTransaction method you have defined

this.adapter.InsertCommand.Transaction = this.Transaction etc etc

Im not sure but since you are inside the partial class of the tableadapter already shouldnt it be just

this.InsertCommand.Transaction = this.Transaction etc etc
0
 
-Dman100-Author Commented:
Hi wht1986,

I can't seem to access the members of that object.  I thought I was missing a using directive based on the error messages, but I've tried every namespace I think is relevant with no luck.

I am not sure if I've created the partial class correctly?

Is there any code I can provide that might help identify my error?

Thanks for the help.
0
 
wht1986Commented:
I'll start up visual studio and try a few things, meanwhile its kind of odd to put a transaction inside a single table adapter like that.  Usually i create a transaction from the connection object and then assign that to multiple tableadapters so that all my updates across all the tables are in a single transaction. Just curious why you have a transaction in a single table adpater. Im guessing to hold off commiting while you do something else in the code.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
-Dman100-Author Commented:
Hi wht1986,

Actually, this is just a simple exercise to understand the uses of transactions using typed datasets and pessimistic concurrency.

The problem is that transactions are not available by default with typed datasets.  From what I found, transactions can be implemented thru the use of a partial class, which is what I am trying to do.

Thanks for your help.  I sincerely appreciate it.
0
 
wht1986Commented:
Here's my example and it seems to work. You had a few syntax errors but nothing major.  Let me know if this is what you are trying to do.


namespace DataSet1TableAdapters
{
    public partial class TestTableTableAdapter
    {
        private SqlTransaction _transaction;
 
        private SqlTransaction Transaction
        {
            get{return this._transaction;}
            set{this._transaction = value;}
        }
 
        public void BeginTransaction()
        {
            if (this.Connection.State != ConnectionState.Open)
            {
                this.Connection.Open();
            }
 
            this.Transaction = this.Connection.BeginTransaction();
 
            foreach (SqlCommand command in this.CommandCollection)
            {
                command.Transaction = _transaction;
            }
 
            this.Adapter.InsertCommand.Transaction = this.Transaction;
            this.Adapter.UpdateCommand.Transaction = this.Transaction;
            this.Adapter.DeleteCommand.Transaction = this.Transaction;
 
        }
 
        public void CommitTransaction()
        {
            this.Transaction.Commit();
            this.Connection.Close();
        }
 
        public void RollbackTransaction()
        {
            this.Transaction.Rollback();
            this.Connection.Close();
        }
    }
}
 
 
Here i fetch, modify, and update the data via a transaction
 
DataSet1TableAdapters.TestTableTableAdapter adpt = new TestTableTableAdapter();
DataSet1.TestTableDataTable tbl = adpt.GetData();
tbl[1].Column2 = "chunky5";
adpt.BeginTransaction();
adpt.Update(tbl);
adpt.CommitTransaction();

Open in new window

0
 
-Dman100-Author Commented:
Hi wht1986,

A couple of the errors I discovered I could fix by changing the partial class on the EmployeesTableAdapter class to the Employees class where I have the property for adapter and the fill and update methods.

Notice the difference how I am accessing the Adapter member compared to your code:  
Your code:
this.Adapter.InsertCommand.Transaction = this.Transaction;
this.Adapter.UpdateCommand.Transaction = this.Transaction;
this.Adapter.DeleteCommand.Transaction = this.Transaction;

My code:
this._adapter.Adapter.InsertCommand.Transaction = this.Transaction;
this._adapter.Adapter.UpdateCommand.Transaction = this.Transaction;
this._adapter.Adapter.DeleteCommand.Transaction = this.Transaction;

Why is it different in my code?

I am getting only one error now:

'PessimisticConcurrencyExample.Employee' does not contain a definition for 'CommandCollection' and no extension method 'CommandCollection' accepting a first argument of type 'PessimisticConcurrencyExample.Employee' could be found (are you missing a using directive or an assembly reference?

It appears I'm missing a namespace for the CommandCollection?

See the full code below for the employee class file and the partial class for employee:

Thanks for your help.
Employee class file:
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PessimisticConcurrencyExample.NorthwindTableAdapters;
using System.Data;
using System.Data.SqlClient;
 
namespace PessimisticConcurrencyExample
{
    class Employee
    {
        private EmployeesTableAdapter _adapter = null;
 
        public EmployeesTableAdapter adapter
        {
            get
            {
                if (_adapter == null)
                {
                    _adapter = new EmployeesTableAdapter();
                }
                return _adapter;
            }
        }
 
        public Northwind.EmployeesDataTable GetEmployees()
        {
            return adapter.GetEmployees();
        }
    }
}
 
 
Partial class for employee class file:
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 
 
namespace PessimisticConcurrencyExample
{
    public partial class Employee
    {
        private SqlTransaction _transaction;
 
        private SqlTransaction Transaction
        {
            get
            {
                return this._transaction;
            }
            set
            {
                this._transaction = value;
            }
        }
 
        public void BeginTransaction()
        {
            if (this._transaction.Connection.State != ConnectionState.Open)
            {
                this._transaction.Connection.Open();
            }
 
            this.Transaction = this._transaction.Connection.BeginTransaction();
 
            foreach (SqlCommand command in this.CommandCollection)
            {
                command.Transaction = _transaction;
            }
 
            this._adapter.Adapter.InsertCommand.Transaction = this.Transaction;
            this._adapter.Adapter.UpdateCommand.Transaction = this.Transaction;
            this._adapter.Adapter.DeleteCommand.Transaction = this.Transaction;
 
        }
 
        public void CommitTransaction()
        {
            this.Transaction.Commit();
            this._transaction.Connection.Close();
        }
 
        public void RollbackTransaction()
        {
            this.Transaction.Rollback();
            this._transaction.Connection.Close();
        }
    }
}

Open in new window

0
 
wht1986Commented:
If you look at my code I have no Employee Class per se. My line of code that was
this.Adapter.InsertCommand.Transaction = this.Transaction;
is inside the partial class of the TableAdapter.

That is to say the VS designer automatically made a table adapter class called TestTableTableAdapter.

My class is a partial class on the same TestTableTableAdapter.  This you can think of it as i am extending the implementation of the current TestTableTableAdapter without having to do any inheritance.

As such, I am able to access all the internal methods of the table adapter.

What it appears you are doing now, is creating a seperate class (not based on extending the table adapter) and providing support through methods and properties to execute an underlying adapter.  This appears to be a slight change in what you first posted as i took the line "public partial class EmployeesTableAdapter" to mean that you were creating a partial class extension on the VS generated table adapter.

For the purposes of me giving a clear example.  Let say VS designer created a table adapter called EmployeesTableAdapter.

1) Specifically are you trying to create a partial of this class
2) Are you creating a fresh 'wrapper' class
3) where do you want the transaction support

i think we are almost there
0
 
-Dman100-Author Commented:
Hi wht1986,

I was kinda following the example in this tutorial: http://www.asp.net/learn/data-access/tutorial-63-cs.aspx

To answer the questions you posted:

1) Specifically are you trying to create a partial of this class...Yes, I was trying to create a partial class, but in my scenario, I wasn't sure if I needed to create a partial class of the tableadapter or the employee class file.  Initially, I was trying to create a partial class of the tableadapter, but I couldn't access the members indicated in my first post, which after switching to the partial class of the employee class file, I could access the adapter property member.
2) Are you creating a fresh 'wrapper' class...I'm still learning .net so I'm not clear on exactly what a wrapper class is.
3) where do you want the transaction support...on my winform button click event handlers, I want to instantiate the employee class and call the appropriate methods...for example, to update that are wrapped in a try/catch block and call the transaction methods....BeginTransaction, Commit or Rollback.

Does this make sense?  I hope I am answering your questions correctly...being new to .net, I might not be explaining myself correctly.  Please let me know if what I'm saying is a clear as mud.

Thanks again for all your help!
0
 
wht1986Commented:
Ok to answer part of the question, sometimes properties on the dataset, datatable, and table adapters can not be seen from the outside world. The designer likes to mark them as private or protected. In the designed, select your table adapter on the screen and look in the properties panel. You need to set the access modifier to public.  Then you can see Connection and Adapter collections from outside the tableadapter object.
Capture1.JPG
0
 
wht1986Commented:
grr wrong picture, lol
Capture.JPG
0
 
wht1986Commented:
Like everything else in software developement, there are a million ways to do anything.  Lets start simple, just using the designer based data table, i can do something like the following on a button click.


private void button1_Click(object sender, EventArgs e)
{
    // empty dataset
    DataSet1 ds = new DataSet1();
    
    // create an adapter
    DataSet1TableAdapters.UsersTableAdapter adpt = new DataSet1TableAdapters.UsersTableAdapter();
    
    // get the records
    adpt.Fill(ds.Users);
 
    // add a new record
    ds.Users.AddUsersRow("NewUserName", "NewNickName");
 
    // delete the first record
    ds.Users[0].Delete();
 
    // create a transaction and set it
    adpt.Connection.Open();
    SqlTransaction trx = adpt.Connection.BeginTransaction();
    adpt.Adapter.SelectCommand.Transaction = trx;
    adpt.Adapter.InsertCommand.Transaction = trx;
    adpt.Adapter.UpdateCommand.Transaction = trx;
    adpt.Adapter.DeleteCommand.Transaction = trx;
 
    try
    {
        // do the inserts and updates
        adpt.Update(ds.Users);
 
        // commit it
        trx.Commit();
    }
    catch
    {
        // rollback
        trx.Rollback();
    }
 
    // close the connection
    adpt.Connection.Close();
}

Open in new window

0
 
wht1986Commented:
Now finally for fun, I created a partial class of the built in table adapter and created a new method "UpdateWithTransaction". I call it like the regular update method but it does all the heavy work of setting the transaction.

Anyhow hope this helps give you some ideas.
public partial class UsersTableAdapter
{
    public void UpdateWithTransaction(DataSet1.UsersDataTable dt)
    {
        this.Connection.Open();
        SqlTransaction trx = this.Connection.BeginTransaction();
        this.Adapter.SelectCommand.Transaction = trx;
        this.Adapter.InsertCommand.Transaction = trx;
        this.Adapter.UpdateCommand.Transaction = trx;
        this.Adapter.DeleteCommand.Transaction = trx;
 
        try
        {
            this.Update(dt);
            trx.Commit();
        }
        catch
        {
            trx.Rollback();
        }
        finally
        {
            this.Connection.Close();
        }
    }
}
 
 
button click event now looks like
 
private void button1_Click(object sender, EventArgs e)
{
    // empty dataset
    DataSet1 ds = new DataSet1();
 
    // create an adapter
    DataSet1TableAdapters.UsersTableAdapter adpt = new DataSet1TableAdapters.UsersTableAdapter();
 
    // get the records
    adpt.Fill(ds.Users);
 
    // add a new record
    ds.Users.AddUsersRow("NewUserName", "NewNickName");
 
    // delete the first record
    ds.Users[0].Delete();
 
    adpt.UpdateWithTransaction(ds.Users);
 
}

Open in new window

0
 
-Dman100-Author Commented:
Hi wht1986,

Okay, think the problem is definately related to the connection modifier access.  Initially, it was set to private, but I set it to public.  I've cleaned and re-built the solution, but for whatever reason, the partial class cannot find the definitions for 'Connection', 'CommandCollection' and 'Adapter'.

See the attached screen shot of the Connection modifier set to public.  Would there be anything else that would make these member unaccessible?

I can try to delete the entire project and start over to see if that makes a difference.
capture.png
0
 
wht1986Commented:
My partial class is working fine.  I created a zip file at http://www.kevinwhite.net/ForDman.zip that contains all the files for a simple project. you will have to change the connection string obviously to run it, but it does work.  Copy it to a directory and the do a open web site from the file system with visual studio.
Capture.JPG
0
 
-Dman100-Author Commented:
Hi wht1986,

Sorry for the delay in responding back.  I was running a few tests to verify my results.  The example you provided works and I was able to duplicate the example and get it to work.

Here is where I see a difference.

The example you provided is a website example.  I was successful when I created a website example following your example.

My original project was a winform app, which I still have been unable to access the connection, adapter and command collection members.

What is the difference in creating a partial class in a web app and winform app that would not expose the adapter, connection and commandcollection members?

0
 
wht1986Commented:
Yay Microsoft. I have a winform version of the project at http:\\www.kevinwhite.net/ForDmanWin.zip

2 things of note:

1) in windows forms double clicking the tableadapter on the designer will auto create the partial class for you. (see pic)

2) I had a multiproject solution like you did. if you right click the project or (Shift F6) you will see the project builds fine.  It's only when you try to run the app through the debugger (F5) that you get the failures. in fact, after you do a build, you can run the exe from the file system just fine.

On a whim, I created a blank winform project and did the exact same code. I had no issues running F5.

So what do I think it is, we hit some hiccup in the IDE in how it handles dynamically denerated code of a dataset in a multi project solution.

The attached code is for the single project solution.  I still wouldnt handle a transation in this manner but it was for a learning exercise.
Capture.JPG
0
 
wht1986Commented:
When I really need a transaction it is usually because I am updating several tables at once and what all the sql actions to be rolled up into a transaction.  So in the case of the button click that initiates 3 table updates in a transaction, the code i would use would be like:
        private void SaveButton_Click(object sender, EventArgs e)
        {
 
            DataSet1TableAdapters.TestTableTableAdapter adpt1 = new DataSet1TableAdapters.TestTableTableAdapter();
            DataSet1TableAdapters.ProductsTableAdapter adpt2 = new DataSet1TableAdapters.ProductsTableAdapter();
            DataSet1TableAdapters.CheezWhizTableAdapter adpt3 = new DataSet1TableAdapters.CheezWhizTableAdapter();
 
            // make sure the all use the same connection
            SqlConnection conn = adpt1.Connection;
            adpt2.Connection = conn;
            adpt3.Connection = conn;
 
            SqlTransaction trx = null;
 
            try
            {
                conn.Open();
                trx = conn.BeginTransaction();
                adpt1.Transaction = trx;
                adpt2.Transaction = trx;
                adpt3.Transaction = trx;
 
                adpt1.Update(this.dataSet1.TestTable);
                adpt2.Update(this.dataSet1.Products);
                adpt3.Update(this.dataSet1.CheezWhiz);
 
                trx.Commit();
            }
            catch (Exception exc)
            {
                trx.Rollback();
            }
            finally
            {
                conn.Close();
            }
        }

Open in new window

0
 
wht1986Commented:
I think we hashed out all the basics of partial classes and transactions through table adapters though :)
0
 
wht1986Commented:
Was the code I posted sufficient to get you going in the direction you wanted?
0
 
-Dman100-Author Commented:
Hi wht1986,

Sorry I didn't respond sooner.  I've been out of town all weekend and just got back this afternoon.

Yes, the code you posted did make sense and has me going in the right direction.  I'm working on a few examples to get more familiarized with transactions, but it makes much more sense now.

Again, I want to say thank you for all the help and hanging in there with my novice questions.  Your explanations helped the light bulb turn on for me.  I sincerely appreciate your help.

Best Regards,
-D
0
 
-Dman100-Author Commented:
Thanks again!!!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 13
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now