private static void SearchByComplexQuery() { var lq = new LinkQuery { LinkTypeId = "ProductItems", Direction = LinkDirection.OutBound, SourceEntityTypeId = "Product", TargetEntityTypeId = "Item" }; var AllProductsLinkedToItems = remoteManager.DataService.LinkSearch(lq, LoadLevel.DataAndLinks); var sq = new SystemQuery { Created = DateTime.Today.AddDays(-1), CreatedOperator = Operator.GreaterThan }; var complexQuery = new ComplexQuery { LinkQuery = lq, SystemQuery = sq }; var allProductsLinkedToItemsThatWasCreatedToday = remoteManager.DataService.Search(complexQuery, LoadLevel.DataAndLinks); }
public async Task <DocumentDbResult <Project> > GetProjectsAsync(ComplexQuery query) { if (query == null) { throw new ArgumentNullException(nameof(query)); } if (!query.Count && (query.Top == null || query.Top > Constants.MaxProjects)) { query.Top = Constants.MaxProjects; } var fin = _translator.Translate(query); if (query.Count) { var count = await _repoCounter.GetListAsync(fin).ConfigureAwait(false); return(new DocumentDbResult <Project>(count.Sum(x => x.Number), null)); } query.Count = true; var finc = _translator.Translate(query); var count2 = (await _repoCounter.GetListAsync(finc).ConfigureAwait(false)).Sum(x => x.Number); query.Count = false; var result = await GetListAsync(fin).ConfigureAwait(false); return(new DocumentDbResult <Project>(count2, result)); }
void IComplexQueryEntity.AdjustComplexQuery(ComplexQuery query) { query.Add(new Contact { ReturnBehavior = ReturnBehavior.OnlySpecified, ContactID = new IntReturn(), Email = new StringReturn() }); }
public string GetUserNameById(string id) { ComplexQuery cmquery = new ComplexQuery(); cmquery.AddNeedField("users", "USER_NAME"); cmquery.AddCriterion("USER_ID", id, CriteriaOperator.Equal); DataTable table = dataFactory.Query(cmquery); return(table.Rows[0][0].ToString().Trim()); }
public string GetReportNameBy(int id) { ComplexQuery cmquery = new ComplexQuery(); cmquery.AddNeedField("Report", "Name"); cmquery.AddCriterion("ID", id, CriteriaOperator.Equal); DataTable table = dataFactory.Query(cmquery); return(table.Rows[0][0].ToString().Trim()); }
private static void SearchByCriteria() { Criteria itemCriteria = new Criteria { FieldTypeId = "ItemNumber", Operator = Operator.BeginsWith, Value = ITEM_NUMBER }; ComplexQuery x = new ComplexQuery(); List <Entity> entities = remoteManager.DataService.Search(itemCriteria, LoadLevel.Shallow); }
/// <summary> /// 获得分厂ID和名字 /// </summary> /// <returns></returns> public string GetFactory() { ComplexQuery query = new ComplexQuery(); query.AddNeedField("Factory", "ID"); query.AddNeedField("Factory", "Name"); DataTable dt = dataFactory.Query(query); if (dt.Rows.Count == 0) { throw new Exception("查无数据"); } return(ComboboxJsonParser.DataTableToJson(dt, "ID", "Name")); }
public void DocumentFilterSample() { var query = new ComplexQuery( null, "id eq 'moeta(42)'", 1, null, null, "dev", false); var documentTranslator = new DocumentTranslator("test", Helpers.Enums.DocumentType.Data, "records", 3); var sqlQuery = documentTranslator.Translate(query); Assert.AreEqual("select top 30 c.identifier, c.robot from c where c.title = 'moeta' and c.entity = 'kotori/project' and c.instance = 'dev' ", sqlQuery); }
public void ProjectSelectSample() { var query = new ComplexQuery( "id, robot", "title eq 'moeta'", 30, null, null, "dev", false); var projectTranslator = new ProjectTranslator(); var sqlQuery = projectTranslator.Translate(query); Assert.AreEqual("select top 30 c.identifier, c.robot from c where c.title = 'moeta' and c.entity = 'kotori/project' and c.instance = 'dev' ", sqlQuery); }
public void ProjectSelectSample3() { var query = new ComplexQuery( "a,b,c", null, null, null, null, "dev", false); var projectTranslator = new ProjectTranslator(); var sqlQuery = projectTranslator.Translate(query); Assert.AreEqual("select c.a,c.b,c.c from c where c.entity = 'kotori/project' and c.instance = 'dev' ", sqlQuery); }
/// <summary> /// 获得视图中所有变量的Id值 /// </summary> /// <param name="viewName"></param> /// <returns></returns> private ArrayList GetParametorsId(string viewName) { ArrayList result = new ArrayList(); ComplexQuery cmpquery = new ComplexQuery(); cmpquery.AddNeedField("ContrastTable", "VariableName"); cmpquery.AddCriterion("ViewName", viewName, CriteriaOperator.Equal); DataTable data = dataFactory.Query(cmpquery); foreach (DataRow row in data.Rows) { result.Add(row["VariableName"].ToString().Trim()); } return(result); }
public void DocumentCountSample() { var query = new ComplexQuery( "id, englishName", "title eq 'moeta' and property/field ne 'val' or number ge 5 and (likes ne 3 or special lt 3)", 30, null, "_created asc, createdDateTime desc", "dev", true); var documentTranslator = new DocumentTranslator("testproject", Helpers.Enums.DocumentType.Content, "articles", null); var sqlQuery = documentTranslator.Translate(query); Assert.AreEqual("select count(1) from c where c.title = 'moeta' and c.property.field <> 'val' or c.number >= 5 and (c.likes <> 3 or c.special < 3) and c.entity = 'kotori/document' and c.instance = 'dev' and c.projectId = 'kotori://api/projects/testproject' and c.documentTypeId = 'kotori://api/projects/testproject/content/articles' ", sqlQuery); }
public void ProjectCountSample() { var query = new ComplexQuery( "id, englishName", "title eq 'moeta' and property/field ne 'val' or number ge 5 and (likes ne 3 or special lt 3)", 30, null, "_created asc, createdDateTime desc", "dev", true); var projectTranslator = new ProjectTranslator(); var sqlQuery = projectTranslator.Translate(query); Assert.AreEqual("select count(1) from c where c.title = 'moeta' and c.property.field <> 'val' or c.number >= 5 and (c.likes <> 3 or c.special < 3) and c.entity = 'kotori/project' and c.instance = 'dev' ", sqlQuery); }
public void ProjectSelectSampleWithTransformation() { var query = new ComplexQuery( "", "id eq 'yuri-yuri'", null, null, null, "dev", false); var projectTranslator = new ProjectTranslator(); var sqlQuery = projectTranslator.Translate(query); Assert.AreEqual("select * from c where c.identifier = 'kotori://api/projects/yuri-yuri' and c.entity = 'kotori/project' and c.instance = 'dev' ", sqlQuery); }
/// <summary> /// 获得视图中所有变量的Id值 /// </summary> /// <param name="viewName"></param> /// <returns></returns> public System.Collections.ArrayList GetVariableId(int productLineId, string viewName) { string connectionString = ConnectionStringFactory.GetConnectionStringByProductLineID(productLineId, DatabaseType.DCSSystemDatabase); ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); ArrayList result = new ArrayList(); ComplexQuery cmpquery = new ComplexQuery(); cmpquery.AddNeedField("ContrastTable", "VariableName"); cmpquery.AddCriterion("ViewName", viewName, CriteriaOperator.Equal); DataTable data = dataFactory.Query(cmpquery); foreach (DataRow row in data.Rows) { result.Add(row["VariableName"].ToString().Trim()); } return(result); }
public string GetEletricalRoom(int factoryId) { try { string connString = ConnectionStringFactory.GetConnectionStringByFactoryID(factoryId, DatabaseType.EnergyDatabase); ISqlServerDataFactory factory = new SqlServerDataFactory(connString); ComplexQuery query = new ComplexQuery(); query.AddNeedField("ElectricalRoom", "ID"); query.AddNeedField("ElectricalRoom", "Name"); DataTable dt = factory.Query(query); return(ComboboxJsonParser.DataTableToJson(dt, "ID", "Name")); } catch { return("[]"); } }
public async Task <Project> GetProjectAsync(string instance, string id) { if (instance == null) { throw new ArgumentNullException(nameof(instance)); } if (string.IsNullOrEmpty(id)) { return(null); } var query = new DynamicQuery("id eq @id", new { id }); var complex = new ComplexQuery(null, query.ToSqlQuery(), 1, null, null, instance); var fin = _translator.Translate(complex); var result = await GetFirstOrDefaultAsync(new DynamicQuery(fin)).ConfigureAwait(false); return(result); }
/// <summary> /// 获得实时数据的table表 /// </summary> /// <param name="dataSetInformations"></param> /// <returns></returns> private DataTable GetDataItemTable(IEnumerable <DataSetInformation> dataSetInformations) { //DataItem result = new DataItem(); ComplexQuery cmpquery = new ComplexQuery(); foreach (var item in dataSetInformations) { cmpquery.AddNeedField(item.TableName, item.FieldName, item.ViewId); } cmpquery.JoinCriterion = new JoinCriterion { DefaultJoinFieldName = "v_date", JoinType = JoinType.FULL_JOIN }; cmpquery.TopNumber = 1; //cmpquery.OrderByClause = new OrderByClause("realtime_line_data.v_date", true); DataTable table = dataFactory.Query(cmpquery); return(table); }
private DataItem GetDataItem(DataSetInformation dataSetInformation) { DataItem result = new DataItem(); ComplexQuery cmpquery = new ComplexQuery(); cmpquery.AddNeedField(dataSetInformation.TableName, dataSetInformation.FieldName, dataSetInformation.FieldName); cmpquery.TopNumber = 1; cmpquery.AddOrderByClause("v_date", true); DataTable table = dataFactory.Query(cmpquery); foreach (DataRow item in table.Rows) { result = new DataItem { ID = dataSetInformation.ViewId, Value = item[dataSetInformation.FieldName].ToString().Trim() }; } return(result); }
/// <summary> /// 返回最近两天的历史数据表 /// </summary> /// <param name="viewName"></param> /// <returns></returns> private DataTable GetHistoryDataTable(string viewName) { bool flag = false; DataTable result = new DataTable(); IList <NeedField> needFields = new List <NeedField>(); IEnumerable <DataSetInformation> dataSetInfor = GetDataSetInformation(viewName); foreach (var item in dataSetInfor) { if (flag == false) { needFields.Add(new NeedField { FieldName = "v_date", TableName = item.TableName, VariableName = "date" }); flag = true; } needFields.Add(new NeedField { FieldName = item.FieldName, TableName = item.TableName, VariableName = item.ViewId }); } JoinCriterion joinCriterion = new JoinCriterion { DefaultJoinFieldName = "v_date", JoinType = JoinType.FULL_JOIN }; ComplexQuery cmpquery = new ComplexQuery(needFields, joinCriterion); //cmpquery.AddCriterion("v_date", DateTime.Now.AddDays(1).Date, CriteriaOperator.LessThan); //cmpquery.AddCriterion("v_date", DateTime.Now.AddDays(-1).Date, CriteriaOperator.MoreThan); result = dataFactory.Query(cmpquery); return(result); }
/// <summary> /// 获得实时数据的table表 /// </summary> /// <param name="dataPathInfor"></param> /// <returns></returns> public DataTable GetDataItemTable(int productLineId, IEnumerable <DataPathInformation> dataPathInfor) { string connectionString = ConnectionStringFactory.NXJCConnectionString; ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString); ComplexQuery cmpquery = new ComplexQuery(); foreach (var item in dataPathInfor) { cmpquery.AddNeedField(item.TableName, item.FieldName, item.ViewId); } cmpquery.JoinCriterion = new JoinCriterion { DefaultJoinFieldName = "v_date", JoinType = JoinType.FULL_JOIN }; cmpquery.TopNumber = 1; //cmpquery.OrderByClause = new OrderByClause("realtime_line_data.v_date", true); DataTable table = dataFactory.Query(cmpquery); return(table); }
public static void TranslateIntoComplexQuery(ComplexQuery complexQuery, SqlCommand command) { string baseString = "SELECT "; StringBuilder result = new StringBuilder(); result.Append(baseString); if (complexQuery.IsDictinct == true) { result.Append("DISTINCT "); } if (complexQuery.TopNumber != 0) { result.Append(TranslateHelper.GetTopNumber(complexQuery.TopNumber)); } foreach (var item in complexQuery.NeedFields) { result.Append(TranslateHelper.GetFieldString(item)); } result.Remove(result.Length - 1, 1); result.Append(TranslateHelper.GetJoinConditionString(complexQuery.NeedFields, complexQuery.JoinCriterion)); if (complexQuery.Criterions.Count() > 0) { TranslateHelper.GetCriterionString(result, complexQuery.Criterions, command, complexQuery.SqlOperator); } if (complexQuery.OrderByClauses.Count > 0) { result.Append(TranslateHelper.GetStringFromOrderByClause(complexQuery.OrderByClauses)); } command.CommandText = result.ToString(); }
public static void TranslateIntoComplexQuery(ComplexQuery complexQuery, SqlCommand command) { string baseString = "SELECT "; StringBuilder result = new StringBuilder(); result.Append(baseString); if (complexQuery.IsDictinct == true) { result.Append("DISTINCT "); } if (complexQuery.TopNumber!= 0) { result.Append(TranslateHelper.GetTopNumber(complexQuery.TopNumber)); } foreach (var item in complexQuery.NeedFields) { result.Append(TranslateHelper.GetFieldString(item)); } result.Remove(result.Length - 1, 1); result.Append(TranslateHelper.GetJoinConditionString(complexQuery.NeedFields, complexQuery.JoinCriterion)); if (complexQuery.Criterions.Count() > 0) { TranslateHelper.GetCriterionString(result, complexQuery.Criterions, command, complexQuery.SqlOperator); } if (complexQuery.OrderByClauses.Count > 0) { result.Append(TranslateHelper.GetStringFromOrderByClause(complexQuery.OrderByClauses)); } command.CommandText = result.ToString(); }
public List <Entity> Search(ComplexQuery query, LoadLevel level) { throw new NotImplementedException(); }
/// <summary> /// Initialize properties. /// </summary> /// <param name="instance">Instance.</param> /// <param name="query">Query.</param> public void Init(string instance, ComplexQuery query) { Instance = instance; Query = query; }
public WorkAreaFolder UpdateSharedWorkAreaQuery(Guid id, ComplexQuery query) { throw new NotImplementedException(); }
static void Main(string[] args) { string conn = "Data Source=QH-20140814XCYI;Initial Catalog=Test;Integrated Security=True"; SqlServerDataFactory dataFactory = new SqlServerDataFactory(conn); DataTable dttest = new DataTable(); DataColumn dc = new DataColumn("d", typeof(string)); dttest.Columns.Add(dc); DataColumn dc1 = new DataColumn("c", typeof(int)); dttest.Columns.Add(dc1); DataRow dr = dttest.NewRow(); dr["c"] = 2; dr["d"] = "123ss"; dttest.Rows.Add(dr); DataRow dr1 = dttest.NewRow(); dr1["c"] = 3; dr1["d"] = "123ss"; dttest.Rows.Add(dr1); //dataFactory.Insert("B", dttest); string[] key = { "c" }; dataFactory.Update("B", dttest, key); Console.ReadKey(); //////////////////////////////////////delete Delete delete = new Delete("tableName"); delete.AddCriterions("a", "zhangxin", "1", CriteriaOperator.MoreThan); delete.AddCriterions("a", "zhangdi", 2, CriteriaOperator.LessThan); delete.AddCriterions("b", "2", CriteriaOperator.Equal); delete.AddSqlOperator(SqlOperator.OR); SqlCommand deleteCmd = new SqlCommand(); DeleteTranslator.TranslateIntoDelete(delete, deleteCmd); Console.WriteLine(deleteCmd.CommandText); Console.WriteLine(deleteCmd.Parameters.Count); //Console.ReadKey(); ////////////////////////////////////////////////// Test test = new Test { a = "1", b = "2", c = "3" }; Insert <Test> insert = new Insert <Test>("tableName", test); insert.AddExcludeField("a"); SqlCommand insertCmd = new SqlCommand(); SaveTranslator.TranslateIntoInsert <Test>(insert, insertCmd); Console.WriteLine(insertCmd.CommandText); Console.WriteLine(insertCmd.Parameters.Count); //Console.ReadKey(); //////////////////////////////////////////////////////////// Test test1 = new Test { a = "1", b = "2", c = "3" }; Update <Test> update = new Update <Test>("taleName", test1); update.AddCriterion("a", "a", "1", CriteriaOperator.Equal); update.AddCriterion("b", "b", "2", CriteriaOperator.Equal); update.AddExcludeField("c"); update.AddSqlOperator(SqlOperator.AND); SqlCommand updateCmd = new SqlCommand(); SaveTranslator.TranslateIntoUpdate <Test>(update, updateCmd); Console.WriteLine(updateCmd.CommandText); Console.WriteLine(updateCmd.Parameters.Count); //Console.ReadKey(); //////////////////////////////////////////////////////////// Query query = new Query("tableName"); query.AddCriterion("a.b", "1", CriteriaOperator.Equal); //query.AddCriterion("b", "b", "2", CriteriaOperator.Equal); query.AddCriterion("c", "____", CriteriaOperator.Like); query.AddOrderByClause(new OrderByClause("a", false)); query.AddOrderByClause(new OrderByClause("b", true)); //query.AddSqlOperator(SqlOperator.AND); SqlCommand queryCmd = new SqlCommand(); QueryTranslator.TranslateIntoSelect(query, queryCmd); Console.WriteLine(queryCmd.CommandText); Console.WriteLine(queryCmd.Parameters.Count); //Console.ReadKey(); ////////////////////////////////////////////////////////////// ComplexQuery cmquery = new ComplexQuery(); IList <NeedField> list = new List <NeedField>(); NeedField needField = new NeedField { TableName = "tableName1", FieldName = "a", VariableName = "" }; list.Add(needField); cmquery.NeedFields = list; cmquery.AddNeedField("tableName2", "b"); cmquery.AddNeedField("tableName3", "c", "test"); IDictionary <string, string> testjoin = new Dictionary <string, string>(); testjoin.Add("tableName1", "c1"); testjoin.Add("tableName2", "c2"); testjoin.Add("tableName3", "c3"); //cmquery.AddJoinCriterion("assss", JoinType.FULL_JOIN); cmquery.AddJoinCriterion(testjoin, JoinType.INNER_JOIN); //cmquery.IsDictinct = true; //cmquery.TopNumber = 9; cmquery.AddCriterion("a", "a", "1", CriteriaOperator.Equal); cmquery.AddCriterion("b", "b", "2", CriteriaOperator.Equal); cmquery.AddCriterion("c", "c", "3", CriteriaOperator.Like); List <OrderByClause> orders = new List <OrderByClause>(); orders.Add(new OrderByClause("a", true)); orders.Add(new OrderByClause("b", false)); cmquery.OrderByClauses = orders; SqlCommand cmqueryCmd = new SqlCommand(); ComplexQueryTranslator.TranslateIntoComplexQuery(cmquery, cmqueryCmd); Console.WriteLine(cmqueryCmd.CommandText); Console.WriteLine(cmqueryCmd.Parameters.Count); //Console.ReadKey(); /////////////////////////////////////////////////////////// ComplexQuery testquery = new ComplexQuery(); Console.WriteLine(testquery.TopNumber); Console.WriteLine(DateTime.Today); Console.WriteLine(DateTime.Now.AddDays(-1).Date); //Console.ReadKey(); /////////////////////////////////////////////////////// //Query testQuery = new Query("B"); //SqlServerDataFactory dataFactory = new SqlServerDataFactory("Data Source=QH-20140814XCYI;Initial Catalog=Test;Integrated Security=True"); //IEnumerable<BModle> result = dataFactory.Query<BModle>(testQuery); //foreach (var item in result) //{ // //BModle a = (BModle)item; // Console.WriteLine(item.d); // Console.WriteLine(item.c); //} //Console.WriteLine(result.Count()); //Console.ReadKey(); JoinCriterion joinCri = new JoinCriterion(); Console.WriteLine("++" + joinCri.DefaultJoinFieldName + "++"); Console.ReadKey(); Criterion testcri = new Criterion("name", "", 1, CriteriaOperator.Equal); Console.WriteLine(testcri.ParameterName); Console.ReadKey(); //SqlServerDataFactory dataFactory = new SqlServerDataFactory(""); //SqlParameter a = new SqlParameter(); //SqlParameter b = new SqlParameter("", typeof(int)); //dataFactory.Query("", a, b); //dataFactory.Query(""); //SqlParameter[] arry = { new SqlParameter(), new SqlParameter("", typeof(int)) }; //dataFactory.Query("", arry); }
public IEnumerable <Report> GetByComplexQuery(ComplexQuery complexQuery) { throw new NotImplementedException(); }