Getting Started with ADO.NET

In the coming examples, you'll create a more complex display with a DataGrid, and you'll display data from multiple tables but to get started, you'll keep it as simple as possible. In this first example, you'll create a simple Windows Form with a single ListBox called lbBugs. You'll populate this ListBox with bits of information from the Bugs table in the WindForms_Bugs database.

To get started, you need to create the WindForm_Bugs database based on the description provided previously, or you may download it from our web site. In addition, you may find it convenient to create an ODBC connection known as a Data Source Name (DSN).

To create the DSN, click Start Settings Control Panel. Within the Control Panel, click on Administrative Tools and then on Data Sources (ODBC). The ODBC Data Source Administrator dialog box will open, as shown in Figure 19-7.

Figure 19-7. ODBC Data Source Administrator

figs/pnwa_1907.gif

Click on the System DSN tab, and then click on the Add button. The dialog to select a data source will open as shown in Figure 19-8. Scroll to the bottom and choose SQL Server.

Figure 19-8. Create a data source

figs/pnwa_1908.gif

Enter the name WindForm_bugs and choose your server from the drop-down menu in the next dialog. You will be prompted to identify how SQL Server should verify the authenticity of the login ID. Choose "With SQL Server authentication using a login ID and password entered by the user." In the Login ID, choose sa (the system administrator) and enter the sa password, as shown in Figure 19-9.

Figure 19-9. Entering the login ID

figs/pnwa_1909.gif

On the next dialog box, change the default database to WindForm_Bugs, as shown in Figure 19-10.

Figure 19-10. Changing the default database

figs/pnwa_1910.gif

Accept the defaults on the following dialog and then test the data source you've created on the final dialog, as shown in Figure 19-11. You can now use this DSN in your application.

Figure 19-11. Testing the data source

figs/pnwa_1911.gif

To test your DSN and see how you place data in a Windows application, you'll create a new Windows Application project named SimpleBugListBox. You can create it in either C# or in VB.NET.

Drag a ListBox onto the form and name it lbBugs (that is, change the value of its (Name) property to lbBugs). Stretch the ListBox to fill the form, as shown in Figure 19-12.

Figure 19-12. The ListBox

figs/pnwa_1912.gif

Example 19-2 is the complete source code from the code window.

Example 19-2. A Simple ADO.NET in C#

figs/csharpicon.gif

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
 
namespace SimpleBugListBox
{
 /// 

/// Summary description for Form1. ///

public class Form1 : System.Windows.Forms.Form { private System.Windows.Forms.ListBox lbBugs; ///

/// Required designer variable. ///

private System.ComponentModel.Container components = null; public Form1( ) { // // Required for Windows Form Designer support // InitializeComponent( ); // connect to the Bugs database string connectionString = "server=YourServer; uid=sa; pwd=YourPassword; database=WindForm_Bugs"; // get records from the Bugs table string commandString = "Select BugID, Description from Bugs"; // create the data set command object // and the DataSet SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, connectionString); DataSet dataSet = new DataSet( ); // fill the data set object dataAdapter.Fill(dataSet,"Bugs"); // Get the one table from the DataSet DataTable dataTable = dataSet.Tables[0]; // for each row in the table, display the info foreach (DataRow dataRow in dataTable.Rows) { lbBugs.Items.Add( dataRow["BugID"] + ": " + dataRow["Description"] ); } } ///

/// Clean up any resources being used. ///

protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose( ); } } base.Dispose( disposing ); } #region Windows Form Designer generated code ///

/// Required method for Designer support - do not modify /// the contents of this method with the code editor. ///

private void InitializeComponent( ) { this.lbBugs = new System.Windows.Forms.ListBox( ); this.SuspendLayout( ); // // lbBugs // this.lbBugs.Location = new System.Drawing.Point(24, 16); this.lbBugs.Name = "lbBugs"; this.lbBugs.Size = new System.Drawing.Size(240, 95); this.lbBugs.TabIndex = 0; // // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(292, 273); this.Controls.AddRange(new System.Windows.Forms.Control[ ] { this.lbBugs}); this.Name = "Form1"; this.Text = "Form1"; this.ResumeLayout(false); } #endregion ///

/// The main entry point for the application. ///

[STAThread] static void Main( ) { Application.Run(new Form1( )); } } }

The VB.NET example is nearly identical, except for the constructor, which is shown in Example 19-3.

Example 19-3. Constructor in VB.NET

figs/vbicon.gif

Public Sub New( )
 MyBase.New( )
 
 'This call is required by the Windows Form Designer.
 InitializeComponent( )
 
 Dim connectionString As String
 connectionString = _
 "Server=YourServer; uid=sa; pwd=YourPassword; " & _ 
 database=WindForm_Bugs"
 
 Dim commandString As String
 commandString = "Select BugID, Description from Bugs"
 
 Dim myDataAdapter As New System.Data.SqlClient.SqlDataAdapter(_ 
 commandString, connectionString)
 
 Dim myDataSet As New DataSet( )
 
 myDataAdapter.Fill(myDataSet, "Bugs")
 
 Dim myDataTable As DataTable
 myDataTable = myDataSet.Tables(0)
 
 Dim theRow As DataRow
 For Each theRow In myDataTable.Rows
 lbBugs.Items.Add(theRow("BugID") & ": " & _ 
 theRow("Description"))
 Next
 End Sub

With just about eight lines of code in the form's constructor, you have extracted a set of data from the database and displayed it in the ListBox, as shown in Figure 19-13

Figure 19-13. Displaying the list of bugs

figs/pnwa_1913.gif

The eight lines accomplished the following tasks:

  1. Created the string for the connection. The connection string is whatever string is needed to connect to the database. In the case of our example:

    figs/csharpicon.gif

    string connectionString =
     "server=YourServer; uid=sa; 
     pwd=YourPassword; database=WindForms_Bugs";

    figs/vbicon.gif

    Dim connectionString As String
    connectionString = _
    "Server=YourServer; uid=sa; pwd=YourPassword; " & _ 
     database=WindForm_Bugs"
  2. Created the string for the select statement, which generates a table containing bug IDs and their descriptions:

    figs/csharpicon.gif

    string commandString =
     "Select BugID, Description from Bugs";

    figs/vbicon.gif

    Dim commandString As String
    commandString = "Select BugID, Description from Bugs"
  3. Created the DataAdapter to extract the data from the SQL Server database and pass in the selection and connection strings:

    figs/csharpicon.gif

    SqlDataAdapter dataAdapter =
    new SqlDataAdapter(
    commandString, connectionString);

    figs/vbicon.gif

    Dim myDataAdapter As New System.Data.SqlClient.SqlDataAdapter(_
     commandString, connectionString)
  4. Created a new DataSet object:

    figs/csharpicon.gif

    DataSet dataSet = new DataSet( );

    figs/vbicon.gif

    Dim myDataSet As New DataSet( )
  5. Filled the DataSet with the data obtained from the SQL select statement using the DataAdapter:

    figs/csharpicon.gif

    dataAdapter.Fill(dataSet,"Bugs");

    figs/vbicon.gif

    myDataAdapter.Fill(myDataSet, "Bugs")
  6. Extracted the DataTable from the DataTableCollection object:

    figs/csharpicon.gif

    DataTable dataTable = dataSet.Tables[0];

    figs/vbicon.gif

    Dim myDataTable As DataTable
    myDataTable = myDataSet.Tables(0)
  7. Iterated the rows in the data table to fill the ListBox:

    figs/csharpicon.gif

    foreach (DataRow dataRow in dataTable.Rows)
    {
     lbBugs.Items.Add(
     dataRow["BugID"] + 
     ": " + dataRow["Description"] );
    }

    figs/vbicon.gif

    Dim theRow As DataRow
    For Each theRow In myDataTable.Rows
     lbBugs.Items.Add(theRow("BugID") & ": " & _
     theRow("Description"))
    Next

19.3.1 Using the Wizards

Visual Studio .NET provides extensive wizard support for automating the interaction with the DataBase. The advantage of using wizards is that it can simplify the development process and shield you from the details of database interaction. The disadvantage is that it shields you from the details of database interaction, and thus can leave you vulnerable when things don't work as expected.

This book does not focus on using the wizards; frankly we prefer to write code by hand. However, a quick review will give you a sense of the power of this level of automation. Create a new Windows application and drag a ListBox in place as you have in previous examples. Next, drag a SqlConnection control onto the form. (The SqlConnection control can be found under the Data tab of the Toolbox.) The SqlConnection control will appear in the "tray" below the form, as shown circled in Figure 19-14.

Figure 19-14. Adding a SQLConnection object

figs/pnwa_1914.gif

SQL is pronounced "see-quill" or "ess-que-ell." In this book, we pronounce it as "see-quill" and so write "a SQLConnection" (a see-quillConnection) rather than "an SQLConnection" (an ess-que-ellConnection).

Select the SQLConnection control on the form and click on the connectionString property in the properties window (highlighted in the lower-righthand corner of Figure 19-14) and drop down the list. Click on New Connection and a new connection dialog box appears, as shown in Figure 19-15. You can use the same values used in your ODBC connection.

Figure 19-15. New Connection dialog

figs/pnwa_1915.gif

Drag an SqlCommand control (again from the Data tab of the Toolbox) onto the tray. Drop its Connection property and set it to the existing connection you just created, as shown in Figure 19-16.

Figure 19-16. Hooking the command to the connection

figs/pnwa_1916.gif

Click on the CommandText property. Click on the button with three dots. This brings up the Query Builder, as shown in Figure 19-17. Click on Bugs to choose the Bugs table, click Add, and then close. You will select only from the Bugs table for now.

Figure 19-17. Starting the Query Builder

figs/pnwa_1917.gif

Within the Query Builder, you can select the columns you want to display and control sorting and filtering. Check BugID and Description to include these columns in the query results (as you did manually in the previous example), as shown in Figure 19-18.

Figure 19-18. Building the query

figs/pnwa_1918.gif

You are ready now to return to your code to use the command and connection objects. Enter the code-editing window, and you'll find that Visual Studio .NET has added two member variables to your class:

figs/csharpicon.gif

private System.Data.SqlClient.SqlConnection sqlConnection1;
private System.Data.SqlClient.SqlCommand sqlCommand1;

These components are initialized in the InitializeComponent section of the code (normally collapsed). Click the + sign next to this block of code to expand it. You'll find that the Connection and Command objects are instantiated and initialized, as shown in this excerpt:

figs/csharpicon.gif

this.sqlConnection1 = new System.Data.SqlClient.SqlConnection( );
this.sqlCommand1 = new System.Data.SqlClient.SqlCommand( );
this.sqlConnection1.ConnectionString = "data source=YourServer;initial 
 catalog=WindForm_Bugs; password=YourPassword;persist security info=True;
 user id=sa;workstation id=YOURSERVER;packet size=4096";
this.sqlCommand1.CommandText = "SELECT BugID, Description FROM Bugs";
this.sqlCommand1.Connection = this.sqlConnection1;

Associate the command object with the data adapter explicitly:

figs/csharpicon.gif

SqlDataAdapter dataAdapter = new SqlDataAdapter( );
dataAdapter.SelectCommand = sqlCommand1;
dataAdapter.TableMappings.Add("Table", "Bugs");

Don't forget to add:

figs/csharpicon.gif

using System.Data.SqlClient;

to the top of the file.

The complete constructor is shown in:

figs/csharpicon.gif

public Form1( )
{
 InitializeComponent( );
 
 DataSet DataSet = new DataSet( );
 
 SqlDataAdapter dataAdapter = new SqlDataAdapter( );
 dataAdapter.SelectCommand = sqlCommand1;
 dataAdapter.TableMappings.Add("Table", "Bugs");
 
 // fill the data set object
 dataAdapter.Fill(DataSet,"Customers");
 
 // Get the one table from the DataSet
 DataTable dataTable = DataSet.Tables[0];
 
 // for each row in the table, display the info
 foreach (DataRow dataRow in dataTable.Rows)
 {
 lbBugs.Items.Add(
 dataRow["BugID"] + 
 ": " + dataRow["Description"] );
 }
}

You can see that the wizards have simplified the process considerably.

19.3.2 Command and Control Objects

Rather than using the wizards, you can create the command and control objects programmatically as shown in Example 19-4 in C# and in Example 19-5 in VB.NET.

Much of the code generated by Visual Studio .NET is left out of this listing to save space.

 

Example 19-4. Explicit command and connection objects (C#)

figs/csharpicon.gif

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
 
namespace CommandObjectCS
{
 public class Form1 : System.Windows.Forms.Form
 {
 private System.Data.SqlClient.SqlConnection myConnection;
 private System.Data.DataSet myDataSet;
 private System.Data.SqlClient.SqlCommand myCommand;
 private System.Data.SqlClient.SqlDataAdapter myDataAdapter;
 private System.Windows.Forms.ListBox lbBugs;
 /// 

/// Required designer variable. ///

private System.ComponentModel.Container components = null; public Form1( ) { // // Required for Windows Form Designer support // InitializeComponent( ); string connectionString = "server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs"; // create and open the myConnection object myConnection = new System.Data.SqlClient.SqlConnection(connectionString); myConnection.Open( ); // create the dataset, set property myDataSet = new System.Data.DataSet( ); myDataSet.CaseSensitive=true; // get records from the Bugs table string commandString = "Select BugID, Description from Bugs"; myCommand = new System.Data.SqlClient.SqlCommand( ); myCommand.Connection=myConnection; myCommand.CommandText= commandString; myDataAdapter = new SqlDataAdapter( ); myDataAdapter.SelectCommand = myCommand; myDataAdapter.TableMappings.Add("Table", "Bugs"); myDataAdapter.Fill(myDataSet); // Get the one table from the DataSet DataTable myDataTable = myDataSet.Tables[0]; // for each row in the table, display the info foreach (DataRow dataRow in myDataTable.Rows) { lbBugs.Items.Add( dataRow["BugID"] + ": " + dataRow["Description"] ); } } } }

Example 19-5. Explicit command and connection objects (VB.NET)

figs/vbicon.gif

Imports System.Data
Imports System.Data.SqlClient
 
Public Class Form1
 Inherits System.Windows.Forms.Form
 
 Private myConnection As System.Data.SqlClient.SqlConnection
 Private myDataSet As System.Data.DataSet
 Private myCommand As System.Data.SqlClient.SqlCommand
 Private myDataAdapter As System.Data.SqlClient.SqlDataAdapter
 
#Region " Windows Form Designer generated code "
#End Region
 
 Public Sub New( )
 MyBase.New( )
 
 'This call is required by the Windows Form Designer.
 InitializeComponent( )
 
 Dim connectionString As String
 connectionString = _
 "Server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs"
 
 myConnection = _
 New System.Data.SqlClient.SqlConnection(connectionString)
 myConnection.Open( )
 
 myDataSet = New System.Data.DataSet( )
 myDataSet.CaseSensitive = True
 
 
 Dim commandString As String
 commandString = "Select BugID, Description from Bugs "
 
 myCommand = New System.Data.SqlClient.SqlCommand( )
 myCommand.Connection = myConnection
 myCommand.CommandText = commandString
 
 myDataAdapter = New SqlDataAdapter( )
 myDataAdapter.SelectCommand = myCommand
 myDataAdapter.TableMappings.Add("Table", "Bugs")
 myDataAdapter.Fill(myDataSet)
 
 Dim myDataTable As DataTable
 myDataTable = myDataSet.Tables(0)
 
 Dim dataRow As DataRow
 For Each dataRow In myDataTable.Rows
 lbBugs.Items.Add(dataRow("BugID") & ": " & _
 dataRow("Description"))
 Next
 End Sub
End Class

In Example 19-4 and Example 19-5, start by creating four new instance members for the Form class:

figs/csharpicon.gif

private System.Data.SqlClient.SqlConnection myConnection;
private System.Data.DataSet myDataSet;
private System.Data.SqlClient.SqlCommand myCommand;
private System.Data.SqlClient.SqlDataAdapter myDataAdapter;

figs/vbicon.gif

Private myConnection As System.Data.SqlClient.SqlConnection
Private myDataSet As System.Data.DataSet
Private myCommand As System.Data.SqlClient.SqlCommand
Private myDataAdapter As System.Data.SqlClient.SqlDataAdapter

The connection is created by instantiating a SQLConnection object with the connection string:

figs/csharpicon.gif


 

figs/csharpicon.gif

myConnection = new System.Data.SqlClient.SqlConnection(connectionString);

The VB.NET is identical, except that you leave off the semicolon.

The connection is explicitly opened:

figs/vbicon.gif

myConnection.Open( )

You can hang on to this connection object and reuse it, as you'll see in later examples. This connection can also be used for transactions, as described in Chapter 20.

Next, create the DataSet object and set its CaseSensitive property to true to indicate that string comparisons within DataTable objects are case sensitive:

figs/vbicon.gif

myDataSet = New System.Data.DataSet( )
myDataSet.CaseSensitive = True

Create the SqlCommand object and give that new command object the connection object and the text for the command:

figs/vbicon.gif

myCommand = New System.Data.SqlClient.SqlCommand( )
myCommand.Connection = myConnection
myCommand.CommandText = commandString

Finally, create the SqlDataAdapter object and assign to it the SqlCommand object you just created. Then tell the DataSet how to map the table columns and instruct the SqlDataAdapter to fill the DataSet:

figs/vbicon.gif

myDataAdapter = New SqlDataAdapter( )
myDataAdapter.SelectCommand = myCommand
myDataAdapter.TableMappings.Add("Table", "Bugs")
myDataAdapter.Fill(myDataSet)





Programming. NET Windows Applications
Programming .Net Windows Applications
ISBN: 0596003218
EAN: 2147483647
Year: 2003
Pages: 148
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net