RE: Userform to add and update a table

From: vishal subramaniam (vishalsu_at_microsoft.com)
Date: 04/27/04 

Date: Tue, 27 Apr 2004 09:54:48 GMT

vishalsu@online.microsoft.com

RESOLUTION /LINKS:
======================
One of the most common scenarios in application development is to display
data on a form. This walkthrough illustrates a simple Windows Form that
displays data from a single table in a data grid. The grid is editable, and
you will be able to make changes to data and update the database. Although
the result is not complex, the walkthrough illustrates many of the basic
procedures you will use when accessing data with forms.

In order to complete this walkthrough, you will need:

Access to a server with the Pubs SQL Server sample database.
The walkthrough is split into a number of smaller pieces:

Creating the Windows Form.
Creating and configuring the dataset you will bind the form against. This
includes creating a query that populates the dataset from the database.
Adding the DataGrid control to the form and binding it to data.
Add code to fill the dataset.
Add code that sends dataset changes back to the database.
Creating the Project and Form
The first step is to create a Windows Form.

To create the project and form

If you already have a solution open, select Close Solution from the File
menu.
Note In production projects, you will very often have multiple projects
in the same solution. In this walkthrough, however, you will close any open
solution and create a new one along with the project so that there is no
interference between what you will do here and any existing forms,
datasets, and so on.
>From the File menu, point to New, and then click Project.
In the Project Types pane, choose Visual Basic Projects, Visual C#
Projects, or Visual C++ Projects.
In the Templates pane, choose Windows Application for Visual Basic and
Visual C# projects, or choose Windows Forms Application (.NET) for Visual
C++ projects.
Assign a name to the project that will be unique and will conform to the
naming conventions you use. For example, you might name this project
Walkthrough_Simple1.
When you have assigned a name and specified a new solution, click OK.
Visual Studio creates a new project and displays a new form in the Windows
Form Designer.

Creating and Configuring a Dataset
As with most data-access scenarios in Windows Forms applications, you'll be
working with a dataset. A dataset is a container — a cache — that holds the
records you are interested in working with.

Note Using a dataset is only one option for data access, and is not the
optimal choice in some scenarios. Nonetheless, datasets are usually the
right choice in Windows Forms applications, and you will use one in this
walkthrough. For more information, see Recommendations for Data Access
Strategies.
In this walkthrough, you will add a dataset to the form. However, because
the dataset does not already exist, you will not manually add it to the
form. Instead, you will perform the following set of steps:

Create a data adapter using a wizard. The adapter contains SQL statements
used to read and write database information. The wizard helps you define
the SQL statements you need. If necessary, the wizard also creates a
connection to the database.
Generate the dataset schema. In this process, you will have Visual Studio
create a new typed dataset class based on the tables and columns you are
accessing. When you generate the dataset class, you will also add an
instance of it to the form.
It is important that you follow all the procedures in this section.
Otherwise your form will not have the dataset that you will be using in
subsequent parts of the walkthrough.

For more information on data adapters, see Introduction to Data Adapters.
For more information about datasets, see Introduction to Datasets.

Configuring a Data Connection and Data Adapter
To begin, you create a data adapter that contains the SQL statement used to
populate the dataset later. As part of this process, you define a
connection to access a database. You configure the data adapter using a
wizard, which makes it easy to create the SQL statements you need for data
access.

Note When the wizard is done, you must continue to the next section in
order to generate a dataset and complete the data access portion of your
form.
Security Note Storing connection-string details (such as the server name,
user name, and password) can have implications for the security of your
application. Using Windows Integrated Security is a more secure way to
control access to a database. For more information, see Database Security.
To create the data connection and data adapter

>From the Data tab of the Toolbox, drag an OleDbDataAdapter object onto the
form.
Note You could also use the SqlDataAdapter, which is optimized for
working with SQL Server 7.0 or later. In this walkthrough, you use the
OleDbDataAdapter because it is more generic, providing ADO.NET access to
any OLE DB-compatible data source.
The Data Adapter Configuration Wizard starts, which will help you create
both the connection and the adapter.

In the wizard, do the following:
In the second pane, create or choose a connection pointing to the SQL
Server Pubs database.
In the third pane, specify that you want to use an SQL statement to access
the database.
In the fourth pane, create the following SQL statement:
SELECT au_id, au_lname, au_fname, city, state, phone, contract
FROM authors
For assistance building the SQL statement, click Query Builder to launch
the Query Builder.

Note In this walkthrough, you will populate the dataset with all the rows
from the authors table. In production applications, you typically optimize
data access by creating a query that returns only the columns and rows you
need. For an example, see Walkthrough: Displaying Data in a Windows Form
Using a Parameterized Query.
Click Finish to complete the wizard.
When the wizard is complete, you will have a connection (OleDbConnection1
in Visual Basic, or oleDbConnection1 in Visual C# or Visual C++) containing
information about how to access your database. You will also have a data
adapter (OleDbDataAdapter1 in Visual Basic, or oleDbDataAdapter1 in Visual
C# or Visual C++) that contains a query defining which table and columns in
the database you want to access.

After the wizard is complete, generate the dataset based on the SQL query
that you created during this procedure. For more information, see the next
section.

Creating the Dataset
After you have established the means to connect to the database and
specified the information you want (via the SQL command in the data
adapter), you can have Visual Studio create a dataset. Visual Studio can
generate the dataset automatically based on the query you specified for the
data adapter. The dataset is an instance of the DataSet class based on a
corresponding XML Schema (.xsd file) that describes the class's elements
(table, columns, and constraints). For more information about the
relationship between datasets and schemas, see Introduction to Data Access
with ADO.NET.

To generate a dataset

>From the Data menu, choose Generate DataSet.
Tip If you do not see the Data menu, click in the form; the form must
have focus for the menu to appear.
The Generate Dataset dialog box is displayed.

Select the New option and name the dataset dsAuthors.
In the list under Choose which table(s) to add to the dataset, the authors
table should be selected.

Make sure Add this dataset to the designer is checked, and then click OK.
Visual Studio generates a typed dataset class (dsAuthors) and a schema that
defines the dataset. You will see the new schema (dsAuthors.xsd) in
Solution Explorer.

Tip In Solution Explorer, click Show All Files to see that the schema
file's dependent .vb or .cs file, which contains the code the defines your
new dataset class.
Finally, Visual Studio adds an instance of the new dataset class
(DsAuthors1 or dsAuthors1) to the form.

At this point you have set up everything you need in order to get
information out of the database and into a dataset. You are ready to create
a form that will display the data.

Adding a DataGrid Control to Display the Data
In this walkthrough, you will add a single control — a DataGrid control —
that can display all the records from the dataset at the same time. An
alternative would be to use individual controls such as text boxes to
display one record at a time. That strategy then requires you to add
navigation to the form. For simplicity, therefore, you will use a data grid.

Note For an example of how to use individual text boxes to display
records from a dataset, see Walkthrough: Displaying Data in a Windows Form
Using a Parameterized Query.
The data grid must be bound to the dataset in order to display the data.

To add a bound DataGrid control to the form

If you have not already done so, switch to the form designer by clicking
the tab at the top of the current window.
>From the Windows Forms tab of the Toolbox, drag a DataGrid control onto the
form.
Press F4 to display the Properties window.
In the DataSource property, select DsAuthors1 (or dsAuthors1) as the data
source. Do not choose DsAuthors1.Authors (or dsAuthors1.Authors).
In the DataMember property, select authors.
Setting these two properties binds the authors data table in the DsAuthors1
dataset to the grid.

Resize the grid so you can see all the columns. Change its height so you
will be able to see several author records.
Populating the DataGrid Control
Although the data grid is bound to the dataset you created, the dataset
itself is not automatically filled in. Instead, you must fill the dataset
yourself by calling a data adapter method. For more information about
filling datasets, see Introduction to Datasets.

To populate the DataGrid control

>From the Windows Forms tab of the Toolbox, drag a Button control onto the
form.
Name the button btnLoad and change the caption by setting its Text property
to Load.
Double-click the button to create an event-handling method for its Click
event.
In the method, clear the dataset you created, and then call the data
adapter's Fill method, passing it the dataset you want to fill.
The following example shows what the complete method will look like:

' Visual Basic
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnLoad.Click
   DsAuthors1.Clear()
   OleDbDataAdapter1.Fill(DsAuthors1)
End Sub

// C#
private void btnLoad_Click(object sender, System.EventArgs e)
{
   dsAuthors1.Clear();
   oleDbDataAdapter1.Fill(dsAuthors1);
}

// C++
private:
   System::Void btnLoad_Click(System::Object * sender,
      System::EventArgs * e)
      {
         dsAuthors1::Clear();
         oleDbDataAdapter1::Fill(dsAuthors1);
      }
Updating the Database
When users make a change in the grid, the control automatically saves the
updated record in the dataset. In Windows Forms, the data-binding
architecture writes the values of data-bound controls to the data rows they
are bound to.

Note In Web Form pages, data binding works somewhat differently. For a
step-by-step example of data binding in Web Forms pages, see Walkthrough:
Displaying Data in a Web Forms Page and Walkthrough: Updating Data Using a
Database Update Query in Web Forms.
However, when you work with a dataset, updates require two stages. After
the data is in the dataset, you still have to send it from the dataset to
the database. The data adapter can do this with its Update method, which
examines every record in the specified data table in the dataset and, if a
record has changed, sends the appropriate Update, Insert, or Delete command
to the database. For more information, see Introduction to Dataset Updates.

In this walkthrough, you will add a button to the form that users can press
when they want to send their updates to the database.

To update the database

>From the Windows Forms tab of the Toolbox, drag a Button control onto the
form.
Name the button btnUpdate and change the caption by setting its Text
property to Save Changes in Database.
Double-click the button to create an event-handling method for its Click
event.
In the method, call the data adapter's Update method, passing it the
dataset containing the updates you want to send to the database. Use the
MessageBox object to display confirmation text.
The following example shows what the complete method will look like:

' Visual Basic
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click
   OleDbDataAdapter1.Update(DsAuthors1)
   MessageBox.Show("Database updated!")
End Sub

// C#
private void btnUpdate_Click(object sender, System.EventArgs e)
{
   oleDbDataAdapter1.Update(dsAuthors1);
   MessageBox.Show("Database updated!");
}

// C++
private:
   System::Void btnUpdate_Click(System::Object * sender,
      System::EventArgs * e)
      {
         oleDbDataAdapter1::Update(dsAuthors1);
         MessageBox::Show("Database updated!");
      }
Testing
You can now test the form to make sure it displays authors data in the grid
and that users can make updates.

To test the form

Press F5 to run the form.
Note It is not uncommon to get an exception of type
System.Data.SqlClient.SqlException at this point, most likely due to the
way your database login credentials were saved. For information on
remedying this issue, see Cannot Access a Data Base at Run Time that I Can
Access at Design Time.
When the form is displayed, click the Load button.
A list of authors is displayed in the grid.

Make a change in a record in the grid.
When you move to another record in the grid, the change is preserved. Make
a mental note of your change.

Click the Load button again.
This reloads the dataset from the database and refreshes the grid. Notice
that the change you made in Step 3 was not preserved — because you did not
save changes from the dataset to the database.

Make a change in a record in the grid again.
Click the Save Changes in Database button.
You will see the message box displayed, but there is no change in the grid.

Click the Load button once more to re-load data from the database.
This time, the change you made in Step 5 is preserved, because the data was
saved in the database.

Next Steps
This walkthrough has illustrated the basic steps involved in displaying
data in a form. Some enhancements you could make to the form in this
walkthrough include:

Format the grid by changing its color, font, and so on.
Make the grid display data without being explicitly loaded by the user. To
do this, you add a Fill method to the form's New method (in Visual Basic)
or constructor (in C#) and remove the Load button.
Display only selected information in the grid. In many instances, you will
base the display on information that the user supplies at run time (for
example, you might display authors only from a particular city). To do
this, you create a parameterized query. For more information, see
Walkthrough: Displaying Data in a Windows Form Using a Parameterized Query.
Separate data access from the user interface. In this walkthrough, you have
created a form that accesses the data more-or-less directly (via the
dataset). A more flexible and maintainable design is to create a
data-access component that handles data access. The form (that is, the user
interface) could then interact with the component as needed. The same
component could be used by multiple forms (and by other components), which
eliminates the overhead and redundancy of redesigning data access for every
form you create. For more information on creating component-based data
access, see Walkthrough: Creating a Distributed Application.

LINKS:
=====
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vboricodeexamplesfordataaccess.asp

This posting is provided "AS IS" with no warranties, and confers no rights.