Connecting to local SQL Server database using C#

Question

Suppose I have created a SQL Server database called Database1.mdf in the App_Data folder in Visual Studio with a table called Names.

How could I establish a connection to read the table values using C#?

So far I've tried something like this:

SqlConnection conn = new SqlConnection("Server=localhost;Database=Database1;");

conn.Open();

// create a SqlCommand object for this connection
SqlCommand command = conn.CreateCommand();
command.CommandText = "Select * from Names";

But I get an error:

database not found/error connecting to database

Did you attach DB to SQL Server? – Pavel Voronin Aug 31 '12 at 18:42 — Pavel Voronin, Aug 31 '12 at 18:42

score 15 · Accepted Answer · edited Aug 31 '12 at 19:22

15

In Data Source (on the left of Visual Studio) right click on the database, then Configure Data Source With Wizard. A new window will appear, expand the Connection string, you can find the connection string in there

edited Aug 31 '12 at 19:22

Zoltan Toth

46,981
12
120
134

answered Aug 31 '12 at 18:40

Mohamad Y. Dbouk

199
12

3

To display the data source window: On the menu bar, choose View, Other Windows, Data Sources (or choose the Shift+Alt+D keys). – Ed Harrod Apr 27 '15 at 13:07

score 8 · Answer 2 · edited May 04 '14 at 02:32

8

If you use SQL authentication, use this:

using System.Data.SqlClient;

SqlConnection conn = new SqlConnection();
conn.ConnectionString = 
     "Data Source=.\SQLExpress;" + 
     "User Instance=true;" + 
     "User Id=UserName;" + 
     "Password=Secret;" + 
     "AttachDbFilename=|DataDirectory|Database1.mdf;"
conn.Open();

If you use Windows authentication, use this:

using System.Data.SqlClient;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = 
     "Data Source=.\SQLExpress;" + 
     "User Instance=true;" + 
     "Integrated Security=true;" + 
     "AttachDbFilename=|DataDirectory|Database1.mdf;"
conn.Open();

edited May 04 '14 at 02:32

John Washam

4,073
4
32
43

answered Aug 31 '12 at 23:08

Hassan Boutougha

3,871
1
17
17

1

If you are not using named instance, then simply use dot like this `"Data Source=."` – Arulraj Jun 06 '18 at 05:35
I used this for SQLEXPRESS `connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True"` from MSDN here https://msdn.microsoft.com/en-us/library/jj653752%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396#sse – Hrvoje T Aug 04 '18 at 20:23

score 5 · Answer 3 · answered Aug 31 '12 at 18:40

If you're using SQL Server express, change

SqlConnection conn = new SqlConnection("Server=localhost;" 
       + "Database=Database1;");

to

SqlConnection conn = new SqlConnection("Server=localhost\SQLExpress;" 
       + "Database=Database1;");

That, and hundreds more connection strings can be found at http://www.connectionstrings.com/

score 4 · Answer 4 · answered Jan 11 '13 at 18:18

4

SqlConnection c = new SqlConnection(@"Data Source=localhost; 
                           Initial Catalog=Northwind; Integrated Security=True");

answered Jan 11 '13 at 18:18

ablaze

722
7
30

score 3 · Answer 5 · answered Aug 31 '12 at 18:38

3

You try with this string connection

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Database1.mdf;Database=dbname; Trusted_Connection=Yes;

answered Aug 31 '12 at 18:38

Aghilas Yakoub

28,516
5
46
51

score 2 · Answer 6 · answered Aug 31 '12 at 18:43

2

I like to use the handy process outlined here to build connection strings using a .udl file. This allows you to test them from within the udl file to ensure that you can connect before you run any code.

Hope that helps.

answered Aug 31 '12 at 18:43

czuroski

4,316
9
50
86

score 1 · Answer 7 · answered Aug 25 '21 at 09:46

Visual Studio 2019 (and probably a few previous versions).

View -> SQL Server Object Explorer
Top of the tree is 'SQL Server'
Under 'SQL Server', are couple of '(localdb)....'
Expand the (localdb)... -> Databases until you find your db.
Database Name (eg. Database1) -> Right-click -> Properties, and scroll the many properties (eg. "ANSI NULL Default"). Find the "Connection string" property, copy the value into your code, and you're running.

Connecting to local SQL Server database using C#

7 Answers7

Linked