C#讀Csv檔案、Execl並匯入SqlServer資料庫。速度不錯

//讀檔案原始碼；

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.OleDb;

namespace lyzApplication2009

{

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

}

}

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

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

namespace lyzApplication2009

{

/// <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 = PublicClass.ConnectString;

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

}

}