台銀 URL
CSV
https://rate.bot.com.tw/xrt/fltxt/0/2023-07-03
帶入查詢日
https://rate.bot.com.tw/xrt/flcsv/0/day
如為今天 帶入 day
TXT
https://rate.bot.com.tw/xrt/flcsv/0/2023-07-03
https://rate.bot.com.tw/xrt/fltxt/0/day
Postman 測試
如今天 傳入 日期是不會回傳TXT / CSV 的
因為是要每天把前一天的台銀(收盤)匯率轉入DB
所以 先根據回傳的CSV 結構 在DB 建立一個table
建立一個datatable
string sqlstr = "select * from [dbo].[RateBOT] where 1=2";
// DataSet ds = new DataSet();
DataTable Dt=new DataTable();
Dt = cls_sql.getSqlData(ConfigurationManager.AppSettings.Get("DSC_statistics"), sqlstr, null, "RateBOT", CommandType.Text).Tables[0];
//開始讀取匯率 讀取CSV 檔
using (HttpClient client = new HttpClient())
{
string vDate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
var requestUri = ConfigurationManager.AppSettings.Get("BOTURL").ToString() + vDate;
var vresult = client.PostAsync(requestUri, null).Result.Content.ReadAsStringAsync().Result;
string[] vline = vresult.Split('\n');
for (int i = 0; i < vline.Length; i++)
{
string[] vColumn = vline[i].Replace("\r", "").Split(',');
if (i == 0) //column name 如空的datatable 先建立column
{
//for (int j = 0; j < vColumn.Length; j++)
//{
// DataColumn dataColumn = new DataColumn(j.ToString() + vColumn[j]); // 因為column name 會重複 故前面加上序號
// Dt.Columns.Add(dataColumn);
//}
//DataColumn dataColumn = new DataColumn(vline[i]);
//dataTable.Columns.Add(dataColumn);
}
else
{
if (vColumn.Length > 1)
{
DataRow dataRow = Dt.NewRow();
dataRow[0] = vDate.Replace("-", "");
for (int j = 0; j < vColumn.Length - 1; j++) //因每行後面有多個 , 故-1
{
dataRow[j + 1] = vColumn[j].ToString();
//第一欄 為日期 故 +1
}
Dt.Rows.Add(dataRow);
}
}
}
SqlConnection SqlConnection = new SqlConnection(ConfigurationManager.AppSettings.Get("DSC_statistics").Trim());
SqlConnection.Open();
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(SqlConnection))
{
sqlBulkCopy.DestinationTableName = Dt.TableName;
foreach (var column in Dt.Columns)
sqlBulkCopy.ColumnMappings.Add(column.ToString(), column.ToString());
sqlBulkCopy.WriteToServer(Dt);
}
}
留言列表