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.
· 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:
· 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.
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.
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:
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)”.
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.
In the next window, you have to choose the stored procedure created.
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.
Edit name of methods in order to make the method name meaningful for an application, as shown in below figure:
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).
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.
Sushant Mishra
27-Jul-2017It was really helpful to read this post.