The problemYou have SQL Servers for each of your environments (development, test, pre-prod, production) and in each one, the table names are different. In fact, it’s not the table names that are different but the schema names but since the schema name is part of the table name, it must be specified.Ex : dev.ZeTable et prod.ZeTable
Easy to solve, right? Just put the schema value in the config file and do a simple concatenation at runtime. Well, it’s not as easy as it seams because the table name is stored in an attribute of the partial class generated by the LINQ to SQL designer and Microsoft didn’t provide a way or method to change it at runtime.
[Table(Name="dev.ZeTable")] public partial class TheTable
SolutionThe solution is to dynamically load at runtime a mapping specific for each environment. Here’s how to do it:
Initial Mapping Use the LINQ to SQL designer to create the initial mapping. The .dbml file will act as the starting point for our specialized mappings.
SqlMetalNext, use the SqlMetal.exe tool to generate a mapping file but instead of pointing the tool to the database and redo all our customization in XML, the trick is to point it to the .dbml file we created earlier using the LINQ to SQL designer. This way, all our initial customizations are preserved. Nice!
From the Visual Studio 2008 Command Prompt, invoque SqlMetal like this :C:\>SqlMetal /map:"MyApp.PROD.map" "C:\Visual Studio 2008\Projects\MyDataClasses.dbml" /code:"test.cs"Microsoft (R) Database Mapping Generator 2008 version 1.00.21022for Microsoft (R) .NET Framework version 3.5Copyright (C) Microsoft Corporation. All rights reserved.
SqlMetal.exe will generate a C# or VB class. We won’t use it so you can delete it.
Next, open the mapping file and change the name of the tables to reflect the right schema:<Table Name="prod.ZeTable" Member="TheTables">
A good practice might be to include the schema name in the mapping file name:Ex : MyApp.PROD.map
DataContextNext, we need to dynamically load the mappings at runtime by specifying a connection string and the mapping info in the DataContext constructor. The Microsoft’s documentation is located here :http://msdn.microsoft.com/en-us/library/bb534562.aspx
You can store that info in the config file and load it dynamically: <appSettings> <add key="MyAppConnectionString" value=" MyAppDev" /> <add key="MyAppLinqMappings" value="c:\MyApp.DEV.map" /> </appSettings> <connectionStrings> <add name="MyAppDev" connectionString="Data Source=MyDevServer;Initial Catalog=MyDatabase;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings>
//Read the ConnectionStrings from the config file TheConnectionString = ConfigurationManager.ConnectionStrings[ConfigurationManager.AppSettings.Get("MyAppConnectionString")].ConnectionString; //Read the LINQ mapping and store it in memory System.Data.Linq.Mapping.XmlMappingSource TheLinqMappings = System.Data.Linq.Mapping.XmlMappingSource.FromReader(XmlReader.Create(ConfigurationManager.AppSettings.Get("MyAppnLinqMappings")));
Next, feed the DataContext’s constructor :
TheDataClassesDataContext ctx = new TheDataClassesDataContext(TheConnectionString, TheLinqMappings)
Remember Me
Theme design by Jelle Druyts
Powered by: newtelligence dasBlog 2.1.8102.813
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way. Since I'm self employed, this mean that these do not represent my own opinions. Gee, go figure!No chipmunks were harmed while coding, deploying and configuring this blog...well, maybe a little bit but not that much. Hey, some of them even enjoyed it. Crazy chipmunks ;-)
Warning: While reading this blog, do not operate heavy machinery, never use a lit match or open flame to check fuel level, don�t try to dry yourself in a microwave oven, do not play in the dishwasher, do not use this blog in any function that involves insertion into a body cavity. Reading in conjunction with alcohol or sedatives may cause drowsiness. May irritate eyes. Use before the expiration date. If you do not understand, or cannot read all directions, cautions and warnings, do not read this blog. Warning - Contents may be hot and under pressure. May blow off causing eye or other serious injury. Point away from face and people, especially while reading. Suitable for vegetarians. This is not a dating agency. Not to be used for anything else. Reading this blog does not enable you to fly. CAUTION: Risk of electric shock - Dot not open. Do not use as an ice cream topping. Not for intimate hygiene.
© Copyright 2009, Guy Barrette
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
E-mail