articles

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

Updating the TableAdapter to Use JOINs

Chris Anderson14026 29-Jan-2013

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

CREATETABLE [dbo].[Category](

      [CategoryID] [int] IDENTITY(1,1)NOTNULL,

      [Name] [varchar](50)NULL,

 CONSTRAINT [PK_Category] PRIMARY KEYCLUSTERED

(

      [CategoryID] ASC

))

 

GO

Supplier

CREATETABLE [dbo].[Supplier](

      [SupplierID] [int] IDENTITY(1,1)NOTNULL,

      [SupplierName] [varchar](50)NULL,

 CONSTRAINT [PK_Supplier] PRIMARY KEYCLUSTERED

(

      [SupplierID] ASC

))

 

GO

Product

CREATETABLE [dbo].[Product](

      [ProductID] [int] IDENTITY(1,1)NOTNULL,

      [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 KEYCLUSTERED

(

      [ProductID] ASC

))

 

GO

 

ALTERTABLE [dbo].[Product]  WITHCHECKADD  CONSTRAINT [FK_Product_Category] FOREIGNKEY([CategoryID])

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

GO

 

ALTERTABLE [dbo].[Product] CHECKCONSTRAINT [FK_Product_Category]

GO

 

ALTERTABLE [dbo].[Product]  WITHCHECKADD  CONSTRAINT [FK_Product_Supplier] FOREIGNKEY([SupplierID])

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

GO

 

ALTERTABLE [dbo].[Product] CHECKCONSTRAINT [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

      LEFTJOIN Category ON

            Category.CategoryID = Product.CategoryID

      LEFTJOIN 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).

createprocedure [dbo].[sp_GetProductByCategoryID] @CategoryId int

as

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

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

       FROM Product

      LEFTJOIN Category ON

            Category.CategoryID = Product.CategoryID

      LEFTJOIN 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:

Updating the TableAdapter to Use JOINs

Now you can see that one additional method added in a TableAdapter which is used to get the details of product on the basis of category id by using stored procedure (sp_GetProductByCategoryID).

Updating the TableAdapter to Use JOINs

The brittleness of the TableAdapter’s autogenerated INSERT, UPDATE, and DELETE statements are, fortunately, limited to adhoc SQL statements. If your TableAdapter uses stored procedures, you can customize the SelectCommand, InsertCommand, UpdateCommand, or DeleteCommand stored procedures and rerun the TableAdapter Configuration wizard without having to fear that the stored procedures will be modified.


Updated 11-Jul-2020
hi I am software developer at mindstick software pvt. ltd.

Leave Comment

Comments

Liked By