C# IDbHelper.GetParameter方法代码示例

        /// <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="id">主键</param>
 /// <param name="fieldName">字段</param>
 /// <param name="fieldValue">值</param>
 /// <param name="oldModifiedUserId">最后修改者</param>
 /// <param name="oldModifiedOn">最后修改时间</param>
 /// <returns>已被修改</returns>
 public static bool IsModifed(IDbHelper dbHelper, string tableName, string fieldName, Object fieldValue, string oldModifiedUserId, DateTime? oldModifiedOn)
 {
     bool returnValue = false;
     string sqlQuery = " SELECT " + BaseBusinessLogic.FieldId
                     + "," + BaseBusinessLogic.FieldCreateUserId
                     + "," + BaseBusinessLogic.FieldCreateOn
                     + "," + BaseBusinessLogic.FieldModifiedUserId
                     + "," + BaseBusinessLogic.FieldModifiedOn
                     + " FROM " + tableName
                     + " WHERE " + fieldName + " = " + dbHelper.GetParameter(fieldName);
     DataTable dataTable = dbHelper.Fill(sqlQuery, new IDbDataParameter[] { dbHelper.MakeParameter(fieldName, fieldValue)});
     returnValue = IsModifed(dataTable, oldModifiedUserId, oldModifiedOn);
     return returnValue;
 }

 /// <summary>
 /// 按日期查询
 /// </summary>
 /// <param name="dateTime">记录日期 yyyy/mm/dd</param>
 /// <param name="processId">模块主键</param>
 /// <param name="createUserId">用户主键</param>
 /// <returns>数据表</returns>
 public DataTable GetDataTableByDate(IDbHelper dbHelper, string createOn, string processId, string createUserId)
 {
     string sqlQuery = " SELECT * FROM " + BaseLogEntity.TableName
             + " WHERE CONVERT(NVARCHAR, " + BaseLogEntity.FieldCreateOn + ", 111) = " + dbHelper.GetParameter(BaseLogEntity.FieldCreateOn)
             + " AND " + BaseLogEntity.FieldProcessId + " = " + dbHelper.GetParameter(BaseLogEntity.FieldProcessId)
             + " AND " + BaseLogEntity.FieldUserId + " = " + dbHelper.GetParameter(BaseLogEntity.FieldUserId);
     sqlQuery += " ORDER BY " + BaseLogEntity.FieldCreateOn;
     string[] names		= new string[3];
     names[0] = BaseLogEntity.FieldCreateOn;
     names[1] = BaseLogEntity.FieldProcessName;
     names[2] = BaseLogEntity.FieldUserId;
     Object[] values	= new Object[3];
     values[0]	= createOn;
     values[1]   = processId;
     values[2]	= createUserId;
     DataTable dataTable = new DataTable(BaseLogEntity.TableName);
     dbHelper.Fill(dataTable, sqlQuery, DbHelper.MakeParameters(names, values));
     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>
 /// 获得条件语句
 /// 20110523 吉日嘎拉，改进空数组 
 /// </summary>
 /// <param name="dbHelper">数据库连接</param>
 /// <param name="names">字段名</param>
 /// <param name="values">字段值</param>
 /// <param name="relation">逻辑关系</param>
 /// <returns>字符串</returns>
 public static string GetWhereString(IDbHelper dbHelper, ref string[] names, Object[] values, string relation)
 {
     string returnValue = string.Empty;
     string subSqlQuery = string.Empty;
     for (int i = 0; i < names.Length; i++)
     {
         if ((names[i] != null) && (names[i].Length > 0))
         {
             //if (values[i] == null || String.IsNullOrEmpty(values[i].ToString()))
             if (values[i] == null)
             {
                 subSqlQuery = " (" + names[i] + " IS NULL) ";
                 // 这里就不需要参数化了
                 names[i] = null;
             }
             else
             {
                 if (values[i] is Array)
                 {
                     if (((Array)values[i]).Length > 0)
                     {
                         subSqlQuery = " (" + names[i] + " IN (" + StringUtil.ArrayToList((string[])values[i], "'") + ")) ";
                     }
                     else
                     {
                         subSqlQuery = " (" + names[i] + " IS NULL) ";
                     }
                     // 这里就不需要参数化了
                     names[i] = null;
                 }
                 else
                 {
                     subSqlQuery = " (" + names[i] + " = " + dbHelper.GetParameter(names[i]) + ") ";
                     //if ((values[i].ToString().IndexOf('[') >= 0) || (values[i].ToString().IndexOf(']') >= 0))
                     //{
                     //    values[i] = values[i].ToString().Replace("[", "/[");
                     //    values[i] = values[i].ToString().Replace("]", "/]");
                     //    values[i] = SqlSafe(values[i].ToString());
                     //    subSqlQuery = " (" + names[i] + " LIKE '" + values[i] + "' ESCAPE '/') ";
                     //    values[i] = null;
                     //    subSqlQuery = " (" + names[i] + " LIKE ? ESCAPE '/') ";
                     //}
                 }
                 // 这里操作，就会有些重复了，不应该进行处理
                 // values[i] = this.SqlSafe(values[i].ToString());
             }
             returnValue += subSqlQuery + relation;
         }
     }
     if (returnValue.Length > 0)
     {
         returnValue = returnValue.Substring(0, returnValue.Length - relation.Length - 1);
     }
     return returnValue;
 }