public void ExecuteSQL_With_Transaction()
{
string sql = "insert into Aluno(nota,curso) values(@nota,@curso) ";
List <Parameter> parameters = new List <Parameter>();
SqlAndParameters sqlParameters = new SqlAndParameters();
connectionSql.DbConnectionBase.Open();
connectionSql.DbTransaction = connectionSql.DbConnectionBase.BeginTransaction();
parameters.Add(new Parameter {
ParameterKey = "@nota", ParameterValue = 50
});
parameters.Add(new Parameter {
ParameterKey = "@curso", ParameterValue = "PttAP2"
});
sqlParameters.Sql = sql;
sqlParameters.Parameter = parameters;
queryBuilder.ExecuteSql(sqlParameters);
connectionSql.DbTransaction.Rollback();
Assert.IsTrue(true);
}
public bool any(SqlAndParameters sqlParameter)
{
bool result = false;
IDataReader readerResult = null;
try
{
ValidateConnection();
using (var sqlComand = DbConnection.DbConnectionBase.CreateCommand())
{
if (DbConnection?.DbTransaction != null)
{
sqlComand.Transaction = DbConnection.DbTransaction;
}
if (sqlComand.Parameters != null)
{
sqlComand.SetParameters(sqlParameter);
}
// set sql
sqlComand.CommandText = sqlParameter.Sql;
// set command type
sqlComand.CommandType = sqlParameter.isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
// get and set values
readerResult = sqlComand.ExecuteReader();
while (readerResult.Read())
{
if (readerResult[0] != null)
{
result = true;
}
}
}
}
catch (Exception)
{
throw;
}
finally
{
if (readerResult != null && !readerResult.IsClosed)
{
readerResult.Close();
}
if (DbConnection.canClose)
{
DbConnection.DbConnectionBase.Close();
}
}
return(result);
}
public DataTable ExecuteSqlGetDataTable(SqlAndParameters sqlParameter)
{
DataSet dstable = new DataSet();
IDbCommand sqlComand;
try
{
ValidateConnection();
if (DbConnection?.SetAdapter == null)
{
throw new Exception("Object SetAdapter can not be null, initialize!");
}
// initialize sqlcommand
sqlComand = DbConnection.DbConnectionBase.CreateCommand();
if (DbConnection?.DbTransaction != null)
{
sqlComand.Transaction = DbConnection.DbTransaction;
}
if (sqlComand.Parameters != null)
{
sqlComand.SetParameters(sqlParameter);
}
// set sql
sqlComand.CommandText = sqlParameter.Sql;
// set command type
sqlComand.CommandType = sqlParameter.isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
// set command on adapter
DbConnection.SetAdapter.SelectCommand = sqlComand;
// set get data
DbConnection.SetAdapter.Fill(dstable);
}
catch (Exception)
{
throw;
}
finally
{
if (DbConnection.canClose)
{
DbConnection.DbConnectionBase.Close();
}
}
return(dstable.Tables[0]);
}
public T ExecuteSqlGetObject <T>(SqlAndParameters sqlParameter) where T : new()
{
T objectType = new T();
IDataReader readerResult = null;
try
{
ValidateConnection();
using (var sqlComand = DbConnection.DbConnectionBase.CreateCommand())
{
if (DbConnection?.DbTransaction != null)
{
sqlComand.Transaction = DbConnection.DbTransaction;
}
if (sqlComand?.Parameters != null)
{
sqlComand.SetParameters(sqlParameter);
}
// set sql
sqlComand.CommandText = sqlParameter.Sql;
// set command type
sqlComand.CommandType = sqlParameter.isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
// get and set values
readerResult = sqlComand.ExecuteReader(CommandBehavior.SingleRow);
// map data from reader to new object
objectType = Mappers.Mapper.Map <T>(readerResult).FirstOrDefault();
}
}
catch (Exception)
{
throw;
}
finally
{
if (readerResult != null && !readerResult.IsClosed)
{
readerResult.Close();
}
if (DbConnection.canClose)
{
DbConnection.DbConnectionBase.Close();
}
}
return(objectType);
}
public static void SetParameters(this IDbCommand command, SqlAndParameters sqlParameter)
{
// check befor use
sqlParameter?.Parameter?.ForEach(keyValueParameter =>
{
IDbDataParameter parameters = command.CreateParameter();
parameters.ParameterName = keyValueParameter.ParameterKey;
parameters.Value = keyValueParameter.ParameterValue;
command.Parameters.Add(parameters);
});
}
public void ODBC_ExecuteSqlGetDataTable()
{
StringBuilder builder = new StringBuilder();
SqlAndParameters sqlParameters = new SqlAndParameters();
System.Data.DataTable table = null;
builder.Append("Select * from c02 where CFB='' and CF=1 ");
sqlParameters.Sql = builder.ToString();
table = queryBuilder.ExecuteSqlGetDataTable(sqlParameters);
Assert.IsTrue(table.Rows.Count > 0);
}
public IEnumerable <T> ExecuteSqlGetObjectList <T>(SqlAndParameters sqlParameter) where T : new()
{
IEnumerable <T> objectTypeList;
try
{
ValidateConnection();
using (var sqlComand = DbConnection.DbConnectionBase?.CreateCommand())
{
if (DbConnection?.DbTransaction != null)
{
sqlComand.Transaction = DbConnection.DbTransaction;
}
if (sqlComand.Parameters != null)
{
sqlComand.SetParameters(sqlParameter);
}
// set sql
sqlComand.CommandText = sqlParameter.Sql;
// set command type
sqlComand.CommandType = sqlParameter.isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
// get values from reader
IDataReader readerResult = sqlComand.ExecuteReader();
// get list of object from reader
objectTypeList = Mappers.Mapper.Map <T>(readerResult);
}
}
catch (Exception)
{
throw;
}
finally
{
if (DbConnection.canClose)
{
DbConnection.DbConnectionBase.Close();
}
}
return(objectTypeList);
}
/// <summary>
/// Executes the SQL get single.
/// </summary>
/// <param name="sqlParameter">The SQL parameter.</param>
/// <param name="DbConnection">The database connection.</param>
/// <returns></returns>
/// <exception cref="System.Exception">Object connection or connectionString is null.</exception>
public object ExecuteSqlGetSingle(SqlAndParameters sqlParameter)
{
object total = null;
try
{
isConnectionStringValid();
using (var sqlComand = DbConnection.DbConnectionBase?.CreateCommand())
{
if (DbConnection?.DbTransaction != null)
{
sqlComand.Transaction = DbConnection.DbTransaction;
}
if (sqlComand.Parameters != null)
{
sqlComand.SetParameters(sqlParameter);
}
// set sql
sqlComand.CommandText = sqlParameter.Sql;
// set command type
sqlComand.CommandType = sqlParameter.isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
// get values from reader
var totalScalar = sqlComand.ExecuteScalar();
// get list of object from reader
total = Convert.IsDBNull(totalScalar) ? total : totalScalar;
}
}
catch (Exception)
{
throw;
}
finally
{
if (DbConnection.canClose)
{
DbConnection.DbConnectionBase.Close();
}
}
return(total);
}
public void ExecuteSqlGetAny()
{
string sql = "select * from Aluno";
List <Parameter> parameters = new List <Parameter>();
SqlAndParameters sqlParameters = new SqlAndParameters();
bool hasAny = false;
sqlParameters.Sql = sql;
hasAny = queryBuilder.any(sqlParameters);
Console.WriteLine(hasAny);
Assert.IsTrue(hasAny);
}
public void ExecuteSqlGetObject()
{
string sql = "select * from Aluno where alunoID=@alunoID";
List <Parameter> parameters = new List <Parameter>();
SqlAndParameters sqlParameters = new SqlAndParameters();
Aluno aluno = new Aluno();
parameters.Add(new Parameter {
ParameterKey = "@alunoID", ParameterValue = 1
});
sqlParameters.Sql = sql;
sqlParameters.Parameter = parameters;
aluno = queryBuilder.ExecuteSqlGetObject <Aluno>(sqlParameters);
Assert.IsTrue(true);
}
public void ExecuteSqlGetDataTable()
{
string sql = "select * from Aluno";
List <Parameter> parameters = new List <Parameter>();
SqlAndParameters sqlParameters = new SqlAndParameters();
System.Data.DataTable table = null;
sqlParameters.Sql = sql;
table = queryBuilder.ExecuteSqlGetDataTable(sqlParameters);
Console.WriteLine(table.Rows.Count);
Assert.IsTrue(table.Rows.Count > 0);
}
public void ExecuteSqlGetObjectList()
{
string sql = "select * from Aluno";
List <Parameter> parameters = new List <Parameter>();
SqlAndParameters sqlParameters = new SqlAndParameters();
List <Aluno> alunoLista = new List <Aluno>();
sqlParameters.Sql = sql;
alunoLista = queryBuilder.ExecuteSqlGetObjectList <Aluno>(sqlParameters).ToList();
Console.WriteLine(alunoLista.Count());
Assert.IsTrue(alunoLista.Count() > 0);
}
public void ExecuteSql(SqlAndParameters sqlParameter)
{
try
{
ValidateConnection();
using (var sqlComand = DbConnection.DbConnectionBase.CreateCommand())
{
if (DbConnection?.DbTransaction != null)
{
sqlComand.Transaction = DbConnection.DbTransaction;
}
if (sqlComand.Parameters != null)
{
sqlComand.SetParameters(sqlParameter);
}
// set sql
sqlComand.CommandText = sqlParameter.Sql;
// set command type
sqlComand.CommandType = sqlParameter.isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
// execute query
sqlComand.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
finally
{
if (DbConnection.canClose)
{
DbConnection.DbConnectionBase.Close();
}
}
}
public void ODBC_ExecuteSqlGetObject()
{
StringBuilder builder = new StringBuilder();
SqlAndParameters sqlParameters = new SqlAndParameters();
List <Parameter> parameters = new List <Parameter>();
cfp102 db2object = null;
builder.Append("Select * from c102 where CCH=? and CFB=1");
parameters.Add(new Parameter {
ParameterKey = "?", ParameterValue = "121"
});
sqlParameters.Sql = builder.ToString();
sqlParameters.Parameter = parameters;
db2object = queryBuilder.ExecuteSqlGetObject <cfp102>(sqlParameters);
Assert.IsNotNull(db2object);
}
public void ExecuteSQL()
{
string sql = "insert into Aluno(nota,curso) values(@nota,@curso) ";
List <Parameter> parameters = new List <Parameter>();
SqlAndParameters sqlParameters = new SqlAndParameters();
parameters.Add(new Parameter {
ParameterKey = "@nota", ParameterValue = 50
});
parameters.Add(new Parameter {
ParameterKey = "@curso", ParameterValue = "PttAP2"
});
sqlParameters.Sql = sql;
sqlParameters.Parameter = parameters;
queryBuilder.ExecuteSql(sqlParameters);
Assert.IsTrue(true);
}
public IEnumerable <T> ExecuteSqlGetObjectList <T>(SqlAndParameters sqlParameter) where T : new()
{
return(DbExecuteQuery.ExecuteSqlGetObjectList <T>(sqlParameter));
}
public T ExecuteSqlGetObject <T>(SqlAndParameters sqlParameter) where T : new()
{
return(DbExecuteQuery.ExecuteSqlGetObject <T>(sqlParameter));
}
public void ExecuteSql(SqlAndParameters sqlParameter)
{
DbExecuteQuery.ExecuteSql(sqlParameter);
}
public bool any(SqlAndParameters sqlParameter)
{
return(DbExecuteQuery.any(sqlParameter));
}
public object ExecuteSqlGetSingle(SqlAndParameters sqlParameter)
{
return(DbExecuteQuery.ExecuteSqlGetSingle(sqlParameter));
}
public DataTable ExecuteSqlGetDataTable(SqlAndParameters sqlParameter)
{
return(dbQuery.ExecuteSqlGetDataTable(sqlParameter));
}
public DataTable ExecuteSqlGetTabela(SqlAndParameters sqlParameter)
{
return(DbExecuteQuery.ExecuteSqlGetTabela(sqlParameter));
}
public bool any(SqlAndParameters sqlParameter)
{
return(dbQuery.any(sqlParameter));
}
