使用存储过程时如何在asp.net中执行回滚 [英] How to perform Rollback in asp.net when using Stored Procedure

问题描述

我想单击一次按钮即可将数据插入12个不同的表中.为此，我正在使用单个存储过程.但是我的问题是当我这样做时，如果有任何异常发生，我的数据将被部分插入，即值被插入到某些表中，而某些保持为空，并且由于所有相互关联而出现此问题.因此想知道是否有任何方法可以执行回滚，以便在发生任何异常时回滚整个查询，并且不会在任何表中插入数据.这是我当前用于插入值的代码.

I want to insert data into 12 different tables on a single click of a button. For this I am using single stored procedure. But my problem is when I am doing this and if there is any exception occurs my data is getting inserted partially i.e values is getting inserted in some tables and some remains empty and due to this problem occurs since all are related to one another. So wanted to know is there any way to perform Rollback so that if any exception occurs entire query is rolled back and data is not inserted in any of the table. This is the code I am currently using for inserting values.

public int Sp_InsertUpdateDelete(string s, SqlParameter[] spa)
{
    SqlConnection sc = new SqlConnection(cs);
    sc.Open();
    SqlCommand scm = new SqlCommand(s, sc);
    scm.CommandType = CommandType.StoredProcedure;
    foreach (SqlParameter sql in spa)
    {
        scm.Parameters.Add(sql);
    }
    int k = scm.ExecuteNonQuery();
    sc.Close();
    return k;

}



protected void btnHostingSubmit_Click(object sender, EventArgs e)
{
    string select = "select * from tbl_Hosting where Customer_Id='" + ddlCustomerName.SelectedValue + "'";
    DataSet s = gs.select(select);
    if (s.Tables[0].Rows.Count > 0)
    {
        Response.Write("<script>alert('Customer Already Exist');</script>");
    }
    else
    {
        if (ddlHosting.SelectedValue == "Yes")
        {
            SqlParameter[] spa = new SqlParameter[29];
            spa[0] = new SqlParameter("@Customer_Id", Convert.ToInt16(ddlCustomerName.SelectedValue));
            spa[1] = new SqlParameter("@Type", 2);
            //Hosting
            if (txtHostingSDate.Text == "" || txtHostingSDate.Text == null)
            {
                spa[2] = new SqlParameter("@Hosting_start_date", null);
            }
            else
            {
                spa[2] = new SqlParameter("@Hosting_start_date", Convert.ToDateTime(txtHostingSDate.Text));
            }
            if (txtHosingEDate.Text == "" || txtHosingEDate.Text == null)
            {
                spa[3] = new SqlParameter("@Hosting_end_date", null);
            }
            else
            {
                spa[3] = new SqlParameter("@Hosting_end_date", Convert.ToDateTime(txtHosingEDate.Text));
            }
            spa[4] = new SqlParameter("@Hosting_provider", ddlHostingPro.SelectedItem.ToString());
            spa[5] = new SqlParameter("@Hosting_type", ddlHostingType.SelectedItem.ToString());
            spa[6] = new SqlParameter("@Hosting_server", ddlHostingServer.SelectedItem.ToString());
            spa[7] = new SqlParameter("@Hosting_total_id", Convert.ToInt16(txtHostingId.Text));
            spa[8] = new SqlParameter("@Hosting_mail_tracking", ddlHostingMailTracking.SelectedItem.ToString());
            spa[9] = new SqlParameter("@Hosting_mail_tracking_users", Convert.ToInt16(txtHostingMtUser.Text));
            spa[10] = new SqlParameter("@Hosting_dns", ddlHostingDns.SelectedItem.ToString());
            spa[11] = new SqlParameter("@Hosting_mail", ddlHostingMail.SelectedItem.ToString());
            spa[12] = new SqlParameter("@Hosting_web", ddlHostingWeb.SelectedItem.ToString());
            spa[13] = new SqlParameter("@Hosting_manage_dns", ddlHostingMngDns.SelectedItem.ToString());

            if (ddlHostingDns.SelectedValue == "No" && (ddlHostingMail.SelectedValue == "Yes" || ddlHostingWeb.SelectedValue == "Yes"))
            {
                spa[14] = new SqlParameter("@Hosting_ns1", txtNS1.Text);
                spa[15] = new SqlParameter("@Hosting_ns2", txtNS2.Text);
            }
            else
            {
                spa[14] = new SqlParameter("@Hosting_ns1", ddlHostingNS1.SelectedItem.ToString());
                spa[15] = new SqlParameter("@Hosting_ns2", ddlHostingNS2.SelectedItem.ToString());
            }


            spa[16] = new SqlParameter("@Hosting_rec_ip", txtHostingARecordIp.Text);
            spa[17] = new SqlParameter("@Hosting_mx_rec1", txtMXRecord1.Text);
            spa[18] = new SqlParameter("@Hosting_mx_rec2", txtMXRecord2.Text);
            spa[19] = new SqlParameter("@Hosting_mx_ip1", txtHostingMxIp1.Text);
            spa[20] = new SqlParameter("@Hosting_space", ddlHostingSpace.SelectedItem.ToString());
            spa[21] = new SqlParameter("@Hosting_mx_ip2", txtHostingMxIp2.Text);
            spa[22] = new SqlParameter("@Hosting_data_transfer", ddlhostingDataTrans.SelectedItem.ToString());
            spa[23] = new SqlParameter("@Hosting_manage_dns_amt", txtHostingMangDnsAmt0.Text);
            spa[24] = new SqlParameter("@Hosting_amt", txtHostingAmt0.Text);
            spa[25] = new SqlParameter("@Hosting_c_ns1", txtHostingNS1.Text);
            spa[26] = new SqlParameter("@Hosting_c_ns2", txtHostingNS2.Text);
            spa[27] = new SqlParameter("@Hosting_c_ns3", txtHostingNS3.Text);
            spa[28] = new SqlParameter("@Hosting_c_ns4", txtHostingNS4.Text); 
int k = gs.Sp_InsertUpdateDelete("Sp_Hosting", spa);  
if (k > 0)
            {
                Response.Write("<script>alert('Hosting Added Success');</script>");
            }
            Clear();

        }

推荐答案

using(SqlConnection conn = new SqlConnection())
{
   try
   {
    conn.Open();

    SqlTransaction tran = conn.BeginTransaction("Transaction1");
Cmd = new SqlCommand(sQuery, Conn);
Cmd.Transaction = tran;
    //Your Code

    tran.Commit(); //both are successful
   }
   catch(Exception ex)
   {
      //if error occurred, reverse all actions. By this, your data consistent and correct
      tran.Rollback();
   }
}

https://msdn.microsoft.com/en-us/library/a90c30fy.aspx

这篇关于使用存储过程时如何在asp.net中执行回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！