Apply Your Knowledge

   

---

Exercises

1.1 Preselecting Data with Parameterized Stored Procedures

One of the biggest issues in working with server-side data such as SQL Server data is to minimize the amount of data that you load into your application. That's because communications with such servers are typically comparatively slow, whereas the servers themselves have enough processing power to quickly locate the exact data that you want. In this exercise, you'll see how you can minimize the amount of data retrieved by using a series of stored procedures with parameters

Estimated Time : 30 minutes.

1. Create a new Visual Basic .NET project to use for the Exercises in this chapter.

2. Add a new form to the project.

3. Place a ComboBox control named cboCustomers, a Button control named btnLoad, and a DataGrid control named dgMain on the form.

4. Double-click the button to open the form's module. Enter these statements at the top of the module:

    Imports System.Data Imports System.Data.SqlClient 

5. Use a tool such as SQL Query Analyzer to create this stored procedure:

    CREATE PROC procCustomerList AS SELECT CustomerID, CompanyName FROM Customers ORDER BY CompanyName 

6. Use a tool such as SQL Query Analyzer to create this stored procedure:

    CREATE PROC procCustomerDetails   @CustomerID char(5) AS SELECT * FROM Customers WHERE CustomerID = @CustomerID 

7. Use a tool such as SQL Query Analyzer to create this stored procedure:

    CREATE PROC procOrdersForCustomer   @CustomerID char(5) AS SELECT * FROM Orders WHERE CustomerID = @CustomerID 

8. To minimize load time, the form will start by loading only the customer list into the ComboBox control. Enter this code to load the customer list:

    Dim mcnn As SqlConnection = _  New SqlConnection(_  "Data Source=(local);" & _  "Initial Catalog=Northwind; " & _  "Integrated Security=SSPI") Private Sub Exercise1_1_Load(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) _  Handles MyBase.Load     ' Load the customer list     Dim cmdCustomers As SqlCommand = _      mcnn.CreateCommand     cmdCustomers.CommandType = _      CommandType.StoredProcedure     cmdCustomers.CommandText = _      "procCustomerList"     mcnn.Open()     Dim ds As DataSet = New DataSet()     Dim da As SqlDataAdapter = _      New SqlDataAdapter()     da.SelectCommand = cmdCustomers     da.Fill(ds, "Customers")     With cboCustomers        .DataSource = ds.Tables("Customers")        .DisplayMember = "CompanyName"        .ValueMember = "CustomerID"     End With     mcnn.Close() End Sub 

9. When the user clicks the Load button, you'll use the other stored procedures to load only the data of interest. Enter this code to build the DataSet and bind it to the DataGrid:

    Private Sub btnLoad_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) _  Handles btnLoad.Click     ' Create a new DataSet     Dim ds As DataSet = New DataSet()     ' Load only the customer of interest     Dim cmdCustomer As SqlCommand = _      mcnn.CreateCommand()     cmdCustomer.CommandType = _      CommandType.StoredProcedure     cmdCustomer.CommandText = _      "procCustomerDetails"     cmdCustomer.Parameters.Add(New _      SqlParameter("@CustomerID", _      SqlDbType.Text, 5))     cmdCustomer.Parameters(_      "@CustomerID").Value = _      cboCustomers.SelectedValue     Dim daCustomer As SqlDataAdapter = _      New SqlDataAdapter()     daCustomer.SelectCommand = cmdCustomer     daCustomer.Fill(ds, "Customers")     ' Load the orders for this customer     Dim cmdOrders As SqlCommand = _      mcnn.CreateCommand()     cmdOrders.CommandType = _      CommandType.StoredProcedure     cmdOrders.CommandText = _      "procOrdersForCustomer"     cmdOrders.Parameters.Add(New _      SqlParameter("@CustomerID", _      SqlDbType.Text, 5))     cmdOrders.Parameters(_      "@CustomerID").Value = _      cboCustomers.SelectedValue     Dim daOrders As SqlDataAdapter = _      New SqlDataAdapter()     daOrders.SelectCommand = cmdOrders     daOrders.Fill(ds, "Orders")     ' Relate the two DataTables     Dim relCustOrder As DataRelation = _      ds.Relations.Add("CustOrder", _      ds.Tables("Customers").Columns(_      "CustomerID"), _      ds.Tables("Orders").Columns(_      "CustomerID"))     ' Bind the data to the user interface     dgMain.DataSource = ds     dgMain.DataMember = "Customers" End Sub 

10. Set the form as the startup form for the project.

11. Run the project. Select a customer from the list in the ComboBox and then press the Load button. The form will display only the information for that customer. Click on the + sign next to the customer, and then click the CustOrder hyperlink, to see the order information, as shown in Figure 1.29.

    Figure 1.29. Retrieving data on a particular customer and their orders.

    

1.2 Using a Schema with a Stored Procedure

You saw both schema files and stored procedures in this chapter, but you didn't see how to use the two of them together. In this exercise, you'll use a SQL Server stored procedure to create both a schema file and the corresponding Command object, and you'll see how to connect them to one another.

Estimated Time : 15 minutes.

1. Create a new Visual Basic .NET project to use for the Exercises in this chapter.

2. Right-click on the project in Solution Explorer and select Add, Add New Item. Add a new DataSet object to the project and name it dsOrdersForCustomer.xsd.

3. Expand the Server Explorer treeview to show a Data Connection to the Northwind sample database. Drill into the Stored Procedures folder within this database.

4. Drag the procOrdersForCustomer stored procedure from Server Explorer and drop it on the DataSet schema designer. This will create a new element with all the necessary included elements to represent the stored procedure.

5. Add a new form to the project.

6. Place a ComboBox control named cboCustomers, a Button control named btnLoad, and a DataGrid control named dgMain on the form.

7. Double-click the button to open the form's module. Enter these statements at the top of the module:

    Imports System.Data Imports System.Data.SqlClient 

8. Drag the procOrdersForCustomer stored procedure from Server Explorer and drop it on the form. This will create a SqlDataAdapter object and a SqlCommand object.

9. Select the SqlCommand object and change its name to cmdOrdersForCustomer.

10. Enter this code to load the customer list:

     Private Sub Exercise1_2_Load(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) _  Handles MyBase.Load     ' Load the customer list     Dim cmdCustomers As SqlCommand = _      SqlConnection1.CreateCommand     cmdCustomers.CommandType = _      CommandType.StoredProcedure     cmdCustomers.CommandText = _      "procCustomerList"     SqlConnection1.Open()     Dim ds As DataSet = New DataSet()     Dim da As SqlDataAdapter = _      New SqlDataAdapter()     da.SelectCommand = cmdCustomers     da.Fill(ds, "Customers")     With cboCustomers        .DataSource = ds.Tables("Customers")        .DisplayMember = "CompanyName"        .ValueMember = "CustomerID"     End With     SqlConnection1.Close() End Sub 

11. Enter this code to build the DataSet and bind it to the DataGrid:

     Private Sub btnLoad_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) _  Handles btnLoad.Click     ' Create a new DataSet     Dim ds As dsOrdersForCustomer = _      New dsOrdersForCustomer()     ' Load only the data of interest     cmdOrdersForCustomer.Parameters(_      "@CustomerID").Value = _      cboCustomers.SelectedValue     Dim daCustomer As SqlDataAdapter = _      New SqlDataAdapter()     daCustomer.SelectCommand = _      cmdOrdersForCustomer     daCustomer.Fill(ds, "Orders")     ' Bind the data to the user interface     dgMain.DataSource = ds     dgMain.DataMember = "Orders" End Sub 

12. Set the form as the startup form for the project.

13. Run the project. Select a customer from the list in the ComboBox and then press the Load button. The form will display only the orders for that customer.

Review Questions

1:	What is metadata?
A1:	Metadata is information that describes data. For example, an XML schema file is data that describes the structure of other data.
2:	What is the difference between an XML element and an XML attribute?
A2:	An element is a standalone XML entity. An attribute is a value that further describes an element.
3:	What is the use of a simple type in the XML Schema Designer?
A3:	The simple type in the XML Schema Designer is useful for describing data with restrictions. For example, an integer that must be between 0 and 50 could be represented by a simple type.
4:	What is a facet in an XML schema?
A4:	A facet is a piece of information, such as minimum length, describing an XML simple type.
5:	When should you use one-to-many relationships instead of nested relationships in an XML schema?
A5:	One-to-many relationships are most useful in an XML schema when the schema is being used in conjunction with a relational database.
6:	Describe the difference between an ad hoc query and a stored procedure.
A6:	An ad hoc query consists of SQL statements that are sent to the server. A stored procedure consists of SQL statements permanently stored on the server.
7:	List and describe the four basic T-SQL statements.
A7:	The SELECT statement retrieves data; the UPDATE statement updates existing data; the INSERT statement adds new data; and the DELETE statement deletes data.
8:	Name four ways to execute SQL statements.
A8:	Using the Visual Studio IDE, through osql, through SQL Query Analyzer, or with your own home-grown solutions.
9:	In a T-SQL SELECT statement, what is the difference between the WHERE clause and the HAVING clause?
A9:	The WHERE clause restricts the output of the statement. The HAVING clause restricts the rows used as input to an aggregate.
10:	What is the purpose of the @@IDENTITY variable?
A10:	The @@IDENTITY variable returns the last identity value to have been assigned to a table.

Exam Questions

1:	You are creating a schema file to represent the Vehicles table in your database. You need to represent a field named SerialNo in this schema. Data entered in the SerialNo field must have precisely 14 digits. What should you use to represent this field in the XML schema? Complex type Simple type Element Attribute
A1:	B. A simple type can be modified by facets, which allow you to specify data restrictions such as minimum, maximum, or exact length.
2:	Your application contains a DataSet schema file that represents students taking a laboratory course. Each student is assigned a unique student ID when they enroll in the university. Each student is also assigned to a lab bench, and no two students can be assigned to the same lab bench. What XML settings should you use for the StudentID and LabBench rows in the schema file? Mark StudentID as a primary key, and mark LabBench as a primary key. Mark StudentID as a unique key, and mark LabBench as a primary key. Mark StudentID as a primary key, and mark LabBench as a unique key. Mark StudentID as a unique key, and mark LabBench as a unique key.
A2:	C. A schema file can only have a single primary key. You should choose as the primary key the element that most distinctly identifies the entity that is represented by the schema file. Unique keys can then be used to enforce uniqueness on other elements.
3:	You are developing an XML schema file for an application that will retrieve data from an Oracle database. The database includes an Orders table and a LineItems table. Each order has one or more line items associated with it. You've already created the Orders table within the XML schema file. How should you represent the LineItems table within the XML schema file? Add LineItems as a separate table. Use a one-to-many relationship to relate the LineItems table to the Orders table. Add LineItems as a separate table. Use a nested relationship to relate the LineItems table to the Orders table. Add LineItems as a simple type. Add a row using this simple type to the Orders table. Add LineItems as an element. Add a row using this element to the Orders table.
A3:	A. For data stored in a relational database, one-to-many relationships provide the most natural mapping in an XML schema file.
4:	Your application includes a strongly typed DataSet named dsWarehouse. You have extracted a table named Inventory from the DataSet to a DataTable named dt. The Inventory table contains a column named StockOnHand. Which syntax can you use to refer to a value in this column? (Select two.) dt.Rows(0).StockOnHand dt.Rows(0)."StockOnHand" dt.Rows(0)("StockOnHand") dt.Rows(0).Item.StockOnHand
A4:	A, C. The early-bound syntax of answer A is only possible with a strongly typed DataSet. But you're not required to use early binding with strongly typed DataSets, so the late bound syntax of answer C will also work.
5:	You are developing an XML schema file for an application that will retrieve data from Web applications developed by your company's trading partners . These Web applications use XML as their native format. The information you will retrieve includes a list of parts and a list of prices. Each part has one or more prices associated with it. You've already created the Parts table within the XML schema file. How should you represent the Prices table within the XML schema file? Add Prices as a separate table. Use a one-to-many relationship to relate the Prices table to the Parts table. Add Prices as a separate table. Use a nested relationship to relate the Prices table to the Parts table. Add Prices as a simple type. Add a row using this simple type to the Parts table. Add Prices as an element. Add a row using this element to the Parts table.
A5:	B. For data coming from XML sources, nested relationships generally provide the most natural mapping in an XML schema file.
6:	You have been assigned the task of developing an XML schema file to serve as part of the data layer of your company's new Web application. The application will deliver details on sporting events to subscribers. Each sporting event is characterized by several pieces of information including the date, sport, and teams involved. What should you use to represent a sporting event in the XML schema? Complex type Simple type Element Attribute
A6:	A. Because the sporting event will contain several pieces of information, it is best represented as a complex type. The constituent pieces of information within this type can be represented by elements, attributes, or simple types.
7:	You are developing an XML schema file that will hold information about your company's customers. Which of these data restrictions can you represent by a facet in the schema? The values of the CustomerType element are limited to "Active" and "Inactive." The values of the CustomerID element must be four characters long for active customers and five characters long for inactive customers. Each customer may have one or more representatives. Each customer is associated with precisely one company.
A7:	A. You can represent a fixed set of choices within a simple type by using the enumeration facet. Facets are not suited for representing relationships between elements within a table or for representing relationships between different tables.
8:	You are writing a SQL query to retrieve information about all customers whose offices are in Germany. You have written the following SQL statement: SELECT * FROM Customers WHERE Country = Germany When you execute this query, you receive an error message: Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'Germany'. How should you modify the SQL statement to fix this problem? SELECT * FROM Customers WHERE Country = [Germany] SELECT * FROM Customers WHERE Country = #Germany# SELECT * FROM Customers WHERE Country = "Germany" SELECT * FROM Customers WHERE Country = 'Germany'
A8:	D. In T-SQL for SQL Server, you use single quotes to surround a literal value.
9:	You are using a SQL INSERT statement to insert records in a table named Products. The Products table has the following structure: ProductID integer, identity, no default value, cannot be null ProductName varchar(50), no default value, cannot be null UnitPrice money, default value 10.00, cannot be null Color varchar(10), no default value, can be null Which column must you explicitly specify in the INSERT statement? ProductID ProductName UnitPrice Color
A9:	B. An INSERT statement must specify explicit values for any columns that are not identity columns, are not nullable, and do not have a default value.
10:	You are designing an application that will manage customers and their orders. Which of the following situations is not a good candidate for implementation with stored procedures? Retrieving the list of all customers in the database. Retrieving the list of all orders for a particular customer. Inserting a new order into the Orders table. Free-form querying by the database administrator.
A10:	C. Stored procedures can be used to carry out any SQL statement. These statements can have parameters, and they need not be SELECT statements. However, stored procedures are not useful for executing completely free-form SQL.
11:	Your application needs to return the total number of customers in the database. What is the fastest way to do this? Write ad hoc SQL to return the total number of customers. Use the SqlCommand.ExecuteScalar method to execute the SQL statement. Write ad hoc SQL to return the total number of customers. Use the SqlDataAdapter.Fill method to execute the SQL statement. Create a stored procedure to return the total number of customers. Use the SqlCommand.ExecuteScalar method to execute the stored procedure. Create a stored procedure to return the total number of customers. Use the SqlDataAdapter.Fill method to execute the stored procedure.
A11:	C. Stored procedures execute faster than the corresponding ad hoc SQL statements because stored procedures are stored in the database in compiled form. The ExecuteScalar method is faster than filling a DataSet for returning a single value.
12:	Your SQL Server database contains a table, Sales, with these columns: SalesID (int, identity) StoreNumber (int) DailySales (int) You want to see a list of each store with its total daily sales. The list should be filtered to only include stores whose total daily sales are more than 10. Which SQL statement should you use? SELECT StoreNumber, DailySales FROM Sales WHERE DailySales > 10 SELECT StoreNumber, SUM(DailySales) FROM Sales WHERE DailySales > 10 GROUP BY StoreNumber SELECT StoreNumber, SUM(DailySales) FROM Sales GROUP BY StoreNumber HAVING SUM(DailySales) > 10 SELECT StoreNumber, SUM(DailySales) FROM Sales WHERE DailySales > 10 GROUP BY StoreNumber HAVING SUM(DailySales) > 10
A12:	C. The GROUP BY clause is required to obtain aggregate numbers . The HAVING clause filters the results after the aggregation has been performed. The answers containing the WHERE clause are incorrect because WHERE filters the input to the aggregations.
13:	Your SQL Server database contains a table, Sales, with these columns: SalesID (int, identity) StoreNumber (int) DailySales (int) You want to see a list of each store with its total daily sales. The list should be filtered to only include rows from the table where the daily sales are more than 10. Which SQL statement should you use? SELECT StoreNumber, DailySales FROM Sales WHERE DailySales > 10 SELECT StoreNumber, SUM(DailySales) FROM Sales WHERE DailySales > 10 GROUP BY StoreNumber SELECT StoreNumber, SUM(DailySales) FROM Sales GROUP BY StoreNumber HAVING SUM(DailySales) > 10 SELECT StoreNumber, SUM(DailySales) FROM Sales WHERE DailySales > 10 GROUP BY StoreNumber HAVING SUM(DailySales) > 10
A13:	B. The GROUP BY clause is required to obtain aggregate numbers. The WHERE clause filters rows before aggregating them. The answers containing the HAVING clause are incorrect because HAVING filters the results after aggregation.
14:	Your SQL Server database contains a table, Experiments, with the following columns: ExperimentID (int, identity) ExperimentType (char(1)) ExperimentDate (datetime) You wish to delete all rows from the table in which the ExperimentType value is either "A" or "C." You do not wish to delete any other rows. Which SQL statement should you use? DELETE FROM Experiments WHERE ExperimentType LIKE '[AC]' DELETE FROM Experiments WHERE ExperimentType LIKE '[A-C]' DELETE FROM Experiments WHERE ExperimentType LIKE 'A' OR 'C' DELETE * FROM Experiments WHERE ExperimentType IN ('A', 'C')
A14:	A. Answer B would also delete rows with an ExperimentType of B. Answer C would take the OR of "A" and "C" before evaluating the LIKE clause. DELETE * is not valid T-SQL syntax.
15:	Your SQL Server database contains a table, Sales, with these columns: SalesID (int, identity) StoreNumber (int) DailySales (int) You wish to create a stored procedure that accepts as inputs the store number and daily sales, inserts a new row in the table with this information, and returns the new identity value. Which SQL statement should you use? CREATE PROCEDURE procInsertSales @StoreNumber int, @DailySales int, @SalesID int AS INSERT INTO Sales (StoreNumber, DailySales) VALUES (@StoreNumber, @DailySales) SELECT @SalesID = @@IDENTITY CREATE PROCEDURE procInsertSales @StoreNumber int, @DailySales int, @SalesID int OUTPUT AS INSERT INTO Sales (SalesID, StoreNumber, DailySales) VALUES (@SalesID, @StoreNumber, @DailySales) CREATE PROCEDURE procInsertSales @StoreNumber int, @DailySales int, @SalesID int OUTPUT AS INSERT INTO Sales (SalesID, StoreNumber, DailySales) VALUES (0, @StoreNumber, @DailySales) SELECT @SalesID = @@IDENTITY CREATE PROCEDURE procInsertSales @StoreNumber int, @DailySales int, @SalesID int OUTPUT AS INSERT INTO Sales (StoreNumber, DailySales) VALUES (@StoreNumber, @DailySales) SELECT @SalesID = @@IDENTITY
A15:	D. Answer A does not indicate that @SalesID is an output parameter. Answers B and C attempt to insert values into the identity column, rather than letting SQL Server assign the new value.

Suggested Readings and Resources

1. Visual Studio .NET Combined Help Collection

Accessing Data

Building and Editing XML Schema

2. Delaney, Kalen. Inside SQL Server 2000 . Microsoft Press, 2000.

3. Gunderloy, Mike. ADO and ADO.NET Programming . Sybex, 2002.

4. SQL Server Books Online

Transact-SQL Reference

5. Wahlin, Dan. XML For ASP.NET Developers . Sams, 2002.

---

Top