Are there any best practices or known issues (such as performance issues) with dynamic connections in .netTiers in ASP.NET?
Here are the details -
A few days ago, I posted the good news that .netTiers works out of the box with SQL Azure (see this thread). Unfortunately, when you deploy a Web Role (an ASP.NET web site or web service) to Windows Azure, you cannot change the web.config file.
Instead, Windows Azure allows you to change its internal configuration files. Such changes cause your web roles to restart, just like changes to the web.config file cause your web sites and web services to reload. In the Azure configuration files (ServiceDefinition.csdef and ServiceConfiguration.cscfg), I have defined a setting called "NetTiersConnectionString":
<?xml version="1.0" encoding="utf-8"?><ServiceDefinition name="MYPROJECTAzure" xmlns="http://schemas.microsoft.com/ServiceHosting/2008/10/ServiceDefinition"> <WebRole name="MYPROJECT_WebRole" enableNativeCodeExecution="true"> <InputEndpoints> <!-- Must use port 80 for http and port 443 for https when running in the cloud --> <InputEndpoint name="HttpIn" protocol="http" port="8080" /> </InputEndpoints> <ConfigurationSettings> <Setting name="NetTiersConnectionString" /> </ConfigurationSettings> </WebRole></ServiceDefinition><?xml version="1.0"?><ServiceConfiguration serviceName="MYPROJECTAzure" xmlns="http://schemas.microsoft.com/ServiceHosting/2008/10/ServiceConfiguration"> <Role name="MYPROJECT_WebRole"> <Instances count="2" /> <ConfigurationSettings> <Setting name="NetTiersConnectionString" value="Server=tcp:MYSQLAZURESERVER.ctp.database.windows.net;Database=master;User ID=MYLOGIN;Password=MYPASSWORD;Trusted_Connection=False;" /> <Setting name="DBVersionRequired1" value="1" /> <Setting name="DBVersionRequired2" value="1" /> <Setting name="SSLNotForced" value="1" /> </ConfigurationSettings> </Role></ServiceConfiguration>
Then, in each of my pages I use this connection string like this:
I first add the connection when the page is loaded -
Private Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init DataRepository.AddConnection("MYCONNECTION", sNetTiersConnectionString) End Sub
In other places in the page, I use the dynamic connection - for example like this:
DataRepository.Connections("MYCONNECTION").Provider.MYENTITYProvider.Insert(oMYENTITY)
Finally in the page's unload event handler, I remove the connection:
Private Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload DataRepository.Connections.Remove(MYCONNECTION) End Sub
Is this ok?It works, but are there any side effects I'm not aware of or performance issues that are known when using such dynamic connections?
SuperJeffe: I changed the ConnectionStrings collection, reading the actual connection string from Windows Azure configuration and it worked! I modified the .netTiers templates and added a "IsConnectionStringAzure" property:
<%@ Property Name="IsConnectionStringAzure" Type="System.Boolean" Default="False" Category="02. Framework Generation - Optional" Description="Indicates if at runtime the netTiersConnectionString is read from Window Azure's ServiceConfiguration.cscfg or from Web.config/app.config." %>
Just set "IsConnectionStringAzure" to "true" (the default is "false") and at runtime, your DAL will read from Windows Azure configuration instead of web.config (or app.config). When creating a new Windows Azure project, add this to your ServiceDefinition.csdef file:
<ConfigurationSettings> <Setting name="netTiersConnectionString" /> </ConfigurationSettings>
And then in your ServiceConfiguration.cscfg add this:
<ConfigurationSettings> <Setting name="netTiersConnectionString" value="Server=tcp:MYSQLAZURESERVER.ctp.database.windows.net;Database=master;User ID=MYLOGIN;Password=MYPASSWORD;Trusted_Connection=False;" /> </ConfigurationSettings>
Your netTiersConnectionString can be your regular connection string that point to a local on-premise SQL Server database (for development), or a SQL Azure database when you deploy to the Windows Azure cloud (Staging or Production).
I attach the changes to the .netTiers 2.3 templates I made and below I describe in detail the actual changes.
Blake: so far everything is working great, but to make sure we will scan all templates that use "connectionString" and confirm that there's nothing else that needs to be changed.
I also have a series of videos I've created that cover Windows Azure, SQL Azure and how to use .netTiers with both - I'll publish them soon.Thanks to my co-workers, Arif and Lanh, who helped me think through the steps we needed to implement to make this work.
Good times!
Emmanuel Hunahttp://www.ehuna.org
Download the ZIP file here: http://www.ehuna.org/files/NetTiers-Support-Windows-Azure.zipHere's what you'll find in the ZIP file:
\doc 01-Readme-NetTiers-Azure-Support-Code-Changes.txt - a log of the changes I made in the original (generated) C# code. 02-Readme-NetTiers-Azure-Support-Template-Changes.txt - a log of the changes I made to the .netTiers 2.3 templates. I also copy it below.
\NetTiers.cst - changes to the main .netTiers template. Copy this file over your NetTiers.cst template.
\NetTiers\References\ AzureSDKv1_0\Microsoft.ServiceHosting.ServiceRuntime.dll - Copy this folder and DLL to your NetTiers\References folder. In the future we can add support for different versions of Windows Azure (currently there's only v1.0).
\NetTiers\DataAccessLayer\DataRepository.cst - Changes to the DataRepository.cst template, copy this file over your DataAccessLayer\DataRepository.cst template.
\NetTiers\VisualStudio\vsnet2005.project.cst - Changes to the vsnet2005.project.cst template. When "IsConnectionStringAzure" to "true", I add a reference to Microsoft.ServiceHosting.ServiceRuntime.dll so at runtime I can read the "netTiersConnectionString" from the Windows Azure configuration file ("ServiceConfiguration.cscfg"). Copy this file over your VisualStudio\vsnet2005.project.cst template.
Finally here's a detailed description of the changes that were made to the .netTiers templates:
1) Copy C:\Program Files\Windows Azure SDK\v1.0\ref Microsoft.ServiceHosting.ServiceRuntime.dll Microsoft.ServiceHosting.ServiceRuntime.xml to \NetTiers\References\AzureSDKv1_02) Edit NetTiers.cst: Add under <%-- 2. Framework Generation Category --%> <%@ Property Name="IsConnectionStringAzure" Type="System.Boolean" Default="False" Category="02. Framework Generation - Optional" Description="Indicates if at runtime the netTiersConnectionString is read from Window Azure's ServiceConfiguration.cscfg or from Web.config/app.config." %>3) Edit NetTiers\DataAccessLayer\DataRepository.cst and add:<%@ Property Name="IsConnectionStringAzure" Type="System.Boolean" Default="False" Category="02. Framework Generation - Optional" Description="Indicates if at runtime the netTiersConnectionString is read from Window Azure's ServiceConfiguration.cscfg or from Web.config/app.config." %>4) Edit NetTiers\VisualStudio\vsnet2005.project.cst<%@ Property Name="IsConnectionStringAzure" Type="System.Boolean" Default="False" Category="02. Framework Generation - Optional" Description="Indicates if at runtime the netTiersConnectionString is read from Window Azure's ServiceConfiguration.cscfg or from Web.config/app.config." %>5) Edit NetTiers.cst: After line 1904: this.GetTemplate("DataRepository.cst").SetProperty("VisualStudioVersion", VisualStudioVersion); Add: this.GetTemplate("DataRepository.cst").SetProperty("VisualStudioVersion", VisualStudioVersion); this.GetTemplate("DataRepository.cst").SetProperty("IsConnectionStringAzure", IsConnectionStringAzure);6) Edit NetTiers.cst: After line 2822: this.GetTemplate(projectTemplate).SetProperty("ValidationType", ValidationType); Add: this.GetTemplate(projectTemplate).SetProperty("ValidationType", ValidationType); this.GetTemplate(projectTemplate).SetProperty("IsConnectionStringAzure", IsConnectionStringAzure);7) Edit NetTiers.cst: After line 1303: SafeCopyFile(this.CodeTemplateInfo.DirectoryName + "\\References\\nunit.framework.dll", libPath + "\\nunit.framework.dll"); Add: SafeCopyFile(this.CodeTemplateInfo.DirectoryName + "\\References\\nunit.framework.dll", libPath + "\\nunit.framework.dll"); if(IsConnectionStringAzure) { SafeCopyFile(this.CodeTemplateInfo.DirectoryName + "\\References\\AzureSDKv1_0\\Microsoft.ServiceHosting.ServiceRuntime.dll", libPath + "\\Microsoft.ServiceHosting.ServiceRuntime.dll"); }8) Edit NetTiers\VisualStudio\vsnet2005.project.cst: After line 258: <ItemGroup> Add: <% if (IsConnectionStringAzure) { %> <Reference Include="Microsoft.ServiceHosting.ServiceRuntime, Version=0.2.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35, processorArchitecture=MSIL"> <SpecificVersion>False</SpecificVersion> <HintPath>..\References\Microsoft.ServiceHosting.ServiceRuntime.dll</HintPath> </Reference> <%}%>9) Edit NetTiers\DataAccessLayer\DataRepository.cst After line 24: using <%=DALNameSpace%>.Bases; Add: using <%=DALNameSpace%>.Bases; <% if ( IsConnectionStringAzure ) { %> using Microsoft.ServiceHosting.ServiceRuntime; <% } %>10) Edit NetTiers\DataAccessLayer\DataRepository.cst Replace in line 299, collection ConnectionStrings: public static ConnectionStringSettingsCollection ConnectionStrings { get { <% if ( IncludeDesignTimeSupport ) { %> // use default ConnectionStrings if _section has already been discovered if ( _config == null && _section != null ) { return WebConfigurationManager.ConnectionStrings; } return Configuration.ConnectionStrings.ConnectionStrings; <% } else { %> return WebConfigurationManager.ConnectionStrings; <% } // end if ( IncludeDesignTimeSupport ) { %> } } With: public static ConnectionStringSettingsCollection ConnectionStrings { get { <% if ( IsConnectionStringAzure ) { %> ConnectionStringSettingsCollection oConnectionStrings = new ConnectionStringSettingsCollection(); ConnectionStringSettings oSettings = new ConnectionStringSettings(); oSettings.Name = "netTiersConnectionString"; oSettings.ConnectionString = RoleManager.GetConfigurationSetting("netTiersConnectionString"); oConnectionStrings.Add(oSettings); return oConnectionStrings; <% } else { %> <% if ( IncludeDesignTimeSupport ) { %> // use default ConnectionStrings if _section has already been discovered if ( _config == null && _section != null ) { return WebConfigurationManager.ConnectionStrings; } return Configuration.ConnectionStrings.ConnectionStrings; <% } else { %> return WebConfigurationManager.ConnectionStrings; <% } // end if ( IncludeDesignTimeSupport ) { %> <% } // end if ( IsConnectionStringAzure ) { %> } }
Hello,
Although that works, that doesn't look safe todo. I'll check around and let you know.Thanks-Blake Niemyjski
Blake Niemyjski CodeSmith Tools, LLC. Software Development Engineer Blog: http://windowscoding.com/blogs/blake/ .NetTiers team | Visit http://www.nettiers.com
Thanks Blake - I appreciate any help and direction you can provide.
No, there may be a few extra string setters in the code, but nothing that's going to cause any significant performance issues. In the end, your just setting a connection string.
I am just wondering if there is some way to add a "Azure" sensing code to the default connection string stuff. Instead of searching for the ConnectionString in App.Config or Web.Config, if it knows it's azure, search for it in it's config. That way, people don't have to change code.
jeff
---------------------------------------------------------------------- Member of the .NetTiers team | Visit http://www.nettiers.com----------------------------------------------------------------------
Jeff,
Yes, that sounds like a good idea - it's very easy to read the connection string from the Azure configuration:
Imports Microsoft.ServiceHosting.ServiceRuntime sNetTiersConnectionString = RoleManager.GetConfigurationSetting("NetTiersConnectionString")
I'm thinking we could add a property in NetTiers.cst:
<%@ Property Name="IsHostedInAzure" Type="System.Boolean" Default="False" Category="04. General - Advanced" Description="If true the connection string will be read from Azure's ServiceConfiguration.cscfg configuration file instead of web.config or app.config " %>
Can you point me to the template where I could make this change?
You would need to make this change in the various templates. First, You would need to update the .netTiers cst. The best thing to do would be to do a windows search through all the templates for any places where its looking up the connection string.Thanks-Blake Niemyjski
Blake,
Thanks for the tip - can you just point me to one example where a template is looking up the connection string? I'll then search for similar patterns in all other templates.
NetTiersSection.Providers[i].Parameters["connectionStringName"] = _connectionStringName; // remove previous connection string, if any NetTiersSection.Providers[i].Parameters.Remove("connectionString"); if ( !String.IsNullOrEmpty(_connectionString) ) { NetTiersSection.Providers[i].Parameters["connectionString"] = _connectionString; }
DataRepository.CS
Just do a search for connectionString :)
Thanks-Blake Niemyjski
I believe you can just change this property in the DataRepository.cs file.
1: /// <summary>
2: /// Gets a reference to the ConnectionStringSettings collection.
3: /// </summary>
4: public static ConnectionStringSettingsCollection ConnectionStrings
5: {
6: get
7: {
8: // use default ConnectionStrings if _section has already been discovered
9: if ( _config == null && _section != null )
10: {
11: return WebConfigurationManager.ConnectionStrings;
12: }
13:
14: return Configuration.ConnectionStrings.ConnectionStrings;
15: }
16: }
This method just needs to return the ConnectionStringSettingsCollection. Hopefully you can do that and I think your good.
Something tells me there might be another spot to check, I'm pretty sure I remember there's another place where this needs to be changed.Thanks-Blake Niemyjski
Very nice. Great work!
Glad you liked it! I cross posted the template changes on my blog:
Modifying .netTiers templates to support Windows Azure configuration http://blog.ehuna.org/2009/09/modifying_nettiers_templates_t.html
Is there any way these changes could make it into the main .netTiers branch?This way in the next release (2.4 or 3.0?), out of the box ,netTiers will support reading the connection string from Windows Azure configuraton.
I'll review the changes and get it into a nightly . Is there anyway you could download the templates via svn and then drag your templates over them and then right click and select create patch and then attach it here.Thanks-Blake Niemyjski
Hi Blake,
I'll try to find some time to do it - but I need to set this up for the first time - snv client, etc...What SVN client do you suggest? Where do I find the credentials I can use?
Alternatively, this ZIP file:
http://www.ehuna.org/files/NetTiers-Support-Windows-Azure.zip
contains everything that's needed.
Once I get a bit of time, I also want to make one more change: adding instructions to VisualStudio/reportHTML.xsl for Windows Azure users (basically adding the "netTiersConnectionString" to Azure's ServiceDefinition.csdef and ServiceConfiguration.cscfg.