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.
Create a new Visual Basic .NET project to use for the Exercises in this chapter.
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.
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
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
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
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
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
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
Set the form as the startup form for the project.
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.
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.
Create a new Visual Basic .NET project to use for the Exercises in this chapter.
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.
Expand the Server Explorer treeview 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 will create 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.
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
Drag the procOrdersForCustomer stored procedure from Server Explorer and drop it on the form. This will create a SqlDataAdapter object and a SqlCommand object.
Select the SqlCommand object and change its name to cmdOrdersForCustomer.
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
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
Set the form as the startup form for the project.
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.
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. |
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?
|
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?
|
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?
|
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.)
|
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?
|
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?
|
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?
|
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?
|
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:
Which column must you explicitly specify in the INSERT statement?
|
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?
|
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?
|
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?
|
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?
|
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?
|
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?
|
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. |
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 |