none
VS2008 Solution with multiple projects. Best practices for connection strings and SSCE files?

    Question

  • Hi everyone - I'm getting myself into a right muddle and am looking for advice.

     

    How do people deal with connection string matters when taking a dataset defined in one assembly (and by default using the connection strings defined within that assembly in the settings files) and then using that assembly in an app (which also has a requirement to see the same physical database).

     

    I'm not sure I've explained that terribly well but what I'm trying to avoid is duplicate copies of my database which so far seems to be the only way that I've managed to make stuff work.  I'm not very experienced with SSCE data access (I'm a serices/sockets/ip person) and this may just be ignorance.  Most of the examples seem to assume that the data is in the same place as the app whereas I'm trying to collate a whole series of functions into a helper assembly that I'd like to re-use for other things.

     

    Ideally the dataset designer would provide an easy way of choosing from centralised connection strings - perhaps this is what the Dataset Project implies - but again the docs are mostly focused on SQL Server?  Otherwise the best I've been able to do is make the connection properties public and try to update them that way or use a post-build action to copy the database from my datalayer project directory to that of my application |DataDirectory|

     

    I suppose the question might be if you have a dataset containing multiple tableadapters that assumes one connection string, is there any easy way to keep such strings co-ordinated between projects without hardcoding them?  With a server resource, the same non-specific connection string resolves to the same server (if that make sense) and this seems to be were I can't make the logical shift.

     

    Does anyone have any thoughts and can they please point this SSCE noob in the right direction?

     

    Cheers

    Tuesday, January 15, 2008 8:08 AM

All replies

  • Hi devlinse

     

    Thanks for using our product.

    I am really not sure if I got your question perfectly right. But I will try to answer in simplest way I can from what I could make.

    If this is not the information you are looking for, can please let me know again your exact requirement maybe in terms of a prototype.

     

    SqlCe supports DataDirectory feature. DataDirectory is usually nice way to shield the location where you want to store the application specific data. (Which may or many not be same folder)

    Usually when you do a click once deployment the path of data directory is decided at time of deployment.

     

    If you want to explicitly set your data directory you can use something like this for your application (should not matter how many projects are there, so even if your solution has multiple projects, enlist them in same application domain and keep the data directory consistent)

    AppDomain.CurrentDomain.SetData("DataDirectory", @"C:\MyData\");

     

    "C:\MyData" - You can set any path you want in the system. I guess at some place you will have to hardcode this (probably settings file).

     

    While declaring a sqlce connection string you should declare something like

     

    string dbFileName = "TestDb.sdf";

    string dbConnectionString = "Data Source=|DataDirectory|" + dbFileName;

    SqlCeConnection sqlCeConnection = new SqlCeConnection(dbConnectionString);

     

    Now once you specify DataDirectory in your connection the sdf file at that location will be used.

    In case the particular directory does not exist the following will happen:

    For applications that are put in a folder on the user's computer, the database folder uses the application folder.
    For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.

     

    Now as you already know what DataDirectory you had set or your are using, you can at same time write your application logic too based on that location.

     

    This way you can have multiple connections working on same data base while without hard coding paths into each connection string.

     

    From Read Me:

    New features

    SQL Server Compact Edition includes some features that are not included in SQL Server Mobile 3.0. These features include the following:

    |DataDirectory| substitution string support
    ClickOnce support

    |DataDirectory| substitution string support

    |DataDirectory| (enclosed in pipe symbols) is a substitution string that indicates the database path. Therefore, you do not have to include the full path in the code. When you include the full path in the code, you may experience problems because the full database path can be serialized in different locations. The |DataDirectory| substitution string also makes it easy to share a project and to deploy an application.

    For example, if you include the full path in the code, the application can have the following connection string.

    Data Source= c:\program files\MyApp\Mydb.sdf

    If you use the |DataDirectory| substitution string, the application can have the following connection string.

    Data Source = |DataDirectory|\Mydb.sdf

    To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules are applied to access the database folder:

    For applications that are put in a folder on the user's computer, the database folder uses the application folder.
    For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.
    Note The .NET Compact Framework does not support the AppDomain.SetData method on Microsoft Windows Mobile-based devices. If an application calls the AppDomain.SetData method on a Windows Mobile-based device, you receive an error message.

     

    Tuesday, February 05, 2008 12:54 PM
  •  

    Hi Ravi,

     

    Thanks very much for such a detailed answer.  I'm not sure if it answers my question or not, but it was worth it just to find out about AppDomain.SetData which I had no idea about.

     

    I was aware of |DataDirectory| but where I was getting stuck was ensuring that multiple projects (all in the same solution) used the same connectionstring data at both design time and runtime.

     

    If you imagine something like a DAL with the database residing as a member of that project, I was finding difficult to ensure that other projects kept up with schema changes etc.  I think that most of my problems come from the design time tools (xsd's) insisting on storing connection string information in the project - rather than allowing multiple projects to centralise them. 

     

    I'm may be misunderstanding something fundamental about data access or it may be that SetData is actually what I want to make things a bit more deterministic.

     

    Thanks

    Friday, February 08, 2008 7:19 AM