CSV 导入到 Datatable

 

first step is to remove the noise. both try/catches are useless. the first one will never throw because you are simply concatenating strings. the second will provides no value it's the same as not catching at all.

i believe you want to use the name of the worksheet for the tablename, not the filename. and once the datatable is filled you then need to save those records to the database.

var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Text;HDR=Yes;FMT=Delimited"""); 
using(var connection = new OleDbConnection(connectionString)) 
using(var command = connection.CreateCommand()) 
{ 
    connection.Open(); 
    command.CommandText = "select * from [Sheet1]"; 
 
    var table = new DataTable(); 
    using(var reader = command.ExecuteReader()) 
    { 
         table.Load(reader); 
         return table; 
    } 
} 

 

 

then you can use this table to populate the database

var connectionString = "sql connection string"; 
using(var connection = new SqlConnection(connectionString)) 
{ 
    connection.Open(); 
    using(var tx = connection.BeginTransaction()) 
    { 
        try 
        { 
            foreach(DataRow row in table.Rows) 
            { 
                using(var command = connection.CreateCommand()) 
                { 
                   command.CommandText = "insert into table (columns...) values (@p1,...)"; 
                   //add parameters to command 
                   command.ExecuteNonQuery(); 
                }  
             } 
             tx.Commit(); 
        } 
        catch 
        { 
           tx.RollBack(); 
           throw; 
        } 
    } 
}