Using Dynamic Data with multiple databases

  • Articolo

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:

image

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.

DynamicDataEFAndL2S.zip

Comments

  • Anonymous
    December 11, 2008
    The comment has been removed

  • Anonymous
    December 12, 2008
    I also blogged about this a while back. http://azamsharp.com/Posts/31_Using_Multiple_Databases_with_Dynamic_Data_Website_Project.aspx

  • Anonymous
    December 12, 2008
    this website helped me a lot in my career

  • Anonymous
    January 07, 2009
    Our software engineers did an excellent job in creating a set of projects to show the new Dynamic Data

  • Anonymous
    January 09, 2009
    Introduction What : Create your own project, in this blog is called DynamicDataProject, that mimics the

  • Anonymous
    January 12, 2009
    How would this work for a join table that spans multiple databases? I have what I guess you could call a subset database. I need to pull books that live in a different database and tie that to my current database where I add all kinds of units from each book. I have a join table in my units database ("bookunits") that has a book ID and unit ID. How do I get the "bookunits" insert/edit page to pull a list of books and populate a dropdown list? Hope I was clear enough. I can provide some more specifics if needed, but I was hoping to be generic enough so others may benefit from an explanation. Thanks for your time...

  • Anonymous
    January 12, 2009
    Tim, your question is a bit outside the scope of the article (which is about how to structure the app when using two DBs), so I would suggest post a new question to the Dynamic Data forum (http://forums.asp.net/1145.aspx). thanks, David

  • Anonymous
    February 17, 2009
    David Ebbo&#39;s blog : Using Dynamic Data with multiple databases

  • Anonymous
    February 18, 2009
    Please post corrections/new submissions to the Dynamic Data Forum . Put FAQ Submission/Correction in

  • Anonymous
    February 07, 2012
    can we create two different interfaces (e.g. two apsnet aspx pages ) for the same database in Dynamic Data web site ? if ok, I then can make one for reading and the other editing editing by setting the attributes of Gridviews in the different interfaces this may improve the Dynamic Data Model.

  • Anonymous
    February 07, 2012
    @Diaa: yes, you should be able to do that. Please use the forum (forums.asp.net/1145.aspx) if you run into specific issues.

  • Anonymous
    February 09, 2012
    plz David , How ?

  • Anonymous
    February 10, 2012
    @diaa: Did you see my previous comment? The forum is the best place to go for discussing details. Blog post comments don't work well for back and forth discussion, and are not seen widely in the community.

  • Anonymous
    April 07, 2014
    David, I've tried to set multiple contexts up on our DD site using a combination of this method and the one described here: csharpbits.notaclue.net/.../dynamic-data-registering-multiple.html It's mostly the method you describe here, but with skipping the menu to switch contexts on the default.aspx page, and instead just displaying both sets of tables. We were starting with an existing DD site that had a L2S context, and we're trying to add an EF context. Everything builds and the contexts seem to get registered - the Tables property has the correct tables loaded - but the VisibleTables.Count = 0 always. Even though the context has ScaffoldAllTables=true when it's registered. Any idea what could be happening? thanks, marcel

  • Anonymous
    April 07, 2014
    Marcel, it would be best to post on DD forum (forums.asp.net/1145.aspx), as few other people will see this here. Thanks!