Utilizing the Scaffolder magic

First off, I must apologize for the long wait. 5 months is far too long between posts,

however due to a lot of new interesting things happening in my life lately, this could not be avoided.

In the previous part of this tutorial we went through how to iterate through

a object model using Reflection and displayed a input field for each simple property available in that model.

In this tutorial we will continue where we ended last time, so for you who has not read the first part of this tutorial.

I would suggest you do that now! ... Don't worry, this part will still be here by the time you are done with Part 1.



Today we will cover

Loading data from the database

Saving data back to the database

Editing complex properties

Loading some data

As one of the most important things with this user control is that we want to edit existing data.

Without this functionality then the scaffolder thing-magic wont be as... magical.

So where do we start? Loading the data of course!

So jump straight into your ScaffolderControl.ascx and directly below

the declaration of your variable called disabledString we will have to add an additional variable.

var propertyValue = property.GetValue(this.Item, null);

Once again we are using the beauty of Reflection, the function called GetValue will return the value of that property,

simple as that. The input parameter that I've used is the Object Item we have declared in the code-behind.

If you remember from the first part of this tutorial.

With the help of this new variable we will be able to grab the value of that property and assign it on our input field.

It should look something like this:

<!-- Scaffolder stuff goes here --> <% if (Item != null) { foreach (var property in Properties) { if (!Scaffolder.IsComplexProperty(property)) { var canEdit = Scaffolder.IsEditableProperty(property); var disabledString = canEdit ? "" : "readonly"; var propertyValue = property.GetValue(this.Item, null); %> <div class="form-group"> <label for="prop_<%= property.Name %>" class="control-label col-xs-12"><%= Scaffolder.GetDisplayName(property.Name) %></label> <div class="col-xs-12"> <input type="text" class="form-control" <%= disabledString %> placeholder="Edit <%= Scaffolder.GetDisplayName(property.Name) %>" value="<%=propertyValue %>" /> </div> </div> <% } } } %>

We have simply just added the value="<%=propertyValue %>" to our input field.

Now! It is time for you to hit F5 and run your project! :-)

If you are like me and have not yet added any rows to the database,

well then all you will see is... This

And if you ask me, this isnt super interesting just yet.

We need some data! Or that monkey will never get excited again.

Adding some data to the database!



Don't worry, we will come back to loading the data some more in a moment ;-)

If you already know how to simply add some data to your database,

then you can skip this part and jump straight to Loading some more data down below.

If you are unsure of how to do it or just want to continue reading.

Then sure! Go ahead! I can't stop you, even if I wanted to.

Depending on your tool of choice, be it SQL-Server Management Studio or

ust using Visual Studio's own SQL-Server browser,

get them ready as it is time! For the sake of simplicity I will just use the Visual Studio's Server Explorer.

If you remember the structure I used in Part 1, check it out!

Because the first thing I want to add is a new Planet.

Just open up your Data Connections inside your Server Explorer.

Unfold the database, ScaffolderEntities -> Tables -> Planet. Right Click on the Planet

and select Show Table Data.



This will enable you to edit the content of this table.

Now for the sake of it, I've added two Planets.

NULL Earth 0 7000000000 NULL Krypton 1234125132 0

You can just copy paste them and then Right-click -> Execute SQL,

by doing that the new rows will be added to the database and saved properly.

This will also generate ID's for our planets.

Select one of the planets ID and copy it to your clipboard CTRL+C is your friend ;-)

We are not done just yet, we still need to add a super hero! So open up the Hero table the same way we opened up the Planet table.

Now, paste CTRL+V the planet ID into the HomePlanetId column And add the following (or of your own choice)

NULL Zerratar 25 <PLANET_ID>

Remember, we only need one Hero for now.

But you are free to add as many as you would like.

This should be enough for just viewing the data. Moving on...

Loading some more data



Hozaaam! If you have not already, please just hit F5 and see if it works or not.

For me the result is as following

Awesome! So we have some data properly loaded and are.. wait.. We are able to edit the Home Planet ID!

This is not what we want..

Seriously? Would you remember the GUID of a planet?

Of course not! Unless you are Rain Man or something.

Although... That is probably how I would look if I had to remember it.

Lets continue, our next step is to actually do something with these Complex Types.

When we have a ID that points to a different object or Table, that means we have a reference to another object. In this case, a Complex object.

I would say that a Complex object or Type is a object that contains more than one Property.

For instance, our Hero table have more than one Property, and could therefore be seen as a Complex Property.

Litteraly a Complex Type is a Container of more types or properties. Ahh. I should have started with that.

Now please don't go all Rain Man on me...

Back to the coding. First of all, we will need to add another variable. This one we call isIdentity

var isIdentity = Scaffolder.IsIdentityProperty(property);

We first want to know if the current property is a ID (Identity Property) or not.

Because if it is, then all we want to do is to create a list of available object.

We can then use that dropdown/select field to pick between available options.

Now time for some radical changes to our ScaffolderControl.ascx,

so open it up if you have not already.

We want to replace our previous Property Loop with the following code

<!-- Scaffolder stuff goes here --> <% if (Item != null) { foreach (var property in Properties) { var isIdentity = Scaffolder.IsIdentityProperty(property); if (!Scaffolder.IsComplexProperty(property)) { var canEdit = Scaffolder.IsEditableProperty(property); var disabledString = canEdit ? "" : "readonly"; var propertyValue = property.GetValue(this.Item, null); if (isIdentity) { %> <div class="input-group"> <label for="prop_<%= property.Name %>" class="control-label col-xs-12"><%= Scaffolder.GetDisplayName(property.Name) %></label> <select name="prop_<%= property.Name %>"> <option value="null">NULL</option> <% /* Oh dear! We need to iterate through each possible options here! */ %> </select> </div> <% } else { %> <div class="form-group"> <label for="prop_<%= property.Name %>" class="control-label col-xs-12"><%= Scaffolder.GetDisplayName(property.Name) %></label> <div class="col-xs-12"> <input name="prop_<%= property.Name %>" type="text" class="form-control" <%= disabledString %> placeholder="Edit <%= Scaffolder.GetDisplayName(property.Name) %>" value="<%= propertyValue %>" /> </div> </div> <%} } } } %>

As you can see, I've added the new variable isIdentity aswell for a conditional check,

this to see if the property we are going through is an identity property or not.

I've also added a select element, this element will be our input for Complex Object References.

Added a missing name attribute to our previous input field.

So without any further due, please try it out. Time to hit F5 baby!

This is my result and...

... ... Hot diggity damn! Our ID just turned into a select?! And what about our Home Planet?!

Well, what we did was correct, but there is one issue here.

The function Scaffolder.IsIdentityProperty works as long as the name of the actual Table is the same as the name of the property.

But our property is called HomePlanetId and not PlanetId.

Lets take a look at our IsIdentityProperty function.

So jump straight into Scaffolder.cs You will find the following code

public static bool IsIdentityProperty(System.Reflection.PropertyInfo p) { /* This function will try to determine if the target property is a Identity (Id) or not */ var obj = p.DeclaringType; if ((p.Name.ToLower().EndsWith("id")) && obj != null) { var n = p.Name.ToLower(); if (n == "id") return true; n = n.Remove(n.Length - 2); var propMatch = obj.GetProperties().FirstOrDefault(i => i.Name.ToLower() == n); if (propMatch != null) { // We only want to manage linked objects. // Its super hard otherwise to find the corresponding object with the name of the ID alone. if (p.PropertyType == typeof(Guid) || p.PropertyType == typeof(Guid?)) { return true; } if ((p.PropertyType == typeof(int) || p.PropertyType == typeof(int?))) { return true; } } } return false; }

Is it hard to understand?

What happens is that if a property ends with the string "Id" it will be considered an ID or Reference to an object.



The problem is this line

var propMatch = obj.GetProperties().FirstOrDefault(i => i.Name.ToLower() == n);

What we could do is to change the name of the table we have in our database from Planet to HomePlanet,

change our column HomePlanetId into PlanetId or change this line.

The biggest issue would be that If we change this line and we have more than one Table with similar names.

Lets say we do have a table called HomePlanet and a table called EnemyPlanet It will be very hard to distinguish between them both.

So I would recommend changing the name of the HomePlanetId column into PlanetId and then refresh the database model.

However, for picking the simple road I will just update this line for now. And change it into

var propMatch = obj.GetProperties().FirstOrDefault(i => n.ToLower().Contains(i.Name.ToLower()));

This will resolve our issue for now, so hit F5 whenever you are ready!

You will now see that both ID and Home Planet ID have become select/dropboxes.

So we are getting very close to what we want. But we still don't want the ID of our current Object to become a select element.

So jump back into ScaffolderControl.ascx. We are going to add a tiny bit of code so we can prevent this from happening.

Inside the if statement

if (isIdentity)

We want to add another conditional check, canEdit.

if(isIdentity && canEdit)

Voila! Hit F5 once again!

Here is my results

Sweet! It is getting there!

Loading Data Dynamically from the database



Now with our select element ready, we need to add some actual options to pick between.

To do this, we will need to load every row from that table we are referencing towards. Wow, how do we do that?

Well, it is easier than you could expect. Lets jump into our ScaffolderControl.ascx.cs

First of all, we want to add a new class to the bottom, just inside the namespace ofcourse.

public class TableRowValue { public Guid Id { get; set; } public string Name { get; set; } }

Note: It is not a requirement to add this class in the same file, it can be added to as a seperate file if wanted.

However since we are only going to use it from ScaffolderControl.ascx.cs, we can just as well put it in here.

Next thing is to add a new property. At the top of the ScaffolderControl class, add

private static List<string> TableNames = null;

It should look something like

public partial class ScaffolderControl : System.Web.UI.UserControl { public PropertyInfo[] Properties { get; set; } private static List<string> TableNames = null; // .......... }

We will use this TableNames property to keep track on all our table names so that we only need to grab them once.

Because the next thing you want to do is to add the following function (inside the ScaffolderController class)

public IEnumerable<TableRowValue> GetTableRows(PropertyInfo property) { var propertyName = property.Name; using (var db = new ScaffolderEntities()) { if (TableNames == null) { TableNames = new List<string>(); var databaseName = db.Database.Connection.Database; // Grab all the table names from our database var result = db.Database.SqlQuery(typeof(string), "SELECT TABLE_NAME " + "FROM INFORMATION_SCHEMA.TABLES " + "WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='" + databaseName + "'"); TableNames = result.Cast<string>().ToList(); // property.Name } if (propertyName.EndsWith("Id")) propertyName = propertyName.Remove(propertyName.Length - 2); var targetTable = TableNames.FirstOrDefault(t => propertyName.ToLower().Contains(t.ToLower())); if (!string.IsNullOrEmpty(targetTable)) { // With the magic of reflection we can find the corresponding type // using the following code // var targetType = Assembly.GetExecutingAssembly().GetTypes().FirstOrDefault(t=>t.Name == targetTable); // However, for the sake of everyones sanity, we will take a much more simpler approach // and ask directly for the columns we need. // Remember, in our database, and in each table we have one Column called: Id // and one column called: Name // So lets use it for our advantage. However, this might not be the case for the future... return db.Database.SqlQuery<TableRowValue>("SELECT * FROM " + targetTable).ToList(); } } return null; }

This function will try and grab all the rows from the target referenced table and return a Id and a Name.

We will then use these values to fill up our Select element in ScaffolderControl.ascx. So jump straight over!

Now update the Select element to look something like this

<select name="prop_<%= property.Name %>"> <option value="null">NULL</option> <% var rows = GetTableRows(property); foreach (var row in rows) { var isSelected = propertyValue != null && row.Id == new Guid(propertyValue.ToString()); %> <option value="<%= row.Id %>" <%= isSelected ? "selected" : "" %>><%=row.Name %></option> <% } %> </select>

You should now hit F5 and feel awesome!

At this point, the select element looks a bit out of place, so lets prettify it a bit.

So just copy-paste the following code and replace it with what you've got.

if (isIdentity && canEdit) { %> <div class="input-group"> <label for="prop_<%= property.Name %>" class="control-label col-xs-12"><%= Scaffolder.GetDisplayName(property.Name) %></label> <div class="col-xs-12"> <select name = "prop_<%= property.Name %>" < select name="prop_<%= property.Name %>" class="form-control"> <option value = "null" > NULL </ option > <% var rows = GetTableRows(property); foreach (var row in rows) { var isSelected = propertyValue != null && row.Id == new Guid(propertyValue.ToString()); %> <option value = "<%= row.Id %>" <%= isSelected ? "selected" : "" %>><%= row.Name %></ option > <% } %> </select> </div> </div> <% }

I've added a new div to contain / hold the select element,

I've also added a bootstrap class on the select element to make it more beautiful.

This is how it should look like now:

Sweet huh? Man, you're doing some great progress here! So.. Here is a cookie for you!

Maestro! What about saving?



It couldn't come any sooner! Saving our changes back to our database is one of the big features of this Control..

I mean, really, of what use would this be otherwise?

Well.... Lets not follow in Brians footsteps.

First thing we want to do is to add a Save button. Shouldn't be too hard right?

Open up ScaffolderControl.ascx and aaaalmost at the end of the file.

Add a asp:Button. It should look something like the following code:

<asp:Button ID="btnSave" class="btn btn-primary" Text="Save" runat="server"/>

Now toggle over to Design view in Visual Studio and double click on your Save button.

This will tell visual studio to generate a btnSave_Click event for this button.

Jump over to ScaffolderControl.ascx.cs if you are not already there. And go to the newly generated method.

and replace the content of the method with the following.

protected void btnSave_Click(object sender, EventArgs e) { var targetTableType = this.Item.GetType().BaseType; var targetTable = targetTableType.Name; using (var db = new ScaffolderEntities()) { /* Load all property values we have set from the web form all our properties should start with the name prop_ */ var allProperties = this.Request.Form.AllKeys.Where(k => k.StartsWith("prop_")).OrderBy(o => o); var targetType = Assembly.GetExecutingAssembly().GetTypes().FirstOrDefault(t => t.Name == targetTable); var entity = Activator.CreateInstance(targetType); var entityProperties = entity.GetType().GetProperties(); /* Quick and ugly way to find the id property This also requires the primary key to be called "id" We can use ObjectContext here to find the primary key(s) instead. */ var id = entityProperties.FirstOrDefault(prop => prop.Name.ToLower() == "id"); if (id != null) { /* Check if we are supplying a valid ID and if it is, then we want to load this entity instead of using our previously instanced one. */ var idValue = this.Request.Form[this.Request.Form.AllKeys.FirstOrDefault(k => k.ToLower() == "prop_id")]; if (!Scaffolder.IsUndefinedIdentity(idValue)) { entity = db.Set(targetType).SqlQuery("SELECT * FROM " + targetTable + " WHERE id='" + idValue + "'").Cast<object>().FirstOrDefault(); } } foreach (var p in allProperties) { var pName = p.Substring("prop_".Length); var value = this.Request.Form[p]; /* Find any matching property and set its value to the one we have inputted in the web forms. */ var targetProperty = entityProperties.FirstOrDefault(prop => prop.Name == pName); if (targetProperty != null) { targetProperty.SetValue(entity, value.ParseAs(targetProperty.PropertyType), null); } /* Check if this property is an ID or not. If it is an ID and the value is empty / undefined. This is going to be a new entity, so we want to add it to the database. */ if (Scaffolder.IsIdentityProperty(targetProperty) && !Scaffolder.IsEditableProperty(targetProperty) && Scaffolder.IsUndefinedIdentity(value)) { db.Set(targetType).Add(entity); } } /* Finally save your changes and update our Item object! */ db.SaveChanges(); Item = entity; } }

Wow! That was a lot of code to go through. So I will just give you a quick description.

The save method will grab the name of the target Table/Entity that we want to update.

It will then try to find the ID property of this entity to see if it already exists or not.

If the entity exists, we want to update that one if not, we want to create a new one.

However, right now the code wont compile as it is, we still need to add some helper functions.

Start by creating a new file (a class), and place it in your Classes folder.

I named this one ObjectExtensions.cs and replace the content of that file with the following code:

public static class ObjectExtensions { public static object ParseAs(this string val, Type type) { try { if (String.IsNullOrEmpty(val)) return (object)null; if (type == typeof(DateTime) || type == typeof(DateTime?)) return (object)DateTime.Parse(val); if (type == typeof(TimeSpan) || type == typeof(TimeSpan?)) return (object)Guid.Parse(val); if (type == typeof(Guid) || type == typeof(Guid?)) return (object)Guid.Parse(val); if (type == typeof(Decimal) || type == typeof(Decimal?)) return (object)Decimal.Parse(val); if (type == typeof(int) || type == typeof(int?)) return (object)Int32.Parse(val); if (type == typeof(float) || type == typeof(float?)) return (object)Single.Parse(val); if (type == typeof(double) || type == typeof(double?)) return (object)Double.Parse(val); if (type == typeof(long) || type == typeof(long?)) return (object)Int64.Parse(val); if (type == typeof(short) || type == typeof(short?)) return (object)Int16.Parse(val); if (type == typeof(byte) || type == typeof(byte?)) return (object)Byte.Parse(val); if (type == typeof(bool) || type == typeof(bool?)) return (object)Boolean.Parse(val); if (type == typeof(string)) return (object)val; return (object)val; } catch { return (object)null; } } }

This is a simple value converter. It will take in a string and a Type property and return a parsed value.

Now, jump into Scaffolder.cs and add the following code

public static bool IsUndefinedIdentity(string value) { /* This function will check if the value is null, empty guid, -1 or 0. This to determine if a ID has been properly set or not. */ return value == null || value == Guid.Empty.ToString() || value == "0" || value == "-1"; }

And that is it! Wow.. Now it is time for you to stretch your legs and be proud of yourself!

Let's hope I didnt miss anything! If so, you can download the full project below.

Conclusion



In this tutorial, we have created a simple Scaffolding Control for ASP.NET Using Reflection and Entity Framework.

Here is a list of what you can improve or add

Add a new Button for setting the ID of the Item to Guid.Empty,

so that you can create new objects instead of updating an existing one.

so that you can create new objects instead of updating an existing one. List all Heros in a table, inside a new webform and add a Edit/Delete button on each row.

Add support for editing a list of complex objects.

In this tutorial I had an additional Table called SuperPower,

this project could be extended to Add/Edit/Delete these.

In this tutorial I had an additional Table called SuperPower, this project could be extended to Add/Edit/Delete these. Make a more complex/generic solution for loading Object References.

To avoid hardcoding column names. This can be done with additional Reflection or using SMO to read primary keys and map it that way.

Another hint for grabbing the primary key of a table, you can use a SQL Query to do the trick for you.

SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = 'TableName'

You can download the complete Tutorial files here Tutorials.TheScaffolder_Complete.zip

This .zip file contains a script.sql of the database. You are free to use it however you like.

Final words



There are a lot of different ways of creating a Scaffolder Component. For instance,

MVC.NET already have templates for generating Scaffolding controls out of the box.

You can read more about it here http://www.asp.net/visual-studio/overview/2013/aspnet-scaffolding-overview

And instead of reflection you can use SQL Server Management Objects (SMO)

which you can read more about here https://msdn.microsoft.com/en-us/library/ms162202.aspx

You can also just hop on to google.com and give a good ol' search on how to use SMO.

And finally..

Thats it for this time! Thank you for reading, and I hope to see you here again soon!

Have fun and stay awesome!!