import excel read row by row then insert into database c# with softartisans ExcelWriter

Question

trying to import bulk data from excel sheet with softartisans ExcelWriter.i do not find much sample code to read row by row and insert into database can i get sample code to read and insert row by row in c#

imported excel file saved in temp location and started reading records

try

        {

            string temp_file_name = StateManager.NetworkID + DateTime.Now.Ticks.ToString() + ".xls";

            this.import_file_upload_box.PostedFile.SaveAs(Server.MapPath("temp_files") + "\\" + temp_file_name);

            string strConn;

            recct = ThisWorkbook.Sheets("Project_Details").Range("A2", ThisWorkbook.Sheets("Project_Details").Range("A2").End(xlDown)).Rows.Count;



            ExcelApplication xla = new ExcelApplication();

            Workbook wb = xla.Create(ExcelApplication.FileFormat.Xlsx);

            Worksheet ws = wb.Worksheets[0];

        }

        catch

        {



        }

Actual result should read row by row on imported Excel

score 0 · Answer 1 · answered Aug 06 '19 at 12:41

0

why don't you do a bulk insert like the example below?

the code below can be found on this link

INSERT INTO dbo.ImportTest 

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 

'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]

answered Aug 06 '19 at 12:41

user2387215

1
3

need to validate each column .if its valid then only it has to insert – kumarjcet Aug 06 '19 at 13:02

score 0 · Answer 2 · answered Aug 06 '19 at 13:21

I generally use OLEDB for this.
- Import it into a DataTable
- Do validation
- Write to Database

public static DataTable LoadCSV(string fileName)
        {
            string sqlString = "Select * FROM [" + fileName + "];";
            string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                + InboundSourceFilePath + ";" + "Extended Properties='text;HDR=NO;'";
            DataTable theCSV = new DataTable();

            using (OleDbConnection conn = new OleDbConnection(conStr))
            {
                using (OleDbCommand comm = new OleDbCommand(sqlString, conn))
                {
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(comm))
                    {
                        adapter.Fill(theCSV);
                    }
                }
            }
            return theCSV;
        }

import excel read row by row then insert into database c# with softartisans ExcelWriter

2 Answers2