Home > DeveloperSection > Articles > Updating the TableAdapter to Use JOINs

Updating the TableAdapter to Use JOINs


ASP.Net ASP.Net 
Ratings:
0 Comment(s)
 7868  View(s)
Rate this:

Updating the TableAdapter to Use JOINs

In this tutorial we will briefly exploring how to create a Table Adapter that includes JOINs in its main query.

Before using TableAdapter, design these five tables in your database because we use these tables in Table Adapter.

Category

CREATE TABLE [dbo].[Category](

      [CategoryID] [int] IDENTITY(1,1) NOT NULL,

      [Name] [varchar](50) NULL,

 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED

(

      [CategoryID] ASC

))

 

GO

Supplier

CREATE TABLE [dbo].[Supplier](

      [SupplierID] [int] IDENTITY(1,1) NOT NULL,

      [SupplierName] [varchar](50) NULL,

 CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED

(

      [SupplierID] ASC

))

 

GO

Product

CREATE TABLE [dbo].[Product](

      [ProductID] [int] IDENTITY(1,1) NOT NULL,

      [ProductName] [varchar](50) NULL,

      [QuantityPerUnit] [int] NULL,

      [UnitPrice] [int] NULL,

      [UnitsInStock] [int] NULL,

      [ReorderLevel] [int] NULL,

      [CategoryID] [int] NULL,

      [SupplierID] [int] NULL,

 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED

(

      [ProductID] ASC

))

 

GO

 

ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_Category] FOREIGN KEY([CategoryID])

REFERENCES [dbo].[Category] ([CategoryID])

GO

 

ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Category]

GO

 

ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_Supplier] FOREIGN KEY([SupplierID])

REFERENCES [dbo].[Supplier] ([SupplierID])

GO

 

ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Supplier]

GO

 

Now create a new asp.net web application

·         Create a new ASP.Net web application using Microsoft Visual C#.

·         Go to Project -> Select Add New item.

Updating the TableAdapter to Use JOINs

·         Select Data from the Installed templates.

·         Select DataSet from the option given for Data tab.

·         Enter the DAL.xsd in the Name textbox for Dataset, as shown in the figure below:

Updating the TableAdapter to Use JOINs

·         Right click on xsd file and select Add -> Table Adapter.

·         Validate connection with your SQL server database.

·         After validating connection, select Use SQL statements option from TableAdapter configuration wizard as shown in below figure.

Updating the TableAdapter to Use JOINs

Enter a Main Query that Contains JOINs

SELECT ProductID, ProductName, Product.SupplierID, Product.CategoryID,

       QuantityPerunit, UnitPrice, UnitsInStock, ReorderLevel, Category.Name, Supplier.SupplierName

       FROM Product

      LEFT JOIN Category ON

            Category.CategoryID = Product.CategoryID

      LEFT JOIN Supplier ON

            Supplier.SupplierID = Product.SupplierID

A join merges the records from one table with records from another table based on some criteria. In the above query, for example, the “LEFT JOIN Category ON Category.CategoryID = Product.CategoryID” instructs SQL Server to merge each product record with the Category record whose CategoryID value matches the product’s CategoryID value. The merged result allows us to work with the corresponding category fields for each product (such as CategoryName).

In particular, the TableAdapter’s wizard will not auto-generate corresponding INSERT, UPDATE, and DELETE statements if the main query contains any JOINS.

Updating the TableAdapter to Use JOINs

By default, the TableAdapter will automatically create INSERT, UPDATE, and DELETE statements based on the main query. If you click the advanced button you can see that this feature is enabled. Despite this setting, theTableAdapter will not be able to create the INSERT, UPDATE, and DELETE statements because the main querycontains a JOIN.

Click Finish to complete the wizard. At this point your DataSet’s Designer will include a single TableAdapter with a DataTable with columns for each of the fields returned in the SELECT query’s column list. This includes the CategoryName and SupplierName, as shown below:

Updating the TableAdapter to Use JOINs

While the DataTable has the appropriate columns, the TableAdapter lacks values for its InsertCommand,

UpdateCommand, and DeleteCommand properties. To confirm this, click on the TableAdapter in the Designer and then go to the Properties window. There you will see that the InsertCommand, UpdateCommand, and DeleteCommand properties are set to “(None)”.

Updating the TableAdapter to Use JOINs

To work around this shortcoming, we can manually provide the SQL statements and parameters for the

InsertCommand, UpdateCommand, and DeleteCommand properties via the Properties window. Alternatively, we could start by configuring the TableAdapter’s main query to not include any JOINs. This will allow the INSERT, UPDATE, and DELETE statements to be autogenerated for us. After completing the wizard, we could then manually update the TableAdapter’s SelectCommand from the Properties window so that it includes the JOIN syntax.

While this approach works, it is very brittle when using adhoc SQL queries because any time the TableAdapter’s main query is reconfigured through the wizard, the autogenerated INSERT, UPDATE, and DELETE statements are recreated. That means all of the customizations we later made would be lost if we rightclicked on the TableAdapter, chose Configure from the context menu, and completed the wizard again.

Now create a stored procedure in your database on the basis of above tables. This procedure will return product details after taking the category id through parameter (CategoryId).

create procedure [dbo].[sp_GetProductByCategoryID] @CategoryId int

as

SELECT ProductID, ProductName, Product.SupplierID, Product.CategoryID,

       QuantityPerunit, UnitPrice, UnitsInStock, ReorderLevel, Category.Name, Supplier.SupplierName

       FROM Product

      LEFT JOIN Category ON

            Category.CategoryID = Product.CategoryID

      LEFT JOIN Supplier ON

            Supplier.SupplierID = Product.SupplierID

      WHERE Category.CategoryID = @CategoryId

Now add the above stored procedure in your TableAdapter.

·         Right click on TableAdapter and choose Add Query option.  The following (TableAdapter Query Configuration Wizard) will open.

·         Choose Use existing stored procedure option and click on Next to proceed.

Updating the TableAdapter to Use JOINs

In the next window, you have to choose the stored procedure created.

Updating the TableAdapter to Use JOINs

Now choose the shape of data returned by the stored procedure. Select Tabluar data option because our stored procedure return product details in tabular format.

Updating the TableAdapter to Use JOINs

Edit name of methods in order to make the method name meaningful for an application, as shown in below figure: