/// <summary> /// Checks which invite link has been used by newly joined user and /// updates corresponding record in database for it /// </summary> /// <param name="user">newly joined user</param> private async Task OnUserJoinedAsync(SocketGuildUser user) { await using var dbContext = DatabaseFactory.Create(); var inviteLinks = await user.Guild.GetInvitesAsync(); var inviteeDb = await dbContext.GetOrCreateUserEntityAsync(user); try { await user.SendMessageAsync(Config.GetValue <string>("Welcoming:DirectMessage")); } catch (HttpException ex) when(ex.DiscordCode == 50007) { // User has disabled DMs } foreach (var link in inviteLinks) { if (link.Inviter.Id != DiscordSocketClient.CurrentUser.Id) { continue; } if (link.Uses == 0) { continue; } var invite = await dbContext.Invites.AsQueryable() .Where(i => i.InviteLink == link.Url) .FirstOrDefaultAsync(); if (invite == null) { continue; } invite.UsedByUserId = inviteeDb.Id; await dbContext.SaveChangesAsync(); await link.DeleteAsync(); var welcomeMessageTemplate = Config.GetValue <string>("Welcoming:Message"); if (welcomeMessageTemplate == null) { return; } var welcomeMessage = string.Format( welcomeMessageTemplate, await UsersService.GetDisplayNameAsync(user) ); await user.Guild.DefaultChannel.SendMessageAsync(welcomeMessage); return; } await user.Guild.DefaultChannel.SendMessageAsync( $"Nový uživatel **{Format.Sanitize(user.Username)}** se připojil na server pomocí odkazu, který nebyl vytvořen `$invite` příkazem!"); }
public void Test_Initialization() { var dbConfig = new DbConfig() { ConnectionString = "Server=127.0.0.1;Port=3306; Uid=root; Pwd=1197344; Database=test;", DatabaseType = DatabaseType.MySql, SchemaName = "test", AuditRecordType = typeof(AuditRecord) }; DatabaseFactory.Initialize(dbConfig); database = DatabaseFactory.Create(); Mapping.SetNextId <Employee>(NextIdOption.AutoIncrement); Mapping.SetNextId <Program>(NextIdOption.AutoIncrement); Mapping.SetNextId <EmployeeProgram>(NextIdOption.AutoIncrement); Mapping.SetNextId <Address>(NextIdOption.AutoIncrement); Mapping.SetNextId <FinancialInfo>(NextIdOption.AutoIncrement); Mapping.SetNextId <AdditionalInfo>(NextIdOption.AutoIncrement); Mapping.SetNextId <Lookup>(NextIdOption.AutoIncrement); Mapping.SetNextId <AuditRecord>(NextIdOption.AutoIncrement); }
public void GetAllBots_ExistingBots_ShouldReturnListOfBots() { var databaseContext = DatabaseFactory.Create(); var timeProvider = TimeProviderFactory.Create(); var botService = new BotsService(databaseContext, timeProvider); databaseContext.Bots.Add(new BotModel { Id = "1000", Name = "Bot1", CreateTime = timeProvider.Get() }); databaseContext.Bots.Add(new BotModel { Id = "1001", Name = "Bot2", CreateTime = timeProvider.Get() }); databaseContext.SaveChanges(); var result = botService.GetAllBots(); Assert.Equal(2, result.Count); Assert.Equal("1000", result[0].Id); Assert.Equal("1001", result[1].Id); }
public SchemaPropertyExtension Load(string targetSchemaType, string sourceID) { string sql = "SELECT TOP 1 * FROM SC.SchemaPropertyExtensions WHERE "; WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendCondition("TargetSchemaType", targetSchemaType); builder.AppendCondition("SourceID", sourceID); sql += builder.ToSqlString(TSqlBuilder.Instance); SchemaPropertyExtension obj = null; using (DbContext context = DbContext.GetContext(GetConnectionName())) { Database db = DatabaseFactory.Create(context); using (var dr = db.ExecuteReader(System.Data.CommandType.Text, sql)) { if (dr.Read()) { obj = new SchemaPropertyExtension(dr["TargetSchemaType"].ToString(), dr["SourceID"].ToString(), dr["Description"].ToString()); obj.InternalDefinitionXml = dr["Definition"].ToString(); } } } return(obj); }
/// <summary>获取费用单位开放权限列表 /// </summary> /// <param name="filialeId">公司ID</param> /// <param name="branchId">部门ID</param> /// <param name="companyId">往来单位ID</param> /// <returns></returns> public IEnumerable <CostPermissionInfo> GetCostPermissionList(Guid filialeId, Guid branchId, Guid companyId) { string sql = @" SELECT CP.FilialeId,CP.BranchId,CC.CompanyId,CC.CompanyName FROM CostCompany CC INNER JOIN CostPermission CP ON CC.CompanyId=CP.CompanyId WHERE 1=1 "; var parms = new[] { new Parameter("@CompanyId", companyId), new Parameter(PARM_FILIALE_ID, filialeId), new Parameter(PARM_BRANCH_ID, branchId) }; if (filialeId != Guid.Empty) { sql += " AND CP.FilialeId=@FilialeId"; } if (branchId != Guid.Empty) { sql += " AND CP.BranchId=@BranchId"; } if (companyId != Guid.Empty) { sql += " and CC.CompanyId=@CompanyId"; } using (var db = DatabaseFactory.Create()) { return(db.Select <CostPermissionInfo>(true, sql, parms)); } }
/// <summary> /// 读取没有安排到任务中的JobID的集合 /// </summary> /// <param name="batchCount"></param> /// <returns></returns> public IList <string> LoadUnTaskedJobs(int batchCount, TimeSpan timeOffset) { string sqlTemplate = "SELECT {0} J.JOB_ID FROM WF.JOBS J LEFT JOIN WF.SYS_NOT_RUNNING_TASK T WITH(UPDLOCK READPAST) ON J.JOB_ID = T.RESOURCE_ID WHERE (T.STATUS IS NULL) AND {1} ORDER BY J.CREATE_TIME"; string top = batchCount >= 0 ? "TOP " + batchCount : string.Empty; WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("ENABLED", "1"); WhereSqlClauseBuilder builder2 = new WhereSqlClauseBuilder(LogicOperatorDefine.Or); builder2.AppendItem("LAST_START_EXE_TIME", string.Format("DATEADD(SECOND, -{0}, GETDATE())", (int)timeOffset.TotalSeconds), "<", true); builder2.AppendItem("LAST_START_EXE_TIME", "NULL", "IS", true); string sql = string.Format(sqlTemplate, top, new ConnectiveSqlClauseCollection(builder, builder2).ToSqlString(TSqlBuilder.Instance)); List <string> result = new List <string>(800); using (DbContext context = DbContext.GetContext(this.GetConnectionName())) { Database db = DatabaseFactory.Create(context); DataTable table = db.ExecuteDataSet(CommandType.Text, sql).Tables[0]; foreach (DataRow row in table.Rows) { result.Add(row["JOB_ID"].ToString()); } } return(result); }
public override void DeleteOperation() { DataView users = new DataView(this.Context.OriginalData.Tables["USERS"], string.Empty, "GUID", DataViewRowState.Deleted); Database db = DatabaseFactory.Create(this.Context.InitialParams.AccreditAdminConnectionName); foreach (DataRowView drv in users) { try { string user_delete = string.Format("DELETE USERS WHERE GUID = '{0}'", drv["GUID"].ToString()); int count = db.ExecuteNonQuery(CommandType.Text, user_delete); if (count == 1) { this.DeleteCount++; this.Context.InitialParams.Log.Write(string.Format("USERS表GUID是{0},LogonName是{1}的记录删除成功", drv["GUID"].ToString(), drv["LOGON_NAME"].ToString())); } } catch (Exception ex) { this.Context.InitialParams.Log.Write(string.Format("用户表GUID 是{0},LogonName是{1}的记录执行删除时出错,错误是{2}", drv["GUID"].ToString(), drv["LOGON_NAME"].ToString(), ex.Message)); } } }
public void Log_ExistingBot_ShouldReturnTrueAndSaveInDatabase() { var databaseContext = DatabaseFactory.Create(); var timeProvider = TimeProviderFactory.Create(); var statsService = new StatsService(databaseContext, timeProvider); databaseContext.Bots.Add(new BotModel { Id = "1", Name = "Bot1" }); databaseContext.SaveChanges(); var stats = new LogStatsDto { BotId = "1", ExecutedCommandsCount = 2, GuildsCount = 3, MembersCount = 4 }; var result = statsService.Log(stats); var loggedStats = databaseContext.Stats.First(); Assert.True(result); Assert.Equal(1, databaseContext.Stats.Count()); Assert.Equal("1", loggedStats.BotId); Assert.Equal(timeProvider.Get(), loggedStats.CreateTime); Assert.Equal(2, loggedStats.ExecutedCommandsCount); Assert.Equal(3, loggedStats.GuildsCount); Assert.Equal(4, loggedStats.MembersCount); }
public void GetStatsForBot_ExistingBot_ShouldReturnListOfStats() { var databaseContext = DatabaseFactory.Create(); var timeProvider = TimeProviderFactory.Create(); var statsService = new StatsService(databaseContext, timeProvider); databaseContext.Bots.Add(new BotModel { Id = "1", Name = "Bot1", Stats = new List <StatsModel> { new StatsModel { BotId = "1", CreateTime = timeProvider.Get(), ExecutedCommandsCount = 1001 }, new StatsModel { BotId = "1", CreateTime = timeProvider.Get().AddDays(1), ExecutedCommandsCount = 1002 }, new StatsModel { BotId = "1", CreateTime = timeProvider.Get().AddDays(1), ExecutedCommandsCount = 1003 } } }); databaseContext.SaveChanges(); var result = statsService.GetStatsForBot("1"); Assert.Equal(2, result.Count); Assert.Equal(1001, result[0].ExecutedCommandsCount); Assert.Equal(1003, result[1].ExecutedCommandsCount); }
private DataSet DoSplitPageQuery(QueryCondition qc, bool retrieveTotalCount) { string sql = GetQuerySql(qc, retrieveTotalCount); using (DbContext context = DbContext.GetContext(string.IsNullOrEmpty(this.ConnectionName) ? ConnectionDefine.DBConnectionName : this.ConnectionName)) { DataSet ds = null; int serverVersion = Convert.ToInt32(context.Connection.ServerVersion.Split('.')[0]); Database db = DatabaseFactory.Create(context); //根据SQL Server版本选择分页语句的写法 if (serverVersion > 8) { ds = db.ExecuteDataSet(CommandType.Text, sql, "RESULT", "RESULT_COUNT"); } else { ds = db.ExecuteDataSet("CommonSplitPageQuery", qc.SelectFields, qc.FromClause, qc.WhereClause, qc.OrderByClause, qc.PrimaryKey, qc.RowIndex / qc.PageSize + 1, qc.PageSize, 1); } return(ds); } }
//public SearchOrgChildrenCondition(string strRootGuids, string strAttrs, DataAccess da) //{ // InitSearchOrgChildrenCondition(strRootGuids, strAttrs, da); //} /// <summary> /// 构造函数(构造数据对象查询条件) /// </summary> /// <param name="strRootValues">指定的机构标识(多个之间采用“,”分隔)</param> /// <param name="soc">查询要求的查询列名称 /// (GUID、USER_GUID、LOGON_NAME、ORIGINAL_SORT、GLOBAL_SORT、ALL_PATH_NAME)</param> /// <param name="strAttrs">要求获取的属性</param> public SearchOrgChildrenCondition(string strRootValues, SearchObjectColumn soc, string strAttrs) { using (DbContext context = DbContext.GetContext(CommonResource.AccreditConnAlias)) { string strRootGuids = string.Empty; if (strRootValues.Length > 0 && soc != SearchObjectColumn.SEARCH_GUID) { Database database = DatabaseFactory.Create(context); DataSet ds = OGUReader.GetObjectsDetail("ORGANIZATIONS", strRootValues, soc, string.Empty, SearchObjectColumn.SEARCH_NULL, string.Empty); foreach (DataRow row in ds.Tables[0].Rows) { if (strRootGuids.Length > 0) { strRootGuids += ","; } strRootGuids += row["GUID"].ToString(); } } else { strRootGuids = strRootValues; } InitSearchOrgChildrenCondition(strRootGuids, strAttrs, string.IsNullOrEmpty(strRootValues) ? true : false); } }
public IEnumerable <ReferenceHistoryEntry> GetReferenceHistoryEntries(string objId) { ReferenceHistoryEntryCollection list = new ReferenceHistoryEntryCollection(); WhereSqlClauseBuilder where1 = new WhereSqlClauseBuilder(); where1.AppendItem("R.ObjectID", objId); WhereSqlClauseBuilder where2 = new WhereSqlClauseBuilder(); where2.AppendItem("R.ParentID", objId); var sql = string.Format(@"SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ParentID = O.ID WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {0} UNION SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ObjectID = O.ID WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {1} ORDER BY VersionStartTime ASC ", where1.ToSqlString(TSqlBuilder.Instance), where2.ToSqlString(TSqlBuilder.Instance)); using (var context = DbHelper.GetDBContext(this.GetConnectionName())) { Database db = DatabaseFactory.Create(context); var cmd = db.GetSqlStringCommand(sql); using (var dr = db.ExecuteReader(cmd)) { ORMapping.DataReaderToCollection(list, dr); } } return(list); }
/// <summary> /// 获取与指定对象关联的条件历史 /// </summary> /// <param name="id">对象ID</param> /// <param name="type">类型,如果为<see langword="null"/>则不限。</param> /// <returns></returns> public IEnumerable <SCCondition> GetConditionHistoryEntries(string id, string type) { SCConditionCollection entries = new SCConditionCollection(); using (var context = DbHelper.GetDBContext(this.GetConnectionName())) { Database db = DatabaseFactory.Create(context); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("OwnerID", id); if (type != null) { builder.AppendItem("Type", type); } var sql = @"SELECT * FROM [SC].[Conditions] WHERE " + builder.ToSqlString(TSqlBuilder.Instance) + " ORDER BY [VersionEndTime] DESC, SortID ASC"; var cmd = db.GetSqlStringCommand(sql); using (var dr = db.ExecuteReader(cmd)) { ORMapping.DataReaderToCollection(entries, dr); } return(entries); } }
public void Test() { Database db = DatabaseFactory.Create("LocalSQL"); DataSet dataset = db.ExecuteDataSet("SELECT * FROM Shippers"); DbTraceHelper.TraceData(dataset); }
/// <summary> /// 保持SignInInfo信息 /// </summary> /// <param name="signInInfo">SignInInfo信息</param> /// <remarks> /// <code source="..\Framework\TestProjects\DeluxeWorks.Library.Passport.Test\DataObjectsTest.cs" region="SignInInfoPersistTest" lang="cs" title="保存SignInInfo对象" /> /// </remarks> public void SaveSignInInfo(ISignInInfo signInInfo) { ORMappingItemCollection mapping = LoadMappingFromResource("MCS.Library.Passport.DataObjects.SignInInfoMapping.xml", typeof(ISignInInfo)); string sql = string.Format("UPDATE PASSPORT_SIGNIN_INFO SET {0} WHERE {1}", ORMapping.GetUpdateSqlClauseBuilder(signInInfo, mapping).ToSqlString(TSqlBuilder.Instance), ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(signInInfo, mapping).ToSqlString(TSqlBuilder.Instance)); using (TransactionScope scope = TransactionScopeFactory.Create()) { using (DbContext context = DbContext.GetContext(DataAdapter.DBConnectionName)) { Database db = DatabaseFactory.Create(DataAdapter.DBConnectionName); if (db.ExecuteNonQuery(CommandType.Text, sql) == 0) { sql = string.Format("INSERT INTO PASSPORT_SIGNIN_INFO {0}", ORMapping.GetInsertSqlClauseBuilder(signInInfo, mapping).ToSqlString(TSqlBuilder.Instance)); db.ExecuteNonQuery(CommandType.Text, sql); } } scope.Complete(); } }
/// <summary> /// 多条采购单存在同主商品且有一条待调价的记录 /// 调价审核通过时同步更新未提交的采购单中的商品采购价 /// </summary> /// <param name="realGoodsIds">子商品列表</param> /// <param name="companyId">主商品绑定公司</param> /// <param name="price"> </param> /// <param name="warehouseId"></param> /// <param name="hostingFilialeId"></param> /// <returns></returns> public bool UpdatePurchasingDetailPrice(IEnumerable <Guid> realGoodsIds, Guid companyId, decimal price, Guid warehouseId, Guid hostingFilialeId) { var builder = new StringBuilder(@"update lmShop_PurchasingDetail set Price=@Price,CPrice=@Price where PurchasingID in(select PurchasingID from lmShop_Purchasing where PurchasingState=0 and CompanyID=@CompanyID and WarehouseID=@WarehouseId and PurchasingFilialeId=@PurchasingFilialeId) and GoodsID in( "); var realGoodsIdStr = new StringBuilder(""); var parms = new[] { new Parameter("@Price", price), new Parameter("@CompanyID", companyId), new Parameter("@WarehouseId", warehouseId), new Parameter("@PurchasingFilialeId", hostingFilialeId), }; foreach (var id in realGoodsIds) { if (id != Guid.Empty) { if (realGoodsIdStr.Length == 0) { realGoodsIdStr.Append(id); } else { realGoodsIdStr.Append(",").Append(id); } } } builder.Append("select id as RealGoodsId from splitToTable('" + realGoodsIdStr + "',','))"); using (var db = DatabaseFactory.Create()) { return(db.Execute(false, builder.ToString(), parms)); } }
public JobCollection UPDLOCKLoadJobs(int batchCount, IConnectiveSqlClause whereClause) { JobCollection result = new JobCollection(); if (whereClause.IsEmpty == false) { using (DbContext context = DbContext.GetContext(this.GetConnectionName())) { string top = batchCount >= 0 ? "TOP " + batchCount : string.Empty; Database db = DatabaseFactory.Create(context); using (IDataReader dr = db.ExecuteReader(CommandType.Text, string.Format(UPDLOCK_LOAD_JOBS, top, whereClause.AppendTenantCodeSqlClause(typeof(JobBase)).ToSqlString(TSqlBuilder.Instance)))) { ORMapping.DataReaderToCollection(result, dr); } } AfterLoad(result); } return(result); }
/// <summary> /// 修改采购单商品价格,排除赠品 /// </summary> /// <param name="purchasingId"></param> /// <param name="realGoodsIds"></param> /// <param name="price"></param> /// <param name="cprice"></param> /// <returns></returns> public bool UpdateDetailsPrice(Guid purchasingId, List <Guid> realGoodsIds, decimal price, decimal cprice) { if (realGoodsIds.Count > 0) { var strbSql = new StringBuilder(); strbSql.Append("UPDATE lmShop_PurchasingDetail SET Price=").Append(price).Append(",CPrice=").Append(cprice); strbSql.Append(" WHERE Price!=0 AND PurchasingID='").Append(purchasingId).Append("'"); var strbRealGoodsIds = new StringBuilder(); foreach (var id in realGoodsIds) { if (id != Guid.Empty) { if (strbRealGoodsIds.Length == 0) { strbRealGoodsIds.Append(id); } else { strbRealGoodsIds.Append(",").Append(id); } } } strbSql.Append(" AND GoodsId IN (select id as RealGoodsId from splitToTable('" + strbRealGoodsIds + "',','))"); using (var db = DatabaseFactory.Create()) { return(db.Execute(false, strbSql.ToString())); } } return(false); }
public JobBase LoadOneUnTaskedJob(string jobID) { string sqlTemplate = "SELECT J.* FROM WF.JOBS J WITH(UPDLOCK READPAST) LEFT JOIN WF.SYS_NOT_RUNNING_TASK T WITH(UPDLOCK READPAST) ON J.JOB_ID = T.RESOURCE_ID WHERE (T.STATUS IS NULL) AND J.JOB_ID = {0}"; string sql = string.Format(sqlTemplate, TSqlBuilder.Instance.CheckUnicodeQuotationMark(jobID)); JobBase result = null; using (DbContext context = DbContext.GetContext(this.GetConnectionName())) { Database db = DatabaseFactory.Create(context); using (IDataReader dr = db.ExecuteReader(CommandType.Text, sql)) { while (dr.Read()) { JobBase job = new JobBase(); ORMapping.DataReaderToObject(dr, job); result = job; } } if (result != null) { JobScheduleAdapter.Instance.FillJobSchdules(result); } } return(result); }
/// <summary>获取公司对应具体供应商采购索票额度集合 /// </summary> /// <param name="filialeId">公司ID</param> /// <param name="dateYear">年份</param> /// <param name="dateMonth">月份</param> /// <param name="pageIndex">当前页码 </param> /// <param name="pageSize"> 每页显示条数</param> /// <param name="totalCount">总记录数 </param> /// <returns>Return:供应商采购索票额度集合</returns> public IList <ProcurementTicketLimitInfo> GetProcurementTicketLimitDetailList(Guid filialeId, int dateYear, int dateMonth, int pageIndex, int pageSize, out int totalCount) { const string SQL = @" SELECT CC.CompanyId,CC.CompanyName, ISNULL(PTL.TakerTicketLimit,0) AS TakerTicketLimit,ISNULL(FilialeId,'00000000-0000-0000-0000-000000000000') AS FilialeId FROM lmShop_CompanyCussent AS CC LEFT JOIN ( SELECT CompanyId,TakerTicketLimit,FilialeId FROM [ProcurementTicketLimit] WHERE DateYear=@DateYear AND DateMonth=@DateMonth {0}{1} ) AS PTL ON CC.CompanyId=PTL.CompanyId WHERE CC.CompanyId IN(SELECT CompanyId FROM [CompanyBindingFiliale] {2} {1} GROUP BY CompanyId)"; var parms = new[] { new Parameter("@DateYear", dateYear), new Parameter("@DateMonth", dateMonth), new Parameter("@FilialeId", filialeId) }; using (var db = DatabaseFactory.Create()) { //var pageQuery = new Keede.DAL.Helper.Sql.PageQuery(pageIndex, pageSize, string.Format(SQL,filialeId!=Guid.Empty?" AND ":"", // filialeId != Guid.Empty ? " FilialeId=@FilialeId " : "", filialeId != Guid.Empty ? " WHERE " : ""), " CompanyName ASC"); var pageItem = db.Select <ProcurementTicketLimitInfo>(true, string.Format(SQL, filialeId != Guid.Empty ? " AND " : "", filialeId != Guid.Empty ? " FilialeId=@FilialeId " : "", filialeId != Guid.Empty ? " WHERE " : ""), parms); var procurementTicketLimitInfos = pageItem as ProcurementTicketLimitInfo[] ?? pageItem.ToArray(); totalCount = pageItem != null?procurementTicketLimitInfos.Count() : 0; return(procurementTicketLimitInfos.ToList()); } }
/// <summary> /// 根据TaskGuid得到UserAccomplishedTask /// </summary> /// <param name="strTaskGuid"></param> /// <returns></returns> public UserTaskCollection GetUserAccomplishedTasks(params string[] strTaskGuid) { UserTaskCollection utc = new UserTaskCollection(); if (strTaskGuid.Length > 0) { InSqlClauseBuilder builder = new InSqlClauseBuilder(); builder.AppendItem(strTaskGuid); string strSql = "SELECT * FROM WF.USER_ACCOMPLISHED_TASK WHERE (TASK_GUID " + builder.ToSqlStringWithInOperator(TSqlBuilder.Instance) + ")"; using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName())) { Database db = DatabaseFactory.Create(dbi); using (IDataReader dr = db.ExecuteReader(CommandType.Text, strSql)) { while (dr.Read()) { UserTask ut = new UserTask(); ORMapping.DataReaderToObject(dr, ut); utc.Add(ut); } } } } return(utc); }
/// <summary> /// 根据条件返回GoodsPriceChange表的所有数据 /// </summary> /// <param name="saleFilialeId">公司ID</param> /// <param name="salePlatformId">销售平台ID</param> /// <param name="goodsName">商品名称</param> /// <param name="goodsId">商品编号</param> /// <param name="type">0:销售价;1:加盟价;2:批发价</param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="total">数据总数</param> /// <returns></returns> public List <GoodsPriceChange> GetAllGoodsPriceChange(Guid?saleFilialeId, Guid?salePlatformId, string goodsName, Guid goodsId, int type, int pageIndex, int pageSize, out int total) { var sqlsb = new StringBuilder(SQL_SELECT + " where 1=1"); if (!string.IsNullOrEmpty(saleFilialeId.ToString()) && saleFilialeId != Guid.Empty) { sqlsb.AppendFormat(@" AND [SaleFilialeId]='{0}'", saleFilialeId); } if (!string.IsNullOrEmpty(salePlatformId.ToString()) && salePlatformId != Guid.Empty) { sqlsb.AppendFormat(@" AND [SalePlatformId]='{0}'", salePlatformId); } if (!string.IsNullOrEmpty(goodsName)) { sqlsb.AppendFormat(@" AND [GoodsName]='{0}'", goodsName); } if (goodsId != Guid.Empty) { sqlsb.AppendFormat(@" AND [GoodsId]='{0}'", goodsId); } if (type != -1) { sqlsb.AppendFormat(@" AND [Type]='{0}'", type); } using (var db = DatabaseFactory.Create()) { var list = new Keede.DAL.Helper.Sql.PageQuery(pageIndex, pageSize, sqlsb.ToString(), " Datetime DESC"); var pageItem = db.SelectByPage <GoodsPriceChange>(true, list); total = (int)pageItem.RecordCount; return(pageItem.Items.ToList()); } }
public void Test_Initialization() { var dbConfig = new DbConfig() { ConnectionString = string.Format("Data Source= " + "(DESCRIPTION =" + "(ADDRESS = (PROTOCOL = TCP)(HOST = {0} )(PORT = {1}))" + "(CONNECT_DATA = (SID = {2})));User Id={3};Password={4};", "localhost", "1521", "xe", "easylink", "1197344"), DatabaseType = DatabaseType.Oracle, SchemaName = "EASYLINK", AuditRecordType = typeof(AuditRecord) }; DatabaseFactory.Initialize(dbConfig); database = DatabaseFactory.Create(); Mapping.SetNextId <Employee>(NextIdOption.Sequence, "EMPLOYEE_ID_SEQ"); Mapping.SetNextId <Program>(NextIdOption.Sequence, "PROGRAM_ID_SEQ"); Mapping.SetNextId <EmployeeProgram>(NextIdOption.Sequence, "EMPLOYEE_PROGRAM_ID_SEQ"); Mapping.SetNextId <Address>(NextIdOption.Sequence, "ADDRESS_ID_SEQ"); Mapping.SetNextId <FinancialInfo>(NextIdOption.Sequence, "FINANCIAL_INFO_ID_SEQ"); Mapping.SetNextId <AdditionalInfo>(NextIdOption.Sequence, "ADDITIONAL_INFO_ID_SEQ"); Mapping.SetNextId <Lookup>(NextIdOption.Sequence, "LOOKUP_ID_SEQ"); Mapping.SetNextId <AuditRecord>(NextIdOption.Sequence, "AUDIT_ID_SEQ"); }
/// <summary> /// 删除sessionID相关的SignInInfo信息 /// </summary> /// <param name="sessionID">sessionID</param> /// <remarks> /// <code source="..\Framework\TestProjects\DeluxeWorks.Library.Passport.Test\DataObjectsTest.cs" region="SignInInfoPersistTest" lang="cs" title="删除SignInInfo对象" /> /// </remarks> public void DeleteRelativeSignInInfo(string sessionID) { ExceptionHelper.CheckStringIsNullOrEmpty(sessionID, "sessionID"); StringBuilder strB = new StringBuilder(256); strB.Append(TSqlBuilder.Instance.DBStatementBegin); strB.AppendFormat("DELETE PASSPORT_SIGNIN_INFO WHERE SIGNIN_ID = {0}" + TSqlBuilder.Instance.DBStatementSeperator + "DELETE PASSPORT_TICKET WHERE SIGNIN_ID = {0}", TSqlBuilder.Instance.CheckQuotationMark(sessionID, true)); strB.Append(TSqlBuilder.Instance.DBStatementEnd); using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required)) { using (DbContext context = DbContext.GetContext(DataAdapter.DBConnectionName)) { Database db = DatabaseFactory.Create(DataAdapter.DBConnectionName); db.ExecuteNonQuery(CommandType.Text, strB.ToString()); } scope.Complete(); } }
public override void DeleteOperation() { //1删除组织表 DataView organizations = new DataView(this.Context.OriginalData.Tables["ORGANIZATIONS"], string.Empty, "GUID", DataViewRowState.Deleted); Database db = DatabaseFactory.Create(this.Context.InitialParams.AccreditAdminConnectionName); foreach (DataRowView drv in organizations) { try { string organization_delete = string.Format("DELETE ORGANIZATIONS WHERE GUID = '{0}'", drv["GUID"].ToString()); int count = db.ExecuteNonQuery(CommandType.Text, organization_delete); if (count > 0) { this.DeleteCount++; this.Context.InitialParams.Log.Write(string.Format("ORGANIZATIONS表GUID 是{0}的记录删除成功,ALL_PATH_NAME是'{1}'", drv["GUID"].ToString(), drv["ALL_PATH_NAME"].ToString())); } } catch (Exception ex) { this.Context.InitialParams.Log.Write(string.Format("ORGANIZATIONS表GUID 是{0}的记录执行删除时出错,错误是{1}", drv["GUID"].ToString(), ex.Message)); } } }
/// <summary> /// 保存OpenIDBinding /// </summary> /// <param name="binding"></param> public void SaveOpenIDBinding(OpenIDBinding binding) { binding.NullCheck("binding"); string sql = ORMapping.GetInsertSql(binding, TSqlBuilder.Instance); using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required)) { using (DbContext context = DbContext.GetContext(DataAdapter.DBConnectionName)) { Database db = DatabaseFactory.Create(DataAdapter.DBConnectionName); try { db.ExecuteNonQuery(CommandType.Text, sql); } catch (SqlException ex) { if (ex.Number == 2627) { throw new OpenIDBindingException(Translator.Translate(Define.DefaultCategory, "OpenID已经绑定到某用户了,不能重复绑定")); } throw; } } scope.Complete(); } }
public void RegisterBot_ExistingBot_ShouldReturnDuplicatedIdError() { var databaseContext = DatabaseFactory.Create(); var timeProvider = TimeProviderFactory.Create(); var botService = new BotsService(databaseContext, timeProvider); databaseContext.Bots.Add(new BotModel { Id = "1000", Name = "Some other bot", CreateTime = DateTime.Now }); databaseContext.SaveChanges(); var bot = new RegisterBotDto { Id = "1000", Name = "Bot1" }; var result = botService.RegisterBot(bot); Assert.Equal(RegisterResult.DuplicatedIdError, result); Assert.Equal(1, databaseContext.Bots.Count()); }
/// <summary> /// /// </summary> /// <param name="openID"></param> /// <returns></returns> public OpenIDBinding GetBindingByOpenID(string openID) { ExceptionHelper.CheckStringIsNullOrEmpty(openID, "openID"); ORMappingItemCollection mappings = ORMapping.GetMappingInfo(typeof(OpenIDBinding)); string sql = string.Format("SELECT * FROM {0} WHERE OPEN_ID = {1}", mappings.TableName, TSqlBuilder.Instance.CheckUnicodeQuotationMark(openID)); DataTable table; using (DbContext context = DbContext.GetContext(DataAdapter.DBConnectionName)) { Database db = DatabaseFactory.Create(DataAdapter.DBConnectionName); table = db.ExecuteDataSet(CommandType.Text, sql).Tables[0]; } OpenIDBinding binding = null; if (table.Rows.Count > 0) { binding = new OpenIDBinding(); ORMapping.DataRowToObject(table.Rows[0], binding); } return(binding); }
public static string[] QueryGuidsByCondition(IConnectiveSqlClause condition) { QueryCondition qc = new QueryCondition(); qc.FromClause = "WF.PROCESS_INSTANCES (NOLOCK)"; qc.SelectFields = "INSTANCE_ID"; qc.OrderByClause = "CREATE_TIME DESC"; qc.WhereClause = condition.ToSqlString(TSqlBuilder.Instance); string sql = string.Format("SELECT {0} FROM {1} WHERE 1 = 1 {2} {3} ORDER BY {4}", qc.SelectFields, qc.FromClause, qc.WhereClause.IsNotEmpty() ? " AND " + qc.WhereClause : string.Empty, qc.GroupBy.IsNotEmpty() ? "GROUP BY " + qc.GroupBy : string.Empty, qc.OrderByClause); using (DbContext context = DbContext.GetContext(WorkflowSettings.GetConfig().ConnectionName)) { Database db = DatabaseFactory.Create(context); using (var dr = db.ExecuteReader(CommandType.Text, sql)) { List <string> guids = new List <string>(); while (dr.Read()) { guids.Add(dr.GetString(0)); } return(guids.ToArray()); } } }
static void Main(string[] args) { DatabaseFactory.AddProviderFactory(System.Data.SqlClient.SqlClientFactory.Instance, DatabaseType.SqlServer); Dictionary <string, GetHandler> cached = new Dictionary <string, GetHandler>(); //using (var db = DatabaseFactory.Create("ZDATA")) //{ // for (int i = 0; i < 1000000; i++) // { // db.CreateCommand("insert into ") // } //} var item = new Item { Id = 1, Name = "abc", Age = 10 }; using (var db = DatabaseFactory.Create("test")) { var commd = db.CreateCommand("select * from Item where id=@id", new { id = 1 }); } //create insert sql }