Hi its been 4 days and ive found a way to load different criteria easliy see below this is vaild for root and collection objects.
[Serializable()]
internal sealed class Criteria
{
private SqlCommand _cmd = null;
public Criteria(SqlCommand cmd) { _cmd = cmd; }
public SqlCommand CurrentCommand
{
get { return _cmd; }
set { _cmd = value; }
}
}
[Serializable()]
public sealed class Message : BusinessBase<Message>
{
#region Factory Methods
public static Message LoadInstance(Guid id)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "message_get";
cmd.Parameters.AddWithValue("@id", id);
Criteria criteria = new Criteria(cmd);
return DataPortal.Fetch<Message>(criteria);
}
public static void LoadbyName(string name)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "message_get_by_name";
cmd.Parameters.AddWithValue("@name", name);
Criteria criteria = new Criteria(cmd);
return DataPortal.Fetch<Message>(criteria);
}
public static void DeleteInstance(Guid id)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "message_delete";
cmd.Parameters.AddWithValue("@id", id);
Criteria criteria = new Criteria(cmd);
DataPortal.Delete(criteria);
}
#endregion
#region Data Access
private void DataPortal_Delete(Criteria criteria)
{
using (SqlConnection cn = new SqlConnection(Database.MessageDispatcherConnection))
{
cn.Open();
// By the time we get here we have all our paramters packaged with values :-)
using (criteria.CurrentCommand.Connection = cn)
{
criteria.CurrentCommand.CommandType = CommandType.StoredProcedure;
criteria.CurrentCommand.ExecuteNonQuery();
}
}
}
private void DataPortal_Fetch(Criteria criteria)
{
using (SqlConnection cn = new SqlConnection(Database.MessageDispatcherConnection))
{
cn.Open();
// By the time we get here we have all our paramters packaged with values :-)
using (criteria.CurrentCommand.Connection = cn)
{
criteria.CurrentCommand.CommandType = CommandType.StoredProcedure;
using (SafeDataReader dr = new SafeDataReader(criteria.CurrentCommand.ExecuteReader()))
{
dr.Read();
DoFetch(dr);
}
}
}
}
#endregion
}
For a collection the same applies NOTE i removed the criteria class into a centeral place as both classes uses the same behaviour.
[Serializable()]
public sealed class MessageCollection : BusinessListBase<MessageCollection, Message>
{
/// <summary>
/// Loads all messages.
/// </summary>
/// <returns></returns>
public static MessageCollection LoadAll()
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "message_get_all";
Criteria criteria = new Criteria(cmd);
return DataPortal.Fetch<MessageCollection>(criteria); ;
}
/// <summary>
/// Loads a message collection by a message type.
/// </summary>
/// <returns></returns>
public static MessageCollection LoadByMessageType(string type)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "message_by_by_type";
cmd.Parameters.AddWithValue("@somevalue", type);
Criteria criteria = new Criteria(cmd);
return DataPortal.Fetch<MessageCollection>(criteria); ;
}
private MessageCollection()
{
/* require use of factory methods */
}
private void DataPortal_Fetch(Criteria criteria)
{
RaiseListChangedEvents = false;
using (SqlConnection cn = new SqlConnection(Database.MessageDispatcherConnection))
{
cn.Open();
// criteria.CurrentCommand carries all the info we need to execute the command.
using (criteria.CurrentCommand.Connection = cn)
{
criteria.CurrentCommand.CommandType = CommandType.StoredProcedure;
using (SafeDataReader dr = new SafeDataReader(criteria.CurrentCommand.ExecuteReader()))
{
while (dr.Read())
{
this.Add(Message.LoadChildInstance(dr));
}
}
}
}
RaiseListChangedEvents = true;
}
}
Done !! This works like a charm well there might me room for more improvement let me know wot u guys think,
:-)
P.S Thanks in advance for any comments.
I don't believe that SqlCommand is serializable, so this won't work with a remote data portal.
Also, from an architectural perspective, you are allowing data access details (like the use of SQL Server) to creep out of the DataPortal_XYZ methods and into other parts of your code. This reduces maintainability.
However, if you are willing to accept those costs, and the technique is working for your purposes, then you are all set ![Smile [:)]](/emoticons/emotion-1.gif)
One other thought.
Your Criteria class is totally reusable at this point, so it wouldn't need to be a nested class. You could make it available to all your business objects by having it inherit from CriteriaBase. This would mean you wouldn't need to code Criteria in every business class.
Again, I have serious reservations about the limitations of what you are doing, but if you are going to do it, you might as well go all the way.
We actually had to do something similar in a few business objects just because the way that data could be pulled from the database into the business object really limited us to needing different stored procedures.
We created a base filter criteria class that has the following structure...
[
Serializable] public class xxxxxxxCriteria{
protected string storedProcedureName; public xxxxxxxxCriteria(){
storedProcedureName =
"xxxxxx";}
public virtual void AddParameters(SqlCommand cm){
}
public void ApplyFilterCriteria(SqlCommand cm){
cm.CommandType =
CommandType.StoredProcedure;cm.CommandText = storedProcedureName;
AddParameters(cm);
}
}
Then, for all inheriting classes, the constructor changes the name of the stored procedure (if needed) and overrides the "AddParameters" method. Each factory method can now call its own FilterCriteria class - which can be serialized - and whenever the Fetch methods are being called, we just had to add in the ApplyFilterCriteria method call like this:
private
void ExecuteFetch(SqlConnection cn, xxxxxxxxFilterCriteria criteria){
using (SqlCommand cm = cn.CreateCommand()){
criteria.ApplyFilterCriteria(cm);
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader())){
............. }}
}
I am not sure how this fits in with the way you are trying to model custom filter criteria, but it has worked pretty well for us in the few cases that we needed multiple stored procedures. I thought it was a really clean approach just because the Factory Methods dictate where the data is being pulled from rather than writing some smelly code within the Fetch methods.
Ive managed to try a slightly better solution see below.
Create a Base Criteria class that uses a generic IDCommand Interface :
[Serializable()]
internal class CriteriaBase
{
private IDbCommand _cmd = null;
public CriteriaBase() {}
public CriteriaBase( IDbCommand cmd) {_cmd = cmd}
public virual CurrentDbCommand
{
get {return cmd ;}
set {cmd = value;}
}
}
[Serializable()]
public class Message : BusinessBase<Message>
{
public static Message LoadInstance(Guid id)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "message_get";
cmd.Parameters.AddWithValue("@id", id);
Criteria criteria = new Criteria(cmd);
return DataPortal.Fetch<Message>(criteria);
}
[Serializable()]
private class Criteria : CriteriaBase
{
// Note here we can pass a concrete provider , SQL,MSSQL ECT....
public Criteria() : base(SqlCommand cmd) {}
}
private void DataPortal_Fetch(Criteria criteria)
{
using (SqlConnection cn = new SqlConnection(Database.MessageDispatcherConnection))
{
cn.Open();
// By the time we get here we have all our paramters packaged with values :-)
using (criteria.CurrentCommand.Connection = cn)
{
criteria.CurrentDbCommand.CommandType = CommandType.StoredProcedure;
using (SafeDataReader dr = new SafeDataReader(criteria.CurrentCommand.ExecuteReader()))
{
dr.Read();
DoFetch(dr);
}
}
}
}
}
Again this work extremly well for me only now i can hit different dbs ;-)
Thank you for any futher comments or suggestions
Be careful when using this method, since it causes the query to not use the indexes.
mr_lasseter:
Be careful when using this method, since it causes the query to not use the indexes.
As far as I know, injection attacks are possible if you use string concat for the parameter values. But not if you use actual parameters:
sqlStr = sqlStr & " AND columnName=@columnValue"
and then add the value to the SqlCommand:
cm.Parameters.AddWithValue("@columnValue", criteria.ColumnValue)
This is a best-of-both-worlds approach, because parameters to SqlCommand are automatically escaped as appropriate to make sure they are valid parameters within the SQL query itself. Thus the injection problem is avoided, but you still get to do dynamic SQL creation.
I'm still a fan of Erland Sommarskog's method for doing dynamic SQL inside a stored procedure.
http://www.sommarskog.se/dyn-search.html
I guess it amounts to the much the same thing as doing dynamic SQL in a .NET string but even so, it's kind of nice to bury the ugly stuff and then drive it like any other sproc. I've had some sprocs start life as a standard sproc and later, when I wanted a more flexible, efficient search, changed it to dynamic and add a few more properties to my criteria object.
Ross
Copyright (c) Marimer LLC