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