how to create a new mdf file in express 2008?

[FlyingBear]

how to create a new mdf file in express 2008?

before, I used some sample projects which have the mdf file, so I can use sql express on local machine. now, for a new project, I want to create a brand new mdf file, but can not figure out how to do it.


thanks

[cicatrix]

This link has the instructions for creating a database file:
http://www.vbdotnetheaven.com/Upload...LDatabase.aspx

[jmcilhinney]

I don't think cicatrix's advice is really what you need in this case. To add a new SQL Server Express database, i.e. an MDF file, to your project you right-click the project in the Solution Explorer and select Add New Item. You can also use Project from the main menu. You then select Server-based Database in the Add New Item dialogue. You then use the Server Explorer or Database Explorer to add tables, etc, to the database.

[FlyingBear]

once "add new item" what should I choose? if I select 'local database", it adds compact sql server, not sql express. I managed to create a new express sql db from sql server management studio, add it as data source from the express vs.

But, the next question pops: how can I distribute program with this mdf file? should each pc install sql express?
once "add new item" what should I choose? if I select 'local database", it adds compact sql server, not sql express. I managed to create a new express sql db from sql server management studio, add it as data source from the express vs.

But, the next question pops: how can I distribute program with this mdf file? should each pc install sql express?

thanks

bear

[jmcilhinney]

once "add new item" what should I choose? if I select 'local database", it adds compact sql server, not sql express.

Do you know what frustrates me? When people post and ask for help, then don't bother to read it properly when it's provided. Go back and read my post and please read it properly this time. I've already told you EXACTLY what to do.

But, the next question pops: how can I distribute program with this mdf file? should each pc install sql express?

Yes. If you create an installer for your app then SQL Server Express can be included as a prerequisite, just like the .NET Framework, so it can be installed automatically.

[FlyingBear]

Hi, jmcilhinney

sorry for any frustrations, you helped me many times, I appreciate it.

I think maybe I was confused by 2 questions: 1. how to add mdf to the project? 2. how to create a new mdf database?

The solution is: 1. add the data source to the project; 2. create the db from sql management studio.


thanks

bear

[jmcilhinney]

SQL Server Express works in two different ways.

1. It can work in the conventionak way that SQL Server does, i.e. you create a new database that is attached to a server permanently. You generally build the database in a client tool like Management Studio, or you can use code. The data is stored in an MDF file and you connect to the database by specifying the server\instance name and the database name.

2. You create a loose MDF file, generally by adding one to your VS project. You then generally build the database in VS and deploy the MDF file with your app. You connect to the database by specifying the path of the MDF file and the name of the server\instance to attach it to at run time.

Option 1 is what you would choose for multi-user apps where they will all use a single database on the network. Option 2 is for single-user apps where one client will connect to the database on the local machine. Adding a server-based database to your VB project is option 2.

[FlyingBear]

jmcilhinney, really appreciate the explaination.

funny thing is: I created a new database in sql management studio, then from vs, connect to the created mdf file (copy it to local), used both methods (partially) you explained. I just want to use it as what access file does before for local user, so, 2) is what I want.


thanks again

bear

[jmcilhinney]

An MDF file is an MDF file so you certainly can create it in Management Studio and then add it to your project. It's a bit more cumbersome though, so I guess you'd only do that if you needed to use an existing database rather than one you were creating new.

[FlyingBear]

do you mean " you'd only do that if you needed to create a new db rather than use an existing database"?

thanks


bear

so I guess you'd only do that if you needed to use an existing database rather than one you were creating new.

[jmcilhinney]

No, I meant it how I said it. You'd only add an MDF file that had been created via Management Studio if it was one that had been created previously with the intention of being attached to the server instance. If you were creating a brand new database to include in your project then you'd create it through VS rather than SSMS because it's simpler.

[FlyingBear]

haha, finally I figured it out why I feel so weird.

In your original reply, you mentioned use the SERVER-Based db in adding new item, I'm using express version, it only has "Service" based db. I never thought it means local mdf file (I thought it mean web service).

Now, it is clear, the "Service" based db is the mdf file I need, and then you rexplaination make 100% meaningful to me.

thanks jmcilhinney, really appreciate your patient.


bear

[jmcilhinney]

Ah, sorry for my mistyping. I didn't actually check and that should have been "Service". It means that it relies on the SQL Server service being running.