Chapter 4: Querying Your Database

Creating Commands in Visual Studio .NET

Visual Studio .NET can save you time, effort, and quite a few headaches by helping you create and configure Command objects quickly and easily. Let's look at the Visual Studio .NET design-time features for working with Command objects.

Dragging and Dropping from the Toolbox

The primary starting point for creating Command objects in Visual Studio .NET is the Toolbox. On the Data tab, you'll find Command objects for each of the .NET data providers. To create an OleDbCommand, you simply drag-and-drop the OleDbCommand item from the Toolboxonto your design surface or to the design surface's components tray. You'll have a new Command object in your designer's components tray, as shown in Figure 4-1.

Figure 4-1

Dragging and dropping an OleDbCommand item from the Toolboxto create a new Command

As you learned earlier in this chapter, you must specify a Connection and a query string in order to create a useful Command object. Visual Studio .NET can help you set the Connection and CommandText properties of your new Command at design time.

Specifying a Connection

Once you've added a Command to your components tray, you'll want to set its Connection property to a Connection object. If you select the Command object's Connection property in the Properties window, you'll get a drop-down list of choices. You can opt to use an existing Connection, create a new Connection, or leave the Connection property blank (none). Figure 4-2 shows a Windows formwith an OleDbConnection and an OleDbCommand in its components tray. The OleDbCommand is selected, and the Properties windowshows how you can set the object's Connection property to the existing OleDbConnection.

Figure 4-2

Setting a Command object's Connection property

If you select New from the drop-down list, Visual Studio .NET will open the Data Link Properties dialog box, where you can create a new connection string. Visual Studio .NET will add a new Connection object using the connection string you build and set your Command object's Connection property to this new Connection object.

Using Query Builder

Visual Studio .NET simplifies the process of building your query string through Query Builder. Select a Command object in your designer's components tray, and then select the object's CommandText property in the Properties window. You'll see a button that indicates that this property has its own property page. Click this button to invoke the Query Builder dialog box, shown in Figure 4-3.

Query Builder offers a simple graphical user interface to help you build queries. When it launches, Query Builder prompts you to select the tables, views, and functions you want to access in your query, as shown in Figure 4-3. As you select objects, you'll see them added to the design surface behind the Add Table dialog box.

Figure 4-3

Selecting tables for your query

After you specify the tables you want to access in your query, Query Builder helps you graphically select the columns, apply filters and sorting order, and so forth. In Figure 4-4, we've selected the Customers table and specified the columns from the table that we want to fetch in the query. Notice that we've also specified a parameter for the CustomerID column.

Figure 4-4.

Using Query Builder to select columns and criteria for a query

The Query Builder dialog boxhas three design panes. The top pane provides a simple way to select columns. The second pane simplifies the process of adding filters and sort orders to the result set. The third pane contains the text of the query you've built. Changes you make in one pane affect the other two. You can right-click in the text pane and choose Verify from the shortcut menu to find out whether the query you've built is valid.

Another handy command on the shortcut menu, Run, runs the query you've built and displays the results in the bottom pane. If you've created a parameterized query, Query Builder will display a dialog box to let you specify values for the parameters. Query Builder also contains logic that enables you to edit the data in the results pane to modify the contents of your database.

Using Your New Command Object in Code

We've successfully created and configured our Command object based on the following query:

SELECT CustomerID, CompanyName, ContactName, ContactTitle        FROM Customers WHERE CustomerID LIKE ?

Before we add code to execute our Command and fetch its results, drag-and-drop a list box item from the Toolboxonto your form. Modify its size to take up most of the form. We'll use this list box to display the contents of the CompanyName column for the rows we retrieve.

Double-click on the form to enter the form's Load event. Now we want to execute the Command, fetch the results through a DataReader, and display the contents of the CompanyName column in our list box. Even though we specified a parameter for the CustomerID column, we can supply the wildcard character % so the query will return all customers.

Visual Basic .NET developers can add the code shown in Figure 4-5.

Figure 4-5

Using the Command object at run time in Visual Basic .NET

Visual C# .NET developers can add the following code:

//Open the connection. oleDbConnection1.Open(); //Specify the wildcard for the parameter to retrieve all customers. oleDbCommand1.Parameters[0].Value = "%"; //Execute the query and display all CompanyNames in the list box. OleDbDataReader rdr = oleDbCommand1.ExecuteReader(); while (rdr.Read())     listBox1.Items.Add(rdr["CompanyName"]); //Close the DataReader and Connection. rdr.Close(); oleDbConnection1.Close();

Visual C# .NET developers, don't forget to add using System.Data.OleDb; to the using block at the top of the form.

Run the project, and you'll see the list box filled with the names of the companies in the Customers table.

Dragging and Dropping from Server Explorer

If you're basing a Command on a stored procedure call, you can create and configure your Command object by dragging and dropping the stored procedure from Server Explorer onto your design surface. Dragging and dropping a SQL Server stored procedure creates a SqlCommand, and using a stored procedure from other data sources creates an OleDbCommand.

In Figure 4-6, we've dragged the SQL Server Northwind database's CustOrderHist stored procedure onto a Windows form. You can see in the Properties window that the CommandText, CommandType, and Connection properties are set so that you can call this stored procedure easily through code.

Figure 4-6

Creating Command objects based on stored procedures

note

Visual Studio .NET adds delimiters to the name of the stored procedure so that you don't run into problems calling stored procedures that have odd characters such as spaces in their names.

Visual Studio .NET has also populated the new Command object's Parameters collection. Select the Parameters collection in the Properties window, and then click the button to the right to launch its property page, as shown in Figure 4-7.

Figure 4-7

Viewing the Parameters collection for a stored procedure Command

Visual Studio .NET's data tools query your database for schema information about the stored procedure in order to populate the Command object's Parameters collection. However, many databases, such as SQL Server, don't differentiate between input/output and output-only parameters. As a result, if you want to call a stored procedure that uses output parameters, you might need to set the direction for those parameters by hand in the Properties window.

note

Dragging and dropping a table or view from Server Explorer creates a DataAdapter rather than a Command. We'll discuss DataAdapter objects in the next chapter.



Microsoft ADO. NET Core Reference
Microsoft ADO.NET (Core Reference) (PRO-Developer)
ISBN: 0735614237
EAN: 2147483647
Year: 2002
Pages: 104
Authors: David Sceppa

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