public IEnumerable <T> Query <T>(Expression <Func <T, bool> > where, int?commandTimeout = null)
{
var builder = new StringBuilder();
var currenttype = typeof(T);
builder.Append(" where ");
var queryProperties = new List <QueryParameter>();
QueryResult result = null;
if (where.Body is BinaryExpression)
{
var body = (BinaryExpression) where.Body;
// Percorre a arvore de expressão e devolve os parametros da consulta
WalkTree(body, ExpressionType.Default, ref queryProperties);
result = DynamicWhere(queryProperties, currenttype);
}
else if (where.Body is MethodCallExpression)
{
var body = where.Body as MethodCallExpression;
var methodName = body.Method.Name;
var propertyName = GetPropertyName(body);
var propertyValue = ((body.Arguments[0] as ConstantExpression).Value);
var link = GetOperator(ExpressionType.Default);
var opr = string.Empty;
switch (methodName)
{
case "Contains":
opr = " LIKE ";
propertyValue = string.Format("%{0}%", propertyValue);
break;
case "StartsWith":
opr = " LIKE ";
propertyValue = string.Format("{0}%", propertyValue);
break;
case "EndsWith":
opr = " LIKE ";
propertyValue = string.Format("%{0}", propertyValue);
break;
}
queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr));
result = DynamicWhere(queryProperties, currenttype);
}
CreateOrReuseConnection();
return(DapperExt.ExecuteQuey <T>(_connection, result.Sql, result.Param, GetCurrentTransaction()));
}
public void GetCountTest2()
{
var sql = DapperExt.GetCount("id = @w_id", new { w_id = 1 }, "myt");
var exp = "SELECT COUNT(*) FROM myt WHERE id = @w_id";
Assert.Equal(exp, sql);
}
/// <summary>
/// Retorna o último comando SQL que foi executado
/// </summary>
/// <returns></returns>
public string GetSQL()
{
if (string.IsNullOrEmpty(_sql))
{
_sql = DapperExt.GetSQL();
}
return(_sql);
}
public void GetCountTest()
{
var con = new { id = 1 };
var sql = DapperExt.GetCount(con, "myt");
var exp = "SELECT COUNT(*) FROM myt WHERE id = @w_id";
Assert.Equal(exp, sql);
}
private string DynamicWhere2(List <QueryParameter> queryProperties, Type currentType)
{
var builder = new StringBuilder();
builder.Append(" Where ");
for (int i = 0; i < queryProperties.Count(); i++)
{
QueryParameter item = queryProperties[i];
var fieldType = GePropertyInfo(currentType, item.PropertyName); // currenttype.GetField(item.PropertyName, BindingFlags.PutRefDispProperty );
var columnTable = string.Empty;
var param = string.Empty;
if (fieldType != null)
{
columnTable = fieldType.GetCustomAttribute <ColumnAttribute>().Name;
param = columnTable;
}
var dialeto = DapperExt.GetDialect();
var prefixParam = dialeto == "Oracle" ? ":" : "@";
if (!string.IsNullOrEmpty(columnTable))
{
if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0)
{
//builder.Append(string.Format("{0} {1} {2} @{1} ", item.LinkingOperator, item.PropertyName, item.QueryOperator));
builder.Append(string.Format("{0} {1} {2} {3} ", item.LinkingOperator, DapperExt.Encapsulate(columnTable), item.QueryOperator, item.PropertyValue));
}
else
{
//builder.Append(string.Format("{0} {1} @{0} ", item.PropertyName, item.QueryOperator));
builder.Append(string.Format("{0} {1} {2} ", DapperExt.Encapsulate(columnTable), item.QueryOperator, item.PropertyValue));
}
}
else
{
if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0)
{
builder.Append(string.Format("{0} {1} {2} {3} ", item.LinkingOperator, item.PropertyName, item.QueryOperator, item.PropertyValue));
}
else
{
builder.Append(string.Format("{0} {1} {2} ", item.PropertyName, item.QueryOperator, item.PropertyValue));
}
}
}
return(builder.ToString());
}
public void InsertTest()
{
var data = new Demo {
Id = 1, Name = "cat"
};
var sql = DapperExt.Insert(data, "myt");
var exp = "INSERT INTO myt (Id, Name, Ext1, Ext2, Ext3) VALUES (@Id, @Name, @Ext1, @Ext2, @Ext3)";
Assert.Equal(exp, sql);
}
public void UpdateTest2()
{
var data = new UpdateDemo2 {
Name = "cat", Ext1 = "ext1"
};
var con = new { id = 1 };
var sql = DapperExt.Update(data, con, "myt");
var exp = "UPDATE myt SET myName = @Name, Ext1 = @Ext1, Ext2 = @Ext2 WHERE id = @w_id";
Assert.Equal(exp, sql);
}
public void UpdateNotNullTest()
{
var data = new UpdateDemo {
Name = "cat", Ext1 = "ext1"
};
var con = new { id = 1 };
var sql = DapperExt.UpdateNotNull(data, con, "myt");
var exp = "UPDATE myt SET Name = @Name, Ext1 = @Ext1 WHERE id = @w_id";
Assert.Equal(exp, sql);
}
public void UpdateModelWithIdTest2()
{
var data = new UpdateDemo4 {
Id = 1, Name = "cat"
};
var con = new { id = 1 };
var sql = DapperExt.Update(data, con, "myt");
var exp = "UPDATE myt SET a_id = @Id, last_name = @Name WHERE id = @w_id";
Assert.Equal(exp, sql);
}
public BaseRepository(string connectionString, string schema)
{
_connectionString = connectionString;
_schema = schema;
if (string.IsNullOrEmpty(OracleConfiguration.TnsAdmin))
{
OracleConfiguration.TnsAdmin = @"E:\app\client\jcarlos\product\12.2.0\client_1\Network\Admin";
OracleConfiguration.WalletLocation = @"E:\app\client\jcarlos\product\12.2.0\client_1\Network\Admin";
}
DapperExt.SetSchemaName(_schema);
}
/// <summary>
/// Usar esse construtor quando a leitura da string de conexao não usuar o App.Config
/// </summary>
/// <param name="connectionStringName"></param>
/// <param name="schema"></param>
/// <param name="dataBaseDialect"></param>
/// <param name="dataBaseProvider"></param>
public DbContext(string connectionString, string schema = "Admin", string dataBaseDialect = "Oracle", string dataBaseProvider = "Oracle.ManagedDataAccess.Client") //"Npgsql"
{
_connectionFactory = DbProviderFactories.GetFactory(dataBaseProvider);
_connectionString = connectionString;
_dataBaseProvider = dataBaseProvider;
_schema = schema;
var dialeto = dataBaseDialect;
switch (dialeto)
{
case "PostgreSQL":
DapperExt.SetDialect(Dialect.PostgreSQL);
break;
case "SQLite":
DapperExt.SetDialect(Dialect.SQLite);
break;
case "MySQL":
DapperExt.SetDialect(Dialect.MySQL);
break;
case "Oracle":
DapperExt.SetDialect(Dialect.Oracle);
break;
case "SQLServer":
DapperExt.SetDialect(Dialect.SQLServer);
break;
default:
DapperExt.SetDialect(Dialect.PostgreSQL);
break;
}
DapperExt.SetSchemaName(_schema);
}
public TEntidade ProcurarBySQL(string sql)
{
OracleDataReader reader;
TEntidade result = default(TEntidade);
try
{
using (OracleConnection connection = new OracleConnection(_connectionString))
{
reader = (OracleDataReader)((IWrappedDataReader)connection.ExecuteReader(sql)).Reader;
if (reader != null && reader.HasRows)
{
result = DapperExt.ConvertReaderToObject <TEntidade>(reader);
}
}
}
catch (Exception)
{
throw;
}
return(result);
}
private QueryResult DynamicWhere(List <QueryParameter> queryProperties, Type currentType)
{
IDictionary <string, Object> expando = new ExpandoObject();
QueryResult result = null;
var builder = new StringBuilder();
builder.Append(" where ");
for (int i = 0; i < queryProperties.Count(); i++)
{
QueryParameter item = queryProperties[i];
var fieldType = GePropertyInfo(currentType, item.PropertyName); // currenttype.GetField(item.PropertyName, BindingFlags.PutRefDispProperty );
var columnTable = string.Empty;
var param = string.Empty;
if (fieldType != null && fieldType.GetCustomAttribute <ColumnAttribute>() != null)
{
columnTable = fieldType.GetCustomAttribute <ColumnAttribute>().Name;
param = columnTable;
}
else if (fieldType != null && fieldType.CustomAttributes.Count() > 0)
{
//CustomAttributeData customAttrib = fieldType.GetCustomAttributesData().First(x => x.AttributeType == typeof(ColumnAttribute));
//if (customAttrib != null )
//{
// columnTable = ((ColumnAttribute)customAttrib).Name;
// param = columnTable;
//}
foreach (var atrib in fieldType.GetCustomAttributesData())
{
if (atrib.AttributeType == typeof(System.ComponentModel.DataAnnotations.Schema.ColumnAttribute) || atrib.AttributeType == typeof(Dapper.DapperExt.ColumnAttribute))
{
string attributeTypeName = atrib.AttributeType.Name;
columnTable = atrib.ConstructorArguments[0].Value?.ToString();
param = columnTable;
}
//else if (atrib.AttributeType == typeof(Dapper.DapperExt.ColumnAttribute))
//{
// columnTable = (Dapper.DapperExt.ColumnAttribute)atrib).Name;
// param = columnTable;
//}
}
}
var dialeto = DapperExt.GetDialect();
var prefixParam = dialeto == "Oracle" ? ":" : "@";
if (!string.IsNullOrEmpty(columnTable))
{
if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0)
{
//builder.Append(string.Format("{0} {1} {2} @{1} ", item.LinkingOperator, item.PropertyName, item.QueryOperator));
builder.Append(string.Format("{0} {1} {2} {3} ", item.LinkingOperator, DapperExt.Encapsulate(columnTable), item.QueryOperator, prefixParam + param));
}
else
{
//builder.Append(string.Format("{0} {1} @{0} ", item.PropertyName, item.QueryOperator));
builder.Append(string.Format("{0} {1} {2} ", DapperExt.Encapsulate(columnTable), item.QueryOperator, prefixParam + param));
}
expando[columnTable] = item.PropertyValue;
}
else
{
if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0)
{
builder.Append(string.Format("{0} {1} {2} {3} ", item.LinkingOperator, item.PropertyName, item.QueryOperator, prefixParam + item.PropertyName));
}
else
{
builder.Append(string.Format("{0} {1} {2} ", item.PropertyName, item.QueryOperator, prefixParam + item.PropertyName));
}
expando[item.PropertyName] = item.PropertyValue;
}
}
result = new QueryResult(builder.ToString().TrimEnd(), expando);
return(result);
}
public T Get <T>(Expression <Func <T, bool> > where)
{
var builder = new StringBuilder();
var sql = new StringBuilder();
var currenttype = typeof(T);
var tableName = DapperExt.GetTableName(currenttype);
sql.Append("Select ");
BuildSelect(sql, GetScaffoldableProperties <T>().ToArray());
sql.AppendFormat(" From {0} ", tableName);
var queryProperties = new List <QueryParameter>();
if (where.Body is BinaryExpression)
{
var body = (BinaryExpression) where.Body;
// Percorre a arvore de expressão e devolve os parametros da consulta
WalkTree(body, ExpressionType.Default, ref queryProperties);
sql.Append(DynamicWhere2(queryProperties, currenttype));
}
else if (where.Body is MethodCallExpression)
{
var body = where.Body as MethodCallExpression;
var methodName = body.Method.Name;
var propertyName = GetPropertyName(body);
var propertyValue = ((body.Arguments[0] as ConstantExpression).Value);
var link = GetOperator(ExpressionType.Default);
var opr = string.Empty;
switch (methodName)
{
case "Contains":
opr = " LIKE ";
propertyValue = string.Format("%{0}%", propertyValue);
break;
case "StartsWith":
opr = " LIKE ";
propertyValue = string.Format("{0}%", propertyValue);
break;
case "EndsWith":
opr = " LIKE ";
propertyValue = string.Format("%{0}", propertyValue);
break;
}
queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr));
sql.Append(DynamicWhere2(queryProperties, currenttype));
}
CreateOrReuseConnection();
var registro = _connection.ExecuteQuey <T>(sql.ToString()).FirstOrDefault(); // Query<T>(sql.ToString(),result.Param).FirstOrDefault();
return(registro);
}
