C# ASP.NET CSV檔案匯入資料庫

標籤：des   blog   http   os   io   檔案   for   ar   資料   

原文:C# ASP.NET CSV檔案匯入資料庫

 

 

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.OleDb;

using System.Data.Sql;

using System.Data.SqlClient;

using System.IO;

 

namespace HPRSP.CommonDataObject

{

public class CSVHelper

{

 

private string path;

private string fileName;

/// <summary>

/// z建構函式

/// </summary>

public CSVHelper(string filePath, string fileName)

{

this.path = filePath;

this.fileName = fileName;

}

 

/// <summary>

/// 從Csv中讀取資料

/// </summary>

/// <returns></returns>

public DataTable Read()

{

return Read(null);

}

/// <summary>

/// 通過檔案流的方式來讀取CSV檔案，預設第一列為標題列，列之間用逗號分隔

/// </summary>

/// <param name="files"></param>

/// <returns></returns>

public DataTable ReadCsvFileToTable()

{

return ReadCsvFileToTable(true, ‘,‘);

}

/// <summary>

/// 通過檔案流的方式來讀取CSV檔案，預設列之間用逗號分隔

/// </summary>

/// <param name="files">檔案名稱</param>

/// <param name="HeadYes">第一行是否為欄位標題</param>

/// <returns></returns>

public DataTable ReadCsvFileToTable(bool HeadYes)

{

return ReadCsvFileToTable(HeadYes, ‘,‘);

}

/// <summary>

/// 通過檔案流的方式來讀取CSV檔案

/// </summary>

/// <param name="files">檔案名稱</param>

/// <param name="HeadYes">第一行是否為欄位標題</param>

/// <param name="span">分隔字元</param>

/// <returns></returns>

public DataTable ReadCsvFileToTable(bool HeadYes, char span)

{

//檔案路徑和檔案名稱

string files = path + fileName;

DataTable dt = new DataTable();

StreamReader fileReader = new StreamReader(files, Encoding.Default);

try

{

//是否為第一行（如果HeadYes為TRUE，則第一行為標題列）

int lsi = 0;

//列之間的分隔字元

char cv = span;

while (fileReader.EndOfStream == false)

{

string line = fileReader.ReadLine();

string[] y = line.Split(cv);

//第一行為標題列

if (HeadYes == true)

{

//第一行

if (lsi == 0)

{

for (int i = 0; i < y.Length; i++)

{

dt.Columns.Add(y[i].Trim().ToString());

}

lsi++;

}

//從第二列開始為資料列

else

{

DataRow dr = dt.NewRow();

for (int i = 0; i < y.Length; i++)

{

dr[i] = y[i].Trim();

}

dt.Rows.Add(dr);

}

}

//第一行不為標題列

else

{

if (lsi == 0)

{

for (int i = 0; i < y.Length; i++)

{

dt.Columns.Add("Col" + i.ToString());

}

lsi++;

}

DataRow dr = dt.NewRow();

for (int i = 0; i < y.Length; i++)

{

dr[i] = y[i].Trim();

}

dt.Rows.Add(dr);

}

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

fileReader.Close();

fileReader.Dispose();

}

 

return dt;

}

 

/// <summary>

/// 從csv中讀取資料

/// </summary>

/// <param name="colNames">列名列表，可為空白</param>

/// <returns></returns>

private DataTable Read(string[] colNames)

{

string sql = CreateSql(colNames);

return ExecuteTable(sql);

}

 

/// <summary>

/// 通過執行sql語句，從Csv中讀取資料

/// </summary>

/// <param name="sql">sql語句</param>

/// <returns></returns>

private DataTable ExecuteTable(string sql)

{

DataTable table = new DataTable();

using (OleDbConnection connection = new OleDbConnection(GetConnString(true)))

{

OleDbCommand command = connection.CreateCommand();

command.CommandText = sql;

 

OleDbDataAdapter adapter = new OleDbDataAdapter(command);

adapter.Fill(table);

}

return table;

}

 

#region 私人方法

 

private string CreateSql(string[] colNames)

{

string cols = "*";

if (null != colNames && colNames.Length > 0)

{

StringBuilder colList = new StringBuilder();

for (int i = 0; i < colNames.Length - 1; i++)

{

colList.AppendFormat("[{0}],", colNames[i]);

}

colList.AppendFormat("[{0}]", colList[colList.Length]);

cols = colList.ToString();

}

return string.Format("select {0} from {1}", cols, fileName);

}

private string GetConnString(bool IsHeaderRow)

{

return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=‘Text;FMT=Delimited; HDR={0}‘;data source={1}", IsHeaderRow ? "Yes" : "No", path);

}

#endregion

}

}

 

 

 

在D:\VSS2005\政務\HPRSP_黃埔海關風險管理平台\原始碼\CommonDataObject\下增加了CSV檔案匯入資料庫的操作類：CSVHelper.cs .用法很簡單，只要傳入CSV檔案的檔案路徑和檔案名稱就行。下面是一個例子：

 

 

string conn = ConfigurationManager.ConnectionStrings["HPRSP"].ToString();

//擷取並儲存上傳的檔案

if (fuUpload.PostedFile != null)

fuUpload.PostedFile.SaveAs(Server.MapPath("") + "\\" + fuUpload.FileName);

else

throw new Exception("請選擇需上傳的檔案!");

//在建構函式中傳入檔案路徑和檔案名稱

HPRSP.CommonDataObject.CSVHelper CsvFile = new HPRSP.CommonDataObject.CSVHelper(Server.MapPath("") + "\\", fuUpload.FileName);

//調用Read()方法讀取檔案，具體的實現過程請查看類：CSVHelper.cs

DataTable dt= CsvFile.Read();

foreach(DataRow dr in dt.Rows)

{

string StoreName = "uspZFPG_INDEX_UPDATE";

 

ParamCollection parmsInsert = new ParamCollection();

//下面[]內為列名，即第一行對應的列名稱,如的測試資料

//

parmsInsert.Add(new Param("CustomsCode", dr["關區"].ToString(), ParamType.Varchar, 4, ParameterDirection.Input));

parmsInsert.Add(new Param("IndexID", dr["指標ID"].ToString(), ParamType.Varchar, 10, ParameterDirection.Input));

parmsInsert.Add(new Param("StandardValue", dr["目標值"].ToString(), ParamType.Varchar, 50, ParameterDirection.Input));

parmsInsert.Add(new Param("Value", dr["完成值"].ToString(), ParamType.Varchar, 50, ParameterDirection.Input));

parmsInsert.Add(new Param("IsReachStandard", dr["是否達標"], ParamType.Nvarchar, 50, ParameterDirection.Input));

 

try

{

SqlDatabase.ExecuteNonQuery(conn, CommandType.StoredProcedure, StoreName, 300, parmsInsert);

}

catch (SqlException ex)

{

Errors++;

//string Desc = "匯入“執法評估指標 " + dr["IndexID"].ToString() + ",關區 " + dr["CustomsCode"].ToString() + ",時間段標誌 " + dr["TimeSpan"].ToString() + "”時出錯：" + ex.ToString() + DateTime.Now;

throw ex;

}

}

C# ASP.NET CSV檔案匯入資料庫