Archive

Archive for the ‘EntitySpaces’ Category

Query with User Defined Column Selection in EntitySpaces 2010

December 8, 2010 Leave a comment

I need to execute a query based upon a Table Column name that the user will select from a ComboBox and a search value from a Textbox. Since the Column names are strongly typed off of the Enity Collection, I have not stumbled across the method to perform this type of query.

I have figured out how to create a column from the metadata…

Dim esCol As esColumnMetadata = _Record.es.Meta.Columns.FindByColumnName(cboField1.Text)

Then I tried the following…

Job1matchCollection1.Query.Where(esCol.Name = txtSearchField1.Text)
Job1matchCollection1.Query.Load()

but the SQL query is…

SELECT * FROM [JOB1MATCH] WHERE ()

so it appears the the WHERE clause is not getting set. Am I going down the right or wrong path? I am ultimately going to bind the collection to a DataGridView in a WinForm application.sColumnMetadata = _Record.es.Meta.Columns.FindByColumnName(cboField1.Text)

ANSWER:

Here’s an example (we are using the literal SQL approach). That is, anything between “” is passed literally straight through to SQL.

string columnName = "EmployeeId";
esColumnMetadata col = coll.es.Meta.Columns.FindByColumnName(columnName);
 
int id = 1;
EmployeesQuery q = new EmployeesQuery();
q.Where("<" + col.Name + " = " + id.ToString() + ">");
 
// Just to see the SQL (testing only)
string sql = q.Parse();
 
EmployeesCollection eColl = new EmployeesCollection();
eColl.Load(q);

This generates the following SQL:

SELECT * FROM [Employees] WHERE (EmployeeID = 1)
Advertisements
Categories: EntitySpaces Tags: