public EasyQueryController(ApplicationDbContext dbContext, IHostingEnvironment env)
{
this._dbContext = dbContext;
eqService = new EqServiceProviderDb();
eqService.StoreQueryInCache = false;
string dataPath = System.IO.Path.Combine(env.ContentRootPath, ""); //enter your value
eqService.DataPath = dataPath;
eqService.Formats.SetDefaultFormats(FormatType.MsSqlServer);
eqService.Formats.UseSchema = true;
eqService.ModelLoader = (model, modelName) => {
(model as DbModel).LoadFromDbContext(dbContext);
};
eqService.ConnectionResolver = () => {
return(dbContext.Database.GetDbConnection());
};
eqService.ValueListResolver = (listname) => {
//TO DO
return(Enumerable.Empty <ListItem>());
};
}
public OrderController(AppDbContext context)
{
this.dbContext = context;
eqService = new EqServiceProviderDb();
eqService.ModelLoader = (model, modelName) => {
model.LoadFromEntityType(typeof(Order));
model.SortEntities();
};
}
public CustomerController(NwindContext context)
{
_context = context;
_eqService = new EqServiceProviderDb();
_eqService.ModelLoader = (model, modelName) => {
model.LoadFromEntityType(typeof(Customer));
model.SortEntities();
//These operators are not supported in OData queries - so we need to remove them
model.Operators.RemoveByIDs(model, "InList,NotInList,ListContains,Contains,NotContains");
};
}
public EasyQueryController(AppDbContext dbContext, IHostingEnvironment env)
{
this._dbContext = dbContext;
eqService = new EqServiceProviderDb();
eqService.StoreQueryInCache = false;
string dataPath = System.IO.Path.Combine(env.ContentRootPath, "App_Data");
eqService.DataPath = dataPath;
eqService.Formats.SetDefaultFormats(FormatType.MsSqlServer);
eqService.Formats.UseSchema = true;
eqService.ModelLoader = (model, modelName) => {
(model as DbModel).LoadFromDbContext(dbContext);
};
eqService.ConnectionResolver = () => {
return(dbContext.Database.GetDbConnection());
};
eqService.ValueListResolver = (listname) => {
if (listname == "Regions")
{
return(new List <ListItem> {
new ListItem("US", "USA", new List <ListItem> {
new ListItem("CA", "California"),
new ListItem("CO", "Colorado"),
new ListItem("OR", "Oregon"),
new ListItem("WA", "Washington")
}),
new ListItem("CA", "CANADA", new List <ListItem> {
new ListItem("AB", "Alberta"),
new ListItem("ON", "Ontario"),
new ListItem("QC", "Québec")
}),
});
}
return(Enumerable.Empty <ListItem>());
};
}
public AdvancedSearchController()
{
eqService = new EqServiceProviderDb();
eqService.DefaultModelName = "Recommended_SystemModel";
eqService.StoreModelInSession = true;
eqService.StoreQueryInSession = true;
string connectionString = ConfigurationManager.ConnectionStrings["Recommended_System"].ConnectionString;
eqService.Connection = new SqlConnection(connectionString);
//You can set DbConnection directly (without using ConfigurationManager)
//eqService.Connection = new SqlCeConnection("Data Source=" + System.IO.Path.Combine(dataPath, "Northwind.sdf"));
//to support saving/loading models and queries to/from Session
eqService.SessionGetter = key => Session[key];
eqService.SessionSetter = (key, value) => Session[key] = value;
//Uncomment in case you need to implement your own model loader or add some changes to existing one
//eqService.ModelLoader = (model, modelName) => {
// model.LoadFromConnection(eqService.Connection, FillModelOptions.Default);
//};
//Setting format of result SQL
eqService.Formats.SetDefaultFormats(FormatType.MsSqlServer);
eqService.Formats.UseSchema = false;
//Custom lists resolver
eqService.CustomListResolver = (listname) => {
if (listname == "ListName")
{
return(new List <ListItem> {
new ListItem("ID1", "Item 1"),
new ListItem("ID2", "Item 2")
});
}
return(Enumerable.Empty <ListItem>());
};
//EqServiceProvider needs to know where to save/load queries to/from
eqService.DataPath = System.Web.HttpContext.Current.Server.MapPath("~/App_Data");
}
public HomeController()
{
_eqService = new EqServiceProviderDb
{
ModelLoader = (model, modelName) =>
{
model.LoadFromContext(typeof(O2DataMart));
// model.LoadFromDBContext(O2DataMart.GetType(), DbContextOptions.IncludeComplexTypesInParentEntity);
model.SortEntities();
}
};
_dbConnectionString = ConfigurationManager.ConnectionStrings[@"O2DataMart"].ConnectionString;
_eqService.Connection = new SqlConnection(_dbConnectionString);
_eqService.Connection = new SqlConnection(_dbConnectionString);
_schemaRepository = new SchemaRepository(_eqService.Connection.ConnectionString);
}
public EasyQueryController(IHostingEnvironment env, IConfiguration config)
{
eqService = new EqServiceProviderDb();
eqService.DefaultModelId = "NWindSQL";
eqService.CacheGetter = (key) => HttpContext.Session.GetString(key);
eqService.CacheSetter = (key, value) => HttpContext.Session.SetString(key, value);
eqService.StoreQueryInCache = true;
eqService.Formats.SetDefaultFormats(FormatType.MsSqlServer);
eqService.Formats.UseSchema = true;
string dataPath = System.IO.Path.Combine(env.ContentRootPath, "App_Data");
eqService.DataPath = dataPath;
eqService.ConnectionResolver = () => {
return(new SqlConnection(config.GetConnectionString("EqDemoDb")));
};
eqService.ValueListResolver = (listname) => {
if (listname == "Regions")
{
return(new List <ListItem> {
new ListItem("US", "USA", new List <ListItem> {
new ListItem("CA", "California"),
new ListItem("CO", "Colorado"),
new ListItem("OR", "Oregon"),
new ListItem("WA", "Washington")
}),
new ListItem("CA", "CANADA", new List <ListItem> {
new ListItem("AB", "Alberta"),
new ListItem("ON", "Ontario"),
new ListItem("QC", "Québec")
}),
});
}
return(Enumerable.Empty <ListItem>());
};
}
public RulesController()
{
_dqRuleService = new DqRuleService();
eqService = new EqServiceProviderDb();
eqService.DefaultModelName = "cdmamodel"; // "NWindSQL";
eqService.SessionGetter = key => Session[key];
eqService.SessionSetter = (key, value) => Session[key] = value;
eqService.StoreQueryInSession = true;
eqService.Formats.SetDefaultFormats(FormatType.Oracle);
eqService.Formats.UseSchema = true;
eqService.Formats.EOL = EOLSymbol.None;
string dataPath = System.Web.HttpContext.Current.Server.MapPath("~/App_Data");
eqService.DataPath = dataPath;
//eqService.Connection = new SqlCeConnection("Data Source=" + System.IO.Path.Combine(dataPath, "Northwind.sdf"));
eqService.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringCDMA"].ToString());
eqService.CustomListResolver = (listname) =>
{
if (listname == "Regions")
{
return(new List <ListItem> {
new ListItem("US", "USA", new List <ListItem> {
new ListItem("CA", "California"),
new ListItem("CO", "Colorado"),
new ListItem("OR", "Oregon"),
new ListItem("WA", "Washington")
}),
new ListItem("CA", "CANADA", new List <ListItem> {
new ListItem("AB", "Alberta"),
new ListItem("ON", "Ontario"),
new ListItem("QC", "Québec")
}),
});
}
return(Enumerable.Empty <ListItem>());
};
}
public AdvancedSearchController()
{
var context = new ApplicationDbContext();
context.Database.Initialize(false);
var connection = context.Database.Connection;
eqService = new EqServiceProviderDb();
eqService.StoreModelInSession = true;
eqService.StoreQueryInSession = true;
eqService.Connection = connection;
eqService.ModelLoader = (model, modelName) => {
model.Clear();
model.LoadFromDBContext(context);
};
eqService.SessionGetter = key => Session[key];
eqService.SessionSetter = (key, value) => Session[key] = value;
}
public EasyReportController(IHostingEnvironment env, IConfiguration config, ApplicationDbContext dbContext, UserManager <ApplicationUser> userManager)
{
this._dbContext = dbContext;
_userManager = userManager;
eqService = new EqServiceProviderDb();
eqService.Paging.Enabled = true;
eqService.DefaultModelId = "NWindSQL";
eqService.UserId = "TestUser";
eqService.StoreModelInCache = false;
eqService.StoreQueryInCache = false;
eqService.Formats.SetDefaultFormats(FormatType.MsSqlServer);
eqService.Formats.UseSchema = true;
//EqServiceProvider needs to know where to save/load queries to/from
string dataPath = System.IO.Path.Combine(env.ContentRootPath, "App_Data");
eqService.DataPath = dataPath;
eqService.ConnectionResolver = () => {
return(new SqlConnection(config.GetConnectionString("DefaultConnection")));
};
//You can set DbConnection directly (without using ConfigurationManager)
//eqService.Connection = new SqlCeConnection("Data Source=" + System.IO.Path.Combine(dataPath, "Northwind.sdf"));
//Uncomment in case you need to implement your own model loader or add some changes to existing one
eqService.ModelLoader = (model, modelName) => {
(model as DbModel).LoadFromDbContext(dbContext);
model.EntityRoot.Scan(ent => {
//Make invisible all entities started with "AspNetCore" and "Report"
if (ent.Name.StartsWith("Asp") || ent.Name == "Report")
{
ent.UseInConditions = false;
ent.UseInResult = false;
ent.UseInSorting = false;
}
}
, null, false);
};
//The following four handlers (QuerySaver, QueryLoader, QueryRemover and QueryListResolver) are overrided in order to don't save to all Reports in database
//This is for demo purpose only, you may freely delete this code or modify to your notice
// --- begining of overrided handlers ---
eqService.QuerySaver = (query, queryId) => {
var CurrentUserId = GetCurrentUserId();
if (!string.IsNullOrEmpty(CurrentUserId))
{
if (!string.IsNullOrEmpty(queryId))
{
StringBuilder xml = new StringBuilder();
var queryXML = XmlWriter.Create(xml);
query.SaveToXmlWriter(queryXML, Query.RWOptions.All);
queryXML.Flush();
Report NewReport = new Report()
{
Id = query.ID,
UserId = CurrentUserId,
Name = query.QueryName,
QueryXML = xml.ToString()
};
_dbContext.Reports.Add(NewReport);
_dbContext.SaveChanges();
}
}
};
eqService.QueryLoader = (query, queryId) => {
var CurrentUserId = GetCurrentUserId();
if (!string.IsNullOrEmpty(CurrentUserId))
{
var report = _dbContext.Reports.Find(queryId);
string queryString = report.QueryXML;
if (String.IsNullOrWhiteSpace(queryString))
{
eqService.DefaultQueryLoader(query, queryId);
report.QueryXML = query.SaveToString();
_dbContext.Reports.Update(report);
_dbContext.SaveChanges();
}
else
{
query.LoadFromString(queryString);
}
}
};
eqService.QueryRemover = (queryId) => {
var CurrentUserId = GetCurrentUserId();
if (!string.IsNullOrEmpty(CurrentUserId))
{
var deleteReport = _dbContext.Reports.Find(queryId);
if (deleteReport != null)
{
if (deleteReport.UserId == CurrentUserId)
{
_dbContext.Reports.Remove(deleteReport);
_dbContext.SaveChanges();
}
}
}
};
eqService.QueryListResolver = (CurrentUserId) => {
if (!string.IsNullOrEmpty(CurrentUserId))
{
List <QueryListItem> queryItems = _dbContext.Reports
.Where(r => r.UserId == CurrentUserId)
.Select(r => new QueryListItem(r.Id, r.Name))
.ToList();
return(queryItems);
}
return(Enumerable.Empty <QueryListItem>());
};
//Custom lists resolver
eqService.ValueListResolver = (listname) => {
if (listname == "ListName")
{
return(new List <ListItem> {
new ListItem("ID1", "Item 1"),
new ListItem("ID2", "Item 2")
});
}
return(Enumerable.Empty <ListItem>());
};
}
public EasyReportController(IHostingEnvironment env, IConfiguration config)
{
eqService = new EqServiceProviderDb();
eqService.Paging.Enabled = true;
eqService.DefaultModelId = "NWindSQL";
eqService.UserId = "TestUser";
eqService.StoreModelInCache = true;
eqService.StoreQueryInCache = true;
//EqServiceProvider needs to know where to save/load queries to/from
eqService.Formats.SetDefaultFormats(FormatType.MsSqlServer);
eqService.Formats.UseSchema = true;
string dataPath = System.IO.Path.Combine(env.ContentRootPath, "App_Data");
eqService.DataPath = dataPath;
eqService.ConnectionResolver = () => {
return(new SqlConnection(config.GetConnectionString("EqDemoDb")));
};
//You can set DbConnection directly (without using ConfigurationManager)
//eqService.Connection = new SqlCeConnection("Data Source=" + System.IO.Path.Combine(dataPath, "Northwind.sdf"));
//to support saving/loading models and queries to/from Session
eqService.CacheGetter = (key) => HttpContext.Session.GetString(key);
eqService.CacheSetter = (key, value) => HttpContext.Session.SetString(key, value.ToString());
//The following four handlers (QuerySaver, QueryLoader, QueryRemover and QueryListResolver) are overrided in order to don't save to the server the changes user make - all changed/added queries are stored in Session object
//This is for demo purpose only, you may freely delete this code or modify to your notice
// --- begining of overrided handlers ---
eqService.QuerySaver = (query, queryId) => {
if (!string.IsNullOrEmpty(queryId))
{
HttpContext.Session.SetString("query" + queryId, query.SaveToString());
List <QueryListItem> queries = HttpContext.Session.GetObject <List <QueryListItem> >("queryList");
if (queries != null)
{
QueryListItem item = queries.Find(x => x.id.Equals(queryId));
if (item == null)
{
item = new QueryListItem(query.ID, query.QueryName);
queries.Add(item);
}
item.name = query.QueryName;
item.description = query.QueryDescription;
HttpContext.Session.SetObject("queryList", queries);
}
}
};
eqService.QueryLoader = (query, queryId) => {
if (!string.IsNullOrEmpty(queryId))
{
string queryString = HttpContext.Session.GetString("query" + queryId);
if (String.IsNullOrWhiteSpace(queryString))
{
eqService.DefaultQueryLoader(query, queryId);
HttpContext.Session.SetString("query" + queryId, query.SaveToString());
}
else
{
query.LoadFromString(queryString);
}
}
};
eqService.QueryRemover = (queryId) => {
if (!string.IsNullOrEmpty(queryId))
{
HttpContext.Session.Remove("query" + queryId);
List <QueryListItem> queries = HttpContext.Session.GetObject <List <QueryListItem> >("queryList");
if (queries != null)
{
QueryListItem item = queries.Find(x => x.id.Equals(queryId));
if (item != null)
{
queries.Remove(item);
}
}
}
};
eqService.QueryListResolver = (modelId) => {
List <QueryListItem> queryItems = HttpContext.Session.GetObject <List <QueryListItem> >("queryList");
if (queryItems == null)
{
queryItems = eqService.DefaultQueryListResolver(modelId) as List <QueryListItem>;
HttpContext.Session.SetObject("queryList", queryItems);
}
return(queryItems);
};
// --- end of overrided handlers ---
//Uncomment in case you need to implement your own model loader or add some changes to existing one
// eqService.ModelLoader = (model, modelName) => {
// model.LoadFromConnection(eqService.Connection, FillModelOptions.Default);
// model.
// };
//Custom lists resolver
eqService.ValueListResolver = (listname) => {
if (listname == "ListName")
{
return(new List <ListItem> {
new ListItem("ID1", "Item 1"),
new ListItem("ID2", "Item 2")
});
}
return(Enumerable.Empty <ListItem>());
};
}
public EasyQueryController(IHostingEnvironment env, IConfiguration config)
{
eqService = new EqServiceProviderDb();
eqService.DefaultModelId = "NWindSQL";
eqService.CacheGetter = (key) => HttpContext.Session.GetString(key);
eqService.CacheSetter = (key, value) => HttpContext.Session.SetString(key, value);
eqService.StoreQueryInCache = true;
eqService.Paging.Enabled = true;
eqService.Formats.SetDefaultFormats(FormatType.MsSqlServer);
eqService.Formats.UseSchema = true;
string dataPath = System.IO.Path.Combine(env.ContentRootPath, "App_Data");
eqService.DataPath = dataPath;
eqService.ConnectionResolver = () => {
return(new SqlConnection(config.GetConnectionString("EqDemoDbMVC")));
};
eqService.ValueListResolver = (listname) => {
if (listname == "Regions")
{
return(new List <ListItem> {
new ListItem("US", "USA", new List <ListItem> {
new ListItem("CA", "California"),
new ListItem("CO", "Colorado"),
new ListItem("OR", "Oregon"),
new ListItem("WA", "Washington")
}),
new ListItem("CA", "CANADA", new List <ListItem> {
new ListItem("AB", "Alberta"),
new ListItem("ON", "Ontario"),
new ListItem("QC", "Québec")
}),
});
}
if (listname.StartsWith("Cities"))
{
var connection = eqService.ConnectionResolver() as SqlConnection;
var command = connection.CreateCommand();
string entityCity = eqService.Model.EntityRoot.FindAttribute(EntityAttrProp.Expression, "Customers.City").Expr;
string entityCountry = eqService.Model.EntityRoot.FindAttribute(EntityAttrProp.Expression, "Customers.Country").Expr;
if (listname == "Cities.")
{
command.CommandText = string.Format("SELECT DISTINCT {0} FROM Customers", entityCity);
}
else
{
var param = listname.Substring(listname.IndexOf('.') + 1);
command.CommandText = string.Format("SELECT DISTINCT {0} FROM Customers WHERE {1} IN ({2})", entityCity, entityCountry, AddQuotesForParams(param));
}
try {
connection.Open();
var dataReader = command.ExecuteReader();
var valueList = new List <ListItem>();
while (dataReader.Read())
{
valueList.Add(new ListItem(dataReader[entityCity].ToString()));
}
dataReader.Close();
connection.Close();
return(valueList);
}
finally {
connection.Close();
}
}
return(Enumerable.Empty <ListItem>());
};
}
