Repository.tt

<#@ template debug="false" hostspecific="true" language="C#" #>
<#@ output extension=".Generated.cs" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="EnvDTE" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="EnvDTE" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ include file="Settings.ttinclude" once="true"#><##>
<#@ include file="..\..\T4SSDT.ttinclude" once="true"#><##>
//------------------------------------------------------------------------------
// <auto-generated>
// T4SSDT Data access layer code generator.
// </auto-generated>
//------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.CodeDom.Compiler;

namespace Model
{
<#
var hostServiceProvider = (IServiceProvider)this.Host;
var dte = (DTE)hostServiceProvider.GetService(typeof(DTE));
TSqlModel model = null;

foreach (Project project in dte.Solution)
{
    if (project.Kind == "{00d1a9c2-b5f0-4af3-8072-f6c62b433612}")
    {
        model = GenerateModelFromProjectItems(project.ProjectItems);
        break;
    }
}
    
if (null == model)
    Error("Database project was not found!");

/****************************************** REPOSITORY INTERFACE ***************************************/
void RenderCrudInterface(string name, ColumnInfo[] columns, ColumnInfo[] primaryKey)
{
#>
        int Create(<#=name#> obj);
<# if (null != primaryKey) {#>
        int Update(<#=name#> obj);
        int Delete<#=name#>(<#=string.Join(", ", primaryKey.Select(c => c.TypeName + " " + ToCamelCase(c.FieldName)))#>);
        bool Get<#=name#>ById(<#=string.Join(", ", primaryKey.Select(c => c.TypeName + " " + ToCamelCase(c.FieldName)))#>, out <#=name#> obj);
<# }
}

void RenderViewAccessorInterface(string name, ColumnInfo[] columns, ColumnInfo[] primaryKey)
{
#>
        IEnumerable<<#=name#>> Get<#=name#>();
<#
}

void RenderProcedureCallerInterface(string name, ParameterInfo[] parameters)
{
#>
        int <#=name#>(<#=string.Join(", ",
                                     parameters.Select(p => (p.IsOutput ? "ref " : "") + p.TypeName + " " + p.Name))#>);
<#
}
#>
    [GeneratedCode("T4SSDT", "1.0")]
    public partial interface IRepository
    {
<#
ForEachTable(model, RenderCrudInterface, ExcludeFromPOCO.Union(ExcludeFromCRUD));
ForEachView(model, RenderViewAccessorInterface, ExcludeFromPOCO.Union(ExcludeFromCRUD));
ForEachStoredProcedure(model, RenderProcedureCallerInterface, ExcludeFromCRUD);

#>
    }

<#
/****************************************** REPOSITORY IMPLEMENTATION **********************************/
void RenderCrudMethods(string name, ColumnInfo[] columns, ColumnInfo[] primaryKey)
{
    IEnumerable<ColumnInfo> dataColumns = columns.Where(c => !c.IsIdentity);
#>
        public int Create(<#=name#> obj)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                string cmdText = @"INSERT INTO <#=name#> VALUES (<#=string.Join(", ", dataColumns.Select(c => "@" + c.FieldName))#>)";
                SqlCommand cmd = new SqlCommand(cmdText, connection);
                cmd.Parameters.AddRange(new[]
                {
<# foreach (ColumnInfo column in dataColumns) { #>
                    new SqlParameter("@<#=column.FieldName#>", obj.<#=column.FieldName#>),
<# } #>
                });

                using (cmd)
                {
                    return cmd.ExecuteNonQuery();
                }
            }
        }

<# if (null != primaryKey) {#>
        public int Update(<#=name#> obj)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                string cmdText = @"UPDATE <#=name#>
SET <#=string.Join(", ", dataColumns.Where(c => !primaryKey.Contains(c)).Select(c => $"{c.FieldName} = @{c.FieldName}"))#>
WHERE <#=string.Join(", ", primaryKey.Select(c => $"{c.FieldName} = @{c.FieldName}"))#>";
                SqlCommand cmd = new SqlCommand(cmdText, connection);
                cmd.Parameters.AddRange(new[]
                {
<# foreach (ColumnInfo column in dataColumns) { #>
                    new SqlParameter("@<#=column.FieldName#>", obj.<#=column.FieldName#>),
<# } #>
                });

                using (cmd)
                {
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public int Delete<#=name#>(<#=string.Join(", ", primaryKey.Select(c => c.TypeName + " " + ToCamelCase(c.FieldName)))#>)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                string cmdText = @"DELETE FROM <#=name#> WHERE <#=string.Join(" AND ", primaryKey.Select(c => $"{c.FieldName} = @{c.FieldName}"))#>";
                SqlCommand cmd = new SqlCommand(cmdText, connection);
                cmd.Parameters.AddRange(new[]
                {
<# foreach (ColumnInfo column in primaryKey) { #>
                    new SqlParameter("@<#=column.FieldName#>", <#=ToCamelCase(column.FieldName)#>),
<# } #>
                });

                using (cmd)
                {
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public bool Get<#=name#>ById(<#=string.Join(", ", primaryKey.Select(c => c.TypeName + " " + ToCamelCase(c.FieldName)))#>, out <#=name#> obj)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                string cmdText = @"
SELECT <#=string.Join(", ", columns.Select(c => c.FieldName))#>
FROM <#=name#>
WHERE <#=string.Join(" AND ", primaryKey.Select(c => $"{c.FieldName} = @{c.FieldName}"))#>";
                SqlCommand cmd = new SqlCommand(cmdText, connection);
                cmd.Parameters.AddRange(new[]
                {
<# foreach (ColumnInfo column in primaryKey) { #>
                    new SqlParameter("@<#=column.FieldName#>", <#=ToCamelCase(column.FieldName)#>),
<# } #>
                });

                using (cmd)
                {
                    obj = new <#=name#>();
                    IDataReader reader = cmd.ExecuteReader();
                    if (reader.NextResult())
                    {
<# foreach (ColumnInfo column in columns) { #>
                        obj.<#=column.FieldName#> = (<#=column.TypeName#>)reader["<#=column.FieldName#>"];
<# } #>
                    }
                    else
                        return false;

                    return true;
                }
            }
        }
<# }
}

void RenderViewAccessor(string name, ColumnInfo[] columns, ColumnInfo[] primaryKey)
{
#>
        public IEnumerable<<#=name#>> Get<#=name#>()
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                string cmdText = @"SELECT <#=string.Join(", ", columns.Select(c => c.FieldName))#> FROM <#=name#>";
                SqlCommand cmd = new SqlCommand(cmdText, connection);

                using (cmd)
                {
                    IDataReader reader = cmd.ExecuteReader();
                    while (reader.NextResult())
                    {
                        <#=name#> obj = new <#=name#>();
<# foreach (ColumnInfo column in columns) { #>
                        obj.<#=column.FieldName#> = (<#=column.TypeName#>)reader["<#=column.FieldName#>"];
<# } #>
                        yield return obj;
                    }
                }
            }
        }
<#
}

void RenderProcedureCaller(string name, ParameterInfo[] parameters)
{
#>
        public int <#=name#>(<#=string.Join(", ", parameters.Select(p => (p.IsOutput ? "ref " : "") + p.TypeName + " " + ToCamelCase(p.Name)))#>)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                connection.Open();

                SqlCommand cmd = new SqlCommand("<#=name#>", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(new[]
                {
<# foreach (ParameterInfo p in parameters) { #>
                    new SqlParameter("@<#=p.Name#>", <#=ToCamelCase(p.Name)#>)<#=p.IsOutput ? " { Direction = ParameterDirection.InputOutput }" : string.Empty#>,
<# } #>
                });

                using (cmd)
                {
                    int retVal = cmd.ExecuteNonQuery();
<# foreach (ParameterInfo p in parameters.Where(p => p.IsOutput)) { #>
                    <#=ToCamelCase(p.Name)#> = (<#=p.TypeName#>)cmd.Parameters["@<#=p.Name#>"].Value;
<# } #>

                    return retVal;
                }
            }
        }
<#
}

#>
    [GeneratedCode("T4SSDT", "1.0")]
    public partial class SqlRepository : IRepository
    {
        private readonly string _connectionString;
        private readonly string _readOnlyConnectionString;

        public SqlRepository(string connectionString)
        {
            _connectionString = connectionString;
            _readOnlyConnectionString = connectionString;
        }

        public SqlRepository(string connectionString, string readOnlyConnectionString)
        {
            _connectionString = connectionString;
            _readOnlyConnectionString = readOnlyConnectionString;
        }

<#
ForEachTable(model, RenderCrudMethods, ExcludeFromPOCO.Union(ExcludeFromCRUD));
ForEachView(model, RenderViewAccessor, ExcludeFromPOCO.Union(ExcludeFromCRUD));
ForEachStoredProcedure(model, RenderProcedureCaller, ExcludeFromCRUD);

#>
    }
}
<# model.Dispose(); #>