ASP.Net/ADO.net Database/Transaction SqlConnection

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

Implicit Transactions using TransactionScope

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Transactions" %>
<%@ Import Namespace="System.Web.Configuration" %>
<script runat="server">
    void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            int categoryID;
            string connectionString = WebConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;            
            using (TransactionScope scope = new TransactionScope())
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {                    
                  categoryID = InsertCategory(connection);
                }
                scope.ruplete();
            }
            lblResult.Text= "Category is written successfully. Category ID= " + categoryID.ToString();
        }
        catch (Exception ex)
        {
            lblResult.Text= "Exception is : " + ex.Message;
        }
    }
    
    int InsertCategory(SqlConnection connection)
    {
        string sql = "Insert into Production.ProductCategory(Name," +
                        "rowguid, ModifiedDate) Values(@Name, @rowguid, @ModifiedDate); SELECT @@IDENTITY";
        connection.Open(); 
        SqlCommand command = new SqlCommand(sql, connection);
        command.rumandType = CommandType.Text;
        SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
        nameParam.Value = txtCategoryName.Text;
        command.Parameters.Add(nameParam);
        SqlParameter guidParam = new SqlParameter("@rowguid", SqlDbType.UniqueIdentifier);
        guidParam.Value = System.Guid.NewGuid();
        command.Parameters.Add(guidParam);
        SqlParameter modifieDateParam = new SqlParameter("@ModifiedDate", SqlDbType.DateTime);
        modifieDateParam.Value = System.DateTime.Now;
        command.Parameters.Add(modifieDateParam);
        int categoryID = Convert.ToInt32(command.ExecuteScalar());  
        return categoryID;      
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Implicit Transactions using TransactionScope</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblCategoryName" runat="server" Text="Category Name:" Width="179px"></asp:Label>        
        <asp:TextBox ID="txtCategoryName" runat="server"/>                
        &nbsp;<asp:Button ID="btnSave" runat="server" Text="Save" Width="92px" OnClick="btnSave_Click"/>        
        <br/><br/>
        <asp:Label ID="lblResult" runat="server" Font-Bold="true" Font-Size="Small" />
    </div>
    </form>
</body>
</html>



Open connection and begin transaction

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
SqlTransaction _Transact;
void page_load(Object Sender, EventArgs E)
{
    
  String ConnectionString = "Server=localhost; Database=pubs; uid=sa; pwd=;";
  String CommandText = "UPDATE authors SET state="PU" WHERE state="UT"";
  String CommandText2 = "UPDATE authors SET au_id="1" WHERE au_id="2"";
    
    SqlConnection _Connection = new SqlConnection(ConnectionString);
    SqlCommand _Command = new SqlCommand();
    
    try{
        _Connection.Open();
        _Transact = _Connection.BeginTransaction();
        
        _Command.Connection = _Connection;
        _Command.Transaction = _Transact;
        
        _Command.rumandText = CommandText;
        _Command.ExecuteNonQuery();
        
        _Command.rumandText = CommandText2;
        _Command.ExecuteNonQuery();
        
        _Transact.rumit();
        
        MessageLiteralControl.Text = "Transaction Successful!";
    }catch (Exception Err){
        _Transact.Rollback();
        MessageLiteralControl.Text = "Transaction Failed!<br/>" + Err.Message;
    }
    finally{
    _Connection.Close();
    }
}
</script>
<html>
  <body>
    <form runat="server" ID="Form1">
      <p>
        <asp:Label id="MessageLabel" runat="server" EnableViewState="false"></asp:Label>
      </p>
    </form>
  </body>
</html>



Using Explicit Transactions using CommittableTransaction

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Transactions" %>
<%@ Import Namespace="System.Web.Configuration" %>
<script runat="server">
    void btnSave_Click(object sender, EventArgs e) {
        CommittableTransaction trans = new CommittableTransaction();
        try {
            string connectionString =  WebConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectionString)) {
                string sql = "Insert into Production.ProductCategory(Name,rowguid, ModifiedDate) Values(@Name, @rowguid, @ModifiedDate)";
                connection.Open(); 
                SqlCommand command = new SqlCommand(sql, connection);
                command.rumandType = CommandType.Text;
                SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
                nameParam.Value = txtCategoryName.Text;
                command.Parameters.Add(nameParam);
                SqlParameter guidParam = new SqlParameter("@rowguid", SqlDbType.UniqueIdentifier);
                guidParam.Value = System.Guid.NewGuid();
                command.Parameters.Add(guidParam);
                SqlParameter modifieDateParam = new SqlParameter("@ModifiedDate", SqlDbType.DateTime);
                modifieDateParam.Value = System.DateTime.Now;
                command.Parameters.Add(modifieDateParam);
                
                connection.EnlistTransaction(trans);
                command.ExecuteNonQuery();
                trans.rumit();
            }
            lblResult.Text = "Category is written successfully";            
        }
        catch (Exception ex)
        {
            trans.Rollback();
            lblResult.Text = "Exception is : " + ex.Message;            
        }
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Using Explicit Transactions using CommittableTransaction</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblCategoryName" runat="server" Text="Category Name:" Width="179px"></asp:Label>        
        <asp:TextBox ID="txtCategoryName" runat="server"/>               
        &nbsp;
        <asp:Button ID="btnSave" runat="server" Text="Save" Width="92px" OnClick="btnSave_Click"/>        
        <br/><br/>
        <asp:Label ID="lblResult" runat="server" Font-Bold="true" Font-Size="Small" />
    </div>
    </form>
</body>
</html>



Working with Transactions with an SQL Server Database

<%@ Page Language=VB Debug=true %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script runat=server>
Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
    Dim DBConn as SQLConnection
    Dim DBDelete As New SQLCommand
    Dim DBTrans As SQLTransaction
"        DBConn = New SQLConnection("server=localhost;" _
 "           & "Initial Catalog=TT;" _
  "          & "User Id=sa;" _
   "         & "Password=yourpassword;")
    DBConn = New SQLConnection("Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=nfexuser;Password="password";")
    DBConn.Open()
    DBTrans = DBConn.BeginTransaction()
    DBDelete.Connection = DBConn
    DBDelete.Transaction = DBTrans
    DBDelete.rumandText = "Delete From Employee " _
        & "Where ID = 1"
    DBDelete.ExecuteNonQuery()
    DBDelete.rumandText = "Delete From Employee " _
        & "Where ID = 2"
    DBDelete.ExecuteNonQuery()
    DBTrans.RollBack()
    lblMessage.Text = "No action was taken."
    "DBTrans.rumit()
End Sub
</SCRIPT>
<HTML>
<HEAD>
<TITLE>Working with Transactions with an SQL Server Database</TITLE>
</HEAD>
<Body LEFTMARGIN="40">
<form runat="server">
<BR><BR>
<asp:label
    id="lblMessage"
    runat="Server"
/>
</form>
</BODY>
</HTML>
Источник — «http://nfex.ru/index.php?title=ASP.Net/ADO.net_Database/Transaction_SqlConnection&oldid=1649»