string strDir = "d:\";
striing strFileName= "test.csv";
DataTable dt = new DataTable();
//CSVファイルのデータを、DataTableに変換
using (OleDbConnection con = new OleDbConnection())
{
con.ConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"text;HDR=Yes;FMT=Delimited\"",
strDir);
string commText = String.Format("SELECT * FROM [{0}]", strFileName);
using (OleDbDataAdapter da = new OleDbDataAdapter(commText, con))
{
da.Fill(dt);
}
}
using (SqlConnection cn = new SqlConnection(SQLserver接続文字列))
{
cn.Open();
SqlTransaction tran = cn.BeginTransaction();
try
{
for (int r = 0; r < dt.Rows.Count; r++)
{
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandText = @"insert into [Product] ( [商品名], [単価] ) values ( @name, @tanka )";
cmd.Parameters.Add("@name", SqlDbType.Nvarchar, 50).Value = dt.Rows[r]["商品名"];
cmd.Parameters.Add("@tanka", SqlDbType.Int).Value = dt.Rows[r]["単価"];
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
}
tran.Dispose();
cn.Close();
}
参考
http://dobon.net/vb/dotnet/file/readcsvfile.html