/// <summary>
/// Add a parameter to this dynamic parameter list
/// </summary>
public void Add(
string name, object value = null, DbType?dbType = null, ParameterDirection?direction = null, int?size = null, byte?precision = null, byte?scale = null
)
{
parameters[Clean(name)] = new ParamInfo
{
Name = name,
Value = value,
ParameterDirection = direction ?? ParameterDirection.Input,
DbType = dbType,
Size = size,
Precision = precision,
Scale = scale
};
}
public new void Add(string name, object value = null, DbType?dbType = null, ParameterDirection?direction = null, int?size = null)
{
if (dbType == null && value is string)
{
if (size == null)
{
dbType = DbType.AnsiString;
}
else
{
dbType = DbType.AnsiStringFixedLength;
}
}
base.Add(name, value, dbType, direction, size);
}
/// <summary>
/// Add parameter
/// </summary>
/// <param name="name">Parameter name</param>
/// <param name="value">Parameter value</param>
/// <param name="dbType">DbType</param>
/// <param name="direction">Direction</param>
/// <param name="size">Size</param>
/// <param name="precision">Precision</param>
/// <param name="scale">Scale</param>
public void Add(string name, object value = null, DbType?dbType = null, ParameterDirection?direction = null, int?size = null, byte?precision = null, byte?scale = null)
{
var item = new ParameterItem()
{
Name = name,
Value = value,
ParameterDirection = direction ?? ParameterDirection.Input,
DbType = dbType,
Size = size,
Precision = precision,
Scale = scale
};
Add(item);
}
/// <summary>
/// Adds the specified name.
/// </summary>
/// <param name="name">The name.</param>
/// <param name="value">The value.</param>
/// <param name="dbType">Type of the database.</param>
/// <param name="direction">The direction.</param>
/// <param name="size">The size.</param>
public void Add(string name, object value = null, DbType?dbType = null, ParameterDirection?direction = null, int?size = null)
{
if (Parameters.ContainsKey(name))
{
Parameters.Remove(name);
}
var parameter = new StatementParameter()
{
Name = name,
Value = value,
DbType = dbType,
Direction = direction,
Size = size
};
Parameters.Add(name, parameter);
}
/// <summary>
/// Method for creating parameters of type T
/// </summary>
/// <param name="name">Parameter name</param>
/// <param name="value">Parameter value</param>
/// <param name="type">Parameter DBType, optional</param>
/// <param name="direction">Parameter direction (Input / Output / InputOutput / ReturnValue), optional</param>
/// <param name="size">Parameter size (for types with variable size)</param>
/// <returns>created IDataParameter object</returns>
public T Create(string name, object value, DbType?type = null, ParameterDirection?direction = null, int?size = null)
{
var result = new T();
result.ParameterName = name;
if (type.HasValue)
{
result.DbType = type.Value;
}
result.Value = value;
if (direction.HasValue)
{
result.Direction = direction.Value;
}
if (size.HasValue)
{
SetSize(result, size.Value);
}
return(result);
}
public static IDbDataParameter AddParameter(this IDbCommand command, string name, DbType parameterType,
ParameterDirection?direction = null, int?size = null)
{
Verify.ValidString(name, nameof(name));
if (parameterType == DbType.Time)
{
var sqlParameter = new SqlParameter(name, SqlDbType.Time);
if (direction.HasValue)
{
sqlParameter.Direction = direction.Value;
}
if (size.HasValue)
{
sqlParameter.Size = size.Value;
}
command.Parameters.Add(sqlParameter);
return(sqlParameter);
}
var parameter = command.CreateParameter();
parameter.ParameterName = name;
parameter.DbType = parameterType;
if (direction.HasValue)
{
parameter.Direction = direction.Value;
}
if (size.HasValue)
{
parameter.Size = size.Value;
}
command.Parameters.Add(parameter);
return(parameter);
}
public static IDbDataParameter CreateParameter(
this IDbConnection source,
string name = default,
object value = default,
ParameterDirection?direction = default,
DbType?dbType = default,
int?size = default,
byte?scale = default,
byte?precision = default,
DataRowVersion?sourceVersion = default,
string sourceColumn = default)
{
if (source == null)
{
throw new ArgumentNullException(nameof(source));
}
using (IDbCommand cmd = source.CreateCommand())
{
return(cmd.CreateParameter(name, value, direction, dbType, size, scale, precision, sourceVersion, sourceColumn));
}
}
private static DynamicParameters GetParameters(ISPEntity iEntity)
{
DynamicParameters parameters = new DynamicParameters();
IDictionary <string, object> dicProperInfos = iEntity.GetChangedProperties();
foreach (KeyValuePair <string, object> item in dicProperInfos)
{
PropertyInfo pInfo = iEntity.GetType().GetProperty(item.Key);
ParameterDirection?direction = null;
if (pInfo != null)
{
direction = pInfo.GetParameterType();
}
int?size = null;
if (pInfo != null && pInfo.GetSize() > 0)
{
size = pInfo.GetSize();
}
parameters.Add(item.Key, item.Key, item.Value, null, direction, size);
}
return(parameters);
}
public static void AddParameter(
this IDbCommand command,
string parameterName,
object value,
DbType?dbType = null,
ParameterDirection?direction = null,
int?size = null,
byte?precision = null,
byte?scale = null)
{
var param = command.CreateParameter();
param.ParameterName = parameterName;
param.Value = value;
if (dbType != null)
{
param.DbType = dbType.Value;
}
if (direction != null)
{
param.Direction = direction.Value;
}
if (size != null)
{
param.Size = size.Value;
}
if (precision != null)
{
param.Precision = precision.Value;
}
if (scale != null)
{
param.Scale = scale.Value;
}
command.Parameters.Add(param);
}
public static IDbCommand AddParameter(
this IDbCommand command,
string name,
object value,
int precision,
int?scale = null,
DbType?dbType = null,
ParameterDirection?direction = null)
{
Check.ArgNotNull(command, nameof(command));
var p = command.CreateParameter();
p.ParameterName = name;
p.Value = value;
if (dbType.HasValue)
{
p.DbType = dbType.Value;
}
p.Precision = (byte)precision;
if (scale.HasValue)
{
p.Scale = (byte)scale.Value;
}
if (direction.HasValue)
{
p.Direction = direction.Value;
}
command.AddParameter(p);
return(command);
}
/// <summary>
/// 创建命令参数
/// </summary>
/// <param name="providerFactory">创建数据源类的提供程序</param>
/// <param name="name">参数名称</param>
/// <param name="value">参数值</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">参数大小</param>
/// <param name="precision">精度</param>
/// <param name="scale">小数位</param>
/// <param name="direction">方向</param>
/// <returns></returns>
public static IDbDataParameter CreateParameter(this DbProviderFactory providerFactory, string name, object value,
DbType?dbType = null, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
IDbDataParameter parameter = providerFactory.CreateParameter();
parameter.ParameterName = name;
parameter.Value = value;
if (dbType != null)
{
parameter.DbType = dbType.Value;
}
if (size != null && (size.Value > 0 || size.Value == -1))
{
parameter.Size = size.Value;
}
if (precision != null && precision.Value > 0)
{
parameter.Precision = (byte)precision.Value;
}
if (scale != null && scale.Value > 0)
{
parameter.Scale = (byte)scale.Value;
}
if (direction != null)
{
parameter.Direction = direction.Value;
}
else
{
parameter.Direction = ParameterDirection.Input;
}
// 补充字符串的长度
if (value != null && value.GetType() == typeof(string) && size == null)
{
string s = value.ToString();
if (dbType == null)
{
parameter.DbType = DbType.String;
}
if (parameter.DbType == DbType.String || parameter.DbType == DbType.StringFixedLength ||
parameter.DbType == DbType.AnsiString || parameter.DbType == DbType.AnsiStringFixedLength)
{
if (s.Length <= 256)
{
parameter.Size = 256;
}
else if (s.Length <= 512)
{
parameter.Size = 512;
}
else if (s.Length <= 1024)
{
parameter.Size = 1024;
}
else if (s.Length <= 4000)
{
parameter.Size = 4000;
}
else if (s.Length <= 8000)
{
parameter.Size = 8000;
}
else
{
parameter.Size = -1;
}
}
}
// 返回创建的参数
return(parameter);
}
///// <summary>
///// 获取XML类型的 SQL 片断
///// </summary>
//protected virtual string GetSqlValueByXml(object value, object dbType, int? size = null, int? precision = null, int? scale = null, ParameterDirection? direction = null)
//{
// return this.EscapeQuote(((SqlXml)value).Value, false, true);
//}
/// <summary>
/// 获取其它类型的 SQL 片断
/// </summary>
protected virtual string GetSqlValueByOther(object value, object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
if (value is byte[])
{
throw new NotSupportedException("System.Byte[] does not support serialization into strings.");
}
if (TypeUtils.IsPrimitiveType(value.GetType()))
{
return(value.ToString());
}
else
{
return(this.EscapeQuote(value.ToString(), false, false));
}
}
public IDbDataParameter AddParameter(string?name = null, object?value = null, DbType?type = null, ParameterDirection?direction = null)
{
var parameter = this._command.CreateParameter();
if (name != null)
{
parameter.ParameterName = name;
}
if (value != null)
{
parameter.Value = value;
}
if (type != null)
{
parameter.DbType = type.Value;
}
if (direction != null)
{
parameter.Direction = direction.Value;
}
this._command.Parameters.Add(parameter);
return(parameter);
}
// 生成集合对应的 SQL 片断
private string GetSqlValue(IEnumerable value, ResolveToken token, object dbType, int?size, int?precision, int?scale, ParameterDirection?direction = null)
{
if (value == null)
{
return("NULL");
}
var iterator = value.GetEnumerator();
List <string> sqlValues = new List <string>();
while (iterator.MoveNext())
{
string text = this.GetSqlValue(iterator.Current, token, dbType, size, precision, scale, direction);
sqlValues.Add(text);
}
// =>a,b,c
string sql = string.Join(",", sqlValues);
return(sql);
}
/// <summary>
/// 生成 value 对应的 SQL 片断
/// </summary>
/// <param name="value">SQL值</param>
/// <param name="token">解析SQL命令时的参数上下文</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">长度</param>
/// <param name="precision">精度</param>
/// <param name="scale">小数位</param>
/// <param name="direction">查询参数类型</param>
/// <returns></returns>
public string GetSqlValue(object value, ResolveToken token,
object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
// https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-2017
// 1.Date 3个字节,DateTime 8个字节 DateTime2 <=4 6个字节 其它8个字节，外加1个字节存储精度
// 2.如果转换时没有指定数据类型的长度，则SQServer自动提供长度为30
// 3.隐式转换优先级 https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017
// 4.参数化查询只需要重写 CreateParameter
if (value == null)
{
return("NULL");
}
Type type = value.GetType();
if (token != null && token.Parameters != null)
{
// 参数化 ##########
if (!(value is string) && !(value is byte[]) && (value is IEnumerable))
{
return(this.GetSqlValue(value as IEnumerable, token, dbType, size, precision, scale));
}
else
{
return(this.AddParameter(value, token, dbType, size, precision, scale, direction).ParameterName);
}
}
else
{
// 非参数化 ##########
// 枚举类型
if (type.IsEnum)
{
return(this.GetSqlValueByEnum(value));
}
// Guid 类型
else if (value is Guid)
{
return(this.GetSqlValueByGuid(value));
}
// 数据类型
else if (TypeUtils.IsNumberType(type))
{
return(this.GetSqlValueByNumber(value));
}
// byte[] 类型
else if (value is byte[])
{
return(this.GetSqlValueByBytes(value));
}
// 布尔类型
else if (value is bool)
{
return(this.GetSqlValueByBoolean(value, dbType));
}
// 字符类型
else if (value is char || value is string)
{
return(this.GetSqlValueByString(value, dbType, size));
}
// 时间类型
else if (value is TimeSpan)
{
return(this.GetSqlValueByTime(value, dbType, scale));
}
// 日期类型
else if (value is DateTime)
{
return(this.GetSqlValueByDateTime(value, dbType, scale));
}
// 日期类型（带时区）
else if (value is DateTimeOffset)
{
return(this.GetSqlValueByDateTimeOffset(value, dbType, scale));
}
// 集合类型
else if (value is IEnumerable)
{
return(this.GetSqlValue(value as IEnumerable, token, dbType, size, precision, scale));
}
else
{
throw new NotSupportedException(string.Format("type {0} not supported serialize to string", type.FullName));
}
}
}
/// <summary>
/// Adds single parameter to current Command Builder. <br />
/// </summary>
public CommandBuilder AddParameter(string parameterName, object parameterValue = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null, byte? precision = null, byte? scale = null)
{
_parameters.Add(new ParameterInfo(parameterName, parameterValue, dbType, direction, size, precision, scale));
_parametersStr = string.Join(", ", _parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get<dynamic>(n)) + "'"));
return this;
}
/// <summary>
/// New Parameter
/// </summary>
/// <param name="name">The name of the parameter.</param>
/// <param name="value">The value of the parameter.</param>
/// <param name="dbType">The type of the parameter.</param>
/// <param name="direction">The in or out direction of the parameter.</param>
/// <param name="size">The size of the parameter.</param>
public ParameterInfo(string name, object value, DbType?dbType, ParameterDirection?direction, int?size) : this(name, value, dbType, direction, size, null, null)
{
}
public static void Add(this DynamicParameters parameter, string name, object value, SqlDbType?sqlDbType, ParameterDirection?direction, int?size)
{
parameter.Add(name, value, (sqlDbType != null ? sqlDbTypeMap[sqlDbType.Value] : (DbType?)null), direction, size);
}
public static void Add(this DynamicParameters parameter, string name, object value = null, SqlDbType?sqlDbType = null, ParameterDirection?direction = null, int?size = null, byte?precision = null, byte?scale = null)
{
parameter.Add(name, value, (sqlDbType != null ? sqlDbTypeMap[sqlDbType.Value] : (DbType?)null), direction, size, precision, scale);
}
private static SqlParameter CreateParameter <T>(string name, T value, SqlDbType type, int?size, ParameterDirection?direction)
{
SqlParameter param = new SqlParameter();
param.ParameterName = name;
param.SqlDbType = type;
if (value == null)
{
param.Value = DBNull.Value;
}
else
{
param.Value = value;
}
if (size != null)
{
param.Size = size.Value;
}
if (direction != null)
{
param.Direction = direction.Value;
}
return(param);
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name="parameterName">参数名</param>
/// <param name="value">参数值</param>
/// <param name="dbType">数据库类型</param>
/// <param name="size">长度</param>
/// <param name="direction">Dataset参数类型</param>
/// <param name="scale">参数值的精度</param>
public void AddParameter(string parameterName, object value, DbType?dbType, int?size, ParameterDirection?direction, byte?scale = null)
{
AddParameter(new DbParameterInfo(parameterName, value, dbType, size, direction, scale: scale));
}
/// <summary>
/// 增加一个参数
/// </summary>
protected override IDbDataParameter AddParameter(object value, object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
if (value is bool)
{
value = ((bool)value) ? 1 : 0;
}
else if (value is Guid)
{
value = ((Guid)value).ToByteArray();
dbType = OracleDbType.Raw;
}
//else if (value is DateTimeOffset)
//{
// value = new Oracle.ManagedDataAccess.Types.OracleTimeStampTZ(((DateTimeOffset)value).LocalDateTime, TimeZone.CurrentTimeZone.StandardName);
//}
OracleParameter parameter = (OracleParameter)base.AddParameter(value, dbType, size, precision, scale, direction);
// 补充 DbType
parameter.SetDbType(dbType);
return(parameter);
}
// 增加一个参数
protected override IDbDataParameter AddParameter(object value, object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
MySqlParameter parameter = (MySqlParameter)base.AddParameter(value, dbType, size, precision, scale, direction);
// 补充 DbType
MySqlDbTypeInfo dbTypeInfo = MySqlDbTypeInfo.Create(dbType);
if (dbTypeInfo != null && dbTypeInfo.DbType != null)
{
parameter.DbType = dbTypeInfo.DbType.Value;
}
else if (dbTypeInfo != null && dbTypeInfo.SqlDbType != null)
{
parameter.MySqlDbType = dbTypeInfo.SqlDbType.Value;
}
if (size != null && (size.Value > 0 || size.Value == -1))
{
parameter.Size = size.Value;
}
if (precision != null && precision.Value > 0)
{
parameter.Precision = (byte)precision.Value;
}
if (scale != null && scale.Value > 0)
{
parameter.Scale = (byte)scale.Value;
}
return(parameter);
}
public void Add(string name, object value = null, DbType?dbType = null, ParameterDirection?direction = null, int?size = null)
{
dynamicParameters.Add(name, value, dbType, direction, size);
}
/// <summary>
/// 增加一个SQL参数
/// </summary>
/// <param name="value">值</param>
/// <param name="token">解析SQL命令时的参数上下文</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">长度</param>
/// <param name="precision">精度</param>
/// <param name="scale">小数位</param>
/// <param name="direction">查询参数类型</param>
/// <returns></returns>
protected virtual IDbDataParameter AddParameter(object value, ResolveToken token,
object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
string name = string.Format("{0}p{1}", _provider.ParameterPrefix, token.Parameters.Count);
var parameter = _provider.DbProviderFactory.CreateParameter(name, value, null, size, precision, scale, direction);
token.Parameters.Add(parameter);
return(parameter);
}
/// <summary>
/// Adds a parameter to this dynamic parameter list.
/// </summary>
/// <param name="name">Parameter name.</param>
/// <param name="value">Parameter value.</param>
/// <param name="dbType">Parameter DB type.</param>
/// <param name="direction">Parameter direction.</param>
/// <param name="size">Parameter size.</param>
public void Add(string name, object value = null, OracleDbType?dbType = null, ParameterDirection?direction = null, int?size = null)
{
_parameters[Clean(name)] = new ParamInfo
{
Name = name,
Value = value,
ParameterDirection = direction ?? ParameterDirection.Input,
DbType = dbType,
Size = size
};
}
/// <summary>
/// Create a OracleParameter.
/// </summary>
/// <param name="name">Parameter name.</param>
/// <param name="type">Parameter OracleDbType.</param>
/// <param name="direction">Parameter direction.</param>
/// <param name="value">Parameter value.</param>
/// <returns>The created parameter.</returns>
/// <exception cref="System.ArgumentException"><paramref name="name"/> is null or empty.</exception>
private static OracleParameter CreateParameterInternal(string name, OracleDbType?type, ParameterDirection?direction, object value)
{
if (string.IsNullOrWhiteSpace(name))
{
throw new ArgumentException("Parameter name is null or empty.", "name");
}
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = name;
parameter.Value = value ?? DBNull.Value;
if (type.HasValue)
{
parameter.OracleDbType = type.Value;
}
if (direction.HasValue)
{
parameter.Direction = direction.Value;
}
return(parameter);
}
/// <summary>
/// 生成 value 对应的 SQL 片断
/// </summary>
public string GetSqlValue(object value, object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
// https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-2017
// 1.Date 3个字节,DateTime 8个字节 DateTime2 <=4 6个字节 其它8个字节，外加1个字节存储精度
// 2.如果转换时没有指定数据类型的长度，则SQServer自动提供长度为30
// 3.隐式转换优先级 https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017
// 4.参数化查询只需要重写 CreateParameter
if (value == null)
{
return("NULL");
}
Type type = value.GetType();
if (this.Parameterized)
{
// 参数化 ##########
if (!(value is string) && !(value is byte[]) && (value is IEnumerable))
{
return(this.GetSqlValue(value as IEnumerable, dbType, size, precision, scale));
}
else
{
return(this.AddParameter(value, dbType, size, precision, scale, direction).ParameterName);
}
}
else
{
// 非参数化 ##########
// Guid 类型
if (value is Guid)
{
return(this.GetSqlValueByGuid(value));
}
// 布尔类型
else if (value is bool)
{
return(this.GetSqlValueByBoolean(value, dbType));
}
// 字符类型
else if (value is char || value is string)
{
return(GetSqlValueByString(value, dbType, size));
}
// 时间类型
else if (value is TimeSpan)
{
return(this.GetSqlValueByTime(value, dbType, precision));
}
// 日期类型
else if (value is DateTime)
{
return(this.GetSqlValueByDateTime(value, dbType, precision));
}
// 日期类型（带时区）
else if (value is DateTimeOffset)
{
return(this.GetSqlValueByDateTimeOffset(value, dbType, precision));
}
//// xml类型
//else if (value is SqlXml)
// return this.GetSqlValueByXml(value, dbType);
// 集合类型
else if (value is IEnumerable)
{
return(this.GetSqlValue(value as IEnumerable, dbType, size, precision, scale));
}
// 其它 <int byte long etc.>
else
{
return(this.GetSqlValueByOther(value, dbType, size, precision, scale, direction));
}
}
}
// 增加一个参数
protected override IDbDataParameter AddParameter(object value, object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
MySqlParameter parameter = (MySqlParameter)base.AddParameter(value, dbType, size, precision, scale, direction);
// 补充 DbType
parameter.SetDbType(dbType);
return(parameter);
}
/// <summary>
/// 增加一个参数
/// </summary>
protected virtual IDbDataParameter AddParameter(object value, object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
string parameterName = string.Format("{0}p{1}", _provider.ParameterPrefix, this.Parameters.Count);
IDbDataParameter parameter = _provider.DbProviderFactory.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = value;
if (size != null && (size.Value > 0 || size.Value == -1))
{
parameter.Size = size.Value;
}
if (precision != null && precision.Value > 0)
{
parameter.Precision = (byte)precision.Value;
}
if (scale != null && scale.Value > 0)
{
parameter.Scale = (byte)scale.Value;
}
if (direction != null)
{
parameter.Direction = direction.Value;
}
else
{
parameter.Direction = ParameterDirection.Input;
}
// 补充字符串的长度
if (value != null && value.GetType() == typeof(string) && size == null)
{
string s = value.ToString();
if (dbType == null)
{
parameter.DbType = DbType.String;
}
if (parameter.DbType == DbType.String || parameter.DbType == DbType.StringFixedLength ||
parameter.DbType == DbType.AnsiString || parameter.DbType == DbType.AnsiStringFixedLength)
{
if (s.Length <= 256)
{
parameter.Size = 256;
}
else if (s.Length <= 512)
{
parameter.Size = 512;
}
else if (s.Length <= 1024)
{
parameter.Size = 1024;
}
else if (s.Length <= 4000)
{
parameter.Size = 4000;
}
else if (s.Length <= 8000)
{
parameter.Size = 8000;
}
else
{
parameter.Size = -1;
}
}
}
this.Parameters.Add(parameter);
return(parameter);
}