ConnectString didn't work in C#

Question

public static DataSet ParseDatabaseData(string sheetName)
{
 string connectionString = "Provider=System.Data.SqlClient;Data Source= MHSPC56888_VM1\\SQLEXPRESS;Initial Catalog=xxxxxxx;User id=xx;Password=xxxxx;"; 

    SqlConnection conn = new SqlConnection(connectionString);

    string strSQL = "SELECT * FROM [" + sheetName + "$]";
    SqlCommand cmd = new SqlCommand(strSQL, conn);
    conn.Open();
    DataSet dataset = new DataSet();
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    adapter.Fill(dataset);
    conn.Close();
    return dataset;

}

The error show that 'provider' keyword is wrong.

Please help me to correct how to connect with Database through connection string?

Answer 1

You do not need to specify the Provider in the Connection String.

Try it like this:

public static DataSet ParseDatabaseData(string sheetName)
{
    string connectionString = "Data Source= MHSPC56888_VM1\\SQLEXPRESS;Initial Catalog=xxxxxxx;User id=xx;Password=xxxxx;"; 

Answer 2

Instead of mentioning the connection string in the individual file itself, you can place the connection string in the web.config or app.config and use the config where ever required.

Sample for web.config place the connection string under the <configuration>, there you can provide the provider name:

<configuration>
    <connectionStrings>
       <add name="ConnString" 
            connectionString="Data Source= MHSPC56888_VM1\\SQLEXPRESS;Initial Catalog=xxxxxxx;User id=xx;Password=xxxxx;" 
            providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

and inside the file

public static DataSet ParseDatabaseData(string sheetName)
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);

Note: add using System.Configuration; for the ConfigurationManager.