Using ADO with the SQL Native Client

SQL Native Client is a new data access method from Microsoft. It boasts its own dynamic link library. This extends the capabilities of ADO on the SQL Server. This article explores two aspects of this method.

Introduction

In an earlier article one of the new features of SQL 2005, namely the Multiple Active Result Sets (MARS) was described with an example where the SQL Native Client OLEDB provider was used. In SQL 2005 Server a new data type, the XML data type, was also added. If you ever need to leverage the new features of SQL 2005 Server in your application, you should consider using the SQL Native Client as the data access provider.

The SQL Native Client is contained in a single dynamic link library and supports both OLEDB and ODBC interfaces. However the names of these interfaces are different from those used in MDAC. The SQL Native Client supports the For XML clauses in the queries as well as the XML data type.

In this tutorial two items are explored. First, the methods of connection and command objects when the ADO accesses the database with the SQL Native Client provider are described with an example. Second, an example of connecting to the SQL 2005 server with this provider to execute a “for XML query” and display it with the web browser object is described.

Again as in the previous tutorial, an MS Access platform will be used in the examples, which necessitates using a form and a command button. The ADO will provide the data from the SQL 2005 Server, the FileSystemObjects takes it to the intranet web root and the web browser will navigate to the intranet URL to display the returned data.

{mospagebreak title=Adding Library References and ActiveX Objects}

Create an MS Access application and create a new form in design. Since the ADO objects will be used you need to set up a reference. Open the Microsoft Visual Basic editor and click on Tools to obtain the following drop-down menu.

This opens the window References. Scroll down to get the Microsoft ActiveX data Objects 2.8 library and the scrrun.dll (Microsoft Scripting Runtime) items by checking them as shown in the next picture.

.

These references provide access to the methods and properties in these dlls. By referencing these libraries you also get the support of the invaluable intellisense. The properties and methods contained in these dlls can be seen in the Object Browser as shown for the ADO 2.8 library.

.

Now with the form showing you may add the Microsoft Web Browser control by accessing it as shown in the next picture. The web browser control will display the file that is obtained through the connection.

Drag and drop a button and the web browser control on the form, and resize the web browser control as shown. Add another button to explore the properties.

{mospagebreak title=Exploring the SQL Native Client}

The next paragraph shows the code in the command button’s click event. Comments are provided on the appropriate parts of the code. It may be interesting to note that once the SQLNCLI provider is used, several of the defaults enter into the connections string. The initial catalog used here is the Northwind database. This is not an example database in the SQL 2005 Server. It was exported to SQL2005 from the SQL 2000 server. For details refer to this link.

Private Sub Command7_Click()
'ADODB Stream is declared but not used.
Dim myStream As New ADODB.Stream
'declare the ADODB Connection
Dim myConnection As New ADODB.Connection
'declare the ADODB Command
Dim myCommand As New ADODB.Command 'Open the connection using the SQL 
Native Client
'Hodentek/Mysorian is the SQL 2005 Server (Mysorian) 'on the machine,
Hodentek
myConnection.Open "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _ "Persist Security Info=False; Initial Catalog=Northwind;" & _ "Data Source=Hodentekmysorian" 'use the connection as the Active Connection for the command myCommand.ActiveConnection = myConnection 'explore the various properties for this connection Debug.Print ("Attributes of the Connection: " & myConnection.Attributes) Debug.Print ("Command Time out of the Connection: " &
myConnection.CommandTimeout) Debug.Print ("Connection string for the Connection: " &
myConnection.ConnectionString) Debug.Print ("Cursor Location of the connection: " &
myConnection.CursorLocation) Debug.Print ("The Provider for this Connection: " &
myConnection.Provider) Debug.Print ("Connection's Default Database: " &
myConnection.DefaultDatabase) Debug.Print ("Isolation Level for this Connection: " &
myConnection.IsolationLevel) Debug.Print ("The Mode for this connection: " &
myConnection.Mode) Debug.Print ("The version used for this connection: " &
myConnection.Version) Debug.Print ("What is the State of this Connection: " & myConnection.State) 'explore the command created Debug.Print ("Command's Active Connection is: " &
myCommand.ActiveConnection) Debug.Print ("Command's Timeout is: " & myCommand.CommandTimeout) Debug.Print ("Command's CommandType is: " & myCommand.CommandType) Debug.Print ("Command's Dialect is: " & myCommand.Dialect) Debug.Print ("Command's State is: " & myCommand.State)
'Elucidating the command's properties further Debug.Print ("Name of Command property 0 is: " &
myCommand.Properties(0).Name) Debug.Print ("Name of Command property 1 is: " &
myCommand.Properties(1).Name) Debug.Print ("Name of Command property 2 is: " &
myCommand.Properties(2).Name) Debug.Print ("Name of Command property 3 is: " &
myCommand.Properties(3).Name) Debug.Print ("Name of Command property 4 is: " &
myCommand.Properties(4).Name) Debug.Print ("Name of Command property 5 is: " &
myCommand.Properties(5).Name) myConnection.Close End Sub

The properties of the connection and command are printed out to the immediate screen as shown in the next paragraph.

Attributes of the Connection: 
0 Command Time out of the Connection: 
30 Connection string for the Connection: 
Provider=SQLNCLI.1;Integrated Security=SSPI; Persist Security Info=False;
Initial Catalog=Northwind; Data Source=Hodentekmysorian;
Use Procedure for Prepare=1;Auto Translate=True; Packet 
Size=4096;Workstation ID=HODENTEK; Use Encryption for Data=False; Tag with column collation
when possible=False; MARS Connection=False; DataTypeCompatibility=0;Trust Server
Certificate=False Cursor Location of the connection: 2 The Provider for this Connection: SQLNCLI.1 Connection's Default Database: Northwind Isolation Level for this Connection: 4096 The Mode for this connection: 0 The version used for this connection: 2.8 What is the State of this Connection: 1 Command's Active Connection is: Provider=SQLNCLI.1;Integrated
Security=SSPI; Persist Security Info=False; Initial Catalog=Northwind; Data Source=Hodentekmysorian; Use Procedure for Prepare=1; Auto Translate=True; Packet Size=4096;Workstation ID=HODENTEK; Use Encryption for Data=False; Tag with column collation when
possible=False; MARS Connection=False; DataTypeCompatibility=0;Trust Server
Certificate=False Command's Timeout is: 30 Command's CommandType is: 8 Command's Dialect is: {C8B521FB-5CF3-11CE-ADE5-00AA0044773D} Command's State is: 0 '-------------------- Name of Command property 0 is: IAccessor Name of Command property 1 is: IColumnsInfo Name of Command property 2 is: IColumnsRowset Name of Command property 3 is: IConnectionPointContainer Name of Command property 4 is: IConvertType Name of Command property 5 is: IDBAsynchStatus
These are a few out of 97 properties.

It may be noticed that a shorter connection string was used while opening the connection, but when the connection string was accessed a larger string was returned. These are the defaults.

Of these properties in the Connection String,  two of them deserve special mention. The DataTypeCompatibility, whose default is 0, refers to the ADO data types. If you want to use the data types that are new in SQL 2005 you should use 80, the other value for this argument. Also you can have the functionality of opening multiple recordsets (resultsets?) only if you set the MARS connection property to True.

The dialect property which appeared beginning with ADO 2.6 is used mainly in the context of XML streams and refers to the dialect (flavor of syntax) of XML used. The one that is referenced here is the default, a string which is a provider independent Globally Unique Identifier.

{mospagebreak title=Displaying XML data retrieved from SQL 2005}

ADO can execute queries on the SQL 2005 server with the for XML clause and retrieve XML data by the support provided by SQL Native Client for this kind of query. The code shown in the following paragraph is what is executed by the program when the command button with the caption “Display Data from SQL Server 2005″ is clicked.

Private Sub Command0_Click()
Dim myStream As New ADODB.Stream
Dim myConnection As New ADODB.Connection
Dim myCommand As New ADODB.Command
'Open the connection with the SQL Native Client Provider
myConnection.Open "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Persist Security Info=False; Initial Catalog=Northwind;" & _
"Data Source=Hodentekmysorian"
'make the opened connection the command's active connection
myCommand.ActiveConnection = myConnection
'provide a SQL statement to be executed with this command
myCommand.CommandText = "SELECT Phone, City, Country from customers " & _
"where CustomerID like 'A%' for xml auto"
'Open a stream object
myStream.Open
'associate the stream with the Outstream of the command
myCommand.Properties ("Output Stream").Value = myStream
'provide the root element for this XML (fragment)
myCommand.Properties ("xml root") = "root"
'MsgBox (myCommand.Properties(0).Name)
'execute the command with the Execute Stream flag
myCommand.Execute , , adExecuteStream
'to the beginning of stream
myStream.Position = 0
'set up the character set to be associated with the returned stream
myStream.Charset = "ISO-8859-1" Dim strxml As String
strxml = myStream.ReadText(adReadAll)
Call browse(strxml)
End Sub

The value of “myStream,” which is in XML, is returned by the query. The procedure takes this string and, using the FileSytemObject, places it in the root directory of the local intranet web server running IIS 5.1. The web browser control browses this file from the web site, resulting in a display which is shown after the code.

Sub browse (ByVal stringi As String)
    WebBrowser6.Visible = True
 'declare a new FileSystemObject  Dim fsys As New FileSystemObject  
Dim
outstream As TextStream 'set file location to intranet root (web root) testfile =
"C:inetpubwwwrootsqlNC.xml" 'make the stream flow to the testfile Set outstream =
fsys.CreateTextFile(testfile, True, False) 'write the outstream to
file variable stringi
outstream.WriteLine (stringi) Set outstream = Nothing 'navigate to the intranet site to display
WebBrowser6.Navigate2 ("http://localhost/sqlNC.xml") End Sub

Summary

SQL Native Client is the brand new data access method from Microsoft with its own new dynamic link library. This extends the capabilities of ADO on the SQL Server. It so happened that with MS Access 2003, when the Web Browser Control was added, the Navigate() method was not supported by intellisense, but it still worked. You may also notice that in the display above, the encoding came out as “utf-8,” whereas it was specifically programmed for “ISO-8859-1.” The result however shows characters with an accent acute which is not possible with “utf-8.” If one runs the code with “utf-8,” one would find that the browser will throw up a parsing error.

One thought on “Using ADO with the SQL Native Client

  1. If you are looking for data on SQL 2005 Server, this is the fastest. You may also connect to SQL 2000 Server with this.

    I look forwarad to your comments and thanks for reading the article.

    sincerely,

    Jay

[gp-comments width="770" linklove="off" ]