mechanism to import excel data in SQL database table dynamically

Question

0.00/5 (No votes)

See more:

we have an application which generates the report into excel format.I have a requirement to develop an application to import the excel file report into a SQL database tables using C# windows application.

There are few points to keep in mind.

1. The application should be dynamic to cover a new column added or a existing column removal in the report. for example i have a report for student details having Roll No, Age, Name in the excel file and my database table Student is also having column like ROLLNo,Age,Name. So if in future they add a new column to the report say "Student Department" then the application should be able to cover this changes without any code changes in the existing application. The application should be configured to cover this change dynamically.

2. The name of the columns in excel and sql database table would be different

Please suggest is there a way to achieve this in c# using winform application

Posted 18-Aug-13 18:45pm

superselector

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)

_Asif_ · Answer 1 · 2013-08-18T18:58:00

Solution 1

It should be 3 step process

1. You can induce the intelligence at stored procedure level which actually inserts the data in your tables
2. You can store mapping between excel columns and stored procedure in an xml file
3. Your presentation layer would be simple that is reading the xml report and invoking the xml

Thanks
Asif

Posted 18-Aug-13 18:58pm

_Asif_

Comments

superselector 19-Aug-13 1:40am

Hi Thanks for your response.. but how am i going to check for new columns in DB tables. shall i check for the existense of table in the column mapping xmls and create the column. and how can handle the removal of column from database without affecting the existing data.