/// <summary>
/// Gets the select command ready to execute the select query.
/// </summary>
/// <param name="whereClause">An optional where clause to add to the query. If the where contains parameters, they need to be named as @1 @2 @3 etc.</param>
/// <param name="parameters">A list of parameter values.</param>
/// <returns>
/// A select command already parametrized to execute.
/// </returns>
public IDatabaseCommand GetCommand(string whereClause = null, params object[] parameters)
{
if (string.IsNullOrEmpty(whereClause) && parameters != null && parameters.Length > 0)
{
throw new Exception("Can not pass parameters without a where clause.");
}
var builder = new StringBuilder(this.CommandText);
if (!string.IsNullOrWhiteSpace(whereClause))
{
builder.AppendFormat(" WHERE {0}", whereClause);
}
var command = this.Connector.CreateCommand();
if (parameters != null)
{
for (var index = 0; index < parameters.Length; index++)
{
var oldName = $"@{index + 1}";
var newName = this.FormatProvider.GetParameterName($"p{(index + 1)}");
builder.Replace(oldName, newName);
var parameter = parameters[index];
var type = parameter == null ? typeof(object) : parameter.GetType();
var commandParameter = command.AddParameter(newName, DbTypeConverter.FromType(type));
commandParameter.Value = parameter;
}
}
command.CommandText = builder.ToString();
return(command);
}
IDictionary <string, string> ProcessRow(IDictionary <string, object> row, ExportType exportType)
{
var dico = new Dictionary <string, string>();
foreach (var field in row)
{
Property prop = Map.FindPropertyByColumnName(field.Key);
if (prop == null)
{
continue;
}
string value;
string key;
if (exportType == ExportType.Sql)
{
key = ExportDialect.Escape(field.Key);
var clrType = ExportDialect.GetClrType(prop.DbType, prop.IsNullable);
var converter = TypeConverterStore.GetConverterFactoryMethod(clrType);
var clrValue = converter(field.Value);
value = ExportDialect.GetValueAsSqlString(clrValue, prop);
}
else
{
key = field.Key;
value = DbTypeConverter.ConvertTo <string>(field.Value);
}
dico.Add(key, value);
}
return(dico);
}
private string QueryToExpression <TModel>(DataModelQuery <TModel> query) where TModel : DataModel
{
if (!string.IsNullOrEmpty(query.RawExpression))
{
return(query.RawExpression);
}
Type t = typeof(DataModel);
if (query.GetType().IsGenericType)
{
t = query.GetType().GetGenericArguments()[0];
}
var map = DataModelMap.GetEntityMapping(t);
var sb = new StringBuilder();
foreach (var condition in query.Conditions)
{
if (condition.EvalSubject != null &&
condition.FieldMap == null)
{
switch (condition.FindFieldMappingBy)
{
case FieldMappingKeyType.ClrMember:
condition.FieldMap = map.FieldMappings[condition.EvalSubject];
break;
case FieldMappingKeyType.DbColumn:
condition.FieldMap = map.GetFieldMappingByDbColumnName(condition.EvalSubject);
break;
}
}
if (condition.FieldMap != null)
{
if (sb.Length > 0)
{
sb.Append(" AND ");
}
sb.Append(condition.FieldMap.ColumnName);
sb.Append(CompareOpToExpression(condition.CompareOp));
sb.Append(ValueToExpression(condition.FieldMap, condition.CompareValue));
}
else
{
if (sb.Length > 0)
{
sb.Append(" AND ");
}
sb.Append(condition.EvalSubject);
sb.Append(CompareOpToExpression(condition.CompareOp));
if (condition.CompareOp != Compare.Null &&
condition.CompareOp != Compare.NotNull)
{
sb.Append(ValueToExpression(
DbTypeConverter.ToDbType(condition.CompareValue.GetType()),
condition.CompareValue));
}
}
}
return(sb.ToString());
}
protected void PrepareDataTable(SqliteDataReader reader, EbDataTable dt)
{
try
{
int _fieldCount = reader.FieldCount;
for (int i = 0; i < _fieldCount; i++)
{
dt.Columns.Add(new EbDataColumn
{
ColumnName = reader.GetName(i),
ColumnIndex = i,
Type = DbTypeConverter.ConvertToDbType(reader.GetFieldType(i))
});
}
while (reader.Read())
{
EbDataRow dr = dt.NewDataRow();
object[] oArray = new object[_fieldCount];
reader.GetValues(oArray);
dr.AddRange(oArray);
dt.Rows.Add(dr);
}
}
catch (Exception ex)
{
EbLog.Error(ex.Message);
}
}
public static void DbMigrationTest_ClassInitialize(TestContext context)
{
ServerTestHelper.ClassInitialize(context);
//运行测试前，这个库升级到最新的内容，同时它的历史记录需要清空
using (var c = new RafyDbMigrationContext(UnitTestEntityRepositoryDataProvider.DbSettingName))
{
c.RunDataLossOperation = DataLossOperation.All;
if (IsTestDbSQLite())
{
c.DeleteAllTables();
}
else
{
c.HistoryRepository = new DbHistoryRepository();
}
c.AutoMigrate();
c.ResetDbVersion();
if (!IsTestDbSQLite())
{
c.ResetHistory();
}
var dbProvider = DbMigrationProviderFactory.GetProvider(c.DbSetting);
dbTypeConverter = (dbProvider.CreateRunGenerator() as SqlRunGenerator).DbTypeCoverter;
};
}
/// <summary>
/// Executes the specified query and returns a list of <see cref="TResultType" />.
/// </summary>
/// <param name="whereClause">A where filter clause. Do not add the "WHERE" keyword to it. If you need to pass parameters, pass using @1, @2, @3.</param>
/// <param name="parameters">A list of parameter values.</param>
/// <returns>
/// A list of <see cref="TResultType" />.
/// </returns>
public async Task <List <TResultType> > ExecuteAsync(string whereClause = null, params object[] parameters)
{
var builder = new StringBuilder(this.CommandText);
var formatProvider = this.Connector.GetCommandFormatProvider();
if (!string.IsNullOrWhiteSpace(whereClause))
{
builder.AppendFormat(" WHERE {0}", whereClause);
}
using (var command = this.Connector.CreateCommand())
{
if (parameters != null)
{
for (var index = 0; index < parameters.Length; index++)
{
var oldName = $"@{index + 1}";
var newName = formatProvider.GetParameterName($"p{(index + 1)}");
builder.Replace(oldName, newName);
var parameter = parameters[index];
var type = parameter == null ? typeof(object) : parameter.GetType();
var commandParameter = command.AddParameter(newName, DbTypeConverter.FromType(type));
commandParameter.Value = parameter;
}
}
command.CommandText = builder.ToString();
return(await this.Connector.ExecuteReaderAsync(command, async reader => await this.Mapper.MapAsync(reader)));
}
}
public DbColumn(PropertyInfo propertyInfo)
{
PropertyInfo = propertyInfo;
var colAttr = propertyInfo.GetCustomAttribute <SqlColumnAttribute>();
if (colAttr != null)
{
ColumnName = colAttr.ColumnName;
Key = colAttr.Key;
Increment = colAttr.Increment;
Length = colAttr.Length;
Nullable = colAttr.Nullable;
DataType = colAttr.DataType;
}
if (string.IsNullOrEmpty(ColumnName))
{
ColumnName = propertyInfo.Name;
}
if (string.IsNullOrEmpty(DataType))
{
var columnType = propertyInfo.PropertyType;
if (columnType.IsGenericTypeDefinition && columnType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
Nullable = true;
columnType = columnType.GetGenericArguments()[0];
}
var p = DbTypeConverter.Lookup(columnType);
DataType = p.Key;
if (Length == 0)
{
Length = p.Value;
}
}
}
public IParameterQueryable Parameter(string paramId, object value)
{
currentParamId = paramId;
commandEngine.AddConfiguration(command =>
{
var param = command.CreateParameter();
param.ParameterName = paramId;
param.Value = value;
param.DbType = DbTypeConverter.From(value.GetType());
command.Parameters.Add(param);
Parameters.Add(param.ParameterName, param);
});
return(this);
}
internal PersistanceColumnInfo(
string name,
EntityPropertyMeta propertyMeta,
ColumnMeta columnMeta,
PersistanceTableInfo table,
DbTypeConverter dbTypeConverter
)
{
this.Table = table;
this.Name = name;
this.Meta = columnMeta;
this.PropertyType = TypeHelper.IgnoreNullable(propertyMeta.PropertyType);
this.DbType = columnMeta.DbType ?? dbTypeConverter.FromClrType(this.PropertyType);
this.Property = propertyMeta.ManagedProperty as IProperty;
}
private DbParameter AddParamInner <T>(string name, T val, ParameterDirection direction, int size = 0, byte precision = 0, byte scale = 0)
{
if (name is null)
{
throw new ArgumentNullException(nameof(name));
}
DbParameter param = _cmd.CreateParameter();
param.ParameterName = name;
param.Value = (object)val ?? DBNull.Value;
param.Direction = direction;
param.DbType = DbTypeConverter.ConvertToDbType <T>();
param.Size = size;
param.Precision = precision;
param.Scale = scale;
_cmd.Parameters.Add(param);
return(param);
}
void Load()
{
AddConverter(typeof(string), DbTypeConverter.ConvertTo <string>);
//byte
AddConverter(typeof(byte?), (x) => DbTypeConverter.ConvertTo <byte?>(x));
AddConverter(typeof(byte), (x) => DbTypeConverter.ConvertTo <byte>(x));
//Int16
AddConverter(typeof(short?), (x) => DbTypeConverter.ConvertTo <short?>(x));
AddConverter(typeof(short), (x) => DbTypeConverter.ConvertTo <short>(x));
//Int32
AddConverter(typeof(int?), (x) => DbTypeConverter.ConvertTo <int?>(x));
AddConverter(typeof(int), (x) => DbTypeConverter.ConvertTo <int>(x));
//Int64
AddConverter(typeof(long?), (x) => DbTypeConverter.ConvertTo <long?>(x));
AddConverter(typeof(long), (x) => DbTypeConverter.ConvertTo <long>(x));
//Datetime
AddConverter(typeof(DateTime?), (x) => DbTypeConverter.ConvertTo <DateTime?>(x));
AddConverter(typeof(DateTime), (x) => DbTypeConverter.ConvertTo <DateTime>(x));
//DatetimeOffset
AddConverter(typeof(DateTimeOffset?), (x) => DbTypeConverter.ConvertTo <DateTimeOffset?>(x));
AddConverter(typeof(DateTimeOffset), (x) => DbTypeConverter.ConvertTo <DateTimeOffset>(x));
//char
AddConverter(typeof(char?), (x) => DbTypeConverter.ConvertTo <char?>(x));
AddConverter(typeof(char), (x) => DbTypeConverter.ConvertTo <char>(x));
//boolean
AddConverter(typeof(bool?), (x) => DbTypeConverter.ConvertTo <bool?>(x));
AddConverter(typeof(bool), (x) => DbTypeConverter.ConvertTo <bool>(x));
//Guids
AddConverter(typeof(Guid?), (x) => DbTypeConverter.ConvertTo <Guid?>(x));
AddConverter(typeof(Guid), (x) => DbTypeConverter.ConvertTo <Guid>(x));
//single
AddConverter(typeof(float?), (x) => DbTypeConverter.ConvertTo <float?>(x));
AddConverter(typeof(float), (x) => DbTypeConverter.ConvertTo <float>(x));
//double
AddConverter(typeof(double?), (x) => DbTypeConverter.ConvertTo <double?>(x));
AddConverter(typeof(double), (x) => DbTypeConverter.ConvertTo <double>(x));
//decimal
AddConverter(typeof(decimal?), (x) => DbTypeConverter.ConvertTo <decimal?>(x));
AddConverter(typeof(decimal), (x) => DbTypeConverter.ConvertTo <decimal>(x));
}
public void TestParamConvert()
{
var dc = new DbTypeConverter();
var type = typeof(Student);
var ps = TypeExtensions.GetProperties(type, BindingFlags.Instance | BindingFlags.Public)
.Where(i => i.CanRead).ToList();
foreach (var item in _Students)
{
var ds = _Converter.GetConverter(type).Item1(item);
Assert.Equal(ps.Count, ds.Count);
for (int i = 0; i < ps.Count; i++)
{
Assert.Equal(dc.Convert(ps[i].PropertyType), ds[i].DbType);
Assert.Equal(ParameterDirection.Input, ds[i].Direction);
Assert.True(ds[i].IsNullable);
Assert.Equal("@" + ps[i].Name, ds[i].ParameterName);
var value = ps[i].GetMethod.Invoke(item, new object[0]) ?? DBNull.Value;
Assert.Equal(value, ds[i].Value);
}
}
}
public IParameterQueryable <TItem> Parameter(string paramId, Func <TItem, object> createValue)
{
currentParamId = paramId;
commandEngine.AddParamConfiguration((command, item) =>
{
var value = createValue(item);
if (command.Parameters.Contains(paramId))
{
command.Parameters[paramId].Value = value;
command.Parameters[paramId].DbType = DbTypeConverter.From(value.GetType());
}
else
{
var parameter = command.CreateParameter();
parameter.ParameterName = paramId;
parameter.Value = value;
parameter.DbType = DbTypeConverter.From(value.GetType());
command.Parameters.Add(parameter);
}
});
return(this);
}
public static void AppendTableDefinitionFrom(TextWriter tw, DataTable metaTable, string tempTableName)
{
tw.WriteLine();
tw.Write("CREATE TABLE {0} (", tempTableName);
tw.WriteLine();
int columnCount = 1;
foreach (DataColumn col in metaTable.Columns)
{
tw.Write("\t[{0}]\t{1}", col.ColumnName, DbTypeConverter.TranslateToSqlType(col.DataType).ToUpper());
if (columnCount++ < metaTable.Columns.Count)
{
tw.Write(",");
}
tw.WriteLine();
}
tw.Write(");");
tw.WriteLine();
}
public QueryBuilder CreateTempTableFor(DataTable queryResult, ObjectMetadata tableMeta)
{
sb.AppendFormat("CREATE TABLE {0} (", tableMeta.ToFullString());
sb.AppendLine();
int columnCount = 1;
foreach (DataColumn col in queryResult.Columns)
{
sb.AppendFormat("\t[{0}] {1}", col.ColumnName, DbTypeConverter.TranslateToSqlType(col.DataType).ToUpper());
if (columnCount++ < queryResult.Columns.Count)
{
sb.Append(",");
}
sb.AppendLine();
}
sb.Append(");");
sb.AppendLine();
return(this);
}
// this class have been deprecated.
public static void AppendTableDefinitionFrom(this StringBuilder sb, DataTable metaTable, string tempTableName)
{
sb.AppendLine();
sb.AppendFormat("CREATE TABLE {0} (", tempTableName);
sb.AppendLine();
int columnCount = 1;
foreach (DataColumn col in metaTable.Columns)
{
sb.AppendFormat("\t[{0}]\t{1}", col.ColumnName, DbTypeConverter.TranslateToSqlType(col.DataType).ToUpper());
if (columnCount++ < metaTable.Columns.Count)
{
sb.Append(",");
}
sb.AppendLine();
}
sb.Append(");");
sb.AppendLine();
}
public void ShouldConvertToDbType()
{
var i = DbTypeConverter.TranslateToSqlType(typeof(int));
var shrt = DbTypeConverter.TranslateToSqlType(typeof(short));
var l = DbTypeConverter.TranslateToSqlType(typeof(long));
var d = DbTypeConverter.TranslateToSqlType(typeof(decimal));
var s = DbTypeConverter.TranslateToSqlType(typeof(string));
var ba = DbTypeConverter.TranslateToSqlType(typeof(byte[]));
var date = DbTypeConverter.TranslateToSqlType(typeof(DateTime));
var flt = DbTypeConverter.TranslateToSqlType(typeof(float));
var gid = DbTypeConverter.TranslateToSqlType(typeof(Guid));
Assert.That(s.ToLower(), Is.EqualTo("nvarchar(max)"));
Assert.That(i.ToLower(), Is.EqualTo("int"));
Assert.That(shrt.ToLower(), Is.EqualTo("smallint"));
Assert.That(l.ToLower(), Is.EqualTo("bigint"));
Assert.That(d.ToLower(), Is.EqualTo("decimal(30,6)"));
Assert.That(ba.ToLower(), Is.EqualTo("varbinary(max)"));
Assert.That(date.ToLower(), Is.EqualTo("datetime2"));
Assert.That(flt.ToLower(), Is.EqualTo("real"));
Assert.That(gid.ToLower(), Is.EqualTo("uniqueidentifier"));
}
private static void ProcessManagedProperties(
Type type,
PersistanceTableInfo table,
EntityMeta em,
IDbIdentifierQuoter identifierProvider,
DbTypeConverter dbTypeConverter
)
{
foreach (var property in em.EntityProperties)
{
var meta = property.ColumnMeta;
if (meta == null)
{
continue;
}
var propertyName = property.Name;
//生成 ManagedPropertyBridge
var epm = em.Property(propertyName);
if (epm == null)
{
throw new ArgumentNullException(string.Format("{0}.{1} 属性需要使用托管属性进行编写。", type.FullName, propertyName));
}
var columnName = identifierProvider.Prepare(meta.ColumnName);
var column = new PersistanceColumnInfo(columnName, epm, meta, table, dbTypeConverter);
if (meta.IsPrimaryKey)
{
table.PKColumn = column;
}
table.Columns.Add(column);
}
}
public void Init()
{
// Arrange.
this.converter = new DbTypeConverter();
}
public static OleDbType GetOleDbType(DbType dbType)
{
return(DbTypeConverter.ToOleDbType(dbType));
}
internal ModelDiffer(DbTypeConverter dbTypeConverter)
{
this.dbTypeConverter = dbTypeConverter;
}
/// <summary>
/// Returns a generated array of <see cref="DbParameter"/>s
/// that should be passed in with the command.
/// </summary>
/// <returns></returns>
protected virtual DbParameter[] CreateParameters()
{
// add insert/update values
var ret = new List <DbParameter>();
foreach (var colmap_kvp in ModelMap.FieldMappings)
{
string fld = colmap_kvp.Key;
DataModelColumnAttribute colmap = colmap_kvp.Value;
DbParameter param = null;
switch (StatementCommand)
{
case SqlStatement.INSERT:
if (string.IsNullOrEmpty(colmap.InsertParam))
{
continue;
}
if (colmap.IncludeOnInsert ||
colmap.ReturnAsOutputOnInsert)
{
param = Provider.DbFactory.CreateParameter();
param.ParameterName = !colmap.InsertParam.StartsWith("@")
? "@" + colmap.InsertParam
: colmap.InsertParam;
if (colmap.ReturnAsOutputOnInsert)
{
param.Direction = ParameterDirection.Output;
}
}
break;
case SqlStatement.UPDATE:
if (string.IsNullOrEmpty(colmap.UpdateParam))
{
continue;
}
bool add = true;
if (!CommandBuilder.SetAllValues)
{
add = DataModel.ModifiedProperties.Contains(fld);
}
if (add)
{
param = Provider.DbFactory.CreateParameter();
param.ParameterName = !colmap.UpdateParam.StartsWith("@")
? "@" + colmap.UpdateParam
: colmap.UpdateParam;
}
break;
}
if (param != null && ret.Find(p => p.ParameterName == param.ParameterName) == null)
{
if (param is SqlParameter)
{
((SqlParameter)param).SqlDbType = colmap.SqlDbType;
}
else
{
param.DbType = colmap.DbType;
}
if (colmap.ColumnSize.HasValue)
{
param.Value = colmap.ColumnSize.Value;
}
else if (colmap.DataType == typeof(string))
{
param.Size = (DataModel[fld] ?? string.Empty).ToString().Length;
}
if (param.Direction == ParameterDirection.Input ||
param.Direction == ParameterDirection.InputOutput)
{
param.IsNullable = colmap.IsNullable;
param.Value = DataModel[fld] ?? DBNull.Value;
}
ret.Add(param);
}
}
foreach (var foreignMapping_kvp in ModelMap.ForeignModelMappings)
{
ForeignDataModelAttribute foreignMapping = foreignMapping_kvp.Value;
if (foreignMapping.TargetMemberType.IsOrInherits(typeof(IEnumerable)))
{
continue;
}
switch (StatementCommand)
{
case SqlStatement.INSERT:
var fieldValue = DataModel.ColumnMappedValue[foreignMapping.LocalColumn];
var paramName = "@" + foreignMapping.LocalColumn;
if (ret.Find(p => p.ParameterName == paramName) == null)
{
if (ret.Find(p => p.ParameterName == paramName) != null)
{
continue;
}
var param = Provider.DbFactory.CreateParameter();
param.ParameterName = paramName;
if (param is SqlParameter)
{
((SqlParameter)param).SqlDbType = foreignMapping.LocalColumnSqlDbType;
}
else
{
param.DbType = foreignMapping.LocalColumnDbType;
}
if (foreignMapping.LocalColumnSize.HasValue)
{
param.Value = foreignMapping.LocalColumnSize.Value;
}
else if (foreignMapping.TargetMemberType == typeof(string))
{
param.Size = (fieldValue ?? string.Empty).ToString().Length;
}
if (param.Direction == ParameterDirection.Input ||
param.Direction == ParameterDirection.InputOutput)
{
param.IsNullable = foreignMapping.LocalColumnIsNullable;
param.Value = fieldValue ?? DBNull.Value;
}
ret.Add(param);
}
break;
}
}
// add query conditions
if (Query != null)
{
foreach (DataModelQueryCondition <TModel> cond in Query.Conditions)
{
DataModelColumnAttribute fm = cond.FieldMap;
if (fm == null)
{
switch (cond.FindFieldMappingBy)
{
case FieldMappingKeyType.ClrMember:
fm = ModelMap[cond.EvalSubject];
break;
case FieldMappingKeyType.DbColumn:
fm = ModelMap.GetFieldMappingByDbColumnName(cond.EvalSubject);
break;
}
}
string paramName = string.Empty;
if (fm != null)
{
switch (StatementCommand)
{
case SqlStatement.SELECT:
if (string.IsNullOrEmpty(fm.SelectParam))
{
continue;
}
paramName = fm.SelectParam;
break;
case SqlStatement.INSERT:
if (string.IsNullOrEmpty(fm.InsertParam))
{
continue;
}
paramName = fm.InsertParam;
break;
case SqlStatement.UPDATE:
if (string.IsNullOrEmpty(fm.UpdateParam))
{
continue;
}
paramName = fm.UpdateParam;
break;
case SqlStatement.DELETE:
if (string.IsNullOrEmpty(fm.DeleteParam))
{
continue;
}
paramName = fm.DeleteParam;
break;
}
if (ret.Find(p => p.ParameterName == paramName) == null)
{
if (UsesSproc && cond.CompareOp != Compare.Equal)
{
throw new InvalidOperationException(
"Cannot produce an ad hoc WHERE clause with a SQL Stored Procedure.");
}
DbParameter param = Provider.DbFactory.CreateParameter();
param.ParameterName = !paramName.StartsWith("@")
? "@" + paramName
: paramName;
if (param is SqlParameter)
{
((SqlParameter)param).SqlDbType = fm.SqlDbType;
}
else
{
param.DbType = fm.DbType;
}
if (fm.ColumnSize.HasValue)
{
param.Value = fm.ColumnSize.Value;
}
else if (fm.DataType == typeof(string))
{
fm.ColumnSize =
(cond.CompareValue ?? string.Empty).ToString().Length;
}
param.IsNullable = fm.IsNullable;
param.Value = cond.CompareValue ?? DBNull.Value;
ret.Add(param);
}
}
else
{
if (cond.CompareValue != null)
{
paramName = "@" + cond.EvalSubject;
if (ret.Find(p => p.ParameterName == paramName) == null)
{
DbParameter param = Provider.DbFactory.CreateParameter();
param.ParameterName = paramName;
if (param is SqlParameter)
{
((SqlParameter)param).SqlDbType
= DbTypeConverter.ToSqlDbType(cond.CompareValue);
}
else
{
param.DbType = DbTypeConverter.ToDbType(cond.CompareValue);
}
if (cond.CompareValue is string)
{
param.Size = ((string)cond.CompareValue).Length;
}
param.IsNullable = true;
param.Value = cond.CompareValue;
ret.Add(param);
}
}
}
}
}
return(ret.ToArray());
}
/// <summary>
/// 設定命令參數的資料型別。
/// </summary>
/// <param name="parameter">命令參數。</param>
/// <param name="dbType">欄位資料型別。</param>
public virtual void SetParameterDbType(DbParameter parameter, EFieldDbType dbType)
{
parameter.DbType = DbTypeConverter.ToDbType(dbType);
}
public override string ToString()
{
return(DbTypeConverter.ConvertFromGenericDbType(type, Program.AppSet.ProviderType.ToString()) + extra);
}
/// <summary>
/// Gets the select command ready to execute the select query.
/// </summary>
/// <param name="whereClause">An optional where clause to add to the query. If the where contains parameters, they need to be named as @1 @2 @3 etc.</param>
/// <param name="parameters">A list of parameter values.</param>
/// <returns>
/// A select command already parametrized to execute.
/// </returns>
public IDatabaseCommand GetCommand(string whereClause = null, params object[] parameters)
{
if (string.IsNullOrWhiteSpace(whereClause))
{
this.Command.CommandText = this.CommandText;
return(this.Command);
}
whereClause = $"WHERE {whereClause}";
this.Command.CommandText = $"{this.CommandText} {whereClause}";
this.Command.ClearParameters();
if (parameters != null)
{
for (var index = 0; index < parameters.Length; index++)
{
var parameter = parameters[index];
var type = parameter == null ? typeof(object) : parameter.GetType();
var commandParameter = this.Command.AddParameter(this.FormatProvider.GetParameterName((index + 1).ToString()), DbTypeConverter.FromType(type));
commandParameter.Value = parameter ?? DBNull.Value;
}
}
return(this.Command);
}
/// <summary>
/// Executes the specified query and returns a list of <see cref="TResultType" />.
/// </summary>
/// <param name="whereClause">A where filter clause. Do not add the "WHERE" keyword to it. If you need to pass parameters, pass using @1, @2, @3.</param>
/// <param name="parameters">A list of parameter values.</param>
/// <returns>
/// A list of <see cref="TResultType" />.
/// </returns>
public List <TResultType> Execute(string whereClause = null, params object[] parameters)
{
if (!string.IsNullOrWhiteSpace(whereClause))
{
this.Command.CommandText = $"{this.CommandText} WHERE {whereClause}";
this.Command.ClearParameters();
if (parameters != null)
{
for (var index = 0; index < parameters.Length; index++)
{
var parameter = parameters[index];
var type = parameter == null ? typeof(object) : parameter.GetType();
var commandParameter = this.Command.AddParameter($"@{index + 1}", DbTypeConverter.FromType(type));
commandParameter.Value = parameter ?? DBNull.Value;
}
}
}
return(this.Connector.ExecuteReader(this.Command, reader => this.Mapper.Map(reader)));
}
public void ConvertToDbTypeTest()
{
// Arrange.
var converter = new DbTypeConverter();
// Act / Assert.
Assert.AreEqual(DbType.AnsiString, converter.ConvertToDbType(DbDataType.AnsiString));
Assert.AreEqual(DbType.Binary, converter.ConvertToDbType(DbDataType.Binary));
Assert.AreEqual(DbType.Byte, converter.ConvertToDbType(DbDataType.Byte));
Assert.AreEqual(DbType.Boolean, converter.ConvertToDbType(DbDataType.Boolean));
Assert.AreEqual(DbType.Currency, converter.ConvertToDbType(DbDataType.Currency));
Assert.AreEqual(DbType.Date, converter.ConvertToDbType(DbDataType.Date));
Assert.AreEqual(DbType.DateTime, converter.ConvertToDbType(DbDataType.DateTime));
Assert.AreEqual(DbType.Decimal, converter.ConvertToDbType(DbDataType.Decimal));
Assert.AreEqual(DbType.Double, converter.ConvertToDbType(DbDataType.Double));
Assert.AreEqual(DbType.Guid, converter.ConvertToDbType(DbDataType.Guid));
Assert.AreEqual(DbType.Int16, converter.ConvertToDbType(DbDataType.Int16));
Assert.AreEqual(DbType.Int32, converter.ConvertToDbType(DbDataType.Int32));
Assert.AreEqual(DbType.Int64, converter.ConvertToDbType(DbDataType.Int64));
Assert.AreEqual(DbType.Object, converter.ConvertToDbType(DbDataType.Object));
Assert.AreEqual(DbType.SByte, converter.ConvertToDbType(DbDataType.SByte));
Assert.AreEqual(DbType.Single, converter.ConvertToDbType(DbDataType.Single));
Assert.AreEqual(DbType.String, converter.ConvertToDbType(DbDataType.String));
Assert.AreEqual(DbType.Time, converter.ConvertToDbType(DbDataType.Time));
Assert.AreEqual(DbType.UInt16, converter.ConvertToDbType(DbDataType.UInt16));
Assert.AreEqual(DbType.UInt32, converter.ConvertToDbType(DbDataType.UInt32));
Assert.AreEqual(DbType.UInt64, converter.ConvertToDbType(DbDataType.UInt64));
Assert.AreEqual(DbType.VarNumeric, converter.ConvertToDbType(DbDataType.VarNumeric));
Assert.AreEqual(DbType.AnsiStringFixedLength, converter.ConvertToDbType(DbDataType.AnsiStringFixedLength));
Assert.AreEqual(DbType.StringFixedLength, converter.ConvertToDbType(DbDataType.StringFixedLength));
Assert.AreEqual(DbType.Xml, converter.ConvertToDbType(DbDataType.Xml));
Assert.AreEqual(DbType.DateTime2, converter.ConvertToDbType(DbDataType.DateTime2));
Assert.AreEqual(DbType.DateTimeOffset, converter.ConvertToDbType(DbDataType.DateTimeOffset));
}
public static SqlDbType GetSqlDbType(DbType dbType)
{
return(DbTypeConverter.ToSqlDbType(dbType));
}
private void LoadColumn(ColumnElement columnElement, Table table)
{
if (columnElement == null)
{
return;
}
Columns.Add(new Tuple <string, string, DbType, int>(table.GetNameWithSchema(), columnElement.Name, DbTypeConverter.FromDataType(columnElement.DataType), columnElement.Length));
if (columnElement.IsPrimaryKey)
{
PrimaryKeys.Add(new Tuple <string, string>(table.GetNameWithSchema(), columnElement.Name));
}
if (!string.IsNullOrWhiteSpace(columnElement.ForeignKey))
{
var parts = columnElement.ForeignKey.Split('.');
var schemaName = parts.Length > 2 ? parts[0] : null;
var objectName = parts.Length > 2 ? parts[1] : parts[0];
var columnName = parts.Length > 2 ? parts[2] : parts[1];
ForeignKeys.Add(new ForeignKey(new ObjectName(table.Schema, table.ActualName), new[] { columnElement.Name }, new ObjectName(schemaName, objectName), new[] { columnName }));
}
}
public static Type GetSystemType(DbType dbType)
{
return(DbTypeConverter.ToSystemType(dbType));
}
/// <summary>
/// Populates the command parameters.
/// </summary>
/// <typeparam name="TParameters">The type of the parameters.</typeparam>
/// <param name="connector">Reference to a database connector.</param>
/// <param name="command">The command.</param>
/// <param name="descriptor">The routine type descriptor.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="ignoreProperties">The ignore properties.</param>
/// <exception cref="System.ArgumentNullException">descriptor - descriptor</exception>
private static void PopulateCommandParameters <TParameters>(IDatabaseConnector connector, IDatabaseCommand command, IRoutineTypeDescriptor descriptor, TParameters parameters, string[] ignoreProperties)
{
if (descriptor == null)
{
throw new ArgumentNullException(nameof(descriptor), $"{nameof(descriptor)} can not be null.");
}
var formatProvider = connector.GetCommandFormatProvider();
foreach (var parameter in descriptor.Parameters)
{
if (ignoreProperties != null && ignoreProperties.Contains(parameter.ParameterName))
{
continue;
}
var commandParameter = command.AddParameter(formatProvider.GetParameterName(parameter.ParameterName), DbTypeConverter.FromType(parameter.Type));
commandParameter.Direction = parameter.IsInput ? ParameterDirection.Input : ParameterDirection.Output;
if (parameters == null || parameters.Equals(default(TParameters)))
{
continue;
}
var value = parameter.PropertyInfo.GetValue(parameters);
commandParameter.Value = value ?? DBNull.Value;
}
}
public static IInsertQuery CreateInsertQuery(string db)
{
return(CreateInsertQuery(DbTypeConverter.Convert(db)));
}
