user2422414 user2422414 - 7 months ago 13
SQL Question

Using Oledb connection string for Localhost c#

When use Oledb c# connection
I noticed that a lot connection using file.
But how to connect to localhost using oledb?
I created database and tables using Microsoft SQL Server Management that connect with SQL Express and using window authentication

When using this function i don't know how should convert to connect to localhost

//Want the connString to connect localhost instead of file
public static string connString = @"Provider=Microsoft.JET.OLEDB.4.0;data source=" + Path + "\\database\\errDB.mdb";
public static OleDbConnection connection;

public myFunction()
{
string sqlString = "SELECT name,contact,accessLevel,Crudential_ID FROM errors where Crudential_ID =@ID";
connection = new OleDbConnection(connString);
OleDbCommand command = new OleDbCommand(sqlString, connection);

//Open connection
connection.Open();

command.Parameters.Add("@ID", OleDbType.VarChar);
command.Parameters["@ID"].Value = "test";

//Read from database
OleDbDataReader reader = command.ExecuteReader();
if(reader.HasRows)
{
.....
}
connection.Close();
}

Answer

connectionstrings.com - true to its name - is indispensable when you frequently need to construct connection strings. For your specific case, this would be the relevant section.

Based on that, your connection string should look something like this:

Provider=SQLNCLI11;Server=.\SQLEXPRESS;Database=SOMEDATABASE;Trusted_Connection=yes;

To break it down:

  • SQLNCLI11 is the SQL Native Client OLEDB provider. You can see available providers in SQL Management Studio, under Server Objects > Linked Servers > Providers.
  • .\SQLEXPRESS is your servername and instance. The . is shorthand for localhost (you can also use localhost if you prefer), and SQLEXPRESS is the default instance name that SQL Express installs under.
  • SOMEDATABASE - whatever your database name is.
  • Trusted_Connection=yes - Use windows authentication. Sometime you see it as Integrated Security=SSPI. They are one and the same.