uncategorized

使用XML Query一次新增多筆資料

對於一次將多筆資料新增或是更新到同一個資料表，有很多種方法

  • 利用While + insert into 指令

    這種方式資料庫連線需要開開關關，是非常消耗效能的，除非是不得已情況下，不建議採用此方式進行

  • 利用ADO.NET底層方式加上Data Table或是Data set方式

    這種方式透過ADO.NET幫我們做掉這些步驟，但是，有時候卻因為太自動化，導致在需求會有不敷使用

  • XML Query方式

    這種方式需要在AP端與DB端建立程式，感覺比較麻煩，不過，我覺得這樣在某方面的自由度卻也比較高
以第三種方式的簡易範例
產生XML資料

  1. 定義資料欄位

    1
    string[] strColumn = new string[6] { "C1", "C2", "C3", "C4", "C5", "C6" };

  2. 建立XML資料

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    string[] strValue;
    string strXMLData = string.Empty;
    System.IO.StringWriter ser = new System.IO.StringWriter();
    XmlTextWriter XTW = new XmlTextWriter(ser);
    XTW.WriteStartElement("Root");//開始主節點
    for (int i = 0; i < strdata.Length; i++)//判斷有幾筆資料
    {
           strValue = strdata[i].Split(',');
           XTW.WriteStartElement("Data");
           for (int j = 0; j < strColumn.Length; j++)
           {
                XTW.WriteStartElement(strColumn[j]);
                XTW.WriteString(strValue[j]);
                XTW.WriteEndElement();
            }
           XTW.WriteEndElement();
     }
     XTW.WriteEndElement();
     ser.Flush();
     ser.Close();

  3. 用陣列格式傳入，所以要先用Splite取得每筆的各欄資料

    1
    string[] strdata = new string[4] { "01,A,11,2009-10-10,1,1", "02,B,11,2009-10-10,2,2", "03,C,11,2009-10-10,3,3","04,D,11,2009-10-10,4,4" };

  4. XML資料轉型為String，做為傳入預存程序的參數

    1
    strXMLData = ser.ToString();
預存程序撰寫下列程式碼

  1. 宣告傳入參數格式為XML

    1
    ALTER PROCEDURE [dbo].[SP_Change](@ALLBINChange XML)

  2. 取出@ALLBINChange內的XML資料，這部分依照資料庫需求取得必須的資料。把XML當作一個資料表來操作即可

    1
    2
    3
    4
    5
    6
    7
    select WIS.Data.query('./C1').value('.','varchar(15)') as C1,
    WIS.Data.query('./C2').value('.','varchar(15)') as C2,
    WIS.Data.query('./C3').value('.','varchar(50)') as C3,
    WIS.Data.query('./C4').value('.','datetime') as C4,
    WIS.Data.query('./C5').value('.','int') as C5,
    WIS.Data.query('./C6').value('.','int') as C6
    from @ALLBINChange.nodes('/Root/Data') as root(Data)

  3. 更新資料為例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    update dbo.BBBB set A1=a.C1,A2=a.C2,A3=a.C3,A4=a.C4
    from(
    select WIS.Data.query('./C1').value('.','varchar(15)') as C1,
    WIS.Data.query('./C2').value('.','varchar(15)') as C2,
    WIS.Data.query('./C3').value('.','varchar(50)') as C3,
    WIS.Data.query('./C4').value('.','datetime') as C4,
    WIS.Data.query('./C5').value('.','int') as C5,
    WIS.Data.query('./C6').value('.','int') as C6
    from @ALLBINChange.nodes('/Root/Data') as root(Data))as a
    where ID=a.C1

這樣就可以將資料透過XML方式做一次性的更新或是新增到資料庫中

Share