本文介绍了Excel到Linq到SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 大家好, 请通过以下代码。我试图使用Linq到Excel从Excel读取数据,并需要将其推送到SQL Server。这是一种数据迁移。现在问题是我能够将Excel数据加载到var中。现在需要将其转发到SQLCommand中。现在,在我尝试将数据读入SQL命令的每个循环中,它显示堆栈溢出异常。 请帮助。 string [] sheetName = new string [ 2 ]; sheetName [ 0 ] = ccipra- v212r0-F1\" ; sheetName [ 1 ] = ccipra- v212r0-F2\" ; var excelFile = new LinqToExcel.ExcelQueryFactory(txtNCCIExcel.Text); ar ncciData = 来自 excelFile.Worksheet< Class.NCCIData>(sheetName [ 0 ])选择 a; 使用(System.Data.SqlClient.SqlConnection NCCIConn = SQLDAL.GetSQLConnection()) {< big> foreach ( var cc ncciData) // 此处显示错误< / big> { System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand( ); comm.CommandText = 插入NCCIData(Column1,Column2,PriorTo1996,EffectiveDate,DeletionDate ,修饰符,PTPEditRationale,Leaf,[Version]) + 值(@ col1 ,@ col2,@ PT1996,@ Effect,@ Deletion,@ Modeifier,@ 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(); } } 先谢谢。 Raam。解决方案 1)SSIS是适用的工具。您甚至可以从t-sql启动SSIS包: https://www.mssqltips.com/sqlservertip/2992/how-to-execute-an-integration-services-ssis-package-from-a-sql-server-stored-程序/ [ ^ ] 2)LinqToExcel [ ^ ]您可以使用的非标准包装。它没有积极开发。因此它可能有错误。我不认为你真的需要这个包基于你正在做什么,但如果你坚持下去,下载包和调试。或者最终联系作者。 3)有许多库可以用来从Excel中读取数据而无需办公室。像这样: NPOI https://github.com/tonyqus/npoi [ ^ ] ClosedXML http://closedxml.codeplex.com/ [ ^ ] ExcelDataReader https://github.com/ExcelDataReader/ExcelDataReader [ ^ ] 或其他人 [ ^ 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.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. 解决方案 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[^] 这篇关于Excel到Linq到SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-30 06:13