C# IDbHelper.MakeParameters方法代码示例

        /// <summary>
        /// 获取子节点列表
        /// </summary>
        /// <param name="dbHelper">数据库连接</param>
        /// <param name="tableName">目标表明</param>
        /// <param name="fieldId">主键字段</param>
        /// <param name="id">值</param>
        /// <param name="fieldParentId">父亲节点字段</param>
        /// <param name="order">排序</param>
        /// <param name="idOnly">只需要主键</param>
        /// <returns>数据表</returns>
        public static DataTable GetChildrens(IDbHelper dbHelper, string tableName, string fieldId, string id, string fieldParentId = null, string order = null, bool idOnly = false)
        {
            string sqlQuery = string.Empty;
            DataTable dataTable = new DataTable(tableName);
            if (dbHelper.CurrentDbType == DbTypes.Oracle)
            {
                if (idOnly)
                {
                    sqlQuery = "   SELECT " + fieldId;
                }
                else
                {
                    sqlQuery = "   SELECT * ";
                }
                sqlQuery += "          FROM " + tableName
                         + "    START WITH " + fieldId + " = " + dbHelper.GetParameter(fieldId)
                         + "  CONNECT BY PRIOR " + fieldId + " = " + fieldParentId;
                if (!String.IsNullOrEmpty(order))
                {
                    sqlQuery += " ORDER BY " + order;
                }
                string[] names = new string[1];
                names[0] = fieldId;
                Object[] values = new Object[1];
                values[0] = id;
                dbHelper.Fill(dataTable, sqlQuery, dbHelper.MakeParameters(names, values));
            }
            else if (dbHelper.CurrentDbType == DbTypes.SqlServer)
            {
                if (idOnly)
                {
                    sqlQuery = " WITH Tree AS (SELECT Id "
                             + "       FROM " + tableName
                             + "       WHERE Id IN ('" + id + "') "
                             + "       UNION ALL "
                             + "      SELECT ResourceTree.Id "
                             + "        FROM " + tableName + " AS ResourceTree INNER JOIN "
                             + "             Tree AS A ON A." + fieldId + " = ResourceTree." + fieldParentId + ") "
                             + " SELECT Id "
                             + "   FROM Tree ";
                }
                else
                {
                    sqlQuery = " WITH Tree AS (SELECT * "
                             + "       FROM " + tableName
                             + "       WHERE Id IN ('" + id + "') "
                             + "       UNION ALL "
                             + "      SELECT ResourceTree.* "
                             + "        FROM " + tableName + " AS ResourceTree INNER JOIN "
                             + "             Tree AS A ON A." + fieldId + " = ResourceTree." + fieldParentId + ") "
                             + " SELECT * "
                             + "   FROM Tree ";
                }

                dbHelper.Fill(dataTable, sqlQuery);
            }
            return dataTable;
        }

 /// <summary>
 /// 删除表格数据
 /// </summary>
 /// <param name="dbHelper">数据库连接</param>
 /// <param name="tableName">目标表名</param>
 /// <param name="parameters">删除条件</param>
 /// <returns>影响行数</returns>
 public static int Delete(IDbHelper dbHelper, string tableName, List<KeyValuePair<string, object>> parameters = null)
 {
     string sqlQuery = " DELETE " + " FROM " + tableName;
     string whereString = GetWhereString(dbHelper, parameters, BaseBusinessLogic.SQLLogicConditional);
     if (whereString.Length > 0)
     {
         sqlQuery += " WHERE " + whereString;
     }
     return dbHelper.ExecuteNonQuery(sqlQuery, dbHelper.MakeParameters(parameters));
 }

 /// <summary>
 /// 读取属性
 /// </summary>
 /// <param name="dbHelper">数据库连接</param>
 /// <param name="tableName">目标表名</param>
 /// <param name="parameters">字段名,键值</param>
 /// <param name="targetField">获取字段</param>
 /// <returns>属性</returns>
 public static string GetProperty(IDbHelper dbHelper, string tableName, List<KeyValuePair<string, object>> parameters, string targetField)
 {
     string returnValue = string.Empty;
        string sqlQuery = " SELECT " + targetField
        + " FROM " + tableName
        + " WHERE " + GetWhereString(dbHelper, parameters, BaseBusinessLogic.SQLLogicConditional);
        object returnObject = dbHelper.ExecuteScalar(sqlQuery, dbHelper.MakeParameters(parameters));
        if (returnObject != null)
        {
       returnValue = returnObject.ToString();
        }
        return returnValue;
 }

 /// <summary>
 /// 锁定表记录
 /// </summary>
 /// <param name="dbHelper">数据库连接</param>
 /// <param name="tableName">目标表名</param>
 /// <param name="names">字段名数组</param>
 /// <param name="values">键值数组</param>
 /// <param name="targetField">获取字段</param>
 /// <returns>锁定的行数</returns>
 public static int LockNoWait(IDbHelper dbHelper, string tableName, List<KeyValuePair<string, object>> parameters)
 {
     int returnValue = 0;
     string sqlQuery = " SELECT " + BaseBusinessLogic.FieldId
         + " FROM " + tableName
         + " WHERE " + GetWhereString(dbHelper, parameters, BaseBusinessLogic.SQLLogicConditional);
     sqlQuery += " FOR UPDATE NOWAIT ";
     try
     {
         DataTable dataTable = new DataTable("ForUpdateNoWait");
         dbHelper.Fill(dataTable, sqlQuery, dbHelper.MakeParameters(parameters));
         returnValue = dataTable.Rows.Count;
     }
     catch
     {
         returnValue = -1;
     }
     return returnValue;
 }

        /// <summary>
        /// 获取个数
        /// </summary>
        /// <param name="dbHelper">数据库连接</param>
        /// <param name="tableName">目标表名</param>
        /// <param name="parameters">目标字段,值</param>
        /// <returns>行数</returns>
        public static int GetCount(IDbHelper dbHelper, string tableName, List<KeyValuePair<string, object>> parameters, KeyValuePair<string, object> parameter = new KeyValuePair<string, object>())
        {
            int returnValue = 0;
            string sqlQuery = " SELECT COUNT(1) "
                + " FROM " + tableName
                + " WHERE " + GetWhereString(dbHelper, parameters, BaseBusinessLogic.SQLLogicConditional);

            if (!string.IsNullOrEmpty(parameter.Key))
            {
               switch (DbHelper.DbType)
               {
                   case DbTypes.Access:
                       // BaseSequence表的ID 是字符类型
                       if (tableName == "BaseSequence")
                           sqlQuery += BaseBusinessLogic.SQLLogicConditional + "( " + parameter.Key + " <> '" + parameter.Value + "' ) ";
                       else if (parameter.Value == null)
                           sqlQuery += BaseBusinessLogic.SQLLogicConditional + "( " + parameter.Key + " <> 0 ) ";
                        else
                           sqlQuery += BaseBusinessLogic.SQLLogicConditional + "( " + parameter.Key + " <> " + parameter.Value + " ) ";
                       break ;
                   default :
                       sqlQuery += BaseBusinessLogic.SQLLogicConditional + "( " + parameter.Key + " <> '" + parameter.Value + "' ) ";
                       break ;
               }
            }

            object returnObject = null;
            if (parameters != null)
            {
                returnObject = dbHelper.ExecuteScalar(sqlQuery, dbHelper.MakeParameters(parameters));
            }
            else
            {
                returnObject = dbHelper.ExecuteScalar(sqlQuery);
            }
            if (returnObject != null)
            {
                returnValue = int.Parse(returnObject.ToString());
            }
            return returnValue;
        }

 /// <summary>
 /// 获取数据表
 /// </summary>
 /// <param name="dbHelper">数据库连接</param>
 /// <param name="tableName">数据来源表名</param>
 /// <param name="parameters">字段名,字段值</param>
 /// <param name="topLimit">前几个记录</param>
 /// <param name="targetField">目标字段</param>
 /// <returns>数据表</returns>
 public static string[] GetProperties(IDbHelper dbHelper, string tableName, List<KeyValuePair<string, object>> parameters, int? topLimit = null, string targetField = null)
 {
     if (string.IsNullOrEmpty(targetField))
     {
         targetField = BaseBusinessLogic.FieldId;
     }
     // 这里是需要完善的功能，完善了这个，是一次重大突破
     string sqlQuery = " SELECT " + targetField + " FROM " + tableName;
     string whereSql = string.Empty;
     if (topLimit != null && topLimit > 0)
     {
         switch (dbHelper.CurrentDbType)
         {
             case DbTypes.Access:
             case DbTypes.SqlServer:
                 sqlQuery = " SELECT TOP " + topLimit.ToString() + targetField + " FROM " + tableName;
                 break;
             case DbTypes.Oracle:
                 whereSql = " ROWNUM < = " + topLimit;
                 break;
         }
     }
     string subSql = GetWhereString(dbHelper, parameters, BaseBusinessLogic.SQLLogicConditional);
     if (subSql.Length > 0)
     {
         if (whereSql.Length > 0)
         {
             whereSql = whereSql + BaseBusinessLogic.SQLLogicConditional + subSql;
         }
         else
         {
             whereSql = subSql;
         }
     }
     if (whereSql.Length > 0)
     {
         sqlQuery += " WHERE " + whereSql;
     }
     if (topLimit != null)
     {
         switch (dbHelper.CurrentDbType)
         {
             case DbTypes.MySql:
                 sqlQuery += " LIMIT 0, " + topLimit;
                 break;
         }
     }
     DataTable dataTable = new DataTable(tableName);
     dbHelper.Fill(dataTable, sqlQuery, dbHelper.MakeParameters(parameters));
     return BaseBusinessLogic.FieldToArray(dataTable, targetField);
 }

 public static DataTable GetDataTable(IDbHelper dbHelper, string tableName, List<KeyValuePair<string, object>> parameters, int topLimit = 0, string order = null, string sqlLogicConditional = null)
 {
     // 这里是需要完善的功能，完善了这个，是一次重大突破
     string sqlQuery = " SELECT * FROM " + tableName;
     string whereSql = string.Empty;
     if (topLimit != 0)
     {
         switch (dbHelper.CurrentDbType)
         {
             case DbTypes.Access:
             case DbTypes.SqlServer:
                 sqlQuery = " SELECT TOP " + topLimit.ToString() + " * FROM " + tableName;
                 break;
             case DbTypes.Oracle:
                 whereSql = " ROWNUM < = " + topLimit;
                 break;
         }
     }
     if (string.IsNullOrEmpty(sqlLogicConditional))
     {
         sqlLogicConditional = BaseBusinessLogic.SQLLogicConditional;
     }
     string subSql = GetWhereString(dbHelper, parameters, sqlLogicConditional);
     if (!string.IsNullOrEmpty(subSql))
     {
         if (whereSql.Length > 0)
         {
             whereSql = whereSql + sqlLogicConditional + subSql;
         }
         else
         {
             whereSql = subSql;
         }
     }
     if (whereSql.Length > 0)
     {
         sqlQuery += " WHERE " + whereSql;
     }
     if ((order != null) && (order.Length > 0))
     {
         sqlQuery += " ORDER BY " + order;
     }
     if (topLimit != 0)
     {
         switch (dbHelper.CurrentDbType)
         {
             case DbTypes.MySql:
                 sqlQuery += " LIMIT 0, " + topLimit;
                 break;
         }
     }
     DataTable dataTable = new DataTable(tableName);
     if (parameters != null && parameters.Count > 0)
     {
         dataTable = dbHelper.Fill(sqlQuery, dbHelper.MakeParameters(parameters));
     }
     else
     {
         dataTable = dbHelper.Fill(sqlQuery);
     }
     return dataTable;
 }

 /// <summary>
 /// 获取父节点列表
 /// </summary>
 /// <param name="dbHelper">数据库连接</param>
 /// <param name="tableName">目标表明</param>
 /// <param name="fieldCode">编码字段</param>
 /// <param name="code">编码</param>
 /// <param name="order">排序</param>
 /// <param name="idOnly">只需要主键</param>
 /// <returns>数据表</returns>
 public static DataTable GetParentsByCode(IDbHelper dbHelper, string tableName, string fieldCode, string code, string order, bool idOnly = false)
 {
     string sqlQuery = string.Empty;
     if (idOnly)
     {
         sqlQuery = "   SELECT " + BaseBusinessLogic.FieldId;
     }
     else
     {
         sqlQuery = "   SELECT * ";
     }
     sqlQuery += "     FROM " + tableName;
     switch (dbHelper.CurrentDbType)
     {
         case DbTypes.Access:
         case DbTypes.SqlServer:
             sqlQuery += "    WHERE (LEFT(" + dbHelper.GetParameter(fieldCode) + ", LEN(" + fieldCode + ")) = " + fieldCode + ") ";
             break;
         case DbTypes.Oracle:
             sqlQuery += "    WHERE (SUBSTR(" + dbHelper.GetParameter(fieldCode) + ", 1, LENGTH(" + fieldCode + ")) = " + fieldCode + ") ";
             break;
     }
     if (!String.IsNullOrEmpty(order))
     {
         sqlQuery += " ORDER BY " + order;
     }
     string[] names = new string[1];
     Object[] values = new Object[1];
     names[0] = fieldCode;
     values[0] = code;
     DataTable dataTable = new DataTable(tableName);
     dbHelper.Fill(dataTable, sqlQuery, dbHelper.MakeParameters(names, values));
     return dataTable;
 }

 /// <summary>
 /// 获取父节点
 /// </summary>
 /// <param name="tableName">目标表名</param>
 /// <param name="fieldCode">编码字段</param>
 /// <param name="code">编号</param>
 /// <returns>主键</returns>
 public static string GetParentIdByCode(IDbHelper dbHelper, string tableName, string fieldCode, string code)
 {
     string parentId = string.Empty;
     string sqlQuery = " SELECT MAX(Id) AS Id "
                     + "    FROM " + tableName
                     + "  WHERE (LEFT(" + dbHelper.GetParameter(fieldCode) + ", LEN(" + fieldCode + ")) = " + fieldCode + ") "
                     + "    AND " + fieldCode + " <>  '" + code + " ' ";
     string[] names = new string[1];
     Object[] values = new Object[1];
     names[0] = fieldCode;
     values[0] = code;
     object returnObject = dbHelper.ExecuteScalar(sqlQuery, dbHelper.MakeParameters(names, values));
     if (returnObject != null)
     {
         parentId = returnObject.ToString();
     }
     return parentId;
 }

 /// <summary>
 /// 通过存储过程获取表数据
 /// </summary>
 /// <param name="dbHelper">数据库连接</param>
 /// <param name="procedureName">存储过程名</param>
 /// <param name="tableName">填充表</param>
 /// <param name="id">主键值</param>
 /// <returns>数据权限</returns>
 public static DataTable GetFromProcedure(IDbHelper dbHelper, string procedureName, string tableName, string id)
 {
     string[] names = new string[1];
     Object[] values = new Object[1];
     names[0] = BaseBusinessLogic.FieldId;
     values[0] = id;
     return dbHelper.ExecuteProcedureForDataTable(procedureName, tableName, dbHelper.MakeParameters(names, values));
 }