Skylar's 資訊筆記

物件名稱：指令碼轉換器

在編輯指令碼中，新增以下程式。

此段程式碼用意為透過 http request 的方式取得 API 資料集，將資料及存成 JSON 檔案，以便利用 JSON 檔匯入，以及利用 JSON 檔案進行資料備份。

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Web.Script.Serialization;
using SC_93fe6db0a73a4078b2bbaf5b04018c74;
using System.Collections.Generic;
using System.IO;
using System.Windows.Forms;
#endregion

/// <summary>
/// This is the class to which to add your code.  Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    /// <summary>
    /// This method is called once, before rows begin to be processed in the data flow.
    ///
    /// You can remove this method if you don't need to do anything here.
    /// </summary>

    private const string _URL = "...";  // API URL 寫在這裡
    //private const string _urlParameters = "?sort_by=asc&per_page=999&created_at_after=2021-11-09 00:00:00&created_at_before=2021-11-09 23:59:59&page=1";  // 之後查詢參數寫在這邊
    private const string _urlParameters = "?sort_by=asc&per_page=999&";  // 之後查詢參數寫在這邊
    private string datetime_sub = "created_at_after=" + DateTime.UtcNow.AddHours(-0.1).ToString("yyyy-MM-dd HH:mm:ss");
    private string datetime_ori = "&created_at_before=" + DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss");
    private const string _token = "...";  // token 寫在這裡

    public override void PreExecute()
    {
        base.PreExecute();
        /*
         * Add your code here
         */
    }

    /// <summary>
    /// This method is called after all the rows have passed through this component.
    ///
    /// You can delete this method if you don't need to do anything here.
    /// </summary>
    public override void PostExecute()
    {
        base.PostExecute();
        /*
         * Add your code here
         */
    }

    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */

        HttpClient httpClient = new HttpClient();
        httpClient.BaseAddress = new Uri(_URL);

        // 新增 JSON Header
        httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
        httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", _token);

        // 列出 Data Response
        string urlParameter = _URL + _urlParameters + datetime_sub + datetime_ori;
        //string urlParameter = _URL + _urlParameters;
        HttpResponseMessage response = httpClient.GetAsync(urlParameter).Result;
        if (response.IsSuccessStatusCode)
        {
            Console.WriteLine("1");
            var dataObjects = response.Content.ReadAsStringAsync().Result;
            //var serializer = new JavaScriptSerializer();
            //List<StoreCredits> storeCredits = serializer.Deserialize<List<StoreCredits>>(dataObjects);

            // 將JSON檔寫入檔案
            string docPath = @"D:\DataExchangeFile\DataExchange\StoreCredits";
            string fileName = "StoreCredit_";
            string date = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss");
            using (StreamWriter streamWriter = new StreamWriter(Path.Combine(docPath, fileName + date + ".json")))
            {
                streamWriter.WriteLine(dataObjects);
                streamWriter.Close();
            }



            // Debug
            //MessageBox.Show(dataObjects);
            //foreach (Customer customer in customers)
            //{
            //    MessageBox.Show(customer.email);
            //    Console.WriteLine(customer.email);
            //}
        }
        else
        {
            Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
        }

        httpClient.Dispose();
    }

}

只有自己摸索過，才能內化轉換成力量。