SQL Connection string Provider name

Question

This is more of a generic question , I am building a report Generator :

public ReportGenerator(IReportGeneratorConfig config)
{
    Configuration = config;
    ImagePaths = new Dictionary<string, string>();
    ReportErrors = new StringBuilder();

    DatabaseForReportQueries = DatabaseFactory.CreateDatabase(Configuration.DatabaseName);

    using (System.Data.Common.DbConnection conn = DatabaseForReportQueries.CreateConnection())
    {
        //get the connection string for use with the report directly.
        ReportConnectionString = conn.ConnectionString + "Provider=SQLOLEDB;";
        conn.Close();
    }
}

This is the error message I'm getting when trying to run the report generation button

Cannot open data source, please check ConnectionString and RecordSource properties. 
ConnectionString:   Database=Test80;Server=SQL01;uid=mcadmin;pwd=password;Provider=SQLOLEDB; 
RecordSource: 

All the information are intact apart from the Provider that I'm sending in my code. I don't know how to find out the provider name in the web app. This is not the same as the providerName tag in connectionStrings.config. It needs to be Provider=something;

The connection string has a providerName = System.Data.SqlClient

Answer 1

You don't need to specify the provider name in the connection string if you are using the .NET Provider for SQL Server (System.Data.SqlClient).

Provider names for .NET providers are implicit based on the implementing class and do not need to be specified in the connection string.

Don't use a COM-based OLE DB provider (SQLNCLI or SQLOLEDB) or ODBC driver for SQL Server data access from .NET applications as that incurs a performance penalty compared with SqlClient.

The equivalent SqlClient connection string is:

Data Source=SQL01;Initial Catalog=Test80;User Id=mcadmin;Password=password;

Answer 2

The Provider Name depends on the SQL Server Native Client version you are using. You might try Provider=SQLNCLI11. For a list of possible connection string settings for sql server you can take a look at ConnectionStrings.com