Re: Send an email until all steps done

Discussion in 'ASP .Net' started by Alexey Smirnov, Feb 3, 2010.

  1. On Feb 3, 3:05 pm, "aspfun via DotNetMonster.com" <u53138@uwe> wrote:
    > To complete an order, I need to code the following steps one by one.
    >
    > 1) update order table,  tOrder
    > 2) update customer order history table, tCustomer
    > 3) update shipment table, tShip
    > 4) send an confirfation email
    >
    > The problem is sometime due to unknown reason, after step 2), program can not
    > finish step 3) and 4).
    > Since tOrder and tCustomer table was updated, how to back to their data
    > before update?
    >
    > --
    > Message posted via DotNetMonster.comhttp://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1


    What do you mean to back to their data before an update? In any case
    if an exception is thrown, it must be caught. You should have an
    orderid which can be used to track the order. Once an exception is
    detected you should send user back to the previous step (if it's a
    wizard) with his orderid.
     
    Alexey Smirnov, Feb 3, 2010
    #1
    1. Advertising

  2. Alexey Smirnov

    Miro Guest

    im assuming you are using a table adapter?

    take a look at transactions

    you will have to specify a transaction and call the "open" on your database
    by yourself.

    I suspect you are commit each portion instead of doing it all at once.

    here is an example: -written in notepad
    Grabbed from a book by David Sceppa - ADO.net - Core Reference
    ==============
    Here are a couple examples you should quickly look at...
    (written in notepad so may be a spelling mystake here and there)
    Requires an import of: IMPORTS system.transactions

    String strConn as string ' connection string
    strConn = "Data source=.\SQLExpress;" & "Inital catalog=northwind;integrated
    security=true"

    using cn as new sqlConnection( strConn)
    cn.open()
    'execute your queries
    Endusing

    'Take note how i didn't have to do a cn.close(). That is because i was
    using the "USING cn" ...and the ENDUSING cleans it up for you.

    However...i could have done this:

    Try
    Cn.open
    Catch

    Endtry

    Cn.close()


    A basic example is like this:
    Lets say you are using a DataAdapter or sqldataadapter
    If I do this:

    If i have this
    Dim strconn, strsql as string
    Strconn = "data source=.\SqlExpres;.... ( you ge the idea here )
    Dim cn as new sqlConnection(strConn)
    Dim daCustomers, daOrders as sqlDataadapter
    strSQL = "Select customerID, ComapnyName from customers"
    daCustomers = new sqlDataAdapter( strSQL, cn )
    strSQL = "Select ordered, CustomerID, OrderDate from Orders"
    daOrders = new sqlDataAdapter( strSQL, cn )
    dim ds as new dataset)(
    daCustomers.fill( ds, "Customers" )
    daOrders.fill( ds, "orders" )

    'In the above example...when filling teh data table "Custoemrs" and "orders",
    I actually opened up the connection twice ( which is what you are trying not
    to do )... even though they both have the same "cn" connection (
    sqlconnection ) specified. In this case...they will use the same connection
    in the connection pooling but you can get around the issue by opening up the
    connection just once ( see below ):

    Cn.Open()
    daCustomers.fill( ds, "Customers" )
    daOrders.Fill( ds, "Orders" )
    cn.close()

    because I specifically opened the connection prior to calling the fill
    meathod, ( and closed it afterwards )...they both used the same connection.
    If the connection is opend prior to the .fill it doesn't open it or close
    it.
    Its up to you to close it.

    =Using transactions to do rollbacks on all your updates.

    USING txn As new TransactionScope()
    Using cn1 as new sqlconnection(strcon1)
    Cn1.open()
    'Perform work on teh connection
    Cn1.close
    Endusing
    Using cn2 as new sqlconnection( strconn2)
    Cn2.open
    'preform work on the other connection
    Cn2.close
    Endusing

    'Commit the work performed within the TRANSACTION
    Txn.commit()
    END USING

    You can also "Enlist" the transaction to a connection like this:

    Using CN1 as new sqlconnection(strconn1), cn2 as new sqlconnection(strconn2)
    Cn1.open()
    Cn2.open()
    Using txn as new committableTranasction()
    Cn1.EnlistTransaction( txn )
    Cn2.EnlistTransaction( txn )
    'Perform work on the
    connections
    Txn.commit()
    End using
    'remove the connections from the transactions
    Cn1.enlistTransaction( Nothing)
    Cn2.enlisttransaction( Nothing )

    Cn1.close()
    Cn2.close()
    End using


    =====

    ==============


    "Alexey Smirnov" <> wrote in message
    news:...
    On Feb 3, 3:05 pm, "aspfun via DotNetMonster.com" <u53138@uwe> wrote:
    > To complete an order, I need to code the following steps one by one.
    >
    > 1) update order table, tOrder
    > 2) update customer order history table, tCustomer
    > 3) update shipment table, tShip
    > 4) send an confirfation email
    >
    > The problem is sometime due to unknown reason, after step 2), program can
    > not
    > finish step 3) and 4).
    > Since tOrder and tCustomer table was updated, how to back to their data
    > before update?
    >
    > --
    > Message posted via
    > DotNetMonster.comhttp://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1


    What do you mean to back to their data before an update? In any case
    if an exception is thrown, it must be caught. You should have an
    orderid which can be used to track the order. Once an exception is
    detected you should send user back to the previous step (if it's a
    wizard) with his orderid.
     
    Miro, Feb 3, 2010
    #2
    1. Advertising

  3. On Feb 5, 3:29 pm, "aspfun via DotNetMonster.com" <u53138@uwe> wrote:
    > aspfun wrote:
    > >>im assuming you are using a table adapter?

    >
    > >[quoted text clipped - 123 lines]
    > >>detected you should send user back to the previous step (if it's a
    > >>wizard) with his orderid.

    >
    > >Thank you.
    > >I'll start coding now.

    >
    > I need to perform three jobs: insert, delete and update for three tables.
    > I code as below. Am I right? Can I only open one connection but do three jobs?
    >
    > Dim myTrans as SqlTransaction = myConnection.BeginTransation()
    >
    > dim s1, s2, s3 as string
    > s1 = "insert..."
    > s2= "delete..."
    > s3= "update..."
    >
    > Dim mySQLCommand1 As New SqlCommand(s1, myConnection, myTrans)
    > Dim mySQLCommand2 As New SqlCommand(s2, myConnection, myTrans)
    > Dim mySQLCommand3 As New SqlCommand(s3, myConnection, myTrans)
    >
    > Using myConnection
    >             Try
    >                 myConn.Open()
    >                 mySQLCommand1.ExecuteNonQuery()
    >                 mySQLCommand2.ExecuteNonQuery()
    >                 mySQLCommand2.ExecuteNonQuery()
    >                 myTrans.commit
    >                myConnection.Close()
    >             Catch ex As SqlException
    >                 myTrans.Rollback
    >             Finally
    >                 myConn.Close()
    >             End Try
    >  End Using
    >
    > --
    > Message posted via DotNetMonster.comhttp://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1


    You don't need 3 SqlCommands for this. Take a look this example

    Using connection As New SqlConnection(connectionString)
    connection.Open()

    Dim command As SqlCommand = connection.CreateCommand()
    Dim transaction As SqlTransaction

    ' Start a local transaction
    transaction = connection.BeginTransaction("SampleTransaction")

    ' Must assign both transaction object and connection
    ' to Command object for a pending local transaction.
    command.Connection = connection
    command.Transaction = transaction

    Try
    command.CommandText = _
    "Insert into Region (RegionID, RegionDescription) VALUES
    (100, 'Description')"
    command.ExecuteNonQuery()
    command.CommandText = _
    "Insert into Region (RegionID, RegionDescription) VALUES
    (101, 'Description')"

    command.ExecuteNonQuery()

    ' Attempt to commit the transaction.
    transaction.Commit()
    Console.WriteLine("Both records are written to database.")

    Catch ex As Exception
    Console.WriteLine("Commit Exception Type: {0}",
    ex.GetType())
    Console.WriteLine(" Message: {0}", ex.Message)

    ' Attempt to roll back the transaction.
    Try
    transaction.Rollback()

    Catch ex2 As Exception
    ' This catch block will handle any errors that may
    have occurred
    ' on the server that would cause the rollback to fail,
    such as
    ' a closed connection.
    Console.WriteLine("Rollback Exception Type: {0}",
    ex2.GetType())
    Console.WriteLine(" Message: {0}", ex2.Message)
    End Try
    End Try
    End Using

    Source: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

    Hope this helps
     
    Alexey Smirnov, Feb 5, 2010
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Peter
    Replies:
    0
    Views:
    3,375
    Peter
    Jul 1, 2003
  2. James Stroud
    Replies:
    2
    Views:
    317
    Lawrence D'Oliveiro
    Sep 9, 2007
  3. Chris Reay

    Does Socket.send send all bytes?

    Chris Reay, Nov 3, 2003, in forum: Ruby
    Replies:
    2
    Views:
    223
    Chris Reay
    Nov 4, 2003
  4. Zhidian Du
    Replies:
    0
    Views:
    158
    Zhidian Du
    Feb 21, 2004
  5. Replies:
    2
    Views:
    410
    Tintin
    Jan 5, 2007
Loading...

Share This Page