Using Dynamic Data with multiple databases

Most Dynamic Data web sites typically only use a single database, with either a Linq To Sql or Entity Framework model over it.  But in some cases, you need your site to use multiple databases/models.  This came up today in this forum thread.  In fact, the original poster (Chris) is the one that came up with a good solution, and that's what my sample app in this post is doing (so credits to him!).

The full sample is attached at the end, so feel free to get it now if you prefer!

Note that I'll cheat a little bit, by doing the following:

• I'll only use one Northwind database (bear with me here!)
• I'll create both a Linq To Sql and an Entity Framework model over it

The main reason for doing this is to avoid having to include two MDF files in the sample.  There is actually another reason: by using the same DB, we know we're going to get some name conflicts between the two models, which forces us to make sure that we're able to handle that case!

Also, the fact that the two models use different ORMs brings up an interesting problem, because normally in Dynamic Data you use a different project template for L2S vs EF.  Here, we're going to want some kind of hybrid.  Specifically, what's different between the two are the Page Templates, since they use different DataSource controls (LinqDataSource vs. EntityDataSource).  However, other things like the Field Templates can and should be shared (always avoid duplication when possible!).  So we're aiming for a folder structure that looks like this:

So basically, we keep the DynamicData folder for the shared stuff, and use EF and L2S folders for the different stuff.  What they contain is completely unmodified from what you'd find in the default project templates.

The next step it to deal with the routes.  Our goal is to use different routes for each model so they don't conflict.  e.g. the URL might look like "L2S_NW/Products/List.aspx" for L2S, and "EF_NW/Products/List.aspx" for the EF model. 

Let's take a look at global.asax, which deals with the two things we just discussed: the folder structure and the route set up.  Read through the comments as they cover some important points:

// Keep track of the mapping from db name to MetaModel.  This is used in default.aspx  public static Dictionary<string, MetaModel> Models = new Dictionary<string, MetaModel>();    public static void RegisterRoutes(RouteCollection routes) {      // Register both models along with their routes      RegisterContext(routes, "L2S_NW", typeof(NorthwindLinqToSqlDataContext), "~/DynamicData_L2S");      RegisterContext(routes, "EF_NW", typeof(NorthwindModel.NorthwindEntities), "~/DynamicData_EF");  }    private static void RegisterContext(RouteCollection routes, string dbName, Type contextType, string ddFolder) {      // Set the Dynamic Data folder to the custom one that was passed in.  However, keep the      // field templates in the central location since they're the same for EF and L2S      var model = new MetaModel() {          DynamicDataFolderVirtualPath = ddFolder,          FieldTemplateFactory = new FieldTemplateFactory() {              TemplateFolderVirtualPath = "~/DynamicData/FieldTemplates"          }      };        model.RegisterContext(contextType, new ContextConfiguration() { ScaffoldAllTables = true });        // Register the route, using the db name as the prefix. Also, we add the db name      // as a constraint to make sure that we always build the correct URL for a given db      routes.Add(new DynamicDataRoute("{dbname}/{table}/{action}.aspx") {          Constraints = new RouteValueDictionary(new {              action = "List|Details|Edit|Insert",              dbname = dbName }),          Model = model      });        Models[dbName] = model;  }

Next, let's look at what needs to happen in default.aspx.  Normally, it only lists the tables from one model.  Our goal is to allow the model to be selected via a dbname query string parameter.  e.g. "Default.aspx?dbname=L2S_NW" or "Default.aspx?dbname=EF_NW".

First, in the markup, we'll add a GridView that will act as a model selector, above the existing GridView that lists the tables:

    <asp:GridView ID="ModelMenu" runat="server" AutoGenerateColumns="false"          CssClass="gridview" AlternatingRowStyle-CssClass="even">          <Columns>              <asp:TemplateField HeaderText="Model Name" SortExpression="TableName">                  <ItemTemplate>                      <asp:HyperLink ID="HyperLink1" runat="server"                          NavigateUrl='<%#"?dbname=" + Eval("Key") %>'>                              <%# Eval("Key") %></asp:HyperLink>                  </ItemTemplate>              </asp:TemplateField>          </Columns>      </asp:GridView>  

The interesting part here is the Hyperlink databinding, and how it uses the dictionary key.  Now let's see what the code behind has:

protected void Page_Load(object sender, EventArgs e) {      Dictionary<string, MetaModel> models = ASP.global_asax.Models;        // Bind the first grid to the list of models      ModelMenu.DataSource = models;      ModelMenu.DataBind();        // If we got a model name from the query string, bind the second grid to its tables      string dbName = Request.QueryString["dbname"];      if (!String.IsNullOrEmpty(dbName)) {          Menu1.DataSource = models[dbName].VisibleTables;          Menu1.DataBind();      }  }

Now try running the complete app (attached below).  When you first get to default.aspx, you'll get the list of models.  Once you select one, you'll also see its tables because the dbname parameter is added to the query string.

Then try actually clicking on some of the tables for one of the models, and observe that the URL is only targeting that model (i.e. it starts with EF_NW or L2S_NW).

Hopefully, this gives you an idea of what it takes to use multiple models.

Source Click Here.