How to update a poco with Enterprise-Library 5.0

Question

I would like to pass poco properties to an stored procedure (update and add the Object) With earlier versions of Enterprise Library (e.g. v2.0) I can do something like this:

var arrParam = SqlHelperParameterCache.GetSpParameterSet(ConnectionString(), 
                   SprocNameSet);

for (int x = 0; x <= arrParam.Length - 1; x++)
{           
    System.Reflection.PropertyInfo pi = 
        dataObject.GetType()
        .GetProperty(arrParam[x].ParameterName
          .Substring(1, Convert.ToInt32(arrParam[x].ParameterName.Length) - 1));        
    arrParam[x].Value = pi.GetValue(myDataObject, null);
}

SqlHelper.ExecuteScalar(ConnectionString(), 
    CommandType.StoredProcedure, 
    SprocNameSet, 
    arrParam);

But with Version 5.0 (maybe earlier?) the SqlHelperParameterCache.GetSpParameterSet method is gone.

The question is: how can I get the stored-proc-Parameters and fill these with the poco-properties-values?

Randy Levy · Answer 1 · 2013-06-12T19:04:14.500

You can do something like this:

Database db = DatabaseFactory.CreateDatabase();

string spName = "MySP";
var parameters = new object[] { "Information", 22 };

int value = (int)db.ExecuteScalar(spName, parameters);

Now, this relies on the parameter order. If you want to use names and auto populate the DbCommand and your database supports parameter discovery (e.g. SQL Server) then you could do something like:

public class MyClass
{
    public string Severity { get; set; }
    public int OtherValue { get; set; } 

}

MyClass myClass = new MyClass() { OtherValue = 1, Severity = "Information" };

Database db = DatabaseFactory.CreateDatabase();

string spName = "MySP";            
DbCommand cmd = db.GetStoredProcCommand(spName);

db.PopulateCommandValues(cmd, myClass); 

int value = (int)db.ExecuteScalar(cmd);

public static class DatabaseExtensions
{
    public static void PopulateCommandValues<T>(this Database db, 
        DbCommand cmd, T poco)
    {
        if (!db.SupportsParemeterDiscovery)
        {
            throw new InvalidOperationException("Database does not support parameter discovery");
        }

        db.DiscoverParameters(cmd);

        foreach (DbParameter parameter in cmd.Parameters)
        {
            if (parameter.Direction != System.Data.ParameterDirection.Output &&
                parameter.Direction != System.Data.ParameterDirection.ReturnValue)
            {
                PropertyInfo pi = poco.GetType().GetProperty(
                    parameter.ParameterName.Substring(1)); // remove @ from parameter

                if (pi != null)
                {
                    parameter.Value = pi.GetValue(poco, null);
                }
            }
        }
    }
}

This assumes that the POCO property names are the same as the stored procedure parameter names.

How to update a poco with Enterprise-Library 5.0

1 Answers1