Auto-generate a Web Admin Interface using LINQ to SQL

Leave a reply

Like a lot of web programmers, I spent years using PHP to create web applications. One of the tools I frequently used in conjunction with PHP was phpMyAdmin . This helpful web-based tool allowed me to easily manage my MySQL databases via a web browser.

After reading Scott Guthrie’s “Binding UI using the ASP:LinqDataSource Control” article I became intrigued by the idea of building aphpMyAdmin-like inteface that could utlize any “LINQ to SQL Class”. In theory this seemed simple, in practice it involved a lot reflection .

I have a very early build of this running and have provided the code below. Comments are sprinkled throughout the code and explain what is happening. Here is how to create this:

Step 1: Create the ASPX page

Create the following ASPX file: ~/admin/LinqMyAdmin.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="LinqMyAdmin.aspx.cs" Inherits="LinqMyAdmin" %> 
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
 
<html xmlns="http://www.w3.org/1999/xhtml">  
<head runat="server">  
    <title>LinqMyAdmin</title> 
    <link href="./LinqMyAdmin.css" rel="Stylesheet" type="text/css" /> 
</head> 
<body> 
    <form id="form1" runat="server">  
    <div> 
        <div id="contentwrapper">  
        <div id="contentcolumn">  
            <asp:PlaceHolder ID="LinqPlaceHolder" EnableViewState="true" Visible="true" runat="server"></asp:PlaceHolder> 
            <h2 id="TableHeader" runat="server" /> 
            <asp:GridView   
                ID="TableGrid"   
                PageSize = "20" 
                AllowPaging = "true" 
                AllowSorting = "true" 
                EnableViewState = "true" 
                AutoGenerateColumns = "true" 
                AutoGenerateDeleteButton = "true" 
                AutoGenerateEditButton = "true" 
                runat="server">  
                <AlternatingRowStyle CssClass="odd" /> 
                <PagerStyle CssClass="pager" HorizontalAlign="Left" /> 
            </asp:GridView> 
        </div> 
        </div> 
        <div id="leftcolumn">  
            <h2>Tables</h2> 
            <asp:Repeater ID="TablesRepeater" EnableViewState="false" runat="server">  
                <HeaderTemplate> 
                    <ul> 
                </HeaderTemplate> 
                <ItemTemplate> 
                    <li><a href="linqmyadmin.aspx?table=<%# Container.DataItem %>"><%# Container.DataItem %></a></li> 
                </ItemTemplate> 
                <FooterTemplate> 
                    </ul> 
                </FooterTemplate> 
            </asp:Repeater> 
        </div> 
    </div>          
    </form> 
</body> 
</html>

Step 2: Create the Code-Behind

Create the following code-behind file: ~/admin/LinqMyAdmin.aspx.cs

using System;  
using System.Collections;  
using System.Data.Linq.Mapping;  
using System.Reflection;  
using System.Text.RegularExpressions;  
using System.Web;  
using System.Web.UI.WebControls;  
 
public partial class LinqMyAdmin : System.Web.UI.Page  
{  
    // --------------------------------------------------------------------  
    // CHANGE THE FOLLOWING LINE TO REFER TO YOUR LinqDataContext CLASS  
    // --------------------------------------------------------------------  
    string LinqSQLClass = "Intranet.Data.NorthwindDataContext, App_Code";  
    Type LinqContextType;  
 
    /// <summary>  
    /// Executed upon Page Load.  
    /// </summary>  
    protected void Page_Load(object sender, EventArgs e)  
    {  
        string TableName = Request.QueryString["table"];  
 
        // Display a list of available Tables  
        LinqContextType = System.Type.GetType(LinqSQLClass, true);  
        TablesRepeater.DataSource = GetAllTables(LinqContextType);  
        TablesRepeater.DataBind();  
 
        // If we have a "table" value in our querystring, then display the table.  
        if (TableName != null)  
        {  
            DisplayTable(TableName);  
        }  
    }  
 
    /// <summary>  
    /// Gets a list of all Tables found in a LinqContext  
    /// </summary>  
    /// <param name="_Type">The LinqContext Type</param>  
    /// <returns>list of table names.</returns>  
    private ArrayList GetAllTables(Type _Type)  
    {  
        ArrayList _Tables = new ArrayList();  
        PropertyInfo[] _AllProperties = _Type.GetProperties();  
        foreach (var _PropInfo in _AllProperties)  
        {  
            // LINQ Tables are Generics and will be declared as type System.Data.Linq.Table<TEntity>  
            // I worked a long time to create a generic Table Type comparison.  Nothing worked.  
            // So I ended up just converting the type to a string and searching for a pattern.  :(  
            if (Regex.IsMatch(_PropInfo.PropertyType.ToString(), @"^System.Data.Linq.Table") == true)  
            {  
                _Tables.Add(_PropInfo.Name);  
            }  
        }  
        return _Tables;  
    }  
 
    /// <summary>  
    ///   
    /// </summary>  
    /// <param name="TableName"></param>  
    private void DisplayTable(string TableName)  
    {  
        // We don't know the ContextTypeName or TableName in advance.  
        // Create a LinqDataSource control dynamically and add it to our  
        // PlaceHolder control.  
        LinqDataSource _linqSource = new LinqDataSource();  
        _linqSource.ID = "LinqSource";  
        _linqSource.ContextTypeName = LinqSQLClass;  
        _linqSource.TableName = TableName;  
        _linqSource.EnableInsert = true;  
        _linqSource.EnableDelete = true;  
        _linqSource.EnableUpdate = true;  
        _linqSource.AutoPage = true;  
        _linqSource.AutoSort = true;  
        _linqSource.EnableViewState = true;  
        LinqPlaceHolder.Controls.Add(_linqSource);  
 
        // Fetch the Primary Keys associated with the current table.  
        string[] _ID = GetTableKeys(TableName);  
          
        // Attach our LinqDataSource to our DataGrid.  
        TableHeader.InnerHtml = "Table -&gt; " + TableName;  
        TableGrid.DataSourceID = "LinqSource";  
        TableGrid.DataKeyNames = _ID;  
    }  
 
    /// <summary>  
    /// Get all properties associated with our LinqContext Table.  
    /// </summary>  
    /// <param name="TableName">The TableName</param>  
    /// <returns>The properties associated with this table</returns>  
    private PropertyInfo[] GetTableProperties(string TableName)  
    {  
        // All we currently have is a table name; "Customers" (plural).  
 
        // What we want to fetch are the properties associated with the  
        // model that is associated with our table.  That model, example,  
        // would be called "Customer" (singular).    
 
        // We find the connection between table & model by using  
        // reflection on the table.  The table will have have a type  
        // of System.Data.Linq.Table<Customer>.  The "Customer Type" is   
        // retrievable by looking at the Generic Arguments.  
          
        //  Clear as mud?  Great, let's get started...  
 
        Type _TableType = LinqContextType.GetProperty(TableName).PropertyType;  
        Type _ModelType;  
        PropertyInfo[] _ReturnProperties = null;  
 
        // Make sure we have a Generic Type  
        if (_TableType.IsGenericType == true)  
        {  
            Type[] _GenericArgs = _TableType.GetGenericArguments();  
 
            // Ensure that we have some Generic Arguments  
            if (_GenericArgs.Length > 0)  
            {  
                // Our model type will be the first Arg.  
                _ModelType = _GenericArgs[0];  
                _ReturnProperties = _ModelType.GetProperties();  
            }  
        }  
        return _ReturnProperties;  
    }  
 
    /// <summary>  
    /// Gets the primary keys associated with a LinqContext Table  
    /// </summary>  
    /// <param name="TableName">The Table Name</param>  
    /// <returns>A list of primary keys associated with the table</returns>  
    private string[] GetTableKeys(string TableName)  
    {  
        ArrayList _Keys = new ArrayList();  
        bool _PrimaryKey = false;  
 
        // Loop through each property found in the Table Model.  
        foreach (var _PropInfo in GetTableProperties(TableName))  
        {  
            // The Primary Key flag is a Custom Attribute.  Get all Custom Attributes  
            // associated with the current property.  
            Object[] _Objects = _PropInfo.GetCustomAttributes(true);  
 
            // Reset our Primary Key flag.  
            _PrimaryKey = false;  
 
            // Loop through each Custom Attribute.  
            foreach (Object _obj in _Objects)  
            {  
                // The attribute we're looking for is "System.Data.Linq.Mapping.ColumnAttribute"  
                if (_obj.GetType() == typeof(System.Data.Linq.Mapping.ColumnAttribute))  
                {  
                    ColumnAttribute _Attribute = (ColumnAttribute)_obj;  
                    if (_Attribute.IsPrimaryKey == true)  
                    {  
                        _PrimaryKey = true;  
                    }  
                }  
            }  
 
            if (_PrimaryKey == true)  
            {  
                _Keys.Add(_PropInfo.Name);  
            }  
        }  
 
        return _Keys.ToArray(typeof(string)) as string[];  
    }  
}

Step 3: Create the CSS

Create the following CSS file: ~/admin/LinqMyAdmin.css

body {  
    background-color: #FFFFFF;  
    font-size: 15px;  
}  
#contentwrapper {  
    float: left;  
    width: 100%;  
}  
#contentcolumn {  
    margin-left: 200px; /*Set left margin to LeftColumnWidth*/ 
}  
#leftcolumn {  
    float: left;  
    width: 200px; /*Width of left column*/ 
    margin-left: -100%;  
}  
#leftcolumn ul {  
    list-style: none;  
    margin-left: 5px;  
    padding-left: 0px;  
}  
#leftcolumn li   
{  
    padding-left: 0px;  
    margin-left: 0px;  
    margin-bottom: 5px;  
}  
h2 {  
    font-family: Arial;  
}  
a:link {  
    color:#d42945;  
    text-decoration:none;  
}     
a:visited {  
    color:#d42945;  
    text-decoration:none;  
}         
a:hover, a:focus {  
    color:#f03b58;  
    text-decoration: underline;  
}  
table {  
    border: solid 1px #e5eff8;  
}  
th {  
    font-weight: normal;  
    background:#f4f9fe;  
    text-align: center;  
    border: solid 1px #e5eff8;  
    padding:.3em 1em;  
}  
thead th {  
    background:#f4f9fe;  
    text-align: center;  
    color:#66a3d3 
}     
tr.odd td {  
    background: #f7fbff 
}  
td {  
    color: #678197;  
    border: solid 1px #e5eff8;  
    padding:.3em 1em;  
    text-align: center;  
}  
.pager td {  
    text-align: left;  
}

Access the page via the following URL:

http://localhost/admin/LinqMyAdmin.aspx

I would not recommend putting this code on a public server or using production data. If you do happen to upload this code to a public server, be sure to secure access to the page.

Obviously this web application lacks a lot of functionality. What is amazing though is how much functionality I am getting “for free” because of ASP.NET’s LinqDataSource and GridView. However, I find that I’m reaching the limit of what I can do dynamically. For example, weaving automatic foreign-key mappings into this code is proving difficult. I have some thoughts on how to overcome this, but have not yet found the time to experiment.

I hope to provide more updates later. Feedback is welcome!