ASP.NET Source Code

db_trans.aspx
<%@ Page Language="VB" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SQLClient" %>
<script language="VB" runat="server">
    Sub Page_Load(sender As Object, e As EventArgs)
        Dim myConnection  As SqlConnection
        Dim myCommand     As SqlCommand
        Dim myTransaction As SqlTransaction
        ' Create our connection
		myConnection = New SqlConnection("Data Source=10.2.2.133;" _
						& "Initial Catalog=samples;User Id=samples;Password=password;" _
						& "Connect Timeout=15;Network Library=dbmssocn;")
        ' Open the connection.
        myConnection.Open()
        ' Create our command and set its connection property.
        myCommand = New SqlCommand()
        myCommand.Connection = myConnection
        ' Begin a transaction and assign it to the Transaction property of our command.
        myTransaction = myConnection.BeginTransaction()
        myCommand.Transaction = myTransaction
        ' Build our command text and execute the command
        myCommand.CommandText = "INSERT INTO [scratch] (text_field, integer_field, date_time_field) " _
                        & "VALUES ('" _
                        & CStr(WeekdayName(WeekDay(Now()))) & "', '" _
                        & CInt(Day(Now())) & "', '" _
                        & Now() & "');"
        myCommand.ExecuteNonQuery()
        litSqlCommand.Text = myCommand.CommandText
        ' Either commit or rollback the transaction based on querystring
        If Request.QueryString("rollback") = "true" Then
            litRollback.Text = "Rolling Back Transaction."
            myTransaction.Rollback()
        Else
            ' If we're going to commit the transaction that means that we're adding
            ' a record to our sample table.  In order to keep the table a managable
            ' size we need to remove a record from the table as well.  This isn't
            ' very important to the functioning of the script, but it makes my life
            ' easier and might be interesting to some.
            myCommand.CommandText = "DELETE FROM scratch WHERE id = " _
                & "(SELECT TOP 1 id FROM scratch ORDER BY id ASC)"
            myCommand.ExecuteNonQuery()
            litRollback.Text = "Commiting Transaction."
            myTransaction.Commit()
        End If
        ' Close the connection
        myConnection.Close()
    End Sub
</script>
<html>
<head>
<title>ASP.NET Database Transactions Sample</title>
</head>
<body>
<form runat="server" EnableViewState="false">
<p>
<strong>Executing SQL Command:</strong><br />
<code><asp:Literal id="litSqlCommand" runat="server" /></code>
</p>
<p>
<strong><asp:Literal id="litRollback" runat="server" /></strong>
</p>
<br />
<p>
<strong>Run the script again:</strong>
<a href="<%= Request.ServerVariables("URL") %>?rollback=false">Commit Transaction</a>
or
<a href="<%= Request.ServerVariables("URL") %>?rollback=true">Rollback Transaction</a>
</p>
<br />
<p>
You can view any data that was committed to the database via our
<a href="http://aspnet.asp101.com/samples/db_simple.aspx">Simple Database</a>
sample.  It's probably easiest if you use the value of the
<code>date_time_field</code> (shown in the SQL statement above)
to locate the appropriate record.
</p>
</form>
<hr />
<p>
Click <a href="http://www.asp101.com/samples/db_trans_aspx.asp">here</a>
to read about and download the source code.
</p>
</body>
</html>