Introducing SQL Native Client | Solid Quality Learning, Microsoft Corporation Staff - Microsoft SQL Server 2005 Database Essentials Step by Step

The new SQL Native Client is a single dynamic link library (DLL) that can be used in OLE DB and ODBC interfaces because it includes both data providers. By using SQL Native Client, applications can use the new SQL Server 2005 features. Some new SQL Server 2005 features that require SQL Native Client are XML Datatype Support, Multiple Active Result Sets, and Query Notification.

Using the XML Datatype

As you learned in Chapter 5, Designing a Database to Solve Business Needs, SQL Server 2005 includes a new datatype designed to store XML documents and XML fragments in the database. Because this datatype was not included in previous versions of SQL Server, the standard SQL Server client does not provide support for this datatype. SQL Native Client adds the DBTYPE_XML datatype in the OLE DB provider and the SQL_SS_XML datatype in the ODBC provider. From the ADO.NET programming perspective, applications do not need to be changed to access the xml datatype because it is always converted to a string type.

Using Multiple Active Result Sets (MARS)

A new feature of SQL Server 2005 supports multiple active result sets (MARS). In previous versions of SQL Server, the application would fail if it tried to use multiple active statements. For example, the following code (included as \Ch06\Sample10.vb in the sample files) fails in the cmdUpd.ExecuteNonQuery() statement because it tries to open a result set in the same connection as that which the cmdSel object is using.

 Dim conn As New SqlClient.SqlConnection( _     "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True") Dim cmdsel As New SqlClient.SqlCommand( _     "SELECT DepartmentID FROM HumanResources.Department", conn) Dim cmdUpd As New SqlClient.SqlCommand( _     "UPDATE HumanResources.Department SET ModifiedDate=GETDATE() " + _     "WHERE DepartmentID=@DepartmentId", conn) Dim par As SqlClient.SqlParameter = _     cmdUpd.Parameters.Add("@DepartmentId", SqlDbType.Int) conn.Open() Dim dr As SqlClient.SqlDataReader = cmdsel.ExecuteReader While dr.Read     par.Value = CInt(dr(0))     'Other operations     cmdUpd.ExecuteNonQuery() End While dr.Close() conn.Close()

The previous code returns an InvalidOperationException with the following message:

 There is already an open DataReader associated with this Command which must be closed first.

When using MARS, the same code above will succeed. To enable MARS, add the following text to the connection string:

 MultipleActiveResultSets=True

Using Query Notification

Because many applications use the ADO.NET disconnected model, they cache tables in memory. Therefore, data in the server may change without the applications knowledge. To avoid this situation, SQL Native Client provides the Query Notification feature.

Creating an Application that Uses Query Notification

From the Start menu, choose All Programs Microsoft Visual Studio 2005 Microsoft Visual Studio 2005.
From the File menu, choose New Project.
In the Project Types section, select Visual Basic and choose the Windows Application template.
Name the project NotificationTest and click OK to create the project.
If the Toolbox is not visible, select Toolbox from the View menu.
Add a DataGridView and name it dgDepartments .
Double-click the Windows Form (not the DataGridView) to add the Load event.
Scroll to the top of the code and, just below the first lines, add the following Imports statement (included in the sample files as \Ch06\Sample11.vb) to reference the SQL Client namespace.
```
 Imports System.Data.SqlClient 
```
Within the Form1 class declaration, declare the depend variable of SqlDependency type, enabled to manage events, as follows (also located in \Ch06\Sample11.vb):
```
 Dim WithEvents depend As SqlDependency 
```

In the forms Load event, write the following code (also located in \Ch06\Sample11.vb):

 Dim AdventureWorks As String = _         "Data Source=(local);Initial Catalog=AdventureWorks;" + _         "Integrated Security=True"     Dim Conn As New SqlConnection(AdventureWorks)     Dim CmdSel As New SqlCommand( _         "SELECT DepartmentID, Name FROM HumanResources.Department", Conn)     depend = New SqlDependency(CmdSel)     Dim da As New SqlDataAdapter(CmdSel)     Dim ds As New DataSet     SqlDependency.Start(AdventureWorks)     da.Fill(ds)     dgDepartments.DataSource = ds     dgDepartments.DataMember = "Table"

Add the depend_OnChange event as follows (included in the sample files as \Ch06\Sample12.vb):

 Private Sub depend_OnChange(ByVal sender As Object, _     ByVal e As System.Data.SqlClient.SqlNotificationEventArgs) _     Handles depend.OnChange     MsgBox("Data has changed in the server, reload the dataset") End Sub

Note	Make sure that the Service Broker is enabled in the SQL Server instance. To do this, open SQL Server Surface Area Configuration, as explained in Chapter 3, Reviewing Microsoft SQL Server 2005 Management Tools. Click the Surface Area Configuration For Features link at the bottom of the window.

Save and run the application by pressing F5. The department data will be displayed.
Without closing the application, open SQL Server Management Studio by choosing Start All Programs Microsoft SQL Server 2005 SQL Server Management Studio.
Connect to the database engine using Windows Authentication.
In Object Explorer, expand the server node. Expand the Databases folder, the AdventureWorks database node, and the Tables folder.
Right-click the HumanResources.Department table and select Open Table from the context menu.
Modify any of the department names , and the depend_OnChange event will fire, displaying the message box you coded in Step 10 above.