Roman Tumaykin

Simple Data Access Layer Generator.

January 17, 2016 | | Tags : SQL Data Access Layer C# User Manual

Introduction

A few years back I created a T4-based C# code generator that was generating enums based on the database tables. It was a very useful exercise. I learned how to build Visual Studio item templates, T4 templates implementation, plus I used the tool to simplify my own development, since I no longer needed to remember to update enums in response to the data changes. Later I decided to use my experience to build another tool, which I called a Simple Data Access Generator. In addition to enums generation, this tool was also generating C# code to call stored procedures and retrieve the results. In the end, calling stored procedures is always done the same way - open connection, create command, define parameters, assign parameters values, execute stored procedure, and finally retrieve return value, output parameters, and recordsets returned by the stored procedure. The difficult part has always been that SQL types are different from .Net types and therefore, after every call, it was necessary to translate everything into what C# could consume.

Why stored procedures only? Because I hold a firm opinion that applications should never call to the tables or views directly. Application should not know the database schema at all, and all it should be allowed to call - stored procedures. There are other arguments too, but I don’t want to focus on that topic.

Originally this tool also used T4 templates. It worked only for Visual Studio 2012. I had to create a separate extension to support Visual Studio 2013 because I could not make it work in both versions from the same codebase. Recently I created a version for Visual Studio 2015 and decided to change the approach. Instead of using T4 templates I decided to generate C# code directly. That turned out to be much simpler and also allowed me to make this extension compatible with previous versions of Visual Studio. I’ve been using this tool to write production code ever since I created it, so I feel confident that it does a great job and can be trusted. I have never created a full user manual on how to use this tool, so today I am paying off that debt.

Functionality

  1. Enums from database. Simple Data Access Layer produces enums based on the tables selected by user. As long as table has at least one character column and one numeric column, it can be used to generate enums. I specifically don’t require any uniqueness on any of these columns, since if the produced enums end up having duplicates, the code won’t compile anyway.

  2. C# methods to call stored procedures. Simple Data Access Layer creates a static Execute method for each stored procedure selected by a user. Each parameter for that stored procedure, plus an optional scope (transaction), is a part of that method. If the .Net framework supports async/await, then an asynchronous ExecuteAsync method is produced as well. The Execute and ExecuteAsync methods both return a class with the following properties:

    • procedure return value;
    • every parameter for that stored procedure, input and output, populated with values;
    • every recordset returned by the stored procedure for the SQL Versions 2012 and below, and the first recordset for SQL version 2014 and above. Based on the columns of a row in a given recordset, a class is created to hold returned row values. Client receives a List collection of these row classes.

    For example, for the following stored procedure:

    CREATE PROCEDURE [dbo].[GetCustomer] 
	@CustomerId int
AS
SELECT	[CustomerId],
     [FirstName],
     [LastName]
FROM	[dbo].[customer]
WHERE	[CustomerId] = @CustomerId;
GO

    The following C# code class would be generated:

    public class GetCustomer
{
 public class ParametersCollection
 {
     public global::System.Int32? CustomerId { get; private set; }

     public ParametersCollection(global::System.Int32? customerId)
     {
         this.CustomerId = customerId;
     }

 }

 public ParametersCollection Parameters { get; private set; }
 public global::System.Int32 ReturnValue { get; private set; }

 public class Record0
 {
     public global::System.Int32? CustomerId { get; private set; }
     public global::System.String FirstName { get; private set; }
     public global::System.String LastName { get; private set; }

     public Record0(global::System.Int32? customerId, global::System.String firstName,
         global::System.String lastName)
     {
         this.CustomerId = customerId;
         this.FirstName = firstName;
         this.LastName = lastName;
     }

 }

 public global::System.Collections.Generic.List<Record0> Recordset0 { get; private set; }

 public static async global::System.Threading.Tasks.Task<GetCustomer> ExecuteAsync(
     global::System.Int32? customerId, global::Sample.ExecutionScope executionScope = null,
     global::System.Int32 commandTimeout = 30)
 {
     var retValue = new GetCustomer();
     {
         var retryCycle = 0;
         while (true)
         {
             global::System.Data.SqlClient.SqlConnection conn = executionScope == null
                 ? new global::System.Data.SqlClient.SqlConnection(global::Sample.ExecutionScope.ConnectionString)
                 : executionScope.Transaction.Connection;
             try
             {
                 if (conn.State != global::System.Data.ConnectionState.Open)
                 {
                     if (executionScope == null)
                     {
                         await conn.OpenAsync();
                     }
                     else
                     {
                         retryCycle = int.MaxValue;
                         throw new global::System.Exception("Execution Scope must have an open connection.");
                     }
                 }
                 using (global::System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand())
                 {
                     cmd.CommandType = global::System.Data.CommandType.StoredProcedure;
                     if (executionScope != null && executionScope.Transaction != null)
                         cmd.Transaction = executionScope.Transaction;
                     cmd.CommandTimeout = commandTimeout;
                     cmd.CommandText = "[dbo].[GetCustomer]";
                     cmd.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@CustomerId",
                         global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.Input, true,
                         10, 0, null, global::System.Data.DataRowVersion.Default, customerId) {});
                     cmd.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@ReturnValue",
                         global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.ReturnValue,
                         true, 0, 0, null, global::System.Data.DataRowVersion.Default,
                         global::System.DBNull.Value));
                     using (global::System.Data.SqlClient.SqlDataReader reader = await cmd.ExecuteReaderAsync())
                     {
                         retValue.Recordset0 = new global::System.Collections.Generic.List<Record0>();
                         while (await reader.ReadAsync())
                         {
                             retValue.Recordset0.Add(
                                 new Record0(
                                     reader.IsDBNull(0)
                                         ? null
                                         : await reader.GetFieldValueAsync<global::System.Int32?>(0),
                                     reader.IsDBNull(1)
                                         ? null
                                         : await reader.GetFieldValueAsync<global::System.String>(1),
                                     reader.IsDBNull(2)
                                         ? null
                                         : await reader.GetFieldValueAsync<global::System.String>(2)));
                         }
                     }
                     retValue.Parameters = new ParametersCollection(customerId);
                     retValue.ReturnValue = (global::System.Int32) cmd.Parameters["@ReturnValue"].Value;
                     return retValue;
                 }
             }
             catch (global::System.Data.SqlClient.SqlException e)
             {
                 if (retryCycle++ > 9 || !ExecutionScope.RetryableErrors.Contains(e.Number))
                     throw;
                 global::System.Threading.Thread.Sleep(1000);
             }
             finally
             {
                 if (executionScope == null && conn != null)
                 {
                     ((global::System.IDisposable) conn).Dispose();
                 }
             }
         }
     }
 }

 public static GetCustomer Execute(global::System.Int32? customerId,
     global::Sample.ExecutionScope executionScope = null, global::System.Int32 commandTimeout = 30)
 {
     var retValue = new GetCustomer();
     {
         var retryCycle = 0;
         while (true)
         {
             global::System.Data.SqlClient.SqlConnection conn = executionScope == null
                 ? new global::System.Data.SqlClient.SqlConnection(global::Sample.ExecutionScope.ConnectionString)
                 : executionScope.Transaction.Connection;
             try
             {
                 if (conn.State != global::System.Data.ConnectionState.Open)
                 {
                     if (executionScope == null)
                     {
                         conn.Open();
                     }
                     else
                     {
                         retryCycle = int.MaxValue;
                         throw new global::System.Exception("Execution Scope must have an open connection.");
                     }
                 }
                 using (global::System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand())
                 {
                     cmd.CommandType = global::System.Data.CommandType.StoredProcedure;
                     if (executionScope != null && executionScope.Transaction != null)
                         cmd.Transaction = executionScope.Transaction;
                     cmd.CommandTimeout = commandTimeout;
                     cmd.CommandText = "[dbo].[GetCustomer]";
                     cmd.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@CustomerId",
                         global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.Input, true,
                         10, 0, null, global::System.Data.DataRowVersion.Default, customerId) {});
                     cmd.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@ReturnValue",
                         global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.ReturnValue,
                         true, 0, 0, null, global::System.Data.DataRowVersion.Default,
                         global::System.DBNull.Value));
                     using (global::System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
                     {
                         retValue.Recordset0 = new global::System.Collections.Generic.List<Record0>();
                         while (reader.Read())
                         {
                             retValue.Recordset0.Add(
                                 new Record0(
                                     reader.IsDBNull(0) ? null : reader.GetFieldValue<global::System.Int32?>(0),
                                     reader.IsDBNull(1) ? null : reader.GetFieldValue<global::System.String>(1),
                                     reader.IsDBNull(2) ? null : reader.GetFieldValue<global::System.String>(2)));
                         }
                     }
                     retValue.Parameters = new ParametersCollection(customerId);
                     retValue.ReturnValue = (global::System.Int32) cmd.Parameters["@ReturnValue"].Value;
                     return retValue;
                 }
             }
             catch (global::System.Data.SqlClient.SqlException e)
             {
                 if (retryCycle++ > 9 || !ExecutionScope.RetryableErrors.Contains(e.Number))
                     throw;
                 global::System.Threading.Thread.Sleep(1000);
             }
             finally
             {
                 if (executionScope == null && conn != null)
                 {
                     ((global::System.IDisposable) conn).Dispose();
                 }
             }
         }
     }
 }
}

Walkthrough

  1. Open Visual Studio 2015, open Tools>Extensions and Updates... menu, change your search target to “Online” and type in a search box “Simple Data Access Layer” by Roman Tumaykin.

    Step 1

  2. Click on the download button, install the extension. Restart Visual Studio if necessary.
  3. Create a new C# project. For example a console application.

  4. In the solution explorer, select newly created project, and, choose Project>Add New Item from the Visual Studio menu. Select “Simple Data Access Layer” template from “Visual C# Items”/”Data” in the navigation tree.

    Step 2

    Click Trust on this nasty “Security Warning”. There is no way around it.

    Step 2 (Warning)

    Please note that the template also adds a Microsoft.SqlServer.Types 11.0.2 nuget package to support spatial data types. You will see a readme file from this package.

  5. After the new file is created, you will see the custom editor:

    Step 3

  6. Click on Edit Model Info. Fill in information in the “Design Time Database Connection” area. This will set database and credentials that the template will use to generate code. This does not have to (and even shouldn’t) be your production database. You create all objects against a development database, and then at runtime you will be connecting to production database, using generated code. You should use an account that has rights to read from system tables in the design database. Please note that the designer stores password for the SQL authentication in a local .suo solution file, which you most likely won’t commit to any of the source control systems, so it is pretty safe to check “Save password”. The “Connection string” in the “Run Time Options” area is what the generated code will look for during the execution, and will use it to establish connection to the target database. You will need to make sure this connection string exists in the application where the generated code will be used. The “Namespace for generated code” contains the root namespace for all of the generated code. Subsequent namespaces will be created in the following way:
    • Scope/Transaction can be started by creating new instance of <Root Namespace>.ExecutionScope;
    • Table Type classes will be in <Root Namespace>.TableVariables.<Table Type Schema Name>.<Table Type Name>;
    • Static Execute Methods are respectively <Root Namespace>.Executables.<Stored Procedure Schema Name>.<Stored Procedure Name>.Execute (...) and <Root Namespace>.Executables.<Stored Procedure Schema Name>.<Stored Procedure Name>.ExecuteAsync (...);

    Step 4

  7. Click Next and choose tables, and respective columns, to produce enums. If you like you can explicitly specify the enum name in the alias column:

    Step 5

  8. Click Next and choose the stored procedures to have C# access code to be generated for. Same as before, you may choose an explicit name for a generated class by using the alias column:

    Step 6

  9. Click Finish. But in order to save the changes, you will need to save the config file. Save the config file explicitly, not by choosing save all. You may see for the first few times warning about files modified by external application, so just reload them. I will investigate and fix this issue later.

    Step 7

    Now you can see that the .cs file has been populated with the code:

    Step 8

    Now you can use these methods in your application. Just don’t forget to add a real connection string to the application configuration file.

Working with the Table Type Variables

I tried to simplify Table Type Parameters handling, so for each Table Type parameter, a class is generated, that you can use to pass data to the stored procedure.

For example, for the following database objects

CREATE TYPE [dbo].TableType1 AS TABLE (
	col1 int,
	col2 int
);
GO
CREATE PROCEDURE dbo.TableProc1 
	@Tt1 TableType1 READONLY
AS
GO

these clases will be generated - Sample.TableVariables.dbo.TableType1Row and Sample.TableVariables.dbo.TableType1 : List<TableType1Row>. You can just populate a collection of TableType1Row with data, pass it to as TableType1 constructor and then pass it as an argument to a call to a Sample.Executales.dbo.TableProc1.Execute method:

var tt = new Sample.TableVariables.dbo.TableType1(new[]
{
    new Sample.TableVariables.dbo.TableType1Row(1, 2),
    new Sample.TableVariables.dbo.TableType1Row(2, 3),
    new Sample.TableVariables.dbo.TableType1Row(4, 5)
});
var res = Sample.Executables.dbo.TableProc1.Execute(tt);

.

Conclusion

You can see how simple is to use this Visual Studio extension. It usually saves me an enormous amount of time.

The code is hosted on Githib. Please feel free to ask any questions or leave comments.

Comments