returning a valid dataset using asp.net c#

Question

I keep getting an error when I select a value from my drop down.

**The SelectCommand property has not been initialized before calling 'Fill'.**

It looks like my dataset is returning as empty.

I want to stick to the 3 tier structure.

How do i return a valid dataset using my code?

DAL

public static DataTable GetCustomer(collection b)
{
    {
        DataTable table;
        try
        {
            string returnValue = string.Empty;
            DB = Connect();
            DBCommand = connection.Procedure("getCustomer");
            DB.AddInParameter(DBCommand, "@CustomerRef", DbType.String, b.CustomerRef1);

            DbDataReader reader = DBCommand.ExecuteReader();
            table = new DataTable();
            table.Load(reader);
            return table;
        }
        catch (Exception ex)
        {
            throw (ex);
        }

    }

}

BLL

Looks like I have some redundant code below. I would like to utilize my connection class below:

   public DataSet returnCustomer(collection b)
   {
       try
       {
           SqlDataAdapter adapt = new SqlDataAdapter();
           DataSet table = new DataSet();

           adapt.Fill(table, "table");
           return table;
       }
       catch (Exception ex)
       {
           throw ex;
       }
   }

Connection Class

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.ObjectBuilder;
using System.Data.Common;
using System.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class connection
    {
        const string StrConnection = "CustomerHelperConnectionString";
        internal static Database DB;
        public static DbCommand DBCommand;
        public static Database Connect()
        {

            try
            {
                DB = DatabaseFactory.CreateDatabase(StrConnection);
                return DB;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static DbCommand Procedure(string procedure)
        {

            try
            {
                DBCommand = DB.GetStoredProcCommand(procedure);
                return DBCommand;
            }
            catch (Exception ex)
            {
                throw (ex);            
            }
        }
    }
}

PL

protected void ddl_Customers_SelectedIndexChanged(object sender, EventArgs e)
{

    DAL.collection cobj = new collection();
    BLL.business bobj = new business();

    string selectedValue = ddl_Customers.SelectedValue.ToString();

        //populate the text boxes
        txtCustomerRef.Text = bobj.returnCustomer(cobj).Tables[0].Rows[0][0].ToString();
}

Answer 1

Change your DAL code like this:

public static DataTable GetCustomer(collection b)
{
    {
        DataTable table = new DataTable();
        try
        {
            string returnValue = string.Empty;
            DB = Connect();
            DBCommand = connection.Procedure("getCustomer");
            DB.AddInParameter(DBCommand, "@CustomerRef", DbType.String, b.CustomerRef1);

            SqlDataAdapter adptr = new SqlDataAdapter(DBCommand);
            adptr.Fill(table);
            return table;
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }
}

and now your BAL like this,

public DataSet returnCustomer(collection b)
   {
       try
       {
           DataSet _ds = new DataSet();
           _ds.Tables.Add(DAL.GetCustomer(b));
           return _ds;
       }
       catch (Exception ex)
       {
           throw ex;
       }
   }

Answer 2

As per my assumption mentioned in comments to OP's question, you need to following.

Change DAL to have this public static DataTable GetCustomer(string customer_ref) and use this DB.AddInParameter(DBCommand, "@CustomerRef", DbType.String, customer_ref);

I see there is no work done in BAL, so I am skipping usage of that.

Instead of using BLL object in bobj use one of DAL instance as this has GetCustomer and has some work to fetch info from DB.

So assuming bobj is an instance of DAL, next, change PL, like this - txtCustomerRef.Text = bobj.GetCustomer(selectedValue).Tables[0].Rows[0][0].ToString();