参数化构造的通用查询方法

下面是一个非分页的参数化构造的通用查询方法

        /// <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);