How do I connect to an MDF database file?

Question

I'm experimenting in connecting a C# app to an MDF database for the first time, and I need a little help ..

I made a small MDF database file in Visual Studio 2010, then created another project and imported the file into the project itself.

I am not trying to connect to the MDF file via code. Here the code I'm using:

namespace DBtestApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        System.Data.SqlClient.SqlConnection con;
        private void Form1_Load(object sender, EventArgs e)
        {
            con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = "DataSource=.\\SQLEXPRESS; AttachDbFilename =SampleDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            con.Open();
            MessageBox.Show("Connection opened");
            con.Close();
            MessageBox.Show("Connection closed");
        }
    }
}

When I run the application, I get an exception at the line where I define the connection string, and the exception has this message at the top of the stack:

System.ArgumentException: Keyword not supported: 'datasource'.

Can someone point me in the right direction ?

this post might help: [How do I connect to an .mdf (Microsoft SQL Server Database File) in a simple web project?][1] Cheerz [1]: http://stackoverflow.com/questions/173209/how-do-i-connect-to-an-mdf-microsoft-sql-server-database-file-in-a-simple-web — Helikaon, Jan 19 '12 at 13:11

KV Prajapati · Accepted Answer · 2013-10-25T04:35:31.850

34

Add space between Data Source

 con.ConnectionString = @"Data Source=.\SQLEXPRESS;
                          AttachDbFilename=c:\folder\SampleDatabase.mdf;
                          Integrated Security=True;
                          Connect Timeout=30;
                          User Instance=True";

edited Oct 25 '13 at 04:35

answered Jan 19 '12 at 13:14

KV Prajapati

93,659
19
148
186

1

And while you're editing, remove the spaces in `; AttachDbFilename =` – H H Jan 19 '12 at 13:18
2

Tried it, but now I got the following exception: `System.Data.SqlClient.SqlException (0x80131904): An attempt to attach an auto-named database for file SampleDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.` – Ahmad Jan 19 '12 at 13:25
1

You need to specify the path of sampleDatabase.mdf. Have a look at edited post. – KV Prajapati Jan 19 '12 at 13:26
But why should I specify the complete path when I've added the MDF file to the project's local resources ? .. I don't want to hardcode a path as that would mean that MDF file always has to be there .. Can I not reference to its local presence within the project ? – Ahmad Jan 19 '12 at 13:32
Use AttachDbFilename=|DataDirectory|\SampleDatabase.mdf instead of absolute path. – KV Prajapati Jan 19 '12 at 13:35
Alright, it worked, thanks ! This is the final line I used: `con.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SampleDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";` – Ahmad Jan 19 '12 at 13:55
2

I was thinking of editing this so that the string was concatenated to eliminate the horizontal scroll, but I'm afraid I don't know enough about this to trust myself not to make the answer incorrect. – jamesmortensen Oct 24 '13 at 19:39

score 26 · Answer 2 · answered Nov 16 '16 at 08:17

26

Go to server explorer > Your Database > Right Click > properties > ConnectionString and copy the connection string and past the copied to connectiongstring code :)

answered Nov 16 '16 at 08:17

Mahmmoud Qassem

275
3
2

One of the best answers I've seen so far :) – Stas Ivanov Jul 14 '17 at 14:59
This will break the moment you try it on another machine. While that connection string is valid for your machine, if you take a moment to read it you'll notice that it has the path hardcoded. – Patrick Kelly Dec 07 '19 at 16:01

user1943915 · Answer 3 · 2013-12-13T18:21:27.767

string sqlCon = @"Data Source=.\SQLEXPRESS;" +
                @"AttachDbFilename=|DataDirectory|\SampleDB.mdf;
                Integrated Security=True;
                Connect Timeout=30;
                User Instance=True";
SqlConnection Con = new SqlConnection(sqlCon);

The filepath should have |DataDirectory| which actually links to "current project directory\App_Data\" or "current project directory" and get the .mdf file.....Place the .mdf in either of these places and should work in visual studio 2010.And when you use the standalone application on production system, then the current path where the executable file is, should have the .mdf file.

score 8 · Answer 4 · answered Aug 31 '17 at 18:25

8

For Visual Studio 2015 the connection string is:

"Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Database1.mdf;Integrated Security=True"

answered Aug 31 '17 at 18:25

July.Tech

1,336
16
20

This was the only thing that worked for me with an MDF. I'm using VS2019 with .Net Core 3. – BrianLegg Feb 11 '20 at 20:43

score 1 · Answer 5 · edited Jun 28 '21 at 12:50

1

Alternative solution, where you can have the database in the folder you want inside the solution. That worked for me:

.ConnectionString(@"Data Source=(LocalDB)\MSSQLLocalDB;
                    AttachDbFilename="+AppDomain.CurrentDomain.BaseDirectory+"Folder1\\Folder2\\SampleDatabase.mdf" + ";
                    Integrated Security=True;")

edited Jun 28 '21 at 12:50

OldBuildingAndLoan

2,801
4
32
40

answered Oct 18 '17 at 16:55

f4d0

1,182
11
21

score 1 · Answer 6 · answered Jan 19 '12 at 13:14

1

Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;

answered Jan 19 '12 at 13:14

John Woo

258,903
69
498
492

I tried this modified version" `"Server=.\\SQLExpress;AttachDbFilename=SampleDatabase.mdf;Database=SampleDatabase; Trusted_Connection=Yes"`, and got the following exception: `System.Data.SqlClient.SqlException (0x80131904): A file activation error occurred. The physical file name 'SampleDatabase.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation. Cannot attach the file 'SampleDatabase.mdf' as database 'SampleDatabase'.` – Ahmad Jan 19 '12 at 13:30

score 0 · Answer 7 · answered Jun 11 '14 at 11:45

SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\Samples\MyApp\C#\bin\Debug\Login.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

this is working for me... Is there any way to short the path? like

SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=\bin\Debug\Login.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

score 0 · Answer 8 · answered Dec 14 '22 at 08:09

0

Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename="C:\Users\hasif\Documents\Visual Studio 2015\Projects\vgsoft\SqlserverRepo\data\Database1.mdf";Integrated Security=True;Connect Timeout=30

1

2 ConnectionString

answered Dec 14 '22 at 08:09

lava

6,020
2
31
28

How do I connect to an MDF database file?

8 Answers8

1

2

ConnectionString

Linked