Building a Scaffolding Project for my Data Access Architecture

Goals

• Standardize my data access architecture
• Build a generator to scaffold the data access layer

Technologies Used

• Dapper - I've really enjoyed this ORM straight from the guys at StackOverflow (Entity Framework from Microsoft is a great choice as well)

Project Files

I've uploaded the code for this post on my Github.

• Code Generator Winform
• Data Access Example

CRUD Operations through Stored Procedures

Starting with the database, this archetecture expects all CRUD operations to be managed through stored procedures. The first tab view in the code generator winform will create three stored procedures (save, delete, and retrieve) for each table.

The SQL generated here will insert the three stored procedures using a specific table name based nomenclature (ex. EXAMPLE_TABLE_NAME_SP_SAVE) that will allow the data access layer code generator to detect all associated stored procedures. This nomenclature is important to note because it will allow us to add additional stored procedures in the future that will then be added to the data access file.

Scaffolding Models

I've choosen to scaffold the models myself simply because it gives me more control. Model scaffolding is managed on the second tab of the winform.

[Display(Name = "Company Name")]
public string company_name { get; set; }

Looking at this property within the Company Model, we can see that the generator has given us a DisplayNameAttribute that we can access for convience later on.

Scaffolding the Data Access Layer

Now we have reached the interesting part! Let's take a look at the generated save function for Company Data Access.

public Company Retrieve(string company_id)
{
    using (IDbConnection cnn = new SqlConnection(GetConnectionString()))
    {
        string sql = $"EXEC COMPANY_SP_RETRIEVE " +
        $"{company_id} ";

        return cnn.Query<Company>
        (sql).FirstOrDefault();
    }
}

Using Dapper we pass the sql for our stored procedure to the database and are returned the Company Model object.

That's it! Everything we need to access our data in a database-first scaffolding work flow.

Rocco Daigler

Web developer

Hi! I'm a professsional .NET developer as well as an avid hobbyist in Swift developement. I enjoy finding complex business processes and translating them into software solutions. I put this site together so that I could share code snippets for difficult problems that I was unable to find resources for. I hope there's something here that can help you!