/// <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); }
public ActiveRecordScope(string connString) { _helper = new SqlDbHelper(connString); _provider = new SqlServerProvider(_helper); _cn = new SqlConnection(connString); _cn.Open(); _tx = _cn.BeginTransaction(); }
/// <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)); }
static DbHelper() { BootStrapServers = new DbHelper<SqlConnection, SqlCommand, SqlParameter>(); BootStrapServers.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["BootStrapServers"].ConnectionString; //OpenService_CDNCooperators = new DbHelper<MySqlConnection, MySqlCommand, MySqlParameter>(); //OpenService_CDNCooperators.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OpenService_CDNCooperators"].ConnectionString; OpenService_Files = new DbHelper<MySqlConnection, MySqlCommand, MySqlParameter>(); OpenService_Files.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OpenService_Files"].ConnectionString; }
/// <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="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); }
/// <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="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="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="conn"></param> private static void GetHelper(string conn) { //判断是否需要新建连接 if (ConnectionString != conn || _helper == null) { //如果原来的连接存在,释放 if (_helper != null && !string.IsNullOrEmpty(conn)) { _helper = null; } if (_helper == null) { lock (_lock) { if (_helper == null) { GetHelper(conn, DatabaseType); } } } } }
/// <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; }
/// <summary> /// SqlBuilder /// </summary> /// <param name="dbHelper"></param> /// <param name="identity"></param> /// <param name="returnId"></param> public SqlBuilder(IDbHelper dbHelper, bool identity, bool returnId) : this(dbHelper) { Identity = identity; ReturnId = returnId; }
public PluginRepository(IDbHelper dbHelper, List <Command> commands) { _dbHelper = dbHelper; _commands = commands; }
/// <summary> /// 构造函数 /// </summary> /// <param name="dbHelper">数据库连接</param> /// <param name="userInfo">用户信息</param> public BaseOrganizeLogOnManager(IDbHelper dbHelper, BaseUserInfo userInfo) : this(dbHelper) { UserInfo = userInfo; }
/// <summary> /// 构造函数 /// </summary> /// <param name="dbHelper">数据库连接</param> /// <param name="userInfo">用户信息</param> /// <param name="tableName">指定表名</param> public BaseOrganizeLogOnManager(IDbHelper dbHelper, BaseUserInfo userInfo, string tableName) : this(dbHelper, userInfo) { base.CurrentTableName = tableName; }
public BaseOrganizeScopeManager(IDbHelper dbHelper, BaseUserInfo userInfo, string tableName) : this(dbHelper, userInfo) { base.CurrentTableName = tableName; }
public BaseParameterManager(IDbHelper dbHelper) : this() { DbHelper = dbHelper; }
public ApplicationServiceBase(ApplicationStrategyStore store, IExceptionLogger logger, IDbHelper db) : base(store, logger) { if (db == null) { throw new ArgumentNullException("db"); } Db = db; Init(); }
protected Service(IUnitOfWork unitOfWork, IDbHelper dbHelper, IMapper mapper) { _unitOfWork = unitOfWork; _IDbHelper = dbHelper; _mapper = mapper; }
/// <summary> /// 根据条件语句取得符合条件的数据表,慎用!!!! /// </summary> /// <param name="m_where">条件语句,不包含“where”</param> /// <returns></returns> public static DataTable getTable(string m_where) { IDbHelper Sql = GetHelper(); return(Sql.ExecuteDataTable(CommandType.Text, "select [ID],[ClassID],[ZtID],[Title],[Content],[UserID],[UserName],[AskTime],[ClickCount] from [Question] where " + m_where)); }
public BillingMemoQueryProcessor(IDbHelper dbHelper) { this.dbHelper = dbHelper; }
public DriverRepository(IDbHelper dbHelper) { this.dbHelper = dbHelper; }
public PeriodicBillingCreatedQueryProcessor(IDbHelper dbHelper) { this.dbHelper = dbHelper; }
/// <summary> /// 构造函数 /// </summary> /// <param name="dbHelper">数据库连接</param> /// <param name="userInfo">用户信息</param> public BaseUserPermissionManager(IDbHelper dbHelper, BaseUserInfo userInfo) : this(dbHelper) { UserInfo = userInfo; }
/// <summary> /// 将数据插入表 /// </summary> /// <param name="M">赋值后的实体</param> /// <returns></returns> public static void Insert(InfoType M) { IDbHelper Sql = GetHelper(); StringBuilder sb = new StringBuilder(); sb.Append("insert into [InfoType]([TypeName],[TemplateIndex],[TemplateList],[TemlateAdminForm],[TemplateForm],[TemplateContent],[num1],[num2],[num3],[num4],[num5],[num6],[num7],[num8],[num9],[num10],[nvarchar1],[nvarchar2],[nvarchar3],[nvarchar4],[nvarchar5],[nvarchar6],[nvarchar7],[nvarchar8],[nvarchar9],[nvarchar10],[nvarchar11],[nvarchar12],[nvarchar13],[nvarchar14],[nvarchar15],[decimal1],[decimal2],[decimal3],[decimal4],[decimal5],[text1],[text2],[text3],[text4],[text5],[bit1],[bit2],[bit3],[bit4],[bit5]) values("); sb.Append("N'" + M.TypeName + "'"); sb.Append(","); sb.Append("N'" + M.TemplateIndex + "'"); sb.Append(","); sb.Append("N'" + M.TemplateList + "'"); sb.Append(","); sb.Append("N'" + M.TemlateAdminForm + "'"); sb.Append(","); sb.Append("N'" + M.TemplateForm + "'"); sb.Append(","); sb.Append("N'" + M.TemplateContent + "'"); sb.Append(","); sb.Append("N'" + M.Num1 + "'"); sb.Append(","); sb.Append("N'" + M.Num2 + "'"); sb.Append(","); sb.Append("N'" + M.Num3 + "'"); sb.Append(","); sb.Append("N'" + M.Num4 + "'"); sb.Append(","); sb.Append("N'" + M.Num5 + "'"); sb.Append(","); sb.Append("N'" + M.Num6 + "'"); sb.Append(","); sb.Append("N'" + M.Num7 + "'"); sb.Append(","); sb.Append("N'" + M.Num8 + "'"); sb.Append(","); sb.Append("N'" + M.Num9 + "'"); sb.Append(","); sb.Append("N'" + M.Num10 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar1 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar2 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar3 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar4 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar5 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar6 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar7 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar8 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar9 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar10 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar11 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar12 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar13 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar14 + "'"); sb.Append(","); sb.Append("N'" + M.Nvarchar15 + "'"); sb.Append(","); sb.Append("N'" + M.Decimal1 + "'"); sb.Append(","); sb.Append("N'" + M.Decimal2 + "'"); sb.Append(","); sb.Append("N'" + M.Decimal3 + "'"); sb.Append(","); sb.Append("N'" + M.Decimal4 + "'"); sb.Append(","); sb.Append("N'" + M.Decimal5 + "'"); sb.Append(","); sb.Append("N'" + M.Text1 + "'"); sb.Append(","); sb.Append("N'" + M.Text2 + "'"); sb.Append(","); sb.Append("N'" + M.Text3 + "'"); sb.Append(","); sb.Append("N'" + M.Text4 + "'"); sb.Append(","); sb.Append("N'" + M.Text5 + "'"); sb.Append(","); sb.Append("N'" + M.Bit1 + "'"); sb.Append(","); sb.Append("N'" + M.Bit2 + "'"); sb.Append(","); sb.Append("N'" + M.Bit3 + "'"); sb.Append(","); sb.Append("N'" + M.Bit4 + "'"); sb.Append(","); sb.Append("N'" + M.Bit5 + "'"); sb.Append(")"); if (DataBase.CmsDbType == DataBase.DbType.SqlServer) { sb.Append(";select @@Identity"); } if (DataBase.CmsDbType == DataBase.DbType.SQLite) { sb.Append(";select last_insert_rowid()"); } if (DataBase.CmsDbType == DataBase.DbType.MySql) { sb.Append(";select LAST_INSERT_ID()"); } if (DataBase.CmsDbType == DataBase.DbType.Access) { sb.Append(";select max(id) from InfoType"); } if (DataBase.CmsDbType == DataBase.DbType.Oracle) { sb.Append(";select LAST_INSERT_ID()"); } M.Id = Sql.ExecuteScalar(CommandType.Text, sb.ToString()).ToInt32(); }
public BaseOrganizeScopeManager(IDbHelper dbHelper) : this() { DbHelper = dbHelper; }
/// <summary> /// 将修改过的实体修改到数据库 /// </summary> /// <param name="M">赋值后的实体</param> /// <returns></returns> public static int Update(InfoType M) { IDbHelper Sql = GetHelper(); StringBuilder sb = new StringBuilder(); sb.Append("update [InfoType] set "); sb.Append("[TypeName]=N'" + M.TypeName + "'"); sb.Append(","); sb.Append("[TemplateIndex]=N'" + M.TemplateIndex + "'"); sb.Append(","); sb.Append("[TemplateList]=N'" + M.TemplateList + "'"); sb.Append(","); sb.Append("[TemlateAdminForm]=N'" + M.TemlateAdminForm + "'"); sb.Append(","); sb.Append("[TemplateForm]=N'" + M.TemplateForm + "'"); sb.Append(","); sb.Append("[TemplateContent]=N'" + M.TemplateContent + "'"); sb.Append(","); sb.Append("[num1]=N'" + M.Num1 + "'"); sb.Append(","); sb.Append("[num2]=N'" + M.Num2 + "'"); sb.Append(","); sb.Append("[num3]=N'" + M.Num3 + "'"); sb.Append(","); sb.Append("[num4]=N'" + M.Num4 + "'"); sb.Append(","); sb.Append("[num5]=N'" + M.Num5 + "'"); sb.Append(","); sb.Append("[num6]=N'" + M.Num6 + "'"); sb.Append(","); sb.Append("[num7]=N'" + M.Num7 + "'"); sb.Append(","); sb.Append("[num8]=N'" + M.Num8 + "'"); sb.Append(","); sb.Append("[num9]=N'" + M.Num9 + "'"); sb.Append(","); sb.Append("[num10]=N'" + M.Num10 + "'"); sb.Append(","); sb.Append("[nvarchar1]=N'" + M.Nvarchar1 + "'"); sb.Append(","); sb.Append("[nvarchar2]=N'" + M.Nvarchar2 + "'"); sb.Append(","); sb.Append("[nvarchar3]=N'" + M.Nvarchar3 + "'"); sb.Append(","); sb.Append("[nvarchar4]=N'" + M.Nvarchar4 + "'"); sb.Append(","); sb.Append("[nvarchar5]=N'" + M.Nvarchar5 + "'"); sb.Append(","); sb.Append("[nvarchar6]=N'" + M.Nvarchar6 + "'"); sb.Append(","); sb.Append("[nvarchar7]=N'" + M.Nvarchar7 + "'"); sb.Append(","); sb.Append("[nvarchar8]=N'" + M.Nvarchar8 + "'"); sb.Append(","); sb.Append("[nvarchar9]=N'" + M.Nvarchar9 + "'"); sb.Append(","); sb.Append("[nvarchar10]=N'" + M.Nvarchar10 + "'"); sb.Append(","); sb.Append("[nvarchar11]=N'" + M.Nvarchar11 + "'"); sb.Append(","); sb.Append("[nvarchar12]=N'" + M.Nvarchar12 + "'"); sb.Append(","); sb.Append("[nvarchar13]=N'" + M.Nvarchar13 + "'"); sb.Append(","); sb.Append("[nvarchar14]=N'" + M.Nvarchar14 + "'"); sb.Append(","); sb.Append("[nvarchar15]=N'" + M.Nvarchar15 + "'"); sb.Append(","); sb.Append("[decimal1]=N'" + M.Decimal1 + "'"); sb.Append(","); sb.Append("[decimal2]=N'" + M.Decimal2 + "'"); sb.Append(","); sb.Append("[decimal3]=N'" + M.Decimal3 + "'"); sb.Append(","); sb.Append("[decimal4]=N'" + M.Decimal4 + "'"); sb.Append(","); sb.Append("[decimal5]=N'" + M.Decimal5 + "'"); sb.Append(","); sb.Append("[text1]=N'" + M.Text1 + "'"); sb.Append(","); sb.Append("[text2]=N'" + M.Text2 + "'"); sb.Append(","); sb.Append("[text3]=N'" + M.Text3 + "'"); sb.Append(","); sb.Append("[text4]=N'" + M.Text4 + "'"); sb.Append(","); sb.Append("[text5]=N'" + M.Text5 + "'"); sb.Append(","); sb.Append("[bit1]=N'" + M.Bit1 + "'"); sb.Append(","); sb.Append("[bit2]=N'" + M.Bit2 + "'"); sb.Append(","); sb.Append("[bit3]=N'" + M.Bit3 + "'"); sb.Append(","); sb.Append("[bit4]=N'" + M.Bit4 + "'"); sb.Append(","); sb.Append("[bit5]=N'" + M.Bit5 + "'"); sb.Append(" where Id='" + M.Id + "'"); sb.Append(""); if (DataBase.CmsDbType == DataBase.DbType.SqlServer) { sb.Append(";select @@ROWCOUNT"); } if (DataBase.CmsDbType == DataBase.DbType.SQLite) { sb.Append(";select 0"); } if (DataBase.CmsDbType == DataBase.DbType.MySql) { sb.Append(";SELECT ROW_COUNT()"); } if (DataBase.CmsDbType == DataBase.DbType.Access) { sb.Append(";select 0"); } if (DataBase.CmsDbType == DataBase.DbType.Oracle) { sb.Append(";select SQL%ROWCOUNT"); } return(Sql.ExecuteScalar(CommandType.Text, sb.ToString()).ToInt32()); }
public DirectoryController(ITagService tagService, IDbHelper dbHelper, IDirectoryService directoryService) { _directoryService = directoryService; _dbHelper = dbHelper; _tagService = tagService; }
/// <summary> /// 根据条件语句取得第一个实体 /// </summary> /// <param name="m_where">条件语句,不包含“where”</param> /// <returns></returns> public static InfoType Find(string m_where) { IDbHelper Sql = GetHelper(); InfoType M = new InfoType(); DbDataReader Rs = Sql.ExecuteReader(CommandType.Text, "select [id],[TypeName],[TemplateIndex],[TemplateList],[TemlateAdminForm],[TemplateForm],[TemplateContent],[num1],[num2],[num3],[num4],[num5],[num6],[num7],[num8],[num9],[num10],[nvarchar1],[nvarchar2],[nvarchar3],[nvarchar4],[nvarchar5],[nvarchar6],[nvarchar7],[nvarchar8],[nvarchar9],[nvarchar10],[nvarchar11],[nvarchar12],[nvarchar13],[nvarchar14],[nvarchar15],[decimal1],[decimal2],[decimal3],[decimal4],[decimal5],[text1],[text2],[text3],[text4],[text5],[bit1],[bit2],[bit3],[bit4],[bit5] from [InfoType] where " + m_where, true); if (!Rs.Read()) { M.Id = 0; } else { M.Id = Rs["id"].ToInt32(); M.TypeName = Rs["TypeName"].ToString(); M.TemplateIndex = Rs["TemplateIndex"].ToString(); M.TemplateList = Rs["TemplateList"].ToString(); M.TemlateAdminForm = Rs["TemlateAdminForm"].ToString(); M.TemplateForm = Rs["TemplateForm"].ToString(); M.TemplateContent = Rs["TemplateContent"].ToString(); M.Num1 = Rs["num1"].ToString(); M.Num2 = Rs["num2"].ToString(); M.Num3 = Rs["num3"].ToString(); M.Num4 = Rs["num4"].ToString(); M.Num5 = Rs["num5"].ToString(); M.Num6 = Rs["num6"].ToString(); M.Num7 = Rs["num7"].ToString(); M.Num8 = Rs["num8"].ToString(); M.Num9 = Rs["num9"].ToString(); M.Num10 = Rs["num10"].ToString(); M.Nvarchar1 = Rs["nvarchar1"].ToString(); M.Nvarchar2 = Rs["nvarchar2"].ToString(); M.Nvarchar3 = Rs["nvarchar3"].ToString(); M.Nvarchar4 = Rs["nvarchar4"].ToString(); M.Nvarchar5 = Rs["nvarchar5"].ToString(); M.Nvarchar6 = Rs["nvarchar6"].ToString(); M.Nvarchar7 = Rs["nvarchar7"].ToString(); M.Nvarchar8 = Rs["nvarchar8"].ToString(); M.Nvarchar9 = Rs["nvarchar9"].ToString(); M.Nvarchar10 = Rs["nvarchar10"].ToString(); M.Nvarchar11 = Rs["nvarchar11"].ToString(); M.Nvarchar12 = Rs["nvarchar12"].ToString(); M.Nvarchar13 = Rs["nvarchar13"].ToString(); M.Nvarchar14 = Rs["nvarchar14"].ToString(); M.Nvarchar15 = Rs["nvarchar15"].ToString(); M.Decimal1 = Rs["decimal1"].ToString(); M.Decimal2 = Rs["decimal2"].ToString(); M.Decimal3 = Rs["decimal3"].ToString(); M.Decimal4 = Rs["decimal4"].ToString(); M.Decimal5 = Rs["decimal5"].ToString(); M.Text1 = Rs["text1"].ToString(); M.Text2 = Rs["text2"].ToString(); M.Text3 = Rs["text3"].ToString(); M.Text4 = Rs["text4"].ToString(); M.Text5 = Rs["text5"].ToString(); M.Bit1 = Rs["bit1"].ToString(); M.Bit2 = Rs["bit2"].ToString(); M.Bit3 = Rs["bit3"].ToString(); M.Bit4 = Rs["bit4"].ToString(); M.Bit5 = Rs["bit5"].ToString(); } Rs.Close(); Rs = null; return(M); }
/// <summary> /// 构造函数 /// </summary> /// <param name="dbHelper">数据库连接</param> public BaseOrganizeLogOnManager(IDbHelper dbHelper) : this() { DbHelper = dbHelper; }
/// <summary> /// 根据条件语句取得符合条件的数据表,慎用!!!! /// </summary> /// <param name="m_where">条件语句,不包含“where”</param> /// <returns></returns> public static DataTable getTable(string m_where) { IDbHelper Sql = GetHelper(); return(Sql.ExecuteDataTable(CommandType.Text, "select [id],[TypeName],[TemplateIndex],[TemplateList],[TemlateAdminForm],[TemplateForm],[TemplateContent],[num1],[num2],[num3],[num4],[num5],[num6],[num7],[num8],[num9],[num10],[nvarchar1],[nvarchar2],[nvarchar3],[nvarchar4],[nvarchar5],[nvarchar6],[nvarchar7],[nvarchar8],[nvarchar9],[nvarchar10],[nvarchar11],[nvarchar12],[nvarchar13],[nvarchar14],[nvarchar15],[decimal1],[decimal2],[decimal3],[decimal4],[decimal5],[text1],[text2],[text3],[text4],[text5],[bit1],[bit2],[bit3],[bit4],[bit5] from [InfoType] where " + m_where)); }
public MatchingHistorySearchQueryProcessor(IDbHelper dbHelper) { this.dbHelper = dbHelper; }
internal DBQueue() { _dbHelper = new SQLite("rtx.db",false,false); }
public CustomerGroupQueryProcessor(IDbHelper dbHelper) { this.dbHelper = dbHelper; }
public BillingScheduledIncomeQueryProcessor(IDbHelper dbHelper) { this.dbHelper = dbHelper; }
/// <summary> /// SqlBuilder /// </summary> /// <param name="dbHelper"></param> /// <param name="identity"></param> public SqlBuilder(IDbHelper dbHelper, bool identity) : this(dbHelper) { Identity = identity; }
public override int Update(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition, IDictionary <string, object> updates) { ITable table = mappingProvider.GetTable(entityType); QueryExpression queryExpression = new QueryExpression(); queryExpression.EntityType = entityType; foreach (string propertyName in updates.Keys) { queryExpression.Selects.Add(propertyName); } queryExpression.Wheres.Add(condition); SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression); var sb = new StringBuilder(); var command = db.Connection.CreateCommand(); int parameterCounter = 1; int index; sb.Append("update "); sb.Append(GetTableName(entityType, mappingProvider, tableMapping)); sb.Append(" "); sb.Append(sqlAnalyseResult.MasterTableNameAlias); //join foreach (SqlTable sqlTable in sqlAnalyseResult.ForeignTables) { sb.Append(" left outer join "); sb.Append(GetTableName(entityType, mappingProvider, tableMapping)); sb.Append(" "); sb.Append(sqlTable.ForeignTableNameAlias); sb.Append(" on "); index = 0; foreach (SqlRelationColumn sqlRelationColumn in sqlTable.RelationColumns) { if (index > 0) { sb.Append(" and "); } index++; sb.Append(sqlRelationColumn.Expression); } } sb.Append(" set "); index = 0; foreach (KeyValuePair <string, object> kv in updates) { if (index > 0) { sb.Append(","); } index++; IColumn column = table.Columns[kv.Key]; sb.Append(sqlAnalyseResult.MasterTableNameAlias); sb.Append("."); sb.Append(column.ColumnName); sb.Append("="); sb.Append(CreateParameterNameInStatement(parameterCounter)); //object propertyValue = column.PropertyAdapter.GetValue(obj); //Validate(column, propertyValue);//验证如:检查字符串长度,是否为null AddParameter(command.Parameters, ref parameterCounter, kv.Value, column.PropertyType, column.Size); } //where if (queryExpression.Wheres.Count > 0) { sb.Append(" where "); GenerateSelectSql_Where(sb, command.Parameters, ref parameterCounter, queryExpression.Wheres, sqlAnalyseResult, true, mappingProvider); } //执行sql command.CommandText = sb.ToString(); command.CommandType = CommandType.Text; int returnCount = db.ExecuteNonQuery(command); return(returnCount); }
public FolderDAL(IDbHelper db) : base(db) { }
/// <summary> /// 构造函数 /// </summary> /// <param name="dbHelper">数据库连接</param> /// <param name="userInfo">用户信息</param> /// <param name="tableName">指定表名</param> public UserByIdentityManager(IDbHelper dbHelper, BaseUserInfo userInfo, string tableName) : this(dbHelper, userInfo) { base.CurrentTableName = tableName; }
/// <summary> /// 构造函数 /// </summary> /// <param name="dbHelper">数据库连接</param> public BaseUserPermissionManager(IDbHelper dbHelper) : this() { DbHelper = dbHelper; }
/// <summary> /// 构造函数 /// </summary> /// <param name="dbHelper">数据库连接</param> public UserByIdentityManager(IDbHelper dbHelper) : this() { DbHelper = dbHelper; }
/// <summary> /// 构造函数 /// </summary> /// <param name="dbHelper">数据库连接</param> /// <param name="userInfo">用户信息</param> /// <param name="tableName">指定表名</param> public BaseUserPermissionManager(IDbHelper dbHelper, BaseUserInfo userInfo, string tableName) : this(dbHelper, userInfo) { base.CurrentTableName = tableName; }
/// <summary> /// 构造函数 /// </summary> /// <param name="dbHelper">数据库连接</param> /// <param name="userInfo">用户信息</param> public UserByIdentityManager(IDbHelper dbHelper, BaseUserInfo userInfo) : this(dbHelper) { UserInfo = userInfo; }
public BaseParameterManager(IDbHelper dbHelper, BaseUserInfo userInfo) : this() { DbHelper = dbHelper; UserInfo = userInfo; }
public AuditServerImpl(IDbHelper dbHelper = null, ILogServer logServer = null) : base(dbHelper, logServer) { }
public BaseOrganizeScopeManager(IDbHelper dbHelper, BaseUserInfo userInfo) : this(dbHelper) { UserInfo = userInfo; }
public DateService(DateStrategyStore store, IExceptionLogger logger, IDbHelper db) : base(store, logger, db) { Init(); }
/// <summary> /// 获取符合条件记录的条数 /// </summary> /// <param name="m_where">条件语句,不包含“where”</param> /// <returns></returns> public static int Count(string m_where) { IDbHelper Sql = GetHelper(); return(Convert.ToInt32(Sql.ExecuteScalar(CommandType.Text, "select count(0) from [InfoType] where " + m_where))); }
public AggregatedEntityRepository(IDbHelper dbHelper) { DbHelper = dbHelper; }
public Program(IDbHelper helper) { _helper = helper; }
public override int Insert(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, IDictionary <string, object> inserts, IDictionary <string, object> returns) { ITable table = mappingProvider.GetTable(entityType); var sb = new StringBuilder(); var command = new MySqlCommand(); int parameterCounter = 1; #region 循环每个属性 string tableName = GetTableName(entityType, mappingProvider, tableMapping); sb.Append("insert into "); sb.Append(tableName); sb.Append(" ("); //Oracle存储过程参数有顺序之分 //List<OracleParameter> returnParameters = new List<OracleParameter>();//取回的存储过程 Dictionary <string, string> returnColumns = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase); //属性名是键,sql参数名是值 int index = 0; foreach (KeyValuePair <string, object> kv in inserts) { if (index > 0) { sb.Append(","); } var column = table.Columns[kv.Key]; if (column.InsertBehavior.Generate) { sb.Append(column.ColumnName); index++; } } sb.Append(") values ("); index = 0; foreach (KeyValuePair <string, object> kv in inserts) { if (index > 0) { sb.Append(","); } IColumn column = table.Columns[kv.Key]; if (column.InsertBehavior.Generate) { if (column.InsertBehavior.ValueBehavior == ValueBehavior.UseValueExpression) { sb.Append(column.InsertBehavior.ValueExpression); } else { sb.Append(CreateParameterNameInStatement(parameterCounter)); //object propertyValue = column.PropertyAdapter.GetValue(obj); //Validate(column, propertyValue);//验证如:检查字符串长度,是否为null AddParameter(command.Parameters, ref parameterCounter, kv.Value, column.PropertyType, column.Size); } index++; } } sb.Append(")"); foreach (string propertyName in table.PrimaryProperties) { ColumnWithIdentity column = (ColumnWithIdentity)table.Columns[propertyName]; //取回主键值 ,本版本只支持主键值的取回 if (column.IsIdentity && column.ReturnAfterInsert) { string pname = CreateParameterName(parameterCounter); returnColumns.Add(propertyName, pname); sb.Append(";select LAST_INSERT_ID();"); break;//只能有一个 } } #endregion command.CommandText = sb.ToString(); command.CommandType = CommandType.Text; //执行sql object returnValue = db.ExecuteScalar(command); //read return value foreach (KeyValuePair <string, string> item in returnColumns) { returns.Add(item.Key, ConvertDbValue(returnValue, table.Columns[item.Key].PropertyType)); break;//只能有一个 } return(1); }