"Mark Baldwin" <sWozzi3@community.nospam> wrote in message
news:%23Gq$$ZxcHHA.4516@TK2MSFTNGP04.phx.gbl...
>I am using SQL2000 and .NET 2.0
>
> I have a dataset, with two DataTables each having a TableAdapter. I need
> to use the insert command on the first TableAdapter and then execute a
> stored procedure on the second table adapter - all within a transaction.
>
> Now I want to use datasets, because they auto generate the code which is
> exactly the same as I intend to manually create anyway and they have the
> advantage that I can quickly refresh them if any columns change etc
>
> All of my database operations are in stored procedures in the server, but
> accessed through the TableAdapter.
>
> Problem is that each TableAdapter uses it's own connection. I have tried
> creating my own connection and transaction and assigning the connection to
> the TableAdapter but I just get an error when executing a stored
> procedure...
>
> [System.InvalidOperationException] = {"ExecuteScalar 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."}
>
> Anyway, I have tried to wrap these operations in a TransactionScope but
> this promotes to a DTC transaction with SQL2000 which isn't acceptable.
>
> Surely there is straightforward way in which you can utilize datasets to
> load data from the database and then tableadapters to run any operations
> through predetermind stored procedures.
>
> Any help much appreciated...
> Mark
>

"WenYuan Wang [MSFT]" <v-wywang@online.microsoft.com> wrote in message
news:krFyUoNdHHA.4368@TK2MSFTNGHUB02.phx.gbl...
> Hi Mark and Bruno,
>
> By the way, due to some performance issue, I'm afraid reflection is not a
> good choice for us. However, have you consider adding a partial class to
> expose the DataAdapter? So that we can access DataAdapter out of
> TableAdapter namesapace and set the transaction to its commands.
>
> For example: (This is a sample, you should have to modify it according to
> your scenario.)
> namespace WebApplication3.TestDataBase20060908DataSetTableAdapters{
> partial class Table_1TableAdapter
> {
> public System.Data.SqlClient.SqlDataAdapter getDataAdapter()
> {
> return this.Adapter;
> }
> }
> }
>
> Additionally, expose a method to set transcation,
> public void setTransactions(System.Data.SqlClient.SqlTransaction st)
> {
> this.Adapter.SelectCommand.Transaction = st;
> this.Adapter.UpdateCommand.Transaction = st;
> this.Adapter.InsertCommand.Transaction = st;
> this.Adapter.DeleteCommand.Transaction = st;
> return;
> }
>
> Hope this helps,
> Sincerely,
> Wen Yuan
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>