Skip to content

Object relational mapping (ORM)

Jump to bottom Edit New page
Csaba Bernáth edited this page Jun 28, 2023 · 2 revisions

Object-relational mapping (ORM)

Basic concepts of Data Commander C# ORM

ORM Feature Data Commander Dapper NHibernate
C# source code Generated Manual Manual
C# source code: typed input parameters Yes No No
C# source code: typed result set Yes No No
Magic framework No Reflection Reflection
Magic SQL statement manipulation No No Yes
Compile vs runtime errors Compile time Runtime Runtime
Debuggable Yes No No
Performance Yes No No

How to generate insert/update/delete SQL statements to a SQL table

Requirements

  • The SQL table must have an identifier column (uniqueidentifier). The identifier column should have a unique constraint.
  • The SQL table can have a version column (bigint) for optimistic concurrency control.
  • All columns (including identifier and version) must have value before inserting.

Description of generated C# entity class

  • Immutable record class for inserting and updating rows.

Description of generated C# methods

  • The SQL table row is updated by identifier and expected version. The record class contains new values of the row. The additional 'expected version' parameter is used for optimistic concurrency control.
  • The SQL table row is deleted by identifier and expected version. Data Commander generates C# (requires .NET Standard 2.0 + Foundation assembly) source code wrapper to a SQL table.

How to generate C# ORM source code

  1. Right click on a table in object explorer.
  2. Navigate to 'Script table as' menu item.
  3. Click 'C# ORM to clipboard' menu item.
  4. Paste the content of the clipboard into a .cs file in Visual Studio.
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using Foundation.Collections.ReadOnly;
using Foundation.Data;
using Foundation.Data.SqlClient;
using Foundation.Data.SqlClient.SqlStatementFactories;
using Foundation.Text;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace OrmSamples
{
    [TestClass]
    public class UnitTest1
    {
        [TestMethod]
        public void TestMethod1()
        {
            var records = Enumerable.Range(0, 100)
                .Select(i => new OrmSampleTable(Guid.NewGuid(), i.ToString(), i, DateTime.Now))
                .ToList();
            var insertSqlStatement = OrmSampleTableSqlStatementFactory.CreateInsertSqlStatement(records);
            var updateSqlStatements = records
                .Select(record =>
                {
                    var version = record.Version + 1;
                    var text = record.Text + version;
                    return new OrmSampleTable(record.Id, text, version, DateTime.Now);
                })
                .Select(OrmSampleTableSqlStatementFactory.CreateUpdateSqlStatement)
                .ToList();
            var deleteSqlStatements = records
                .Select(record => OrmSampleTableSqlStatementFactory.CreateDeleteSqlStatement(record.Id))
                .ToList();
            var textBuilder = new TextBuilder();
            textBuilder.Add(insertSqlStatement);
            textBuilder.Add(updateSqlStatements.SelectMany(i => i));
            textBuilder.Add(deleteSqlStatements.SelectMany(i => i));
            var commandText = textBuilder.ToLines().ToIndentedString("    ");

            using (var connection = SqlConnectionFactory.Create())
            {
                connection.Open();
                var executor = connection.CreateCommandExecutor();
                executor.ExecuteNonQuery(new CreateCommandRequest(commandText));
            }
        }

        public sealed class OrmSampleTable
        {
            public readonly Guid Id;
            public readonly long Version;
            public readonly string Text;
            public readonly DateTime Timestamp;

            public OrmSampleTable(Guid id, long version, string text, DateTime timestamp)
            {
                Id = id;
                Version = version;
                Text = text;
                Timestamp = timestamp;
            }
        }

        public static class OrmSampleTableSqlStatementFactory
        {
            public static ReadOnlyCollection<Line> CreateInsertSqlStatement(IEnumerable<OrmSampleTable> records)
            {
                var columns = new[]
                {
                    "Id",
                    "Version",
                    "Text",
                    "Timestamp"
                };
                var rows = records.Select(record => new[]
                {
                    record.Id.ToSqlConstant(),
                    record.Version.ToSqlConstant(),
                    record.Text.ToNullableNVarChar(),
                    record.Timestamp.ToSqlConstant()
                }).ToReadOnlyCollection();
                var insertSqlStatement = InsertSqlStatementFactory.Create("dbo.OrmSampleTable", columns, rows);
                return insertSqlStatement;
            }

            public static ReadOnlyCollection<Line> CreateUpdateSqlStatement(OrmSampleTable record, long expectedVersion)
            {
                var setColumns = new[]
                {
                    new ColumnNameValue("Version", record.Version.ToSqlConstant()),
                    new ColumnNameValue("Text", record.Text.ToNullableNVarChar()),
                    new ColumnNameValue("Timestamp", record.Timestamp.ToSqlConstant())
                };
                var whereColumns = new[]
                {
                    new ColumnNameValue("Id", record.Id.ToSqlConstant()),
                    new ColumnNameValue("Version", expectedVersion.ToSqlConstant())
                };
                var updateSqlStatement = UpdateSqlStatementFactory.Create("dbo.OrmSampleTable", setColumns, whereColumns);
                var validation = ValidationFactory.Create("update dbo.OrmSampleTable failed");
                var textBuilder = new TextBuilder();
                textBuilder.Add(updateSqlStatement);
                textBuilder.Add(validation);
                return textBuilder.ToLines();
            }

            public static ReadOnlyCollection<Line> CreateDeleteSqlStatement(Guid id, long version)
            {
                var whereColumns = new[]
                {
                    new ColumnNameValue("Id", id.ToSqlConstant()),
                    new ColumnNameValue("Version", version.ToSqlConstant())
                };
                var deleteSqlStatement = DeleteSqlStatementFactory.Create("dbo.OrmSampleTable", whereColumns);
                var validation = ValidationFactory.Create("delete dbo.OrmSampleTable failed");
                var textBuilder = new TextBuilder();
                textBuilder.Add(deleteSqlStatement);
                textBuilder.Add(validation);
                return textBuilder.ToLines();
            }
        }
    }
}

How to generate C# Command/Query wrapper to a SQL command/query

Data Commander generates C# (requires .NET Standard 2.0 + Foundation assembly) source code wrapper for a SQL command/query. The query is similar to a CQRS Command/Query:

  • Command/Query class (command/query input parameters)
  • QueryResult class (query output/result)
  • (Command/Query)Handler class (the handler which executes the command/query)

How to use the ORM generator

Download and restore the SQL Server 2016 sample database from https://github.com/microsoft/sql-server-samples

Open the database with Data Commander and execute the following command:

/* Query Configuration
{
  "Using": "using Foundation.Assertions;
using Foundation.Collections.ReadOnly;
using Foundation.Data;
using Foundation.Data.SqlClient;",
  "Namespace": "Foundation.NetStandard20.Test.GetPersonQueryNamespace",
  "Name": "GetCustomerInvoices",
  "Results": [
    "Customer",
    "Invoice"
  ]
}
*/
declare @customerId int = 1 /*not null*/
declare @invoiceDate date = '20160101'
-- CommandText
select
    c.CustomerID,
    c.CustomerName
from Sales.Customers c
where c.CustomerID = @customerId

select
    i.InvoiceID,
    i.CustomerID,
    i.InvoiceDate
from Sales.Invoices i
where
    i.CustomerID = @customerId
    and i.InvoiceDate >= @invoiceDate
order by i.CustomerID,i.InvoiceID

The program generates C# classes in one file:

using System;
using System.Data;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;
using Foundation.Assertions;
using Foundation.Collections.ReadOnly;
using Foundation.Data;
using Foundation.Data.SqlClient;

namespace Foundation.NetStandard20.Test.GetPersonQueryNamespace
{
    public sealed class GetCustomerInvoicesDbQuery
    {
        public readonly int CustomerId;
        public readonly DateTime? InvoiceDate;

        public GetCustomerInvoicesDbQuery(int customerId, DateTime? invoiceDate)
        {
            CustomerId = customerId;
            InvoiceDate = invoiceDate;
        }
    }

    public sealed class GetCustomerInvoicesDbQueryResult
    {
        public readonly ReadOnlySegmentLinkedList<Customer> Customer;
        public readonly ReadOnlySegmentLinkedList<Invoice> Invoice;

        public GetCustomerInvoicesDbQueryResult(ReadOnlySegmentLinkedList<Customer> customer, ReadOnlySegmentLinkedList<Invoice> invoice)
        {
            Customer = customer;
            Invoice = invoice;
        }
    }

    public sealed class Customer
    {
        public readonly int CustomerID;
        public readonly string CustomerName;

        public Customer(int customerID, string customerName)
        {
            CustomerID = customerID;
            CustomerName = customerName;
        }
    }

    public sealed class Invoice
    {
        public readonly int InvoiceID;
        public readonly int CustomerID;
        public readonly DateTime InvoiceDate;

        public Invoice(int invoiceID, int customerID, DateTime invoiceDate)
        {
            InvoiceID = invoiceID;
            CustomerID = customerID;
            InvoiceDate = invoiceDate;
        }
    }

    public sealed class GetCustomerInvoicesDbQueryHandler
    {
        private const string CommandText = @"select
    c.CustomerID,
    c.CustomerName
from Sales.Customers c
where c.CustomerID = @customerId

select
    i.InvoiceID,
    i.CustomerID,
    i.InvoiceDate
from Sales.Invoices i
where
    i.CustomerID = @customerId
    and i.InvoiceDate >= @invoiceDate
order by i.CustomerID,i.InvoiceID";
        private static int? CommandTimeout = 0;
        private readonly IDbConnection _connection;
        private readonly IDbTransaction _transaction;

        public GetCustomerInvoicesDbQueryHandler(IDbConnection connection, IDbTransaction transaction)
        {
            Assert.IsNotNull(connection);
            _connection = connection;
            _transaction = transaction;
        }

        public GetCustomerInvoicesDbQueryResult Handle(GetCustomerInvoicesDbQuery query)
        {
            Assert.IsNotNull(query);
            var request = ToExecuteReaderRequest(query, CancellationToken.None);
            return ExecuteReader(request);
        }

        public Task<GetCustomerInvoicesDbQueryResult> HandleAsync(GetCustomerInvoicesDbQuery query, CancellationToken cancellationToken)
        {
            Assert.IsNotNull(query);
            var request = ToExecuteReaderRequest(query, cancellationToken);
            return ExecuteReaderAsync(request);
        }

        private CreateCommandRequest ToCreateCommandRequest(GetCustomerInvoicesDbQuery query)
        {
            var parameters = ToParameters(query);
            return new CreateCommandRequest(CommandText, parameters, CommandType.Text, CommandTimeout, _transaction);
        }

        private static ReadOnlyList<object> ToParameters(GetCustomerInvoicesDbQuery query)
        {
            var parameters = new SqlParameterCollectionBuilder();
            parameters.Add("customerId", query.CustomerId);
            parameters.AddNullableDate("invoiceDate", query.InvoiceDate);
            return parameters.ToReadOnlyList();
        }

        private ExecuteReaderRequest ToExecuteReaderRequest(GetCustomerInvoicesDbQuery query, CancellationToken cancellationToken)
        {    
            var createCommandRequest = ToCreateCommandRequest(query);
            return new ExecuteReaderRequest(createCommandRequest, CommandBehavior.Default, cancellationToken);
        }

        private GetCustomerInvoicesDbQueryResult ExecuteReader(ExecuteReaderRequest request)
        {
            GetCustomerInvoicesDbQueryResult result = null;
            var executor = _connection.CreateCommandExecutor();
            executor.ExecuteReader(request, dataReader =>
            {
                var customer = dataReader.ReadResult(ReadCustomer).ToReadOnlySegmentLinkedList(128);
                var invoice = dataReader.ReadNextResult(ReadInvoice).ToReadOnlySegmentLinkedList(128);
                result = new GetCustomerInvoicesDbQueryResult(customer, invoice);
            });

            return result;
        }

        private async Task<GetCustomerInvoicesDbQueryResult> ExecuteReaderAsync(ExecuteReaderRequest request)
        {
            GetCustomerInvoicesDbQueryResult result = null;
            var connection = (DbConnection)_connection;
            var executor = connection.CreateCommandAsyncExecutor();
            await executor.ExecuteReaderAsync(request, async dataReader =>
            {
                var customer = (await dataReader.ReadResultAsync(ReadCustomer, request.CancellationToken)).ToReadOnlySegmentLinkedList(128);
                var invoice = (await dataReader.ReadNextResultAsync(ReadInvoice, request.CancellationToken)).ToReadOnlySegmentLinkedList(128);
                result = new GetCustomerInvoicesDbQueryResult(customer, invoice);
            });

            return result;
        }

        private static Customer ReadCustomer(IDataRecord dataRecord)
        {
            var customerID = dataRecord.GetInt32(0);
            var customerName = dataRecord.GetString(1);

            return new Customer(customerID, customerName);
        }

        private static Invoice ReadInvoice(IDataRecord dataRecord)
        {
            var invoiceID = dataRecord.GetInt32(0);
            var customerID = dataRecord.GetInt32(1);
            var invoiceDate = dataRecord.GetDateTime(2);

            return new Invoice(invoiceID, customerID, invoiceDate);
        }
    }
}
  1. The generated code can be used like this:
public void TestMethod1()
{
    var csb = new SqlConnectionStringBuilder();
    csb.DataSource = @".\SQL2016_001";
    csb.InitialCatalog = "WideWorldImporters";
    csb.IntegratedSecurity = true;

    using (var connection = new SqlConnection())
    {
        connection.ConnectionString = csb.ConnectionString;
        connection.Open();

        var query = new GetCustomerInvoicesQuery(1, new DateTime(2016, 01, 01));
        var handler = new GetCustomerInvoicesHandler(connection, null);
        var result = handler.Handle(query);
    }
}

var connectionStringBuilder = new SqlConnectionStringBuilder();
connectionStringBuilder.DataSource = @".\SQL2016_001";
connectionStringBuilder.InitialCatalog = "WideWorldImporters";
connectionStringBuilder.IntegratedSecurity = true;

using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
{
	connection.Open();
	var executor = connection.CreateCommandAsyncExecutor();

	var commandText = "waitfor delay '00:00:01'";
	var affectedRows = await executor.ExecuteNonQueryAsync(new ExecuteNonReaderRequest(commandText));

	commandText = "select top 1 i.InvoiceID from Sales.Invoices i";
	var scalar = await executor.ExecuteScalarAsync(new ExecuteNonReaderRequest(commandText));

	var commandText = @"select
c.CustomerID,
c.CustomerName
from Sales.Customers c

select
i.InvoiceID,
i.CustomerID,
i.InvoiceDate
from Sales.Invoices i
order by i.CustomerID,i.InvoiceID";
	var response = await executor.ExecuteReaderAsync(new ExecuteReaderRequest(commandText), ReadCustomer, ReadInvoice);
	var customers = response.Objects1;
	var invoices = response.Objects2;
}
Add a custom footer
Add a custom sidebar
Clone this wiki locally