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
– Pavel Voronin

2012-08-31 18:42:27 +00:00
Commented Aug 31, 2012 at 18:42 — Pavel Voronin
– Pavel Voronin, Commented Aug 31, 2012 at 18:42

Zoltan Toth · Accepted Answer · 2012-08-31 19:22:58Z

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, 2012 at 19:22

Zoltan Toth

47.8k12 gold badges132 silver badges138 bronze badges

answered Aug 31, 2012 at 18:40

Mohamad Dbouk

19912 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ed Harrod Over a year ago

To display the data source window: On the menu bar, choose View, Other Windows, Data Sources (or choose the Shift+Alt+D keys).

John Washam · Accepted Answer · 2014-05-04 02:32:08Z

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 4, 2014 at 2:32

John Washam

4,1134 gold badges35 silver badges46 bronze badges

answered Aug 31, 2012 at 23:08

Hassan Boutougha

3,9191 gold badge19 silver badges19 bronze badges

2 Comments

Arulraj Over a year ago

If you are not using named instance, then simply use dot like this "Data Source=."

Hrvoje T Over a year ago

I used this for SQLEXPRESS

connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True"

from MSDN here msdn.microsoft.com/en-us/library/…

David · Accepted Answer · 2012-08-31 18:40:30Z

5

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/

answered Aug 31, 2012 at 18:40

David

73.7k19 gold badges134 silver badges177 bronze badges

Comments

ablaze · Accepted Answer · 2013-01-11 18:18:25Z

4

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

answered Jan 11, 2013 at 18:18

ablaze

7229 silver badges30 bronze badges

Comments

Aghilas Yakoub · Accepted Answer · 2012-08-31 18:38:53Z

3

You try with this string connection

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

answered Aug 31, 2012 at 18:38

Aghilas Yakoub

29k5 gold badges49 silver badges51 bronze badges

Comments

czuroski · Accepted Answer · 2012-08-31 18:43:17Z

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, 2012 at 18:43

czuroski

4,35210 gold badges53 silver badges87 bronze badges

Comments

Stephen Hosking · Accepted Answer · 2021-08-25 09:46:26Z

2

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.

answered Aug 25, 2021 at 9:46

Stephen Hosking

1,47517 silver badges34 bronze badges

Collectives™ on Stack Overflow

Connecting to local SQL Server database using C#

7 Answers 7

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Comments

Linked

Related