023-Excel匯入資料庫

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
//連接Excel使用
using System.Data.OleDb;
//連接SQL使用
using System.Data.SqlClient;

namespace ExceltoDB
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string xlsPath = ""; string sheetName = "";
            xlsPath = @"C:\ImportFile.xls";
            sheetName = "Sheet1";
            //呼叫ImportDB函式
            ImportDB(xlsPath, sheetName);
        }

        //宣告靜態函式
        protected static void ImportDB(string xlsPath, string sheetName)
        {
            int i=0;
            //Excel的連線字串，HDR代表是否用第一列作欄位名稱，IMEX=1代表利用讀取用
            //xlsx格式不適用
            //using (OleDbConnection conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + xlsPath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"))
            using (OleDbConnection conn = new OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + xlsPath + "';Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'"))
            {
                //開啟OLEDB連結
                conn.Open();
                //類似T-SQL語法
                OleDbCommand cmd = new OleDbCommand("SELECT [品號] FROM [" + sheetName + "$];", conn);
                //執行讀取
                OleDbDataReader reader = cmd.ExecuteReader();

                //方法一
                //SQL連線字串，此例使用檔案型資料庫
                using (SqlConnection cn = new SqlConnection(@"Data Source= 主機位址;Initial catalog=選用DB;User id =帳號;Password =密碼"))//連線字串請自輸填入
                {
                    //開啟SQL連結
                    cn.Open();
                    //宣告交易，並指定連線
                    SqlTransaction stran = cn.BeginTransaction();
                    //利用例外處理包起來，使用一次Commit以達到類似批次處理效果
                    try
                    {
                        //逐列讀取直到結束
                        while (reader.Read())
                        {
                            //T-SQL新增語法
                            SqlCommand scmd = new SqlCommand(@"UPDATE INVMB SET MB051 = 0,USR_GROUP = 'DS120201' WHERE MB001 = '" + reader[0] + "'", cn); //自行輸入SQL語法
                            //宣告命令所使用的交易
                            scmd.Transaction = stran;
                            //執行命令，但未真正進入資料庫，Commit後才真正進入
                            scmd.ExecuteNonQuery();
                        }
                        //迴圈跑完一次匯入
                        stran.Commit();
                    }
                    //例外
                    catch (SqlException ex)
                    {
                        MessageBox.Show(ex.Message);
                        MessageBox.Show(ex.Number);
                        stran.Rollback();
                    }
                    catch (OleDbException ex)
                    {
                        MessageBox.Show(ex.Message);
                        stran.Rollback();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                        stran.Rollback();
                    }
                    //不管有無例外發生皆會執行
                    finally
                    {
                        //關閉連結和OleDbDataReader
                        cn.Close();
                        conn.Close();
                        reader.Close();
                        MessageBox.Show("已完成匯入...");
                    }
                }
            }
        }
    }
}