public static void DeleteMenuByDirId(int dir) { //SysTakeawayMenu.Columns var cmd = new QueryCommand("UPDATE dbo.Sys_Takeaway_Menu SET IsDeleted = 1 WHERE DirID=@DirID;"); cmd.AddParameter("@DirID", dir, DbType.Int32); DataService.ExecuteQuery(cmd); }
public static object Execute(string sql) { var provider = ProviderFactory.GetProvider( "DCStreetHockey" ); var queryCommand = new QueryCommand( sql, provider ); return _db.Provider.ExecuteScalar( queryCommand ); }
public void QueryCommand_Should_Use_CommandType_Text_By_Default() { var provider = ProviderFactory.GetProvider("Northwind"); QueryCommand q = new QueryCommand("SELECT * FROM Orders", provider); Assert.Equal(CommandType.Text, q.CommandType); }
public FromQuery(SqlQuery oQuery, String sName) : base() { _Cmd = oQuery.GetCommand(); Name = sName; Provider = _Cmd.Provider; Columns = oQuery.SelectColumns.Select((X) => (IColumn)new DatabaseColumn(X.Name, this) { DataType = X.DataType, DefaultSetting = X.DefaultSetting, ForeignKeyTo = X.ForeignKeyTo, IsForeignKey = X.IsForeignKey, FriendlyName = X.FriendlyName }).ToList(); foreach (Aggregate oAggregate in oQuery.Aggregates) { bool hasAlais = !String.IsNullOrEmpty(oAggregate.Alias); Columns.Add(new DatabaseColumn(hasAlais ? oAggregate.Alias : oAggregate.ColumnName, this) { DataType = oAggregate.GetDataType() }); } }
public List<OTAStatistics> GetOTAStatistics(string type,string orderby="ASC") { var result = new List<OTAStatistics>(); var commandText = string.Format(@" select aa.d2 datetime,bb.firstCount,aa.totalCount from ( select stat_date d2,sum(total_count) totalCount from YLTEST.STAT_OTA_BATCH_USER_{0} group by stat_date ) aa left join ( select trunc(create_date_time) d1, count(*) firstCount from YLTEST.STAT_OTA_FIRST_{0} group by trunc(create_date_time)) bb on to_char(bb.d1,'yyyymmdd') = aa.d2 order by aa.d2 {1} ",type,orderby); var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG); var queryCommand = new QueryCommand(commandText, provider); var ds = provider.ExecuteDataSet(queryCommand); if (ds != null && ds.Tables.Count == 1) { var dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { OTAStatistics item = new OTAStatistics(); item.Date = DateTime.ParseExact(row["DATETIME"].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture); item.FirstCount = string.IsNullOrEmpty(row["FIRSTCOUNT"].ToString()) ? 0 : int.Parse(row["FIRSTCOUNT"].ToString()); item.TotalCount = string.IsNullOrEmpty(row["TOTALCOUNT"].ToString()) ? 0 : int.Parse(row["TOTALCOUNT"].ToString()); result.Add(item); } } } return result; }
public List<OTARecorder> GetOTAStatisticsDetails(string dateTime,string type) { var result = new List<OTARecorder>(); var commandText = string.Format(@" select b.batch,a.newcount,b.total_count from( select batch,count({1}) newcount from YLTEST.STAT_OTA_FIRST_{1} a where create_date_time between to_timestamp(to_date('{0}','yyyy-mm-dd')) and to_timestamp(to_date('{0}','yyyy-mm-dd'))+.99999 group by batch) a right join ( select batch,total_count from YLTEST.STAT_OTA_BATCH_USER_{1} where stat_date between to_char(to_timestamp(to_date('{0}','yyyy-mm-dd')),'yyyymmdd') and to_char(to_timestamp(to_date('{0}','yyyy-mm-dd'))+.99999,'yyyymmdd')) b on a.batch = b.batch order by batch", dateTime,type); var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG); var queryCommand = new QueryCommand(commandText, provider); var ds = provider.ExecuteDataSet(queryCommand); if (ds != null && ds.Tables.Count == 1) { var dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { var item = new OTARecorder(); item.Batch = row["BATCH"].ToString(); item.NewCount = string.IsNullOrEmpty(row["NEWCOUNT"].ToString()) ? 0 : int.Parse(row["NEWCOUNT"].ToString()); item.TotalCount = string.IsNullOrEmpty(row["TOTAL_COUNT"].ToString()) ? 0 : int.Parse(row["TOTAL_COUNT"].ToString()); result.Add(item); } } } return result; }
public List<string> GetIMEIOnCondition(DateTime startDate, DateTime endDate, string batch, List<string> actionIds) { #region Data var StartDate = startDate.ToString("yyyy-MM-dd"); var EndDate = endDate.ToString("yyyy-MM-dd"); string ActionIds = ""; foreach (var item in actionIds) { ActionIds = ActionIds + "," + item; } #endregion StringBuilder comm = new StringBuilder(); comm.AppendFormat(@" select imei from yltest.imei_uniq_55"); var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG); var queryCommand = new QueryCommand(comm.ToString(), provider); var imeis = new List<string>(); var ds = provider.ExecuteDataSet(queryCommand); if (ds != null && ds.Tables.Count == 1) { var dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { imeis.Add(row["IMEI"].ToString()); } } } return imeis; }
public Common.ServicesResult GetMansionArea(System.Web.HttpContext context) { var phone = context.Request["phone"]; QueryCommand cmd = new QueryCommand(CompanyMansionAreaQuery); cmd.AddParameter("@Phone", phone); return new Common.ServicesResult { data = DataService.GetDataTable(cmd) }; }
public static bool ChangeMenuOutOfStokcStatus(object menuId, bool isOutOfStock) { var cmd = new QueryCommand("UPDATE dbo.Sys_Takeaway_Menu SET IsOutOfStock = @IsOutOfStock,OutOfStockDate = GETDATE() WHERE ID=@ID AND IsOutOfStock <> @IsOutOfStock;"); cmd.AddParameter("@IsOutOfStock", isOutOfStock, DbType.Boolean); cmd.AddParameter("@ID", menuId, DbType.Int32); DataService.ExecuteQuery(cmd); return true; }
public static bool ChangeMenuPrice(object menuId, decimal newPrice) { var cmd = new QueryCommand("UPDATE dbo.Sys_Takeaway_Menu SET Price = @Price WHERE ID=@ID AND Price <> @Price;"); cmd.AddParameter("@Price", newPrice, DbType.Decimal); cmd.AddParameter("@ID", menuId, DbType.Int32); DataService.ExecuteQuery(cmd); return true; }
public static void RecycleAdminOrder( ) { if (AppContext.Context.CurrentRole == (int)EleoooRoleDefine.OrderMeal) { QueryCommand cmd = new QueryCommand("Update Orders Set OrderOper=null where OrderOper=@OrderOper and OrderStatus in(2,3,4);"); cmd.AddParameter("@OrderOper", AppContext.Context.User.Id); DataService.ExecuteQuery(cmd); } }
public void QueryCommand_Should_Respect_CommandType_When_Creating_DbCommands() { var provider = ProviderFactory.GetProvider("Northwind"); QueryCommand q = new QueryCommand("SELECT * FROM Orders", provider); q.CommandType = CommandType.StoredProcedure; var dbCommand = q.ToDbCommand(); Assert.Equal(CommandType.StoredProcedure, dbCommand.CommandType); }
public void GitHub_Issue149_QueryCommand_Should_Respect_Timeout_When_Creating_DbCommands() { var provider = ProviderFactory.GetProvider("Northwind"); QueryCommand q = new QueryCommand("SELECT * FROM Orders", provider); q.CommandTimeout = Timeout; var dbCommand = q.ToDbCommand(); Assert.Equal(Timeout, dbCommand.CommandTimeout); }
protected string GetCompanyPhone( ) { string vSql = "select top 1 CompanyPhone from sys_company where id=" + Meal.OrderSellerID.ToString( ); QueryCommand cmd = new QueryCommand(vSql); object v = DataService.ExecuteScalar(cmd); if (Utilities.IsNull(v)) return null; else return v.ToString( ); }
protected string GetMemberPhone( ) { string vSql = "select top 1 memberphoneNumber from sys_member where id=" + Meal.OrderMemberID.ToString( ); QueryCommand cmd = new QueryCommand(vSql); object v= DataService.ExecuteScalar(cmd); if (Utilities.IsNull(v)) return null; else return v.ToString( ); }
public Common.ServicesResult Query(System.Web.HttpContext context) { string result = string.Empty; string tel = context.Request.Params["CompanyTel"]; if (string.IsNullOrEmpty(tel)) { result = "[]"; goto label_end; } QueryCommand cmd = new QueryCommand("select distinct [Sys_Area_Mansion].[id],[Sys_Area_Mansion].[name] from [Sys_Area_Mansion] inner join [Sys_Company] on [Sys_Company].[AreaDepth] like [Sys_Area_Mansion].[AreaDepth]+'%' where [Sys_Company].[CompanyTel] = @companyTel;"); cmd.AddParameter("@companyTel", tel, DbType.String); DataTable dt = DataService.GetDataSet(cmd).Tables[0]; result = JsonConvert.SerializeObject(dt); label_end: return new Common.ServicesResult { data = result }; }
// Methods public static bool BackupDataBase(string databasename, string strPath,out string message) { message = string.Empty; try { QueryCommand cmd = new QueryCommand(string.Format(SQL_BACKUP, databasename, strPath),DAL.DB.Provider.Name); DataService.ExecuteQuery(cmd); return true; } catch (Exception ex) { Logging.Log("DBBackupHelper->BackupDataBase", ex); message = ex.Message; return false; } }
public Dictionary<string, double> AppStatDetailForOneDay(string appNo, string type, string dateTime, string table, string comeFrom) { Dictionary<string, double> result = new Dictionary<string, double>(); // var commandText = string.Format(@"select {0},sum(count) count from YLTEST.{1} where app_no='{2}' and statistic_date='{3}' //group by {0} ", type,table,appNo, dateTime); if ((type.EqualsOrdinalIgnoreCase("FIRMWARE_MODE") || type.EqualsOrdinalIgnoreCase("MANUFACTURER") || type.EqualsOrdinalIgnoreCase("COME_FROM")) && (table.EqualsOrdinalIgnoreCase("STAT_APP_PV") || table.EqualsOrdinalIgnoreCase("STAT_APP_DOWN") || table.EqualsOrdinalIgnoreCase("STAT_APP_DOWN_OK") )) { StringBuilder commandText = new StringBuilder(); commandText.AppendFormat(@"select {0},sum(count) count from YLTEST.{1} where app_no=:appNo and statistic_date=:statDate ", type, table); if (!string.IsNullOrEmpty(comeFrom) && !comeFrom.EqualsOrdinalIgnoreCase("none")) { commandText.AppendFormat(" and COME_FROM = :from1 "); } commandText.AppendFormat(" group by {0} order by count", type); var provider = ProviderFactory.GetProvider(ConnectionStrings.Key_ORACLE_LOG); var queryCommand = new QueryCommand(commandText.ToString(), provider); queryCommand.AddParameter("appNo", appNo, DbType.String); queryCommand.AddParameter("statDate", dateTime.ToInt32(), DbType.Int32); if (!string.IsNullOrEmpty(comeFrom) && !comeFrom.EqualsOrdinalIgnoreCase("none")) { queryCommand.AddParameter("from1", comeFrom, DbType.String); } var ds = provider.ExecuteDataSet(queryCommand); if (ds != null && ds.Tables.Count == 1) { var dt = ds.Tables[0]; if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { result.Add(row[type.ToUpper()].ToString(), string.IsNullOrEmpty(row["COUNT"].ToString()) ? 0 : int.Parse(row["COUNT"].ToString())); } } } } return result; }
public override IEnumerable <int> ExecuteBatch(QueryCommand query, IEnumerable <object[]> paramSets, int batchSize, bool stream) { IEnumerable <int> enumerable2; base.StartUsingConnection(); try { IEnumerable <int> source = this.ExecuteBatch(query, paramSets, batchSize); if (!(stream && !base.ActionOpenedConnection)) { return(source.ToList <int>()); } enumerable2 = new EnumerateOnce <int>(source); } finally { base.StopUsingConnection(); } return(enumerable2); }
public void JavascriptProjectionOfMapOfArrayWithNonexistingFieldWrappedWithObjectShouldReturnArrayOfObjectsWithNulls() { using (var store = GetDocumentStore()) { var executer = store.GetRequestExecutor(); using (executer.ContextPool.AllocateOperationContext(out var context)) { var docBlit = context.ReadObject(new DynamicJsonValue { ["InnerArray"] = new[] { new DynamicJsonValue(), new DynamicJsonValue() }, [Raven.Client.Constants.Documents.Metadata.Key] = new DynamicJsonValue { [Raven.Client.Constants.Documents.Metadata.Collection] = "docs" } }, "newDoc"); store.Commands().Execute(new PutDocumentCommand("docs/1", null, docBlit)); using (var session = store.OpenSession()) { QueryCommand queryCommand = new QueryCommand(session as InMemoryDocumentSessionOperations, new IndexQuery() { Query = "from docs as d select { inner:d.InnerArray.map(x=>({f:x.f}))}" }); store.Commands().Execute(queryCommand); var resBlittable = queryCommand.Result.Results[0] as BlittableJsonReaderObject; var inner = resBlittable["inner"] as BlittableJsonReaderArray; Assert.Equal(2, inner.Length); for (var i = 0; i < 2; i++) { var innerObject = inner[i] as BlittableJsonReaderObject; Assert.Equal(null, innerObject["f"]); } } } } }
public void RegisterContinousQueryForSilverCustomers() { try { // Precondition: Cache is already connected // Query for required operation string query = "SELECT $VALUE$ FROM Models.Customer WHERE OrdersCount >= 7 AND OrdersCount < 10"; var queryCommand = new QueryCommand(query); // Create Continuous Query var cQuery = new ContinuousQuery(queryCommand); // Item add notification // EventDataFilter.None returns the cache keys added cQuery.RegisterNotification(new QueryDataNotificationCallback(QueryItemCallBackForSilverCustomers), EventType.ItemAdded, EventDataFilter.None); // Register continuousQuery on server _cache.MessagingService.RegisterCQ(cQuery); Console.WriteLine("Continous query registered"); } catch (OperationFailedException ex) { if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT) { // Make sure that the query format is correct } else { // Exception can occur due to: // Connection Failures // Operation Timeout // Operation performed during state transfer } } catch (Exception ex) { // Any generic exception like ArgumentException, ArgumentNullException } }
public void VerifyIndexScore_Map_JsProjection_CollectionQuery() { using (var store = GetDocumentStore()) { using (var session = store.OpenSession()) { session.Store(new User { Name = "Jerry", LastName = "Garcia" }); session.Store(new User { Name = "Bob", LastName = "Weir" }); session.SaveChanges(); } using (var commands = store.Commands()) { var command = new QueryCommand(commands.Session, new IndexQuery { Query = "from Users as u select { FullName : u.Name + ' ' + u.LastName }" }); commands.RequestExecutor.Execute(command, commands.Context); var results = new DynamicArray(command.Result.Results); Assert.Equal(2, results.Count()); foreach (dynamic r in results) { var indexScoreAsString = r[Constants.Documents.Metadata.Key][Constants.Documents.Metadata.IndexScore]; Assert.NotNull(indexScoreAsString); var indexScore = float.Parse(indexScoreAsString.ToString(), CultureInfo.InvariantCulture); Assert.Equal(0, indexScore); } } } }
public void QueryResourceByOtherResource(string resName = null) { var res = SelectResourceFromMemory(false, resName); var affNum = _rnd.Next(2) + 1; var query = new QueryCommand(res.ResourceKey, affNum, res.ResourceName); ApplyCommand(query); lock (NodeMemory) foreach (var id in query.Ids) { var key = new ResourceDescription { ResourceKey = id, ResourceName = query.TargetName }; if (!NodeMemory.ContainsKey(key)) { NodeMemory.Add(key, null); } } }
protected virtual void SetParameterValues(QueryCommand query, DbCommand command, object[] paramValues) { if (query.Parameters.Count > 0 && command.Parameters.Count == 0) { for (int i = 0, n = query.Parameters.Count; i < n; i++) { AddParameter(command, query.Parameters[i], paramValues != null ? paramValues[i] : null); } } else if (paramValues != null) { for (int i = 0, n = command.Parameters.Count; i < n; i++) { DbParameter p = command.Parameters[i]; if (p.Direction == ParameterDirection.Input || p.Direction == ParameterDirection.InputOutput) { p.Value = paramValues[i] ?? DBNull.Value; } } } }
public static void SaveProductMap(int varCategoryID, System.Web.UI.WebControls.ListItemCollection itemList) { QueryCommandCollection coll = new SubSonic.QueryCommandCollection(); //delete out the existing QueryCommand cmdDel = new QueryCommand("DELETE FROM Product_Category_Map WHERE CategoryID=@CategoryID", Category.Schema.Provider.Name); cmdDel.AddParameter("@CategoryID", varCategoryID); //add this in coll.Add(cmdDel); DataService.ExecuteTransaction(coll); foreach (System.Web.UI.WebControls.ListItem l in itemList) { if (l.Selected) { ProductCategoryMap varProductCategoryMap = new ProductCategoryMap(); varProductCategoryMap.SetColumnValue("CategoryID", varCategoryID); varProductCategoryMap.SetColumnValue("ProductID", l.Value); varProductCategoryMap.Save(); } } }
public static void SaveTerritoryMap(int varEmployeeID, System.Web.UI.WebControls.ListItemCollection itemList) { QueryCommandCollection coll = new SubSonic.QueryCommandCollection(); //delete out the existing QueryCommand cmdDel = new QueryCommand("DELETE FROM EmployeeTerritories WHERE EmployeeID=@EmployeeID", Employee.Schema.Provider.Name); cmdDel.AddParameter("@EmployeeID", varEmployeeID); //add this in coll.Add(cmdDel); DataService.ExecuteTransaction(coll); foreach (System.Web.UI.WebControls.ListItem l in itemList) { if (l.Selected) { EmployeeTerritory varEmployeeTerritory = new EmployeeTerritory(); varEmployeeTerritory.SetColumnValue("EmployeeID", varEmployeeID); varEmployeeTerritory.SetColumnValue("TerritoryID", l.Value); varEmployeeTerritory.Save(); } } }
public async Task GetMessageById() { //lets create customer first to ensure message created await WithCustomer(client, async customer => { var queryCommand = new QueryCommand <Message>(); queryCommand.SetLimit(1); await AssertEventuallyAsync(async() => { var returnedSet = await client.ExecuteAsync(queryCommand); Assert.Single(returnedSet.Results); var messageId = returnedSet.Results[0].Id; var retrievedMessage = await client .ExecuteAsync(new GetByIdCommand <Message>(messageId)); Assert.NotNull(retrievedMessage); } ); }); }
/// <summary> /// Register continuous query on cache /// </summary> /// <returns> returns instance of registered continuous query instance </returns> private static ContinuousQuery RegisterQuery(QueryCommand queryCommand) { ContinuousQuery continuousQuery = new ContinuousQuery(queryCommand); // Add query notifications continuousQuery.RegisterNotification(new QueryDataNotificationCallback(QueryDataModified), EventType.ItemAdded, EventDataFilter.None); // Item update notification with DataWithMetadata as event data filter to recieve modifed item on updation continuousQuery.RegisterNotification(new QueryDataNotificationCallback(QueryDataModified), EventType.ItemUpdated, EventDataFilter.DataWithMetadata); // Item delete notification continuousQuery.RegisterNotification(new QueryDataNotificationCallback(QueryDataModified), EventType.ItemRemoved, EventDataFilter.None); // Register continuous query on server _cache.MessagingService.RegisterCQ(continuousQuery); // Print output on console Console.WriteLine("\nContinuous Query is registered."); return(continuousQuery); }
public DataSet ExecuteDataSet(QueryCommand qry) { WriteToLog(() => string.Format("ExecuteDataSet(QueryCommand): {0}.", qry.CommandSql)); DbCommand cmd = Factory.CreateCommand(); cmd.CommandText = qry.CommandSql; cmd.CommandType = qry.CommandType; DataSet ds = new DataSet(); using (AutomaticConnectionScope scope = new AutomaticConnectionScope(this)) { cmd.Connection = scope.Connection; AddParams(cmd, qry); DbDataAdapter da = Factory.CreateDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); return(ds); } }
public static void SaveProductMap(int varOrderID, System.Web.UI.WebControls.ListItemCollection itemList) { QueryCommandCollection coll = new SubSonic.QueryCommandCollection(); //delete out the existing QueryCommand cmdDel = new QueryCommand("DELETE FROM Order Details WHERE OrderID=@OrderID", Order.Schema.Provider.Name); cmdDel.AddParameter("@OrderID", varOrderID); //add this in coll.Add(cmdDel); DataService.ExecuteTransaction(coll); foreach (System.Web.UI.WebControls.ListItem l in itemList) { if (l.Selected) { OrderDetail varOrderDetail = new OrderDetail(); varOrderDetail.SetColumnValue("OrderID", varOrderID); varOrderDetail.SetColumnValue("ProductID", l.Value); varOrderDetail.Save(); } } }
void GUI() { if (isDebug && GUILayout.Button("Print")) { OutPutDebugRecord(); } if (isDebug && GUILayout.Button("Query")) { List <EntityBase> list = GetEntityList(); for (int i = 0; i < list.Count; i++) { QueryCommand qc = new QueryCommand(); qc.frame = m_world.FrameCount - 10; qc.id = list[i].ID; ProtocolAnalysisService.SendCommand(qc); } } }
public static void SaveCustomerMap(string varCustomerTypeID, System.Web.UI.WebControls.ListItemCollection itemList) { QueryCommandCollection coll = new SubSonic.QueryCommandCollection(); //delete out the existing QueryCommand cmdDel = new QueryCommand("DELETE FROM CustomerCustomerDemo WHERE CustomerTypeID=@CustomerTypeID", CustomerDemographic.Schema.Provider.Name); cmdDel.AddParameter("@CustomerTypeID", varCustomerTypeID); //add this in coll.Add(cmdDel); DataService.ExecuteTransaction(coll); foreach (System.Web.UI.WebControls.ListItem l in itemList) { if (l.Selected) { CustomerCustomerDemo varCustomerCustomerDemo = new CustomerCustomerDemo(); varCustomerCustomerDemo.SetColumnValue("CustomerTypeID", varCustomerTypeID); varCustomerCustomerDemo.SetColumnValue("CustomerID", l.Value); varCustomerCustomerDemo.Save(); } } }
public void Test_Query_No_Single_Where() { //arrange string table = "vtubers"; string columns = "*"; var query = new QueryCommand <MockDataObject>(table, columns, new WhereStatement() { Equality = Equality.EqualTo, Target = "en_name", Value = "haaaachamaaaa", UseQuotes = true }); var expected = "SELECT * FROM vtubers WHERE en_name = 'haaaachamaaaa'"; //act //assert Assert.AreEqual(expected, query.Statement); }
private async Task <CommandResponse> SelectQuery(QueryCommand qc) { if (qc.Columns.Keys.First().ToLower() == "database()") { var info = new ColumnInfo("DATABASE()", "", typeof(string)); var dict = new Dictionary <ColumnInfo, object[]> { [info] = new object[] { _currentDB } }; return(new CommandResponse(CommandResponseType.Query, dict)); } var results = new Dictionary <ColumnInfo, object[]>(); foreach (var provider in Providers.Values) { // Just overwrite the results so the timings go through for all results = provider.SelectRowsAsync(qc.Table, qc.Columns); } return(new CommandResponse(CommandResponseType.Query, results)); }
public static int GetNextVersionId(int postid, int currentVersion) { if (postid <= 0) { return(1); } else { QueryCommand command = new QueryCommand("Select Max(v.Version) FROM graffiti_VersionStore as v where v.Name = " + DataService.Provider.SqlVariable("Name")); command.Parameters.Add(VersionStore.FindParameter("Name")).Value = "Post:" + postid.ToString(); object obj = DataService.ExecuteScalar(command); if (obj == null || obj is System.DBNull) { return(2); } else { return(Math.Max(((int)obj), currentVersion) + 1); } } }
public static ReportData MostPopularPosts() { // top 5 DataProvider dp = DataService.Provider; QueryCommand cmd = new QueryCommand(@" SELECT Title, Id, IdCount FROM ( SELECT p.Title, p.Id, " + dp.SqlCountFunction("p.Id") + @" as IdCount from graffiti_Post_Statistics AS ps left outer join graffiti_Posts AS p on p.Id = ps.PostId where p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @" group by p.Title, p.Id) as dv order by IdCount desc "); return(GetPostDictionary(cmd, 5)); }
public void QueryLuceneNotOperator() { using (var store = GetDocumentStore()) { store.Initialize(); new UsersByName().Execute(store); using (var session = store.OpenSession()) { session.Store(new User { Name = "First" }, "users/1"); session.Store(new User { Name = "Second" }, "users/2"); session.SaveChanges(); } WaitForIndexing(store); var requestExecuter = store.GetRequestExecutor(); using (var session = store.OpenSession()) using (requestExecuter.ContextPool.AllocateOperationContext(out JsonOperationContext context)) { var command = new QueryCommand((InMemoryDocumentSessionOperations)session, new IndexQuery { Query = "FROM INDEX 'Users/ByName' WHERE exists(Name) AND NOT Name = 'Second'" }); requestExecuter.Execute(command, context); var query = command.Result; Assert.Equal(query.TotalResults, 1); } } }
public DbDataReader ExecuteReader(QueryCommand qry) { AutomaticConnectionScope scope = new AutomaticConnectionScope(this); if (Log != null) { Log.WriteLine(qry.CommandSql); } #if DEBUG //Console.Error.WriteLine("ExecuteReader(QueryCommand):\r\n{0}", qry.CommandSql); #endif DbCommand cmd = scope.Connection.CreateCommand(); cmd.Connection = scope.Connection; //CreateConnection(); cmd.CommandText = qry.CommandSql; cmd.CommandType = qry.CommandType; AddParams(cmd, qry); //this may look completely lame //but there is a bug in here... DbDataReader rdr; //Thanks jcoenen! try { // if it is a shared connection, we shouldn't be telling the reader to close it when it is done rdr = scope.IsUsingSharedConnection ? cmd.ExecuteReader() : cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception) { // AutoConnectionScope will figure out what to do with the connection scope.Dispose(); //rethrow retaining stack trace. throw; } return(rdr); }
public void QueryAndSortCategory() { IClient commerceToolsClient = this.categoryFixture.GetService <IClient>(); Category parentCategory = this.categoryFixture.CreateCategory(); this.categoryFixture.CategoriesToDelete.Add(parentCategory); for (int i = 0; i < 3; i++) { Category category = this.categoryFixture.CreateCategory(this.categoryFixture.GetCategoryDraftWithParent(parentCategory)); this.categoryFixture.CategoriesToDelete.Add(category); } string id = parentCategory.Id; QueryCommand <Category> queryCommand = new QueryCommand <Category>(); queryCommand.Sort(c => c.Name["en"]); queryCommand.Where(c => c.Parent.Id == id); PagedQueryResult <Category> returnedSet = commerceToolsClient.ExecuteAsync(queryCommand).Result; var sortedList = returnedSet.Results.OrderBy(c => c.Name["en"]); Assert.True(sortedList.SequenceEqual(returnedSet.Results)); }
//public List<IGraffitiUser> GetUsers(string role) //{ // throw new NotImplementedException(); //} public string[] GetUsersInRole(string roleName) { QueryCommand command = new QueryCommand( "SELECT u.Name FROM graffiti_Users AS u INNER JOIN graffiti_UserRoles AS ur on u.Id = ur.UserId WHERE ur.RoleName = " + DataService.Provider.SqlVariable("RoleName")); command.Parameters.Add(UserRole.FindParameter("RoleName")).Value = roleName; var userNames = new List <string>(); using (IDataReader reader = DataService.ExecuteReader(command)) { while (reader.Read()) { userNames.Add(reader["Name"] as string); } reader.Close(); } return(userNames.ToArray()); }
/// <summary> /// This method queries cached keys /// </summary> /// <param name="customer"> Instance of Query Command to query cached items</param> private static void QueryKeysInCache(QueryCommand queryCommand) { // Getting keys via query //ICacheReader cacheReader = _cache.ExecuteReaderCQ(continuousQuery, false); ICacheReader cacheReader = _cache.SearchService.ExecuteReader(queryCommand, false); if (!cacheReader.IsClosed) { // Print output on console Console.WriteLine("\nFollowing keys are fetched with Continuous Query."); while (cacheReader.Read()) { string key = cacheReader.GetValue <string>(0); Console.WriteLine("Key: " + key); // A second call to cache to fetch the customer //Customer cachedCustomer = (Customer)_cache.Get(key); } } }
public object ExecuteScalar(QueryCommand qry) { WriteToLog(() => string.Format("ExecuteScalar(QueryCommand): {0}.", qry.CommandSql)); object result; using (AutomaticConnectionScope automaticConnectionScope = new AutomaticConnectionScope(this)) { DbCommand cmd = Factory.CreateCommand(); cmd.Connection = automaticConnectionScope.Connection; if (automaticConnectionScope.IsUsingSharedConnection && CurrentSharedTransaction != null) { cmd.Transaction = CurrentSharedTransaction; } cmd.CommandType = qry.CommandType; cmd.CommandText = qry.CommandSql; AddParams(cmd, qry); result = cmd.ExecuteScalar(); } return(result); }
public override IEnumerable <int> ExecuteBatch(QueryCommand query, IEnumerable <object[]> paramSets, int batchSize, bool stream) { this.StartUsingConnection(); try { var result = this.ExecuteBatch(query, paramSets, batchSize); if (stream == false || this.ActionOpenedConnection) { return(result.ToList()); } else { return(new EnumerateOnce <int>(result)); } } finally { this.StopUsingConnection(); } }
//作为委托传入到DrawPolygon操作中 public void SelectByPolygon(IFeatureLayer drawedLayer) { ILegendItem selectedItem = (GIS.FrameWork.Application.App.Legend as GIS.Common.Dialogs.Legend).SelectedLegendMenuItem; if (selectedItem != null && selectedItem is IFeatureLayer) { IFeatureLayer baseLayer = selectedItem as IFeatureLayer; IFeatureLayer selectLayer = drawedLayer; List <int> resultIndices = null; if (selectLayer != null) { QueryCommand query = new QueryCommand(); resultIndices = query.SpatialFilter(baseLayer.DataSet, selectLayer.DataSet); } if (resultIndices != null) { baseLayer.UnSelectAll(); baseLayer.Select(resultIndices); _map.Refresh(); } } }
public void QueryAndOffsetCategory() { IClient commerceToolsClient = this.categoryFixture.GetService <IClient>(); Category parentCategory = this.categoryFixture.CreateCategory(); this.categoryFixture.CategoriesToDelete.Add(parentCategory); for (int i = 0; i < 3; i++) { Category category = this.categoryFixture.CreateCategory(this.categoryFixture.GetCategoryDraftWithParent(parentCategory)); this.categoryFixture.CategoriesToDelete.Add(category); } string id = parentCategory.Id; QueryCommand <Category> queryCommand = new QueryCommand <Category>(); queryCommand.Where(c => c.Parent.Id == id); queryCommand.Offset = 2; PagedQueryResult <Category> returnedSet = commerceToolsClient.ExecuteAsync(queryCommand).Result; Assert.Single(returnedSet.Results); Assert.Equal(3, returnedSet.Total); }
/// <summary> /// Check if state exists by key then return it, else create it /// </summary> /// <param name="stateKey"></param> /// <param name="stateType"></param> /// <param name="initial"></param> /// <param name="transitionTo"></param> /// <returns></returns> public State CreateStateIfNotExists(string stateKey, StateType stateType = StateType.ProductState, bool initial = true, IReference <State> transitionTo = null) { State state = null; IClient commerceToolsClient = this.GetService <IClient>(); //check if state exists by key var queryCommand = new QueryCommand <State>(); queryCommand.Where(s => s.Key == stateKey); var returnedSet = commerceToolsClient.ExecuteAsync(queryCommand).Result; if (returnedSet.Results.Count == 1) { state = returnedSet.Results[0]; } else { state = this.CreateState(this.GetStateDraft(stateKey, stateType, initial, transitionTo)); } return(state); }
public async Task QueryBalance() { var backendService = Substitute.For <IBackendService>(); var minetokenService = Substitute.For <IMinetokenService>(); var matatakiService = Substitute.For <IMatatakiService>(); backendService.GetUserByTelegramIdAsync(1).Returns(new UserInfo() { Id = 1, Name = "User", WalletAddress = "0x1919", }); backendService.GetTokenAsync("INM").Returns(new TokenInfo() { Id = 1919, Name = "银票", ContractAddress = "0x114514", }); minetokenService.GetBalanceAsync("0x114514", "0x1919").Returns(11.4514m); matatakiService.GetTokenPageUrl(Arg.Any <int>()).Returns(info => "https://matataki/token/" + info[0]); var command = new QueryCommand(backendService, minetokenService, Substitute.For <IUserService>(), matatakiService); var response = command.QueryBalance(new Message() { From = new User() { Id = 1 } }, "INM").GetAsyncEnumerator(); Assert.True(await response.MoveNextAsync()); Assert.Equal("查询中……", response.Current.Content); Assert.True(await response.MoveNextAsync()); Assert.Equal("11.4514 [INM](https://matataki/token/1919)", response.Current.Content); Assert.False(await response.MoveNextAsync()); }
/// <summary> /// クエリコマンドの応答文字列を作成する /// </summary> /// <param name="query"></param> /// <param name="albums"></param> /// <returns></returns> /// <remarks> /// ヒット数1件の場合の処理を作るべきだが、 /// そうすると後続のReadコマンドにDiscID(GN-IDじゃない)が渡ってくるため処理が面倒 /// </remarks> public static string CreateQueryResponse(QueryCommand query, IList<Album> albums) { if (albums == null || albums.Count == 0) { return string.Format("202 No match for disc ID {0}.", query.DiscId); } else { StringBuilder result = new StringBuilder(); result.AppendLine("211 close matches found"); foreach (Album album in albums) { result.AppendLine("Misc " + album.GN_ID + " " + album.ARTIST + " / " + album.TITLE); } result.AppendLine("."); return result.ToString(); } }
public DbDataReader ExecuteReader(QueryCommand qry, out string connectionString) { AutomaticConnectionScope scope = new AutomaticConnectionScope(this); WriteToLog(() => string.Format("ExecuteReader(QueryCommand):\r\n{0}", qry.CommandSql)); // Added for supporting PetaPoco integration for optimized object hydration connectionString = scope.Connection.ConnectionString; DbCommand cmd = scope.Connection.CreateCommand(); cmd.Connection = scope.Connection; //CreateConnection(); cmd.CommandText = qry.CommandSql; cmd.CommandType = qry.CommandType; AddParams(cmd, qry); //this may look completely lame //but there is a bug in here... DbDataReader rdr; //Thanks jcoenen! try { // if it is a shared connection, we shouldn't be telling the reader to close it when it is done rdr = scope.IsUsingSharedConnection ? cmd.ExecuteReader() : cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception) { // AutoConnectionScope will figure out what to do with the connection scope.Dispose(); //rethrow retaining stack trace. throw; } return(rdr); }
private static List<SysNavigation> GetSysNavigation(int roleId, int subSys, MenuRegion region) { string vSql = string.Format(QuerySql, roleId, subSys, GetNavigationRegionFilter(region)); QueryCommand cmd = new QueryCommand(vSql); //AppContext.ActPage.LogMessage(vSql); return DataService.ExecuteListTypedResult<SysNavigation>(cmd); }
public static void UpdateOrderStatus(object orderId, int nStatus) { QueryCommand cmd = new QueryCommand(string.Format("UPDATE Orders SET OrderStatus = {0},OrderUpdateOn=GetDate() WHERE ID = {1};", nStatus, orderId)); DataService.ExecuteQuery(cmd); }
public static void UpdateOrderOper(object orderId, int orderOper) { if (orderOper > 0) { QueryCommand cmd = new QueryCommand(string.Format("UPDATE Orders SET OrderOper={0} WHERE ID = {1};", orderOper, orderId)); DataService.ExecuteQuery(cmd); } }
public static DateTime? GetUserLatestOrderDate(int userID, int companyID) { var cmd = new QueryCommand("Select top 1 OrderDate from Orders where OrderStatus <> @OrderStatus and OrderSellerID = @OrderSellerID and OrderMemberID = @OrderMemberID order by id desc"); cmd.AddParameter("@OrderStatus", (int)OrderStatus.Canceled, DbType.Int32); cmd.AddParameter("@OrderSellerID", companyID, DbType.Int32); cmd.AddParameter("@OrderMemberID", userID, DbType.Int32); var val = DataService.ExecuteScalar(cmd); if (Utilities.IsNull(val)) return null; else return Convert.ToDateTime(val); }
public static DateTime GetOrderUpdateOn(int orderId) { QueryCommand cmd = new QueryCommand("select orderupdateon from orders where id=@id"); cmd.AddParameter("@id", orderId, DbType.Int32); return Convert.ToDateTime(DataService.ExecuteScalar(cmd)); }
public static int GetOrderNum( ) { QueryCommand cmd = new QueryCommand("Select max(OrderNum) + 1 from Orders where OrderDate > dbo.GetToDay();"); var val = DataService.ExecuteScalar(cmd); if (val == DBNull.Value) return 1; else return Convert.ToInt32(val); }
public static Dictionary<object, OrderDetailItem> GetOrderItemMenus(int orderId) { Dictionary<object, OrderDetailItem> result = new Dictionary<object, OrderDetailItem>( ); var cmd = new QueryCommand(ItemDetailMenusCmd); cmd.AddParameter("@OrderId", orderId, DbType.Int32); using (var dr = DataService.GetReader(cmd)) { //decimal orderSum = 0, allSum = 0; bool isFirst = true; OrderDetailItem item; while (dr.Read( )) { var id = Utilities.ToInt(dr[SysTakeawayMenu.Columns.Id]); if (!result.TryGetValue(id, out item)) { item = new OrderDetailItem { OrderSum = isFirst ? Utilities.ToDecimal(dr["OrderSum"]) : 0, DirName = dr[SysTakeawayDirectory.Columns.DirName], MenuName = dr[SysTakeawayMenu.Columns.Name], MenuId = id, OrderId = orderId, OrderQty = Utilities.ToInt(dr[OrdersDetail.Columns.OrderQty]), OrderPrice = Utilities.ToDecimal(dr[SysTakeawayMenu.Columns.Price]), IsCompanyItem = true, IsOutOfStock = Utilities.ToBool(dr[SysTakeawayMenu.Columns.IsOutOfStock]) }; result.Add(id, item); isFirst = false; } else item.OrderQty = item.OrderQty + Utilities.ToInt(dr[OrdersDetail.Columns.OrderQty]); } } return result; }
public static int GetOrderCount(int companyID) { var cmd = new QueryCommand("Select count(*) from Orders where OrderStatus <> @OrderStatus and OrderSellerID = @OrderSellerID"); cmd.AddParameter("@OrderStatus", (int)OrderStatus.Canceled, DbType.Int32); cmd.AddParameter("@OrderSellerID", companyID, DbType.Int32); return Utilities.ToInt(DataService.ExecuteScalar(cmd)); }
public static void ClearOrderMenuDetail(int orderId) { string vSql = string.Format("Delete Orders_Detail Where OrderId = {0};", orderId); QueryCommand cmd = new QueryCommand(vSql); DataService.ExecuteQuery(cmd); }
public static string GetDataBase2005(out string message) { try { message = string.Empty; QueryCommand cmd = new QueryCommand(SQL_GETDATABASE2005, DataService.Provider.Name); return Utilities.ToHTML(DataService.ExecuteScalar(cmd)); } catch (Exception ex) { Logging.Log("DBBackupHelper->GetDataBase2005", ex); message = ex.Message; return string.Empty; } }
public static bool RestoreDataBase(string databasename, string databasefile,out string message) { try { message = string.Empty; QueryCommand cmdProcess = new QueryCommand(string.Format(SQL_GETPROCESS, databasename), DataService.Provider.Name); IDataReader reader = DataService.GetReader(cmdProcess); try { while (reader.Read( )) { QueryCommand cmdKill = new QueryCommand(string.Format(SQL_KILLPROCESS, reader[0]), DataService.Provider.Name); DataService.ExecuteQuery(cmdKill); } } finally { if (!reader.IsClosed) reader.Close( ); } QueryCommand cmdRestore = new QueryCommand(string.Format(SQL_RESTOREDATABASE,databasename,databasefile),DataService.Provider.Name); DataService.ExecuteQuery(cmdRestore); return true; } catch (Exception ex) { Logging.Log("DBBackupHelper->RestoreDataBase", ex); message = ex.Message; return false; } }