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; }