How to return data from the database using 3 tier architecture structure using ASP.NET C#

Question

Let's say I have a 3-tier ASP.NET application written in C#. How are you supposed to utilize the DAL, BLL and PL correctly?

For example let's say I have a stored procedure which requires a parameter of customer ID to be passed in before it returns results. In my data access layer I could have the following:

public DataTable GetCustomerInfo(collection b)
{
    DataTable table;

    try
    {
        string returnValue = string.Empty;
        DB = Connect();
        DBCommand = connection.Procedure("sp_getCust");
        DB.AddInParameter(DBCommand, "@CustomerID", DbType.String, b.CustomerID);

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

Then in my BLL, I would then get that returned table and populate a dataset?

I have tried to populate a dataset without my DataTable called, "table"

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

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

However I get these error:

Error returning datatable to dataset.

Also: how to bind the dataset so that I can return the data to my textboxes?

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

DataSet has a tables collection - you need to return just the first Table from the DataSet:

var dataSet = new DataSet();
adapt.Fill(dataSet, "table");
return dataSet.Tables["table"];

Also, don't do this, as it destroys the stacktrace:

catch (Exception ex)
{
     throw (ex);
}

If you aren't going to do any exception handling, then drop the try / catch entirely. If you are going to do handling and then re-raise, then either just throw, or wrap and throw (e.g. throw new SomeException("Wrapped", ex);)

Finally, note that many of the objects in your DAL are IDisposable - DataReaders, SqlConnection and SqlCommand should all be disposed - I would recommend wrapping the call in a using scope.

edited May 23 '17 at 11:43

Community

1
1

answered Apr 05 '15 at 09:04

StuartLC

104,537
17
209
285

Re: How to bind a DataTable to an Asp.Net UI - you haven't mentioned whether WebForms or MVC, but [here's a basic example](http://stackoverflow.com/a/29428473/314291) in WebForms – StuartLC Apr 05 '15 at 09:11
I thought the whole point of DAL, BLL, PL was so that you didnt need to fire SQL from the code behind in the PL... separation... ! – PriceCheaperton Apr 05 '15 at 09:56
1

Yes, agreed that we don't want Sql leaking out of the Data layer. Typically, folk have moved away from weak schemas like `DataTable` (and typed DataSets) and toward returning entity POCOs from the data / repository, e.g. via ORM's like Entity Framework and Linq2Sql. Also, you might also look at [Onion layering your architecture](http://jeffreypalermo.com/blog/the-onion-architecture-part-1/) instead of "N tier" - e.g. it is often difficult to justify a "Business Tier" for Fetch and Query activities. Onion is also a natural side effect of decoupling your layers through Dependency Injection. – StuartLC Apr 06 '15 at 09:15

score 0 · Answer 2 · answered Apr 05 '15 at 09:12

I have implementing BL (Business Layer) and DAL (Data Access Layer) in one class.

For example, I have one table call "Clarity_Master" in my database. So, I have add one class in visual studio name as "Clarity_BLL.cs"

Clarity_BLL.cs

namespace DAL
{
  public class Clarity_BLL
  {
    public int PURITY_ID { get; set; }
    public string PURITY_NAME { get; set; }
    public string PURITY_CODE { get; set; }
    public int DISPLAY_ORDER { get; set; }
    public bool IDELETE { get; set; }

    public DataTable GET_CLARITYBYNAME()
    {
        ExceptionManager exManager;
        exManager = EnterpriseLibraryContainer.Current.GetInstance<ExceptionManager>();
        DataTable dt = null;
        try
        {
            exManager.Process(() =>
            {
                Database sqlDatabase = DBConnection.Connect();
                DataSet ds = sqlDatabase.ExecuteDataSet("StoreProcedureName",PURITY_NAME_Para1, IDELETE_Para2);
                dt = ds.Tables[0];
            }, "Policy");
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return dt;
    }
  }
 }

And use this BL & DAL classes like below in my PL (Presentation Layer).

Clarity_MST.aspx.cs

public void bindgrid()
    {
        Clarity_BLL obj_CLARITY_BLL = new Clarity_BLL();
        obj_CLARITY_BLL.PURITY_ID = 0;
        obj_CLARITY_BLL.IDELETE = true;
        grdClarity.DataSource = obj_CLARITY_BLL.GET_CLARITYBYNAME();
        grdClarity.DataBind();
    }

Please let me know if you have any questions.

How to return data from the database using 3 tier architecture structure using ASP.NET C#

2 Answers2