/// <summary> /// Gets the current rows from the table. There are no triggers in access so i had to do it with the count method. /// </summary> /// <param name="table">The table to get the current rows from.</param> /// <returns>Returns the number of rows in the given table</returns> public override int GetCurrentRowsFromTable(Table table) { try { SQLQueryBuilder sqb = new SQLQueryBuilder(); string columnName = table.Columns.First().Key; sqb.Select().AddValue("Count(" + columnName + ")").From().AddValue(table.TableName); List <List <object> > result = ReadQuery(sqb.ToString(), new List <KeyValuePair <int, Type> >() { new KeyValuePair <int, Type>(0, typeof(int)) }); if (result == null) { return(0); } else { return((int)result[0][0]); } } catch (Exception e) { throw e; } }
private Seal() { SQLQueryBuilder queryBuilder = new SQLQueryBuilder(); queryBuffer = new List <IDictionary <string, object> >(); }
public JsonResult GetSupplierItemsList(int ProjectID) { string strProjectID;//= string.IsNullOrEmpty(collection["ProjectID"]) ? collection["ProjectID"] : "0"; //if (collection["ProjectID"] == "") // strProjectID = "0"; //else strProjectID = collection["ProjectID"]; //int projectID = Convert.ToInt32(strProjectID); int projectID = ProjectID > 0 ? ProjectID : 0; DBPOProfile poProfile = new DBPOProfile(); SQLQueryBuilder queryBuilder = new SQLQueryBuilder(); queryBuilder.QueryParameters.Add(new SQLQueryParameter("ProjectID", projectID)); var SupplierID = poProfile.GetSupplierDDLItem(queryBuilder); List <SelectListItem> item = (from a in SupplierID select new SelectListItem() { Text = a.SupplierOrgSN, Value = a.SupplierID.ToString() }).Distinct().ToList(); item.Insert(0, new SelectListItem() { Text = "Select All", Value = "0" }); return(Json(item)); }
public JsonResult GetPODetailItem(int POID, DataSourceRequest request) { KoyoSMS.WCF.Common.Model.PageBase pageCondition = new KoyoSMS.WCF.Common.Model.PageBase(); int page = request.Page; int pageSize = request.PageSize; int iPOID = POID > 0 ? POID : 0; SQLQueryBuilder queryBuilder = new SQLQueryBuilder(); DBPOProfile poProfile = new DBPOProfile(); if (iPOID > 0) { queryBuilder.QueryParameters.Add(new SQLQueryParameter("POID", iPOID)); } pageCondition.PageIndex = page - 1; pageCondition.PageSize = pageSize > 0 ? pageSize : 5; poCollection.PODetailViewItems = poProfile.GetPODetailViews(queryBuilder, pageCondition); int total = pageCondition.TotalRecord; var jsonData = new { total = total, data = poCollection.PODetailViewItems }; return(Json(jsonData, JsonRequestBehavior.AllowGet)); }
/// <summary> /// Generates a querie list to create tables. /// <para/> /// SQL query Example: <para/> /// CREATE TABLE table1 (id INTEGER PRIMARY KEY, firstColumn TEXT NOT NULL, secondColumn REAL NOT NULL) /// </summary> /// <returns>Returns a list of queries to create the local tables.</returns> private List <string> CreateTableQueries(Dictionary <string, Table> tables) { List <string> tableQueries = new List <string>(); foreach (KeyValuePair <string, Table> table in tables) { SQLQueryBuilder sqb = new SQLQueryBuilder(); List <string> paramList = new List <string>(); int iter = 0; foreach (KeyValuePair <string, Type> column in table.Value.Columns) { sqb.AddValue(column.Key); if (column.Value == typeof(int)) { sqb.TypeInteger(); } else if (column.Value == typeof(string)) { sqb.TypeText(); } else if (column.Value == typeof(double)) { sqb.TypeReal(); } else if (column.Value == typeof(float)) { sqb.TypeReal(); } else if (column.Value == typeof(DateTime)) { sqb.TypeText(); } else { throw new NotSupportedException(column.Value.Name + " Datatype not supported"); } if (iter.Equals(0)) { sqb.ParamPrimaryKey(); } else { sqb.ParamNot().Null(); } paramList.Add(sqb.Flush()); iter++; } string values = sqb.Brackets_Multiple(paramList, false).Flush(); sqb.Create().Table().IfNotExists().AddValue(table.Value.TableName).AddValue(values); tableQueries.Add(sqb.ToString()); tableQueries.Add(CreateTriggerTable(table.Value.TableName)); tableQueries.Add(InsertStartValueTriggerTable(table.Value.TableName)); tableQueries.Add(CreateCounterAddTriger(table.Value.TableName)); tableQueries.Add(CreateCounterSubTriger(table.Value.TableName)); } return(tableQueries); }
public JsonResult GetProjectItemsList(int MasterID) { //string strMasterID;// = collection["MasterID"];//== "" ? collection["MasterID"] : "0"; //if (collection["MasterID"] == "") // strMasterID = "0"; //else strMasterID = collection["MasterID"]; //int MasterID = Convert.ToInt32(strMasterID); int masterID = MasterID > 0 ? MasterID : 0; DBPOProfile poProfile = new DBPOProfile(); SQLQueryBuilder queryBuilder = new SQLQueryBuilder(); queryBuilder.QueryParameters.Add(new SQLQueryParameter("MasterID", masterID)); var ProjectID = poProfile.GetProjectDDLItem(queryBuilder); List <SelectListItem> item = (from a in ProjectID select new SelectListItem() { Text = a.ProjectName, Value = a.ProjectID.ToString() }).Distinct().ToList(); item.Insert(0, new SelectListItem() { Text = "Select All", Value = "0", Selected = true }); return(Json(item)); }
/// <summary> /// Gets the current rows from the table. Since this is accomplished with a trigger table its just a small querie. /// </summary> /// <param name="table">The table to get the current rows from.</param> /// <returns>Returns the number of rows in the given table</returns> public override int GetCurrentRowsFromTable(Table table) { try { SQLQueryBuilder sqb = new SQLQueryBuilder(); sqb.Select().AddValue("rowCount").From().AddValue(table.TableName + "_count").Where().AddValue("id").Equal().AddValue("1"); List <List <object> > result = ReadQuery(sqb.ToString(), new List <KeyValuePair <int, Type> >() { new KeyValuePair <int, Type>(0, typeof(int)) }); if (result == null) { return(0); } else { return((int)result[0][0]); } } catch (Exception) { throw; } }
/// <summary> /// Creates the trigger table where the row count is automatically update through a custom insert and delete trigger. /// <para/> /// SQL query Example: <para/> /// create table if not exists tableName_count (id integer primary key, number int); /// </summary> /// <param name="tableName">The name of the table.</param> /// <returns>The finished sql string.</returns> private string CreateTriggerTable(string tableName) { SQLQueryBuilder sqb = new SQLQueryBuilder(); string param = sqb.Brackets(sqb.AddValue("id").TypeInteger().ParamPrimaryKey().Comma().AddValue("rowCount").TypeInteger().Flush()).Flush(); sqb.Create().Table().IfNotExists().AddValue(tableName + "_count").AddValue(param); return(sqb.ToString()); }
public List <UserDepartment_View> getUserList(SQLQueryBuilder queryBuilder) { PageBase pageCondition = new PageBase(); IQueryable <UserDepartment_View> query = (from a in db.UserDepartment_View select a); return(queryBuilder.BindQueryToContext(ref query, ref pageCondition)); }
public List <Department> getDepartList(SQLQueryBuilder queryBuilder) { PageBase pageCondition = new PageBase(); IQueryable <Department> query = (from a in db.Department select a); return(queryBuilder.BindQueryToContext(ref query, ref pageCondition)); }
public List <OrgMaster> getMasterList(SQLQueryBuilder queryBuilder) { PageBase pageCondition = new PageBase(); IQueryable <OrgMaster> query = (from a in db.OrgMaster select a); return(queryBuilder.BindQueryToContext(ref query, ref pageCondition)); }
/// <summary> /// Inserts the first and only row of this table. Bascially its set's the rowCOunt to 0. /// <para/> /// SQL query Example: <para/> /// insert into table1Count (rowCount) values (0); /// </summary> /// <param name="tableName">The name of the table.</param> /// <returns>The finished sql string.</returns> private string InsertStartValueTriggerTable(string tableName) { SQLQueryBuilder sqb = new SQLQueryBuilder(); string param = sqb.AddValue("id").Comma().AddValue("rowCount").Flush(); sqb.Insert().Or().Ignore().Into().AddValue(tableName + "_count").Brackets(param).Values().Brackets("1, 0"); return(sqb.ToString()); }
public List <PODetail_View> GetPODetailViews(SQLQueryBuilder queryBuilder, PageBase pageCondition) { IQueryable <PODetail_View> query = (from a in db.PODetail_View orderby a.PODetailID select a); return(queryBuilder.BindQueryToContext(ref query, ref pageCondition)); }
public List <POEntity_View> GetPOViews(SQLQueryBuilder queryBuiler, PageBase pagecondition) { IQueryable <POEntity_View> query = (from a in db.POEntity_View orderby a.POID select a); return(queryBuiler.BindQueryToContext <POEntity_View>(ref query, ref pagecondition)); }
public List <ProjectOrg_View> GetProjectDDLItem(SQLQueryBuilder queryBuilder) { PageBase pageCondtion = new PageBase(); IQueryable <ProjectOrg_View> query = (from a in db.ProjectOrg_View orderby a.ProjectName select a); return(queryBuilder.BindQueryToContext(ref query, ref pageCondtion)); }
public async Task DeleteAsync(long id, string tableName) { using (var connection = _connectionFactory.GetOpenedConnection()) { var affectedrows = await connection.ExecuteAsync(SQLQueryBuilder.BuildDeleteQuery(tableName), new { id }); CheckRows(id, tableName, affectedrows); } }
/// <summary> /// Drops the tables with the given table names. /// <para/> /// SQL query Example: <para/> /// DROP TABLE table1 /// </summary> /// <param name="tableNames">A list of table names to delete.</param> public override void DropTables(List <string> tableNames) { foreach (var tableName in tableNames) { SQLQueryBuilder sqb = new SQLQueryBuilder(); sqb.DropTable().AddValue(tableName); CommitQuery(sqb.ToString()); } }
/// <summary> /// Creates the delete trigger to keep track of the row count. /// Basically every delete command subtracts +1 to the row count. /// <para/> /// SQL query Example: <para/> /// create trigger if not exists table_trigger_sub after delete on table BEGIN update table_count set rowCount = rowCount - 1 where id = 1; END /// </summary> /// <param name="tableName">The name of the table.</param> /// <returns>The finished sql string.</returns> private string CreateCounterSubTriger(string tableName) { SQLQueryBuilder sqb = new SQLQueryBuilder(); sqb.Create().Trigger().IfNotExists().AddValue(tableName + "_trigger_sub").After().Delete().On().AddValue(tableName); sqb.Begin().Update().AddValue(tableName + "_count").Set().AddValue("rowCount").Equal().AddValue("rowCount - 1"); sqb.Where().AddValue("id").Equal().AddValue("1").CommaPoint(true).End(); return(sqb.ToString()); }
public List <POEntity_View> GetSupplierDDLItem(SQLQueryBuilder queryBuilder) { PageBase pageCondition = new PageBase(); IQueryable <POEntity_View> query = (from a in db.POEntity_View orderby a.POID select a); return(queryBuilder.BindQueryToContext(ref query, ref pageCondition)); }
/// <summary> /// Get's the last n rows from the specified table. /// <para/> /// SQL query Example:<para/> /// SELECT * FROM table ORDER BY id DESC LIMIT 100 /// </summary> /// <param name="rows">number of the rows to display.</param> /// <param name="table">The table to get the values from.</param> /// <returns></returns> public override List <List <object> > GetLastNRowsFromTable(Table table, int rows) { SQLQueryBuilder sqb = new SQLQueryBuilder(); sqb.Select().ALL().From().AddValue(table.TableName).OrderBY().AddValue(table.Columns.First().Key).Desc().Limit().AddValue(rows.ToString()); List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table)); return(results); }
public void Test_GetRecsFromDB() { string strSQL = SQLQueryBuilder.GetSQLStatement_DWOrderAddressesNotInGLM(); DAL.CDWOrderAddressRecord objCDWQuery = new CDWOrderAddressRecord(); List <CDWOrderAddressRecord> lstResults = objCDWQuery.GetRecords(strSQL); Assert.IsTrue(lstResults.Count > 0); }
public List <Users> GetUserAccDetail(SQLQueryBuilder queryBuilder) { PageBase pageCondition = new PageBase(); IQueryable <Users> query = from a in db.Users orderby a.FirstName select a; //System.Threading.Thread.Sleep(3000); return(queryBuilder.BindQueryToContext(ref query, ref pageCondition)); }
public async static Task <string> CreateQueryResult(string question) { return(await Task.Run(() => { var query = SQLQueryBuilder.Build(question); var result = new QueryExecutor(ConnectionHelper.ConnectionString).ExecuteQuery(query); return result; })); }
public List <Account> GetAccountItems(SQLQueryBuilder queryBuilder) { PageBase pageCondition = new PageBase(); IQueryable <Account> query = from a in db.Account orderby a.UserID select a; query.Count(); return(queryBuilder.BindQueryToContext(ref query, ref pageCondition)); }
/// <summary> /// Deletes the last n rows of the given table. /// </summary> /// <param name="table">The table to delete the last n data from.</param> /// <param name="rows">The amount of data to delete.</param> public override void DeleteLastNRows(Table table, int rows) { SQLQueryBuilder sqb = new SQLQueryBuilder(); string columnName = table.Columns.First().Key; string param = sqb.Select().AddValue(columnName).From().AddValue(table.TableName).OrderBY(). AddValue(columnName).Asc().Limit().AddValue(rows.ToString()).Flush(); sqb.Delete().From().AddValue(table.TableName).Where().AddValue(columnName).In().Brackets(param); CommitQuery(sqb.ToString()); }
/// <summary> /// Gets all rows in the given id slot. /// <para/> /// SQL query Example: <para/> /// SELECT * from table3 where id >= 0 and id -= 1 /// </summary> /// <param name="table"></param> /// <param name="start"></param> /// <param name="end"></param> /// <returns></returns> public override List <List <object> > GetRowsFromTableWithIndex(Table table, int start, int end) { SQLQueryBuilder sqb = new SQLQueryBuilder(); sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(table.Columns.First().Key); sqb.GreaterThen().AddValue(start.ToString()).AND().AddValue(table.Columns.First().Key).LesserThen().AddValue(end.ToString()); List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table)); return(results); }
public void Test_GetRecsFromDB_Changeset() { DateTime dteDateTimeCutoff = new DateTime(2018, 04, 01, 13, 59, 59); string strSQL = SQLQueryBuilder.GetSQLStatement_DWOrderAddressesChangeset(dteDateTimeCutoff, dteDateTimeCutoff, dteDateTimeCutoff, dteDateTimeCutoff); DAL.CDWOrderAddressRecord objCDWChangesetQuery = new CDWOrderAddressRecord(); List <CDWOrderAddressRecord> lstResults = objCDWChangesetQuery.GetRecords(strSQL); Assert.IsTrue(lstResults.Count > 0); }
public void TestMethod7() { string testQuestion = "Какие памятники находятся в Адмиралтейском районе"; string expectedResult = "SELECT MONUMENTS.NAME FROM AREAS join ADDRESSES on AREAS.ID = ADDRESSES.ID_AREA join MONUMENTS on ADDRESSES.ID = MONUMENTS.ID_ADDRESS WHERE AREAS.NAME like '%Адмиралтейский%'"; string actualResult = SQLQueryBuilder.Build(testQuestion); Assert.AreEqual(expectedResult, actualResult); string sqlResult = new QueryExecution.QueryExecutor(ConnectionHelper.ConnectionString).ExecuteQuery(actualResult); Assert.IsFalse(sqlResult.Contains("error")); }
public void TestMethod6() { string testQuestion = "Какие парки находятся в г Петергоф"; string expectedResult = "SELECT PARKS.NAME FROM ADDRESSES join PARKS on ADDRESSES.ID = PARKS.ID_ADDRESS WHERE ADDRESSES.STREET like '%Петергоф%'"; string actualResult = SQLQueryBuilder.Build(testQuestion); Assert.AreEqual(expectedResult, actualResult); string sqlResult = new QueryExecution.QueryExecutor(ConnectionHelper.ConnectionString).ExecuteQuery(actualResult); Assert.IsFalse(sqlResult.Contains("error")); }
public void TestMethod5() { string testQuestion = "Какие музеи находятся на ул Садовая"; string expectedResult = "SELECT MUSEUMS.NAME FROM ADDRESSES join MUSEUMS on ADDRESSES.ID = MUSEUMS.ID_ADDRESS WHERE ADDRESSES.STREET like '%Садовая%'"; string actualResult = SQLQueryBuilder.Build(testQuestion); Assert.AreEqual(expectedResult, actualResult); string sqlResult = new QueryExecution.QueryExecutor(ConnectionHelper.ConnectionString).ExecuteQuery(actualResult); Assert.IsFalse(sqlResult.Contains("error")); }