using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Collections.Concurrent;
using System.Reflection;
using System.Text;
using System.Data.Common;
using System.Reflection.Emit;
namespace Dapper
{
///
/// Represents a database, assumes all the tables have an Id column named Id
///
/// The type of database this represents.
public abstract partial class Database : IDisposable where TDatabase : Database, new()
{
///
/// A database table of type and primary key of type .
///
/// The type of object in this table.
/// The type of the primary key for this table.
public partial class Table
{
internal Database database;
internal string tableName;
internal string likelyTableName;
///
/// Creates a table in the specified database with a given name.
///
/// The database this table belongs in.
/// The name for this table.
public Table(Database database, string likelyTableName)
{
this.database = database;
this.likelyTableName = likelyTableName;
}
///
/// The name for this table.
///
public string TableName
{
get
{
tableName ??= database.DetermineTableName(likelyTableName);
return tableName;
}
}
///
/// Insert a row into the db
///
/// Either DynamicParameters or an anonymous type or concrete type
///
public virtual int? Insert(dynamic data)
{
var o = (object)data;
List paramNames = GetParamNames(o);
paramNames.Remove("Id");
string cols = string.Join(",", paramNames);
string colsParams = string.Join(",", paramNames.Select(p => "@" + p));
var sql = "set nocount on insert " + TableName + " (" + cols + ") values (" + colsParams + ") select cast(scope_identity() as int)";
return database.Query(sql, o).Single();
}
///
/// Update a record in the database.
///
/// The primary key of the row to update.
/// The new object data.
/// The number of rows affected.
public int Update(TId id, dynamic data)
{
List paramNames = GetParamNames((object)data);
var builder = new StringBuilder();
builder.Append("update ").Append(TableName).Append(" set ");
builder.AppendLine(string.Join(",", paramNames.Where(n => n != "Id").Select(p => p + "= @" + p)));
builder.Append("where Id = @Id");
DynamicParameters parameters = new DynamicParameters(data);
parameters.Add("Id", id);
return database.Execute(builder.ToString(), parameters);
}
///
/// Delete a record for the DB
///
///
///
public bool Delete(TId id)
{
return database.Execute("delete from " + TableName + " where Id = @id", new { id }) > 0;
}
///
/// Gets a record with a particular Id from the DB
///
/// The primary key of the table to fetch.
/// The record with the specified Id.
public T Get(TId id)
{
return database.QueryFirstOrDefault("select * from " + TableName + " where Id = @id", new { id });
}
///
/// Gets the first row from this table (order determined by the database provider).
///
/// Data from the first table row.
public virtual T First()
{
return database.QueryFirstOrDefault("select top 1 * from " + TableName);
}
///
/// Gets the all rows from this table.
///
/// Data from all table rows.
public IEnumerable All()
{
return database.Query("select * from " + TableName);
}
private static readonly ConcurrentDictionary> paramNameCache = new ConcurrentDictionary>();
internal static List GetParamNames(object o)
{
if (o is DynamicParameters parameters)
{
return parameters.ParameterNames.ToList();
}
if (!paramNameCache.TryGetValue(o.GetType(), out List paramNames))
{
paramNames = new List();
foreach (var prop in o.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public).Where(p => p.GetGetMethod(false) != null))
{
var attribs = prop.GetCustomAttributes(typeof(IgnorePropertyAttribute), true);
#pragma warning disable IDE0019 // Use pattern matching - complex enough here
var attr = attribs.FirstOrDefault() as IgnorePropertyAttribute;
#pragma warning restore IDE0019 // Use pattern matching
if (attr == null || (!attr.Value))
{
paramNames.Add(prop.Name);
}
}
paramNameCache[o.GetType()] = paramNames;
}
return paramNames;
}
}
///
/// A database table of type and primary key of type .
///
/// The type of object in this table.
public class Table : Table
{
///
/// Creates a table in the specified database with a given name.
///
/// The database this table belongs in.
/// The name for this table.
public Table(Database database, string likelyTableName)
: base(database, likelyTableName)
{
}
}
private DbConnection _connection;
private int _commandTimeout;
///
/// Get access to the underlying transaction
///
public DbTransaction Transaction { get; private set; }
///
/// Get underlying database connection.
///
public DbConnection Connection => _connection;
///
/// Initializes the database.
///
/// The connection to use.
/// The timeout to use (in seconds).
///
public static TDatabase Init(DbConnection connection, int commandTimeout)
{
TDatabase db = new TDatabase();
db.InitDatabase(connection, commandTimeout);
return db;
}
internal static Action tableConstructor;
internal void InitDatabase(DbConnection connection, int commandTimeout)
{
_connection = connection;
_commandTimeout = commandTimeout;
tableConstructor ??= CreateTableConstructorForTable();
tableConstructor(this as TDatabase);
}
internal virtual Action CreateTableConstructorForTable()
{
return CreateTableConstructor(typeof(Table<>), typeof(Table<,>));
}
///
/// Begins a transaction in this database.
///
/// The isolation level to use.
/// The transaction created
public DbTransaction BeginTransaction(IsolationLevel isolation = IsolationLevel.ReadCommitted)
{
Transaction = _connection.BeginTransaction(isolation);
return Transaction;
}
///
/// Commits the current transaction in this database.
///
public void CommitTransaction()
{
Transaction.Commit();
Transaction = null;
}
///
/// Rolls back the current transaction in this database.
///
public void RollbackTransaction()
{
Transaction.Rollback();
Transaction = null;
}
///
/// Gets a table creation function for the specified type.
///
/// The object type to create a table for.
/// The function to create the table.
protected Action CreateTableConstructor(Type tableType)
{
return CreateTableConstructor(new[] { tableType });
}
///
/// Gets a table creation function for the specified types.
///
/// The object types to create a table for.
/// The function to create the tables.
protected Action CreateTableConstructor(params Type[] tableTypes)
{
var dm = new DynamicMethod("ConstructInstances", null, new[] { typeof(TDatabase) }, true);
var il = dm.GetILGenerator();
var setters = GetType().GetProperties()
.Where(p => p.PropertyType.IsGenericType && tableTypes.Contains(p.PropertyType.GetGenericTypeDefinition()))
.Select(p => Tuple.Create(
p.GetSetMethod(true),
p.PropertyType.GetConstructor(new[] { typeof(TDatabase), typeof(string) }),
p.Name,
p.DeclaringType
));
foreach (var setter in setters)
{
il.Emit(OpCodes.Ldarg_0);
// [db]
il.Emit(OpCodes.Ldstr, setter.Item3);
// [db, likelyname]
il.Emit(OpCodes.Newobj, setter.Item2);
// [table]
var table = il.DeclareLocal(setter.Item2.DeclaringType);
il.Emit(OpCodes.Stloc, table);
// []
il.Emit(OpCodes.Ldarg_0);
// [db]
il.Emit(OpCodes.Castclass, setter.Item4);
// [db cast to container]
il.Emit(OpCodes.Ldloc, table);
// [db cast to container, table]
il.Emit(OpCodes.Callvirt, setter.Item1);
// []
}
il.Emit(OpCodes.Ret);
return (Action)dm.CreateDelegate(typeof(Action));
}
private static readonly ConcurrentDictionary tableNameMap = new ConcurrentDictionary();
private string DetermineTableName(string likelyTableName)
{
if (!tableNameMap.TryGetValue(typeof(T), out string name))
{
name = likelyTableName;
if (!TableExists(name))
{
name = "[" + typeof(T).Name + "]";
}
tableNameMap[typeof(T)] = name;
}
return name;
}
private bool TableExists(string name)
{
string schemaName = null;
name = name.Replace("[", "");
name = name.Replace("]", "");
if (name.IndexOf('.') > 0)
{
var parts = name.Split('.');
if (parts.Length == 2)
{
schemaName = parts[0];
name = parts[1];
}
}
var builder = new StringBuilder("select 1 from INFORMATION_SCHEMA.TABLES where ");
if (!string.IsNullOrEmpty(schemaName)) builder.Append("TABLE_SCHEMA = @schemaName AND ");
builder.Append("TABLE_NAME = @name");
return _connection.Query(builder.ToString(), new { schemaName, name }, Transaction).Count() == 1;
}
///
/// Executes SQL against the current database.
///
/// The SQL to execute.
/// The parameters to use.
/// The number of rows affected.
public int Execute(string sql, dynamic param = null) =>
_connection.Execute(sql, param as object, Transaction, _commandTimeout);
///
/// Queries the current database.
///
/// The type to return.
/// The SQL to execute.
/// The parameters to use.
/// Whether to buffer the results.
/// An enumerable of for the rows fetched.
public IEnumerable Query(string sql, dynamic param = null, bool buffered = true) =>
_connection.Query(sql, param as object, Transaction, buffered, _commandTimeout);
///
/// Queries the current database for a single record.
///
/// The type to return.
/// The SQL to execute.
/// The parameters to use.
/// An enumerable of for the rows fetched.
public T QueryFirstOrDefault(string sql, dynamic param = null) =>
_connection.QueryFirstOrDefault(sql, param as object, Transaction, _commandTimeout);
///
/// Perform a multi-mapping query with 2 input types.
/// This returns a single type, combined from the raw types via .
///
/// The first type in the recordset.
/// The second type in the recordset.
/// The combined type to return.
/// The SQL to execute for this query.
/// The function to map row types to the return type.
/// The parameters to use for this query.
/// The transaction to use for this query.
/// Whether to buffer the results in memory.
/// The field we should split and read the second object from (default: "Id").
/// Number of seconds before command execution timeout.
/// An enumerable of .
public IEnumerable Query(string sql, Func map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null) =>
_connection.Query(sql, map, param as object, transaction, buffered, splitOn, commandTimeout);
///
/// Perform a multi-mapping query with 3 input types.
/// This returns a single type, combined from the raw types via .
///
/// The first type in the recordset.
/// The second type in the recordset.
/// The third type in the recordset.
/// The combined type to return.
/// The SQL to execute for this query.
/// The function to map row types to the return type.
/// The parameters to use for this query.
/// The transaction to use for this query.
/// Whether to buffer the results in memory.
/// The field we should split and read the second object from (default: "Id").
/// Number of seconds before command execution timeout.
/// An enumerable of .
public IEnumerable Query(string sql, Func map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null) =>
_connection.Query(sql, map, param as object, transaction, buffered, splitOn, commandTimeout);
///
/// Perform a multi-mapping query with 4 input types.
/// This returns a single type, combined from the raw types via .
///
/// The first type in the recordset.
/// The second type in the recordset.
/// The third type in the recordset.
/// The fourth type in the recordset.
/// The combined type to return.
/// The SQL to execute for this query.
/// The function to map row types to the return type.
/// The parameters to use for this query.
/// The transaction to use for this query.
/// Whether to buffer the results in memory.
/// The field we should split and read the second object from (default: "Id").
/// Number of seconds before command execution timeout.
/// An enumerable of .
public IEnumerable Query(string sql, Func map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null) =>
_connection.Query(sql, map, param as object, transaction, buffered, splitOn, commandTimeout);
///
/// Perform a multi-mapping query with 5 input types.
/// This returns a single type, combined from the raw types via .
///
/// The first type in the recordset.
/// The second type in the recordset.
/// The third type in the recordset.
/// The fourth type in the recordset.
/// The fifth type in the recordset.
/// The combined type to return.
/// The SQL to execute for this query.
/// The function to map row types to the return type.
/// The parameters to use for this query.
/// The transaction to use for this query.
/// Whether to buffer the results in memory.
/// The field we should split and read the second object from (default: "Id").
/// Number of seconds before command execution timeout.
/// An enumerable of .
public IEnumerable Query(string sql, Func map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null) =>
_connection.Query(sql, map, param as object, transaction, buffered, splitOn, commandTimeout);
///
/// Return a sequence of dynamic objects with properties matching the columns
///
/// The SQL to execute.
/// The parameters to use.
/// Whether the results should be buffered in memory.
/// Note: each row can be accessed via "dynamic", or by casting to an IDictionary<string,object>
public IEnumerable Query(string sql, dynamic param = null, bool buffered = true) =>
_connection.Query(sql, param as object, Transaction, buffered);
///
/// Execute a command that returns multiple result sets, and access each in turn.
///
/// The SQL to execute for this query.
/// The parameters to use for this query.
/// The transaction to use for this query.
/// Number of seconds before command execution timeout.
/// Is it a stored proc or a batch?
public SqlMapper.GridReader QueryMultiple(string sql, dynamic param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) =>
SqlMapper.QueryMultiple(_connection, sql, param, transaction, commandTimeout, commandType);
///
/// Disposes the current database, rolling back current transactions.
///
public virtual void Dispose()
{
var connection = _connection;
if (connection.State != ConnectionState.Closed)
{
_connection = null;
Transaction = null;
connection?.Close();
}
GC.SuppressFinalize(this);
}
}
}