Kobarin's Development Blog

C#やASP.NET、公開APIなどについての記録です。

CSVファイルのデータをSQLserverにインポート(OleDb編)

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