Using ADO with the SQL Native Client - Exploring the SQL Native Client (Page 3 of 4 )
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.
Next: Displaying XML data retrieved from SQL 2005 >>
More Database Articles
More By Jayaram Krishnaswamy
/ 6 
