C# IDbHelper类代码示例

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

 //
 // 锁定表记录
 //
 public static int LockNoWait(IDbHelper dbHelper, string tableName, params KeyValuePair<string, object>[] parameters)
 {
     List<KeyValuePair<string, object>> parametersList = new List<KeyValuePair<string, object>>();
     for (int i = 0; i < parameters.Length; i++)
     {
         parametersList.Add(parameters[i]);
     }
     return LockNoWait(dbHelper, tableName, parametersList);
 }

 public static IDataReader GetDataReader(IDbHelper dbHelper, string tableName, List<KeyValuePair<string, object>> parameters, int topLimit = 0, string order = 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;
         }
     }
     string subSql = GetWhereString(dbHelper, parameters, BaseBusinessLogic.SQLLogicConditional);
     if (!string.IsNullOrEmpty(subSql))
     {
         if (whereSql.Length > 0)
         {
             whereSql = whereSql + BaseBusinessLogic.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)
     {
         return dbHelper.ExecuteReader(sqlQuery, dbHelper.MakeParameters(parameters));
     }
     else
     {
         return dbHelper.ExecuteReader(sqlQuery);
     }
 }

 public TasksService(IDbHelper helper, IProjectRepository projectRepo, IGenericRepository genericRepo, IMemberRepository memberRepo, ITasksRepository taskRepo, INotificationsService notificationService)
 {
     _helper = helper;
     _projectRepo = projectRepo;
     _genericRepo = genericRepo;
     _memberRepo = memberRepo;
     _taskRepo = taskRepo;
     _notificationService = notificationService;
 }

 /// <summary>
 /// 截断表格数据
 /// </summary>
 /// <param name="dbHelper">数据库连接</param>
 /// <param name="tableName">目标表格</param>
 /// <returns>是否成功</returns>
 public static int Truncate(IDbHelper dbHelper, string tableName)
 {
     string sqlQuery = " TRUNCATE TABLE " + tableName;
     // DB2 V9.7 以后才支持这个语句
     if (dbHelper.CurrentDbType == DbTypes.DB2)
     {
         sqlQuery = " ALTER TABLE " + tableName + " ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE ";
     }
     return dbHelper.ExecuteNonQuery(sqlQuery);
 }

 /// <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="whereParameters">条件字段,条件值</param>
 /// <param name="parameters">更新字段,更新值</param>
 /// <returns>影响行数</returns>
 public static int SetProperty(IDbHelper dbHelper, string tableName, List<KeyValuePair<string, object>> whereParameters, List<KeyValuePair<string, object>> parameters)
 {
     SQLBuilder sqlBuilder = new SQLBuilder(dbHelper);
     sqlBuilder.BeginUpdate(tableName);
     foreach (var parameter in parameters)
     {
         sqlBuilder.SetValue(parameter.Key, parameter.Value);
     }
     sqlBuilder.SetWhere(whereParameters);
     // sqlBuilder.SetDBNow(FieldModifiedOn);
     return sqlBuilder.EndUpdate();
 }

 /// <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="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="tableName">数据来源表名</param>
 /// <param name="name">字段名</param>
 /// <param name="value">字段值</param>
 /// <param name="order">排序</param>
 /// <returns>数据表</returns>
 public static DataTable GetDataTable(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(values) + ")";
     }
     if (!String.IsNullOrEmpty(order))
     {
         sqlQuery += " ORDER BY " + order;
     }
     return dbHelper.Fill(sqlQuery);
 }

        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="dbHelper">数据库连接</param>
        /// <param name="userInfo">用户信息</param>
        /// <param name="userEntity">用户实体</param>
        /// <param name="statusCode">状态码</param>
        /// <param name="statusMessage">状态信息</param>
        /// <returns>主键</returns>
        public string AddUser(IDbHelper dbHelper, BaseUserInfo userInfo, BaseUserEntity userEntity, out string statusCode, out string statusMessage)
        {
            // 加强安全验证防止未授权匿名调用
            #if (!DEBUG)
                BaseSystemInfo.IsAuthorized(userInfo);
            #endif

            string returnValue = string.Empty;
            BaseUserManager userManager = new BaseUserManager(dbHelper, userInfo);
            // 若是系统需要用加密的密码，这里需要加密密码。
            if (BaseSystemInfo.ServerEncryptPassword)
            {
                userEntity.UserPassword = userManager.EncryptUserPassword(userEntity.UserPassword);
                // 安全通讯密码、交易密码也生成好
                userEntity.CommunicationPassword = userManager.EncryptUserPassword(userEntity.CommunicationPassword);
            }
            returnValue = userManager.Add(userEntity, out statusCode);
            statusMessage = userManager.GetStateMessage(statusCode);
            // 自己不用给自己发提示信息，这个提示信息是为了提高工作效率的，还是需要审核通过的，否则垃圾信息太多了
            if (userEntity.Enabled == 0 && statusCode.Equals(StatusCode.OKAdd.ToString()))
            {
                // 不是系统管理员添加
                if (!userInfo.IsAdministrator)
                {
                    // 给超级管理员群组发信息
                    BaseRoleManager roleManager = new BaseRoleManager(dbHelper, userInfo);
                    string[] roleIds = roleManager.GetIds(new KeyValuePair<string, object>(BaseRoleEntity.FieldCode, "Administrators"));
                    string[] userIds = userManager.GetIds(new KeyValuePair<string, object>(BaseUserEntity.FieldCode, "Administrator"));
                    // 发送请求审核的信息
                    BaseMessageEntity messageEntity = new BaseMessageEntity();
                    messageEntity.FunctionCode = MessageFunction.WaitForAudit.ToString();

                    // Pcsky 2012.05.04 显示申请的用户名
                    messageEntity.Contents = userInfo.RealName + "(" + userInfo.IPAddress + ")" + AppMessage.UserService_Application + userEntity.UserName + AppMessage.UserService_Check;
                    //messageEntity.Contents = userInfo.RealName + "(" + userInfo.IPAddress + ")" + AppMessage.UserService_Application + userEntity.RealName + AppMessage.UserService_Check;

                    BaseMessageManager messageManager = new BaseMessageManager(dbHelper, userInfo);
                    messageManager.BatchSend(userIds, null, roleIds, messageEntity, false);
                }
            }
            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="userInfo">用户</param>
        /// <param name="Exception">异常</param>
        /// <returns>主键</returns>
        public static string LogException(IDbHelper dbHelper, BaseUserInfo userInfo, Exception ex)
        {
            // 在控制台需要输出错误信息
            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write(ex.InnerException);
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine(string.Empty);

            string returnValue = string.Empty;
            // 系统里应该可以配置是否记录异常现象
            if (!BaseSystemInfo.LogException)
            {
                return returnValue;
            }
            // Windows系统异常中
            if (BaseSystemInfo.EventLog)
            {
                if (!System.Diagnostics.EventLog.SourceExists(BaseSystemInfo.SoftName))
                {
                    System.Diagnostics.EventLog.CreateEventSource(BaseSystemInfo.SoftName, BaseSystemInfo.SoftFullName);
                }
                System.Diagnostics.EventLog eventLog = new System.Diagnostics.EventLog();
                eventLog.Source = BaseSystemInfo.SoftName;
                eventLog.WriteEntry(ex.Message, EventLogEntryType.Error);
            }
            // 判断一下数据库是否打开状态，若数据库都没能打开，还记录啥错误，不是又抛出另一个错误了？
            if (dbHelper != null && dbHelper.GetDbConnection() != null)
            {
                if (dbHelper.GetDbConnection().State == ConnectionState.Open)
                {
                    BaseExceptionManager exceptionManager = new BaseExceptionManager(dbHelper, userInfo);
                    returnValue = exceptionManager.AddEntity(ex);
                }
            }
            return returnValue;
        }