将csv文件读取到数据表并传递到存储过程

Question

我有一个csv文件，它有80多列和3000多行。我的列都在标题中(name1name2name3name4(，这很好。我的错误是它没有计算我的所有行并将其添加到数据库中。。我希望我的流读取器或其他推荐的头跳过第一条记录，因为它充满了列名。然后添加数据行。

using (StreamReader sr = new StreamReader(filename))
{
//this assume the first record is filled with the column names
string[] headers = sr.ReadLine().Split(',');
foreach (string header in headers)
{
tvp.Columns.Add(header);
}
while (!sr.EndOfStream)
{
string[] rows = sr.ReadLine().Split(',');
//Create a new row
DataRow dr = tvp.NewRow();
for (int i = 0; i < headers.Length; i++)
{
dr[i] = rows[i];
}
tvp.Rows.Add(dr);
// Console.WriteLine(tvp);
}               
}
//Passing a Table-Valued Parameter to a Stored Procedure
using (SqlConnection con = new SqlConnection(Connectionstring))
{
con.Open();
//Execute the cmd
// Configure the command and sql parameter. 
SqlCommand cmd = new SqlCommand("dbo.DataTable", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 5000;
// Create a DataTable with the modified rows.  
DataTable addedCategories = tvp.GetChanges(DataRowState.Added);
// these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", tvp);
tvparam.SqlDbType = SqlDbType.Structured;
tvparam.TypeName = "dbo.DataTableType";
//SqlParameter parameter = new SqlParameter("@dt", SqlDbType.Structured)
cmd.Parameters.AddWithValue("@fy", drlFY.SelectedValue);
cmd.Parameters.AddWithValue("@offid", drlOfficeID.SelectedValue);
cmd.Parameters.AddWithValue("@updateBy", "9999");
cmd.Parameters.AddWithValue("@sourcefile", strSrcFile);
// Execute the command
cmd.ExecuteNonQuery();
con.Close();

Answer 1

我不清楚您对从CSV文件导入数据的具体要求，但批量导入是您的一个选择。请访问：https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server?view=sql-服务器-2017