Creating a Data Access Layer in ASP.Net applications for inserting,selecting,deleting and updating data

Sunday, January 23, 2011

In this post I will be continuing my series of posts regarding data access methodologies/technologies that as I call them are pro ORM. I will be demonstrating how to use the connected and the disconnected data access models to retrieve,insert,update,delete data from a database.This is going to be a huge post so embrace yourself.Our database will be an SQL Server database.I am urging you to read this post and this post of mine that are also in this blog.These posts explore the disconnected and connected model and the basic objects(classes) we use to retrieve data.

I will keep using LINQ to SQL and EF as my main data access technologies for my projects. But for anyone to be considered a serious ADO.Net Developer, must understand ADO.Net 2.0 and 1.1.

I have many posts in this blog regarding LINQ to SQL. I will be posting about 20 posts in Entity Framework until the end of this year. So stay tuned.

In this post I will be showing how to select,insert,update,delete data through an access layer. In my other two posts, all the data access code was written in the Presentation layer.

This is not the best way to do things. When the code gets larger and more complicated the code will be less maintainable and less extensible. We must have a separate place to place the data access code.Another layer that will be responsible for accessing the data store.

I am going to demonstrate this with a hands on example. Lets look at the first approach thus using pure t-sql to query the table.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008/2005 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item to your site, a web form. Leave the default name, Default.aspx

3) Add ASP.Net folder in your site, App_Code. Add another folder inside the App_Code special folder and name it DataAccess.

4) Go to View - > Server Explorer (Database Explorer) and add a connection to the Northwind database.You have just to set the instance of the SQL Server and the name of the database.Test the connection.

5) In your web.config (which is pretty much empty) add a <connectionStrings>. Ιn my case it like the one below. In your case you must set the Data Source property to your SQL server instance.

<connectionStrings>
<add name="NorthwindConnectionString"

connectionString="Data Source=FOFO-PC\SQLEXPRESS;Initial Catalog=Northwind;

Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>

6) Add a class file inside the DataAccess folder. Name the file Connection.cs.So we have a Connection class. The code for the whole class is

public class Connection
{
 
public static SqlConnection GetDBConnection()
{
       
string connectionString = ConfigurationManager.

ConnectionStrings["NorthwindConnectionString"].ConnectionString;
 
SqlConnection connection = new SqlConnection(connectionString);
 
       
connection.Open();
return connection;
    }
 
}

This is very simple static method. It returns an SQL connection.Inside this method I do the following.

I get the connection string from the configuration file
I create a new connection object
I open the connection and return the connection object.

Do not forget to add these lines of code in the top of the file.

using System.Data.SqlClient;

using System.Configuration;

7) In the Default.aspx page add a Label control. Leave the default name.

8) Do not forget to add this line of code in the top of the Default.aspx.cs file

using System.Data.SqlClient;

9) In the Page_Load event handling routine of the Default.aspx page type

if (!IsPostBack)
    { 
        Label1.Text = "We will test the connection to the database.";
 
        try 
	{	        
        SqlConnection connection = Connection.GetDBConnection();
        connection.Close();	
        Label1.Text = "We opened and closed the connection to the database";
	}
	catch (SqlException ex)
	{
		
        Label1.Text="We cannot connect to the database" + ex.Message;
	}
    }

10) Run your application and see the result. If you followed everything correctly you would be able to open and close a connection to the database.

The code above is easy to follow. I just get the connection object back from the GetDBConnection function that "lives" inside the Connection class, which is our data access layer.

I just place the code inside a try catch statement and I make sure the code runs the first time the page loads, so the whole code is inside an If statement.

11) Now let's retrieve some data from our database. We will add another file to the DataAccess folder.So add a class file inside that folder and call it DataAccess.cs.

For this example just imagine that we want to connect to the database and get all the prices for each product.

In the DataAccess.cs file we must create a static method.

public static SqlDataReader GetProductPrices()
    {
        string sql = "SELECT ProductName, UnitPrice FROM Products";
 
        SqlConnection connection = Connection.GetDBConnection();
        SqlCommand command = new SqlCommand(sql, connection);
        command.CommandType = CommandType.Text;
 
SqlDataReader reader = command.

ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.CloseConnection);
        return reader;
    }

The code above is very simple.I have a static method that returns a DataReader object.Inside the method

I define the SQL Statement
I get the connection object back from the GetDBConnection function that "lives" inside the Connection class

I create a SqlCommand object by passing as parameters the sql statement and the connection object

I create a DataReader object and return it.I also specify that the query returns a single result set - CommandBehavior.SingleResult

Moreover I specify that the connection object must be closed when the DataReader object closes - CommandBehavior.CloseConnection

Make sure you add these lines of code in the top of the file.

using System.Data.SqlClient;
using System.Data;

12) Add a new web form to your site. Name it GetProductPrices.aspx.Add a BulletedList control to the page.Leave the default name. Add a Label control as well. Leave the default name.

13) In the Page_Load event handling routine of the GetProductPrices.aspx page type

if (!IsPostBack)
     {
            Label1.Text = "We must get all the product prices";
           
       try
       {
          using (SqlDataReader reader = DataAccess.GetProductPrices())
          {
          while (reader.Read())
          {
          string productName = reader.GetString(0);
          decimal unitPrice = reader.GetDecimal(1);
 
          string item = String.Format("{0}, {1:C2}", productName, unitPrice);
          BulletedList1.Items.Add(item);
              }
            }
                Label1.Text = "All product prices are listed.";
        }
            catch (SqlException ex)
            {
 
                Label1.Text = "Cannot get the product price." + ex.Message;
            }
        }

The code above is very easy to follow. I create a DataReader object by calling the GetProductPrices() method from the DataAccess class.

Then I loop through the reader,format some values and add each item pair (product-price) to the bulleted list.

I just place the code inside a try catch statement and I make sure the code runs the first time the page loads, so the whole code is inside an If statement.

Add bookmarks to see how the code runs in debug mode.

Do not forget to add this line of code in the top of the GetProductPrices.aspx.cs file

using System.Data.SqlClient;

14) Run your application and see the products and their prices listed.

15) Now let's add another page to the site. We call it GetAveragePrice.aspx.We want to get the average price of all the products in the database.In the DataAccess.cs file add another static method.

The code for this static method looks like this

 public static decimal GetProductsAveragePrice()
    {
        string sql = "SELECT AVG(UnitPrice) FROM Products";
        object result;
        
 
        using (SqlConnection connection = Connection.GetDBConnection())
        {
            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandType = CommandType.Text;
 
            result = command.ExecuteScalar();
        }
 
        return (decimal)result;
    }

16) The code above is very simple.I have a static method that returns a decimal value.Inside the method

I define the SQL Statement
I get the connection object back from the GetDBConnection function that "lives" inside the Connection class

I create a SqlCommand object by passing as parameters the sql statement and the connection object

I use the ExecuteScalar() method of the command object to return the average price

17) Add a Label control to the GetAveragePrice.aspx page. In the Page_Load event handling routine of the GetAveragePrice.aspx page type

if (!IsPostBack)
 {
   Label1.Text = "We try to get the average price.";
 
  try
{
  decimal averagePrice = DataAccess.GetProductsAveragePrice();
  Label1.Text = string.Format("Average price for all products: {0:C2}",

 averagePrice);
}
  catch (SqlException ex)
  {
               
         Label1.Text = "Cannot get average price." +ex.Message;
  }
}

18) Run your application and see the average price for all products in the database printed out in the screen.

19) Now, we need to update the prices of the products by value that the user specifies. Add another page in your site. Name it UpDateProductPrices.aspx.

Add a Button,Label and a TextBox control to that page. Leave the default names.

20) In the DataAccess.cs file add another static method.

public static int UpdateAllPricesBy(decimal price)
{
string sql = "UPDATE Products SET UnitPrice=UnitPrice + " + price.ToString();
int rowsAffected;
 
  using (SqlConnection connection = Connection.GetDBConnection())
        {
            SqlCommand command = new SqlCommand(sql, connection);
            command.CommandType = CommandType.Text;
 
            rowsAffected = command.ExecuteNonQuery();
        }
 
        return rowsAffected;
    }

21) The code above is very simple.I have a static method that I pass a value that the user will enter. It also returns a integer value.Inside the method

I define the SQL Statement
I get the connection object back from the GetDBConnection function that "lives" inside the Connection class

I create a SqlCommand object by passing as parameters the sql statement and the connection object

I use the ExecuteNonQuery() method of the command object to return the rows affected by the update statement.

22) In the Button1_Click event handling routine of the UpDateProductPrices.aspx page type

 try
        {
            decimal price = decimal.Parse(TextBox1.Text);
            int rowsAffected = DataAccess.UpdateAllPricesBy(price);
            Label1.Text = string.Format("{0} rows affected.", rowsAffected);
        }
        catch (SqlException ex)
        {
     
            Label1.Text = "Cannot update prices." + ex.Message;
        }

 Do not forget to add this line of code in the top of the UpDateProductPrices.aspx.cs file
using System.Data.SqlClient;

23) The  code above is very simple.I just call the UpdateAllPricesBy method and pass it as an input parameter the value the user entered in the textbox. Run your application (with bookmarks) and see the rows affected by the update statement.

24) We move on with our example and let's say we want to delete a product. The user will specify a value (ProducItD) through the interface,click a button and then the product will be deleted. Add another page in your site. Name it DeleteProduct.aspx.Add a Button,Label and a TextBox control to that page. Leave the default names.

25) I am going to use a stored procedure to delete a product. The stored procedure "DeleteProduct" code looks something like this

CREATE PROCEDURE dbo.DeleteProduct
	(
	@ProductID int
	)
AS
	BEGIN TRANSACTION
	
	DELETE FROM [Order Details] WHERE ProductID=@ProductID
	IF @@ERROR <> 0
		ROLLBACK TRANSACTION
	ELSE
	BEGIN
		DELETE FROM Products WHERE ProductID=@ProductID
		IF @@ERROR <> 0
			ROLLBACK TRANSACTION
		ELSE
			COMMIT TRANSACTION
	END
GO

26) In the DataAccess.cs file add another static method and name it DeleteProduct().

public static int DeleteProduct(int productID)
  {
    int rowsAffected = 0;
 
    using (SqlConnection connection = Connection.GetDBConnection())
    {
    SqlCommand command = new SqlCommand("DeleteProduct", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID;
 
    rowsAffected = command.ExecuteNonQuery();
        }
        return rowsAffected;
    }

27) The code above is very simple.I have a static method DeleteProduct() that I pass a value that the user will enter. It also returns a integer value.Inside the method

I get the connection object back from the GetDBConnection function that "lives" inside the Connection class

I create a SqlCommand object by passing as parameters the name of the stored procedure and the connection object

I pass the value entered by the user in the textbox as an input parameter to the stored procedure

I use the ExecuteNonQuery() method of the command object to return the rows affected by the delete statement.

28) In the Button1_Click event handling routine of the DeleteProduct.aspx page type

try
        {
            int productID = int.Parse(TextBox1.Text);
            int rowsAffected = DataAccess.DeleteProduct(productID);
            Label1.Text = string.Format("{0} rows affected.", rowsAffected);
        }
        catch (SqlException ex)
        {
 
            Label1.Text = "Cannot delete product." + ex.Message;
        }

Do not forget to add this line of code in the top of the DeleteProduct.aspx.cs file
using System.Data.SqlClient;


29) Run your application (with bookmarks) and try to delete a row. See the rows affected by the delete statement.

30) We move on with our example and let's say we want to edit our product. Add another page in your site. Name it EditProduct.aspx.Add a GridView control on the page. Leave the default name.

Add an ObjectDataSource control on the page. Leave the default name.Set the DataSourceID property of the GridView control to ObjectDataSource1. When you try to configure your ObjectdDataSource object and select as business object the DataAccess class you realise you must provide methods for Select,Insert,Update,Delete operations.

First we must provide the method for the Select method.Inside the DataAccess.cs file, add another static method GetProducts()

 public static SqlDataReader GetProducts()
    {
        string sql = "SELECT * FROM Products";
 
        SqlConnection connection = Connection.GetDBConnection();
        SqlCommand command = new SqlCommand(sql, connection);
        command.CommandType = CommandType.Text;
 
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult

 | CommandBehavior.CloseConnection);
        return reader;
    }

31) Now, we must write the method to insert products in the database. I will use a stored procedure to do that-InsertProduct. The code for the stored procedure is following

 CREATE PROCEDURE dbo.InsertProduct
	(
	@ProductName nvarchar(40),
	@SupplierID int,
	@CategoryID int,
	@QuantityPerUnit nvarchar(20),
	@UnitPrice money,
	@UnitsInStock smallint, 
	@UnitsOnOrder smallint, 
	@ReorderLevel smallint,
	@Discontinued bit
	)
AS
	INSERT Products 
		(
		ProductName,
		SupplierID,
		CategoryID,
		QuantityPerUnit,
		UnitPrice,
		UnitsInStock, 
		UnitsOnOrder, 
		ReorderLevel,
		Discontinued 
		)
	VALUES
		(
		@ProductName,
		@SupplierID,
		@CategoryID,
		@QuantityPerUnit,
		@UnitPrice,
		@UnitsInStock, 
		@UnitsOnOrder, 
		@ReorderLevel,
		@Discontinued
		)		

	RETURN
GO

32) Inside the DataAccess.cs file, add another static method InsertProduct()

 public static int InsertProduct(
                             int productID,
                             string productName,
                             int supplierID,
                             int categoryID,
                             string quantityPerUnit,
                             decimal unitPrice,
                             int unitsInStock,
                             int unitsOnOrder,
                             int reorderLevel,
                             bool discontinued)
    {
        int rowsAffected = 0;
 
        using (SqlConnection connection = Connection.GetDBConnection())
        {
 SqlCommand command = new SqlCommand("InsertProduct", connection);
 command.CommandType = CommandType.StoredProcedure;
 command.Parameters.Add("@ProductName", SqlDbType.NVarChar, 40).Value =

productName;


 command.Parameters.Add("@SupplierID", SqlDbType.Int).Value = supplierID;
 command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
 command.Parameters.Add("@QuantityPerUnit", SqlDbType.NVarChar, 20).Value =

quantityPerUnit;


 command.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = unitPrice;
 command.Parameters.Add("@UnitsInStock", SqlDbType.SmallInt).Value =

unitsInStock;

 command.Parameters.Add("@UnitsOnOrder", SqlDbType.SmallInt).Value =

unitsOnOrder;

 command.Parameters.Add("@ReorderLevel", SqlDbType.SmallInt).Value =

reorderLevel;

 command.Parameters.Add("@Discontinued", SqlDbType.Bit).Value = discontinued;
 
  rowsAffected = command.ExecuteNonQuery();
        }
        return rowsAffected;
    }

33) The code above is very simple.I have a static method that I pass values as input parameters. It also returns a integer value.Inside the method

I get the connection object back from the GetDBConnection function that "lives" inside the Connection class

I create a SqlCommand object by passing as parameters the name of the stored procedure and the connection object

I pass the input parameter values of the method as input parameters to the stored procedure

I use the ExecuteNonQuery() method of the command object to return the rows affected by the update statement.

34) Now, we must write the method to update products in the database. I will use a stored procedure to do that - UpDateProduct. The code for the stored procedure is following

CREATE PROCEDURE dbo.UpdateProduct
	(
	@ProductID int,
	@ProductName nvarchar(40),
	@SupplierID int,
	@CategoryID int,
	@QuantityPerUnit nvarchar(20),
	@UnitPrice money,
	@UnitsInStock smallint, 
	@UnitsOnOrder smallint, 
	@ReorderLevel smallint,
	@Discontinued bit
	)
AS
	UPDATE Products SET
		ProductName = @ProductName,
		SupplierID = @SupplierID,
		CategoryID = @CategoryID,
		QuantityPerUnit = @QuantityPerUnit,
		UnitPrice = @UnitPrice,
		UnitsInStock = @UnitsInStock, 
		UnitsOnOrder = @UnitsOnOrder, 
		ReorderLevel = @ReorderLevel,
		Discontinued = @Discontinued 
	WHERE 
		ProductID=@ProductID

	RETURN
GO

35) Inside the DataAccess.cs file, add another static method UpdateProduct()

    public static int UpdateProduct(
                             int productID,
                             string productName,
                             int supplierID,
                             int categoryID,
                             string quantityPerUnit,
                             decimal unitPrice,
                             int unitsInStock,
                             int unitsOnOrder,
                             int reorderLevel,
                             bool discontinued)
    {
        int rowsAffected = 0;
 
        using (SqlConnection connection = Connection.GetDBConnection())
        {
            SqlCommand command = new SqlCommand("UpdateProduct", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ProductID", SqlDbType.Int).Value = productID;
command.Parameters.Add("@ProductName", SqlDbType.NVarChar, 40).Value =

 productName;


command.Parameters.Add("@SupplierID", SqlDbType.Int).Value = supplierID;
command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
command.Parameters.Add("@QuantityPerUnit", SqlDbType.NVarChar, 20).Value =

quantityPerUnit;


command.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = unitPrice;
command.Parameters.Add("@UnitsInStock", SqlDbType.SmallInt).Value =

unitsInStock;

command.Parameters.Add("@UnitsOnOrder", SqlDbType.SmallInt).Value =

unitsOnOrder;

command.Parameters.Add("@ReorderLevel", SqlDbType.SmallInt).Value =

reorderLevel;

command.Parameters.Add("@Discontinued", SqlDbType.Bit).Value = discontinued;
 
            rowsAffected = command.ExecuteNonQuery();
        }
        return rowsAffected;
    }

 36) The code above is very simple.I have a static method that I pass values as input parameters. It also returns a integer value.Inside the method

I get the connection object back from the GetDBConnection function that "lives" inside the Connection class

I create a SqlCommand object by passing as parameters the name of the stored procedure and the connection object

I pass the input parameter values of the method as input parameters to the stored procedure

I use the ExecuteNonQuery() method of the command object to return the rows affected by the update statement.

37) Save everything you have implemented so far.Configure the ObjectDataSource object (by following the steps of the wizard) and set the select,insert,update,delete tabs with the appropriate methods.Set the DataKeyNames property to ProductID.Configure the GridView control to enable editing,deleting,selection.

38) Run your application and try to update values from the page.Also try to delete rows from the page.Everything should work fine.

39) We can write some code to intercept exceptions coming out from our database.Add a Label to the page. We will use some of the events of the GridView control.We want to display appropriate messages ot the user when he cancels the "Edit", when the deletion cannot go on and whether the "Update" is successful or not. The actual event and the code inside them follows.

 protected void GridView1_RowCancelingEdit(object sender,

GridViewCancelEditEventArgs e)
    {
        Label1.Text= "Edit cancelled.";
    }
    protected void GridView1_RowDeleted(object sender,

GridViewDeletedEventArgs e)
    {
        if (e.Exception != null)
        {
            Label1.Text = "Cannot delete record";
            e.ExceptionHandled = true;
        }
        else
        {
            Label1.Text = "Record deleted.";
        }
    }
    protected void GridView1_RowUpdated(object sender,

GridViewUpdatedEventArgs e)
    {
        if (e.Exception != null)
        {
            Label1.Text = "Cannot update record.";
            e.ExceptionHandled = true;
        }
        else
        {
            Label1.Text = "Record updated.";
        }
    }

40) Now let's try to insert a record in the database.Create a new page, name it InsertProduct.aspx.
Add a DetailsView control on the page. Leave the default name.Add an ObjectDataSource control on the page. Leave the default name.Set the DataSourceID property of the DetailsView control to ObjectDataSource1. When you try to configure your ObjectdDataSource object, select as the business object the DataAccess class, and select the methods in the appropriate tabs (SELECT,INSERT) for Select,Insert operations.Set the DataKeyNames property of the DetailsView control to ProductID.

41) Run your application and see the first record of the database. Click New and insert a record.If you want to have the DetailsView control to be in insert mode, in the Page_Load event handling routine of the page, type

  DetailsView1.ChangeMode(DetailsViewMode.Insert);


Run your application again and observe that you are in Insert mode when the page loads.

42) Now let's add some static methods that use Datasets in our DataAccess class.This is called the disconnected data access model, since datasets are in memory representation of the data stored in the database. We work with that in memory representation of the data. Let's say we want to retrieve the products per category.Inside the DataAccess.cs file add this method

public static DataSet GetProductsPerCategory(int categoryID)
    {
        DataSet dataSet = new DataSet();
 
        using (SqlConnection connection = Connection.GetDBConnection())
        {
   string sql = "SELECT * FROM Products WHERE CategoryID=@CategoryID";
   SqlCommand command = new SqlCommand(sql, connection);
   command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
   command.CommandType = CommandType.Text;
 
   SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
   dataAdapter.Fill(dataSet, "Products");
        }
        return dataSet;
    }

43) The code above is very simple.I have a static method that I pass a value as input parameter(CategoryID). It also returns a dataset.Inside the method

I create a Dataset object

I get the connection object back from the GetDBConnection function that "lives" inside the Connection class.I define the SQL statement

I create a SqlCommand object by passing as parameters the name of the stored procedure and the connection object

I set the input parameter value of the method as the input parameter to the stored procedure

I use the ExecuteNonQuery() method of the command object to return the rows affected by the update statement.

I create a DataAdapter object and fill the dataset.Finally I return the dataset.

44) Add another page in your site. Name it ProductsPerCategory.aspx.Add a Button,BulletedList,Label and Textbox control on the page. leave the default names.

45) In the Button1_Click event handling routine of the ProductsPerCategory.aspx page type

 try
  {
    int categoryID = int.Parse(TextBox1.Text);
 
           
   DataSet dataSet = DataAccess.GetProductsPerCategory(categoryID);
   foreach (DataRow row in dataSet.Tables["Products"].Rows)
   {
   string mystring = string.Format("{0}, {1}, {2}", row["ProductID"],

row["ProductName"], row["UnitPrice"]);
   BulletedList1.Items.Add(mystring);
                
   }
 
 
 Label1.Text= string.Format("{0} rows in Products table in DataSet.",
 dataSet.Tables["Products"].Rows.Count);
        }
        catch (SqlException ex)
        {
            
            Label1.Text = "Cannot get product data." + ex.Message;
        }

46) Do not forget to add these lines in the top of the ProductsPerCategory.aspx.cs file.

using System.Data.SqlClient;
using System.Data;

Run your application and enter a value in the textbox for the CategoryID.Click the button and see the related products(only the ProductID,ProductName,UnitPrice) printed out in the screen.

47) I am going to use datasets to insert and delete records. Add a new page to your site,name it DatasetInsertDelete.aspx. 
Add a Button,Gridview,Label,Textbox and an objectdatasource control on the page. Leave the default names.Set the DataSourceID property of the GridView control to ObjectDataSource1.

When you try to configure your ObjectdDataSource object, select as the business object the DataAccess class, and select the methods in the appropriate tabs for (SELECT,UPDATE,DELETE)

SELECT= GetProductsPerCategory(categoryID)

 UPDATE = UpdateProduct()

DELETE = DeleteProduct()

In the Define Parameters step set Parameter Source to Control and set ControlID to TextBox1. Then finish the wizard.

Configure the GridView control to enable paging,sorting,editing,deleting.

Set the DataKeyNames property of the GridView control to ProductID.

48) Run your application and enter a value in the textbox for the CategoryID.Click the button and see the related products.Edit and Delete products and make sure they are deleted in actual database.Notice that with a dataset we can have paging and sorting but this cannot be achieved through the data reader object.

49) Now I am going to use DataView controls to bind some data to a GridView. We want to find all products that their untiprice is less than 20 and are not discontinued.

Add a new page to your form,name it MyDataView.aspx. Add a Button,Gridview,Label,Textbox control on the page.Leave the default names.

50) In the Button1_Click event handling routine of the MyDataView.aspx page type

 try
        {
            int categoryID = int.Parse(TextBox1.Text);
 
            DataSet dataSet = DataAccess.GetProductsPerCategory(categoryID);
 
            DataView myview = new DataView(dataSet.Tables["Products"]);
          
            myview.Sort = "UnitPrice";
            myview.RowFilter = "UnitPrice < 20 AND Discontinued = 0";
            GridView1.DataSource = myview;
 
            DataBind();
 
           Label1.Text = "All rows for the criteria specified are returned.";
        }
        catch (SqlException ex)
        {
          
            Label1.Text = "Some error occured....." + ex.Message;
        }

51) The code above is very simple.Inside the method

I store in a variable the CategoryID value the user enters in the textbox

I create the dataset by calling the GetProductsPerCategory method from the DataAccess class

I create a DataView object object by passing as parameter the datatable

I set values for filtering and sorting on the DataView object. I set the DataSource property of the GridView to the view object and I call the DataBind() method

Do not forget to add these lines in the top of the MyDataView.aspx.cs file.


using System.Data.SqlClient;
using System.Data;

52) Run your application and enter a value in the textbox for the CategoryID.Click the button and see the related products.

53) Let's now see how we can bind data to the gridview from related tables.We want the use to enter a category ID in the textbox and get the products for that category in the GridView control.

Then as the user selects records in the first gridview (Product data) to have the related data( order details ) for that product appearing in the second gridview.

Add a new page to your form,name it RelatedTables.aspx. Add a Button,Label,Textbox control on the page.Leave the default names.Add 2 GridView controls on the page. Leave the default names.

Inside the GridView1(the gridview that will get the products data) add this bit of code. We will use this Select button to select values in the Gridview.

  <Columns>
   <asp:CommandField ShowSelectButton="True" />
  </Columns>


54) Now let's add another static method that use Datasets in our DataAccess class that gets data from the Product and Order Details tables.Inside the DataAccess.cs file add this method 
GetProductsAndOrderDetails(int categoryID)

public static DataSet GetProductsAndOrderDetails(int categoryID)
    {
        DataSet dataSet = new DataSet();
 
        using (SqlConnection connection = Connection.GetDBConnection())
        {
      string sql = "SELECT * FROM Products WHERE CategoryID=@CategoryID";
      SqlCommand command = new SqlCommand(sql, connection);
      command.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
      command.CommandType = CommandType.Text;
 
      SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
      dataAdapter.Fill(dataSet, "Products");
 
      command.CommandText = "SELECT * FROM [Order Details]";
      dataAdapter.Fill(dataSet, "OrderDetails");
 
      DataRelation relation = new DataRelation(
      "Products_OrderDetails",
      dataSet.Tables["Products"].Columns["ProductID"],
      dataSet.Tables["OrderDetails"].Columns["ProductID"]);
 
        }
        return dataSet;
    }

55) The code above is very simple.I have a static method that I pass a value as input parameter(CategoryID). It also returns a dataset.Inside the method

I create a new DataSet.I define the SQL statement

I get the connection object back from the GetDBConnection function that "lives" inside the Connection class

I create a SqlCommand object by passing as parameters the name of the stored procedure and the connection object

I set the input parameter value of the method as the input parameter to the stored procedure

I create a DataAdapter object and fill the dataset.

I specify another SQL statement (command.CommandText = "SELECT * FROM [Order Details]";)

I use the DataAdapter object and fill the dataset.

I create a DataRelation object.

Finally I return the dataset.

56) In the Button1_Click event handling routine of the RelatedTables.aspx page type

try
        {
        int categoryID = int.Parse(TextBox1.Text);
 
        DataSet dataSet = DataAccess.GetProductsAndOrderDetails(categoryID);
        GridView1.DataSource = dataSet.Tables["Products"];
        DataBind();
 
            Label1.Text = string.Format("Success.");
        }
        catch (SqlException ex)
        {
             Label1.Text = "Cannot get product data." + ex.Message;
        }

Set the DataKeyNames property of the GridView1 control to ProductID,CategoryID.

57) Do not forget to add these lines in the top of the RelatedTables.aspx.cs file.


using System.Data.SqlClient;
using System.Data;

58) In the GridView1_SelectedIndexChanged event handling routine of the RelatedTables.aspx page type

try
        {
        int productID = (int)GridView1.SelectedDataKey.Values[0];
        int categoryID = (int)GridView1.SelectedDataKey.Values[1];
 
        DataSet dataSet = DataAccess.GetProductsAndOrderDetails(categoryID);
        GridView1.DataSource = dataSet.Tables["Products"];
 
        DataView myview = new DataView(dataSet.Tables["OrderDetails"]);
        myview.RowFilter = "ProductID=" + productID;
        GridView2.DataSource = myview;
 
        DataBind();
 
        Label1.Text = string.Format("Success!!!");
        }
        catch (SqlException)
        {
 
            Label1.Text = "Cannot get the data data.";
        }

59) The code above is very simple.Inside the method

I store in two variables the Category ID and Product ID

I create the dataset by calling the GetProductsAndOrderDetails method from the DataAccess class

I set the DataSource property of the GridView1 to the data table

I create a DataView object object by passing as parameter the datatable (OrderDetails)

I set the value for filtering for the DataView object. I set the DataSource property of the GridView2 to the view object and I call the DataBind() method

60) Run your application and enter a value in the textbox for the CategoryID.Click the button and see the related products.Select a product from the first gridview and see the related information in the second gridview.

I know that this is a rather huge post. You can break it down and try only the bits you are interested in.

If you follow the steps carefully you will learn how to implement a data access layer in ADO.Net 2.0

Email me if you need the source code.

Hope it helps!!!!

Hi,
This is a great help for everyone who want learn asp.net.
Thanks for this post and hope you continue your writing for us.

Ronjon - Monday, November 28, 2011 3:13:58 PM

Hi,
No words to express the usefulness of this article. Keep posting articles like these so that learners like me will get benefitted.

Karthik C - Thursday, August 16, 2012 11:58:07 AM

very nice article..

syed - Sunday, January 6, 2013 1:11:40 PM

Very nice article
Please Mail me the Code
rajucusoon@gmail.com

Raju - Friday, February 8, 2013 10:51:18 AM

very nice peice of code..gave me huge help!

tathagat kumar - Wednesday, April 24, 2013 3:04:23 PM

DotNetStories

Creating a Data Access Layer in ASP.Net applications for inserting,selecting,deleting and updating data

5 Comments