如何在ado.net中使用sqltransaction [英] how to use sqltransaction in ado.net

问题描述

在交易中收到错误...我创建了表Tbl_Invoice（Invoice_Id，Product_Name，Amount，Product_Quantity，Product_Price）和表Tbl_Product（Product_Id，Product_Name，Product_Quantity，Product_Amount）



现在我有一个按钮，我需要通过它来添加文本框（产品名称，数量，产品数量，产品价格）的数据，这些数据由用户运行时填充到网格中，并且我填充了3行网格和当我点击保存按钮

getting an error in transaction ...i have created table Tbl_Invoice (Invoice_Id ,Product_Name,Amount,Product_Quantity,Product_Price) and table Tbl_Product(Product_Id,Product_Name,Product_Quantity,Product_Amount)

now i have one button through which i need to add the data of textboxes(Product Name,Amount,Product Quantity,Product Price) which is filled by user runtime into the grid and i made filled 3 rows of grid and when i click on save button

protected void save_Click(object sender, EventArgs e)
    {
        foreach (GridViewRow g1 in grd.Rows)
        {


            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ConnectionString);

            con.Open();
            SqlTransaction Trans = con.BeginTransaction();

            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.Transaction = Trans;

            try
            {
                if (Convert.ToInt32(g1.Cells[4].Text) > 10)
                {//created exception on qty>10 if filled by user so that it should roll back the whole transaction
                    throw (new Exception("not greater than 10"));
                }
                com.CommandText = "insert into Tbl_Invoice(Product_Name,Amount,Product_Quantity,Product_Price) values ('" + g1.Cells[2].Text + "'," + Convert.ToDouble(g1.Cells[3].Text) + "," + Convert.ToInt32(g1.Cells[4].Text) + "," + Convert.ToDouble(g1.Cells[5].Text) + ")";
                com.CommandText = "update Tbl_Product  set Product_Quantity=Product_Quantity+" + Convert.ToInt32(g1.Cells[4].Text) + ", Product_Amount=Product_Amount+" + Convert.ToDouble(g1.Cells[3].Text) + " where Product_Name='" + g1.Cells[2].Text + "'";
                
                com.ExecuteNonQuery();
                Trans.Commit();
                Response.Write("both are written to tables");
            }
            catch (Exception ep)
            {
                Trans.Rollback();
                Response.Write(ep.ToString());
                Response.Write("neither are written to tables");
            }
            finally
            {
               
                con.Close();
            }   }    }

运行时在网格中添加3行（产品名称，金额，产品数量，产品价格）



第一排衬衫，125,1,125

第二排领带，50,2,25

第三排喘气，1500,12,50



这些是虚拟值我放在这里是格栅

现在按照例外我创建的数量> 10应该回滚整个事务，但它只回滚最后一行而不是整个事务，因为根据酸属性应该执行所有查询或不执行查询。它影响了Tbl_Invoice和Tbl_Product

前两行是不希望..我错过了什么或写错了

请告诉，问候

runtime 3 rows were added in grid repective (Product Name,Amount,Product Quantity,Product Price)

first row Shirt,125,1,125
second row tie,50,2,25
third row pant,1500,12,50

these are dummy values i put here which is in grid
now as per exception i created qty>10 should roll back whole transaction but it only roll back last row not the whole transaction as per acid properties either should execute all queries or no query .It has affected Tbl_Invoice and Tbl_Product
with first 2 rows which is not desired ..what i missed or written wrong
pls tell,regards

推荐答案

这里是使用SqlTransaction类执行多个查询的代码。

Hi,
here is the code to use SqlTransaction class for multiple query execution.

SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=sa;");
myConnection.Open();

// Start a local transaction
SqlTransaction myTrans = myConnection.BeginTransaction();

SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "delete * from Region where RegionID=101";

// Attempt to commit the transaction. 
myCommand.ExecuteNonQuery();
myTrans.Commit();
Response.Write("Both records are written to database.");
}
catch (Exception ep)
{
// Attempt to roll back the transaction. 
myTrans.Rollback();
Response.Write(ep.ToString());
Response.Write("Neither record was written to database.");
}
finally
{
myConnection.Close();
}

------ -----------------------------



或for更多详细信息请关注此URL

http：// msdn .microsoft.com / zh-cn / library / system.data.sqlclient.sqltransaction.aspx

这是完整的文章您。这应该可以帮到你：



初学者在ADO.NET中理解事务和TransactionScope的教程 [ ^ ]

Here is the complete article for you. This should get you started:

A Beginner''s Tutorial for Understanding Transactions and TransactionScope in ADO.NET[^]

这篇关于如何在ado.net中使用sqltransaction的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！