Data Access Application Block in 3.0 - Need Some Help

Topics: Data Access Application Block
Apr 24, 2007 at 3:36 PM
I need some help - I'm new to .Net and am trying to use the newest Data Access Application Blocks (Ent. Lib. 3.0) in my first project. I'm not using Application Block configuration - one can supposedly define the database object directly in code using a config string (comes from the app config file in this case) and that's what I'm trying to do.

I have the following in my Data Access Layer module:

References:
Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Data.dll

using DAAB = Microsoft.Practices.EnterpriseLibrary.Data;
using DAABSQL = Microsoft.Practices.EnterpriseLibrary.Data.Sql;


dbConnString = ConfigurationManager.AppSettings"ConnectionString";
DAAB.Sql.SqlDatabase VaultReconDB = new DAAB.Sql.SqlDatabase(dbConnString);

I'm trying to return a data reader using a stored proc, but Intellisense does not list DbCommand (which is shown as the command object type in the Help info) to allow me to specify my stored-proc name. This makes me think I'm doing something invalid - I've tried qualifying my command-setup statement every way I can see through Intellisense.

I'm trying to get to something like this (as shown in the DAAB documentation for Ent. Lib. 3.0):
DbCommand SPCommand = db.GetStoredProcCommand("GetMarketList");

Can someone let me know what I may be doing wrong? Thanks in advance!!
Apr 24, 2007 at 3:56 PM
I think it's because I didn't have the following: "using System.Data.Common;".
Apr 24, 2007 at 3:57 PM
The code would look more like this:

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
 
 
 
string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
Database VaultReconDB = new SqlDatabase(connectionString);
DbCommand command = VaultReconDB.GetStoredProcCommand("GetMarketList");
using (IDataReader dr = VaultReconDB.ExecuteReader(command))
{
    // Do Something
}

Don't forget to add ObjectBuilder as a Reference, too:

Microsoft.Practices.ObjectBuilder

Regards,

Dave

__________________________

David Hayden
Microsoft MVP C#
Apr 24, 2007 at 5:00 PM
Edited Apr 24, 2007 at 7:12 PM
Thanks, David!

I do already have a reference to ObjectBuilder - I didn't include it above as I didn't realise it was relevant. Your comment helped me to understand it's role a little better.

Your example raises some more questions...

Before I discovered the application blocks, I was trying to create a data layer that looked something like:
//Data Module:
using System.Data;
using System.Data.SqlClient;
//other usings
    public class VaultData
    {
        public VaultData()
        {
            // Constructor logic
            dbConnString = ConfigurationManager.AppSettings["ConnectionString"];
            vaultConnection = new SqlConnection(dbConnString);
            vaultConnection.Open();
        }
        public SqlDataReader GetMarkets()
        {
            SqlDataReader marketReader;
            string storedProcNm = "Get_Market_List";
            sqlCmd = new SqlCommand(storedProcNm,vaultConnection);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            marketReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
            return marketReader;
        }
 
 
        public SqlDataReader GetMarketBranches(int Mkt)
        {
         //similar to GetMarkets()
        }
        //other data-access/manipulation methods
    } //end VaultData class
 
//Code-behind
protected void Page_Load(object sender, EventArgs e)
    {
        // On new load, connect to db and populate the Market list
        if (!Page.IsPostBack)
        {
          dbConn = new VaultData();
          uxMarketList.DataSource = dbConn.GetMarkets();
          //bind reader to gridview & set selected index, etc.
          if (Session["RtnFrmEditPage"] != null)
          {
              if (Convert.ToBoolean(Session["RtnFrmEditPage"]) == true)
              {
                  Session["RtnFrmEditPage"] = false;
                  uxMarketList.SelectedIndex = Convert.ToInt32(Session["MktIndex"]);
                  if (dbConn == null)
                  {
                      dbConn = new VaultData();
                  }
                  uxBranchList.DataSource = dbConn.GetBranches(Convert.ToInt32(uxMarketList.SelectedItem.Value));
                  //bind to branch gridview, set selected index, etc              
                  uxRangeBegDt.Text = Session["RangeBegDt"].ToString();
                  uxRangeEndDt.Text = Session["RangeEndDt"].ToString();
                  FillMainGrid();
              }
              else
                  uxMarketList.SelectedIndex = 0;
          }
       //   dbConn.CloseDisposeConn();
       //   dbConn.vaultConnection = null;        }
    }
The data-access methods are called by the business layer, which currently handles the connection management, as it has to decide whether to do only GetMarkets() or GetMarkets and GetMarketBranches(int Mkt) based on conditions (initial load, coming back from edit/details page, etc.)

This is a totally new paradigm for me and I'm probably missing something, but by your example above, it looks like the database object needs to be created every time something is done. Using your model, I don't understand how I can keep my data access/update code in a separate layer/module and still call the methods for each "task" from the business layer of each page (main page, edit/detail page). Would I basically have to do away with my data-access layer?

I feel like Vinnie Barbarino from "Kotter" - "I'm so confused!!!" Thanks for your help and patience!
Apr 25, 2007 at 3:33 PM
You can still have a VaultData Class that does the DataAccess. It might look something like this:

public class VaultData
{
    private readonly string _connectionString;
	
    public VaultData() : this(ConfigurationManager.AppSettings["ConnectionString"])
    {
    }
	
   public VaultData(string connectionString)
   {
	_connectionString = connectionString;
   }
	
    public IDataReader GetMarkets()
    {
	Database database = new SqlDatabase(_connectionString);
	return database.ExecuteReader("Get_Market_List");
    }
 
    public SqlDataReader GetMarketBranches(int Mkt)
    {
         //similar to GetMarkets()
    }
}

and then in your code just call it like you have been:

VaultData data = new VaultData();
 
using (IDataReader dr = data.GetMarkets())
{
        uxMarketList.DataSource = dr;
        // ...
}

You may want to return a disconnected DataTable or DataSet instead of a DataReader and can certainly change the code in other ways, but the concept is the same. You can use the DAAB from within a Data Layer Class which is a good practice. Note I didn't compile this code or check it for accuracy. I just quickly put it together as a concept.

For simple data access needs, I also recommend looking at TableAdapters. A whole bunch of data access tutorials at:

http://www.asp.net/learn/dataaccess/default.aspx?tabid=63

Hope this helps,

Dave

_________________________________

David Hayden
Microsoft MVP C#