none
How to implement a Transaction Object in typed dataset? RRS feed

  • Question

  • I have used a typed dataset and am Deleting and inserting using my data adapter object, But I am not sure how to enclose this into a tranaction.

    Currently my datapter is DeleteCommand is having the following query.
    "DELETE FROM tblApprovedUsers where UserName = ?"

    and this is how I am invoking this in the code. 

    Dim da As New ApprovedUsersTableAdapters.tblApprovedUsersTableAdapter  

    da.Delete(txtOCid.Text) 

    Me.Close()

     With a typed dataset how can I gather the connection object and the so that I may the transaction object. Is there a particular article that might guide me. or do I need to do a FILL? since the adpater is handling the connection details, how would I go about this.

    Thanks ...


    Monday, June 11, 2012 7:23 PM

Answers

  • A dataset is not direct related to a transaction the table adapter is, but that is then about the connection. 

    With a transaction you should open and close yourself the connection (transaction), while otherwise the adapter does that itself.

    Be aware that transactions in the case of datasets are normally only needed with a dataset which contains more related datatables.

    Also be aware that a transaction should never be interrupted at the client side, if it is done it can have used replies, rollback or commit even if an error is catch meanwhile.

    Never let the transaction wait because the result is a time out and a meanwhile unusable database.


    Success
    Cor

    • Marked as answer by JafferyM Tuesday, June 12, 2012 1:35 PM
    Tuesday, June 12, 2012 4:44 AM
  • Hi JafferyM,

    I guess the data tables are not from the same data base, please correct me, and you want to manage them in one transaction.

    If your requirement is like this then I would suggest that you could get a try on TransactionScope class, please refer to the following link and go through with its code snippet:

    Hope this could give you a light.

    Best Regards,


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by JafferyM Tuesday, June 12, 2012 1:35 PM
    • Unmarked as answer by JafferyM Tuesday, June 12, 2012 1:36 PM
    • Marked as answer by JafferyM Tuesday, June 12, 2012 1:37 PM
    Tuesday, June 12, 2012 7:33 AM
    Moderator

All replies

  • A dataset is not direct related to a transaction the table adapter is, but that is then about the connection. 

    With a transaction you should open and close yourself the connection (transaction), while otherwise the adapter does that itself.

    Be aware that transactions in the case of datasets are normally only needed with a dataset which contains more related datatables.

    Also be aware that a transaction should never be interrupted at the client side, if it is done it can have used replies, rollback or commit even if an error is catch meanwhile.

    Never let the transaction wait because the result is a time out and a meanwhile unusable database.


    Success
    Cor

    • Marked as answer by JafferyM Tuesday, June 12, 2012 1:35 PM
    Tuesday, June 12, 2012 4:44 AM
  • Hi JafferyM,

    I guess the data tables are not from the same data base, please correct me, and you want to manage them in one transaction.

    If your requirement is like this then I would suggest that you could get a try on TransactionScope class, please refer to the following link and go through with its code snippet:

    Hope this could give you a light.

    Best Regards,


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by JafferyM Tuesday, June 12, 2012 1:35 PM
    • Unmarked as answer by JafferyM Tuesday, June 12, 2012 1:36 PM
    • Marked as answer by JafferyM Tuesday, June 12, 2012 1:37 PM
    Tuesday, June 12, 2012 7:33 AM
    Moderator
  • thanks for clearing up my concepts for the adapter and connection.

    Currently I am just working with a single source db, and the complexity is limited to one datatable.

    Tuesday, June 12, 2012 1:32 PM
  • thanks my current implementation is only one source db and one datatable. But It is good to know how to manage this thru multiple source db's using a single transaction.

    thanks.

    (PS. can you reccomend a good ADO.NET book to clear up my concepts. )

    Tuesday, June 12, 2012 1:38 PM
  • Hi JafferyM,

    The book of ADO.Net Programming should be good for you, also you could refer to the following links for online resources.

    I guess this issue is related with typed dataset and I think you may want to go through this link:

    Also for ADO.NET programming, you could follow these links and there are lots of example code snippet for you to understand the concepts of ADO.Net related:

    Hope this could help you.


    Tony Xiao [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, June 13, 2012 10:11 AM
    Moderator
  • Hi Jaffery --

    I agree with Tony, you should use TransactionScope. I'd like to offer you another link to my blog post about the subject:

    http://geek-goddess-bonnie.blogspot.com/2010/12/transactionscope-and-sqlserver.html


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Saturday, June 16, 2012 4:50 PM
  • Hi Jaffery --

    I agree with Tony, you should use TransactionScope. I'd like to offer you another link to my blog post about the subject:

    http://geek-goddess-bonnie.blogspot.com/2010/12/transactionscope-and-sqlserver.html


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Bonnie, 

    I'm curious, the OP wrote he is using one DataTable. What is than the benefit to load the extra's of transaction scope. I don't see it but maybe I miss something?


    Success
    Cor

    Monday, June 18, 2012 6:09 AM
  • Hi Cor,

    Yes, the OP could use the Connection's Transaction. But, TransactionScope seems to me to be much cleaner, more flexible and easier to use. We make use of it extensively in our apps. We have used TransactionScope to wrap both database access and MSMQ activity all into one Transaction and the use of TransactionScope is the *only* way to do that.

    Perhaps using TransactionScope is overkill for what the OP needs to do, but I think getting used to using it now will make his apps more consistent if he gets to the point where he has more complicated stuff. 

    How much extra overhead is there to use TransactionScope anyway, do you have any statistics?


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, June 18, 2012 2:07 PM