C# IDbHelper.Fill方法代码示例

 /// <summary>
 /// 使用存储过程获取分页数据
 /// </summary>
 /// <param name="dbHelper">数据源</param>
 /// <param name="recordCount">返回的记录数</param>
 /// <param name="pageIndex">当前页</param>
 /// <param name="pageSize">每页记录数</param>
 /// <param name="sortExpression">排序字段</param>
 /// <param name="sortDire">排序</param>
 /// <param name="tableName">表名</param>
 /// <param name="whereConditional">查询条件</param>
 /// <param name="selectField">查询字段</param>
 /// <returns></returns>
 public static DataTable GetDataTableByPage(IDbHelper dbHelper, out int recordCount, int pageIndex = 1, int pageSize = 20, string sortExpression = null, string sortDire = null, string tableName = null, string whereConditional = null, string selectField = null)
 {
     DataTable dataTable = null;
     recordCount = 0;
     if (string.IsNullOrEmpty(selectField))
     {
         selectField = "*";
     }
     if (string.IsNullOrEmpty(whereConditional))
     {
         whereConditional = string.Empty;
     }
     List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
     IDbDataParameter dbDataParameter = DbHelper.MakeParameter("RecordCount", recordCount, DbType.Int64, 0, ParameterDirection.Output);
     dbParameters.Add(dbDataParameter);
     dbParameters.Add(DbHelper.MakeParameter("PageIndex", pageIndex));
     dbParameters.Add(DbHelper.MakeParameter("PageSize", pageSize));
     dbParameters.Add(DbHelper.MakeParameter("SortExpression", sortExpression));
     dbParameters.Add(DbHelper.MakeParameter("SortDire", sortDire));
     dbParameters.Add(DbHelper.MakeParameter("TableName", tableName));
     dbParameters.Add(DbHelper.MakeParameter("SelectField", selectField));
     dbParameters.Add(DbHelper.MakeParameter("WhereConditional", whereConditional));
     string commandText = "GetRecordByPage";
     dataTable = dbHelper.Fill(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
     recordCount = int.Parse(dbDataParameter.Value.ToString());
     return dataTable;
 }

 /// <summary>
 /// 获取数据表
 /// </summary>
 /// <param name="dbHelper">数据库连接</param>
 /// <param name="tableName">目标表名</param>
 /// <param name="name">字段名</param>
 /// <param name="values">字段值</param>
 /// <param name="targetField">目标字段</param>
 /// <returns>数据表</returns>
 public static string[] GetProperties(IDbHelper dbHelper, string tableName, string name, Object[] values, string targetField)
 {
     string sqlQuery = " SELECT " + targetField
                     + "   FROM " + tableName
                     + "  WHERE " + name + " IN (" + BaseBusinessLogic.ObjectsToList(values) + ")";
     DataTable dataTable = dbHelper.Fill(sqlQuery);
     return BaseBusinessLogic.FieldToArray(dataTable, targetField);
 }

        /// <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="dbHelper">数据库类型</param>
 /// <param name="dbHelper">数据库连接</param>
 /// <param name="tableName">数据来源表名</param>
 /// <param name="name">字段名</param>
 /// <param name="value">字段值</param>
 /// <param name="order">排序</param>
 /// <returns>数据表</returns>
 public static DataTable GetDataTable(DbTypes DbType,IDbHelper dbHelper, string tableName, string name, object[] values, string order = null)
 {
     string sqlQuery = " SELECT * "
                     + "   FROM " + tableName;
     if (values == null)
     {
         sqlQuery += "  WHERE " + name + " IS NULL";
     }
     else
     {
         sqlQuery += "  WHERE " + name + " IN (" + BaseBusinessLogic.ObjectsToList(DbType,values) + ")";
     }
     if (!String.IsNullOrEmpty(order))
     {
         sqlQuery += " ORDER BY " + order;
     }
     return dbHelper.Fill(sqlQuery);
 }

 /// <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="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="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 GetChildrensByCode(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(" + fieldCode + ", LEN('" + code + "')) = '" + code + "') ";
             break;
         case DbTypes.Oracle:
             sqlQuery += "    WHERE (SUBSTR(" + fieldCode + ", 1, LENGTH('" + code + "')) = '" + code + "') ";
             break;
     }
     if (!String.IsNullOrEmpty(order))
     {
         sqlQuery += " ORDER BY " + order;
     }
     DataTable dataTable = new DataTable(tableName);
     dbHelper.Fill(dataTable, sqlQuery);
     return dataTable;
 }