SQL providerName in web.config

Question

We are using ASP.NET (Framework 2) and setting database connection strings (SQL2005) in web.config.

We are currently using "providerName=SqlServer".

All our data accesses are done using System.Data.SqlClient - should we therefore change to providerName=System.Data.SqlClient? I find many examples of this providerName on the web, but very little explaining what providerName=SqlServer actually means.

Is there a difference? I'm worried that the providerName we currently specify is actually referencing a legacy (and maybe slower) client, or is there an even more efficient client than SqlClient for use with ASP.NET?

Answer 1

System.Data.SqlClient is the .NET Framework Data Provider for SQL Server. ie .NET library for SQL Server.

I don't know where providerName=SqlServer comes from. Could you be getting this confused with the provider keyword in your connection string? (I know I was :) )

In the web.config you should have the System.Data.SqlClient as the value of the providerName attribute. It is the .NET Framework Data Provider you are using.

<connectionStrings>
   <add 
      name="LocalSqlServer" 
      connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" 
      providerName="System.Data.SqlClient"
   />
</connectionStrings>

See http://msdn.microsoft.com/en-US/library/htw9h4z3(v=VS.80).aspx

Answer 2

 WebConfigurationManager.ConnectionStrings["YourConnectionString"].ProviderName;