For this tutorial we will add a TableAdapter and strongly-typed DataTable for the Employee table the DAL.xsd dataset. The Employee table contains a ReportTo field that specified the EmployeeID of the employee’s manager. For example, employee Carl Pietorson has a ReportTo value of 2, which is the EmployerID of Robin Smith. Along with reporting each employee’s ReportTo value, we might also want to retrieve the FirstName and LastName of their employer. This can be accomplished using a JOIN. But using a JOIN when initially creating the TableAdapter precludes the wizard from automatically generating the corresponding insert, update, and delete capabilities. Therefore, we will start by creating a TableAdapter whose main query does not contain any JOINs. Then afterwards, we will update the main query stored procedure to retrieve the manager’s name via a JOIN.
Before moving to an application, create these two tables in your database and some records in these tables:
Employer
CREATE TABLE [dbo].[Employer](
[EmployerID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
CONSTRAINT [PK_Employer] PRIMARY KEY CLUSTERED
(
[EmployerID] ASC
))
Employee
CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[Department] [varchar](50) NULL,
[ReportTo] [int] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
))
GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Employer] FOREIGN KEY([ReportTo])
REFERENCES [dbo].[Employer] ([EmployerID])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Employer]
GO
Now follow these steps:
· Start by opening DataSet (DAL.xsd).
· Right click on the Designer, select the Add option from the context menu, and pick the TableAdapter menu item. This will launch the TableAdapter Configuration wizard.
· Select Create new stored procedures and click Next from the wizard shown below.
Use the following SELECT statement for the TableAdapter’s main query:
SELECT [EmployeeID], [FirstName], [LastName], [Country], [Department], [ReportTo] FROM [dbo].[Employee]
Since this query does not include any JOINs, the TableAdapter wizard will automatically create stored procedures with corresponding INSERT, UPDATE, and DELETE statements, as well as a stored procedure for executing the main query.
The following step allows us to name the TableAdapter’s stored procedure. Use the names Employee_Select, Employee_Insert, Employee_Update, and Employee_Delete, as shown in below figure:
The final step prompts us to name the TableAdapter’s methods. Use Fill and GetEmployees as the method names. Also be sure to leave the “Create methods to send updates directly to the database (GenerateDBDirectMethods)” checkbox checked.
After completing the wizard, take a moment to examine the stored procedures in the database. You should see four new ones: Employee_Select, Employee_Insert, Employee_Update, and Employee_Delete. Next, inspect the EmployeeDataTable and EmployeeTableAdapter
With the insert, update, and delete stored procedures automatically created and the InsertCommand, UpdateCommand, and DeleteCommand properties correctly configured, we are ready to customize the SelectCommand’s stored procedure to return additional information about each employee’s manager. Specifically, we need to update the Employee_Select stored procedure to use a JOIN and return the employer FirstName and LastName values. After the stored procedure has been updated, we will need to update the DataTable so that it includes these additional columns..
Start by going to the Server Explorer, drilling down into the database’s Stored Procedures folder, and opening the Employee_Select stored procedure. If you do not see this stored procedure, rightclick on the Stored Procedures folder and choose Refresh. Update the stored procedure so that it uses a LEFT JOIN to return the employer’s first and last name:
ALTER PROCEDURE [dbo].[Employee_Select]
AS
SET NOCOUNT ON;
SELECT Employee.EmployeeID, Employee.FirstName, Employee.LastName, Employee.Country,
Employee.Department, Employee.ReportTo,
Employer.FirstName as EmployerFirstName, Employer.LastName as EmployerLastName
FROM Employee LEFT JOIN Employer ON
Employee.ReportTo = Employer.EmployerID
At this point, the Employee_Select stored procedure returns EmployerFirstName and EmployerLastName values, but the EmployeeDataTable is missing these columns. These missing columns can be added to the DataTable in one of two ways:
· Manually rightclick on the DataTable in the DataSet Designer and, from the Add menu, choose Column. You can then name the column and set its properties accordingly.
· Automatically the TableAdapter Configuration wizard will update the DataTable’s columns to reflect the fields returned by the SelectCommand stored procedure. When using adhoc SQL statements, the wizard will also remove the InsertCommand, UpdateCommand, and DeleteCommand properties since the SelectCommand now contains a JOIN. But when using stored procedures, these command properties remain intact.
For this tutorial, let use the automatic approach via the TableAdapter Configuration wizard.
Start by rightclicking on the EmployeeTableAdapter and selecting Configure from the context menu. This brings up the TableAdapter Configuration wizard, which lists the stored procedures used for selecting, inserting, updating, and deleting, along with their return values and parameters (if any).
Here we can see that the Employee_Select stored procedure now returns the EmployerFirstName and EmployerLastName fields.
Complete the wizard by clicking Finish. Upon returning to the DataSet Designer, the EmployeeDataTable includes two additional columns: EmployerFirstName and EmployerLastName.
To illustrate that the updated Employee_Select stored procedure is in effect and that the insert, update, and delete capabilities of the TableAdapter are still functional, let’s create a web page that allows users to view and delete employees. Before we create such a page, however, we need to first create a new class in the Business Logic Layer for working with employees from the DataSet.
Create a new class file named Employee.cs.Add the following code to the Employee class.
public class Employee
{
private EmployeeTableAdapter _empAdapter = null;
protected EmployeeTableAdapter Adapter
{
get
{
if (_empAdapter == null)
_empAdapter = new EmployeeTableAdapter();
return _empAdapter;
}
}
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
public DAL.EmployeeDataTable GetEmployees()
{
return Adapter.GetEmployees();
}
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
public bool DeleteEmployee(int employeeID)
{
int rowsAffected = Adapter.Delete(employeeID);
return rowsAffected == 1;
}
}
The Employee class’s Adapter property returns an instance of the DataSet’s EmployeeTableAdapter. This is used by the class’s GetEmployees and DeleteEmployee methods. The GetEmployees method calls the EmployeeTableAdapter’s corresponding GetEmployees method, which invokes the Employee_Select stored procedure and populates its results in an EmployeeDataTable. The DeleteEmployee method similarly calls the EmployeeTableAdapter’s Delete method, which invokes the Employee_Delete stored procedure.
With the Employee class complete, we’re ready to work with employee data through an ASP.NET page. Open the Default.aspx page and drag a GridView from the Toolbox onto the Designer, setting its ID property to Employee. Next, from the GridView’s smart tag, bind the grid to a new ObjectDataSource control named EmployeeDataSource.
Configure the ObjectDataSource to use the Employee class and, from the SELECT and DELETE tabs, ensure that the GetEmployees and DeleteEmployee methods are selected from the dropdown lists. Click Finish to complete the ObjectDataSource’s configuration.
Visual Studio will add a BoundField to the GridView for each of the EmployeeDataTable’s columns.
To allow users to delete employees from this page we need to do two things. First, instruct the GridView to provide deleting capabilities by checking the “Enable Deleting” option from its smart tag. Second, change the ObjectDataSource’s OldValuesParameterFormatString property from the value set by the ObjectDataSource wizard (original_{0}) to its default value ({0}). After making these changes, your GridView and ObjectDataSource’s declarative markup should look similar to the following:
<div>
<asp:GridView ID="Employee" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID"
DataSourceID="EmployeeDataSource" CellPadding="3" BackColor="#DEBA84"
BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellSpacing="2">
<Columns>
<asp:CommandField ShowDeleteButton="True" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
<asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department" />
<asp:BoundField DataField="EmployerFirstName" HeaderText="EmployerFirstName" SortExpression="EmployerFirstName" />
<asp:BoundField DataField="EmployerLastName" HeaderText="EmployerLastName" SortExpression="EmployerLastName" />
</Columns>
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FFF1D4" />
<SortedAscendingHeaderStyle BackColor="#B95C30" />
<SortedDescendingCellStyle BackColor="#F1E5CE" />
<SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<asp:ObjectDataSource ID="EmployeeDataSource" runat="server" DeleteMethod="DeleteEmployee"
SelectMethod="GetEmployees" OldValuesParameterFormatString="{0}" TypeName="TableAdapter_DAL.Employee">
<DeleteParameters>
<asp:Parameter Name="employeeID" Type="Int32" />
</DeleteParameters>
</asp:ObjectDataSource>
</div>
Test out the page by visiting it through a browser. As shown below, the page will list each employee and his or her employer’s name (assuming they have one).
Clicking the Delete button starts the deleting workflow, which culminates in the execution of the Employee_Delete stored procedure.
Thanks for reading this article. I think this will help you a lot.
Leave Comment