[C#]-讀取excel檔案寫入MS SQL Server

最近寫案子有用到C#去讀取excel檔案再寫入MS SQL Server，
這是滿常用到的功能所以在這裡記錄一下。

首先是利用oledb讀取excel檔，再利用OleDbDataAdapter將資料填入DataSet。
OleDbDataAdapter 是 DataSet 和資料來源之間的橋接器，用來擷取和儲存資料。OleDbDataAdapter 會提供這個橋接器，方法是使用 Fill 從資料來源將資料載入 DataSet，並使用 Update 將 DataSet 中所做的變更傳送至資料來源。

OLE DB簡介
OLE DB 是一種以 COM 為基礎、用來存取資料的應用程式發展介面 (Application Programming Interface，API)。OLE DB 可存取以任何格式所儲存的資料 (資料庫、試算表、文字檔等等)，只要該格式能夠使用 OLE DB 提供者 (Provider)。每個 OLE DB 提供者都會公開特定資料來源類型 (例如 SQL Server 資料庫、Microsoft Access 資料庫或 Microsoft Excel 試算表) 的資料。

常用的Provider列表
Database                Provider
----------------------------------------------------
Ms SQL server       Provider=SQLOLEDB
Oracle                   Provider=MSDAORA
Ms Access 2003    Provider=Microsoft.jet.OLEDB.4.0
Ms Access 2007    Provider=Microsoft.ACE.OLEDB.12.0
Ms Excel 2003       Provider=Microsoft.jet.OLEDB.4.0
Ms Excel 2007       Provider=Microsoft.ACE.OLEDB.12.0
IBM DB2                Provider=DB2OLEDB

private void button1_Click(object sender, EventArgs e)
{
  // Show the dialog and get result.
  DialogResult result = openFileDialog1.ShowDialog(); 
  if (result == DialogResult.OK) // Test result.
  {
    // get excel file name
    string fileName = openFileDialog1.FileName;
    textBox1.Text = fileName;
    button2.Enabled = true;
    // import excel into datagridview
    string excelConn = "Provider = Microsoft.Jet.OLEDB.4.0 ; 
           Data Source = " + textBox1.Text + ";
           Extended Properties = 'Excel 8.0;HDR=YES'";
    string strExcelSelect = "SELECT * FROM [Sheet1$]";
    OleDbDataAdapter adapter = 
          new OleDbDataAdapter(strExcelSelect, excelConn);
    DataSet dataSet = new DataSet();
    adapter.Fill(dataSet, "ExcelInfo");
    dataGridView1.DataSource = dataSet.Tables["ExcelInfo"].DefaultView;
  }
}

再來是將資料從datagridview中匯入MS SQL Server

雖然 .NET沒有類似JAVA的preparedstatement，但有類似的應用IDbDataParameter。

private void button2_Click(object sender, EventArgs e)
{
  string strDBInsert = "INSERT INTO customers VALUES
       (@name,@hPhone,@oPhone,@addr,@delivery,@midwife,@source)";
  string strConn = "Data Source = 6A-783500-NB\\SQLEXPRESS;
                   Integrated Security = True";
  SqlConnection sqlConn = new SqlConnection(strConn);
  sqlConn.Open();
  SqlCommand sqlCmd = new SqlCommand(strDBInsert,sqlConn);
  progressBar1.Step = 100/dataGridView1.Rows.Count;
  for (int i = 0; i < dataGridView1.Rows.Count; i++)
  {
    try
    {
      sqlCmd.Parameters.Clear();
      sqlCmd.Parameters.AddWithValue
          ("@name", Convert.ToString(dataGridView1[0, i].Value));
      sqlCmd.Parameters.AddWithValue
          ("@hPhone", Convert.ToString(dataGridView1[1, i].Value));
      sqlCmd.Parameters.AddWithValue
          ("@oPhone", Convert.ToString(dataGridView1[2, i].Value));
      sqlCmd.Parameters.AddWithValue
          ("@addr", Convert.ToString(dataGridView1[3, i].Value));
      sqlCmd.Parameters.AddWithValue
          ("@delivery", Convert.ToString(dataGridView1[4, i].Value));
      sqlCmd.Parameters.AddWithValue
          ("@midwife", Convert.ToString(dataGridView1[5, i].Value));
      sqlCmd.Parameters.AddWithValue
          ("@source", Convert.ToString(dataGridView1[6, i].Value));
      sqlCmd.ExecuteNonQuery();
      progressBar1.PerformStep();
      }
    catch (Exception e1)
    {
      continue;
    }
    dataGridView1.Rows[i].DefaultCellStyle.BackColor =
                                          Color.CornflowerBlue;  
  }
  progressBar1.Value = progressBar1.Maximum;
  sqlConn.Close();
}