Younas Bangash Younas Bangash - 5 months ago 30
SQL Question

Provider for SQL Server is not Register On Local Machine

I used the following code to connect C# with database

string connStr = "Provider=.NET Framework Data Provider for SQL Server"+
" Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True";

try
{
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();

if (conn.State == ConnectionState.Open)
{
using (OleDbCommand cmd = new OleDbCommand(selected_querry, conn))
{
cmd.CommandType = CommandType.Text;
using (OleDbDataAdapter dAdapter = new OleDbDataAdapter(selected_querry, conn))
{
DataSet ds = new DataSet();
try
{
dAdapter.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception)
{
MessageBox.Show("Error: Failed to Fill the required data Field from the DataBase.");
return;
}
}
}
}
else
MessageBox.Show("Connection Not Open");
}

}
catch (Exception e)
{
MessageBox.Show("Error: Failed to retrieve the required data from the DataBase."+e);
return;
}


When i run the code i always get the following error

Provider for SQL Server is not Register On Local Machine

Answer

With an OleDb connection, you need to specify an OLE DB provider rather than a .NET provider in the connection string and specify the OLE DB connection string keywords Server (instead of Data Source) and Trusted_Connection=yes (instead of Integrated Security=SSPI):

string connStr =   "Provider=SQLNCLI11.1"+
    ";Server=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\\Database1.mdf;Trusted_Connection=Yes";

SQL Server OLE DB providers include:

  • SQLOLEDB: legacy MDAC/WDAC provider
  • SQLNCLI: SQL Server 2005 Native client
  • SQLNCLI10: SQL Server 2008 Native client
  • SQLNCLI11: SQL Server 2012 Native client

However, in .NET applciations, it is best to use a managed provider. For SQL Server, the .NET Framework Data Provider for SQL Server (a.k.a SqlClient) will perform better, especially with large result sets. Use SqlConnection, SqlCommand, etc. and omit the provider from the connection string since it is implicit. Your connection string should work if you remove the Provider specification.