C#讀Csv檔案、Execl並匯入SqlServer資料庫

ReadExcelFiles

//讀檔案原始碼；
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace CC
{
 public   class ReadExcel
    {
        private string FileName;
        private string FilsPath;
        /// <summary>
        /// 建構函式檔案所在路徑
        /// </summary>
        /// <param name="Path">檔案路徑（Execl檔案為完整的路徑）</param>
        public ReadExcel(string Path)//構造
        {
            FileName = Path;
            //FilsPath = Files;
        }
        /// <summary>
        /// 建構函式:檔案所在路徑
        /// </summary>
        /// <param name="Path">檔案路徑（為Csv檔案所在的檔案夾路徑）</param>
        /// <param name="Files">讀Csv檔案時的檔案名稱</param>
        public ReadExcel(string Path, string Files)//構造
        {
            FileName = Path;
            FilsPath = Files;
        }
        OleDbConnection OleCon = new OleDbConnection();
        OleDbCommand OleCmd = new OleDbCommand();
        OleDbDataAdapter OleDa = new OleDbDataAdapter();

        #region 讀取Execl檔案返回DataSet
        /// <summary>
        /// 讀取Execl檔案返回DataSet
        /// </summary>
        /// <returns>返回結果集</returns>
        public DataSet Execl()
        {
            DataSet Myds = new DataSet();
            OleCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+FileName+";Extended Properties=Excel 8.0;";
            OleCon.Open();
            OleCmd.CommandText="select * from [sheet1$]";
            OleCmd.Connection = OleCon;
            OleDa.SelectCommand = OleCmd;
            try
            {
                OleDa.Fill(Myds, "Execl");
                return Myds;
            }
            catch
            {
                return Myds;
            }
            finally
            {
                OleCon.Close();
                OleCmd.Dispose();
                OleDa.Dispose();
                OleCon.Dispose();
            }

           
        }
        #endregion

        #region 讀取Csv檔案返回DataSet
        /// <summary>
        ///  讀取Csv檔案返回DataSet
        /// </summary>
        /// <returns>Csv內容</returns>
        public DataSet Csv()
        {
            DataSet CsvData = new DataSet();
            OleCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Text;FMT=Delimited;HDR=YES;'";
            OleCon.Open();
            OleCmd.Connection = OleCon;
            OleCmd.CommandText = "select * From " + FilsPath ;
            OleDa.SelectCommand=OleCmd;
            try
            {
                OleDa.Fill(CsvData, "Csv");
                return CsvData;
            }
            catch
            {
                return CsvData;
            }
            finally
            {
                OleCon.Close();
                OleCmd.Dispose();
                OleDa.Dispose();
                OleCon.Dispose();
            }
        }
        #endregion
    }
}

 ---------------------------------------------------------------------------------------------------------------

SqlBulkDataFiles

//寫入資料庫原始碼；此方法是源檔案內容中的例必須和要匯入的表的例數一至，且資料格式一至

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace CC
{
    /// <summary>
    /// 將資料寫入資料庫表中去
    /// </summary>
    class SqlBulkData
    {
        SqlConnection SqlCon = new SqlConnection();

        #region 將資料寫資料庫表中去（大容量）例與資料庫表例一至
        /// <summary>
        /// 將資料寫資料庫表中去（大容量）例與資料庫表例一至
        /// </summary>
        /// <param name="_Ds">資料來源內容</param>
        /// <param name="_TableName">表名</param>
        /// <returns>返回是否成功！</returns>
        public bool BulkData(DataSet _Ds, string _TableName)
        {
            SqlCon.ConnectionString = "";
            SqlCon.Open();
            SqlBulkCopy SqlBulk = new SqlBulkCopy(SqlCon);
            SqlBulk.DestinationTableName = _TableName;
            try
            {
                SqlBulk.WriteToServer(_Ds.Tables[0], DataRowState.Unchanged);
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                SqlCon.Close();
                SqlCon.Dispose();
                SqlBulk.Close();
            }
        }
        #endregion
    }
}