Skip to content

Instantly share code, notes, and snippets.

@composite

composite/DapperCRUD.cs

Last active November 24, 2015 09:20
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save composite/99ab3f386192399dbac2 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save composite/99ab3f386192399dbac2 to your computer and use it in GitHub Desktop.
Download ZIP
Dapper My CRUD extension
Raw
DapperCRUD.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text;
using Dapper;
namespace Dapper
{
/// <summary>
/// Dapper CRUD 단순확장
/// </summary>
public static class DapperExtensions
{
/// <summary>
/// 엔티티 캐시 접두어
/// </summary>
private const string PROPCACHE_PRIFIX = "DapperExtensions$PROPS!";
/// <summary>
/// 테이블명 캐시 접두어
/// </summary>
private const string TBLCACHE_PREFIX = "DapperExtensions$TBLNM!";
/// <summary>
/// 기타 DB 대응전략 사용 (즉, 미사용)
/// </summary>
public static readonly SQLProcStrategy GenericStrategy = new GenericSQLStrategy();
/// <summary>
/// MS SQL Server에 대한 대응전략 사용
/// </summary>
public static readonly SQLProcStrategy SQLServerStrategy = new MSSQLStrategy();
/// <summary>
/// 공통 DB에서 수용 가능한 최소 시각 (유닉스 시각 기준)
/// </summary>
public static readonly DateTime DBMinDateTime = new DateTime(1970, 1, 1);
public static SQLProcStrategy GetIDStrategy { get; set; }
/// <summary>
/// 엔티티에 대한 캐시 전략 설정 또는 가져오기
/// </summary>
public static IEntityCacheStrategy EntityCacheStrategy { get; set; }
static DapperExtensions()
{
GetIDStrategy = SQLServerStrategy;
}
/// <summary>
/// 단일 행 가져오기 (Key 필요)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="entityToWhere"></param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <returns></returns>
public static T Get<T>(this IDbConnection connection, object entityToWhere, IDbTransaction tran = null, int? timeout = null) where T : class, new()
{
if(entityToWhere == null) throw new ArgumentNullException("entityToWhere");
var name = GetTableNameFromType(typeof(T));
var sb = new StringBuilder("select ");
BuildSelectColumns(typeof(T), sb);
sb.AppendFormat(" from {0} ", name);
sb.Append(" where ");
if (entityToWhere.GetType().IsPrimitive || entityToWhere is string)
{
var prop = GetIdProperties(typeof (T)).Select(p => p.Key).FirstOrDefault();
if (prop != null)
{
var attrs = prop.GetCustomAttributes(typeof(ColumnAttribute), false);
string colname = attrs.Any() ? ((ColumnAttribute)attrs[0]).Name : prop.Name;
sb.AppendFormat(" {0} = @entityToWhere ", colname);
}else throw new InvalidOperationException("Determined type " + typeof(T).FullName + " Must have 1 Key Attribute.");
entityToWhere = new {entityToWhere};
}
else BuildWhere(sb, GetIdProperties(entityToWhere));
return connection.Query<T>(sb.ToString(), entityToWhere, tran, true, timeout).FirstOrDefault();
//using (var reader = connection.ExecuteReader(sb.ToString(), entityToWhere, tran, null)) return BuildFromSingleReader<T>(reader);
}
/// <summary>
/// 객체 파라미터 기반의 조회
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="entityToWhere"></param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <param name="buffer"></param>
/// <returns></returns>
public static IEnumerable<T> GetList<T>(this IDbConnection connection, object entityToWhere, IDbTransaction tran = null, int? timeout = null, bool buffer = true) where T : class, new()
{
var name = GetTableNameFromType(typeof(T));
var sb = new StringBuilder("select ");
BuildSelectColumns(typeof(T), sb);
sb.AppendFormat(" from {0} ", name);
if (entityToWhere != null)
{
if(entityToWhere.GetType().IsPrimitive || entityToWhere is string) throw new ArgumentException("entityToWhere cannot be Primitive or string type.");
sb.Append(" where ");
BuildWhere(sb, GetAllProperties(entityToWhere));
}
return connection.Query<T>(sb.ToString(), entityToWhere, tran, buffer, timeout);
//using (var reader = connection.ExecuteReader(sb.ToString(), entityToWhere)) return BuildFromMultipleReader<T>(reader);
}
/// <summary>
/// 문자열 조건 기반 조회
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="where"></param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <returns></returns>
public static IEnumerable<T> GetList<T>(this IDbConnection connection, string where, IDbTransaction tran = null, int? timeout = null, bool buffer = true) where T : class, new()
{
var name = GetTableNameFromType(typeof(T));
var sb = new StringBuilder("select ");
BuildSelectColumns(typeof(T), sb);
sb.AppendFormat(" from {0} ", name);
if (!string.IsNullOrEmpty(where))
{
sb.Append(" where ");
sb.Append(where);
}
return connection.Query<T>(sb.ToString(), null, tran, buffer, timeout);
//using (var reader = connection.ExecuteReader(sb.ToString())) return BuildFromMultipleReader<T>(reader);
}
/// <summary>
/// 전체 조회
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <param name="buffer"></param>
/// <returns></returns>
public static IEnumerable<T> GetList<T>(this IDbConnection connection, IDbTransaction tran = null, int? timeout = null, bool buffer = true) where T : class, new()
{
var name = GetTableNameFromType(typeof(T));
var sb = new StringBuilder("select ");
BuildSelectColumns(typeof(T), sb);
sb.AppendFormat(" from {0} ", name);
return connection.Query<T>(sb.ToString(), null, tran, buffer, timeout);
//using (var reader = connection.ExecuteReader(sb.ToString())) return BuildFromMultipleReader<T>(reader);
}
/// <summary>
/// 객체 조건 기반의 행 개수
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="entityToWhere"></param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <returns></returns>
public static int GetCount<T>(this IDbConnection connection, object entityToWhere, IDbTransaction tran = null, int? timeout = null) where T : class, new()
{
var name = GetTableNameFromType(typeof(T));
var sb = new StringBuilder();
sb.AppendFormat("select COUNT(*) from {0}", name);
if (entityToWhere != null)
{
if (entityToWhere.GetType().IsPrimitive || entityToWhere is string) throw new ArgumentException("entityToWhere cannot be Primitive or string type.");
sb.Append(" where ");
BuildWhere(sb, GetAllProperties(entityToWhere));
}
return connection.ExecuteScalar<int>(sb.ToString(), entityToWhere);
}
/// <summary>
/// 단순 전체 행 개수
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <returns></returns>
public static int GetCount<T>(this IDbConnection connection, IDbTransaction tran = null, int? timeout = null) where T : class, new()
{
var name = GetTableNameFromType(typeof(T));
var sb = new StringBuilder();
sb.AppendFormat("select COUNT(*) from {0}", name);
return connection.ExecuteScalar<int>(sb.ToString());
}
/// <summary>
/// 리스트가 있는 프로시저 실행
/// (오라클 주의 : OUT REF CURSOR 변수 정의가 필요하며 현재 하나의 커서만 인식 가능.)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="procname"></param>
/// <param name="objectParams"></param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <param name="buffer"></param>
/// <returns></returns>
public static IEnumerable<T> GetProc<T>(this IDbConnection connection, string procname, object objectParams = null, IDbTransaction tran = null, int? timeout = null, bool buffer = true) where T : class, new()
{
if (!buffer) return GetProcWithoutBuffer<T>(connection, procname, objectParams, tran, timeout);
IList<T> list = new List<T>(0);
using (var reader = connection.ExecuteReader(procname, objectParams, tran, timeout, CommandType.StoredProcedure))
{
if (!reader.IsClosed)
{
var props = GetAllProperties(typeof(T));
IDictionary<int, PropertyInfo> affects = new Dictionary<int, PropertyInfo>();
for (int i = 0; i < reader.FieldCount; i++)
{
string colname = reader.GetName(i);
var prop = props.Where(p => colname == p.Value).Select(p => p.Key).FirstOrDefault();
if (prop != null) affects.Add(i, prop);
}
while (reader.Read())
{
T result = new T();
foreach (int i in affects.Keys)
affects[i].SetValue(result, affects[i].PropertyType == typeof(string) ? Convert.ToString(reader.GetValue(i)) : reader.GetValue(i), null);
list.Add(result);
}
}
}
return list;
}
/// <summary>
/// 리스트가 있는 프로시저 실행 (버퍼 없이 실행하기 때문에 커넥션 외부에서 수행 불가능)
/// (오라클 주의 : OUT REF CURSOR 변수 정의가 필요하며 현재 하나의 커서만 인식 가능.)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="procname"></param>
/// <param name="objectParams"></param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <returns></returns>
private static IEnumerable<T> GetProcWithoutBuffer<T>(IDbConnection connection, string procname, object objectParams = null, IDbTransaction tran = null, int? timeout = null) where T : class, new()
{
using (var reader = connection.ExecuteReader(procname, objectParams, tran, timeout, CommandType.StoredProcedure))
{
if (!reader.IsClosed)
{
var props = GetAllProperties(typeof(T));
IDictionary<int, PropertyInfo> affects = new Dictionary<int, PropertyInfo>();
for (int i = 0; i < reader.FieldCount; i++)
{
string colname = reader.GetName(i);
var prop = props.Where(p => colname == p.Value).Select(p => p.Key).FirstOrDefault();
if (prop != null) affects.Add(i, prop);
}
while (reader.Read())
{
T result = new T();
foreach (int i in affects.Keys)
affects[i].SetValue(result, affects[i].PropertyType == typeof(string) ? Convert.ToString(reader.GetValue(i)) : reader.GetValue(i), null);
yield return result;
}
}
}
}
/// <summary>
/// 단순 Insert. PK 불필요
/// </summary>
/// <param name="connection">DB 커넥션</param>
/// <param name="entityToInsert">삽입할 엔티티 객체. 익명 객체 불허.</param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <returns></returns>
public static void Insert<T>(this IDbConnection connection, T entityToInsert, IDbTransaction tran = null, int? timeout = null)
{
var name = GetTableNameFromType(typeof(T));
var sb = new StringBuilder();
sb.AppendFormat("insert into {0}", name);
sb.Append(" (");
BuildInsertParameters(entityToInsert, sb);
sb.Append(") values (");
BuildInsertValues(entityToInsert, sb);
sb.Append(")");
//속성별로 값 재정의 및 DateTime 기본값 설정 방지
foreach (var p in GetNonIdProperties(entityToInsert))
{
if (p.Key.PropertyType == typeof (DateTime))
{
DateTime val = (DateTime)p.Key.GetValue(entityToInsert, null);
if (val < DBMinDateTime) p.Key.SetValue(entityToInsert, DBMinDateTime, null);
}
else if (p.Key.PropertyType == typeof (DateTime?))
{
DateTime? val = (DateTime?)p.Key.GetValue(entityToInsert, null);
if (val.HasValue && val.Value < DBMinDateTime) p.Key.SetValue(entityToInsert, DBMinDateTime, null);
}
}
GetIDStrategy.InsertWithID(connection, sb, entityToInsert);
}
/// <summary>
/// 단순 UPDATE (PK 필요)
/// </summary>
/// <param name="connection">DB 커넥션</param>
/// <param name="entityToUpdate">업데이트할 엔티티 객체, 익명 객체 불허</param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <returns></returns>
public static int Update<T>(this IDbConnection connection, T entityToUpdate, IDbTransaction tran = null, int? timeout = null)
{
var idProps = GetIdProperties(entityToUpdate);
if (!idProps.Any())
throw new ArgumentException("Entity must have at least one [Key] property");
var name = GetTableNameFromType(typeof(T));
var sb = new StringBuilder();
sb.AppendFormat("update {0}", name);
sb.AppendFormat(" set ");
BuildUpdateSet(entityToUpdate, sb);
sb.Append(" where ");
BuildWhere(sb, idProps);
return connection.Execute(sb.ToString(), entityToUpdate);
}
/// <summary>
/// 단순 UPDATE (PK 불필요, 멀티)
/// </summary>
/// <param name="connection">DB 커넥션</param>
/// <param name="entityToUpdate">업데이트할 엔티티 객체, 익명 객체 불허</param>
/// <param name="entityToWhere">업데이트 조건문 객체 혹은 Where 절 문자열</param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <returns></returns>
public static int Update<T>(this IDbConnection connection, T entityToUpdate, object entityToWhere, IDbTransaction tran = null, int? timeout = null)
{
var name = GetTableNameFromType(typeof(T));
var sb = new StringBuilder();
sb.AppendFormat("update {0}", name);
sb.AppendFormat(" set ");
BuildUpdateSet(entityToUpdate, sb);
sb.Append(" where ");
if (entityToWhere is string)
sb.Append(entityToWhere);
else BuildWhere(sb, GetAllProperties(entityToWhere));
return connection.Execute(sb.ToString(), entityToUpdate);
}
/// <summary>
/// 단순 DELETE (PK 필요 또는 익명 객체 허용)
/// </summary>
/// <typeparam name="T">엔티티 타입</typeparam>
/// <param name="connection">DB 커넥션</param>
/// <param name="entityToDelete">삭제할 엔티티 또는 객체</param>
/// <param name="tran"></param>
/// <param name="timeout"></param>
/// <returns></returns>
public static int Delete<T>(this IDbConnection connection, object entityToDelete, IDbTransaction tran = null, int? timeout = null) where T : class
{
var idProps = entityToDelete.GetType().IsAnonymousType() ?
GetAllProperties(entityToDelete) :
GetIdProperties(entityToDelete);
if (!entityToDelete.GetType().IsAnonymousType() && !idProps.Any())
throw new ArgumentException("Entity must have at least one [Key] property");
var name = GetTableNameFromType(typeof(T));
var sb = new StringBuilder();
sb.AppendFormat("delete from {0}", name);
sb.Append(" where ");
BuildWhere(sb, idProps);
return connection.Execute(sb.ToString(), entityToDelete);
}
private static void BuildUpdateSet(object entityToUpdate, StringBuilder sb)
{
var nonIdProps = GetNonIdProperties(entityToUpdate);
for (var i = 0; i < nonIdProps.Count(); i++)
{
var property = nonIdProps.ElementAt(i);
sb.AppendFormat("{0} = @{1}", GetIDStrategy.ColumnSafety(property.Value), property.Key.Name);
if (i < nonIdProps.Count() - 1)
sb.AppendFormat(", ");
}
}
private static void BuildSelectColumns(object entityToInsert, StringBuilder sb)
{
var props = GetAllProperties(entityToInsert);
for (var i = 0; i < props.Count(); i++)
{
var property = props.ElementAt(i);
sb.AppendFormat("{0} AS {1}", property.Value, GetIDStrategy.ColumnSafety(property.Key.Name));
if (i < props.Count() - 1)
sb.Append(", ");
}
}
private static void BuildWhere(StringBuilder sb, IDictionary<PropertyInfo, string> idProps)
{
for (var i = 0; i < idProps.Count(); i++)
{
var property = idProps.ElementAt(i);
sb.AppendFormat("{0} = @{1}", GetIDStrategy.ColumnSafety(property.Value), property.Key.Name);
if (i < idProps.Count() - 1)
sb.AppendFormat(" and ");
}
}
private static void BuildInsertValues(object entityToInsert, StringBuilder sb)
{
var props = GetNonIdProperties(entityToInsert).Where(p => !p.Key.GetCustomAttributes(typeof(DoNotInsertAttribute), false).Any());
for (var i = 0; i < props.Count(); i++)
{
var property = props.ElementAt(i).Key;
sb.AppendFormat("@{0}", property.Name);
if (i < props.Count() - 1)
sb.Append(", ");
}
}
private static void BuildInsertParameters(object entityToInsert, StringBuilder sb)
{
var props = GetNonIdProperties(entityToInsert).Where(p => !p.Key.GetCustomAttributes(typeof(DoNotInsertAttribute), false).Any());
for (var i = 0; i < props.Count(); i++)
{
sb.Append(props.ElementAt(i).Value);
if (i < props.Count() - 1)
sb.Append(", ");
}
}
private static string GetTableNameFromType(object entity)
{
if(entity == null) throw new ArgumentNullException("entity");
var type = entity is Type ? ((Type)entity) : entity.GetType();
if(type.IsAnonymousType()) throw new InvalidCastException("Anonymous type cannot take the table name.");
return GetIDStrategy.ColumnSafety(
EntityCacheStrategy.Cache(
TBLCACHE_PREFIX + type.AssemblyQualifiedName,
type.GetCustomAttributes(typeof (TableAttribute), false).Select(a => ((TableAttribute) a).Name).FirstOrDefault() ?? type.Name
)
);
}
private static IDictionary<PropertyInfo, string> GetAllProperties(object entity)
{
Type type = (entity is Type ? ((Type) entity) : entity.GetType());
string chname = PROPCACHE_PRIFIX + type.AssemblyQualifiedName;
return EntityCacheStrategy.Cache(chname,
type.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(p => !p.GetCustomAttributes(typeof(IgnoreColumnAttribute), false).Any() && (p.PropertyType.IsSubclassOf(typeof(ValueType)) || p.PropertyType == typeof(string)))
.ToDictionary(p => p, p =>
{
var attrs = p.GetCustomAttributes(typeof(ColumnAttribute), false);
return attrs.Any() ? ((ColumnAttribute)attrs[0]).Name : p.Name;
})
);
}
private static IDictionary<PropertyInfo, string> GetNonIdProperties(object entity)
{
return GetAllProperties(entity).Where(p => !p.Key.GetCustomAttributes(false).Any(a => a is KeyAttribute)).ToDictionary(kv => kv.Key, kv => kv.Value);
}
private static IDictionary<PropertyInfo, string> GetIdProperties(object entity)
{
return GetAllProperties(entity).Where(p => p.Key.GetCustomAttributes(false).Any(a => a is KeyAttribute)).ToDictionary(kv => kv.Key, kv => kv.Value);
}
}
public static class TypeExtension
{
public static bool IsAnonymousType(this Type type)
{
if (type == null) return false;
var hasCompilerGeneratedAttribute = type.GetCustomAttributes(typeof(CompilerGeneratedAttribute), false).Any();
var nameContainsAnonymousType = type.FullName.Contains("AnonymousType");
var isAnonymousType = hasCompilerGeneratedAttribute && nameContainsAnonymousType;
return isAnonymousType;
}
public static bool IsNumericType(this Type type)
{
if (type == null) throw new ArgumentNullException("type");
switch (Type.GetTypeCode(type))
{
case TypeCode.Byte:
case TypeCode.Decimal:
case TypeCode.Double:
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.SByte:
case TypeCode.Single:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
return true;
case TypeCode.Object:
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
{
return IsNumericType(Nullable.GetUnderlyingType(type));
}
return false;
}
return false;
}
public static bool IsInteger(this Type type)
{
if (type == null) throw new ArgumentNullException("type");
switch (Type.GetTypeCode(type))
{
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64:
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.UInt64:
return true;
case TypeCode.Object:
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
{
return IsInteger(Nullable.GetUnderlyingType(type));
}
return false;
}
return false;
}
}
/// <summary>
/// 이 클래스는 테이블 기반의 POCO
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
public class TableAttribute : Attribute
{
public string Name { get; private set; }
public string Schema { get; private set; }
public TableAttribute(string name)
{
this.Name = name;
}
public TableAttribute(string name, string schema) : this(name)
{
this.Schema = schema;
}
}
/// <summary>
/// 이 속성이나 필드명은 명명된 칼럼명이 있음.
/// </summary>
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)]
public class ColumnAttribute : Attribute
{
public string Name { get; private set; }
public ColumnAttribute(string name)
{
this.Name = name;
}
}
/// <summary>
/// 이 속성이나 필드명은 주 키로 정의됨.
/// </summary>
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)]
public class KeyAttribute : Attribute { }
/// <summary>
/// 이 속성이나 필드명은 자동 증가 식을 가지고 있음.
/// </summary>
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)]
public class AutoKeyAttribute : Attribute { }
/// <summary>
/// 이 속성이나 필드명은 CRUD 칼럼 대상에서 반드시 제외 대상임.
/// </summary>
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)]
public class IgnoreColumnAttribute : Attribute { }
/// <summary>
/// 이 속성이나 필드명은 INSERT 시 DB에서 유동적으로 처리하기 위해 제외 대상임.
/// </summary>
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)]
public class DoNotInsertAttribute : Attribute { }
/// <summary>
/// 엔티티 요소에 대한 캐시 전략 정의
/// </summary>
public interface IEntityCacheStrategy
{
/// <summary>
/// 캐시 생명주기
/// </summary>
TimeSpan LifeTime { get; }
/// <summary>
/// 캐시로부터 가져오거나 없을 경우 생성 후 가져오기
/// </summary>
/// <typeparam name="T">요소 타입</typeparam>
/// <param name="name">요소명</param>
/// <param name="entity">없을 경우 새 요소</param>
/// <returns></returns>
T Cache<T>(string name, T entity);
}
/// <summary>
/// 캐시를 수행하지 않을 때 사용하는 캐시 전략 정의
/// </summary>
public sealed class NoEntityCacheStrategy: IEntityCacheStrategy
{
public TimeSpan LifeTime { get { return TimeSpan.Zero; } }
public T Cache<T>(string name, T entity)
{
return entity;
}
}
/// <summary>
/// 각 SQL별 표현 전략 정의
/// </summary>
public interface SQLProcStrategy
{
/// <summary>
/// INSERT 시 새로운 키값을 부여받아 클래스에 부여
/// </summary>
/// <param name="conn"></param>
/// <param name="sqlsb"></param>
/// <param name="entity"></param>
void InsertWithID(IDbConnection conn, StringBuilder sqlsb, object entity);
/// <summary>
/// 쿼리에 칼럼명 표현 시 예약어로 인한 오류를 방지하기 위해 안전하게 처리하는 방안
/// </summary>
/// <param name="original"></param>
/// <returns></returns>
string ColumnSafety(string original);
}
/// <summary>
/// 기타 SQL 표현전략 : 할 거 없음
/// </summary>
public sealed class GenericSQLStrategy : SQLProcStrategy
{
public void InsertWithID(IDbConnection conn, StringBuilder sqlsb, object entity)
{
conn.Execute(sqlsb.ToString(), entity);
}
public string ColumnSafety(string original)
{
return original;
}
}
/// <summary>
/// MS SQL Server를 위한 표현전략 정의
/// </summary>
public class MSSQLStrategy : SQLProcStrategy
{
public void InsertWithID(IDbConnection conn, StringBuilder sqlsb, object entity)
{
if(!(conn is SqlConnection)) throw new InvalidOperationException("Connection is not SQL Server. Please change ID Strategy or create new Strategy.");
Type type = entity.GetType();
var props = type.GetProperties();
//AutoInc 우선검색 -> AutoInc 가능한 숫자형 PK 찾기...
var prop = props.Where(p => p.GetCustomAttributes(typeof (AutoKeyAttribute), false).Any()).Take(1)
.Union(props.Where(p => p.GetCustomAttributes(typeof (KeyAttribute), false).Any() && p.PropertyType.IsInteger()).Take(1)).FirstOrDefault();
if (prop == null) conn.Execute(sqlsb.ToString(), entity);
else
{
sqlsb.Append(" SELECT SCOPE_IDENTITY() "); // SCOPE_IDENITY는 numeric(32, 0), 즉, .NET에서는 decimal로 취급.
switch (Type.GetTypeCode(prop.PropertyType))
{
case TypeCode.Int16: prop.SetValue(entity, (short)Math.Truncate((decimal)conn.ExecuteScalar(sqlsb.ToString(), entity)) , null); break;
case TypeCode.Int32: prop.SetValue(entity, (int)Math.Truncate((decimal)conn.ExecuteScalar(sqlsb.ToString(), entity)), null); break;
case TypeCode.Int64: prop.SetValue(entity, (long)Math.Truncate((decimal)conn.ExecuteScalar(sqlsb.ToString(), entity)), null); break;
case TypeCode.UInt16: prop.SetValue(entity, (ushort)Math.Truncate((decimal)conn.ExecuteScalar(sqlsb.ToString(), entity)), null); break;
case TypeCode.UInt32: prop.SetValue(entity, (uint)Math.Truncate((decimal)conn.ExecuteScalar(sqlsb.ToString(), entity)), null); break;
case TypeCode.UInt64: prop.SetValue(entity, (ulong)Math.Truncate((decimal)conn.ExecuteScalar(sqlsb.ToString(), entity)), null); break;
case TypeCode.Double: prop.SetValue(entity, (double)Math.Truncate((decimal)conn.ExecuteScalar(sqlsb.ToString(), entity)), null); break;
case TypeCode.Single: prop.SetValue(entity, (float)Math.Truncate((decimal)conn.ExecuteScalar(sqlsb.ToString(), entity)), null); break;
default: prop.SetValue(entity, conn.ExecuteScalar(sqlsb.ToString(), entity), null); break;
}
}
}
public string ColumnSafety(string original)
{
return "[" + original + "]";
}
}
}
Raw
ModelGen.tt
<#@ template hostspecific="True" #>
<#@ assembly name="EnvDTE" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Data.Entity.Design" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Configuration" #>
<#@ assembly name="System.Windows.Forms" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Data.Common" #>
<#@ import namespace="System.Diagnostics" #>
<#@ import namespace="System.Globalization" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#@ import namespace="System.Configuration" #>
<#@ import namespace="System.Windows.Forms" #>
<#
/*
Based on https://github.com/ericdc1/Dapper.SimpleCRUD/wiki/T4-Template
This code is part of the Dapper.SimpleCRUD project
It is based on the T4 template from the PetaPoco project which in turn is based on the subsonic project.
-----------------------------------------------------------------------------------------
This template can read minimal schema information from the following databases:
* SQL Server
-----------------------------------------------------------------------------------------
*/
// Settings
ConnectionStringName = "SampleconnectionString"; // Uses last connection string in config if not specified
ConfigPath = @""; //Looks in current project for web.config or app.config by default. This overrides to a relative path - useful for seperate class library projects.
Namespace = "MyProgram.Model";
ClassPrefix = "";
ClassSuffix = "";
IncludeViews = false;
IncludeRelationships = true;
ExcludeTablePrefixes = new string[]{"aspnet_","webpages_"};
// Read schema
var tables = LoadTables();
/*
// Tweak Schema
tables["tablename"].Ignore = true; // To ignore a table
tables["tablename"].ClassName = "newname"; // To change the class name of a table
tables["tablename"]["columnname"].Ignore = true; // To ignore a column
tables["tablename"]["columnname"].PropertyName="newname"; // To change the property name of a column
tables["tablename"]["columnname"].PropertyType="bool"; // To change the property type of a column
*/
#>
using System;
using System.Collections.Generic;
using Dapper;
namespace <#=Namespace #>
{
<#
foreach(Table tbl in from t in tables where !t.Ignore select t){
if(IsExcluded(tbl.Name, ExcludeTablePrefixes)) continue;
#>
/// <summary>
/// A class which represents the <#=tbl.Name#> <#=(tbl.IsView)?"view":"table"#>.
/// </summary>
[Table("<#=tbl.Name#>")]
public partial class <#=tbl.ClassName#>
{
<#foreach(Column col in from c in tbl.Columns where !c.Ignore select c)
{#>
[Column("<#=col.Name #>")]
<# if (tbl.PK!=null && tbl.PK.Name==col.Name) { #>
[Key]
<#}#>
public virtual <#=col.PropertyType #><#=CheckNullable(col)#> <#=col.PropertyName #> { get; set; }
<#}#>
<# if (IncludeRelationships) { #>
<#foreach(Key key in from k in tbl.OuterKeys select k)
{#>
public virtual <#=tables[key.ReferencedTableName].ClassName #> <#=tables[key.ReferencedTableName].ClassName #> { get; set; }
<#}#>
<#foreach(Key key in from k in tbl.InnerKeys select k)
{#>
public virtual IEnumerable<<#=tables[key.ReferencingTableName].ClassName #>> <#=tables[key.ReferencingTableName].CleanName #> { get; set; }
<#}#>
<#}#>
}
<#}#>
}
<#+
/*
The contents of this file are subject to the New BSD
License (the "License"); you may not use this file
except in compliance with the License. You may obtain a copy of
the License at http://www.opensource.org/licenses/bsd-license.php
Software distributed under the License is distributed on an
"AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
implied. See the License for the specific language governing
rights and limitations under the License.
*/
string ConnectionStringName = "";
string ConfigPath = "";
string Namespace = "";
string ClassPrefix = "";
string ClassSuffix = "";
string SchemaName = null;
bool IncludeViews;
bool IncludeRelationships;
string[] ExcludeTablePrefixes = new string[]{};
public class Table
{
public List<Column> Columns;
public List<Key> InnerKeys = new List<Key>();
public List<Key> OuterKeys = new List<Key>();
public string Name;
public string Schema;
public bool IsView;
public string CleanName;
public string ClassName;
public string SequenceName;
public bool Ignore;
public Column PK
{
get
{
return this.Columns.SingleOrDefault(x=>x.IsPK);
}
}
public Column GetColumn(string columnName)
{
return Columns.Single(x=>string.Compare(x.Name, columnName, true)==0);
}
public Column this[string columnName]
{
get
{
return GetColumn(columnName);
}
}
}
public class Column
{
public string Name;
public string PropertyName;
public string PropertyType;
public bool IsPK;
public bool IsNullable;
public bool IsAutoIncrement;
public bool Ignore;
}
public class Key
{
public string Name;
public string ReferencedTableName;
public string ReferencedTableColumnName;
public string ReferencingTableName;
public string ReferencingTableColumnName;
}
public class Tables : List<Table>
{
public Tables()
{
}
public Table GetTable(string tableName)
{
return this.Single(x=>string.Compare(x.Name, tableName, true)==0);
}
public Table this[string tableName]
{
get
{
return GetTable(tableName);
}
}
}
static Regex rxCleanUp = new Regex(@"[^\w\d_]", RegexOptions.Compiled);
static Func<string, string> CleanUp = (str) =>
{
str = rxCleanUp.Replace(str, "_");
if (char.IsDigit(str[0])) str = "_" + str;
return str;
};
string CheckNullable(Column col)
{
string result="";
if(col.IsNullable &&
col.PropertyType !="byte[]" &&
col.PropertyType !="string" &&
col.PropertyType !="Microsoft.SqlServer.Types.SqlGeography" &&
col.PropertyType !="Microsoft.SqlServer.Types.SqlGeometry"
)
result="?";
return result;
}
string GetConnectionString(ref string connectionStringName, out string providerName)
{
var _CurrentProject = GetCurrentProject();
providerName=null;
string result="";
ExeConfigurationFileMap configFile = new ExeConfigurationFileMap();
configFile.ExeConfigFilename = GetConfigPath();
if (string.IsNullOrEmpty(configFile.ExeConfigFilename))
throw new ArgumentNullException("The project does not contain App.config or Web.config file.");
var config = System.Configuration.ConfigurationManager.OpenMappedExeConfiguration(configFile, ConfigurationUserLevel.None);
var connSection=config.ConnectionStrings;
//if the connectionString is empty - which is the defauls
//look for count-1 - this is the last connection string
//and takes into account AppServices and LocalSqlServer
if(string.IsNullOrEmpty(connectionStringName))
{
if(connSection.ConnectionStrings.Count>1)
{
connectionStringName = connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].Name;
result=connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].ConnectionString;
providerName=connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].ProviderName;
}
}
else
{
try
{
result=connSection.ConnectionStrings[connectionStringName].ConnectionString;
providerName=connSection.ConnectionStrings[connectionStringName].ProviderName;
}
catch
{
result="There is no connection string name called '"+connectionStringName+"'";
}
}
// if (String.IsNullOrEmpty(providerName))
// providerName="System.Data.SqlClient";
return result;
}
string _connectionString="";
string _providerName="";
void InitConnectionString()
{
if(String.IsNullOrEmpty(_connectionString))
{
_connectionString=GetConnectionString(ref ConnectionStringName, out _providerName);
if(_connectionString.Contains("|DataDirectory|"))
{
//have to replace it
string dataFilePath=GetDataDirectory();
_connectionString=_connectionString.Replace("|DataDirectory|",dataFilePath);
}
}
}
public string ConnectionString
{
get
{
InitConnectionString();
return _connectionString;
}
}
public string ProviderName
{
get
{
InitConnectionString();
return _providerName;
}
}
public EnvDTE.Project GetCurrentProject() {
IServiceProvider _ServiceProvider = (IServiceProvider)Host;
if (_ServiceProvider == null)
throw new Exception("Host property returned unexpected value (null)");
EnvDTE.DTE dte = (EnvDTE.DTE)_ServiceProvider.GetService(typeof(EnvDTE.DTE));
if (dte == null)
throw new Exception("Unable to retrieve EnvDTE.DTE");
Array activeSolutionProjects = (Array)dte.ActiveSolutionProjects;
if (activeSolutionProjects == null)
throw new Exception("DTE.ActiveSolutionProjects returned null");
EnvDTE.Project dteProject = (EnvDTE.Project)activeSolutionProjects.GetValue(0);
if (dteProject == null)
throw new Exception("DTE.ActiveSolutionProjects[0] returned null");
return dteProject;
}
private string GetProjectPath()
{
EnvDTE.Project project = GetCurrentProject();
System.IO.FileInfo info = new System.IO.FileInfo(project.FullName);
return info.Directory.FullName;
}
private string GetConfigPath()
{
if(ConfigPath !="")
return Host.ResolvePath(ConfigPath);
EnvDTE.Project project = GetCurrentProject();
foreach (EnvDTE.ProjectItem item in project.ProjectItems)
{
// if it is the app.config file, then open it up
if (item.Name.Equals("App.config",StringComparison.InvariantCultureIgnoreCase) || item.Name.Equals("Web.config",StringComparison.InvariantCultureIgnoreCase))
return GetProjectPath() + "\\" + item.Name;
}
return String.Empty;
}
public string GetDataDirectory()
{
EnvDTE.Project project=GetCurrentProject();
return System.IO.Path.GetDirectoryName(project.FileName)+"\\App_Data\\";
}
static string zap_password(string connectionString)
{
var rx = new Regex("Password=.*;", RegexOptions.Singleline | RegexOptions.Multiline | RegexOptions.IgnoreCase);
return rx.Replace(connectionString, "Password=******;");
}
static string Singularize(string word)
{
return word.Split(new [] {"_"}, StringSplitOptions.RemoveEmptyEntries).Select(s => char.ToUpperInvariant(s[0]) + s.Substring(1, s.Length - 1)).Aggregate(string.Empty, (s1, s2) => s1 + s2);
}
static string RemoveTablePrefixes(string word)
{
var cleanword = word;
if(cleanword.StartsWith("tbl_")) cleanword = cleanword.Replace("tbl_","");
if(cleanword.StartsWith("tbl")) cleanword = cleanword.Replace("tbl","");
cleanword = cleanword.Replace("_","");
return cleanword;
}
static bool IsExcluded(string tablename, string[] ExcludeTablePrefixes)
{
for (int i = 0; i < ExcludeTablePrefixes.Length; i++)
{
string s = ExcludeTablePrefixes[i];
if(tablename.StartsWith(s)) return true;
}
return false;
}
Tables LoadTables()
{
InitConnectionString();
WriteLine("// This file was automatically generated by the Dapper.SimpleCRUD T4 Template");
WriteLine("// Do not make changes directly to this file - edit the template instead");
WriteLine("// ");
WriteLine("// The following connection settings were used to generate this file");
WriteLine("// ");
WriteLine("// Connection String Name: `{0}`", ConnectionStringName);
WriteLine("// Provider: `{0}`", ProviderName);
WriteLine("// Connection String: `{0}`", zap_password(ConnectionString));
WriteLine("// Include Views: `{0}`", IncludeViews);
WriteLine("");
DbProviderFactory _factory;
try
{
_factory = DbProviderFactories.GetFactory(ProviderName);
}
catch (Exception x)
{
var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
Warning(string.Format("Failed to load provider `{0}` - {1}", ProviderName, error));
WriteLine("");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("// Failed to load provider `{0}` - {1}", ProviderName, error);
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("");
return new Tables();
}
try
{
Tables result;
using(var conn=_factory.CreateConnection())
{
conn.ConnectionString=ConnectionString;
conn.Open();
SchemaReader reader=null;
// Assume SQL Server
reader=new SqlServerSchemaReader();
reader.outer=this;
result=reader.ReadSchema(conn, _factory);
// Remove unrequired tables/views
for (int i=result.Count-1; i>=0; i--)
{
if (SchemaName!=null && string.Compare(result[i].Schema, SchemaName, true)!=0)
{
result.RemoveAt(i);
continue;
}
if (!IncludeViews && result[i].IsView)
{
result.RemoveAt(i);
continue;
}
}
conn.Close();
var rxClean = new Regex("^(Equals|GetHashCode|GetType|ToString|repo|Save|IsNew|Insert|Update|Delete|Exists|SingleOrDefault|Single|First|FirstOrDefault|Fetch|Page|Query)$");
foreach (var t in result)
{
t.ClassName = ClassPrefix + t.ClassName + ClassSuffix;
foreach (var c in t.Columns)
{
c.PropertyName = rxClean.Replace(c.PropertyName, "_$1");
// Make sure property name doesn't clash with class name
if (c.PropertyName == t.ClassName)
c.PropertyName = "_" + c.PropertyName;
}
}
return result;
}
}
catch (Exception x)
{
var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
Warning(string.Format("Failed to read database schema - {0}", error));
WriteLine("");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("// Failed to read database schema - {0}", error);
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("");
return new Tables();
}
}
abstract class SchemaReader
{
public abstract Tables ReadSchema(DbConnection connection, DbProviderFactory factory);
public GeneratedTextTransformation outer;
public void WriteLine(string o)
{
outer.WriteLine(o);
}
}
class SqlServerSchemaReader : SchemaReader
{
// SchemaReader.ReadSchema
public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
{
var result=new Tables();
_connection=connection;
_factory=factory;
var cmd=_factory.CreateCommand();
cmd.Connection=connection;
cmd.CommandText=TABLE_SQL;
//pull the tables in a reader
using(cmd)
{
using (var rdr=cmd.ExecuteReader())
{
while(rdr.Read())
{
Table tbl=new Table();
tbl.Name=rdr["TABLE_NAME"].ToString();
tbl.Schema=rdr["TABLE_SCHEMA"].ToString();
tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
tbl.CleanName=CleanUp(tbl.Name);
if(tbl.CleanName.StartsWith("tbl_")) tbl.CleanName = tbl.CleanName.Replace("tbl_","");
if(tbl.CleanName.StartsWith("tbl")) tbl.CleanName = tbl.CleanName.Replace("tbl","");
tbl.CleanName = tbl.CleanName.Replace("_","");
tbl.ClassName=Singularize(RemoveTablePrefixes(tbl.CleanName));
result.Add(tbl);
}
}
}
foreach (var tbl in result)
{
tbl.Columns=LoadColumns(tbl);
// Mark the primary key
string PrimaryKey=GetPK(tbl.Name);
var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
if(pkColumn!=null)
{
pkColumn.IsPK=true;
}
try
{
tbl.OuterKeys = LoadOuterKeys(tbl);
tbl.InnerKeys = LoadInnerKeys(tbl);
}
catch (Exception x)
{
var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
WriteLine("");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine(String.Format("// Failed to get relationships for `{0}` - {1}", tbl.Name, error));
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("");
}
}
return result;
}
DbConnection _connection;
DbProviderFactory _factory;
List<Column> LoadColumns(Table tbl)
{
using (var cmd=_factory.CreateCommand())
{
cmd.Connection=_connection;
cmd.CommandText=COLUMN_SQL;
var p = cmd.CreateParameter();
p.ParameterName = "@tableName";
p.Value=tbl.Name;
cmd.Parameters.Add(p);
p = cmd.CreateParameter();
p.ParameterName = "@schemaName";
p.Value=tbl.Schema;
cmd.Parameters.Add(p);
var result=new List<Column>();
using (IDataReader rdr=cmd.ExecuteReader())
{
while(rdr.Read())
{
Column col=new Column();
col.Name=rdr["ColumnName"].ToString();
col.PropertyName=/*Singularize*/(CleanUp(col.Name)/*.ToLower()*/);
col.PropertyType=GetPropertyType(rdr["DataType"].ToString());
col.IsNullable=rdr["IsNullable"].ToString()=="YES";
col.IsAutoIncrement=((int)rdr["IsIdentity"])==1;
result.Add(col);
}
}
return result;
}
}
List<Key> LoadOuterKeys(Table tbl)
{
using (var cmd=_factory.CreateCommand())
{
cmd.Connection=_connection;
cmd.CommandText=OUTER_KEYS_SQL;
var p = cmd.CreateParameter();
p.ParameterName = "@tableName";
p.Value=tbl.Name;
cmd.Parameters.Add(p);
var result=new List<Key>();
using (IDataReader rdr=cmd.ExecuteReader())
{
while(rdr.Read())
{
var key=new Key();
key.Name=rdr["FK"].ToString();
key.ReferencedTableName = rdr["Referenced_tbl"].ToString();
key.ReferencedTableColumnName = rdr["Referenced_col"].ToString();
key.ReferencingTableColumnName = rdr["Referencing_col"].ToString();
result.Add(key);
}
}
return result;
}
}
List<Key> LoadInnerKeys(Table tbl)
{
using (var cmd=_factory.CreateCommand())
{
cmd.Connection=_connection;
cmd.CommandText=INNER_KEYS_SQL;
var p = cmd.CreateParameter();
p.ParameterName = "@tableName";
p.Value=tbl.Name;
cmd.Parameters.Add(p);
var result=new List<Key>();
using (IDataReader rdr=cmd.ExecuteReader())
{
while(rdr.Read())
{
var key=new Key();
key.Name=rdr["FK"].ToString();
key.ReferencingTableName = rdr["Referencing_tbl"].ToString();
key.ReferencedTableColumnName = rdr["Referenced_col"].ToString();
key.ReferencingTableColumnName = rdr["Referencing_col"].ToString();
result.Add(key);
}
}
return result;
}
}
string GetPK(string table){
string sql=@"SELECT c.name AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.objects AS o ON i.object_id = o.object_id
LEFT OUTER JOIN sys.columns AS c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE (i.type = 1) AND (o.name = @tableName)";
using (var cmd=_factory.CreateCommand())
{
cmd.Connection=_connection;
cmd.CommandText=sql;
var p = cmd.CreateParameter();
p.ParameterName = "@tableName";
p.Value=table;
cmd.Parameters.Add(p);
var result=cmd.ExecuteScalar();
if(result!=null)
return result.ToString();
}
return "";
}
string GetPropertyType(string sqlType)
{
string sysType="string";
switch (sqlType)
{
case "bigint":
sysType = "long";
break;
case "smallint":
sysType= "short";
break;
case "int":
sysType= "int";
break;
case "uniqueidentifier":
sysType= "Guid";
break;
case "smalldatetime":
case "datetime":
case "datetime2":
case "date":
case "time":
sysType= "DateTime";
break;
case "float":
sysType="double";
break;
case "real":
sysType="float";
break;
case "numeric":
case "smallmoney":
case "decimal":
case "money":
sysType= "decimal";
break;
case "tinyint":
sysType = "byte";
break;
case "bit":
sysType= "bool";
break;
case "image":
case "binary":
case "varbinary":
case "timestamp":
sysType= "byte[]";
break;
case "geography":
sysType = "Microsoft.SqlServer.Types.SqlGeography";
break;
case "geometry":
sysType = "Microsoft.SqlServer.Types.SqlGeometry";
break;
}
return sysType;
}
const string TABLE_SQL=@"SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW'";
const string COLUMN_SQL=@"SELECT
TABLE_CATALOG AS [Database],
TABLE_SCHEMA AS Owner,
TABLE_NAME AS TableName,
COLUMN_NAME AS ColumnName,
ORDINAL_POSITION AS OrdinalPosition,
COLUMN_DEFAULT AS DefaultSetting,
IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType,
CHARACTER_MAXIMUM_LENGTH AS MaxLength,
DATETIME_PRECISION AS DatePrecision,
COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@tableName AND TABLE_SCHEMA=@schemaName
ORDER BY OrdinalPosition ASC";
const string OUTER_KEYS_SQL = @"SELECT
FK = OBJECT_NAME(pt.constraint_object_id),
Referenced_tbl = OBJECT_NAME(pt.referenced_object_id),
Referencing_col = pc.name,
Referenced_col = rc.name
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
WHERE pt.parent_object_id = OBJECT_ID(@tableName);";
const string INNER_KEYS_SQL = @"SELECT
[Schema] = OBJECT_SCHEMA_NAME(pt.parent_object_id),
Referencing_tbl = OBJECT_NAME(pt.parent_object_id),
FK = OBJECT_NAME(pt.constraint_object_id),
Referencing_col = pc.name,
Referenced_col = rc.name
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
WHERE pt.referenced_object_id = OBJECT_ID(@tableName);";
}
#>
Raw
SampleModel.cs
/// <summary>
/// A class which represents the Menu table.
/// </summary>
[Table("Menu")]
public class Menu
{
[Column("MenuSeq")]
[Key]
public virtual int MenuSeq { get; set; }
[Column("ParentSeq")]
public virtual int ParentSeq { get; set; }
[Column("ValuePath")]
public virtual string ValuePath { get; set; }
[Column("MenuName")]
public virtual string MenuName { get; set; }
[Column("MenuAction")]
public virtual string MenuAction { get; set; }
[Column("MenuActionExtend")]
public virtual string MenuActionExtend { get; set; }
[Column("Priority")]
public virtual int Priority { get; set; }
[Column("Status")]
public virtual string Status { get; set; }
[Column("CreateDate")]
public virtual DateTime CreateDate { get; set; }
[Column("CreatorType")]
public virtual string CreatorType { get; set; }
[Column("Creator")]
public virtual int Creator { get; set; }
[Column("UpdateDate")]
public virtual DateTime? UpdateDate { get; set; }
[Column("UpdatorType")]
public virtual string UpdatorType { get; set; }
[Column("Updator")]
public virtual int? Updator { get; set; }
public virtual IEnumerable<MenuHistory> MenuHistory { get; set; }
public virtual IEnumerable<MenuPermission> MenuPermission { get; set; }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment