Excel To Linq to SQL

Question

0.00/5 (No votes)

See more:

Hi All,

Please go thru the below code. I am trying to read data from Excel using Linq to Excel and need to push it to SQL Server. It's a type of Data Migration. Now problem is i am able to load Excel data into var. Now need to forward that into SQLCommand. Now, at for each loop where i am trying to read data into SQL Command it is showing Stack overflow exception.

Kindly help.

C#

string[] sheetName = new string[2];
sheetName[0] = "ccipra-v212r0-f1";
sheetName[1] = "ccipra-v212r0-f2";
                    
var excelFile = new LinqToExcel.ExcelQueryFactory(txtNCCIExcel.Text);
ar ncciData = from a in excelFile.Worksheet<Class.NCCIData>(sheetName[0]) select a;
                  
using (System.Data.SqlClient.SqlConnection NCCIConn = SQLDAL.GetSQLConnection())
{
<big>foreach (var cc in ncciData)   // Here Showing error</big>
{
System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();

comm.CommandText = "Insert into NCCIData(Column1, Column2, PriorTo1996, EffectiveDate, DeletionDate, Modifier, PTPEditRationale, Leaf, [Version]) " +
"Values(@col1, @col2, @PT1996, @Effect, @Deletion, @Modifier, @PTP, @Leaf, @Ver)";

comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@col1", cc.Column1.ToString()));
comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@col2", cc.Column2.ToString()));
comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PT1996", cc.PriorTo1996.ToString()));
comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Effect", cc.EffectiveDate.ToString()));
comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Deletion", cc.DeletionDate.ToString()));
comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Modifier", cc.Modifier.ToString()));
comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PTP", cc.PTPEditRationale.ToString()));
comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Leaf", cc.Leaf.ToString()));
comm.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Ver", cc.Version.ToString()));

comm.ExecuteNonQuery();
}
}

Thanks In advance.

Raam.

Posted 31-Oct-15 15:43pm

Charles Shob

Updated 31-Oct-15 18:04pm

v2

Add a Solution

Comments

PIEBALDconsult 31-Oct-15 22:34pm

If this is a regular thing you need to do, SSIS will do it very simply.

Charles Shob 31-Oct-15 22:40pm

It's not one time. It's every time and need to give it to user. Not programmer end.

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)

**Zoltán Zörgő** · Answer 1 · 2015-10-31T20:49:00

1) SSIS is applicable tool. You can even start SSIS package from t-sql: https://www.mssqltips.com/sqlservertip/2992/how-to-execute-an-integration-services-ssis-package-from-a-sql-server-stored-procedure/[^]

2) LinqToExcel[^] is just a non-standard package you can use. It is not actively developed. Thus it might have bugs. I don't think you really need this package based on what you are doing, but if you stick to it, download the package and debug. Or eventually contact the authors.

3) There are many libraries you can use to read data from Excel without office. Like these:
NPOI https://github.com/tonyqus/npoi[^]
ClosedXML http://closedxml.codeplex.com/[^]
ExcelDataReader https://github.com/ExcelDataReader/ExcelDataReader[^]
or others[^]