Skip to content

Instantly share code, notes, and snippets.

@pwhe23

pwhe23/GenerateProcCalls.tt

Last active May 13, 2021 09:57
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save pwhe23/959dd4cd82e4de7c536b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save pwhe23/959dd4cd82e4de7c536b to your computer and use it in GitHub Desktop.
Download ZIP
Generate Proc calls from Database
Raw
GenerateProcCalls.tt
<#@ Template Debug="true" HostSpecific="true" #>
<#@ Output Extension=".cs" #>
<#@ Assembly Name="Microsoft.CSharp" #>
<#@ Assembly Name="System.Configuration, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" #>
<#@ Assembly Name="System.Core" #>
<#@ Assembly Name="System.Data" #>
<#@ Import Namespace="System" #>
<#@ Import Namespace="System.Collections" #>
<#@ Import Namespace="System.Collections.Generic" #>
<#@ Import Namespace="System.Configuration" #>
<#@ Import Namespace="System.Data.SqlClient" #>
<#@ Import Namespace="System.Diagnostics" #>
<#@ Import Namespace="System.Dynamic" #>
<#@ Import Namespace="System.IO" #>
<#@ Import Namespace="System.Linq" #>
<#
// Configure the proc generation options
var namespaceName = "Namespace";
var className = "Procs";
var connectionString = GetConnectionString(@"..\..\WebFolder\Web.config", "ShadowCopy");
var whitelist = new List<string> {
"Proc1",
"Schema_Proc2",
};
var procs = GetProcs(connectionString, whitelist);
#>
using System;
using System.Data;
using Dapper;
namespace <#= namespaceName #>
{
public static class <#= className #>
{
// ReSharper disable InconsistentNaming
<# foreach (var proc in procs) { #>
public static Proc <#= GetDistinctProcName(proc) #>(<#= GetProcArgs(proc) #>)
{
var prms = new DynamicParameters();
<# foreach (var prm in proc.Params) { #>
<# if (prm.IsOutput) { #>
prms.Add("<#= prm.Name #>", direction: ParameterDirection.Output);
<# } else { #>
prms.Add("<#= prm.Name #>", <#= prm.Name.Replace("@", "") #>);
<# } #>
<# } #>
prms.Add("@result", direction: ParameterDirection.ReturnValue);
return new Proc {
Name = "[<#= proc.Schema + "].[" + proc.Name #>]",
Params = prms,
};
}
<# } #>
// ReSharper restore InconsistentNaming
};
}
<#+
private class Proc
{
public string Schema { get; set; }
public string Name { get; set; }
public string Type { get; set; }
public List<Param> Params { get; set; }
};
private class Param
{
public int Position { get; set; }
public string Name { get; set; }
public string SqlType { get; set; }
public int MaxLength { get; set; }
public bool IsOutput { get; set; }
public bool IsNullable { get; set; }
public bool HasDefaultValue { get; set; }
public Type ClrType { get; set; }
}
private static List<Proc> GetProcs(string cs, List<string> whitelist)
{
var procs = new Dictionary<string, Proc>();
var sql = @"
--REF: http://www.mssqltips.com/sqlservertip/1669/generate-a-parameter-list-for-all-sql-server-stored-procedures-and-functions/
SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema],
so.name AS [ProcName],
so.type AS [ProcType],
sp.parameter_id AS [ParameterID],
sp.name AS [ParameterName],
TYPE_NAME(sp.user_type_id) AS [ParameterDataType],
sp.max_length AS [ParameterMaxLength],
sp.is_output AS [IsOutput],
sp.has_default_value AS [HasDefaultValue]
FROM sys.objects AS so
INNER JOIN sys.parameters AS sp ON so.OBJECT_ID = sp.OBJECT_ID
WHERE so.OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE TYPE IN ('P')) --,'FN'
ORDER BY [Schema], so.name, sp.parameter_id
";
var rows = ReadRows(cs, sql);
foreach (var row in rows)
{
var name = (row.Schema == "dbo" ? "" : row.Schema + "_") + row.ProcName;
if (whitelist != null && !whitelist.Contains(name)) {
continue;
}
if (!procs.ContainsKey(name))
{
procs[name] = new Proc
{
Schema = row.Schema,
Name = row.ProcName,
Type = row.ProcType,
Params = new List<Param>(),
};
}
var proc = procs[name];
proc.Params.Add(new Param
{
Position = row.ParameterID,
Name = row.ParameterName,
SqlType = row.ParameterDataType,
MaxLength = row.ParameterMaxLength,
IsNullable = true,
IsOutput = row.IsOutput,
HasDefaultValue = row.HasDefaultValue,
ClrType = GetClrType(row.ParameterDataType),
});
}
return procs.Values.ToList();
}
private static string GetDistinctProcName(Proc proc)
{
return proc.Schema == "dbo" ? proc.Name : proc.Schema + "_" + proc.Name;
}
private static string GetProcArgs(Proc proc)
{
var args = new List<string>();
foreach (var prm in proc.Params)
{
if (prm.IsOutput) continue;
args.Add(GetParamType(prm) + " " + prm.Name.Replace("@", "") + "=null"); //(prm.IsOutput ? "out " : "") +
}
return string.Join(", ", args);
}
private static string GetParamType(Param prm)
{
return prm.ClrType.Name + (prm.ClrType.IsValueType && prm.IsNullable ? "?" : "");
}
private static Type GetClrType(string sqlType)
{
switch (sqlType)
{
case "int":
return typeof(int);
case "char":
case "varchar":
return typeof(string);
case "bit":
return typeof(bool);
case "datetime":
return typeof(DateTime);
default:
throw new Exception("Invalid SqlType: " + sqlType);
}
}
private static IEnumerable<dynamic> ReadRows(string connectionString, string sql)
{
using (var db = new SqlConnection(connectionString))
using (var cmd = db.CreateCommand())
{
db.Open();
cmd.CommandText = sql;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var dyn = new ExpandoObject();
IDictionary<string, object> dict = dyn;
for (var iter = 0; iter < reader.FieldCount; ++iter)
{
dict[reader.GetName(iter)] = reader.GetValue(iter);
}
yield return dyn;
}
}
}
}
private string GetConnectionString(string file, string name)
{
var path = Host.ResolvePath(file);
var map = new ExeConfigurationFileMap { ExeConfigFilename = path };
var config = ConfigurationManager.OpenMappedExeConfiguration(map, ConfigurationUserLevel.None);
return config.ConnectionStrings.ConnectionStrings[name].ConnectionString;
}
#>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment