0

I have some problem with writing a Method in the template pattern style. But first my code:

My Base class I am using looks like this:

public abstract class DbBase<T> where T : new()
{
    protected abstract Value CommandValue { get; }
    protected abstract CommandType CommandType { get; }
    protected abstract Mapper<T> GetMapper();
    protected abstract IDbConnection GetConnection();
    protected abstract Collection<IDataParameter> GetParameters(IDbCommand command);

    public Collection<IDataParameter> Paramaters { get; set; }

    #region Public Methods
    public T Single(int id)
    {
        return ExecuteReader(id).SingleOrDefault();
    }

    public Collection<T> All()
    {
        return ExecuteReader();
    }
    #endregion

    #region Private Methods
    private Collection<T> ExecuteReader(int? id = null)
    {
        var collection = new Collection<T>();

        using (var connection = GetConnection())
        {
            var command = connection.CreateCommand();
            command.Connection = connection;
            command.CommandType = CommandType;

            if (id.HasValue && id.Value > 0)
                command.CommandText = CommandValue.Single;
            else
                command.CommandText = CommandValue.All;

            var parameters = GetParameters(command);
            if (parameters != null)
            {
                foreach (var param in GetParameters(command))
                    command.Parameters.Add(param);
            }

            try
            {
                connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    try
                    {
                        var mapper = GetMapper();
                        collection = mapper.MapAll(reader);
                        return collection;
                    }
                    finally
                    {
                        if (!reader.IsClosed)
                            reader.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new DbBaseException(ex.Message, ex);
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
            }
        }
    }
    #endregion
}

So now for every piece of code which might get changed I have a details class which is inheriting:

public class UserDb : DbBase<User>
{
    private static readonly string ALL = "SELECT * FROM [USER]"; //don't use star!
    private static readonly string SINGLE = "SELECT * FROM [USER] WHERE USER_ID = @USER_ID";
    private static readonly CommandType commandType = CommandType.Text;

    protected override Value CommandValue
    {
        get
        {
            var value = new Value
            {
                Single = SINGLE,
                All = ALL
            };
            return value;
        }
    }

    protected override CommandType CommandType
    {
        get { return commandType; }
    }

    protected override Mapper<User> GetMapper()
    {
        return new UserMapper();
    }

    protected override Collection<IDataParameter> GetParameters(IDbCommand command)
    {
        var parameters = new Collection<IDataParameter>();
        var param = command.CreateParameter();
        param.ParameterName = "@USER_ID";
        param.Value = 2;
        parameters.Add(param);
        return parameters;
    }
}

Calling Code:

 var userDb = new UserDb();
 var user = userDb.Single(1);

 if (user != null)
     Console.WriteLine(string.Format("{0}, {1}, {2}", user.UserId, user.Username, user.Password));

As you can see I have implemented a method called Single which gives me one specific row by id. My problem is how can I push the id into my ExecuteReader method without breaking the template pattern?

I hope you can help me out guys.

Thx

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
MUG4N
  • 19,377
  • 11
  • 56
  • 83
  • What actualy you mean breaking the template patern? You can rename parameter '@user_id' to '@id' and not override GetParameters method any time – gabba May 12 '12 at 16:39
  • You may need to different name of id field, but name of param can be the same for any cases – gabba May 12 '12 at 16:45

1 Answers1

2

Why are you not using a parameter that has the same name for all your entities like @id. Then you will not need the GetParameters stuff any more. Simply call

command.Parameters.AddWithValue("@id", id);

UPDATE

If you want to be able to use a different number of parameters, you can use the params keyword, which enables you to pass a varying number of parameters (including zero).

public T Single(params int[] id)
{ 
    return ExecuteReader(id).SingleOrDefault();         
}

and

private Collection<T> ExecuteReader(params int[] id)
{
    ...
    for (int i = 0; i < id.Length; i++) {
        command.Parameters.AddWithValue("@id" + i, id[i]);
    }
    ...
}

And you will have to name your parameters @id0, @id1, @id2, ...

var coll = ExecuteReader();
var coll = ExecuteReader(2);
var coll = ExecuteReader(5, 77);
...

var result = db.Single(1);
var result = db.Single(4, 13);
var result = db.Single(5, 100, 1);
...

UPDATE #2

You can also extract parameter names from the SQL text

private Collection<T> ExecuteReader(params object[] p)
{
    ...
    var matches = Regex.Matches(sql, @"@\w+");
    if (matches.Count != p.Length) {
        throw new ArgumentException("The # of parameters does not match ...");
    }
    for (int i = 0; i < p.Length; i++) {
        command.Parameters.AddWithValue(matches[i].Value, p[i]);
    }
    ...
}
Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • what if I am using three different parameters on a SELECT...WHERE statement within an ANY method in the future? I want to declare the parameters in the UserDb class, but have to assign the values for these params. – MUG4N May 12 '12 at 16:44
  • You may need to different name of id field, but name of param can be the same for any cases – gabba May 12 '12 at 16:46
  • what if I am using a method which calls SELECT * FROM USERS WHERE USER_NAME = 'foo' AND AGE = 25 – MUG4N May 12 '12 at 16:48
  • `SELECT * FROM users WHERE user_name = @id0 AND age = @id1`. `var users = dbUser.GetUsersWithNameAndAge("foo", 25)` – Olivier Jacot-Descombes May 12 '12 at 17:00
  • thx Oliver for that interesting post. Looks very promising!The problem is I want to use this class with stored procedures in the future. These stored procedures should not use "@id1", "@id2" and so on because of readability. Do you have any idea how I can set the params including names & values – MUG4N May 12 '12 at 17:02
  • `public T Single(params SqlParameter[] parameters)`. `var result = db.Single(new SqlParameter("@name", "foo"), new SqlParameter("@age", 25));` – Olivier Jacot-Descombes May 12 '12 at 17:09
  • this solution is also not perfect because now I have all parameters in the business layer which is horrible to maintain... – MUG4N May 12 '12 at 17:18
  • Create a class for storing informations belonging to a query `public class QueryInfo { public string Sql { get; set } public string[] ParamNames { get; set; } }` instead of just storing the SQL text alone. Or get the parameter names from the query text by using `Regex`: `var matches = Regex.Matches(sql, @"@\w+");` – Olivier Jacot-Descombes May 12 '12 at 17:29