Apply Your Knowledge -CodeIdol

Apply Your Knowledge

Exercises

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 an application. Communication with such servers is typically comparatively slow, and 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.

Create a new Visual C# .NET project to use for the Exercises in this chapter. Name the project 320C01Exercises.
Add a new form to the project.
Place a ComboBox control (cboCustomers), a Button control (btnLoad), and a DataGrid control (dgMain) on the form.
Switch to the code view and add the following using directives to make the ADO.NET objects available:
```
using System.Data;

using System.Data.SqlClient;
```

Using a tool such as the SQL Query Analyzer or the Visual Studio .NET IDE, create this stored procedure:


CREATE PROC procCustomerList

AS

SELECT CustomerID, CompanyName

FROM Customers

ORDER BY CompanyName

Using a tool such as the SQL Query Analyzer or the Visual Studio .NET IDE, create this stored procedure:


CREATE PROC procCustomerDetails

  @CustomerID char(5)

AS

SELECT * FROM Customers

WHERE CustomerID = @CustomerID

Using a tool such as the SQL Query Analyzer or the Visual Studio .NET IDE, create this stored procedure:


CREATE PROC procOrdersForCustomer

  @CustomerID char(5)

AS

SELECT * FROM Orders

WHERE CustomerID = @CustomerID

To minimize load time, the form starts by loading only the customer list into the ComboBox control. Enter this code to load the customer list in the form's Load event handler:


SqlConnection cnn = new SqlConnection(

   "Data Source=(local); " +

   "Initial Catalog=Northwind;" +

   " Integrated Security=SSPI");



private void Exercise1_1_Load(

   object sender, System.EventArgs e)

{

    // Load the customer list

    SqlCommand cmdCustomers =

        cnn.CreateCommand();

    cmdCustomers.CommandType =

        CommandType.StoredProcedure;

    cmdCustomers.CommandText =

        "procCustomerList";

    cnn.Open();

    DataSet ds = new DataSet();

    SqlDataAdapter da =

        new SqlDataAdapter();

    da.SelectCommand = cmdCustomers;

    da.Fill(ds, "Customers");

    cboCustomers.DataSource =

        ds.Tables["Customers"];

    cboCustomers.DisplayMember =

        "CompanyName";

    cboCustomers.ValueMember =

        "CustomerID";

    cnn.Close();

}

When the user clicks the Load button, the other stored procedures should load only the data of interest. Enter this code to build the DataSet object and bind it to the DataGrid control in the btnLoad Click event handler:


private void btnLoad_Click(

   object sender, System.EventArgs e)

{

    // Create a new DataSet

    DataSet ds = new DataSet();

    // Load only the customer of interest

    SqlCommand cmdCustomer =

        cnn.CreateCommand();

    cmdCustomer.CommandType =

        CommandType.StoredProcedure;

    cmdCustomer.CommandText =

        "procCustomerDetails";

    cmdCustomer.Parameters.Add(

        new SqlParameter(

        "@CustomerID", SqlDbType.Text, 5));

    cmdCustomer.Parameters[

        "@CustomerID"].Value =

        cboCustomers.SelectedValue;

    SqlDataAdapter daCustomer =

        new SqlDataAdapter();

    daCustomer.SelectCommand = cmdCustomer;

    daCustomer.Fill(ds, "Customers");

    // Load the orders for this customer

    SqlCommand cmdOrders =

        cnn.CreateCommand();

    cmdOrders.CommandType =

        CommandType.StoredProcedure;

    cmdOrders.CommandText =

        "procOrdersForCustomer";

    cmdOrders.Parameters.Add(

        new SqlParameter(

        "@CustomerID", SqlDbType.Text, 5));

    cmdOrders.Parameters[

        "@CustomerID"].Value =

        cboCustomers.SelectedValue;

    SqlDataAdapter daOrders =

        new SqlDataAdapter();

    daOrders.SelectCommand = cmdOrders;

    daOrders.Fill(ds, "Orders");

    // Relate the two DataTables

    DataRelation relCustOrder =

       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";

}

Insert the Main() method to launch the form. Set the form as the startup form for the project.
Run the project. Select a customer from the list in the combo box and then press the Load button. The form displays only the information for that customer. Click on the + sign next to the customer to see the order information, as shown in Figure.

29. Use Parameterized Stored Procedures to minimize the amount of data that you need to load into an application.

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.

Right-click on the 320C01Exercises project in Solution Explorer and select Add, Add New Item. Add a new DataSet object to the project and name it dsOrdersForCustomer.xsd.
Expand the Server Explorer tree view to show a data connection to the Northwind sample database. Drill into the Stored Procedures folder within this database.
Drag the procOrdersForCustomer stored procedure from Server Explorer and drop it on the DataSet schema designer. This creates a new element with all the necessary included elements to represent the stored procedure.
Add a new form to the project.
Place a ComboBox control named cboCustomers, a Button control named btnLoad, and a DataGrid control named dgMain on the form.
Switch to the code view and add the following using directives to make the ADO.NET objects available:
```
using System.Data;

using System.Data.SqlClient;
```
Drag the procOrdersForCustomer stored procedure from Server Explorer and drop it on the form. This creates a SqlDataAdapter object and a SqlCommand object.
Select the SqlCommand object and change its name to cmdOrdersForCustomer.

Double-click the form and enter this code to load the customer list in the Load event handler:


private void Exercise1_2_Load(

   object sender, System.EventArgs e)

{

    // Load the customer list

    SqlCommand cmdCustomers =

       sqlConnection1.CreateCommand();

    cmdCustomers.CommandType =

        CommandType.StoredProcedure;

    cmdCustomers.CommandText =

        "procCustomerList";

    sqlConnection1.Open();

    DataSet ds = new DataSet();

    SqlDataAdapter da =

        new SqlDataAdapter();

    da.SelectCommand = cmdCustomers;

    da.Fill(ds, "Customers");

    cboCustomers.DataSource =

        ds.Tables["Customers"];

    cboCustomers.DisplayMember =

        "CompanyName";

    cboCustomers.ValueMember =

        "CustomerID";

    sqlConnection1.Close();

}

Double-click the button control and enter this code to build the DataSet and bind it to the DataGrid in the Click event handler:


private void btnLoad_Click(

   object sender, System.EventArgs e)

{

    // Create a new DataSet

    dsOrdersForCustomer ds =

        new dsOrdersForCustomer();

    // Load only the data of interest

    cmdOrdersForCustomer.Parameters[

        "@CustomerID"].Value =

        cboCustomers.SelectedValue;

    SqlDataAdapter daCustomer =

        new SqlDataAdapter();

    daCustomer.SelectCommand =

        cmdOrdersForCustomer;

    daCustomer.Fill(ds, "Orders");

    // Bind the data to the user interface

    dgMain.DataSource = ds;

    dgMain.DataMember = "Orders";

}

Insert the Main() method to launch the form. Set the form as the startup form for the project.
Run the project. Select a customer from the list in the combo box 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 rather than 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:	You can execute T-SQL statements by 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 enable you to specify data restrictions such as minimum, maximum, or exact length. For more information, see the section "Using Simple Types" in this chapter.
2:	Your application contains a `DataSet` schema file that represents students taking a laboratory course. Each student is assigned a unique student ID upon enrollment 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 have only 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. For more information, see the section "Manipulating DataSet Relationships" in this chapter.
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. For more information, see the section "Manipulating DataSet Relationships" in this chapter.
4:	Your application includes a strongly typed DataSet object named `dsWarehouse`. You have extracted a table named `Inventory` from the `DataSet` object to a `DataTable` object named `dtInventory`. The `Inventory` table contains a column named `StockOnHand`. Which syntax can you use to refer to a value in this column? (Select two.) `dtInventory.Rows[0].StockOnHand` `dtInventory.Rows[0]."StockOnHand"` `dtInventory.Rows[0]["StockOnHand"]` `dtInventory.Rows[0].Item.StockOnHand`
A4:	A, C. The early-bound syntax of answer A is possible only with a strongly typed `DataSet` object. But you're not required to use early binding with strongly typed `DataSet` objects, so the late-bound syntax of answer C also works. For more information, see the section "Creating and Using Strongly Typed DataSet Objects" in this chapter.
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. For more information, see the section "Manipulating DataSet Relationships" in this chapter.
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. For more information, see the section "Manipulating DataSet Relationships" in this chapter.
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. For more information, see the section "Manipulating DataSet Relationships" in this chapter.
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. For more information, see the section "Using Ad Hoc Queries" in this chapter.
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. For more information, see the section "The INSERT Statement" in this chapter.
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 particular customers. Inserting a new order into the `Orders` table. Free-form querying by the database administrator.
A10:	D. 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. For more information, see the section "The SELECT Statement" in this chapter.
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` object for returning a single value. For more information, see the section "Using Stored Procedures" in this chapter and Appendix A.
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 the stores, together with their total daily sales. The list should be filtered to include only 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. For more information, see the section "The SELECT Statement" in this chapter.
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 the stores, together with their total daily sales. The list should be filtered to include only 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. For more information, see the section "The SELECT Statement" in this chapter.
14:	Your SQL Server database contains a table, Experiments, with the following columns: ExperimentID (int, identity) ExperimentType (char(1)) ExperimentDate (datetime) You want to delete all rows from the table where the `ExperimentType` value is either `A` or `C`. You do not want 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. For more information, see the section "The DELETE Statement" in this chapter.
15:	Your SQL Server database contains a table, Sales, with these columns: SalesID (int, identity) StoreNumber (int) DailySales (int) You want 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. For more information, see the section "Using Stored Procedures" in this chapter.

Feb. 9, 2009, 7:48 a.m.
posted by fractal

Apply Your Knowledge