CSV匯入SQLSERVER資料庫

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

    /// <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;    }

    private string conn = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
    /// <summary>
    /// 將指定表的資料Copy至目標資料庫    /// </summary>    /// <param name="dt">資料表</param>    /// <param name="outTime">逾時時間</param>    /// <returns></returns>    public string CopyTableToSql(DataTable dt, int outTime, string tableName)    {        SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.UseInternalTransaction);        sbc.BulkCopyTimeout = outTime;        sbc.NotifyAfter = dt.Rows.Count;        try        {            sbc.DestinationTableName = tableName;            sbc.WriteToServer(dt);            return "";        }        catch (Exception ex)        {            return ex.Message;        }        finally        {            sbc.Close();        }    }