C# Bulk Update Exception using SQLcommandBuilder

Question

0.00/5 (No votes)

See more:

, +

Hi,

I was trying to update/Insert with sqlcommandBuilder but was getting problem after updating 20 record in 21st there was some sql exception(may be data type,range of value.....) the 21st record was not updating because of some sql exception.because of this all other record was getting rollback.Please guide me to solve this problem.
I am supposing if exception will come then how to handle with out effecting the transaction to rollback.

Any exception x,y,z.I want before exception what ever i inserted/Updated that should persist in data base How to handle this Please Guide me guys

Copied from a comment:

C#

string TableName = "EmpDat";
DataTable orgTable = new DataTable();
orgTable.TableName=TableName;
orgTable.Columns.Add("EmpName", typeof(string));
orgTable.Columns.Add("Desig", typeof(string));
orgTable.Columns.Add("EmpFname", typeof(string));
orgTable.Columns.Add("Salary", typeof(int));
orgTable.Columns.Add("Sex", typeof(string));
orgTable.Columns.Add("Branch", typeof(string));
DataRow dr = orgTable.NewRow();
dr["EmpName"] = "Jack";
dr["Desig"] = "Chawki Daar";
dr["EmpFname"] = "Khain The";
dr["Salary"] = 12;
dr["Sex"] = "H";
dr["Branch"] = "Not Allowed";
orgTable.Rows.Add(dr);
SqlTransaction tran;
SqlConnection con = new SqlConnection("Constr");
con.Open();
tran = con.BeginTransaction();
SqlCommand cmd = new SqlCommand("select * from EmpDetail", con);
cmd.Transaction = tran;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds,"EmpDat");
DataRow[] dr1 = ds.Tables[0].Select("empname='Jack'");
dr1[0]["empName"] = "Good Morning";
orgTable.AcceptChanges();
DataTable f = orgTable.GetChanges(DataRowState.Modified);
// con.Open();
SqlCommandBuilder sqlcmdBuild = new SqlCommandBuilder(da);
da.Update(ds.Tables[0]);

Posted 22-Aug-12 21:07pm

Himanshu Yadav

Updated 23-Aug-12 17:35pm

v5

Add a Solution

Comments

[no name] 23-Aug-12 3:38am

What is the exception you are facing.
write the exception first and code as well where you are facing the exception

Himanshu Yadav 23-Aug-12 3:55am

string TableName = "EmpDat";
DataTable orgTable = new DataTable();
orgTable.TableName=TableName;
orgTable.Columns.Add("EmpName", typeof(string));
orgTable.Columns.Add("Desig", typeof(string));
orgTable.Columns.Add("EmpFname", typeof(string));
orgTable.Columns.Add("Salary", typeof(int));
orgTable.Columns.Add("Sex", typeof(string));
orgTable.Columns.Add("Branch", typeof(string));
DataRow dr = orgTable.NewRow();
dr["EmpName"] = "Jack";
dr["Desig"] = "Chawki Daar";
dr["EmpFname"] = "Khain The";
dr["Salary"] = 12;
dr["Sex"] = "H";
dr["Branch"] = "Not Allowed";
orgTable.Rows.Add(dr);
SqlTransaction tran;
SqlConnection con = new SqlConnection("Constr");
con.Open();
tran = con.BeginTransaction();
SqlCommand cmd = new SqlCommand("select * from EmpDetail", con);
cmd.Transaction = tran;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds,"EmpDat");

DataRow[] dr1 = ds.Tables[0].Select("empname='Jack'");
dr1[0]["empName"] = "Good Morning";

orgTable.AcceptChanges();

DataTable f = orgTable.GetChanges(DataRowState.Modified);

// con.Open();
SqlCommandBuilder sqlcmdBuild = new SqlCommandBuilder(da);
da.Update(ds.Tables[0]);

[no name] 23-Aug-12 14:05pm

"some exception" is not at all helpful.

Himanshu Yadav 23-Aug-12 23:34pm

Hi,
Means any exception x,y,z.I want before exception what ever i inserted/Updated that should persist in data base How to handle this Please Guide me guys

Ali91Asadi 23-Aug-12 16:33pm

Did you write this code with parameters!!!!!!!!!(SQL Injection)
Search in Library.

command.CommandText="SELECT * FROM tbl_book WHERE Book_Name LIKE N'%"+ txt_book_name.Text +"%'"+
" AND Book_Latin_Name LIKE N'%"+txt_book_latin_name.Text+"%'";

Himanshu Yadav 23-Aug-12 23:34pm

Hi,
Means any exception x,y,z.I want before exception what ever i inserted/Updated that should persist in data base How to handle this Please Guide me guys

ZurdoDev 24-Aug-12 10:33am

You want to not rollback when there is an exception? Then don't. Somewhere you must have code that is calling Rollback on your transaction. So, just change how it works.

Himanshu Yadav 25-Aug-12 7:36am

hi,
It is in bulk not by query using for loop.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CS2011 · Answer 1 · 2012-09-04T22:27:00

Solution 1

Try using Try catch and then commit your SqlTransaction in catch block

something like

C#

try
{
  //Your code here;
}
Catch(Exeception ex)
{
 //commit your SqlTransaction here;
}

Posted 4-Sep-12 22:27pm

CS2011

v2

Comments

Himanshu Yadav 5-Sep-12 8:43am

Hi,
ok i agree with u but after exception all the updation will rollback so how to control that?

CS2011 6-Sep-12 13:55pm

Not if you commit it. If you commit it it will save the all the data what was inserted in the table till that point.