dapper
支持framework4.0
/*
License: http://www.apache.org/licenses/LICENSE-2.0
Home page: http://code.google.com/p/dapper-dot-net/
Note: to build on C# 3.0 + .NET 3.5, include the CSHARP30 compiler symbol (and yes,
I know the difference between language and runtime versions; this is a compromise).
*/
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Reflection.Emit;
using System.Text;
using System.Threading;
using System.Text.RegularExpressions;
using System.Diagnostics;
using System.Globalization;
using System.Linq.Expressions;
namespace Dapper
{
[AssemblyNeutral, AttributeUsage(AttributeTargets.Class | AttributeTargets.Interface | AttributeTargets.Struct, AllowMultiple = false, Inherited = false)]
internal sealed class AssemblyNeutralAttribute : Attribute { }
///
/// Additional state flags that control command behaviour
///
[Flags]
public enum CommandFlags
{
///
/// No additional flags
///
None = 0,
///
/// Should data be buffered before returning?
///
Buffered = 1,
///
/// Can async queries be pipelined?
///
Pipelined = 2,
///
/// Should the plan cache be bypassed?
///
NoCache = 4,
}
///
/// Represents the key aspects of a sql operation
///
public struct CommandDefinition
{
internal static CommandDefinition ForCallback(object parameters)
{
if (parameters is DynamicParameters)
{
return new CommandDefinition(parameters);
}
else
{
return default(CommandDefinition);
}
}
private readonly string commandText;
private readonly object parameters;
private readonly IDbTransaction transaction;
private readonly int? commandTimeout;
private readonly CommandType? commandType;
private readonly CommandFlags flags;
internal void OnCompleted()
{
if (parameters is SqlMapper.IParameterCallbacks)
{
((SqlMapper.IParameterCallbacks)parameters).OnCompleted();
}
}
///
/// The command (sql or a stored-procedure name) to execute
///
public string CommandText { get { return commandText; } }
///
/// The parameters associated with the command
///
public object Parameters { get { return parameters; } }
///
/// The active transaction for the command
///
public IDbTransaction Transaction { get { return transaction; } }
///
/// The effective timeout for the command
///
public int? CommandTimeout { get { return commandTimeout; } }
///
/// The type of command that the command-text represents
///
public CommandType? CommandType { get { return commandType; } }
///
/// Should data be buffered before returning?
///
public bool Buffered { get { return (flags & CommandFlags.Buffered) != 0; } }
///
/// Should the plan for this query be cached?
///
internal bool AddToCache { get { return (flags & CommandFlags.NoCache) == 0; } }
///
/// Additional state flags against this command
///
public CommandFlags Flags { get { return flags; } }
///
/// Can async queries be pipelined?
///
public bool Pipelined { get { return (flags & CommandFlags.Pipelined) != 0; } }
///
/// Initialize the command definition
///
#if CSHARP30
public CommandDefinition(string commandText, object parameters, IDbTransaction transaction, int? commandTimeout,
CommandType? commandType, CommandFlags flags)
#else
public CommandDefinition(string commandText, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null,
CommandType? commandType = null, CommandFlags flags = CommandFlags.Buffered
#if ASYNC
, CancellationToken cancellationToken = default(CancellationToken)
#endif
)
#endif
{
this.commandText = commandText;
this.parameters = parameters;
this.transaction = transaction;
this.commandTimeout = commandTimeout;
this.commandType = commandType;
this.flags = flags;
#if ASYNC
this.cancellationToken = cancellationToken;
#endif
}
private CommandDefinition(object parameters)
: this()
{
this.parameters = parameters;
}
#if ASYNC
private readonly CancellationToken cancellationToken;
///
/// For asynchronous operations, the cancellation-token
///
public CancellationToken CancellationToken { get { return cancellationToken; } }
#endif
internal IDbCommand SetupCommand(IDbConnection cnn, Action<IDbCommand, object> paramReader)
{
var cmd = cnn.CreateCommand();
var init = GetInit(cmd.GetType());
if (init != null) init(cmd);
if (transaction != null)
cmd.Transaction = transaction;
cmd.CommandText = commandText;
if (commandTimeout.HasValue)
cmd.CommandTimeout = commandTimeout.Value;
if (commandType.HasValue)
cmd.CommandType = commandType.Value;
if (paramReader != null)
{
paramReader(cmd, parameters);
}
return cmd;
}
static SqlMapper.Link<Type, Action> commandInitCache;
static Action GetInit(Type commandType)
{
if (commandType == null) return null; // GIGO
Action action;
if (SqlMapper.Link<Type, Action>.TryGet(commandInitCache, commandType, out action))
{
return action;
}
var bindByName = GetBasicPropertySetter(commandType, "BindByName", typeof(bool));
var initialLongFetchSize = GetBasicPropertySetter(commandType, "InitialLONGFetchSize", typeof(int));
action = null;
if (bindByName != null || initialLongFetchSize != null)
{
var method = new DynamicMethod(commandType.Name + "_init", null, new Type[] { typeof(IDbCommand) });
var il = method.GetILGenerator();
if (bindByName != null)
{
// .BindByName = true
il.Emit(OpCodes.Ldarg_0);
il.Emit(OpCodes.Castclass, commandType);
il.Emit(OpCodes.Ldc_I4_1);
il.EmitCall(OpCodes.Callvirt, bindByName, null);
}
if (initialLongFetchSize != null)
{
// .InitialLONGFetchSize = -1
il.Emit(OpCodes.Ldarg_0);
il.Emit(OpCodes.Castclass, commandType);
il.Emit(OpCodes.Ldc_I4_M1);
il.EmitCall(OpCodes.Callvirt, initialLongFetchSize, null);
}
il.Emit(OpCodes.Ret);
action = (Action)method.CreateDelegate(typeof(Action));
}
// cache it
SqlMapper.Link<Type, Action>.TryAdd(ref commandInitCache, commandType, ref action);
return action;
}
static MethodInfo GetBasicPropertySetter(Type declaringType, string name, Type expectedType)
{
var prop = declaringType.GetProperty(name, BindingFlags.Public | BindingFlags.Instance);
ParameterInfo[] indexers;
if (prop != null && prop.CanWrite && prop.PropertyType == expectedType
&& ((indexers = prop.GetIndexParameters()) == null || indexers.Length == 0))
{
return prop.GetSetMethod();
}
return null;
}
}
///
/// Dapper, a light weight object mapper for ADO.NET
///
static partial class SqlMapper
{
///
/// Implement this interface to pass an arbitrary db specific set of parameters to Dapper
///
public partial interface IDynamicParameters
{
///
/// Add all the parameters needed to the command just before it executes
///
///The raw command prior to execution
///Information about the query
void AddParameters(IDbCommand command, Identity identity);
}
///
/// Extends IDynamicParameters providing by-name lookup of parameter values
///
public interface IParameterLookup : IDynamicParameters
{
///
/// Get the value of the specified parameter (return null if not found)
///
object this[string name] { get; }
}
///
/// Extends IDynamicParameters with facilities for executing callbacks after commands have completed
///
public partial interface IParameterCallbacks : IDynamicParameters
{
///
/// Invoked when the command has executed
///
void OnCompleted();
}
///
/// Implement this interface to pass an arbitrary db specific parameter to Dapper
///
[AssemblyNeutral]
public interface ICustomQueryParameter
{
///
/// Add the parameter needed to the command before it executes
///
///The raw command prior to execution
///Parameter name
void AddParameter(IDbCommand command, string name);
}
///
/// Implement this interface to perform custom type-based parameter handling and value parsing
///
[AssemblyNeutral]
public interface ITypeHandler
{
///
/// Assign the value of a parameter before a command executes
///
///The parameter to configure
///Parameter value
void SetValue(IDbDataParameter parameter, object value);
///
/// Parse a database value back to a typed value
///
///The value from the database
///The type to parse to
///The typed value
object Parse(Type destinationType, object value);
}
///
/// A type handler for data-types that are supported by the underlying provider, but which need
/// a well-known UdtTypeName to be specified
///
public class UdtTypeHandler : ITypeHandler
{
private readonly string udtTypeName;
///
/// Creates a new instance of UdtTypeHandler with the specified UdtTypeName
///
public UdtTypeHandler(string udtTypeName)
{
if (string.IsNullOrEmpty(udtTypeName)) throw new ArgumentException("Cannot be null or empty", udtTypeName);
this.udtTypeName = udtTypeName;
}
object ITypeHandler.Parse(Type destinationType, object value)
{
return value is DBNull ? null : value;
}
void ITypeHandler.SetValue(IDbDataParameter parameter, object value)
{
parameter.Value = ((object)value) ?? DBNull.Value;
if (parameter is System.Data.SqlClient.SqlParameter)
{
((System.Data.SqlClient.SqlParameter)parameter).UdtTypeName = udtTypeName;
}
}
}
///
/// Base-class for simple type-handlers
///
public abstract class TypeHandler : ITypeHandler
{
///
/// Assign the value of a parameter before a command executes
///
///The parameter to configure
///Parameter value
public abstract void SetValue(IDbDataParameter parameter, T value);
///
/// Parse a database value back to a typed value
///
///The value from the database
///The typed value
public abstract T Parse(object value);
void ITypeHandler.SetValue(IDbDataParameter parameter, object value)
{
if (value is DBNull)
{
parameter.Value = value;
}
else
{
SetValue(parameter, (T)value);
}
}
object ITypeHandler.Parse(Type destinationType, object value)
{
return Parse(value);
}
}
///
/// Implement this interface to change default mapping of reader columns to type members
///
public interface ITypeMap
{
///
/// Finds best constructor
///
///DataReader column names
///DataReader column types
///Matching constructor or default one
ConstructorInfo FindConstructor(string[] names, Type[] types);
///
/// Returns a constructor which should *always* be used.
///
/// Parameters will be default values, nulls for reference types and zero'd for value types.
///
/// Use this class to force object creation away from parameterless constructors you don't control.
///
ConstructorInfo FindExplicitConstructor();
///
/// Gets mapping for constructor parameter
///
///Constructor to resolve
///DataReader column name
///Mapping implementation
IMemberMap GetConstructorParameter(ConstructorInfo constructor, string columnName);
///
/// Gets member mapping for column
///
///DataReader column name
///Mapping implementation
IMemberMap GetMember(string columnName);
}
///
/// Implements this interface to provide custom member mapping
///
public interface IMemberMap
{
///
/// Source DataReader column name
///
string ColumnName { get; }
///
/// Target member type
///
Type MemberType { get; }
///
/// Target property
///
PropertyInfo Property { get; }
///
/// Target field
///
FieldInfo Field { get; }
///
/// Target constructor parameter
///
ParameterInfo Parameter { get; }
}
///
/// This is a micro-cache; suitable when the number of terms is controllable (a few hundred, for example),
/// and strictly append-only; you cannot change existing values. All key matches are on **REFERENCE**
/// equality. The type is fully thread-safe.
///
internal partial class Link<TKey, TValue> where TKey : class
{
public static bool TryGet(Link<TKey, TValue> link, TKey key, out TValue value)
{
while (link != null)
{
if ((object)key == (object)link.Key)
{
value = link.Value;
return true;
}
link = link.Tail;
}
value = default(TValue);
return false;
}
public static bool TryAdd(ref Link<TKey, TValue> head, TKey key, ref TValue value)
{
bool tryAgain;
do
{
var snapshot = Interlocked.CompareExchange(ref head, null, null);
TValue found;
if (TryGet(snapshot, key, out found))
{ // existing match; report the existing value instead
value = found;
return false;
}
var newNode = new Link<TKey, TValue>(key, value, snapshot);
// did somebody move our cheese?
tryAgain = Interlocked.CompareExchange(ref head, newNode, snapshot) != snapshot;
} while (tryAgain);
return true;
}
private Link(TKey key, TValue value, Link<TKey, TValue> tail)
{
Key = key;
Value = value;
Tail = tail;
}
public TKey Key { get; private set; }
public TValue Value { get; private set; }
public Link<TKey, TValue> Tail { get; private set; }
}
partial class CacheInfo
{
public DeserializerState Deserializer { get; set; }
public Func<IDataReader, object>[] OtherDeserializers { get; set; }
public Action<IDbCommand, object> ParamReader { get; set; }
private int hitCount;
public int GetHitCount() { return Interlocked.CompareExchange(ref hitCount, 0, 0); }
public void RecordHit() { Interlocked.Increment(ref hitCount); }
}
static int GetColumnHash(IDataReader reader)
{
unchecked
{
int colCount = reader.FieldCount, hash = colCount;
for (int i = 0; i < colCount; i++)
{ // binding code is only interested in names - not types
object tmp = reader.GetName(i);
hash = (hash * 31) + (tmp == null ? 0 : tmp.GetHashCode());
}
return hash;
}
}
struct DeserializerState
{
public readonly int Hash;
public readonly Func<IDataReader, object> Func;
public DeserializerState(int hash, Func<IDataReader, object> func)
{
Hash = hash;
Func = func;
}
}
///
/// Called if the query cache is purged via PurgeQueryCache
///
public static event EventHandler QueryCachePurged;
private static void OnQueryCachePurged()
{
var handler = QueryCachePurged;
if (handler != null) handler(null, EventArgs.Empty);
}
#if CSHARP30
private static readonly Dictionary<Identity, CacheInfo> _queryCache = new Dictionary<Identity, CacheInfo>();
// note: conflicts between readers and writers are so short-lived that it isn't worth the overhead of
// ReaderWriterLockSlim etc; a simple lock is faster
private static void SetQueryCache(Identity key, CacheInfo value)
{
lock (_queryCache) { _queryCache[key] = value; }
}
private static bool TryGetQueryCache(Identity key, out CacheInfo value)
{
lock (_queryCache) { return _queryCache.TryGetValue(key, out value); }
}
private static void PurgeQueryCacheByType(Type type)
{
lock (_queryCache)
{
var toRemove = _queryCache.Keys.Where(id => id.type == type).ToArray();
foreach (var key in toRemove)
_queryCache.Remove(key);
}
}
///
/// Purge the query cache
///
public static void PurgeQueryCache()
{
lock (_queryCache)
{
_queryCache.Clear();
}
OnQueryCachePurged();
}
#else
static readonly System.Collections.Concurrent.ConcurrentDictionary<Identity, CacheInfo> _queryCache = new System.Collections.Concurrent.ConcurrentDictionary<Identity, CacheInfo>();
private static void SetQueryCache(Identity key, CacheInfo value)
{
if (Interlocked.Increment(ref collect) == COLLECT_PER_ITEMS)
{
CollectCacheGarbage();
}
_queryCache[key] = value;
}
private static void CollectCacheGarbage()
{
try
{
foreach (var pair in _queryCache)
{
if (pair.Value.GetHitCount() <= COLLECT_HIT_COUNT_MIN)
{
CacheInfo cache;
_queryCache.TryRemove(pair.Key, out cache);
}
}
}
finally
{
Interlocked.Exchange(ref collect, 0);
}
}
private const int COLLECT_PER_ITEMS = 1000, COLLECT_HIT_COUNT_MIN = 0;
private static int collect;
private static bool TryGetQueryCache(Identity key, out CacheInfo value)
{
if (_queryCache.TryGetValue(key, out value))
{
value.RecordHit();
return true;
}
value = null;
return false;
}
///
/// Purge the query cache
///
public static void PurgeQueryCache()
{
_queryCache.Clear();
OnQueryCachePurged();
}
private static void PurgeQueryCacheByType(Type type)
{
foreach (var entry in _queryCache)
{
CacheInfo cache;
if (entry.Key.type == type)
_queryCache.TryRemove(entry.Key, out cache);
}
}
///
/// Return a count of all the cached queries by dapper
///
///
public static int GetCachedSQLCount()
{
return _queryCache.Count;
}
///
/// Return a list of all the queries cached by dapper
///
///
///
public static IEnumerable<Tuple<string, string, int>> GetCachedSQL(int ignoreHitCountAbove = int.MaxValue)
{
var data = _queryCache.Select(pair => Tuple.Create(pair.Key.connectionString, pair.Key.sql, pair.Value.GetHitCount()));
if (ignoreHitCountAbove < int.MaxValue) data = data.Where(tuple => tuple.Item3 <= ignoreHitCountAbove);
return data;
}
///
/// Deep diagnostics only: find any hash collisions in the cache
///
///
public static IEnumerable<Tuple<int, int>> GetHashCollissions()
{
var counts = new Dictionary<int, int>();
foreach (var key in _queryCache.Keys)
{
int count;
if (!counts.TryGetValue(key.hashCode, out count))
{
counts.Add(key.hashCode, 1);
}
else
{
counts[key.hashCode] = count + 1;
}
}
return from pair in counts
where pair.Value > 1
select Tuple.Create(pair.Key, pair.Value);
}
#endif
static Dictionary<Type, DbType> typeMap;
static SqlMapper()
{
typeMap = new Dictionary<Type, DbType>();
typeMap[typeof(byte)] = DbType.Byte;
typeMap[typeof(sbyte)] = DbType.SByte;
typeMap[typeof(short)] = DbType.Int16;
typeMap[typeof(ushort)] = DbType.UInt16;
typeMap[typeof(int)] = DbType.Int32;
typeMap[typeof(uint)] = DbType.UInt32;
typeMap[typeof(long)] = DbType.Int64;
typeMap[typeof(ulong)] = DbType.UInt64;
typeMap[typeof(float)] = DbType.Single;
typeMap[typeof(double)] = DbType.Double;
typeMap[typeof(decimal)] = DbType.Decimal;
typeMap[typeof(bool)] = DbType.Boolean;
typeMap[typeof(string)] = DbType.String;
typeMap[typeof(char)] = DbType.StringFixedLength;
typeMap[typeof(Guid)] = DbType.Guid;
typeMap[typeof(DateTime)] = DbType.DateTime;
typeMap[typeof(DateTimeOffset)] = DbType.DateTimeOffset;
typeMap[typeof(TimeSpan)] = DbType.Time;
typeMap[typeof(byte[])] = DbType.Binary;
typeMap[typeof(byte?)] = DbType.Byte;
typeMap[typeof(sbyte?)] = DbType.SByte;
typeMap[typeof(short?)] = DbType.Int16;
typeMap[typeof(ushort?)] = DbType.UInt16;
typeMap[typeof(int?)] = DbType.Int32;
typeMap[typeof(uint?)] = DbType.UInt32;
typeMap[typeof(long?)] = DbType.Int64;
typeMap[typeof(ulong?)] = DbType.UInt64;
typeMap[typeof(float?)] = DbType.Single;
typeMap[typeof(double?)] = DbType.Double;
typeMap[typeof(decimal?)] = DbType.Decimal;
typeMap[typeof(bool?)] = DbType.Boolean;
typeMap[typeof(char?)] = DbType.StringFixedLength;
typeMap[typeof(Guid?)] = DbType.Guid;
typeMap[typeof(DateTime?)] = DbType.DateTime;
typeMap[typeof(DateTimeOffset?)] = DbType.DateTimeOffset;
typeMap[typeof(TimeSpan?)] = DbType.Time;
typeMap[typeof(object)] = DbType.Object;
AddTypeHandlerImpl(typeof(DataTable), new DataTableHandler(), false);
}
///
/// Clear the registered type handlers
///
public static void ResetTypeHandlers()
{
typeHandlers = new Dictionary<Type, ITypeHandler>();
AddTypeHandlerImpl(typeof(DataTable), new DataTableHandler(), true);
}
///
/// Configure the specified type to be mapped to a given db-type
///
public static void AddTypeMap(Type type, DbType dbType)
{
// use clone, mutate, replace to avoid threading issues
var snapshot = typeMap;
DbType oldValue;
if (snapshot.TryGetValue(type, out oldValue) && oldValue == dbType) return; // nothing to do
var newCopy = new Dictionary<Type, DbType>(snapshot);
newCopy[type] = dbType;
typeMap = newCopy;
}
///
/// Configure the specified type to be processed by a custom handler
///
public static void AddTypeHandler(Type type, ITypeHandler handler)
{
AddTypeHandlerImpl(type, handler, true);
}
///
/// Configure the specified type to be processed by a custom handler
///
public static void AddTypeHandlerImpl(Type type, ITypeHandler handler, bool clone)
{
if (type == null) throw new ArgumentNullException("type");
Type secondary = null;
if (type.IsValueType)
{
var underlying = Nullable.GetUnderlyingType(type);
if (underlying == null)
{
secondary = typeof(Nullable<>).MakeGenericType(type); // the Nullable
// type is already the T
}
else
{
secondary = type; // the Nullable
type = underlying; // the T
}
}
var snapshot = typeHandlers;
ITypeHandler oldValue;
if (snapshot.TryGetValue(type, out oldValue) && handler == oldValue) return; // nothing to do
var newCopy = clone ? new Dictionary<Type, ITypeHandler>(snapshot) : snapshot;
#pragma warning disable 618
typeof(TypeHandlerCache<>).MakeGenericType(type).GetMethod("SetHandler", BindingFlags.Static | BindingFlags.NonPublic).Invoke(null, new object[] { handler });
if (secondary != null)
{
typeof(TypeHandlerCache<>).MakeGenericType(secondary).GetMethod("SetHandler", BindingFlags.Static | BindingFlags.NonPublic).Invoke(null, new object[] { handler });
}
#pragma warning restore 618
if (handler == null)
{
newCopy.Remove(type);
if (secondary != null) newCopy.Remove(secondary);
}
else
{
newCopy[type] = handler;
if (secondary != null) newCopy[secondary] = handler;
}
typeHandlers = newCopy;
}
///
/// Configure the specified type to be processed by a custom handler
///
public static void AddTypeHandler(TypeHandler handler)
{
AddTypeHandlerImpl(typeof(T), handler, true);
}
///
/// Not intended for direct usage
///
[Obsolete("Not intended for direct usage", false)]
[Browsable(false), EditorBrowsable(EditorBrowsableState.Never)]
public static class TypeHandlerCache
{
///
/// Not intended for direct usage
///
[Obsolete("Not intended for direct usage", true)]
public static T Parse(object value)
{
return (T)handler.Parse(typeof(T), value);
}
///
/// Not intended for direct usage
///
[Obsolete("Not intended for direct usage", true)]
public static void SetValue(IDbDataParameter parameter, object value)
{
handler.SetValue(parameter, value);
}
internal static void SetHandler(ITypeHandler handler)
{
#pragma warning disable 618
TypeHandlerCache.handler = handler;
#pragma warning restore 618
}
private static ITypeHandler handler;
}
private static Dictionary<Type, ITypeHandler> typeHandlers = new Dictionary<Type, ITypeHandler>();
internal const string LinqBinary = "System.Data.Linq.Binary";
///
/// Get the DbType that maps to a given value
///
[Obsolete("This method is for internal use only"), Browsable(false), EditorBrowsable(EditorBrowsableState.Never)]
public static DbType GetDbType(object value)
{
if (value == null || value is DBNull) return DbType.Object;
ITypeHandler handler;
return LookupDbType(value.GetType(), "n/a", false, out handler);
}
internal static DbType LookupDbType(Type type, string name, bool demand, out ITypeHandler handler)
{
DbType dbType;
handler = null;
var nullUnderlyingType = Nullable.GetUnderlyingType(type);
if (nullUnderlyingType != null) type = nullUnderlyingType;
if (type.IsEnum && !typeMap.ContainsKey(type))
{
type = Enum.GetUnderlyingType(type);
}
if (typeMap.TryGetValue(type, out dbType))
{
return dbType;
}
if (type.FullName == LinqBinary)
{
return DbType.Binary;
}
if (typeof(IEnumerable).IsAssignableFrom(type))
{
return DynamicParameters.EnumerableMultiParameter;
}
if (typeHandlers.TryGetValue(type, out handler))
{
return DbType.Object;
}
switch (type.FullName)
{
case "Microsoft.SqlServer.Types.SqlGeography":
AddTypeHandler(type, handler = new UdtTypeHandler("GEOGRAPHY"));
return DbType.Object;
case "Microsoft.SqlServer.Types.SqlGeometry":
AddTypeHandler(type, handler = new UdtTypeHandler("GEOMETRY"));
return DbType.Object;
case "Microsoft.SqlServer.Types.SqlHierarchyId":
AddTypeHandler(type, handler = new UdtTypeHandler("HIERARCHYID"));
return DbType.Object;
}
if (demand)
throw new NotSupportedException(string.Format("The member {0} of type {1} cannot be used as a parameter value", name, type.FullName));
return DbType.Object;
}
///
/// Identity of a cached query in Dapper, used for extensibility
///
public partial class Identity : IEquatable
{
internal Identity ForGrid(Type primaryType, int gridIndex)
{
return new Identity(sql, commandType, connectionString, primaryType, parametersType, null, gridIndex);
}
internal Identity ForGrid(Type primaryType, Type[] otherTypes, int gridIndex)
{
return new Identity(sql, commandType, connectionString, primaryType, parametersType, otherTypes, gridIndex);
}
///
/// Create an identity for use with DynamicParameters, internal use only
///
///
///
public Identity ForDynamicParameters(Type type)
{
return new Identity(sql, commandType, connectionString, this.type, type, null, -1);
}
internal Identity(string sql, CommandType? commandType, IDbConnection connection, Type type, Type parametersType, Type[] otherTypes)
: this(sql, commandType, connection.ConnectionString, type, parametersType, otherTypes, 0)
{ }
private Identity(string sql, CommandType? commandType, string connectionString, Type type, Type parametersType, Type[] otherTypes, int gridIndex)
{
this.sql = sql;
this.commandType = commandType;
this.connectionString = connectionString;
this.type = type;
this.parametersType = parametersType;
this.gridIndex = gridIndex;
unchecked
{
hashCode = 17; // we *know* we are using this in a dictionary, so pre-compute this
hashCode = hashCode * 23 + commandType.GetHashCode();
hashCode = hashCode * 23 + gridIndex.GetHashCode();
hashCode = hashCode * 23 + (sql == null ? 0 : sql.GetHashCode());
hashCode = hashCode * 23 + (type == null ? 0 : type.GetHashCode());
if (otherTypes != null)
{
foreach (var t in otherTypes)
{
hashCode = hashCode * 23 + (t == null ? 0 : t.GetHashCode());
}
}
hashCode = hashCode * 23 + (connectionString == null ? 0 : SqlMapper.connectionStringComparer.GetHashCode(connectionString));
hashCode = hashCode * 23 + (parametersType == null ? 0 : parametersType.GetHashCode());
}
}
///
///
///
///
///
public override bool Equals(object obj)
{
return Equals(obj as Identity);
}
///
/// The sql
///
public readonly string sql;
///
/// The command type
///
public readonly CommandType? commandType;
///
///
///
public readonly int hashCode, gridIndex;
///
///
///
public readonly Type type;
///
///
///
public readonly string connectionString;
///
///
///
public readonly Type parametersType;
///
///
///
///
public override int GetHashCode()
{
return hashCode;
}
///
/// Compare 2 Identity objects
///
///
///
public bool Equals(Identity other)
{
return
other != null &&
gridIndex == other.gridIndex &&
type == other.type &&
sql == other.sql &&
commandType == other.commandType &&
SqlMapper.connectionStringComparer.Equals(connectionString, other.connectionString) &&
parametersType == other.parametersType;
}
}
#if CSHARP30
///
/// Execute parameterized SQL
///
/// Number of rows affected
public static int Execute(this IDbConnection cnn, string sql, object param)
{
return Execute(cnn, sql, param, null, null, null);
}
///
/// Execute parameterized SQL
///
/// Number of rows affected
public static int Execute(this IDbConnection cnn, string sql, object param, IDbTransaction transaction)
{
return Execute(cnn, sql, param, transaction, null, null);
}
///
/// Execute parameterized SQL
///
/// Number of rows affected
public static int Execute(this IDbConnection cnn, string sql, object param, CommandType commandType)
{
return Execute(cnn, sql, param, null, null, commandType);
}
///
/// Execute parameterized SQL
///
/// Number of rows affected
public static int Execute(this IDbConnection cnn, string sql, object param, IDbTransaction transaction, CommandType commandType)
{
return Execute(cnn, sql, param, transaction, null, commandType);
}
///
/// Execute parameterized SQL and return an
///
/// An that can be used to iterate over the results of the SQL query.
public static IDataReader ExecuteReader(this IDbConnection cnn, string sql, object param)
{
return ExecuteReader(cnn, sql, param, null, null, null);
}
///
/// Execute parameterized SQL and return an
///
/// An that can be used to iterate over the results of the SQL query.
public static IDataReader ExecuteReader(this IDbConnection cnn, string sql, object param, IDbTransaction transaction)
{
return ExecuteReader(cnn, sql, param, transaction, null, null);
}
///
/// Execute parameterized SQL and return an
///
/// An that can be used to iterate over the results of the SQL query.
public static IDataReader ExecuteReader(this IDbConnection cnn, string sql, object param, CommandType commandType)
{
return ExecuteReader(cnn, sql, param, null, null, commandType);
}
///
/// Execute parameterized SQL and return an
///
/// An that can be used to iterate over the results of the SQL query.
public static IDataReader ExecuteReader(this IDbConnection cnn, string sql, object param, IDbTransaction transaction, CommandType commandType)
{
return ExecuteReader(cnn, sql, param, transaction, null, commandType);
}
///
/// Executes a query, returning the data typed as per T
///
/// A sequence of data of the supplied type; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is
/// created per row, and a direct column-name===member-name mapping is assumed (case insensitive).
///
public static IEnumerable Query(this IDbConnection cnn, string sql, object param)
{
return Query(cnn, sql, param, null, true, null, null);
}
///
/// Executes a query, returning the data typed as per T
///
/// A sequence of data of the supplied type; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is
/// created per row, and a direct column-name===member-name mapping is assumed (case insensitive).
///
public static IEnumerable Query(this IDbConnection cnn, string sql, object param, IDbTransaction transaction)
{
return Query(cnn, sql, param, transaction, true, null, null);
}
///
/// Executes a query, returning the data typed as per T
///
/// A sequence of data of the supplied type; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is
/// created per row, and a direct column-name===member-name mapping is assumed (case insensitive).
///
public static IEnumerable Query(this IDbConnection cnn, string sql, object param, CommandType commandType)
{
return Query(cnn, sql, param, null, true, null, commandType);
}
///
/// Executes a query, returning the data typed as per T
///
/// A sequence of data of the supplied type; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is
/// created per row, and a direct column-name===member-name mapping is assumed (case insensitive).
///
public static IEnumerable Query(this IDbConnection cnn, string sql, object param, IDbTransaction transaction, CommandType commandType)
{
return Query(cnn, sql, param, transaction, true, null, commandType);
}
///
/// Execute a command that returns multiple result sets, and access each in turn
///
public static GridReader QueryMultiple(this IDbConnection cnn, string sql, object param, IDbTransaction transaction)
{
return QueryMultiple(cnn, sql, param, transaction, null, null);
}
///
/// Execute a command that returns multiple result sets, and access each in turn
///
public static GridReader QueryMultiple(this IDbConnection cnn, string sql, object param, CommandType commandType)
{
return QueryMultiple(cnn, sql, param, null, null, commandType);
}
///
/// Execute a command that returns multiple result sets, and access each in turn
///
public static GridReader QueryMultiple(this IDbConnection cnn, string sql, object param, IDbTransaction transaction, CommandType commandType)
{
return QueryMultiple(cnn, sql, param, transaction, null, commandType);
}
#endif
///
/// Execute parameterized SQL
///
/// Number of rows affected
public static int Execute(
#if CSHARP30
this IDbConnection cnn, string sql, object param, IDbTransaction transaction, int? commandTimeout, CommandType? commandType
#else
this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null
#endif
)
{
var command = new CommandDefinition(sql, (object)param, transaction, commandTimeout, commandType, CommandFlags.Buffered);
return ExecuteImpl(cnn, ref command);
}
///
/// Execute parameterized SQL
///
/// Number of rows affected
public static int Execute(this IDbConnection cnn, CommandDefinition command)
{
return ExecuteImpl(cnn, ref command);
}
///
/// Execute parameterized SQL that selects a single value
///
/// The first cell selected
public static object ExecuteScalar(
#if CSHARP30
this IDbConnection cnn, string sql, object param, IDbTransaction transaction, int? commandTimeout, CommandType? commandType
#else
this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null
#endif
)
{
var command = new CommandDefinition(sql, (object)param, transaction, commandTimeout, commandType, CommandFlags.Buffered);
return ExecuteScalarImpl
using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Web; using log4net; using Dapper; namespace Dapper { public class DapperHelper { static readonly ILog log = LogManager.GetLogger(typeof(DapperHelper)); /// <summary> /// 数据库连接字符串 /// </summary> public static string connectionString = ""; public static string GetConnectionString(string constr) { if (constr == "") { return connectionString; } else { return constr; } } /// <summary> /// 查询列表 /// </summary> /// <param name="sql">查询的sql</param> /// <param name="param">替换参数</param> /// <returns></returns> public static List<T> Query<T>(string sql, object param = null, string constr = "") { using (SqlConnection con = new SqlConnection(GetConnectionString(constr))) { return con.Query<T>(sql, param).ToList(); } } /// <summary> /// 查询列表 /// </summary> /// <param name="sql">查询的sql</param> /// <param name="param">替换参数</param> /// <returns></returns> public static IEnumerable<dynamic> Query(string sql, object param = null, string constr = "") { using (SqlConnection con = new SqlConnection(GetConnectionString(constr))) { return con.Query(sql, param).ToList(); } } public static DataTable QueryDataTable(string sql, object param = null, string constr = "") { using (SqlConnection con = new SqlConnection(GetConnectionString(constr))) { DataTable table = new DataTable("Table"); var reader = con.ExecuteReader(sql, param); table.Load(reader); return table; } } /// <summary> /// 增删改 /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <returns></returns> public static int Execute(string sql, object param = null, string constr = "") { using (SqlConnection con = new SqlConnection(GetConnectionString(constr))) { return con.Execute(sql, param); } } /// <summary> /// Reader获取数据 /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <returns></returns> public static IDataReader ExecuteReader(string sql, object param = null, string constr = "") { using (SqlConnection con = new SqlConnection(GetConnectionString(constr))) { return con.ExecuteReader(sql, param); } } /// <summary> /// Scalar获取数据 /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <returns></returns> public static object ExecuteScalar(string sql, object param = null, string constr = "") { using (SqlConnection con = new SqlConnection(GetConnectionString(constr))) { return con.ExecuteScalar(sql, param); } } /// <summary> /// Scalar获取数据 /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <returns></returns> public static T ExecuteScalarForT<T>(string sql, object param = null, string constr = "") { using (SqlConnection con = new SqlConnection(GetConnectionString(constr))) { return con.ExecuteScalar<T>(sql, param); } } /// <summary> /// 带参数的存储过程 /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <returns></returns> public static List<T> ExecutePro<T>(string proc, object param = null, string constr = "") { using (SqlConnection con = new SqlConnection(GetConnectionString(constr))) { List<T> list = con.Query<T>(proc, param, null, true, null, CommandType.StoredProcedure).ToList(); return list; } } public static object ExecutePro(string proc, object param = null, string constr = "") { using (SqlConnection con = new SqlConnection(GetConnectionString(constr))) { // 执行存储过程 return con.Execute(proc, param, commandType: CommandType.StoredProcedure); } } /// <summary> /// 事务1 - 全SQL /// </summary> /// <param name="sqlarr">多条SQL</param> /// <param name="param">param</param> /// <returns></returns> public static int ExecuteTransaction(string[] sqlarr, string constr = "") { using (SqlConnection con = new SqlConnection(GetConnectionString(constr))) { con.Open(); using (var transaction = con.BeginTransaction()) { try { int result = 0; foreach (var sql in sqlarr) { result += con.Execute(sql, null, transaction); } transaction.Commit(); return result; } catch (Exception ex) { transaction.Rollback(); log.Error(ex.Message); foreach (var sql in sqlarr) { log.Error(sql); } return 0; } } } } /// <summary> /// 事务2 - 声明参数 ///demo: ///dic.Add("Insert into Users values (@UserName, @Email, @Address)", /// new { UserName = "jack", Email = "380234234@qq.com", Address = "上海" }); /// </summary> /// <param name="Key">多条SQL</param> /// <param name="Value">param</param> /// <returns></returns> public static int ExecuteTransaction(Dictionary<string, object> dic, string constr = "") { using (SqlConnection con = new SqlConnection(GetConnectionString(constr))) { con.Open(); using (var transaction = con.BeginTransaction()) { try { int result = 0; foreach (var sql in dic) { result += con.Execute(sql.Key, sql.Value, transaction); } transaction.Commit(); return result; } catch (Exception ex) { transaction.Rollback(); log.Error(ex.Message); foreach (var sql in dic) { log.Error(sql.Key + sql.Value.ToString()); } return 0; } } } } #region 生成sql public static string CreateClassFile(string tablename) { StringBuilder strcolumn = new StringBuilder(1000); string sql = @"select * from syscolumns where id=object_id('" + tablename + @"') "; var syscolumnsList = Query(sql).ToList(); foreach (var column in syscolumnsList) { strcolumn.Append(@" /// <summary> /// Desc: /// </summary> public string " + column.name + @" { get; set; }"); } sql = @"select * from syscolumns where id=object_id('" + tablename + @"') and colstat!=1 and xtype!=189"; string UpdateSql = "UPDATE " + tablename + @" SET "; string InsertSql = ""; var xx = Query(sql).ToList(); foreach (var x in xx) { UpdateSql += "" + x.name + "=@" + x.name + ","; InsertSql += "@" + x.name + ","; } InsertSql = InsertSql.Substring(0, InsertSql.Length - 1); UpdateSql = UpdateSql.Substring(0, UpdateSql.Length - 1) + " where 1=2 "; InsertSql = "insert into dbo." + tablename + @"(" + InsertSql.Replace("@", "") + ")values(" + InsertSql + @") "; string strdto = @" /// <summary> /// /// </summary> public partial class " + tablename + @" { public " + tablename + @"() { } public string InsertSql=""" + InsertSql + @"""; public string UpdateSql= """ + UpdateSql + @""" ; " + strcolumn.ToString() + @" } "; return strdto; } #endregion } }
