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);
}
protected virtual void EnsureLoaded()
{
if (IsDirty)
{
if (!IsLoaded)
{
NPathQuery npquery = new NPathQuery(query.ToNPath(), typeof(T));
Converter.Parameters
.ForEach(param => npquery.Parameters.Add(new QueryParameter(param)));
innerList.Clear();
query.Context.GetObjectsByNPath(npquery, innerList);
// IsLoaded = true;
}
else
{
NPathQuery npquery = new NPathQuery(query.ToNPath(), typeof(T));
List <T> oldList = innerList;
IList tmp = query.Context.FilterObjects(innerList, npquery);
List <T> newList = new List <T>();
foreach (T item in tmp)
{
newList.Add(item);
}
this.innerList = newList;
}
IsDirty = false;
}
}
// Validates if user is authorized to login
private StatusCode ValidateUserCredentials(string p_username, string p_password)
{
string queryString = "SELECT TOP 1 * FROM Mitarbeiter WHERE LoginNamen = ? AND LoginPasswort = ?";
NPathQuery npathQuery = new NPathQuery(queryString, typeof(Mitarbeiter));
npathQuery.Parameters.Add(new QueryParameter(DbType.String, p_username));
npathQuery.Parameters.Add(new QueryParameter(DbType.String, p_password));
user = null;
try
{
user = npcontext.GetObjectByNPath <Mitarbeiter>(queryString, npathQuery.Parameters);
}
catch (Exception e)
{
}
if (user != null)
{
return(StatusCode.USER_VALIDATION_SUCCESSFULL);
}
else
{
return(StatusCode.USER_VALIDATION_FAILED);
}
}
// Gets the working hours for the user
// in the interval from 'start' to 'end' date
public int GetWorkingHoursForInterval(System.DateTime start, System.DateTime end)
{
string queryString = "SELECT * FROM ZeitBuchung WHERE MId = ? AND Datum >= ? AND Datum <= ? AND TypId = ? ORDER BY Datum ASC";
NPathQuery npathQuery = new NPathQuery(queryString, typeof(ZeitBuchung));
npathQuery.Parameters.Add(new QueryParameter(DbType.Int32, userId));
npathQuery.Parameters.Add(new QueryParameter(DbType.DateTime, start));
npathQuery.Parameters.Add(new QueryParameter(DbType.DateTime, end));
npathQuery.Parameters.Add(new QueryParameter(DbType.Int32, ZeitBuchung.ZBTypToInt(ZeitBuchung.ZBTyp.KOMMEN)));
System.Collections.IList l = npcontext.GetObjectsByNPath(npathQuery);
int h = 0;
foreach (ZeitBuchung b in l)
{
ZeitBuchung a = GetCorrespondingZeitBuchungFor(b);
if (a == null)
{
continue;
}
h += a.GetHourDiff(b);
}
return(h);
}
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);
}
//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);
}
}
}
private void LoadObject(string identity, ref object obj, bool ignoreObjectNotFound, Type type, KeyStruct key)
{
IClassMap classMap = this.Context.DomainMap.MustGetClassMap(obj.GetType());
IObjectCache cache = GetObjectCache();
if (classMap.IsReadOnly)
{
if (this.Context.ReadOnlyObjectCacheManager.LoadObject(obj))
{
SetTransactionGuid(obj);
cache.LoadedObjects[key] = obj;
return;
}
}
if (classMap.LoadSpan.Length > 0)
{
IList listToFill = new ArrayList();
NPathQuery query = this.Context.GetLoadObjectNPathQuery(obj, classMap.LoadSpan);
this.Context.PersistenceEngine.LoadObjects(query, listToFill);
if (listToFill.Count < 1)
{
if (ignoreObjectNotFound == false)
{
throw new ObjectNotFoundException("Object of type " + type.ToString() + " and with identity '" + identity + "' not found!"); // do not localize
}
obj = null;
}
else if (listToFill.Count > 1)
{
//throw new WtfException("I thought you said your primary keys were unique, no??")
obj = null;
}
else
{
obj = listToFill[0];
SetTransactionGuid(obj);
cache.LoadedObjects[key] = obj;
}
}
else
{
SetTransactionGuid(obj);
this.Context.PersistenceEngine.LoadObject(ref obj);
if (obj == null)
{
if (ignoreObjectNotFound == false)
{
throw new ObjectNotFoundException("Object of type " + type.ToString() + " and with identity '" + identity + "' not found!"); // do not localize
}
}
else
{
cache.LoadedObjects[key] = obj;
}
}
}
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);
}
}
//Fetch a list of all the orders in the database placed between
//the specified dates, ordered by their order dates
private void FetchOrders()
{
//set up the list view columns
SetupListViewColumns();
//clearing any old list view items
ordersListView.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(Order);
//This is the query string for our query,
//formulated in the query langauge NPath
string npathQueryString = "Select * From Order ";
//add the where clause to the query string
npathQueryString += "Where OrderDate Between ? And ? ";
//create the parameters
QueryParameter fromDateParameter = new QueryParameter(DbType.DateTime, fromDateTimePicker.Value);
QueryParameter toDateParameter = new QueryParameter(DbType.DateTime, toDateTimePicker.Value);
//add the parameters to our query object
npathQuery.Parameters.Add(fromDateParameter);
npathQuery.Parameters.Add(toDateParameter);
//Add order by clause
npathQueryString += "Order By OrderDate";
//Add our query string to our query object
npathQuery.Query = npathQueryString;
//Ask the context to execute the query and return the matching orders
IList orders = context.GetObjectsByQuery(npathQuery);
//Add the resulting orders to the list view
foreach (Order order in orders)
{
AddOrderToListView(order);
}
}
}
// Gets the corresponding ZeitBuchung for the supplied parameter ZeitBuchung.
// E.g. if the "Gehen" Buchung is supplied, the method returns
// the corresponding "Kommen" Buchung and vice versa.
public ZeitBuchung GetCorrespondingZeitBuchungFor(ZeitBuchung b)
{
if (!userLoggedIn)
{
return(null);
}
if (b == null)
{
return(null);
}
ZeitBuchung erg = null;
string queryString = null;
NPathQuery npathQuery = null;
if (b.Typ == ZeitBuchung.ZBTyp.GEHEN)
{
queryString = "SELECT TOP 1 * FROM ZeitBuchung WHERE MId = ? AND Datum < ? AND TypId = ? ORDER BY Datum DESC";
npathQuery = new NPathQuery(queryString, typeof(ZeitBuchung));
npathQuery.Parameters.Add(new QueryParameter(DbType.Int32, userId));
npathQuery.Parameters.Add(new QueryParameter(DbType.DateTime, b.Datum));
npathQuery.Parameters.Add(new QueryParameter(DbType.Int32, ZeitBuchung.ZBTypToInt(ZeitBuchung.ZBTyp.KOMMEN)));
}
if (b.Typ == ZeitBuchung.ZBTyp.KOMMEN)
{
queryString = "SELECT TOP 1 * FROM ZeitBuchung WHERE MId = ? AND Datum > ? AND TypId = ? ORDER BY Datum ASC";
npathQuery = new NPathQuery(queryString, typeof(ZeitBuchung));
npathQuery.Parameters.Add(new QueryParameter(DbType.Int32, userId));
npathQuery.Parameters.Add(new QueryParameter(DbType.DateTime, b.Datum));
npathQuery.Parameters.Add(new QueryParameter(DbType.Int32, ZeitBuchung.ZBTypToInt(ZeitBuchung.ZBTyp.GEHEN)));
}
if (npathQuery == null)
{
return(null);
}
try
{
erg = (ZeitBuchung)npcontext.GetObjectByNPath(npathQuery);
}
catch (Exception e)
{
}
return(erg);
}
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));
}
// Get the last 5 ZeitBuchungen for the user
public ZeitBuchung[] GetRecentZeitBuchungen()
{
if (!userLoggedIn)
{
return(null);
}
string queryString = "SELECT * FROM Mitarbeiter WHERE MId = ? ORDER BY Datum DESC";
NPathQuery npathQuery = new NPathQuery(queryString, typeof(ZeitBuchung));
npathQuery.Parameters.Add(new QueryParameter(DbType.Int32, userId));
ZeitBuchung[] erg = null;
try
{
System.Collections.IList l = npcontext.GetObjectsByNPath(npathQuery);
erg = new ZeitBuchung[l.Count];
l.CopyTo(erg, 0);
}
catch (Exception e)
{ }
return(erg);
}
// Gets the last registered ZeitBuchung for the user
public ZeitBuchung GetLastZeitBuchung()
{
if (!userLoggedIn)
{
return(null);
}
string queryString = "SELECT TOP 1 * FROM Mitarbeiter WHERE MId = ? ORDER BY Datum DESC";
NPathQuery npathQuery = new NPathQuery(queryString, typeof(ZeitBuchung));
npathQuery.Parameters.Add(new QueryParameter(DbType.Int32, userId));
ZeitBuchung erg = null;
try
{
erg = (ZeitBuchung)npcontext.GetObjectByNPath(npathQuery);
}
catch (Exception e)
{
}
return(erg);
}
public virtual void TestFetchEmployeesNamedNancyDavolioByNPathQuery()
{
int bossid = EnsureBoss();
int id = EnsureNancy(bossid);
using (IContext context = GetContextWithAttributes())
{
//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);
}
}
}
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 String[] GetUserLoginNames()
{
if (!userLoggedIn)
{
return(null);
}
string queryString = "SELECT * FROM Mitarbeiter";
NPathQuery npathQuery = new NPathQuery(queryString, typeof(Mitarbeiter));
string[] erg = null;
try
{
System.Collections.IList l = npcontext.GetObjectsByNPath(npathQuery);
erg = new string[l.Count];
for (int i = 0; i < l.Count; ++i)
{
erg[i] = ((Mitarbeiter)l[i]).LoginNamen;
}
}
catch (Exception e)
{ }
return(erg);
}
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);
}
}
public override IList GetObjects(Type type, string where)
{
NPathQuery query = CreateQuery(type, where);
return(GetContext().GetObjectsByNPath(query));
}
public override IList GetObjects(Type type, IDictionary <string, object> match)
{
NPathQuery query = CreateQuery(type, match);
return(GetContext().GetObjectsByNPath(query));
}
