How do I use a SQL Server Express database (.mdf) file like I would an Access (.mdb) file in my VB.NET project?
Posted on 2009-12-21
In the past I have used Access .mdb files with my VB6 applications. I include the .mdb file in my setup package and my code looks for the database file in the user's program data directory.
Now I am working in VB.NET with Visual Studio 2008, and I would keep using Access database files except that Microsoft is not making a 64-bit version of the JET engine. They are still supporting it in 32-but mode, but I suppose that that will not last forever. I would like to use a more current database engine that will continue to be supported for years to come.
It seems that SQL Server Compact is only available in 32-bit, as well, so I am going with SQL Server Express. What I don't understand is how to include the .mdf file in my application and how to connect to it.
If I use the "Add New Data Source" functionality in Visual Studio, it creates connection strings for me that include the server name that I don't think will exist on my client's machines. This is exam software that I will sell to students who are preparing to take an exam, such as an SAT exam. They students have no database software on their systems, so no SQL Server. I understand that I can use SQL Server Express 2005 in my project somehow. I just don't get how to do it.
If I had to "do it all myself", maybe I would include the .mdf file as a "content file", and let the setup program copy the file somewhere, like the user's program data directory. Then, I guess I would have to attach to the database file. But I don't understand how I fire up SQL Server and attach the database so I can use it.
I would like to let Visual Studio handle as much of that as possible, but I find that if I use the built-in functionality, I have to specify the server and stuff that is specific to my system.
So, what do I do?
I need to be able to install this on Windows XP, Windows Vista, and Windows 7, all in 32-bit and 64-bit. I understand I may have to build multiple packages depending on the user's operating system, but I am hoping to have as few installation sets as possible.
Eventually I will use InstallShield, which I have purchased, but at this point I still don't understand how to integrate the database.