Simple.Crud a Generic Solution for Small Data Entry Apps
An experimental prototype to make web CRUD apps quick and easy to build
Writing the same code day after day
As a developer there are things I find myself doing over and over again.
- Building database tables (SQL)
- Building data models (POCOs)
- Building data entry forms
- Writing the code to handle the data operations; Create, Read, Update, Delete (CRUD).
There are tools to make much of this easier such as ORM's. Data models can be generated dynamically from data entities and crud operations are easy with ORM objects.
However, I have grown to dislike the complexity, bulk and poor performance of complex ORM's, in particular Entity Framework. Such tools offer a lot to a big project with a complex data structure but as Stackoverflow have shown with Dapper you often need to bypass them to get the performance you need.
In recent times I have used Simple.Data and Dapper for small web data entry applications with great results. My hobby site Flicka20.com uses Simple.Data for all data operations. I have been doing the standard thing of creating data models for all entities I want to work with. These are usually either written by hand or generated by an ORM. This offers useful benefits such as compile time type checking and intellisense.
But, with the dynamic features of C# and those offered by Simple.Data I couldn't help but wonder if there was a way I could at least simplify part of the job and get rid of models and the repetitive CRUD code I always have to deal with.
In an ideal world we'd create a database structure for a given specification and then generate the interfaces from that structure. We would then tweak them, add in rules and validation and we'd have a working application. There have been tools along the way that do this kind of thing but these tend to be enterprise level and expensive or crude not flexible enough for my needs.
If you look at common .Net Web API applications you have numerous controllers that do almost exactly the same thing with different tables. For example:
public User GetUser(int id)
{
var UserRepository = new UserRepository();
var u = UserRepository.GetById(id);
return u;
}
You would then have a similar thing for products or whatever:
public User GetProduct(int id)
{
var ProductsRepository = new ProductsRepository();
var p = ProductsRepository.GetById(id);
return p;
}
I recently built an ASP.NET MVC5 application that is an admin suite for a collection of DNN databases. The purpose of this application was to allow the client to manage users and data across numerous DNN web sites from a single portal. Now there are a lot of tables in that application and a lot of CRUD operations to perform across them all. I ended up with countless controllers with actions all doing almost identical operations. The application worked well and is still in use but it has a lot of bloat.
So I got to wondering if I could write a prototype web app that would at least make it easier for me to build data entry forms without so much repeated code. Perhaps it's a bad idea? Perhaps using generic controllers and dynamic code would lead to problems? Well, the best way to find out is to try it.
Since I love NancyFX for it's elegance and simplicity I decided to use it for this experiment. I started by writing some simple routes to get data using Simple.Data and some of the dynamic features of C#.
Get a list from a table and load a view with the data sorted by a passed in column name:
Get["/readlist/{tablename}/{sortcolumn}/{view}"] = p =>
{
var db = Database.Open();
var uRows = db[p.tablename].All().OrderByDescending(db[p.tablename][p.sortcolumn]);
ViewBag.Method = "list";
return View[p.view, uRows];
};
So, if the user clicked a link to: /read/list/users/createdDate/userlist they would get a list of users ordered by the column: CreatedDate in descending order shown by the view: userlist
In this example we get a row from a table and load a view:
// get a row from a table and load a view
Get["/data/readrow/{table}/{Id}/{view}"] = p =>
{
ViewBag.FormTitle = "Edit Enquiry";
ViewBag.Method = "update";
ViewBag.TableName = p.table;
var db = Database.Open();
var uRow = db[p.table].FindById(p.Id);
return View[p.view,uRow];
};
In this case we could do: /data/readrow/users/105/user and we'd get the user with: userId=105 loaded into the view: user
As you can see these generic methods could be used for any common operations. Additional methods would be the exception and not the rule.
For the other operations such as create, update and delete I've written a post handler:
Post["/data/modify"] = p =>
{
// deserialise the json string from the form and convert to a dynamic object
var json = Request.Body.AsString();
var formRow = new JavaScriptSerializer().Deserialize<dynamic>(json);
try
{
// find the table name in the dynamic dictionary. There must always be one
if (formRow.ContainsKey(KeyNameTable))
{
string tableName = formRow[KeyNameTable];
string method = formRow[KeyNameMethod];
// Here we need to check if the current user has permission to do the given operation on the table
// Something like:-
// If(!UserHasPermission(tablename, method) return Response.AsText("Unexpected error: User does not have permission to do that!");
var db = Database.Open(); // open db with Simple.Data
// find the data method and modify the table
if (formRow.ContainsKey(KeyNameMethod))
{
switch (method)
{
case "insert":
// here we need to validate the data
// If(!DataValid(tablename, method) return Response.AsText("Unexpected error: Data failed validation!");
var newRow = db[tableName].Insert(formRow);
return Response.AsText(
"The data was inserted successfully into table: " + tableName);
case "update":
// here we need to validate the data
// If(!DataValid(tablename, method) return Response.AsText("Unexpected error: Data failed validation!");
// we could remove tablename and method from the update data but we don't have to. Simple.Data ignores any that don't match the
// table
// formRow.Remove(KeyNameTable);
// formRow.Remove(KeyNameMethod);
db[tableName].UpdateById(formRow);
return Response.AsText("The table: " + tableName + " was updated successfully!");
case "delete":
db[tableName].delete(formRow);
return Response.AsText("The row was successfully deleted from table: " + tableName);
}
}
}
else
{
return Response.AsText("Unexpected error: Table name was not found!");
}
}
catch (Exception ex)
{
return Response.AsText("Unexpected error: " + ex.Message);
}
return Response.AsText("Data operation succeeded!" );
};
This code needs some work but the basic idea is that I can create an HTML form in a view and load it passing in a table name and a method. For example:
Get["/enquiry"] = p =>
{
ViewBag.TableName = "contactlog"; // the table associated with the form
ViewBag.FormTitle = "Enter Enquiry";
ViewBag.Method = "insert"; // for the back end to process the post as an INSERT
return View["enquiry"];
};
This example handles the url: /enquiry It passes the table name we want to work with as: contactlog A title and the method: insert It loads the view: enquiry.chtml This view contains:
@inherits Nancy.ViewEngines.Razor.NancyRazorViewBase
@{
Layout = "Views/Shared/_Layout.cshtml";
}
<div class="row">
<h3>Simple.CRUD</h3>
<h4>@ViewBag.FormTitle</h4>
</div>
<div class="row">
<form class="crud-form">
<!-- hidden fields that hold variables for the server -->
<input name="Id" type="hidden" readonly value="@if(Model!=null){@Model.Id}" />
<input name="tablename" type="hidden" readonly value="@ViewBag.TableName" />
<input name="lastupdated" type="hidden" readonly value="@DateTime.Now" />
<input name="method" type="hidden" readonly value="@ViewBag.Method" />
<div class="form-group">
<label for="name">Full Name</label>
<input class="form-control" id="name" name="name" value="@if(Model!=null){@Model.Name}" type="text" />
</div>
<div class="form-group">
<label for="email">Email Address</label>
<input class="form-control" id="email" name="email" type="email" value="@if(Model!=null){@Model.Email}" />
</div>
<div class="form-group">
<label for="message">Enter a Message</label>
<textarea class="form-control" id="message" name="message" rows="4" cols="60">@if (Model != null){@Model.Message}</textarea>
</div>
<button class="btn btn-primary" type="submit">Save!</button>
@Html.AntiForgeryToken()
</form>
</div>
<div class="row">
<div class="results">
<h3>Form data as Json</h3>
<pre class="alert-info"><code class="results_display"></code></pre>
</div>
<div class="response">
<h3>Server POST response</h3>
<pre class="alert-info"><code class="response_display"></code></pre>
</div>
</div>
As you can see we are passing in a model which will be empty since this is an insert. We need to check for this in the view otherwise it will break. Hence this stuff: value="@if(Model!=null){@Model.Id} If we have a view like this then we can use the same view for the data edit. We just load it with our generic read a row method we built earlier:
Get["/data/readrow/{table}/{Id}/{view}"] = p =>
{
ViewBag.FormTitle = "Edit Enquiry";
ViewBag.Method = "update";
ViewBag.TableName = p.table;
var db = Database.Open();
var uRow = db[p.table].FindById(p.Id);
return View[p.view,uRow];
};
All we need is a link on our list view to load the item for edit:
@inherits Nancy.ViewEngines.Razor.NancyRazorViewBase
@{
ViewBag.Title = "Contact Form";
Layout = "Views/Shared/_Layout.cshtml";
}
<div class="row">
<h3>Simple.CRUD</h3>
<p>List of Enquiries</p><br/>
</div>
@if (Model != null)
{
foreach (var row in Model)
{
<div class="row">
<div class="col-md-3">
<!-- Call generic read row route passing in tablename => contactlog), row id and view name => enquiry-->
<a href="/data/readrow/contactlog/@row.Id/enquiry">@row.Name</a>
</div>
<div class="col-md-3">
@row.Email
</div>
<div class="col-md-2">
@row.Message
</div>
</div>
<hr/>
}
}
else
{
<div>No data!</div>
}
Note the anchor: <a href="/data/readrow/contactlog/@row.Id/enquiry">@row.Name</a> which populates and loads the edit form for a given list item.
This little prototype seems to work well for a simple scenario with a simple table. There are many issues raised by this experiment and it remains to be seen whether or not this idea is useful.
- Validation. We need to validate the data for a given table at the back end. Not difficult to achieve. Pseudo code is already in place in the above example.
- Security. I have implemented anti forgery in the example solution. But the application needs user accounts and a way of ensuring that a given user can perform a given operation on a table. Again, not too difficult to achieve in the server side code.
- Adding more forms and testing and debugging. More needs to be done here to investigate the pitfalls of this approach.
I have made an example project and put the code in Github for you to play with.
This project uses Bootstrap for basic styling because it's easy to set up and has a Typescript file formPost.ts which is compiled to ES5 on build. This code handles ajax form posts for add and edit with some text written out on the screen to show the data posted and the response from the server.