none
Working with .mdf files RRS feed

  • Question

  • Im developing a database driven application and am trying to achieve the following scenario;

     

    When possible the application is served by a central database on a server.  When a connection to this central database cannot be resolved the application falls back to a local copy of the database.  The local copy will be read-only.  On launch if the publish location is available the application will check for updates when an update is available and the user clicks install found a new copy of the database will be downloaded with the update.

     

    Anyone have any advice on the best way to achieve this?

     

     

    Thanks in advance

     

    Alex

     

    Thursday, March 6, 2008 2:01 PM

Answers

  • You just need to pay attention to the update that is make sure you have one DataSet for the update which makes direct update to your central database only.  This very important all updates must start from the dataset to central database and it becomes simple to manage.  You need to write code to change connection from your user instance which is read only to central for update.  You may need separate configuration section in your application to manage your many connections.

     

    Thursday, March 6, 2008 3:26 PM
  • Hello,

     

    Good solution except that the local copy must not be read-only ( see the original post )

     

    I used replication only twice in 4 years, but is it not replication ?

    If you put a local database on each computer, how do you it ? Within an local instance or on a Sql Server Express ( supports client side not server side ) ?

     

    I'm facing this problem ( and possibility ) so i'm really interested by a solution like that

     

    Have a nice day

    Thursday, March 6, 2008 8:35 PM

All replies

  • You just need to pay attention to the update that is make sure you have one DataSet for the update which makes direct update to your central database only.  This very important all updates must start from the dataset to central database and it becomes simple to manage.  You need to write code to change connection from your user instance which is read only to central for update.  You may need separate configuration section in your application to manage your many connections.

     

    Thursday, March 6, 2008 3:26 PM
  • I have been doing this by simply changing the database file location in the connection string. As long as the different database files are the same structure (definition), this will work.

     

    First, close all dataviews,etc. and then close your open connection whether it needs closing or not.

    Then, change your database file.

    Then refill all your, datasets, adapters, etc. and reopen your dataviews. When you do a fill, your new connection is automatically opened.

     

    Of course, you will need to write code to see if you need to update your local *.mdf file.

    Thursday, March 6, 2008 3:48 PM
  • Hello,

     

    Good solution except that the local copy must not be read-only ( see the original post )

     

    I used replication only twice in 4 years, but is it not replication ?

    If you put a local database on each computer, how do you it ? Within an local instance or on a Sql Server Express ( supports client side not server side ) ?

     

    I'm facing this problem ( and possibility ) so i'm really interested by a solution like that

     

    Have a nice day

    Thursday, March 6, 2008 8:35 PM
  • Unless I misunderstand, original post says that local copy is read only? Right?

     

    Anyway, you can open any *.mdf file as I described above in your program as long as they are the same structurally. They can even have different data.

     

    For example:

     

    string myFileNameAndPath = @"c:\temp\MyDB.mdf";

     

    MyProgram.Properties.Settings.Default.MyConnectionString =
          "Data Source=.\\SQLEXPRESS;AttachDbFilename="
          + myfileNameAndPath
          + ";Integrated Security=True;User Instance=True";

     

    If your compiler tells you the connection string is read only, see my post titled "Open any *.mdf".

     

    I would think one can be a user instance and one can be a server instance. Just change your connection string accordingly. I have only done it with user instances.

     

    Naturally, if both DB files are in the same location, they will have to have different names.

    Thursday, March 6, 2008 10:27 PM