1) Open a web application project in Visual Studio 2005.
2) To add a typed dataset, right click the solution and
click “Add New Item.” It will open a new item dialog box like below.
Figure 1 – Add New Item
3) Select Dataset and change the name; click Add.
4) Open the server explorer in visual studio. If you cannott
find the server explorer, click “Server Explorer" from the view menu of
visual studio.
5) It will open Add Connection window. Select the Server
name and select the “NorthWind” database or your own database as below.
Figure 2 – Add Connection
Select Server name, select the database name and click OK.
6) Drag and drop the Employees table into the dataset
designer to create the typed dataset from the server explorer. The designer
will look like figure 3.
Figure 3 – Dataset Designer
By default, it will have a TableAdapter
(EmployeesTableAdapter here) with Fill() and GetData() methods that can be used
to fill and fetch data from the database without implementing a line of code.
Intellisense Support in Visual Studio 2005
Visual Studio 2005 helps us with the intellisense support
(Refer to Figure 4) by listing tables, columns and methods for the constructed
typed dataset.
Figure 4 – Visual Studio Intellisense
“_Employees[0]” in the intellisense is the table name and “_”
is because we used the same name for the dataset name as the table name. If the
Dataset name is different then the table, the name will be “Employees.”
How to use it?
We can use the typed dataset and fill it in the normal way
as we do for a non-typed dataset.
Listing 2: Filling Typed Dataset in a usual way
com = new SqlCommand("Select * from Employees", sqlcon);
ada = new SqlDataAdapter(com);
emp = new Employees();
da.Fill(emp,"Employees");
The table name in the fill method should match the table
name in the typed dataset.
Using EmployeesTableAdapter:
Listing 3: Filling Typed Dataset using TableAdapter
BO.EmployeesTableAdapters.EmployeesTableAdapter ada = new
BO.EmployeesTableAdapters.EmployeesTableAdapter();
gvEmployees.DataSource = ada.GetData();
gvEmployees.DataBind();
In the above code “BO” is the namespace name since I have
used a separate Class library project with name BO and created the typed
dataset there. If you are creating typed dataset in the same web project then
the above code will be:
Listing 4: Filling Typed Dataset using TableAdapter
in Same project
EmployeesTableAdapters.EmployeesTableAdapter ada = new
EmployeesTableAdapters.EmployeesTableAdapter();
gvEmployees.DataSource = ada.GetData();
gvEmployees.DataBind();
Here, “gvEmployees” is the gridview. In the next sections we
will see how to use TableAdapter and extend the typed dataset to provide custom
methods that serve specific purposes. We use the “customer” table in NorthWind
database for this article.
Figure 5 – Customer Table
What is a TableAdapter?
TableAdapters provide communication between your application
and a database. More specifically, a TableAdapter connects to a database,
executes queries or stored procedures, and either returns a new data table
populated with the returned data or fills an existing DataTable with the
returned data. TableAdapters are also used to send updated data from your
application back to the database.
Adding New Functionality
We will try to add a new Selection functionality and a new
update functionality using TableAdapter in Typed dataset using visual studio.