A .NET Application Within Visual Studio .NET

Just to give you a taste of what it is like creating applications in Visual Studio .NET and SQL Server 2000, let's build a simple Windows Forms .NET application. Our example will query customer contact names for customers in the Customers table of the Northwind SQL Server database. Of course, you must have already downloaded .NET and installed it before you can follow along with this example. In case you don't have .NET installed, I've provided enough figures here to give you a very good idea of what the process entails.

Creating a New Project in Visual Studio .NET

Following is a Visual Studio .NET project example that will query customer contact names for customers in the Customers table of the Northwind SQL Server database. It is very easy to re-create, and we suggest you take a stab at it here.

1. Create a new project in Visual Studio .NET by choosing File, New, and then choosing the Project option.

2. When the New Project dialog box appears (see Figure 46.7), choose Visual Basic Projects (or Visual C# Projects) and Windows Applications. Name this project "Unleashed.NET".

   Figure 46.7. Visual Studio .NET New Project dialog box.

   

3. This creates a default form from which you can start.

Adding the Data Connection and Data Adapter

We will be accessing the Customers table in SQL Server's Northwind database, so we will first need to create a data connection and a data adapter to Microsoft SQL Server.

1. From the Data tab of the Toolbox, drag a SQLDataAdapter object into your form (see Figure 46.8).

   Figure 46.8. A Visual Studio .NET Form with a Data Toolbox SqlDataAdapter object selected.

   

2. This will automatically invoke the Data Adapter Configuration Wizard. Both the data connection and the data adapter can be fully configured here.

   1. The wizard starts with the Choose Your Data Connection dialog box (see Figure 46.9). If you already have a connection defined in your project, it will be placed in the dialog box; otherwise , choose to create a new connection and specify the appropriate connection information. (Test the connection as well.)

      Figure 46.9. The Choose Your Data Connection dialog box of the Data Adapter Configuration Wizard.

      

   2. You will have to decide whether to supply SQL statements, build a new stored procedure, or give the name of an existing stored procedure for the data access. In our example we will use the Use SQL Statements option.

   3. You will be presented with a Generate the SQL Statements dialog box where you can simply type in a valid SQL statement. Alternatively, you can use the Query Builder option to formulate the SQL query. What you are really doing is defining what data the data adapter will load into the DataSet . For this example, just type in the following query:

        SELECT CustomerID, ContactName FROM Customers   WHERE (CustomerID = @param1)  

      You should also note that for SqlDataAdapter s, you will use a named parameter ( @param... ) for any values that are to be substituted into the WHERE clause. The OleDBDataAdapter 's SQL statements would use a question mark ( ? ). This dialog box looks like Figure 46.10.

      Figure 46.10. The Generate the SQL Statements dialog box of the Data Adapter Configuration Wizard.

      

   4. The wizard will show you the tasks it has done and indicate whether the SqlDataAdapter has been configured successfully. The details will show you that it generated a SELECT statement, Table mappings, an INSERT statement (which corresponds to the SELECT ), an UPDATE statement (which also corresponds to the SELECT ), and a DELETE statement. Wow!

      After the SqlDataAdapter and DataConnection objects have been configured and added to the form, you must generate a DataSet and then add an instance of this DataSet to the form. We will be binding our TextBox properties to the columns in the DataSet .

   5. Right-click on the SqlDataAdapter ( SqlDataAdapter1 ) that is on your form and choose the Generate Dataset menu option, as shown in Figure 46.11.

      Figure 46.11. Generating the DataSet menu option.

      

   6. Choose to create a new DataSet using the default name that Visual Studio .NET provides ( DataSet1 ). Make sure you have checked the Customers table and checked the box indicating that it will be added to the designer.

      When the process finishes, a DataSet instance named DataSet11 will be on the form and a DataSet schema named DataSet1.xsd will be in the SolutionsExplorer .

Adding Some Controls to Display the Data

The next step is to update the small form example to include a couple of text boxes and a control button. From the Windows Forms tab of the Toolbox, add the following:

• A blank Textbox named txtCustParameter

• A blank Textbox named txtContactName

• A Button named btnGet with the text "Get Contact"

Go ahead and add a label in front of each text box so that the form looks like Figure 46.12.

Figure 46.12. Add text boxes, buttons , and labels to the form.

Adding Code to Populate the DataSet

Now we are ready to complete the application by adding the code to fill the DataSet based on the parameterized value we get from the txtCustParameter text box. This will be plugged into the SQL statement and executed to fill the DataSet (and displayed in the txtContactName text box.

Just double-click on the Get Contact button to create a method for the Click event. You will have to add code to the handler to set the value of the single parameter required by the SQL statement (from txtCustParameter ), make a call to the DataSet 's Clear method to clear the DataSet ( DataSet11 ) between iterations, and call the data adapter's Fill method, passing the reference to the DataSet and the parameter value for the query. The following code is added:

 SqlDataAdapter1.SelectCommand.Parameters("@param1").Value =      txtCustParameter.Text DataSet11.Clear() SqlDataAdapter1.Fill(DataSet11) 

Binding the Text Box to the DataSet

The only thing left to do is to bind the text box to the DataSet and run the application.

1. From the Forms designer, select the txtContactName text box and press F4. This will position you in the Properties window for this text box.

2. Expand the (DataBindings) node in the Properties list and its text property.

3. Expand the DataSet11 and Customers nodes and select the ContactName .

Testing It!

That's it. Now just press the F5 key and test your application by putting in a customer ID value that is in the Customers table. Try the value ALFKI. In Figure 46.13 you can see that the form has successfully retrieved a valid contact name from the Customers database.

Figure 46.13. Form executionretrieving a valid contact name from the Customers table.