Technology Geek| Cloud Architect | Developer

PublicJoe's - Sample Chapter - Teach Yourself C# in 24 Hours - Chapter 21 - Working with a Database in C#

Teach Yourself C# in 24 Hours - Chapter 21 - Working with a Database

in C#

By James Foxall and Wendy Haro-Chun.

ISBN: 0-6723-2287-0

You've heard it so many times that it's almost a cliché: This is the information age. Information is data, and managing information means working with databases. Database design is a skill unto itself, and entire books are devoted to database design and management. In this hour, you'll learn the basics of working with a database using ADO.NET, Microsoft's newest database technology. Although high-end solutions are built around advanced database technologies such as Microsoft's SQL Server, the Microsoft Jet database (used by Microsoft Access) is more readily available and easier to learn, so you'll build working examples that use a Jet database.

The highlights of this hour include the following:

l Introduction to ADO.NET
l Connecting to a database
l Understanding DataTables
l Creating a DataAdapter
l Referencing fields in a DataRow
l Navigating records
l Adding, editing, and deleting records
l Building an ADO.NET example

NOTE

You'll learn a lot in this hour, but realize that this material is really the tip of the iceberg. Database programming can be, and often is, very complex. This hour is intended to get you writing database code as

quickly as possible, but if you plan on doing a lot of database programming, you'll want to consult a dedicated book (or two) on the subject. Start by creating a new Windows Application named Database Example. Change the name of the default formto fclsMain and set its Text property to Database Example. Next, click the View Code button on the Solution Explorer window to access the form's code, scroll down and locate the procedure Main(), and change the reference of Form1 to fclsMain. Finally, click the Form1.cs tab to return to the form designer.

Introduction to ADO.NET

ADO.NET is the .NET platform's new database technology, and it builds on ADO (Active Data Objects). ADO. NET defines DataSet and DataTable objects that are optimized for moving disconnected sets of data across intranets and Internets, including through firewalls. At the same time, ADO.NET includes the traditional Connection and Command objects, as well as an object called a DataReader that resembles a forward-only, readonly ADO recordset. Together these objects provide the very best performance and throughput for retrieving

data from a database.

Connecting to a Database

To access data in a database, you must first establish a connection using an ADO.NET connection object. Two connection objects are included in the .NET Framework: the OleDbConnection object, for working with the same OLE DB data providers you would access through traditional ADO, and the SqlConnection object, for optimized access to Microsoft SQL Server. Because these examples connect to the Microsoft Jet Database, you'll be using the OleDbConnection object. To create an object variable of type OleDbConnection and

initialize the variable to a new connection, you could use a statement such as the following:

OleDbConnection cnADONetConnection = new OleDbConnection();

To use ADO.NET, the first step that you need to take is to add the proper Namespace to your project. Doubleclick the form now to access its events. Scroll to the top of the class and add the following using statement on the line below the other using statements:

using System.Data.OleDb;

You're going to create a module-level variable to hold the connection, so place the cursor below the left bracket ({) that follows the statement public class fclsMain : System.Windows.Forms.Form and

press Enter to create a new line. Enter the following statement:

OleDbConnection m_cnADONetConnection = new OleDbConnection();

Before using this connection, you must specify the data source to which you want to connect. This is done through the ConnectionString property of the ADO.NET connection object. The ConnectionString

contains connection information such as the name of the provider, username, and password. The ConnectionString might contain many connection parameters; the set of parameters available varies

depending on the source of data that you are connecting to. Some of the parameters used in the OLE DB ConnectionString are listed in Table 21.1. If you specify multiple parameters, separate them with a

semicolon.

Table 21.1 - Possible Parameters for ConnectionString

Parameter Description

Provider The name of the data provider (Jet, SQL, and so on) to use.

Data Source= The name of the data source (database) to connect to.

UID= A valid username to use when connecting to the data source.

PWD= A password to use when connecting to the data source.

DRIVER= The name of the database driver to use. This isn't required if a DSN is specified.

SERVER= The network name of the data source server.

The Provider= parameter is one of the most important at this point and is governed by the type of database you're accessing. For example, when accessing a SQL database, you specify the provider information for SQL; when accessing a Jet database, you specify the provider for Jet. In this example, you'll be accessing a Jet (Microsoft Access) database, so you'll use the provider information for Jet.In addition to specifying the provider, you're also going to specify the database. I've provided a sample database at the Web site for this book. This code assumes that you've placed the database in a folder called C: \Temp. If you are using a different folder, you'll need to change the code accordingly.

To specify the ConnectionString property of your ADO.NET connection, place the following statement in the Load event of your form:

m_cnADONetConnection.ConnectionString =

@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\contacts.mdb";

After the connection string is defined, a connection to a data source is established by using the Open()method of the connection object. The Open() method has the following syntax:

objectvariable.Open();

NOTE

Refer to the online documentation for information on the connection strings for providers other than Jet.When you attach to an unsecured Jet database, it's not necessary to provide a username and password. When attaching to a secured Jet database, however, you'll have to provide a username and a password. This is done by passing the username and password as parameters in the ConnectionString property. The sample database I've provided isn't secured, so it's not necessary to provide a username and password.

Closing a Connection to a Data Source

You should always explicitly close a connection to a data source. This means that you should not rely on a variable going out of scope to close a connection, but you should force an explicit disconnect via code. This is

accomplished by calling the Close() method of the connection object.

You're now going to write code to explicitly close the connection when the form is closed. Start by clicking the Form1.cs tab to return to the form designer. Next, click the Events button on the Properties window (the lightening bolt) to access the list of events for the form. Double-click the Closed event to create a new event handler. Enter the following statement in the Closed event:

m_cnADONetConnection.Close();

Manipulating Data

The easiest way to manipulate data using ADO.NET is to create a DataTable object containing the resultset of a table, query, or stored procedure. Using a DataTable, you can add, edit, delete, find, and navigate records. The following sections explain how to use DataTables.

Understanding DataTables

DataTables contain a snapshot of the data in the data source. You generally start by filling a DataTable, and then you manipulate the results of the DataTable before finally sending the changes back to the data source.The DataTable is populated using the Fill() method of a DataAdapter object, and changes are sent back to the database using the Update() method of a DataAdapter. Any changes made to the DataTable appear only in the local copy of the data until you call the Update() method. Having a local copy of the data reduces contention by preventing users from blocking others from reading the data while it is being viewed. This is similar to the Optimistic Batch Client Cursor in ADO.

Creating a DataAdapter

To populate a DataTable, you need to create a DataAdapter, an object that provides a set of properties and methods to retrieve and save data between a DataSet and its source data. The DataAdapter you're going to create will use the connection you've already defined to connect to the data source and will then execute a query you'll provide. The results of that query will be pushed into a DataTable. Just as two ADO.NET connection objects are in the .NET Framework, there are two ADO.NET DataAdapter Objects as well: the OleDbDataAdapter and the SqlDataAdapter. Again, you'll be using the

OleDbDataAdapter because you aren't connecting to Microsoft SQL Server. The constructor for the DataAdapter optionally takes the command to execute when filling a DataTable or DataSet, as well as a connection specifying the data source. (You could have multiple nnections open in a single project.) This constructor has the following syntax:

OleDbDataAdapter cnADONetAdapter = new

OleDbDataAdapter([CommandText],[Connection]);

To add the DataAdapter to your project, first add the following statement immediately below the statement you entered to declare the m_cnADONewConnection object.

OleDbDataAdapter m_daDataAdapter = new OleDbDataAdapter();

Next, add the following statement to the Load event of the form, immediately following the statement that creates the connection:

_daDataAdapter =

new OleDbDataAdapter("Select * From Contacts",m_cnADONetConnection);

Because you're going to use the DataAdapter to update the original data source, you need to specify the insert, update, and delete statements to use to submit changes from the DataTable to the data source. ADO. NET lets you customize how updates are submitted by allowing you to manually specify these statements as database commands or stored procedures. In this case, you're going to have ADO.NET automatically generate these statements for you by creating a CommandBuilder object. Enter the following statement to create the

CommandBuilder.

OleDbCommandBuilder m_cbCommandBuilder =

new OleDbCommandBuilder(m_daDataAdapter);

When you create the CommandBuilder, you pass into the constructor the DataAdapter that you want the CommandBuilder to work with. The CommandBuilder then registers for update events on the DataAdapter

and provides the insert, update, and delete commands as needed. You don't need to do anything further with the CommandBuilder.

NOTE

When using a Jet database, the CommandBuilder object can create the dynamic SQL code only if the table in question has a primary key defined.

Creating and Populating DataTables

You're going to create a module-level DataTable in your project. First, create the DataTable variable by adding the following statement on the line below the statement you entered previously to declare a new modulelevel m_daDataAdapter object:

DataTable m_dtContacts = new DataTable();

You are going to use an integer variable to keep track of the user's current position within the DataTable. To do this, add the following statement immediately below the statement you just entered to declare the new DataTable object:

int m_rowPosition = 0;

Next, add the following statement to the Load event of the form, immediately following the statement that creates the CommandBuilder:

m_daDataAdapter.Fill(m_dtContacts);

NOTE

Because the DataTable doesn't hold a connection to the data source, it's not necessary to close it when you're finished.

Your class should now look like the one in

Figure 21.1.

Figure 21.1 - This code accesses a database and creates a DataTable that can be used anywhere in the class.

Referencing Columns in a DataRow

DataTables contain a collection of DataRows. To access a row within the DataTable, you specify the ordinal of that DataRow. For example, you could access the first row of your DataTable like this:

DataRow m_rwContact = m_dtContacts.Rows[0];

Data elements in a DataRow are called columns. For example, two olumns, ContactName and State, are in the Contacts table I've created. To reference the value of a column, you can pass the column name to the DataRow like this:

m_rwContact["ContactName"] = "Bob Brown";

Debug.WriteLine(m_rwContact["ContactName"]);

NOTE

If you spell a column name incorrectly, an exception occurs when the statement executes at runtime. You're now going to create a procedure that is used to display the current record in the database. To display

the data, you need to add a few controls to the form. Create a new text box and set its properties as follows (you'll probably need to click the Properties button on the Properties window to view the text box's properties rather than its events):

Property Value

Name txtContactName

Text (make blank)

Location 48, 112

Size 112, 20

Add a second text box to the form and set its properties according to the following table:

Property Value

Name txtState

Text (make blank)

Location 168, 112

Size 80, 20

Next, click the Form1.cs tab in the IDE to return to the code window. Position the cursor after the right bracket that ends the fclsMain_Closed() event and press Enter a few times to create some blank lines. Next, enter the following procedure in its entirety:

private void ShowCurrentRecord()

{

if( m_dtContacts.Rows.Count == 0 )

{

txtContactName.Text = "";

txtState.Text = "";

return;

}

txtContactName.Text =

m_dtContacts.Rows[m_rowPosition]["ContactName"].ToString();

txtState.Text = m_dtContacts.Rows[m_rowPosition]["State"].ToString();

}

Ensure that the first record is shown when the form loads by adding the following statement to the Load event, after the statement that fills the DataTable: this.ShowCurrentRecord();

You've now ensured that the first record in the DataTable is shown when the form first loads. Next, you'll learn how to navigate and modify records in a DataTable.

Navigating and Modifying Records

The ADO.NET DataTable object supports a number of methods that can be used to access its DataRows. The simplest of these is the ordinal accessor that you used in your ShowCurrentRecord() method. Because the DataTable has no dependency on the source of the data, this same functionality is available regardless of where the data came from.

You're now going to create buttons that the user can click to navigate the DataTable. The first button is used to move to the first record in the DataTable. Add a new button to the form and set its properties as follows:

Property Value

Name btnMoveFirst

Text <<

Location 16, 152

Size 32, 23

Double-click the button and add the following code to its Click event:

m_rowPosition = 0;

this.ShowCurrentRecord();

A second button is used to move to the previous record in the DataTable. Add another button to the form and set its properties as shown in the following table:

Property Value

Name btnMovePrevious

Text <

Location 56, 152

Size 32, 23

Double-click the button and add the following code to its Click event:

if( m_rowPosition > 0 )

{

m_rowPosition = m_rowPosition - 1;

this.ShowCurrentRecord();

}

A third button is used to move to the next record in the DataTable. Add a third button to the form and set its properties as shown in the following table:

Property Value

Name btnMoveNext

Text >

Location 96, 152

Size 32, 23

Double-click the button and add the following code to its Click event:

if( m_rowPosition < m_dtContacts.Rows.Count - 1 )

{

m_rowPosition = m_rowPosition + 1;

this.ShowCurrentRecord();

}

A fourth button is used to move to the last record in the DataTable. Add yet another button to the form and set its properties as shown in the following table:

Property Value

Name btnMoveLast

Text >>

Location 136, 152

Size 32, 23

Double-click the button and add the following code to its Click event:

if( m_dtContacts.Rows.Count != 0 )

{

m_rowPosition = m_dtContacts.Rows.Count - 1;

this.ShowCurrentRecord();

}

Editing Records

To edit records in a DataTable, simply change the value of a particular column in the desired DataRow. Remember, however, that changes are not made to the original data source until you call Update() on the

DataAdapter, passing in the DataTable containing the changes.

You're now going to add a button that the user can click to update the current record. Add a new button to the form now and set its properties as follows:

Property Value

Name btnSave

Text Save

Location 176, 152

Size 40, 23

Double-click the Save button and add the following code to its Click event:

if( m_dtContacts.Rows.Count != 0 )

{

m_dtContacts.Rows[m_rowPosition]["ContactName"] = txtContactName.Text;

m_dtContacts.Rows[m_rowPosition]["State"] = txtState.Text;

m_daDataAdapter.Update(m_dtContacts);

}

Creating New Records

Adding records to a DataTable is performed very much like editing records. However, to create a new row in the DataTable, you must first call the NewRow() method. After creating the new row, you can set its column values. The row isn't actually added to the DataTable, however, until you call the Add() method on the DataTable's RowCollection.

You're now going to modify your interface so that the user can add new records. You'll use one text box for the contact name and a second text box for the state. When the user clicks a button you'll provide, the values in these text boxes will be written to the Contacts table as a new record. Start by adding a group box to the form and set its properties as shown in the following table:

Property Value

Name grpNewRecord

Text New Contact

Location 16, 192

Size 264, 64

Next, add a new text box to the group box and set its properties as follows:

Property Value

Name txtNewContactName

Text (make blank)

Location 8, 24

Size 112, 20

Add a second text box to the group box and set its properties as shown:

Property Value

Name txtNewState

Text (make blank)

Location 126, 24

Size 80, 20

Finally, add a button to the group box and set its properties as follows:

Property Value

Name btnAddNew

Text Add

Location 214, 24

Size 40, 23

Double-click the Add button and add the following code to its Click event:

DataRow drNewRow = m_dtContacts.NewRow();

drNewRow["ContactName"] = txtNewContactName.Text;

drNewRow["State"] = txtNewState.Text;

m_dtContacts.Rows.Add(drNewRow);

m_daDataAdapter.Update(m_dtContacts);

m_rowPosition = m_dtContacts.Rows.Count - 1;

this.ShowCurrentRecord();

Notice that after the new record is added, the position is set to the last row and the ShowCurrentRecord() procedure is called. This causes the new record to appear in the text boxes you created earlier.

Deleting Records

To delete a record from a DataTable, you call the Delete() method on the DataRow to be deleted. Add a new button to your form (not to the group box) and set its properties as shown in the following table.

Property Value

Name btnDelete

Text Delete

Location 224, 152

Size 56, 23

Double-click the Delete button and add the following code to its Click event:

if( m_dtContacts.Rows.Count != 0 )

{

m_dtContacts.Rows[m_rowPosition].Delete();

m_daDataAdapter.Update(m_dtContacts);

m_rowPosition = 0;

this.ShowCurrentRecord();

}

Your form should now look like that in Figure 21.2.

Figure 21.2 - A basic data-entry form.

Running the Database Example

Press F5 to run the project. If you entered all the code correctly, and you placed the Contacts database into the C:\Temp folder (or modified the path used in code), the form should display without errors, and the first record in the database will appear. Click the navigation buttons to move forward and backward. Feel free to change the information of a contact, click the Save button, and your changes will be made to the underlying database. Next, enter your name and state into the New Contact section of the form and click Add. Your name will be added to the database and displayed in the appropriate text boxes.

Using the Data Form Wizard

Visual Basic .NET includes a tool to help introduce you to ADO.NET - the Data Form Wizard. In this section, you're going to use the Data Form Wizard to create a form that is bound to the same database you used in the previous example. Start by creating a new Windows Application titled Data Form Example. The Data Form Wizard is run by adding it to your project as a form template. Choose Add Windows Form from the Project menu to display the Add New Item dialog box, click the Data Form Wizard icon, change the name to fclsDataForm.cs (see Figure 21.3), and click Open to start the wizard.

Figure 21.3 - The Data Form Wizard as a form template.

The first page of the wizard is simply an introduction. Click Next to get to the first "real" page. This next page is used to choose the dataset you want to bind to the form. ADO.NET datasets hold a collection of

DataTables. Enter AllContacts into the text box (see Figure 21.4) and click Next to continue.

Figure 21.4 - A DataTable is similar to an ADO recordset.

The next page of the wizard is used to specify a connection to a data source (see Figure 21.5). Note: Because you haven't previously defined a connection to the Contacts database, your drop-down list will be empty. Click the New Connection button to display the Data Link Properties dialog box. Notice that this dialog box opens with the Connection page visible. Click the Provider tab to see the list of installed providers on your computer (see Figure 21.6), choose Microsoft Jet 4.0 OLE DB Provider to select it, and then click the Connection tab once more.

Figure 21.5 - Use this page to specify a data source.

Figure 21.6 - You must specify the appropriate provider for the type of data source to which you are connecting. Now that you've selected the provider, you need to locate and select the data source (your Jet database). Click the build button next to the database name text box, and then locate and select the contacts.mdb database. Next, click Test Connection to make sure the information you have supplied creates a valid connection to the database. If the test succeeded, click OK to close the Data Link Properties dialog box. The database should

now appear in the Connection drop-down list. Click Next to continue.

The next step in completing the wizard is to choose the table or tables you want to use (see Figure 21.7). The tables you choose here will be used to supply the data that is bound to your form. Double-click the Contacts table to add it to the Selected Items list and click Next to continue.

Figure 21.7 - Use this page to choose the data to bind to the form.

This page shown in Figure 21.8 is used to specify the columns that you want bound on the form. The two columns in your Contacts table are already selected by default, so click Next to continue.

Figure 21.8 - You don't have to select all the fields in a table.

The last step of the wizard is specifying the style in which you want the data to appear (see Figure 21.9). Because the previous example had you add individual controls for each column, leave the All Records in a Grid

radio button, selected (this will create a data grid). Click Finish to create your new data form, which will appear in the form designer (see Figure 21.10).

Figure 21.9 - The Data Form Wizard gives you a number of choices for displaying your data.

Figure 21.10 - This bound grid was created by the Data Form Wizard.

To test your form, you'll have to display it. Click Form1.cs to display the designer for the default form in your

project and add a new button to the form. Set the button's properties as follows:

Property Value

Name btnShowDataForm

Text Show Data Form

Location 96, 120

Size 104, 23

Next, double-click the button to access its Click event and add the following code:

fclsDataForm objDataForm = new fclsDataForm ();

objDataForm.Show();

Press F5 to run the project, and then click the button and your bound form will appear. To load the grid with records, click the Load button (see Figure 21.11). Figure 21.11 - This grid is bound to the record source. Stop the running project, click fclsDataForm.cs in the Solution Explorer, and then click the View Code button on the Solution Explorer to view the class. Notice that the Data Form Wizard created all the ADO.NET code for you, and even included rudimentary error handling. The Data Form Wizard is a great way to get started with ADO.NET, but it will take you only so far. To create robust ADO.NET applications, you will need to find one or more dedicated resources that focus on the intricacies of ADO.NET.

Summary

Most commercial applications use some sort of database. Becoming a good database programmer requires extending your skills beyond being just a good programmer. There is so much to know about optimizing database and database code, creating usable database interfaces, creating a database scheme - the list goes on. However, writing any database application begins with the basic skills you learned in this hour. You learned how to connect to a database, create and populate a DataTable, and navigate the records in the DataTable. In addition, you learned how to edit records and how to add and delete reco rds. Finally, you learned how to use the Data Form Wizard to create a basic ADO.NET bound form. You are now prepared to write simple, yet

functional, database applications.

Samiyappan Prabakar ,Mobile : +971 50 9042741

Need a vacation? Get great deals to amazing places on Yahoo! Travel.

Technology Geek| Cloud Architect | Developer | Data Engineer

Monday, July 16, 2007

Friday, May 18, 2007

Working with a Database in C#

Thursday, March 22, 2007

Normalization

Tuesday, January 30, 2007

Saturday, January 27, 2007