Friday, April 25, 2008

Generics Examples - Passing paramaters to Stored Procedures

In this post begins a series of examples of application of Generics that were useful to me throughout several developments. In this delivery a generic method that receives an instance of some entity of our domain and a DbCommand, and based on the SqlParameterAttributes attributes “reflected” from the entity maps the value of the properties with the parameters of stored procedure of the received DbCommand. Using this only method and “decorating” with the SqlParameterAttribute attributes the suitable properties of our classes it is possible to simplify the stored procedures calls.

The Method


public static void MatchStoreProceduresParams<T>(T entity, DbCommand command)
{
Type type = entity.GetType();
foreach (PropertyInfo entityProperty in type.GetProperties())
{
SqlParameterAttribute[] propSqlAttributeArray = (SqlParameterAttribute[])entityProperty.GetCustomAttributes(typeof(SqlParameterAttribute), false);
if (propSqlAttributeArray.Length > 0)
{
SqlParameterAttribute propSqlAttribute = propSqlAttributeArray[0];
if (propSqlAttribute != null)
{
SqlParameter sqlParam = new SqlParameter();
sqlParam.ParameterName = propSqlAttribute.Name;
sqlParam.SqlDbType = propSqlAttribute.SqlDbType;
sqlParam.Value = entityProperty.GetValue(entity, null);
if (propSqlAttribute.IsDirectionDefined)
{
sqlParam.Direction = propSqlAttribute.Direction;
}
command.Parameters.Add(sqlParam);
}
}

}
}

Using the method

public static int CustomerSave(CustomerInfo customer)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("CustomerUpdate");
Utils.MatchStoreProceduresParams(customer, dbCommand);
return db.ExecuteNonQuery(dbCommand);
}

Decorando la clase

public class CustomerInfo
{
int _id;
string _name;
int _age;
decimal _balance;

public CustomerInfo()
{

}

[SqlParameter("@CustomerId", System.Data.SqlDbType.Int)]
public int Id
{
get { return _id; }
set { _id = value; }
}

[SqlParameter("@FirstName", System.Data.SqlDbType.Char)]
public string Name
{
get { return _name; }
set { _name = value; }
}

[SqlParameter("@Age", System.Data.SqlDbType.Int)]
public int Age
{
get { return _age; }
set { _age = value; }
}

[SqlParameter("@Balance", System.Data.SqlDbType.Decimal)]
public decimal Balance
{
get { return _balance; }
set { _balance = value; }
}
}

3 comments:

CK said...

Hi,

I tried your way. It gives me an error when I write
[SqlParameter("@UserName", System.Data.SqlDbType.VarChar)]

The error says
System.Data.SqlClient.SqlParameter' is not an attribute class

Can you help me with this please.

Martin Olivares said...

Sorry, I miss include the parameter attributes class. Send me an email to martin.olivares@gmail.com and I can send them to you.

Diego N. Salas said...

Muy bueno me sirvio mucho.