private void Expression() { var users = new TableDefinition { Name = "Users", Columns = new ColumnDefinition[] { new ColumnDefinition("Id", FieldType.Int), new ColumnDefinition("Username", FieldType.String), new ColumnDefinition("Password", FieldType.String), } }; // Select password from useres where id == 2 var select = new SqlSelectQuery(); select.From = new SqlTableSource(users, "u"); select.Fields = new SqlSelectField[] { new SqlSelectField(new SqlColumnExpression(select.From.FieldByIndex(2)), null) }; select.Where = new SqlMathExpression( FieldType.Bool, new SqlColumnExpression(select.From.FieldByIndex(0)), FieldMathOperator.Equal, new SqlConstantExpression(FieldType.Int, 2) ); var driver = new SQLLiteDatabaseDriver(); string sql = driver.GenerateSelect(select); Console.WriteLine(sql); }
public void TestParsingSelectQueryWithThreeJoin() { var secondJoin = string.Format("Select e,f,g from [Data2 as t1 Join (Select * FROM Data3) as t2 on t1.he=t2.hy]"); string sentences = string.Format("Select a,b,c from [Data as t1 Join ({0}) as t2 on t1.ha=t2.ho]", secondJoin); SqlSelectQuery query = new SqlSelectQuery(sentences); Assert.IsTrue(query.Attributes.Trim() == "a,b,c"); Assert.IsTrue(query.TableName.Trim() == "Data"); Assert.IsTrue(query.ConditionClause.Trim() == ""); Assert.IsTrue(query.Strategy.Equals(EvaluationStrategy.Default)); Assert.IsTrue(query.JoinOnAttributes.Trim() == "t1.ha=t2.ho"); Assert.IsTrue(query.HasSubquery == true); var subquery = query.SubQuery; Assert.IsTrue(subquery.HasSubquery == true); Assert.IsTrue(subquery.TableName.Trim() == "Data2"); Assert.IsTrue(subquery.Attributes == "e,f,g"); Assert.IsTrue(subquery.JoinOnAttributes.Trim() == "t1.he=t2.hy"); var subsubquery = subquery.SubQuery; Assert.IsTrue(subsubquery.HasSubquery == false); Assert.IsTrue(subsubquery.TableName.Trim() == "Data3"); Assert.IsTrue(subsubquery.Attributes == "*"); }
public string submitQuerySQL(string sql, out DataTable answerSet) { SqlQuery rawQuery = new SqlQuery(sql); QueryType qType = rawQuery.ProcessType(); answerSet = null; switch (qType) { case QueryType.INSERT: var query = new SqlInsertQuery(sql); query.ProcessAndPopulateEachField(); var iHandler = new InsertQueryHandler(query, underlineDatabase); answerSet = iHandler.HandleInsertQuery(); break; case QueryType.SELECT: var squery = new SqlSelectQuery(sql); var sHandler = new SelectQueryHandler(squery, underlineDatabase); answerSet = sHandler.HandleSelectSqlQuery(); break; case QueryType.CREATE: var cquery = new SqlCreateTableQuery(sql); var handler = new CreateTableHandler(cquery, underlineDatabase); answerSet = handler.HandleCreateTableQuery(); break; default: break; } return("end of submitSQL function"); }
/// <summary> /// Default is Extensional method, while monte carlo is sampling using frequency of event occur /// </summary> /// <param name="attributes"> must specify what they are, wildcard like * not supported !</param> /// <returns></returns> private DataTable ComputeJointResultUsingNaiveStrategy(string attributes, SqlSelectQuery query, bool intermediate) { int attributeSize = PreparePossibleStatesTable(query.TableName); CreatePossibleWorldsTable(query.TableName, attributeSize); PreparePossibleWorldsTable(query.TableName); PreparePossibleWorldsAggregatedTable(query.TableName); return(NaiveStrategy(attributes, query.ConditionClause, query.TableName, intermediate)); }
public IEnumerable <T> Select <T>() where T : class { var sql = new SqlSelectQuery <T>(); sql = sql.Select((o) => o); var strSql = sql.ToString(); return(null); }
public IEnumerable <T> Select <T>(Expression <Func <T, object> > columns, Expression <Func <T, bool> > where = null) where T : class { var sql = new SqlSelectQuery <T>(); sql = sql.Select(columns) .Where(where); var strSql = (sql as ISqlQuery).ToString(); return(null); }
public void TestParsingSelectQueryWithWhereClause() { string sentences = "Select a,b,c from Data where (a=25 and b=15)"; SqlSelectQuery query = new SqlSelectQuery(sentences); Assert.IsTrue(query.Attributes.Trim() == "a,b,c"); Assert.IsTrue(query.TableName.Trim() == "Data"); Assert.IsTrue(query.ConditionClause.Trim() == "a=25 and b=15"); Assert.IsTrue(query.Strategy.Equals(EvaluationStrategy.Default)); }
public void TestParsingSelectQueryWithOnlyStrategy() { string sentences = "Select a,b,c from Data Evaluate using (monte Carlo)"; SqlSelectQuery query = new SqlSelectQuery(sentences); Assert.IsTrue(query.Attributes.Trim() == "a,b,c"); Assert.IsTrue(query.TableName.Trim() == "Data"); Assert.IsTrue(query.ConditionClause.Trim() == ""); Assert.IsTrue(query.Strategy.Equals(EvaluationStrategy.MonteCarlo)); }
private SqlSelectQuery <Member> MembersQuery(bool searchAll) { SqlSelectQuery <Member> query = Context.Select <Member>(); if (searchAll) { return(query); } return(query .Where(x => x.Activated).EqualTo(true) .Where(x => x.Disabled).EqualTo(false) .WhereAny <MemberSubscription, SubscriptionType>(x => x.Type, new [] { SubscriptionType.Trial, SubscriptionType.Full, SubscriptionType.Partial })); }
public IEnumerable <T> Select <T>(string columns, string from, string where = null, string groupBy = null, string having = null, string orderBy = null, int pageIndex = 1, int pageSize = 40) where T : class { int rowsOffet = (pageIndex - 1) * pageSize; var sql = new SqlSelectQuery <string>(); sql = sql.Select(columns, from) .Where(where) .GroupBy(groupBy) .Having(having) .OrderBy(orderBy) .Skip(rowsOffet) .Take(pageSize); var strSql = (sql as ISqlQuery).ToString(); return(null); }
public IEnumerable <T> Select <T>(Expression <Func <T, object> > columns, Expression <Func <T, bool> > where = null, Expression <Func <T, object> > groupBy = null, Expression <Func <T, object> > having = null, Expression <Func <T, object> > orderBy = null, int pageIndex = 1, int pageSize = 40) where T : class { int rowsOffet = (pageIndex - 1) * pageSize; var sql = new SqlSelectQuery <T>(); sql = sql.Select(columns) .Where(where) .GroupBy(groupBy) .Having(having) .OrderBy(orderBy) .Skip(rowsOffet) .Take(pageSize); var strSql = (sql as ISqlQuery).ToString(); return(null); }
public void TestParsingSelectQueryWithJoin() { string sentences = "Select a,b,c from [Data as t1 Join (SELECT * FROM Data2) as t2 on t1.ha=t2.ho]"; SqlSelectQuery query = new SqlSelectQuery(sentences); Assert.IsTrue(query.Attributes.Trim() == "a,b,c"); Assert.IsTrue(query.TableName.Trim() == "Data"); Assert.IsTrue(query.ConditionClause.Trim() == ""); Assert.IsTrue(query.Strategy.Equals(EvaluationStrategy.Default)); var subquery = query.SubQuery; Assert.IsTrue(query.JoinOnAttributes.Trim() == "t1.ha=t2.ho"); Assert.IsTrue(query.HasSubquery); Assert.IsTrue(subquery.HasSubquery == false); Assert.IsTrue(subquery.TableName.Trim() == "Data2"); }
public T Select <T>(Expression <Func <T, object> > columns, object id) where T : class { var entityDef = GetEntityDefintion(typeof(T)); var pkAttributeName = entityDef.Attributes.FirstOrDefault(o => o.IsPrimaryKey == true)?.Name; if (pkAttributeName == null) { pkAttributeName = entityDef.Attributes.First().Name; } var sql = new SqlSelectQuery <T>(); sql = sql.Select(columns) .Where($"{pkAttributeName} == '{id.ToString()}'"); var strSql = (sql as ISqlQuery).ToString(); return(null); }
private void GenereateSelectFields(SqlGenerator sql, SqlSelectQuery query) { if (query.Fields == null) { // Select all fields from GenerateCommaSep(sql, query.From.Columns, true, (f, _) => { GenerateField(sql, new SqlColumnExpression(f.Type, f.Name, query.From)); }); return; } GenerateCommaSep(sql, query.Fields, true, (field, _) => { GenerateField(sql, field.Expression); if (!String.IsNullOrEmpty(field.Alias)) { sql.Add($" AS {field.Alias}"); } }); }
public string GenerateSelect(SqlSelectQuery query) { SqlGenerator sql = new SqlGenerator(); // If there is only 1 const selection if (query.From is SqlConstantSource constSource) { GenerateSource(sql, constSource); return(sql.Generate()); } sql.Add("SELECT"); sql.NewLineIndent(); GenereateSelectFields(sql, query); sql.NewLineUnIndent(); sql.Add("FROM"); sql.NewLineIndent(); GenerateSource(sql, query.From); sql.NewLineUnIndent(); if (query.Where != null) { sql.Add("WHERE"); sql.NewLineIndent(); GenerateField(sql, query.Where); sql.NewLineUnIndent(); } if (query.GroupBy?.Length > 0) { sql.Add("GROUP BY"); GenerateCommaSep(sql, query.GroupBy, false, (f, _) => GenerateField(sql, f)); } if (query.OrderBy?.Length > 0) { sql.Add("ORDER BY"); GenerateCommaSep(sql, query.OrderBy, false, (f, _) => { GenerateField(sql, f.By); sql.Add(f.Ascending ? "ASC" : "DESC"); }); } return(sql.Generate()); }
// s => public QuerySourceType(SqlSelectQuery source, Type type, SourceType sourceType) { this.Source = source; this.Type = type; this.SourceType = sourceType; }
public SelectQueryHandler(SqlSelectQuery squery, IStandardDatabase underlineDatabase) { _query = squery; this.underlineDatabase = underlineDatabase; }
private DataTable ExecuteMonteCarloSampling(string samplingResultTable, string samplingTargetTable, int samplingRuns, SqlSelectQuery query) { //:Todo not used Random random = new Random(); var sql = String.Format("select count(*) from {0}", samplingTargetTable); var result = underlineDatabase.ExecuteSqlWithResult(sql); if (result.Rows.Count != 1) { throw new Exception(""); } var noOfWorlds = (int)result.Rows[0][0]; DataTable allSample = new DataTable(); for (int i = 1; i <= samplingRuns; i++) { var worldNoSelected = random.Next(1, noOfWorlds); var selectStringWorld = string.Format("SELECT * FROM {0} WHERE worldNo = {1}", samplingTargetTable, worldNoSelected); var aSample = underlineDatabase.ExecuteSqlWithResult(selectStringWorld); if (i == 1) { allSample = aSample.Clone(); } allSample = addOneTableToAnother(allSample, aSample, random); } DataTable frenquencyResult = computeFrequencyResult(allSample, samplingResultTable, query.Attributes, samplingRuns, query); return(frenquencyResult); }
/// <summary> /// save allSample table back to database in order to be able to use group by keyword. /// the temporary table called samplingResultTable is created for this purposes /// </summary> /// <param name="allSample"></param> /// <param name="samplingResultTable"></param> /// <param name="query"></param> /// <returns></returns> private DataTable computeFrequencyResult(DataTable allSample, string samplingResultTable, string selectedFields, int noOfSamplingRuns, SqlSelectQuery query) { WriteTableIntoDatabase(samplingResultTable, allSample); var groupingSql = string.Format("SELECT {0},(COUNT(*)/{2}) as p FROM {1} GROUP BY {0}", selectedFields, samplingResultTable, noOfSamplingRuns); return(underlineDatabase.ExecuteSqlWithResult(groupingSql)); }
public static void GetParameterValues_NoConditions_ReturnsEmptyCollection() { SqlContext context = CreateMockContext(); SqlQuery <TestEntity> query = new SqlSelectQuery <TestEntity>(context); (SqlColumn, string, object)[] parameterValues = query.GetParameterValues(context).ToArray();