This is not an example how to use EntitySpaces. Most everything shown below can be done through our DynamicQuery API which is the preferred way. For instance, the GetMaxSalary() example shown in the EmployeesCollection class below could be done via our DynamicQuery like this:
EmployeesQuery query = new EmployeesQuery();
query.Select(query.Salary.Max());
int maxSalary = (int)query.ExecuteScalar();
And the CustomLoad() load method shown in the EmployeesCollection class below could be done via our DynamicQuery like this:
EmployeesCollection coll = new EmployeesCollection();
coll.Query.Where(coll.Query.LastName.Like("g%"));
if(coll.Query.Load())
{
// Then we loaded at least one record
}
So, when you look at the class below remember this is merely to demonstrate to you some of the lower level support functions that you have at your disposal. The idea being, of course, even though you are using an ORM system generated via our ES2009 code generator you are not cut off from the low level ADO.NET API when you need to hug the metal.
public partial class EmployeesCollection : esEmployeesCollection
{
public bool CustomLoad(string partialName)
{
// This populates the EmployeesCollection itself
return this.Load(esQueryType.Text, "SELECT * FROM EMPLOYEES WHERE LastName Like {0}", partialName);
}
public DataSet GetDataSet(int someParameter)
{
// We hate DataSets ;)
return this.FillDataSet(esQueryType.StoredProcedure, "TestProc", someParameter);
}
public DataTable GetDataTable(int someParameter)
{
// We hate DataTables ;)
return this.FillDataTable(esQueryType.StoredProcedure, "TestProc", someParameter);
}
public IDataReader GetReader(string partialName)
{
// We hate DataReaders ;)
return this.ExecuteReader(esQueryType.Text, "SELECT * FROM EMPLOYEES WHERE LastName Like {0}", partialName);
}
public int GetMaxSalary()
{
return (int)this.ExecuteScalar(esQueryType.StoredProcedure, "procGetMaxSalary");
}
}
Of course, we don't really hate DataSets, DataTables, or DataReaders. If you are an EntitySpaces user then you know that using our DynamicQuery API is the way to go. However, if there is something you need that is not available in our DynamicQuery API you have full access to the underlying power of ADO.NET.
There is also a utility class called esUtility that will allow you to access this lower level API without adding custom methods to your EntitySpaces classes. This can be useful when what you need to do doesn't really belong to a particular entity. Here is an example of using the esUtility class.
esUtility util = new esUtility();
IDataReader reader = util.ExecuteReader(esQueryType.Text, "SELECT * FROM EMPLOYEES WHERE LastName Like {0}", "g%");
One thing to note is that you never use decorators on your parameters such as ? or @ or : (depending on your database). The EntitySpaces Data Providers do this for you. For instance, notice in the sample code below that we do not set the parameter name to "@Salary" rather we just use "Salary". This allows you to access stored procedures and still have a database independent application as the EntitySpaces Data Provider will "gussy up" the parameters with the proper decorator. In fact, you really don't have to use the syntax below unless you need to provide extra information such as parameter direction or perhaps the precision or scale of a decimal for some reason. Otherwise you can just use the {0}, {1} syntax as shown above in the GetReader() method. Even when using the {0} syntax EntitySpaces will create a true parameter for you to ensure that no SQL injection attacks are successful.
esParameters parameters = new esParameters();
esParameter param = new esParameter("Salary", null, esParameterDirection.Output);
parameters.Add(param);
esUtility util = new esUtility();
IDataReader reader = util.ExecuteReader(esQueryType.StoredProcedure, "GetMaxSalary", parameters);
While were at it here's another data access tip. This tip allows you to trick your table based collection (via the DynamicQuery) to query against a view. Of course, you can generate entities from a view if you like but if you don't really want the additional classes you can use this technique instead. Notice how we set the QuerySource below.
EmployeesCollection coll = new EmployeesCollection();
coll.Query.es.QuerySource = "MyView"; // <== Select against this view
coll.Query.Select(coll.Query.LastName, coll.Query.FirstName);
coll.Query.Where(coll.Query.Salary > 50000);
coll.Query.Load();
This can be very useful since you can still commit this data back to the Employee table provided that you also brought back the primary key, of course. And remember, these are tricks, not necessarily how to use EntitySpaces. You can easily use our DynamicQuery API to build a join on the fly to that would accomplish the same thing as the view above.
In summary we have worked very hard to expose all of the underlying power of ADO.NET and yet you never have to pull in SqlClient, OracleClient or any other ADO.NET provider. Better still you can invoke stored procedures and not lose database independence if that is what you are after. We hope you have learned a thing or two from this post.
From Mobile Devices to large scale enterprise solutions in need of serious transaction support, EntitySpaces can meet your needs. Whether you’re writing an ASP.NET application with Medium Trust requirements, a Mono application, or a Windows.Forms application, the EntitySpaces architecture is there for you. EntitySpaces is provider independent, which means that you can run the same binary code against any of the supported databases. EntitySpaces is available in both C# and VB.NET. EntitySpaces uses no reflection, no XML files, and sports a tiny foot print of less than 200k. Pound for pound, EntitySpaces is one tough, dependable .NET architecture.
EntitySpaces LLC
Persistence Layer and Business Objects for Microsoft .NET
http://www.entityspaces.net