Click here to Skip to main content
15,440,306 members
Please Sign up or sign in to vote.
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
Updated 31-Oct-15 18:04pm
v2
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

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[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900