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