SQL Server 2005: SQL Server and ADO.NET Table of Contents SQL Server 2005: SQL Server and ADO.NET............................................................................................................3 Lab Setup.......................................................................................................................................................................4 Exercise 1 Building a Windows Form Application Utilizing the Asynchronous Capabilities of ADO.NET................5 Exercise 2 Create a Windows Application Using SqlDependency..............................................................................15 Exercise 3 Build the Change Application....................................................................................................................21 Exercise 4 Enable Notifications and Test....................................................................................................................28 SQL Server 2005: SQL Server and ADO.NET SQL Server 2005: SQL Server and ADO.NET After completing this lab, you will be able to: Objectives (cid:131) Build a Microsoft Windows® application displaying a bound data grid. NOTE: This lab focuses on the (cid:131) Execute a long-running query that would normally block the concepts in this module and as a result may not comply with user interface as it runs. Microsoft® security (cid:131) Build a Windows application displaying a bound data grid. recommendations. (cid:131) Use SqlDependency and SqlNotifications. NOTE: The SQL Server™ 2005 labs are based on beta builds of the product. The intent of these labs is to provide you with a general feel of some of the planned features for the next release of SQL Server. As with all software development projects, the final version may differ from beta builds in both features and user interface. For the latest details on SQL Server 2005, please visit http://www.microsoft.com/sql/2005/. This lab will show you how to use new functionality in ADO.NET Scenario 2.0 with SQL Server 2005. In the first exercise, you will learn how to utilize the asynchronous capabilities of ADO.NET 2.0 by building a User Interface that will allow continued user activity even when it is still servicing a long running query. (cid:131) Basic programming experience with SQL Server Prerequisites (cid:131) Basic knowledge of either VB.NET or C# programming 45 Minutes Estimated Time to Complete This Lab Page 3 of 28 SQL Server 2005: SQL Server and ADO.NET Lab Setup Tasks Detailed Steps 1. Log in. 1. Log in using the Administrator user account. The password is Pass@word1. Page 4 of 28 SQL Server 2005: SQL Server and ADO.NET Exercise 1 Building a Windows Form Application Utilizing the Asynchronous Capabilities of ADO.NET Scenario In this exercise, you will build an application that returns data and binds it to a DataGrid control. In order to fill the grid, you’ll execute a command that simulates a long-running query. In order to demonstrate how you can avoid blocking the user interface while waiting for the results of the command, this demonstration uses the new asynchronous functionality available in ADO.NET. Although you could use any of the existing .NET asynchronous design patterns, this lab uses a delegate to illustrate this behavior. Tasks Detailed Steps 1. Create a Windows 1. From the Windows task bar, launch Microsoft Visual Studio® 2005 by selecting application. Start | All Programs | Microsoft Visual Studio 2005 Beta 2 | Microsoft Visual Studio 2005 Beta 2. 2. From the menu select File | Open | Project/Solution. 3. Create a new Windows application (in either C# or Microsoft Visual Basic® .NET). Set its name to AdoNetAsync, and its location to C:\SQL Labs\User Projects. 4. In the Solution Explorer window, right-click the AdoNetAsync project, and select Add Reference from the context menu. In the Add Reference dialog box, select System.Data.dll, and click OK to add the reference. Your project may already include this reference, but adding it again won't cause any trouble. Repeat for the System.Xml.dll assembly. 2. Add controls to the 1. Use the View | Toolbox menu item to ensure that the Toolbox window is visible. form. Expand the Windows Forms tab in the toolbox, so you can use controls from this section. 2. Add a TextBox control named txtSql to Form1. 3. Add a DataGridView control named grdDemo to Form1. 4. Add a Button control named btnExecute to Form1. Set the Text property to “Execute”. 5. Add a Label control named lblInfo. Set the label's AutoSize property to False. Dock the control to the bottom of the form. Set the Text property to “Ready”. 6. Lay out the controls on the form so that they resemble Figure 1. Page 5 of 28 SQL Server 2005: SQL Server and ADO.NET Tasks Detailed Steps Figure 1: Completed form 3. Add Using/Import Now that you have created the form, you can add the code needed to retrieve data statements and a class from the local SQL Server and bind it to the grid. level variable. 1. Double-click btnExecute to load the code editor. 2. Add a using/Imports statement for the System.Data.SqlClient namespace. In Visual Basic, add an Imports statement for the System.Data namespace, as well: ' Visual Basic Imports System.Data Imports System.Data.SqlClient // C# using System.Data.SqlClient; 3. Declare a private class-level SqlCommand object named cmd. If you're programming in C#, set its value to null: ' Visual Basic Private cmd As SqlCommand // C# private SqlCommand cmd = null; 4. Add Synchronous data Add this code to the Click Event procedure of btnExecute. access code to the 1. Declare a local SqlConnection variable named cnn, initialized to Nothing/null: Form. ' Visual Basic Dim cnn As SqlConnection = Nothing // C# SqlConnection cnn = null; Page 6 of 28 SQL Server 2005: SQL Server and ADO.NET Tasks Detailed Steps 2. Add a Try/Catch block to the procedure. In the Catch block, catch an Exception object named ex. 3. Until instructed otherwise, add code to the Try block. Inside the Try block, update lblInfo to display "Connecting…" ' Visual Basic lblInfo.Text = "Connecting ..." // C# lblInfo.Text = "Connecting ..."; 4. Create and instantiate a SqlConnection variable cnn, using the connection string shown here: ' Visual Basic cnn = New SqlConnection( _ "Data Source=localhost;Integrated Security=true;" & _ "Initial Catalog=AdventureWorks") // C# cnn = new SqlConnection( "Data Source=localhost;Integrated Security=true;" + "Initial Catalog=AdventureWorks"); 5. Open the connection: ' Visual Basic cnn.Open() // C# cnn.Open(); 6. Update the text in lblInfo to display "Executing…": ' Visual Basic lblInfo.Text = "Executing ..." // C# lblInfo.Text = "Executing ..."; 7. Create a new instance of the SqlCommand object, cmd, and in its constructor, specify the CommandText parameter to be the Text property of the txtSql textbox, and the connection to be the SqlConnection object you created earlier: ' Visual Basic cmd = New SqlCommand(txtSql.Text, cnn) Page 7 of 28 SQL Server 2005: SQL Server and ADO.NET Tasks Detailed Steps // C# cmd = new SqlCommand(txtSql.Text, cnn); 8. Create a new DataTable instance named dt: ' Visual Basic Dim dt As New DataTable() // C# DataTable dt = new DataTable(); 9. Create a SqlDataReader and call the ExecuteReader method of the SqlCommand to supply its data: ' Visual Basic Dim reader As SqlDataReader = _ cmd.ExecuteReader(CommandBehavior.CloseConnection ) // C# SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 10. Call the Load method of the DataTable, passing the SqlDataReader object as the parameter, and then close the SqlDataReader: ' Visual Basic dt.Load(reader) reader.Close() // C# dt.Load(reader); reader.Close(); 11. Set the DataGrid control’s DataSource property to be the DataTable you just filled: ' Visual Basic Me.grdDemo.DataSource = dt // C# this.grdDemo.DataSource = dt; 12. Update the text in lblInfo to say "Ready" again: ' Visual Basic Page 8 of 28 SQL Server 2005: SQL Server and ADO.NET Tasks Detailed Steps lblInfo.Text = "Ready" // C# lblInfo.Text = "Ready"; 13. Inside the Catch block, display the message corresponding to the exception: CAUTION: If you used C# ' Visual Basic code expansion to create the MessageBox.Show(ex.Message) try-catch-finally code block, you must remove the throw // C# statement. The MessageBox MessageBox.Show(ex.Message); is used instead of a throw statement. 14. The completed procedure should look like the following: ' Visual Basic Dim cnn As SqlConnection = Nothing Try lblInfo.Text = "Connecting..." cnn = New SqlConnection( _ "Data Source=localhost;Integrated Security=true;" & _ "Initial Catalog=AdventureWorks") cnn.Open() lblInfo.Text = "Executing..." cmd = New SqlCommand(Me.txtSql.Text, cnn) Dim dt As New DataTable Dim reader As SqlDataReader = _ cmd.ExecuteReader(CommandBehavior.CloseConnection) dt.Load(reader) reader.Close() Me.grdDemo.DataSource = dt lblInfo.Text = "Ready" Catch ex As Exception MessageBox.Show(ex.Message) End Try // C# SqlConnection cnn = null; try { lblInfo.Text = "Connecting..."; cnn = new SqlConnection( "Data Source=localhost;Integrated Security=true;" + "Initial Catalog=AdventureWorks"); cnn.Open(); Page 9 of 28 SQL Server 2005: SQL Server and ADO.NET Tasks Detailed Steps lblInfo.Text = "Executing..."; cmd = new SqlCommand(this.txtSql.Text, cnn); DataTable dt = new DataTable(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt.Load(reader); reader.Close(); this.grdDemo.DataSource = dt; lblInfo.Text = "Ready"; } catch (Exception ex) { MessageBox.Show(ex.Message); } 15. Run the application. When the form appears, enter the following query into the SQL Textbox: WAITFOR DELAY '00:00:10'; SELECT * FROM Person.Contact 16. Click the Execute button. Note that while the query is executing the form’s user interface will be unresponsive—try to resize the form, and your efforts will go unheeded until the query has completed its operation. 5. Add Asynchronous In this step, you’ll change the implementation and flow of the application. Instead of data access code to the loading the data synchronously, this version of the application will use the Form. asynchronous capabilities of ADO.NET to allow the form to remain responsive while waiting for results. You need to be aware of an important issue: when using the asynchronous capabilities of Windows applications, you must ensure that you bind the data to the grid on the form’s thread, not on a background thread. Because of the way Windows GUI applications work, you may not interact with the form, its contents, or its properties from any thread besides the thread that created the form. In this example, much of the code you’ve already entered still applies but you’ll need to change the flow of the code somewhat to accommodate the asynchronous features. 1. Within the form’s class, create a new Delegate to bind the data to the DataGrid: ' Visual Basic Private Delegate Sub UICallback(ByVal param As Object) // C# private delegate void UICallback(object param); You need to create two callback procedures for this application to work properly. One of the callbacks, ExecCallback, is called when the results from the query return. The other callback, ReBindOnUIThread, is needed when you interact with the user interface; for example, when you bind the results of your query to the grid. The important consideration here is that for Windows applications, any user interface interaction must be performed on the thread that created the form, and this Page 10 of 28