廣用性更新資料

朋友的需求,透過通用的 Table Type 更新資料庫內不同的 Table,我的解法大概會這樣:
 
–建立範例使用者自訂資料表類型和資料表
use tempdb
create type tpTbl AS TABLE(c1 nvarchar(1000),c2 nvarchar(1000),c3 nvarchar(1000))
create table tb(col1 int,col2 int)
go
 
–table type 的欄位數量和定義都與目標資料表不同
create proc spUpdate @tb tpTbl readonly
as
MERGE tb USING @tb AS source
ON tb.Col1=source.c1
WHEN MATCHED THEN
 UPDATE SET tb.col2=source.c2
WHEN NOT MATCHED BY TARGET THEN
 INSERT (col1,col2) VALUES(c1,c2);
go
 
–測試
truncate table tb
declare @t tpTbl
insert @t(c1,c2) values(1,1),(2,2),(3,3)
exec spUpdate @t
select * from tb
declare @t tpTbl
insert @t(c1,c2) values(1,7),(2,8),(3,9),(4,4)
exec spUpdate @t
select * from tb
/*
drop proc spUpdate
drop type tpTbl
drop table tb
*/
 
 
//簡單地以 C# 來測試
    public partial class Form1 : Form
    {
        DataTable dt=new DataTable();
        const int iRowCount=3;  //指定資料表類型的欄位數量
        public Form1()
        {
            InitializeComponent();
            using(SqlConnection cnn=new SqlConnection("Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;"))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM tb", cnn);
                dt.Load(cmd.ExecuteReader());
                cnn.Close();
            }
            dataGridView1.DataSource = dt;
        }
        private void button1_Click(object sender, EventArgs e)
        {
            using (SqlConnection cnn = new SqlConnection("Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;"))
            {
                cnn.Open();
                SqlCommand cmd = new SqlCommand("spUpdate", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@tb", SqlDbType.Structured));
                DataTable dtPar = dt.GetChanges();
 
                //需要補足欄位數,因為資料表類型參數的欄位是取多個資料表的最大欄位數
                for (int i = dt.Columns.Count; i < iRowCount; i++)
                {
                    dtPar.Columns.Add(new DataColumn("c" + i.ToString()));
                }
                cmd.Parameters[0].Value = dtPar;
                cmd.Parameters[0].TypeName = "tpTbl";
                MessageBox.Show(string.Format("更新 {0} 筆紀錄", cmd.ExecuteNonQuery()));
                cnn.Close();
            }
            dt.AcceptChanges();
        }
    }
 
這樣做雖然方便,但有些問題
1.批次修改會找不到是哪一筆紀錄發生錯誤
2.通用資料型態若欄位太多,則 ADO.NET 所產生的批次語法中,會有很多 NULL 字樣,反而浪費網路傳遞的資源

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: