Oracle DBHelper 減少弱型別的 DbType 錯誤

2011-04-15
1100
1

最近有幸用到 Oracle 來實作網站，這是 demo 第一次摸 Oracle 剛開始的時候經常因為欄位名稱 Key 錯或是資料型態給錯而導致無法寫入，又很容易因為使用參數時沒注意順序而導致查了老半天才發現的窘境，而相當懷念 ORM 技術，但礙於 Oracle 支援 LINQ 的程度還不夠完美，客戶也不願意改變長久的習慣讓我使用 ORM 技術，所以就隨意的寫了一個堪用的 DBHelper 來應急。

demo廢言這個 DBHelper 只有幾個簡單的功能，但這幾個簡單的功能已經足以應付最常出錯的部份了

自動使用參數來做 Insert 和 Update
會判斷資料庫需要的欄位型態，錯誤時明確的提示

用法也很簡單（反正只支援簡單的更新和新增）

這是更新

OracleCommand cmd = db.CreateCommand();

var setField = new Dictionary<string, object>();
var whereField = new Dictionary<string, object>();

setField.Add("user", user);
setField.Add("moddate", DateTime.Now);
setField.Add("url", url);

whereField.Add("id", id);
whereField.Add("event", eventName);
whereField.Add("printdate", printdate);

DBHelper.UpdateTable(cmd, "tableName", setField, whereField);

cmd.ExecuteNonQuery();

這是新增

var setField = new Dictionary<string, object>();

setField.Add("user", user);
setField.Add("moddate", DateTime.Now);
setField.Add("url", url);

DBHelper.InsertTable(cmd, "tableName", setField);

cmd.ExecuteNonQuery();

看得出來寫法相當簡單，再來就是完整的 Code

public static class DBHelper
    {
        private static Dictionary<string, Dictionary<string, Dictionary<string, string>>> fileDataType = new Dictionary<string, Dictionary<string, Dictionary<string, string>>>();

        private static Dictionary<string, Dictionary<string, Dictionary<string, string>>> FileDataType
        {
            get { return fileDataType; }
            set { fileDataType = value; }
        }
        /// <summary>
        /// 最簡單的 Insert Table方法
        /// </summary>
        /// <param name="cmd">OracleCommand</param>
        /// <param name="tableName">要寫入的資料表</param>
        /// <param name="fieldValue">要寫入的欄位名稱和值</param>
        public static void InsertTable(OracleCommand cmd, string tableName, Dictionary<string, object> fieldValue)
        {
            //再塞參數
            AddParameters(cmd, tableName, fieldValue, false);

            //再建立 PLSQL
            cmd.CommandText = InsertPlSql(tableName, fieldValue);

        }
        /// <summary>
        /// 最簡單的 Update Table方法
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="tableName">資料庫欄位</param>
        /// <param name="setFieldValue">要寫入的欄位和值</param>
        /// <param name="whereFieldValue">要 where 的欄位和值</param>
        public static void UpdateTable(OracleCommand cmd,
            string tableName,
            Dictionary<string, object> setFieldValue,
            Dictionary<string, object> whereFieldValue)
        {
            UpdateTable(cmd, tableName, setFieldValue, whereFieldValue, null);
        }
        /// <summary>
        /// 最簡單的 Update Table方法
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="tableName">資料庫欄位</param>
        /// <param name="setFieldValue">要寫入的欄位和值</param>
        /// <param name="whereFieldValue">要 where 的欄位和值</param>
        /// <param name="wherePLSQL">如果有 複雜過濾條件寫在這（無法做格式驗證）</param>
        public static void UpdateTable(OracleCommand cmd,
            string tableName,
            Dictionary<string, object> setFieldValue,
            Dictionary<string, object> whereFieldValue,
            string wherePLSQL)
        {
            //塞入 更新的參數
            AddParameters(cmd, tableName, setFieldValue, false);

            //塞入 搜尋的參數
            AddParameters(cmd, tableName, whereFieldValue, true);

            //先建立 PLSQL
            cmd.CommandText = UpdatePlSql(tableName, setFieldValue, whereFieldValue, wherePLSQL);

        }

        /// <summary>
        /// 從資料庫取得欄位的型別
        /// </summary>
        /// <param name="tableName">資料表名稱</param>
        /// <returns></returns>
        private static Dictionary<string, Dictionary<string, string>> GetDBFieldDataType(OracleCommand oracleCommand, string tableName)
        {
            if (FileDataType.ContainsKey(tableName))
            {
                return FileDataType[tableName];
            }
            else
            {
                var field = new Dictionary<string, Dictionary<string, string>>();
                
                try
                {
                    oracleCommand.CommandText = "SELECT * FROM " + tableName;
                    using (OracleDataReader reader = oracleCommand.ExecuteReader())
                    {
                        reader.Read();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            field.Add(reader.GetName(i),
                                new Dictionary<string, string>()
                                    {
                                        {reader.GetDataTypeName(i),
                                        reader.GetFieldType(i).Name}
                                    });
                        }
                    }

                }
                catch
                {
                    throw;
                }
                FileDataType.Add(tableName, field);
                return field;
            }
        }

        /// <summary>
        /// 產生 Insert PLSQL 語句
        /// </summary>
        /// <param name="insertTable">要寫入的 Table</param>
        /// <param name="insertField">要寫入的欄位名稱與值</param>
        /// <returns></returns>
        private static string InsertPlSql(string insertTable, Dictionary<string, object> insertField)
        {
            var sb = new StringBuilder();
            sb.Append("insert into ");
            sb.Append(insertTable);
            sb.Append(" ( ");
            sb.Append(string.Join(",", insertField.Keys.ToArray()));
            sb.Append(" ) values ( :");
            sb.Append(string.Join(",:", insertField.Keys.ToArray()));
            sb.Append(")");
            return sb.ToString();
        }

        /// <summary>
        /// 產生 Update PLSQL 語句
        /// </summary>
        /// <param name="updateTable">要更新的 Table</param>
        /// <param name="setField">要設定的欄位名稱與值</param>
        /// <param name="whereField">要查詢的欄位</param>
        /// <returns></returns>
        private static string UpdatePlSql(string updateTable,
            Dictionary<string, object> setField,
            Dictionary<string, object> whereField,
            string wherePLSQL)
        {
            var sb = new StringBuilder();
            sb.AppendFormat("update {0}", updateTable);
            sb.Append(" set ");
            int i = 1;
            foreach (var element in setField)
            {
                sb.AppendFormat(" {0}=:{0}{1}",
                    element.Key,
                    i >= setField.Count ? "" : ",");

                i++;
            }

            if (whereField.Count > 0)
            {
                i = 1;
                sb.Append(" where");
                foreach (var element2 in whereField)
                {
                    sb.AppendFormat(" {0}=:ω{1} {2}",
                        element2.Key,
                        element2.Key,
                        i >= whereField.Count ? "" : "and");

                    i++;
                }
            }

            if (!string.IsNullOrEmpty(wherePLSQL))
            {
                string whereStr = sb.ToString();
                if (!whereStr.Contains(" where"))
                {
                    sb.Append(" where");
                }
                else
                {
                    sb.Append(" and ");
                }
                sb.Append(wherePLSQL);
            }

            return sb.ToString();
        }

        /// <summary>
        /// 塞入所以參數的值
        /// </summary>
        /// <param name="cmd">OracleCommand</param>
        /// <param name="tableName">資料表名稱</param>
        /// <param name="fieldValue">欄位的名稱與值</param>
        private static void AddParameters(OracleCommand cmd, string tableName, Dictionary<string, object> fieldValue, bool isWhereField)
        {
            var dataType = new Dictionary<string, Dictionary<string, string>>();
            if (!FileDataType.ContainsKey(tableName))
                dataType = GetDBFieldDataType(cmd, tableName);
            else
                dataType = FileDataType[tableName];

            foreach (var item in fieldValue)
            {
                /*
                 * Dictionary的key是OracleDbType
                 * Value是資料型態
                 */
                string upperkey = CheckDBType(tableName, dataType, item);
                //如果是 Where 就在Key 前面加上詭異字元
                string filterKey = isWhereField ? "ω" + item.Key : item.Key;
                var odbtype = (OracleDbType)Enum.Parse(typeof(OracleDbType), dataType[upperkey].First().Key);
                cmd.Parameters.Add(filterKey, odbtype).Value = item.Value;
            }
        }

        private static string CheckDBType(string tableName, Dictionary<string, Dictionary<string, string>> dataType, KeyValuePair<string, object> item)
        {
            string upperkey = item.Key.ToUpper();
            if (!dataType.ContainsKey(upperkey))
                throw new Exception(string.Concat(tableName, " 資料表中沒有【", upperkey, "】欄位"));

            string insertType = item.Value.GetType().Name;
            string dbType = dataType[upperkey].First().Value;
            if (insertType != dbType)
            {
                throw new Exception(string.Concat(
                    tableName,
                    " 資料表中的 ",
                    upperkey,
                    " 欄位型態為【",
                    dbType,
                    "】傳入的是【",
                    insertType,
                    "】"));
            }
            return upperkey;
        }
    }

demo廢言這玩意還沒有完善，不過對於我是足夠了，如果還有進階需求的可以自行擴充，或是留言說說你想要的功能，讓我有點動力繼續加強它（不過我一點都不想自己寫 ORM ）

Oracle

<< 較舊的文章|回首頁|較新的文章 >>

網友回應

shihwei: 發表於: 2011-05-11; 請問：「//如果是 Where 就在Key 前面加上詭異字元」這個的用途是 ?; 有時候你會想要找到 success='n' 的資料，但是你卻又要將他改成 success='y' 所以利用詭異字元來區分出搜尋還是設定
回覆於: 2011-05-13

我要回應

*暱稱
E-mail	(本站使用Gravatar頭像，E-mail不會顯示於網站上) 不填寫email將不會收到回覆通知。
網站
*回應
	（本留言板不支援輸入任何語法貼 Code 請按我再貼網址進來）
	不打勾才可留言

demoshop

NEWS

Oracle DBHelper 減少弱型別的 DbType 錯誤

網友回應

我要回應