工作上有些場合需要將變更資料庫的過程以Script方式保存,方便交給其他人執行。

有不少工具可以協助從現有資料庫轉出INSERT Script,例如: SQL DumperDatabase Publish WizardToad... 都辦得到。不過剛好有個小需求是希望直接由.NET組成資料輸出INSERT Script,於是我寫了個很陽春的工具函數,將Dictionary<string, object>轉成Oracle用的INSERT Script,其中會再依object的型別做不同的處理,例如: 日期要用TO_DATE('....', 'YYYYMMDD...')等。

【提醒】本範例程式在處理字串值時,只用了Replace("'", "''")的方式防堵SQL Injection這種防禦式做法不若Parameter或LINQ等方式來得安全,若你的資料來源可能被人摻入惡意的攻擊指令,請避免使用。轉成Script的程序較適合資料來自其他可信任系統,或純粹由程式掌控,無被攔截加料疑慮的場合,特此補充。

<%@ Page Language="C#" %>
<script type="text/C#" runat="server">
void Page_Load(object sender, EventArgs e)
{
    Dictionary<string, object> p = new Dictionary<string, object>();
    p.Add("ModDate", DateTime.Now);
    p.Add("PlayerName", "Darkthread");
    p.Add("Score", 65535);
    p.Add("Remark", null);
    Response.ContentType = "text/plain";
    //提醒: 裝組Script的做法可能存在SQL Injection風險
    //      僅適合資料來源可被信任的場合,例如: 自其他糸統匯入
    Response.Write(GenOracleInsertScript("Records", p));
    Response.End();
}
//依據Dictionary<string, object>組裝ORACLE INSERT語法
public string GenOracleInsertScript(string tableName, Dictionary<string, object> p)
{
    StringBuilder sb = new StringBuilder();
    sb.AppendFormat("INSERT INTO {0} (", tableName);
    sb.Append(string.Join(",", p.Keys.ToArray()));
    sb.Append(") VALUES (");
    sb.Append(
        string.Join(",",
            p.Select(
                o =>
                {
                    object v = o.Value;
                    decimal d;
                    if (v == null) return "NULL";
                    string s = v.ToString();
                    if (v is DateTime)
                        return string.Format(
                            "TO_DATE('{0:yyyyMMddHHmmss}','YYYYMMDDHH24MISS')",
                            (DateTime)o.Value);
                    else if (decimal.TryParse(s, out d))
                        return s;
                    else
                    //注意: 此處使用置換單引號防禦SQL Injection
                    //      無法保證絕無被破解的風險
                        //      故本範例僅適合輸入資料來源可被信任的情境
                        //      若資料可能由未知的第三者提供,不建議使用組Script的方式
                        //      請改用Parameter、LINQ或Entity Framework方式處理
                        return string.Format("'{0}'", s.Replace("'", "''"));
                }
        ).ToArray()));
    sb.Append(");\r\n");
    return sb.ToString();
}
</script>
產生結果範例如下:
INSERT INTO Records (ModDate,PlayerName,Score,Remark) 
VALUES (TO_DATE('20110513105317','YYYYMMDDHH24MISS'),'Darkthread',65535,NULL);

Comments

# by pico

"例如: 日期要用TO_DATA('....', 'YYYYMMDD...')" 應該是TO_DATE('....', 'YYYYMMDD...')

# by Jeffrey

to pico, 恭喜發現本篇文章的防偽戳記一枚... orz 謝謝指正,修改囉~~

# by 小言

若是SQL 2008, 用 TVPs 應是最有效率的

# by 小言

若是用SQL 2008, 用TVPs 應是最有效率的

# by AA

GenOracleInsertScript Oracle 如果也有 TVP 當然是很好

# by Jeffrey

to AA, Oracle有類似TVP的玩法 -- OracleBulkCopy http://blog.darkthread.net/post-2011-08-19-odp-net-oraclebulkcopy.aspx 效率蠻好的,可以參考看看。

Post a comment