public override IList FindAllAuctions()
{
IContext context = GetContext();
IQuery query = new NPathQuery("Select *, Item.*, Seller.*, Bids.* from Auction Order By Id", typeof(Auction));
IList auctions = (IList) context.GetObjectsByQuery(query);
return auctions;
}
public override IList FindHighBids(string auctionId)
{
IContext context = GetContext();
IQuery query = new NPathQuery("select Top 1 *, Buyer.* from Bid where Auction = ? order by Amount desc", typeof(Bid));
query.Parameters.Add(new QueryParameter(DbType.String, auctionId));
IList bids = context.GetObjectsByQuery(query);
return bids;
}
public void ParseSubQuery()
{
using (IContext context = GetContext())
{
//,(select count(*) from Products) as Blah
string npath = "select CategoryName,(1+3-(5*Id)) as Häst from Category";
string sql = new NPathQuery(npath,typeof(Category),context).ToSql() ;
Console.WriteLine(sql) ;
}
}
public virtual IQuery ToQuery(MarshalQuery marshalQuery)
{
IQuery query = null;
IContext ctx = this.Context;
IClassMap classMap = ctx.DomainMap.MustGetClassMap(marshalQuery.PrimitiveType);
Type realType = ctx.AssemblyManager.MustGetTypeFromClassMap(classMap);
if (marshalQuery.QueryType == "NPathQuery")
query = new NPathQuery(marshalQuery.QueryString, realType, this.Context);
if (marshalQuery.QueryType == "SqlQuery")
query = new SqlQuery(marshalQuery.QueryString, realType, this.Context);
query.Query = marshalQuery.QueryString;
foreach (MarshalParameter mp in marshalQuery.Parameters)
{
object value = ToParameterValue(mp);
IQueryParameter param = new QueryParameter(mp.Name, mp.DbType, value);
query.Parameters.Add(param);
}
return query;
}
public virtual void TestFetchEmployeesNamedNancyDavolioByNPathQuery()
{
using (IContext context = GetContext())
{
//Create the query string
string npath = "Select * From Employee Where FirstName = ? And LastName = ?";
//Create query parameters
QueryParameter param1 = new QueryParameter(DbType.String, "Nancy");
QueryParameter param2 = new QueryParameter(DbType.String, "Davolio");
//Create an npath query object
NPathQuery npathQuery = new NPathQuery(npath, typeof(Employee));
//add the parameters to the query object
npathQuery.Parameters.Add(param1);
npathQuery.Parameters.Add(param2);
//Ask the context to fetch all employees with the name nancy davolio
IList employees = context.GetObjectsByQuery(npathQuery);
//Assert that the context didn't return a null value
Assert.IsNotNull(employees);
//Assert that the context didn't return an empty list
Assert.IsTrue(employees.Count > 0);
//Make sure that each employee in the collection
//has the name nancy davolio
foreach (Employee employee in employees)
{
Assert.AreEqual("Nancy", employee.FirstName);
Assert.AreEqual("Davolio", employee.LastName);
}
}
}
private void RunQuery(bool eval)
{
string query = GetQuery();
if (query == "")
{
MessageBox.Show("You must enter a query first!");
return;
}
try
{
NPathQuery npath = new NPathQuery(query) ;
IContext context = GetContext();
IClassMap classMap = context.NPathEngine.GetRootClassMap(query, context.DomainMap);
Type type = context.AssemblyManager.MustGetTypeFromClassMap(classMap);
if (type == null)
throw new Exception("Could not find type for classMap " + classMap.Name);
npath.Context = context;
npath.PrimaryType = type;
NPathQueryType npathQueryType = context.NPathEngine.GetNPathQueryType(query);
string sql = npath.ToSql();
textSql.Text = sql;
if (!(eval))
{
if (npathQueryType == NPathQueryType.SelectObjects)
{
DataTable sqlResult = context.SqlExecutor.ExecuteDataTable(sql, context.GetDataSource( classMap.GetSourceMap()), npath.Parameters);
gridRows.DataSource = sqlResult;
IList result = context.GetObjectsByQuery(npath);
DataTable table = GetDataTable(result, context);
gridObjects.DataSource = table;
}
if (npathQueryType == NPathQueryType.SelectScalar)
{
DataTable sqlResult = context.SqlExecutor.ExecuteDataTable(sql, context.GetDataSource( classMap.GetSourceMap()));
gridRows.DataSource = sqlResult;
object result = context.ExecuteScalar(npath);
DataTable table = GetScalarDataTable(result);
gridObjects.DataSource = table;
}
if (npathQueryType == NPathQueryType.SelectTable )
{
DataTable sqlResult = context.SqlExecutor.ExecuteDataTable(sql, context.GetDataSource( classMap.GetSourceMap()));
gridRows.DataSource = sqlResult;
DataTable result = context.GetDataTable(npath);
gridObjects.DataSource = result;
}
}
context.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void RunQuery(string query)
{
if (query == "")
query = GetQuery();
if (query == "")
{
MessageBox.Show("You must enter a query first!");
return;
}
Cursor cacheCursor = this.Cursor ;
// try
// {
this.Cursor = Cursors.WaitCursor ;
Application.DoEvents() ;
IList filter = null;
if (queryResultMenuItem.Checked)
{
filter = GetListViewObjects();
}
objectsListView.Clear() ;
objectsListView.Columns.Clear() ;
objectsListView.Items.Clear() ;
NPathQuery npath = new NPathQuery(query) ;
IClassMap classMap = Context.NPathEngine.GetRootClassMap(query, Context.DomainMap);
Type type = Context.AssemblyManager.MustGetTypeFromClassMap(classMap);
npath.Context = Context;
npath.PrimaryType = type;
NPathQueryType npathQueryType = Context.NPathEngine.GetNPathQueryType(query);
if (npathQueryType == NPathQueryType.SelectObjects)
{
IList result = null;
if (queryDataSourceMenuItem.Checked)
{
result = Context.GetObjects(npath, type);
}
else if (queryFilterMenuItem.Checked)
{
result = Context.FilterObjects(npath);
}
else
{
result = Context.FilterObjects(filter, npath);
}
ObjectListViewItem.SetupColumns(Context, type, objectsListView);
objectsListView.BeginUpdate() ;
foreach (object obj in result)
{
ListViewItem listViewItem = new ObjectListViewItem(Context, obj, type) ;
objectsListView.Items.Add(listViewItem );
}
objectsListView.EndUpdate() ;
}
if (npathQueryType == NPathQueryType.SelectScalar)
{
MessageBox.Show("Only queries that returned objects are allowed in the Object Explorer! For scalar queries, please use the Query Analyzer instead.");
}
if (npathQueryType == NPathQueryType.SelectTable )
{
MessageBox.Show("Only queries that returned objects are allowed in the Object Explorer! For tabular queries, please use the Query Analyzer instead.");
}
// }
// catch (CompositeException compEx)
// {
// ListAllSystemExceptions(compEx.InnerExceptions);
// MessageBox.Show("Exceptions were encountered while executing the query! Please inspect the list of errors for more information!");
// }
// catch (Exception ex)
// {
// IList exceptions = new ArrayList();
// exceptions.Add(ex);
// ListAllSystemExceptions(exceptions);
// MessageBox.Show("An exception was encountered while executing the query! Please inspect the list of errors for more information!");
// }
this.Cursor = cacheCursor ;
}
//Fetch a list of all the employees in the database where the
//first or last name contains the string in the filter text box,
//ordered by first name and last name and add them to the list view
private void FilterEmployees()
{
//set up the list view columns
SetupListViewColumns();
//clearing any old list view items
employeesListView.Items.Clear() ;
using (IContext context = ContextFactory.GetContext())
{
//For this query we will be using an NPathQuery object
//for comfortable handling of parameters
NPathQuery npathQuery = new NPathQuery() ;
//Set the type that we want to fetch objects of
npathQuery.PrimaryType = typeof(Employee);
//This is the query string for our query,
//formulated in the query langauge NPath
string npathQueryString = "Select * From Employee ";
string filter = filterTextBox.Text ;
//Add where clause (unless filter text box is empty)
if (filter.Length > 0)
{
//add wildcards to the filter
filter = "%" + filter + "%";
//add the where clause to the query string
npathQueryString += "Where FirstName LIKE ? or LastName LIKE ?";
//create the parameters
QueryParameter firstNameParameter = new QueryParameter(DbType.String, filter);
QueryParameter lastNameParameter = new QueryParameter(DbType.String, filter);
//add the parameters to our query object
npathQuery.Parameters.Add(firstNameParameter);
npathQuery.Parameters.Add(lastNameParameter);
}
//Add order by clause
npathQueryString += "Order By FirstName, LastName";
//Add our query string to our query object
npathQuery.Query = npathQueryString;
//Ask the context to execute the query and return the matching employees
IList employees = context.GetObjectsByQuery(npathQuery);
//Add the resulting employees to the list view
foreach (Employee employee in employees)
{
AddEmployeeToListView(employee);
}
}
}
public static IList FilterAuthors(
IContext context,
string firstName,
string lastName)
{
//Pad the first and last names with wildcard symbols
firstName = "%" + firstName + "%";
lastName = "%" + lastName + "%";
//Create the npath query string
string npathString = "Select * From Author Where " +
"FirstName Like ? and LastName Like ?";
//Create the npath query object
NPathQuery npathQuery = new NPathQuery(npathString, typeof(Author));
//Add the parameters to the npath query object
npathQuery.Parameters.Add(
new QueryParameter(DbType.AnsiString, firstName));
npathQuery.Parameters.Add(
new QueryParameter(DbType.AnsiString, lastName));
//Ask the context to fetch all authors matching the npath query
IList authors = context.GetObjectsByNPath(npathQuery);
return authors;
}
public override IList GetObjectsOfType(Type type, Filter filter)
{
if (filter.FilterItems.Count < 1)
return GetObjectsOfType(type);
NPathQuery query = new NPathQuery();
query.PrimaryType = type;
string npathString = "Select * From " + GetTypeNameFromType(type) + " Where ";
foreach (FilterItem item in filter.FilterItems)
{
npathString += item.PropertyName;
switch (item.MatchCondition)
{
case MatchCondition.Equals:
npathString += " = ";
break;
case MatchCondition.Like:
npathString += " LIKE ";
item.Value = "%" + item.Value + "%";
break;
case MatchCondition.LargerThan:
npathString += " > ";
break;
case MatchCondition.SmallerThan:
npathString += " < ";
break;
}
npathString += "? And ";
query.Parameters.Add(item.Value);
}
npathString = npathString.Substring(0, npathString.Length - 4);
query.Query = npathString;
return context.GetObjectsByNPath(query);
}
public virtual NPathQuery GetLoadObjectNPathQueryWithSelect(object obj, string npathQueryString, RefreshBehaviorType refreshBehavior)
{
IClassMap classMap = this.Context.NPathEngine.GetRootClassMap(npathQueryString, this.Context.DomainMap);
Type type = this.Context.AssemblyManager.MustGetTypeFromClassMap(classMap);
NPathQuery npathQuery = new NPathQuery(npathQueryString, type);
npathQuery.RefreshBehavior = refreshBehavior;
npathQuery.Parameters.Add(new QueryParameter(DbType.Object, obj));
return npathQuery;
}
