CODEWITHSUNDEEP
.netsql-server
Graeme
Graeme

Reputation:

What's the difference between SQLNCLI and .NET Framework Data Provider for SQL Server

I have an application which uses a UDL file where the connection string specifies "Provider=SQLNCLI.1" which doesn't work on a specific machine because sqlncli.dll is not installed.

Another .Net app however, where the connection string is in app.config and does not specifically show the provider does worrk. The "Change Data Source" dialog in Visual Studio shows data provider as ".NET Framework Data Provider for SQL Server". This app works, even though sqlncli.dll is not installed.

Why? What provider is it using? Does it somehow fall back to sqloledb.dll?

Upvotes: 2

Views: 3669

Answers (2)

Simon Elms
Simon Elms

Reputation: 19738

Microsoft Docs has a useful article about this: When to Use SQL Server Native Client.

To quote:

For new applications, if you're using a managed programming language such as Microsoft Visual C# or Visual Basic, and you need to access the new features in SQL Server, you should use the .NET Framework Data Provider for SQL Server, which is part of the .NET Framework.

If you are developing a COM-based application and need to access the new features introduced in SQL Server, you should use SQL Server Native Client. If you don't need access to the new features of SQL Server, you can continue to use Windows Data Access Components (WDAC).

For existing OLE DB and ODBC applications, the primary issue is whether you need to access the new features of SQL Server. If you have a mature application that does not need the new features of SQL Server, you can continue to use WDAC. But if you do need to access those new features, such as the xml data type, you should use SQL Server Native Client.

So the .NET Framework Data Provider for SQL Server is part of .NET, completely separate from the SQL Server Native Client. Use it for new development. You only need to use the SQL Server Native Client for old applications that use OLE DB or ODBC.

Upvotes: 0

Sijin
Sijin

Reputation: 4550

The SQL Server Native client is an independent data access API that was introduced in SQL Server 2005.

From MSDN

It also provides new functionality above and beyond that supplied by the Windows Data Access Components (Windows DAC, formerly Microsoft Data Access Components, or MDAC). SQL Server Native Client can be used to create new applications or enhance existing applications that need to take advantage of features introduced in SQL Server 2005, such as multiple active result sets (MARS), user-defined data types (UDT), query notifications, snapshot isolation, and XML data type support.

Read more about it and how it compares to MDAC/WDAC which is used by the ADO.Net SQL Server providers here.

Upvotes: 1

Related Questions