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:


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 (15)

  1. shiva says:

    this website helped me a lot in my career

  2. Our software engineers did an excellent job in creating a set of projects to show the new Dynamic Data

  3. Introduction What : Create your own project, in this blog is called DynamicDataProject, that mimics the

  4. Tim Hobbs says:

    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…

  5. davidebb says:

    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

  6. ASP.NET says:

    David Ebbo&#39;s blog : Using Dynamic Data with multiple databases

  7. Please post corrections/new submissions to the Dynamic Data Forum . Put FAQ Submission/Correction in

  8. Diaa Fayed says:

    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.

  9. davidebb says:

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

  10. diaa fayed says:

    plz David , How ?

  11. davidebb says:

    @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.

  12. Marcel G says:

    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

  13. davidebb says:

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