How to check error data that will be upload and roll back transaction?

Question

0.00/5 (No votes)

See more:

Hi, I have a button import to upload csv file data into sql server.How can I check the error if there are one of the row cannot commit? It will throw the exception. But how to know which row data have the error? Then I want roll back the transaction.

I am using this code to upload data into sql server.

C#

private UCA.Common.DataControl.DBUtility dbu = UCA.Common.DataControl.DBOperatorFactory.GetDBOperator(System.Configuration.ConfigurationManager.AppSettings["DBType"].ToString());

C#

string[] cols3 = { "Creator", "Creation_Time", "Date" };
string[] vals3 = { this.UserID, DateTime.Now.ToString, DateTime.Now.ToString}
dbu.insertRow("Asset_Flow_History", cols3, vals3);

Posted 25-Dec-13 21:54pm

hueikar

Add a Solution

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)

Rana Zarour · Answer 1 · 2013-12-25T22:03:00

Solution 1

i think you can submit all rows in single transaction if something goes wrong you can rollback the transaction if not you can commit your answer

SqlTransaction transaction = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);//choose your isolationLevel

then add this transaction to your SqlCommand

hope this help you

Posted 25-Dec-13 22:03pm

Rana Zarour

Comments

hueikar 26-Dec-13 4:24am

But how can I check which row data is not successfully commit? So the user can go edit that particular row data.

Rana Zarour 26-Dec-13 4:42am

you will insert row by row as i can see
here dbu.insertRow("Asset_Flow_History", cols3, vals3);

you can add try catch block here and catch the exception then rollback the transaction and inform user

also you can validate your data before insert it to database maybe this will be another solution