21

I created a .mdf file by SQL Server and put it to a local file. I run my program in a computer which has no SQL Server installed. And also I used classes from the System.Data.SqlClient namespace (i.e. SqlConnection..)

I want to make some kind access connection with .mdf file which was created by SQL Server? Is it possible ?

Breeze
  • 2,010
  • 2
  • 32
  • 43
unbalanced
  • 1,192
  • 5
  • 19
  • 44

3 Answers3

24

I don't think it is possible to interact with an MDF file without going through a SQL Server engine. You would most likely install SQL Server Express (which should be an option if you choose to go through add/remove programs for Visual Studio, or you can download it separately from here - this is the 2008 R2 With Tools option), attach your database, and then connect to that engine from your program.

Many tutorials online will suggest you use the User Instances feature and AttachDbFileName. I suggest you don't go that route as it often leads to confusion - many, many users connect to one instance of the database through Management Studio, a different instance from their program, and then don't understand why one doesn't see the updates made by the other. The User Instances features is deprecated and I highly prefer attaching a database to the instance properly.

In SQL Server 2012 you can download the new SqlLocalDb runtime (you want either the x86 or x64 SqlLocalDB.MSI file from here), which makes this process much easier, as it is a no-maintenance on-demand SQL Server engine. However note that your database will be upgraded to the new 11.0 file format, which means you won't be able to detach/attach or backup/restore to an older version (2008, 2008 R2, etc). I added many troubleshooting techniques for new users starting with SqlLocalDb here.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • actually you are saying to install https://www.microsoft.com/en-us/download/details.aspx?id=23650 that is with management studio and is of 240MB is there any solution available that we donot install management studio and only install its services or instance while deployment simply less size then that one – nouman arshad Aug 08 '16 at 07:33
8

You may try using OrcaMDF which is an open-source project.

What is OrcaMDF?

A C# parser for MDF files. Allows you to read tables, metadata and indexes from MDF files without it being attached to a running SQL Server instance.

(this description is taken from project's page).

Author's blog

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
3

You need SQL to connect to the MDF. You can convert it to an SDF and load the compact edition (free). http://msdn.microsoft.com/en-us/data/ff687142

tzerb
  • 1,433
  • 1
  • 16
  • 35
  • 1
    Note that converting to Compact will only work if your database doesn't use any features or syntax not supported in Compact Edition. Wouldn't Express (also free) be a safer recommendation? It is not as "embedded" but it highly less likely to be problematic. – Aaron Bertrand Apr 28 '12 at 19:09
  • With Express you shouldn't need to use sqloledb. I'm not well versed enough in Compact Edition to know exactly how the interaction differs there, I just know that the file format and features sets are much different. – Aaron Bertrand Apr 28 '12 at 19:13
  • SQL express would work if it works for the OP. With SQL Express you can connect to it the same way. You'll have to use a new connection class with SQL CE (SqlCeConnection). – tzerb Apr 28 '12 at 19:13