Enterprise Library 6.0 Data Access issue with GetStoredProcCommand

Question

I am trying to build data access layer with Enterprise Library 6.0 Data block. below is my config file

<configuration>  
  <configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true"/>
  </configSections>
  <dataConfiguration defaultDatabase="db"/>
  <connectionStrings>
      <add name="db" connectionString="..." providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

and below is my class file

public class MasterRepo
{
    Database db = null;
    static MasterRepo()
    {
        DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory());
    }

    public MasterRepo()
    {
        db = DatabaseFactory.CreateDatabase("db");
    }

    public int Save(int param1, int param2)
    {
        using (DbCommand cmd = db.GetStoredProcCommand("Save", param1, param2))
        {
            return cmd.ExecuteNonQuery();
        }
    }
}

but we are getting error "ExecuteNonQuery: Connection property has not been initialized." at cmd.ExecuteNonQuery() if we replace cmd.ExecuteNonQuery() with db.ExecuteNonQuery(cmd) then it works.

so why it is failing in first place and what is good way of handling this?

it fails because you haven't initialized the connection property :-) coonectionstring, open connection, do work and close it again. — Legends, Apr 22 '15 at 22:28

granadaCoder · Answer 1 · 2015-04-24T17:00:02.897

At the least, put a check in so you know your db got initialized.

public int Save(int param1, int param2)
{
    if(null!=db)
    {
        using (DbCommand cmd = db.GetStoredProcCommand("Save", param1, param2))
        {
            return cmd.ExecuteNonQuery();
        }
    }
    else
    {
        throw new NullReferenceException("Database (db) was null.  This was not expected.");
    }
}

I typically do this:

public abstract class DataLayerBase
{
    private string _instanceName = string.Empty;

    public DataLayerBase()
    {
    }

    public DataLayerBase(string instanceName)
    {
        this._instanceName = instanceName;
    }

    protected Database GetDatabase()
    {
        Database returnDb = null;
        if (this._instanceName.Length > 0)
        {
            returnDb = DatabaseFactory.CreateDatabase(this._instanceName);
        }
        else
        {
            returnDb = DatabaseFactory.CreateDatabase();
        }
        return returnDb;
    }
}



public class ZebraData : DataLayerBase
{
    //Procedures
    private readonly string PROC_ZEBRA_GET_ALL = "[dbo].[uspZebraGetAll]";

    public ZebraData() : base()
    {
    }
    public ZebraData(string instanceName) : base(instanceName)
    {
    }

    public IDataReader ZebraGetAll()
    {
        IDataReader idr = null;
        try
        {
            Database db = base.GetDatabase();
            DbCommand dbc = db.GetStoredProcCommand(this.PROC_ZEBRA_GET_ALL);
            idr = db.ExecuteReader (dbc);
            return idr;
        }
        finally
        {
        } 
    }

}

I would get a "fresh" Database on each call. After use, allow it to fall out of scope and garbage collected.

Enterprise Library 6.0 Data Access issue with GetStoredProcCommand

1 Answers1