How Do I Update The Value Of Sql Database From Excel Spread Sheet Using Asp.Net And Oledb

Question

1.00/5 (1 vote)

See more:

My SQL Database Has field name [Name],[Roll No],[Score] . The Score Field is NULL. i want to select all the row of Excel sheet i.e [Roll No],[Score]. And after the selection i want to iterate to the loop and Check if the Roll No of the excel and Roll No in sql database is equal. if it is equal then Update and Set the Score that of Excel to the Database.

HTML

Roll No              Score
  1                      A
  2                      B
  3                      A

Code Snippet

C#

Query = string.Format("select * from [{0}] , "Sheet1$");
DataSet ds = new DataSet();
Econ.Open();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
try
{
 oda.Fill(ds);
}
catch
{
 ViewData["message"] = "Your excel document does not contain data as per requirement.";
return View();
}
DataTable Exceldt = ds.Tables[0];

connection();
//creating object of SqlBulkCopy 
SqlBulkCopy objbulk = new SqlBulkCopy(con.ConnectionString);
objbulk.DestinationTableName = "[profile].applicant_profile";

Here goes my loop and i have confusion to implement the logic here.. Please    help to implement the loop and logic to compare between excel row and Sql database.

Posted 17-Jul-15 5:06am

Aakash Bashyal

Updated 17-Jul-15 5:11am

v2

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)

Abhipal Singh · Answer 1 · 2015-07-17T23:31:00

There is an easy way out. Instead of you comparing the DataTables row by row. Let your queries do it for you. What you can do is:

1) Fetch all the roll numbers from SQL database's table having Score as NULL

SQL

SELECT RollNo FROM [profile].applicant_profile
WHERE Score IS NULL

2) Convert the result set of the above query (in Step 1) into a comma separated(csv) list.

3) Once you have this data in csv, you can query you excel sheet to provide you score of only those roll numbers (whose scores are null in database) which you fetched in your previous query.

SQL

SELECT RollNo, Score FROM Sheet1$
WHERE RollNo IN ('your csv from point 2')

4) The above query will provide you data which you can straight away update in your database. Just iterate through the data (from point 3) and update the database.
Pseudo code below:

SQL

foreach(row in datatable-from-point-3)
{
UPDATE [profile].applicant_profile
SET Score = 'row.score'
WHERE RollNo = 'row.RollNo'
}