/// <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"));
}
