使用 ADO 搭配 SQL Server Native ClientUsing ADO with SQL Server Native Client

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse

為了利用中引進的新功能 SQL Server 2005 (9.x)SQL Server 2005 (9.x) ,例如 (MARS) 、查詢通知、使用者定義型別 (udt) 或新的 xml 資料類型,使用 ActiveX Data Objects (ADO) 的現有應用程式應該使用 SQL ServerSQL Server Native Client OLE DB 提供者作為其資料存取提供者。In order to take advantage of new features introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x) such as multiple active result sets (MARS), query notifications, user-defined types (UDTs), or the new xml data type, existing applications that use ActiveX Data Objects (ADO) should use the SQL ServerSQL Server Native Client OLE DB provider as their data access provider.

如果您不需要使用 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 所導入的任何新功能,就不需要使用 SQL ServerSQL Server Native Client OLE DB 提供者。您可以繼續使用目前的資料存取提供者 (通常是 SQLOLEDB)。If you do not need to use any of the new features introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x), there is no need to use the SQL ServerSQL Server Native Client OLE DB provider; you can continue using your current data access provider, which is typically SQLOLEDB. 如果您要強化現有的應用程式,而且需要使用 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 所導入的新功能,就應該使用 SQL ServerSQL Server Native Client OLE DB 提供者。If you are enhancing an existing application and you need to use the new features introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x), you should use SQL ServerSQL Server Native Client OLE DB provider.

注意

如果您正在開發新的應用程式,建議您考慮使用 ADO.NET 和 .NET Framework Data Provider for SQL ServerSQL Server 來取代 SQL ServerSQL Server Native Client,以便存取最新 SQL ServerSQL Server 版本的所有新功能。If you are developing a new application, it is recommended that you consider using ADO.NET and the .NET Framework Data Provider for SQL ServerSQL Server instead of SQL ServerSQL Server Native Client to access all the new features of recent versions of SQL ServerSQL Server. 如需有關 .NET Framework Data Provider for SQL ServerSQL Server 的詳細資訊,請參閱 ADO.NET 的 .NET Framework SDK 文件集。For more information about the .NET Framework Data Provider for SQL ServerSQL Server, see the .NET Framework SDK documentation for ADO.NET.

為了讓 ADO 使用最新 SQL ServerSQL Server 版本的新功能,我們已經對 SQL ServerSQL Server Native Client OLE DB 提供者新增了一些增強功能,以便擴充 OLE DB 的核心功能。To enable ADO to use new features of recent versions of SQL ServerSQL Server, some enhancements have been made to the SQL ServerSQL Server Native Client OLE DB provider which extends the core features of OLE DB. 這些增強功能可讓 ADO 應用程式使用較新的 SQL ServerSQL Server 功能,以及取用 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 所導入的兩種資料類型:xmludtThese enhancements allow ADO applications to use newer SQL ServerSQL Server features and to consume two data types introduced in SQL Server 2005 (9.x)SQL Server 2005 (9.x): xml and udt. 這些增強功能也會利用 varcharnvarcharvarbinary 資料類型的增強功能。These enhancements also exploit enhancements to the varchar, nvarchar, and varbinary data types. SQL ServerSQL Server Native Client 會將 SSPROP_INIT_DATATYPECOMPATIBILITY 初始化屬性加入至 ADO 應用程式所設定的 DBPROPSET_SQLSERVERDBINIT 屬性,讓新的資料類型會以與 ADO 相容的方式公開。Native Client adds the SSPROP_INIT_DATATYPECOMPATIBILITY initialization property to the DBPROPSET_SQLSERVERDBINIT property set for use by ADO applications so that the new data types are exposed in a way compatible with ADO. 此外, SQL ServerSQL Server Native Client OLE DB 提供者也會定義名為 DataTypeCompatibility 的新連接字串關鍵字,該關鍵字會在連接字串中設定。In addition, the SQL ServerSQL Server Native Client OLE DB provider also defines a new connection string keyword named DataTypeCompatibility that is set in the connection string.

注意

現有的 ADO 應用程式可以使用 SQLOLEDB 提供者來存取並更新 XML、UDT 和大數值文字與二進位欄位值。Existing ADO applications can access and update XML, UDT, and large value text and binary field values using the SQLOLEDB provider. 新的較大 varchar(max)nvarchar(max)varbinary(max) 資料類型會分別傳回成 ADO 類型 adLongVarCharadLongVarWCharadLongVarBinaryThe new larger varchar(max), nvarchar(max), and varbinary(max) data types are returned as the ADO types adLongVarChar, adLongVarWChar and adLongVarBinary respectively. XML 資料行會傳回成 adLongVarChar,而且 UDT 資料行會傳回成 adVarBinaryXML columns are returned as adLongVarChar, and UDT columns are returned as adVarBinary. 但是,如果您使用 SQL ServerSQL Server Native Client OLE DB 提供者 (SQLNCLI11) 而非 SQLOLEDB,則必須確定將 DataTypeCompatibility 關鍵字設定為 "80",讓新的資料類型能夠正確地對應至 ADO 資料類型。However, if you use the SQL ServerSQL Server Native Client OLE DB provider (SQLNCLI11) instead of SQLOLEDB, you need to make sure to set the DataTypeCompatibility keyword to "80" so that the new data types will map correctly to the ADO data types.

從 ADO 啟用 SQL Server Native ClientEnabling SQL Server Native Client from ADO

為了能夠使用 SQL ServerSQL Server Native Client,ADO 應用程式必須在其連接字串中執行下列關鍵字:To enable the usage of SQL ServerSQL Server Native Client, ADO applications will need to implement the following keywords in their connection strings:

  • Provider=SQLNCLI11

  • DataTypeCompatibility=80

如需原生用戶端所支援之 ADO 連接字串關鍵字的詳細資訊 SQL ServerSQL Server ,請參閱搭配 SQL Server Native Client 使用連接字串關鍵字For more information about the ADO connections string keywords supported in SQL ServerSQL Server Native Client, see Using Connection String Keywords with SQL Server Native Client.

下列範例會建立完全啟用以與 Native Client 搭配使用的 ADO 連接字串 SQL ServerSQL Server ,包括啟用 MARS 功能:The following is an example of establishing an ADO connection string that is fully enabled to work with SQL ServerSQL Server Native Client, including the enabling of the MARS feature:

Dim con As New ADODB.Connection  
  
con.ConnectionString = "Provider=SQLNCLI11;" _  
         & "Server=(local);" _  
         & "Database=AdventureWorks;" _   
         & "Integrated Security=SSPI;" _  
         & "DataTypeCompatibility=80;" _  
         & "MARS Connection=True;"  
con.Open  

範例Examples

下列各節提供如何搭配使用 ADO 與 SQL ServerSQL Server Native Client OLE DB 提供者的範例。The following sections provide examples of how you can use ADO with the SQL ServerSQL Server Native Client OLE DB provider.

擷取 XML 資料行資料Retrieving XML Column Data

在這個範例中,使用了資料錄集來擷取並顯示 SQL ServerSQL Server AdventureWorks 範例資料庫中 XML 資料行的資料。In this example, a recordset is used to retrieve and display the data from an XML column in the SQL ServerSQL Server AdventureWorks sample database.

Dim con As New ADODB.Connection  
Dim rst As New ADODB.Recordset  
Dim sXMLResult As String  
  
con.ConnectionString = "Provider=SQLNCLI11;" _  
         & "Server=(local);" _  
         & "Database=AdventureWorks;" _   
         & "Integrated Security=SSPI;" _   
         & "DataTypeCompatibility=80;"  
  
con.Open  
  
' Get the xml data as a recordset.  
Set rst.ActiveConnection = con  
rst.Source = "SELECT AdditionalContactInfo FROM Person.Contact " _  
   & "WHERE AdditionalContactInfo IS NOT NULL"  
rst.Open  
  
' Display the data in the recordset.  
While (Not rst.EOF)  
   sXMLResult = rst.Fields("AdditionalContactInfo").Value  
   Debug.Print (sXMLResult)  
   rst.MoveNext  
End While  
  
con.Close  
Set con = Nothing  

注意

XML 資料行不支援資料錄集篩選。Recordset filtering is not supported with XML columns. 如果已使用,就會傳回錯誤。If used, an error will be returned.

擷取 UDT 資料行資料Retrieving UDT Column Data

在這個範例中,使用了 Command 物件來執行可傳回 UDT 的 SQL 查詢、更新 UDT 資料,然後將新的資料插回資料庫中。In this example, a Command object is used to execute a SQL query that returns a UDT, the UDT data is updated, and then the new data is inserted back into the database. 這個範例會假設已經在資料庫中註冊了 Point UDT。This example assumes that the Point UDT has already been registered in the database.

Dim con As New ADODB.Connection  
Dim cmd As New ADODB.Command  
Dim rst As New ADODB.Recordset  
Dim strOldUDT As String  
Dim strNewUDT As String  
Dim aryTempUDT() As String  
Dim strTempID As String  
Dim i As Integer  
  
con.ConnectionString = "Provider=SQLNCLI11;" _  
         & "Server=(local);" _  
         & "Database=AdventureWorks;" _   
         & "Integrated Security=SSPI;" _  
         & "DataTypeCompatibility=80;"  
  
con.Open  
  
' Get the UDT value.  
Set cmd.ActiveConnection = con  
cmd.CommandText = "SELECT ID, Pnt FROM dbo.Points.ToString()"  
Set rst = cmd.Execute  
strTempID = rst.Fields(0).Value  
strOldUDT = rst.Fields(1).Value  
  
' Do something with the UDT by adding i to each point.  
arytempUDT = Split(strOldUDT, ",")  
i = 3  
strNewUDT = LTrim(Str(Int(aryTempUDT(0)) + i)) + "," + _  
   LTrim(Str(Int(aryTempUDT(1)) + i))  
  
' Insert the new value back into the database.  
cmd.CommandText = "UPDATE dbo.Points SET Pnt = '" + strNewUDT + _  
   "' WHERE ID = '" + strTempID + "'"  
cmd.Execute  
  
con.Close  
Set con = Nothing  

啟用並使用 MARSEnabling and Using MARS

在此範例中,會建立連接字串,以透過 SQL ServerSQL Server Native Client OLE DB 提供者啟用 MARS,然後建立兩個記錄集物件,以使用相同的連接執行。In this example, the connection string is constructed to enable MARS through the SQL ServerSQL Server Native Client OLE DB provider, and then two recordset objects are created to execute using the same connection.

Dim con As New ADODB.Connection  
  
con.ConnectionString = "Provider=SQLNCLI11;" _  
         & "Server=(local);" _  
         & "Database=AdventureWorks;" _   
         & "Integrated Security=SSPI;" _  
         & "DataTypeCompatibility=80;" _  
         & "MARS Connection=True;"  
con.Open  
  
Dim recordset1 As New ADODB.Recordset  
Dim recordset2 As New ADODB.Recordset  
  
Dim recordsaffected As Integer  
Set recordset1 =  con.Execute("SELECT * FROM Table1", recordsaffected, adCmdText)  
Set recordset2 =  con.Execute("SELECT * FROM Table2", recordsaffected, adCmdText)  
  
con.Close  
Set con = Nothing  

在舊版 OLE DB 提供者中,此程式碼會導致系統在第二次執行時建立隱含連接,因為每個單一連接只能開啟一個作用中結果集。In prior versions of the OLE DB provider, this code would cause an implicit connection to be created on the second execution because only one active set of results could be opened per a single connection. 由於隱含連接不會在 OLE DB 連接集區中共用,所以這會產生額外的負擔。Because the implicit connection was not pooled in the OLE DB connection pool this would cause additional overhead. 使用 SQL ServerSQL Server Native Client OLE DB 提供者公開的 MARS 功能時,您會在一個連接上取得多個作用中結果。With the MARS feature exposed by the SQL ServerSQL Server Native Client OLE DB provider, you get multiple active results on the one connection.

另請參閱See Also

使用 SQL Server Native Client 建置應用程式Building Applications with SQL Server Native Client