HowTo: SQL Database web access with ASP.NET Dynamic Data and Entity Framework (hosted on IIS 6.0)

30Jan Code Inside Team HowTo 6 comments

I started my programming career with PHP and MySQL and I used very often a webbased mysql admin panel called "PhpMyAdmin".
It has (of course) not all features of the SQL Management Studios, but if I only want to have a quick look at the database it is very cool – and you only need a simple browser.
You can create such an tool with ASP.NET Dynamic Data and Linq2Sql or the Entity Framework within minutes.

Intro ASP.NET Dynamic Data
This feature was a part of the .NET 3.5 SP1. If you create a new projects you´ll see two different project templates:

Both templates are nearly the same, but there is a difference:

Dynamic Data Entities Web Applications: Use it only with ADO.NET Entity Framework
Dynamic Data Web Application: Use it only with Linq2Sql

If you try to use the EF in the wrong template you´ll get a runtime error: "The method ‘Skip’ is only supported for sorted input in LINQ to Entities" (Source and more info)

Both templates take a datacontext (Linq2Sql or EF) and create at runtime CRUD and list pages. It use a templating system and you could change everything if you want it.

Step 1: Create ASP.NET Dynamic Data Entites Web Projekt
We choose the entity web project and should see the following project structure:

The "DynamicData" folder contains all the templates, pictures etc.:

"PageTemplates" holds all CRUD and the List templates. The "FieldTemplates" render the different types (e.g. a DateTime).

I´ll keep the default behaviour, templates and styles for this HowTo.

Step 2: Create the Entity Model
Now we need a entity model – that´s why we now need a database. I choose the Northwind sample database.
Now we create the model:

In the next screen you have to establish the connection to your database. Use ".\SQLExpress" if you have a local running SQL Express Server and add everything you want:

Step 3: Setup Dynamic data with the datacontext
This is the important step: Just add the datacontext to the DynamicData "MetaModel", read the comments and paste your datacontext into the metamodel:

MetaModel model = new MetaModel();

            //                    IMPORTANT: DATA MODEL REGISTRATION
            // Uncomment this line to register LINQ to SQL classes or an ADO.NET Entity Data
            // model for ASP.NET Dynamic Data. Set ScaffoldAllTables = true only if you are sure
            // that you want all tables in the data model to support a scaffold (i.e. templates)
            // view. To control scaffolding for individual tables, create a partial class for
            // the table and apply the [Scaffold(true)] attribute to the partial class.
            // Note: Make sure that you change "YourDataContextType" to the name of the data context
            // class in your application.

            //--> Code-Inside Change HERE!
            model.RegisterContext(typeof(NorthwindEntities), new ContextConfiguration() { ScaffoldAllTables = true });

NorthwindEntities is my datacontext and I set the "ScaffoldAllTables" to true. But be carefull with this setup – it allows anybody to create, read, update and delate everything on your model/database. Limit the access via configuration or (the easy way) the IIS settings.

Result

The result (after few minutes) :

Overview

List

Details

Edit

If you just need a plain admin panel for your application (and use Linq2Sql or EF) this is a very nice.

Dynamic Data on IIS 6

Dynamic Data use the Routing API like ASP.NET MVC, thats why you have such URLs:

http://localhost:52016/Alphabetical_list_of_products/Edit.aspx?CategoryName=Beverages&Discontinued=False&ProductID=1&ProductName=Chai

If you have only an IIS 6.0, you should read this great blogpost.

[ Download Democode (without DB) ]

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

About the author

Written by Code Inside Team

Currently there is no additional info about this author.

6 Responses

q
January 30, 2009

Better to use linq to sql, instead of entity framework, for this purpose, since it provides one-to-one mapping from table to class.
Reply
Gerardo Gala
June 27, 2010

Hi All!

Has someone tried to modify the mappingSource of the DataContext at run-time before anything is excecuted. I want to able to change the datasource at run-time to point at different databases depending on who the User is.

Right now it is using this code:

base(global::System.Configuration.ConfigurationManager.ConnectionStrings["FAMP_ConnectionString"].ConnectionString, mappingSource)

Thanks!
Reply
Sam
July 11, 2010

Hi, thanks for the example.

I have a problem, I was able to view data but its showing error while updating, inserting , deleting

Can please advise what might be the problem

Thanks,
Sam
Reply
- Hitesh
  June 24, 2012
  
  hi,u need to go through from global.asax file and make some change which are mansion above Article.
  Reply
Sam
July 11, 2010

I’m able to view the data from the table but I’m getting folling Error: while Inserting, updating, deleting the records

Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerServerErrorException: Unable to update the EntitySet ‘Jobs’ because it has a DefiningQuery and no element exists in the element to support the current operation.
Reply
Ziaur Rahman
June 18, 2011

Informative. But I suggest to provide code beside menu ….
Reply