Top reasons .Net is amazing for prototyping

The other day someone told me .net was slow to get something built, and to be fair to the person I can see why he would have thought that. Most of his interaction with .net projects have been on complex with large enterprise applications that often have integrated multiple other applications.

However, I would maintain that .net is a framework that is actually really fast to develop on and that what he had perceived as being slow was the complexities of a project rather than the actual coding time.

In fact, I would say it’s so fast to get something built in it, it actually becomes the fastest thing to develop a prototype in. Here are my top reasons why.

ASP.NET Core

ASP.NET Core inherits all the best bits from the ASP.NET Framework that came before it, giving the framework almost 20 years of refinement since its original release in 2002. The days of WebForms in the original ASP.NET are now long behind us and we now have the choice of building web applications with either MVC or Razor Pages.

Razor provides the perfect combination of a view language providing helpers to render your html without limiting what can be done on the front end. How you code your HTML is still completely up to you, the helpers just provide features like binding that make it even faster to do.

Another great thing about .Net core over that which came before it, is its platform independent. Rather than being confined to just Windows, you can run it on Mac or Linux too.

Great starter templates

What kicks of a great prototype project is starting with great templates, and ASP.NET Core has a bunch.

As already mentioned, you can build a Web Application with either Razor Pages or MVC, but the templates also provide you with the base for building an API, Angular App, React.js or React.js and Redux or you can simply create an Empty application.

My preference is to go for MVC as it’s what I’m the most familiar with and a key thing for rapidly building a prototype is that you develop rapidly. The idea is to focus on creating something new and unique, not learn how to develop in a new framework.

The MVC Web Application gives you a base site to work with a few pages already set up, bootstrap and jQuery are already included so you start right at the point of working on your logic rather than spending time doing setup.

SQL Server and EF Core

I’ve always been a bit of a database guy. I’m not sure why, but its a topic that has always just made sense to me, and despite being a topic that can get quite complex, the reasons behind it being complex always feel logical.

When it comes to building a prototype though there are two aspects which make storage with .net core super simple.

Firstly, Entity Framework Core (EF Core) means you don’t really need to know any SQL or spend any time writing it. It helps if you do, but at a minimum all you need to be doing is creating a model in your code, adding a few lines for a DB context that tells EF.Core that a model is a table and how they relate. Then turn on migrations and you’re done. When you run the application, the DB gets created for you and each time you change your model, you just add another migration and the next time the application runs the application the DB schema gets updated.

Querying your DB is done by writing LINQ queries against your entity framework model, allowing you to have next to no understanding of SQL and how the DB works. Everything is just done by magic for you.

The second part is SQL Server and its different versions. Often when you think of SQL Server you think of the big DB engine with its many many components that you install on a server or your local machine but there’s two others which are even more important. LocalDB and Azure SQL.

LocalDB is an option that can be installed as part of Visual Studio. No separate application is needed or services to be running in the background. It is essentially the minimum required to start the DB engine to be used for development purposes. In practical terms this means when you start your application EF.Core can run off a LocalDB which didn’t require any setup, but as far as your application in concerned it is no different than working with any other version of SQL Server.

Azure SQL as the name implies is SQL Server on Azure. The only thing I really need to say about this is that you can swap LocalDB and Azure SQL with ease. They may be different but as far as your prototype is concerned, they are the same.

Scaffolding

The only thing quicker than writing code is having someone else do it for you. So we’ve created our application from a template, added a model which generated our database and now its time to create some pages. Well the good news is it’s still not time to write much code because Visual Studio can scaffold out pages based on our model for us!

Adding a controller to our project in Visual Studio gives us some options on what should be generated for us, one of which is MVC Controller with views, using Entity Framework. What that means is given a model it will create controllers and views for listing items, creating them, editing them and deleting them. No coding by us required!

Now it’s unlikely that is exactly what you’re after, but it’s generally a good starting place and deleting code you don’t need is far quicker then writing it.

Azure

Lastly there is Azure. You may have spotted a theme to all these points and that is they all remove any effort required to do any setup and instead focus on building your own logic, and this point is no different.

I remember a time, when if I wanted a server to put an application on, I had to request it, and then wait a while. What I would get back would either be a server that already had resources running on it, or a blank server that would need applications installed on it. e.g. SQL Server or .Net Framework. IIS wouldn’t have been configured and it would be a number of hours before my application would be running.

With Azure you don’t even really need to leave Visual Studio. From the publish dialog box you can create a new App Service and DB, and then publish. All the connection strings are sorted out for you. There are service plans which cost next to nothing, a domain is configured for you and at the end of the publish the website opens and is working. The whole process has taken less than 10 minutes.

SQL Server – Selecting Top 1 in a Left Join

This scenario doesn’t come up too often, but the other day I had a requirement to do a left join through a one to many relationship but only get 1 result from the right side of the join. This kind of scenario can often be accomplished using sub selects, but in this case I needed access to multiple columns so a sub select would be out of the question.

The solution was to do an OUTER APPLY instead of a LEFT JOIN. An outer apply allows you to write a sub query referencing the ID from the original table just like a sub select. However as this is effectively a kind of join you have access to multiple columns.

Example Scenario

Imagine you have a table of customers and a table of addresses and each customer can have multiple addresses.

Our tables will look like this:

Customer (CustomerID, FirstName, LastName)

Address (AddressID, CustomerID, Line1, Line2, Town, County, Country, DateAdded)

The task is to get a list of customers and their most recent address.

Solution

By using an OUTER APPLY we can join the 2 tables and get the most recent address like this:

SELECT c.*, la.*
FROM Customer c
OUTER APPLY 
    (SELECT TOP 1 *
    FROM Address a
    WHERE a.CustomerID = c.CustomerID
    ORDER BY a.DateAdded DESC
    ) AS la

LINQ to SQL Inserts and Deletes

Inserting and Deleting records in a database using LINQ to SQL is just as easy as selecting information. What’s not so easy is actually finding out how to do it. There are lots of excellent blog posts around such as this one by Scott Guthrie http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx, however most of them we’re all written for the Beta version of LINQ to SQL which let you do a .Add() or .Remove() on your table, which was  changed on the final release. 

So to insert do something like this: 

DataClassesDataContext dataContext = new DataClassesDataContext(); 

//Create my new Movie record
Movie movie = new Movie();
movie.Name = "Tim's movie"; 

//Insert the movie into the data context
dataContext.Movies.InsertOnSubmit(movie); 

//Submit the change to the database
dataContext.SubmitChanges();

And to delete do something like this:

DataClassesDataContext dataContext = new DataClassesDataContext();

var movies = from m in dataContext.Movies
                  where m.Name == "Tim's movie"
                  select m;

dataContext.Movies.DeleteAllOnSubmit(movies);

dataContext.SubmitChanges();

LINQ to SQL Connection Strings

LINQ to SQL is great but like all great things at some point it does something that you don’t expect and gives you a headache. An example of this happened to me this week with the differences between how connection strings are handeled when you LINQ to SQL model is in a class library rather than a Website or Web Application.

What makes this issue particulalry annoying is that it only appears when you try and change the database server that your code is looking at which could end up being when it’s going live or moving to a staging server.

So we all know about connection strings, their quite simple and you just store them in your web.config file, which is how LINQ to SQL works when your using them in a Website. But as soon as you move them to a class library things change. First your connection string name is no longer that simple name you gave it e.g. ConnectionString, now it is prefixed with the namespace which is annoying but not the end of the world. Second discovery though is no matter what you do, it just doesn’t seem to pick up the connection string from the web.config file. Reason being your origional connection string has now compiled itself in the class library’s dll and that is what it is using.

The Solution

Depending when you discovered this the solution is not to bad as you either have a lot of code to change or only a small amount. You can always pass a connection string to the constructor when you are createing an instance of the data context e.g.

DataClasses1DataContext da = new DataClasses1DataContext(connectionstring);

You can also set the connection string on your LINQ to SQL model to be blank, this will remove the default contructor and force you to pass a connection string. This way you web application has the choice of what connection string to use and you can keep re-using your class library in different projects.

How to search inside Stored Procedures?

A common problem faced by many developers when it comes to databases and SQL Server is how to search the text inside a stored procedure.

In many systems particularly older Classic ASP solutions, functional code has been moved from the actual application to stored procedures inside the database. This is usually because it will either run faster here, or because it was just a lot easier to perform the necessary task using TSQL. Following this though comes the problem of how you can search what’s in all those stored procedures, especially when you’re getting into the hundreds of them. Let’s say there was a Users table that contained fields for an address, but that now needs to be moved to a table of its own, you would need to search all the code for things accessing those table columns but SQL Server Management Studio certainly doesn’t provide any search box’s with the power to do this.

Never fear though syscomments is here. Syscomments contains the original text from amongst other things all the Stored Procedures in the DB s all you need to do is search that for what you’re looking for:

Select OBJECT_NAME(id), [text]
From syscomments
Where [text] like ‘%Create%’

The function OBJECT_NAME will also help you by converting the id number in the result set into the actual name of the stored procedure (or view, function etc). If you wanted to limit the result to just stored procedures you can add the following line to the where clause:

AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1