Professional Web Applications Themes

Transaction - Microsoft SQL / MS SQL Server

Within a procedure i create two connection RConnection for readonly operation WConnection for write operation on a DB. Associated with the WConnection i create a Transaction with isolationlevel ReadUncommitted; All three object are stored in Session variables. I use a class to perform operation on a DB and i need to have all operation that could modify the DB within the transaction Here is the code : string StrConn = "User ID="+UID+";Password="+PWD+";Initial Catalog=Media;Data Source=(local)"; WConnection.ConnectionString = StrConn; RConnection.ConnectionString = StrConn; RConnection.Open(); WConnection.Open(); WTransaction = WConnection.BeginTransaction(IsolationLevel.ReadUn committed); Session["WConnection"] = WConnection; Session["RConnection"] = RConnection; Session["WTransaction"] = WTransaction; In another procedure (within another ...

  1. #1

    Default Transaction


    Within a procedure i create two connection

    RConnection for readonly operation
    WConnection for write operation on a DB.

    Associated with the WConnection i create a Transaction with
    isolationlevel ReadUncommitted;

    All three object are stored in Session variables.

    I use a class to perform operation on a DB and i need to have all
    operation that could
    modify the DB within the transaction

    Here is the code :


    string StrConn = "User ID="+UID+";Password="+PWD+";Initial
    Catalog=Media;Data Source=(local)";

    WConnection.ConnectionString = StrConn;
    RConnection.ConnectionString = StrConn;

    RConnection.Open();
    WConnection.Open();

    WTransaction =
    WConnection.BeginTransaction(IsolationLevel.ReadUn committed);


    Session["WConnection"] = WConnection;
    Session["RConnection"] = RConnection;
    Session["WTransaction"] = WTransaction;


    In another procedure (within another asp.net page) i create an istance
    of my object
    and associate the connection


    Doent newDoc = new Doent();
    newDoc.Connection = WConnection;
    newDoc.Transaction = WTransaction;

    newDoc.Create(); // this insert a record in a table

    in a second procedure i read the doent list stored on the DB
    Doent Doc = new Doent();
    Doc.Connection = RConnection;
    Doc.List();

    Here is the code of the metod List()

    SqlDataAdapter myCommand = new SqlDataAdapter("Doent_List",
    myConnection);
    myCommand.SelectCommand.CommandType =
    CommandType.StoredProcedure;
    ... // code for parameter of the storedprocedure
    myCommand.ExecuteNonQuery();


    At this point the program interrupts and this message is shown :
    Timeout expired. The timeout period elapsed prior to completion of
    the operation or the server is not responding.


    If i was clear, can someone help me to understand what happens?
    Does the transaction block the execution of the storedprocedure?

    --
    Posted via [url]http://dbforums.com[/url]
    basidati Guest

  2. #2

    Default Transaction

    If i need to run ONLY ONE update, delete or insert.
    Do I need to use begin transaction and commit or rollback ?
    Why ?

    Thanks,

    JC
    JC Guest

  3. #3

    Default Re: Transaction

    Any sql statement is ATOMIC by nature. So no you do not need to wrap it in
    a transaction unless you have other statements you need to be included.

    --

    Andrew J. Kelly
    SQL Server MVP


    "JC" <com.br> wrote in message
    news:00f601c35dc1$bae3abe0$gbl... 


    Andrew Guest

  4. #4

    Default Re: Transaction

    Nope, an insert update or delete is a transaction in itself. The only
    exception is if you use SET IMPLICIT_TRANSACTIONS ON, in that case you have
    to use COMMIT TRAN after each transaction.
    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "JC" <com.br> wrote in message
    news:00f601c35dc1$bae3abe0$gbl... 


    Jacco Guest

Similar Threads

  1. Cant Serialize transaction
    By szhark in forum Macromedia ColdFusion
    Replies: 6
    Last Post: June 27th, 10:39 AM
  2. WS-Transaction
    By moko in forum ASP.NET Web Services
    Replies: 1
    Last Post: August 13th, 12:25 AM
  3. Transaction Log
    By Shamim in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: August 22nd, 06:25 AM
  4. Transaction id and transaction isolation
    By Alex in forum Informix
    Replies: 3
    Last Post: July 23rd, 09:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139