Select Database Type on Deployment

7 posts, 0 answers
  1. Brian Goldman
    Brian Goldman avatar
    34 posts
    Member since:
    Mar 2005

    Posted 17 Dec 2009 Link to this post

    Hello,
    This has probably been answered somewhere but I can't find it. I'm just getting started with open access. I want to develop a web application that will work with either SQL Server 2005, SQL Server 2008 or MySQL. When the user is installing the web application on their server I want them to be able to select among the 3 listed above the database they have available to them or that they want to use.

    So for example if they have mysql availble, they would change the connection string in the web.config to point to their mysql database upload the application and the application would use that.  If they have SQL 2005 they would do the same but point it to their SQL 2005 database.

    So my question is, what is the easiest way to develop this application in open access so that the user can simply change the connection string to the one of the available database types and use the application?

    Thank you,
    Brian
  2. TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 18 Dec 2009 Link to this post

    Hi Brian,

    One way I could think of is to have your databases listed in the connections element and backendconfigurations element. In each connection element you use a different connection id like:

          <connection id="Connection1">
            <backendconfigurationname>mssql2005Configuration</backendconfigurationname>
            ...................
          </connection>
          <connection id="Connection2">
            <backendconfigurationname>mysqlConfiguration</backendconfigurationname>
            .................
          </connection>

    and in your backed configuration you have similar:

        <backendconfigurations>
          <backendconfiguration id="mssqlConfiguration" backend="mssql">
            <mappingname>mssqlMapping</mappingname>
            ..........
          </backendconfiguration>
          <backendconfiguration id="mysqlConfiguration" backend=".....">
            <mappingname>mysqlMapping</mappingname>
            ..........
          </backendconfiguration>
        </backendconfigurations>

    Then change you objectscopeprovider (a sample is supplied with OpenAccess in source): It has a call to the method Database.Get(......) where the first parameter is the connection id from the config.file like: Database.Get("Connection1"). With the Database instance (returned from the Get method) at hand you can get an object scope from the usual way (see the ObjectScopeProvider.cs)

    So the short story is:

    Change your ObjectScopeProvider in such a way that it is aware of the different connection ids in the config


    Regards

    Henrik
  3. DevCraft banner
  4. Brian Goldman
    Brian Goldman avatar
    34 posts
    Member since:
    Mar 2005

    Posted 23 Dec 2009 Link to this post

    Thanks Henrik that helps a little but it's not quite there. One problem is that the connection string seems to be compiled into the class library which is not going to work at all.

    This is what I'm trying to do. Deploy web site with a web.config with connection strings settings like this.
    <connectionStrings>
    If using mysql uncomment and enter your server info 
    !--<add name="TestDB" connectionString="server=mysqlserver;user id=dbusername;password=dbpassword;persist security info=True;database=databaseName;use procedure bodies=False" providerName="MySql.Data.MySqlClient" />--> 
     
    If using SQL 2005 uncomment and enter your server info 
    !--<add name="TestDB" connectionString="data source=sql2005servername;initial catalog=databasename;persist security info=True;user id=databaseusername;password=dbpassword" providerName="System.Data.SqlClient" />-->
    </connectionStrings>

    I think I have all of the backend configuration and mappings correct but no matter I put in the web application connection string it only uses one database. It will not write to the other database if I change the web.config connection string.

    Thanks,
    Brian



  5. TSE
    TSE avatar
    381 posts
    Member since:
    Sep 2008

    Posted 25 Dec 2009 Link to this post

    Hi Brian,

    Ok, I misunderstood your question.

    Did you take a look at this article. I think it suits your needs.

    Regards and merry Christmas

    Henrik
  6. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 28 Dec 2009 Link to this post

    Hello Brian Goldman,

    Just to let you know that you could also use the standard .NET connection strings to achieve your goal and not only ones in the format OpenAccess used to use prior to Q3 2009.(as discussed in the article that Henrik pointed). Here is an example of what could you have in your App.config and web.config files:
    This is the connections section from App.config:
    <connections>
      <connection id="DatabaseConnection2">
        <connectionString>Data Source=webteamtest64\telerik;Initial Catalog=Telerik;Integrated Security=True</connectionString>
        <backendconfigurationname>mssqlConfiguration</backendconfigurationname>
      </connection>
      <connection id="DatabaseConnection1">
        <connectionString>Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True</connectionString>
        <backendconfigurationname>mssqlConfiguration</backendconfigurationname>
      </connection>
    </connections>

    This is the connectionStrings section from web.config:
    <connectionStrings>
        <add name="DatabaseConnection1" connectionString="data source=.\SQLEXPRESS;initial catalog=lqlq;integrated security=True" providerName="System.Data.SqlClient"/>
        <add name="DatabaseConnection2" connectionString="data source=testServer\telerik;initial catalog=Telerik;integrated security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>

    You could then configure just the connection strings in the web.config for the needs of your application.

    Greetings,
    Zoran
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  7. Brian Goldman
    Brian Goldman avatar
    34 posts
    Member since:
    Mar 2005

    Posted 28 Dec 2009 Link to this post

    I am very close now to what I want. The last problems is that it appears the connections cannot have the same name. So depending on the connection string chosen, the name in the objectscopeprovider has to change. For example
    Telerik.OpenAccess.Database.Get("MSSQLDB", xmlDoc.DocumentElement, allDlls)
    or
    Telerik.OpenAccess.Database.Get("MYSQLDB", xmlDoc.DocumentElement, allDlls)

    What is the best way to handle this problem?

    I'm just starting with OpenAccess but so far it has been a nightmare, I hope it gets better. Thank you Henrik and Zoran for your help, if it wasn't for you, I would have gave up already.

  8. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 04 Jan 2010 Link to this post

    Hello Brian Goldman,

    Let me clarify a bit the way OpenAccess works with connection strings. As you can see in the <openaccess> configuration node, there are several <connection> nodes that map each connection string to a backend-specific configuration. This is required as particular database backend (for instance MSSQL) could have settings or field/column mappings that are not relevant to another backend (like MySql). Having this in mind, if during deployment you change a connection string that was initially used obtain a mssql connection to connect to a mysql server, this would most probably lead to runtime errors as the new mysql connection string will use the old mssql backend configuration.

    To avoid such behavior it would be best if you do not change the connection strings but use an additional custom configuration node (for example <activeConnection name="connectionStringName" />) that you can read and use at runtime to determine which connection should be used. Note that you can still change the server and database names according to the deployment machine, just keep the backend that the connection string was initially defined for.

    When you already know which connection string should be used, there are two possible approaches. You can either call the Database.Get() method with the appropriate connection string name as you pointed in your last post or create an additional ObjectScopeProvider for each connection string. It is up to you to decide which method to use, they will both work. Hope that helps.

    All the best,
    Alexander
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Back to Top
DevCraft banner