When you start working with OpenAccess, one of the first tasks you're going to face is the need set a connection string to your database. And odds are, the connection string you're going to start with is not going to be the connection string your going to use on your production server. It might not even be the connection string your going to use in your test environment. Point is, you need to be able to store multiple connection strings and easily "cue" OpenAccess to use the correct connection details at runtime. Fortunately, OpenAccess makes this easy.

 

UNDERSTANDING OPENACCESS CONNECTIONS

First things first: where does OpenAccess store database connection info? By default, when you start working with OpenAccess in Visual Studio (where you'll likely create a class library to hold your model/persistent classes/DAL), all OpenAccess configuration information will be saved in your class library's App.Config file. This includes mapping information related to your persistent classes and database connection details. Connections are stored in the aptly named "connections" section of the OpenAccess config file, and they look something like this:

XML (App.Config Snippet)

<configuration> <configSections> <section name="openaccess" type="Telerik.OpenAccess.Config.ConfigSectionHandler, Telerik.OpenAccess.Config, Version=1.0.0.0, Culture=neutral, PublicKeyToken=7ce17eeaf1d59342" /> </configSections> <openaccess xmlns="http://www.telerik.com/OpenAccess"> <references /> <connections> <connection id="DatabaseConnection1"> <databasename>YourDbName</databasename> <servername>(LOCAL)\SQLEXPRESS</servername> <integratedSecurity>True</integratedSecurity> <backendconfigurationname>mssqlConfiguration</backendconfigurationname> </connection> </connections> <backendconfigurations> <backendconfiguration id="mssqlConfiguration" backend="mssql"> <lockTimeout>5000</lockTimeout> <mappingname>mssqlMapping</mappingname> </backendconfiguration> </backendconfigurations>
...

When working with OpenAccess, you'll generally always have at least one connection in the App.Config file that contains your persistent classes. This is where OpenAccess will look for the connection to use when trying to update your database (if you're using forward mapping and you're allowing OA to update your database schema). You can store many connections here, though. You simply add connections and give them a unique name. So, for a "real world scenario," I might have a config file that looks like this:

XML

<connections> <connection id="DEVdb"> <databasename>YourLocalDevDb</databasename> <servername>(LOCAL)\SQLEXPRESS</servername> <integratedSecurity>True</integratedSecurity> <backendconfigurationname>mssqlConfiguration</backendconfigurationname> </connection> <connection id="TESTdb"> <databasename>YourRemoteTest</databasename> <servername>YourSeverNameOrIP</servername> <integratedSecurity>False</integratedSecurity> <user>YourSqlUsername</user> <password>YourSqlPassword</password> <backendconfigurationname>mssqlConfiguration</backendconfigurationname> </connection> <connection id="PRODdb">
       ...
   </connection> </connections>

To use these connections at runtime, you need to simply supply the connection name (or "id") to the OpenAccess Database.Get() method, like this:

C#

var db = Telerik.OpenAccess.Database.Get("YourConnectionName");
 

STORING CONNECTIONS IN WEB.CONFIG

Now that you know how OpenAccess connections are configured, it's important to understand where you can put these connection details. By default, when you start working with OpenAccess, you'll probably store your DB connection values in your OpenAccess project App.Config. When you build your class library, the App.Config will be embedded in the assembly and your connection strings will travel with the assembly. You'll drop your class library assembly in your web project, and then configure your site Web.Config to look something like this:

XML (Web.Config Snippet)

<configuration> <system.web>
    ...
  </system.web> <openaccess xmlns="http://www.telerik.com/OpenAccess"> <connections /> <references> <reference assemblyname="YourOpenAccessDLLName" configrequired="True" /> </references> </openaccess>
...

Where are the connection strings? Embedded in your assembly! You don't have to set your connection strings in your web.config. If you've set the connection values in your embedded App.Config, they'll work just fine in your web project. The major drawback to this approach, though, is that you have to recompile your OpenAccess class library every time you make a change to your connection details. That's not cool.

Fortunately, OpenAccess allows us to "split" our configuration details across multiple configuration files. In other words, we can leave all of our persistent class mapping values in our OpenAccess class library App.Config file and only move our connection details to the web.config. At run time, OpenAccess will automatically look in booth our Web.Config and App.Config for named connections, and if it finds the named connection in either location, it will use it to open a connection to the database. So now, we can move our "real world connections" out of our App.Config to our Web.Config, like this:

XML (Web.Config Snippet)

<configuration> <system.web>
    ...
  </system.web> <openaccess xmlns="http://www.telerik.com/OpenAccess"> <connections> <connection id="DEVdb"> <databasename>YourLocalDevDb</databasename> <servername>(LOCAL)\SQLEXPRESS</servername> <integratedSecurity>True</integratedSecurity> <backendconfigurationname>mssqlConfiguration</backendconfigurationname> </connection> <connection id="TESTdb"> <databasename>YourRemoteTest</databasename> <servername>YourSeverNameOrIP</servername> <integratedSecurity>False</integratedSecurity> <user>YourSqlUsername</user> <password>YourSqlPassword</password> <backendconfigurationname>mssqlConfiguration</backendconfigurationname> </connection> <connection id="PRODdb">
        ...
      </connection> </connections> <references> <reference assemblyname="YourOpenAccessDLLName" configrequired="True" /> </references> </openaccess>
...

With our connection details in the Web.Config, we can easily change our connection details for our OpenAccess database connections without requiring a recompile of our class library project. Much better!

 

WRAP-UP

Setting connection details for your OpenAccess projects is both easy and flexible. Reviewing what you should know now:

  • You can set connection details in your OpenAccess project's App.Config, your site's Web.Config, OR both
  • You need to keep at least one connection in your App.Config for Forward Mapping database design-time schema updates
  • You use different connections at runtime simply by supplying the connection ID to the Database.Get() method

 

Easy enough, right? In my next post, I'll show you how you can even improve the OpenAccess ObjectScopeProvider class to make changing connections at runtime as easy as changing a config file value. 'Til then, have fun saving time with OpenAccess!


About the Author

Todd Anglin

is an avid HTML5, CSS3, and JavaScript advocate, and geek about all things web development. He is an active speaker and author, helping developers around the world learn and adopt HTML5. Todd works for Telerik as VP of HTML5 Web & Mobile Tools, where his current technical focus is on Kendo UI. Todd is @toddanglin on Twitter

Related Posts

Comments

Comments are disabled in preview mode.