Skip to main content

How to use transaction in sqlbulk in asp.net

There are many situation where you need to use transactions in order to avoid incorrect updation or half updation in the database.SO we use transaction that either all data updated or nothing will effect.
Here is the code :

 SqlTransaction trans;
        if (con.State == ConnectionState.Closed)
            con.Open();
        trans = con.BeginTransaction();
        try
        {
            DataTable dtCourses = new DataTable();
            dtCourses.Columns.Add(new DataColumn("RegistrationNumber", typeof(long)));
            dtCourses.Columns.Add(new DataColumn("CurrentTerm", typeof(string)));
            dtCourses.Columns.Add(new DataColumn("RegistrationForTerm", typeof(string)));
            dtCourses.Columns.Add(new DataColumn("RegistrationStatus", typeof(string)));
            dtCourses.Columns.Add(new DataColumn("AmountPaid", typeof(int)));
            dtCourses.Columns.Add(new DataColumn("DDNumber", typeof(string)));
            dtCourses.Columns.Add(new DataColumn("DDAmount", typeof(string)));
            dtCourses.Columns.Add(new DataColumn("DDDate", typeof(DateTime)));
            dtCourses.Columns.Add(new DataColumn("BankName", typeof(string)));
            dtCourses.Columns.Add(new DataColumn("Remarks", typeof(string)));
            dtCourses.Columns.Add(new DataColumn("EntryBy", typeof(string)));
            dtCourses.Columns.Add(new DataColumn("EntryDateTime", typeof(string)));
            foreach (GridDataItem item in RadGrid1.MasterTableView.Items)
            {
                if ((item.FindControl("RegistrationStatusLabel") as Label).Text == "" && (item.FindControl("RadioButtonList1") as RadioButtonList).SelectedIndex != -1)
                {
                    DataRow dr = dtCourses.NewRow();
                    dr["RegistrationNumber"] = (item.FindControl("RegisterationNumberLabel") as Label).Text;
                    dr["CurrentTerm"] = (item.FindControl("CurrentTermLabel") as Label).Text;
                    dr["RegistrationForTerm"] = (item.FindControl("RegistrationForTermLabel") as Label).Text;
                    dr["RegistrationStatus"] = (item.FindControl("RadioButtonList1") as RadioButtonList).SelectedValue.ToString();
                    if ((item.FindControl("RadioButtonList1") as RadioButtonList).SelectedValue.ToString() != "Skip")
                        dr["AmountPaid"] = (item.FindControl("txtamount") as TextBox).Text;
                    else
                        dr["AmountPaid"] = 0;
                    if ((item.FindControl("RadioButtonList1") as RadioButtonList).SelectedValue.ToString() != "Skip")
                        dr["DDAmount"] = (item.FindControl("txtddamount") as TextBox).Text;
                    else
                        dr["DDAmount"] = 0;
                    dr["DDNumber"] = (item.FindControl("txtddnumber") as TextBox).Text;
                    if ((item.FindControl("RadioButtonList1") as RadioButtonList).SelectedValue.ToString() != "Skip")
                        dr["DDDate"] = (item.FindControl("rdpdddate") as RadDatePicker).SelectedDate.Value;
                    else
                        dr["DDDate"] = DBNull.Value;
                    if ((item.FindControl("RadioButtonList1") as RadioButtonList).SelectedValue.ToString() != "Skip")
                        dr["BankName"] = (item.FindControl("txtbankname") as DropDownList).SelectedItem.ToString();
                    else
                        dr["BankName"] = DBNull.Value;
                    if ((item.FindControl("RadioButtonList1") as RadioButtonList).SelectedValue.ToString() != "Skip")
                        dr["Remarks"] = (item.FindControl("txtremarks") as TextBox).Text;
                    else
                        dr["BankName"] = DBNull.Value;
                    dr["EntryBy"] = HiddenFieldLoginName.Value.ToString();
                    dr["EntryDateTime"] = DateTime.Now.ToString();
                    dtCourses.Rows.Add(dr);
                }
            }
            SqlBulkCopy SaveData = new SqlBulkCopy(con, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers, trans);
            SaveData.ColumnMappings.Add("RegistrationNumber", "RegistrationNumber");
            SaveData.ColumnMappings.Add("CurrentTerm", "CurrentTerm");
            SaveData.ColumnMappings.Add("RegistrationForTerm", "RegistrationForTerm");
            SaveData.ColumnMappings.Add("RegistrationStatus", "RegistrationStatus");
            SaveData.ColumnMappings.Add("AmountPaid", "AmountPaid");
            SaveData.ColumnMappings.Add("DDNumber", "DDNumber");
            SaveData.ColumnMappings.Add("DDAmount", "DDAmount");
            SaveData.ColumnMappings.Add("DDDate", "DDDate");
            SaveData.ColumnMappings.Add("BankName", "BankName");
            SaveData.ColumnMappings.Add("Remarks", "Remarks");
            SaveData.ColumnMappings.Add("EntryBy", "EntryBy");
            SaveData.ColumnMappings.Add("EntryDateTime", "EntryDateTime");
            SaveData.DestinationTableName = "OdlStudentReRegistration";
            SaveData.WriteToServer("YOUR TABLE NAME");
            trans.Commit();
            dtCourses.Clear();
            DisplayAJAXMessage(this, "Submitted Successfuly ");

Comments

Post a Comment

Popular posts from this blog

show image in asp.net form from database

In order to show image from database to webform first add an image control in webpage.   <asp:Image ID="imgStudentImage" runat="server" Height="157px" Width="160px" /> Then add new webform to read image from database for example displayimage.aspx and in code behind model write the following code. public string str = //your string     public SqlConnection conn;     public SqlCommand sql = new SqlCommand();     public SqlDataReader dr;     public string strSQL;     protected void Page_Load(object sender, EventArgs e)     {         conn = new SqlConnection(str);         conn.Open();         long id = Convert.ToInt64(Request.QueryString["mId"]);         strSQL = "";         strSQL = strSQL + " SELECT snap from S...
Post a Comment
Read more

Export Doc,Access,Image,CSV,Excel,Pdf,XML,HTML,Text,Print of Gridview in Asp.net

First import itextsharp.dll in the solution and use three namespces (basically for pdf) using iTextSharp.text; using iTextSharp.text.pdf; using iTextSharp.text.html.simpleparser;          Then Use the following code :- on each button  click  protected void Page_Load(object sender, EventArgs e)         {             if (!Page.IsPostBack)             {                 BindGridDetails(GridView1);             }         }                 protected DataTable BindGridDetails(GridView GridView1)         {   ...
1 comment
Read more

im2col function in MATLAB explanation

Let us suppose we have A=[1 1;2 2] 1 1 2 2 ... ok Now img2col syntax  == im2col(A,[m n],block_type) Where block type = ('distinct','sliding') distinct places 0's in the final output,,,for padding, sliding,,repeats from the very first matrix element. after all elements read out. Now.. Now [m n] -- very important .... Number of Rows to show = M*N (if m=2 ,, n=2 then each column has 4 elements..) and Number of element fetch to repeat is M in row... That means,, OUTPUT OF THIS - im2col(A,[2 2], 'sliding') ans =      1      2      1      2 -- if  A=[1 1 1;2 2 2] A =      1     1     1      2     2     2 then im2col(A,[2 2], 'sliding') ans = contains 4 rows....as 2*2 ...   and fetch 2 elements as M=2 here... and slidin...
Post a Comment
Read more