EziData Solutions

Web, NET and SQL Server

N-Tier Architecture using Datasets

In our posts on using Datasets, Using Strongly Typed Datasets and Adding Additional Queries to a Dataset we outlined a simple 2-tier architecture. This may be suitable for many applications, however it is common to move larger and more complicated applications to an N-tier architecture. To achieve this we can add Business Logic Layer in between our application (the actual aspx pages) and the Data Access Layer containing our Datasets.

To seperate out layers in the web site, create a new folder called BLL under the App_Code folder. The image below shows the structure of the App_Code folder, with the documents that will be created in this post.

In the BLL folder add a new Class called CustomerData

We now need to add a reference to our DAL Customers dataset and implement a few methods to make the data available for use. The code snippet is shown below.

    public class CustomerData
    {
        public CustomerData()
        {
        }
 
        public Customers.CustomersDataTable GetAllCustomers()
        {
            CustomersTableAdapter adp = new CustomersTableAdapter();
            return adp.GetData();
        }
 
        public Customers.CustomersRow GetCustomerByCustomerID(string ID)
        {
            CustomersTableAdapter adp = new CustomersTableAdapter();
            return GetFirstRow(adp.GetCustomersByID(ID));
        }
 
        private Customers.CustomersRow GetFirstRow(Customers.CustomersDataTable table)
        {
            Customers.CustomersRow row;
            row = table.Rows[0] as Customers.CustomersRow;
            return row;
        }
    }

Basically we want to create methods in our BLL class that provide access to each method in the DAL dataset. So in our case we create firstly a method that returns all customers, calling the GetData() method. Secondly, we create a method that returns a single row based on the CustomerID and using the GetCustomersByID method of the Customers dataset. A helper method called GetFirstRow is used to grab just the first row from the DataTable that is returned my the GetCustomersByID method.

Once the CustomerData class has been built, it is a relatively simple task to bind the GridView on out CustomerList.aspx page to the class. Choose Configure Data Source from the ObjectDataSource and select Sample.BusinessLogicLayer.CustomerData from the dropdown list, and click next. In the Choose a method dropdown list, select the appropriate method, in this case GetCustomerByCustomerID.

The Parameters remain unchanged so you can click Next and then Finish. Run the page to see the results.

Posted: Nov 06 2009, 23:03 by Admin | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: ASP.NET | C#

Adding Additional Queries to a Dataset

In our first post on 2-tier architecure Using Strongly Typed Datasets, we created a very simple dataset based on the Customers table in the Northwind database. We are now going to expand upon this simple dataset and add the abilty to return records based on parameters, such as CustomerID.

Opne the Customers.xsd Dataset we created in the previous post and right mouse click on the CustomersTableAdapter heading to open the popup menu. Select Add Query to launsh the TableAdapter Query Configuration Wizard.

  1. Choose Use SQL statements
  2. Choose the SELECT with returns rows option
  3. Type the following SQL statement and click next

SELECT CustomerID, CompanyName, Address, City, PostalCode
FROM Customers
WHERE CustomerID=@ID

Unselect the Fill a DataTable option and rename the Method GetCustomersByID, click next and finish.

 

Build the web site to cofirm it compiles and then open the CustomerList.aspx file we created in the first post. To utilise our new query that expects a CustomerID, we need to add some extra fields to the page. Add a textbox called txtCustomerID and a button called btnSearch.

Next we need to bind the GridView to the new datatable, so select Configure Data Source from the ObjectDataSource Tasks popup. click through until you get the the Define Data Methods page of the wizard. In the Choose a Method dropdown list you will see that we now have two methods available. Select the GetCustmersByID() method.

 

Next we need to define where the data for the parameter @ID will come from. In the Define Parameters screen there are a number of source, including QueryString and Session objects, as well as an option to select a control. We will be getting our information from the textbox called txtCustomerID, so go ahead and define the parameter as shown below. Although it's not manditory, it will be easier to assign a default customerID so that when the page loads the GridView will display one record. If we did not want to select a default value, we would have to handle binding via code, so that we did not attempt to bind the GridView to an empty datatable.

Run the page and check the results. You should see the GridView populated with the record for customer Alfreds Futterkiste.

 

If you enter a different CustomerID, for instance BOTTM and click the button, the page will pstback and display the record for Bottom-Dollar Markets.

Obviously this is a very limited search option, as we need to know the exact customerID to search for. With a little more tweaking, it would be easy to add wildcards to our query or to even add the ability to search on more than one field, so have fun.

Posted: Nov 06 2009, 22:31 by Admin | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: C# | ASP.NET | C#

Using Strongly Typed Datasets

Creating and using a strongly-typed Dataset in ASP.NET provides an effective 2-tier architecture. Using the Northwind SQL Server database, in this post we will create a simple Dataset for use with some data-bound controls on an ASP.NET webpage.

To get started, add a folder called DAL, short for Data Access Layer, under the App_Code folder in a standard ASP.NET Web Site. In this folder, create a new Dataset called Customers.

Visual Studio will create the Dataset and launch the TableAdapter Configuration Wizard to guide you through the process of setting up the Dataset.

  1. Select an existing Connection, or if you haven't got a connection to Northwind already saed, hit the New Connection button.
  2. Choose the Use SQL statements option
  3. Enter the following SQL statement, or click the Query Builder button to visually create a simple SELECT query.
SELECT CustomerID, CompanyName, Address, City, PostalCode
FROM Customers

Visual Studio creates the Dataset complete with a TableAdapter. Build your web site, to make sure it compiles.

To show how easy it is to use this Dataset, create a new blank page in your ASP.NET web site called CustomerList and drag in a GridView control from the Toolbox. From the GridView Tasks popup, select <New Data Source> from the Choose Data Source drop-down list. The Data Source Configuration Wizard will open prompting you to select the source of your data. Normally you probably selected Database to connect to a SQL Server database, but to use our new Customers Dataset we need to select Object.

From the list of objects, select the CustomerTableAdapter.

Next, the wizard will display all the available methods that return DataTables. In our case there is only one, called GetData.

That's all there is to do. Our GridView is now bound to the DataSet. Fire up the webpage and see the results.

Posted: Nov 06 2009, 21:30 by Admin | Comments (0) RSS comment feed |
  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
Filed under: ASP.NET | C#