參數化構造的通用查詢方法

下面是一個非分頁的參數化構造的通用查詢方法

/// <summary>
        /// 參數化查詢資料表
        /// </summary>
        /// <param name="dbHelper">資料庫連接配接</param>
        /// <param name="tableName">表明</param>
        /// <param name="parameters">查詢的參數</param>
        /// <param name="conditions">查詢條件</param>
        /// <param name="topLimit">前多少條</param>
        /// <param name="order">排序</param>
        /// <param name="fields">查詢的字段</param>
        /// <returns>資料表</returns>
        public static DataTable GetDataTable(IDbHelper dbHelper, string tableName, List<KeyValuePair<string, object>> parameters, string conditions, int topLimit = 0, string order = null, string fields = " * ")
        {
            string sqlQuery = "SELECT " + fields + " FROM " + tableName;
            string whereSql = string.Empty;
            if (topLimit != 0)
            {
                switch (dbHelper.CurrentDbType)
                {
                    case CurrentDbType.Access:
                    case CurrentDbType.SqlServer:
                        sqlQuery = "SELECT TOP " + topLimit.ToString() + fields + " FROM " + tableName;
                        break;
                    case CurrentDbType.Oracle:
                        whereSql = " ROWNUM < = " + topLimit;
                        break;
                }
            }
            // 要傳入 conditions
            if (!string.IsNullOrEmpty(conditions))
            {
                conditions = " WHERE " + conditions;
            }
            sqlQuery += conditions + whereSql;
            if ((order != null) && (order.Length > 0))
            {
                sqlQuery += " ORDER BY " + order;
            }
            var dt = new DataTable(tableName);
            if (topLimit != 0)
            {
                switch (dbHelper.CurrentDbType)
                {
                    case CurrentDbType.MySql:
                        sqlQuery += " LIMIT 0, " + topLimit;
                        break;
                }
            }
            if (parameters != null && parameters.Count > 0)
            {
                dt = dbHelper.Fill(sqlQuery, dbHelper.MakeParameters(parameters));
            }
            else
            {
                dt = dbHelper.Fill(sqlQuery);
            }
            return dt;
        }      

該方法在吉日通用權限管理的DotNet.Business.DbLogic中可見

調用方法

List<KeyValuePair<string, object>> dbParameters = new List<KeyValuePair<string, object>>();
　　conditions  ....

 DataTable dtResult = CommonManager.GetDataTable(dbHelper, tableName, dbParameters, conditions,0, orderBy, selectField);