public void ToSQL_001()
{
var data = new Person
{
FirstName = "Chris",
LastName = "Pietschmann"
};
var target = new SQLinqUpdate <Person>(data);
var actual = (SQLinqUpdateResult)target.ToSQL();
Assert.AreEqual("[Person]", actual.Table);
Assert.AreEqual(7, actual.Fields.Count);
Assert.AreEqual("@sqlinq_1", actual.Fields["ID"]);
Assert.AreEqual("@sqlinq_2", actual.Fields["FirstName"]);
Assert.AreEqual("@sqlinq_3", actual.Fields["LastName"]);
Assert.AreEqual("@sqlinq_4", actual.Fields["Age"]);
Assert.AreEqual("@sqlinq_5", actual.Fields["[Is_Employed]"]);
Assert.AreEqual("@sqlinq_6", actual.Fields["ParentID"]);
Assert.AreEqual("@sqlinq_7", actual.Fields["Column With Spaces"]);
Assert.AreEqual(7, actual.Parameters.Count);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_1"]);
Assert.AreEqual("Chris", actual.Parameters["@sqlinq_2"]);
Assert.AreEqual("Pietschmann", actual.Parameters["@sqlinq_3"]);
Assert.AreEqual(0, actual.Parameters["@sqlinq_4"]);
Assert.AreEqual(false, actual.Parameters["@sqlinq_5"]);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_6"]);
Assert.AreEqual(null, actual.Parameters["@sqlinq_7"]);
}
public void ToSQL_001()
{
var data = new Person
{
FirstName = "Chris",
LastName = "Pietschmann"
};
var target = new SQLinqUpdate<Person>(data);
var actual = (SQLinqUpdateResult)target.ToSQL();
Assert.AreEqual("[Person]", actual.Table);
Assert.AreEqual(7, actual.Fields.Count);
Assert.AreEqual("@sqlinq_1", actual.Fields["ID"]);
Assert.AreEqual("@sqlinq_2", actual.Fields["FirstName"]);
Assert.AreEqual("@sqlinq_3", actual.Fields["LastName"]);
Assert.AreEqual("@sqlinq_4", actual.Fields["Age"]);
Assert.AreEqual("@sqlinq_5", actual.Fields["[Is_Employed]"]);
Assert.AreEqual("@sqlinq_6", actual.Fields["ParentID"]);
Assert.AreEqual("@sqlinq_7", actual.Fields["Column With Spaces"]);
Assert.AreEqual(7, actual.Parameters.Count);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_1"]);
Assert.AreEqual("Chris", actual.Parameters["@sqlinq_2"]);
Assert.AreEqual("Pietschmann", actual.Parameters["@sqlinq_3"]);
Assert.AreEqual(0, actual.Parameters["@sqlinq_4"]);
Assert.AreEqual(false, actual.Parameters["@sqlinq_5"]);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_6"]);
Assert.AreEqual(null, actual.Parameters["@sqlinq_7"]);
}
public void Alterar(Solicitacao_DepositoDO dtoDeposito, Erro objErro, ref TransactionManager objTransaction)
{
try
{
this.Validar_Alterar(dtoDeposito, objErro, ref objTransaction);
if (objErro.TemErro())
{
return;
}
var update = new SQLinqUpdate <Solicitacao_DepositoDO>(dtoDeposito)
.Partial(x => x.Cliente_CD)
.Partial(x => x.Cliente_Nome)
.Partial(x => x.Cliente_CPFCNPJ)
.Partial(x => x.Banco_CD)
.Partial(x => x.Banco_Agencia)
.Partial(x => x.Banco_Conta)
.Partial(x => x.Banco_ContaTipo)
.Partial(x => x.Data_Vencimento)
.Partial(x => x.Pago)
.Where(x => x.Interface_Deposito_ID == dtoDeposito.Interface_Deposito_ID);
SqlHelper.Execute(objTransaction.ObjetoDeAcessoDados, update);
}
catch (Exception)
{
throw;
}
}
public async Task UpdateAsync(Expression <Func <T> > update, Expression <Func <T, bool> > condition)
{
var updateSql = new SQLinqUpdate <T>(this.SqlDialect);
updateSql.Where(condition);
updateSql.UpdateSet(update);
var queryRst = updateSql.ToSQL();
this.Logger.LogDebug($"SQL:{queryRst.ToQuery()}{Environment.NewLine}Params:{queryRst.Parameters}");
await this.Connection.ExecuteAsync(queryRst.ToQuery(), queryRst.Parameters);
}
public void UpdateSetExpression_test()
{
var sqlinq = new SQLinqUpdate <ToSQL_009_Class>(new MySqlDialect());
sqlinq.UpdateSet(() => new ToSQL_009_Class()
{
ID = 1,
Name = "test"
});
var sql = sqlinq.ToSQL();
}
public async Task ChangeAppSecuretAsync(long id, string newSecuret)
{
var app = await base.QueryFirstAsync(x => x.Id == id);
var sqlGenerator = new SQLinq <UserEntity>(base.SqlDialect);
var updateSql = new SQLinqUpdate <UserEntity>(this.SqlDialect);
updateSql.Where(x => x.Id == app.AppUserId);
updateSql.UpdateSet(() => new UserEntity {
Password = newSecuret
});
var sqlResult = updateSql.ToSQL();
var sql = sqlResult.ToQuery();
var paramaters = sqlResult.Parameters;
await this.Connection.ExecuteAsync(sql, paramaters);
}
public async Task ChangeAppSecuretAsync(ApplicationEntity entity)
{
var sqlGenerator = new SQLinq <UserEntity>(base.SqlDialect);
sqlGenerator.Where(x => x.Id == entity.AppUserId);
var sqlResult = sqlGenerator.ToSQL();
var sql = sqlResult.ToQuery();
var updateSqlGenerator = new SQLinqUpdate <UserEntity>(base.SqlDialect);
updateSqlGenerator.Where(x => x.Id == entity.AppUserId);
updateSqlGenerator.UpdateSet(() => new UserEntity {
Password = entity.Account.Password, Salt = entity.Account.Salt
});
var updateSqlResult = updateSqlGenerator.ToSQL();
var updateSql = updateSqlResult.ToQuery();
await this.Connection.ExecuteAsync(updateSql, updateSqlResult.Parameters);
}
public void UpdateSetExpression_test()
{
var sqlinq = new SQLinqUpdate <ToSQL_009_Class>(new MySqlDialect());
sqlinq.UpdateSet(() => new ToSQL_009_Class()
{
ID = 1,
//Name = "test"
});
sqlinq.Where(x => x.ID == 1024);
var sql = sqlinq.ToSQL();
var query = sql.ToQuery();
var sqlResult = "UPDATE `MyTable` SET `ID` = @sqlinq_2\r\n WHERE `ID` = @sqlinq_1";
Assert.AreEqual(sqlResult, query);
Assert.AreEqual(sql.Parameters.Count, 2);
Assert.AreEqual(sql.Parameters["@sqlinq_2"], 1);
Assert.AreEqual(sql.Parameters["@sqlinq_1"], 1024);
}
public void Execute_003()
{
var expected = new Person
{
ID = Guid.Parse("9d12a0f4-6494-4d58-bee4-c7a99a287c1c"),
FirstName = "Chris",
LastName = "Pietschmann",
Age = null
};
using (var con = GetDBConnection())
{
con.Open();
var trans = con.BeginTransaction();
try
{
var query = new SQLinqUpdate <Person>(expected);
con.Execute(query, trans);
var actual = con.Query(from p in new SQLinq <Person>()
where p.ID == expected.ID
select p, trans).FirstOrDefault();
Assert.IsNotNull(actual);
Assert.AreEqual(expected.ID, actual.ID);
Assert.AreEqual(expected.FirstName, actual.FirstName);
Assert.AreEqual(expected.LastName, actual.LastName);
Assert.IsNull(actual.Age);
}
finally
{
trans.Rollback();
con.Close();
}
}
}
public void Execute_002()
{
var expected = new Person
{
ID = Guid.Parse("9d12a0f4-6494-4d58-bee4-c7a99a287c1c"),
FirstName = "Chris",
LastName = "Pietschmann",
Age = 1065
};
using (var con = GetDBConnection())
{
con.Open();
var trans = con.BeginTransaction();
try
{
var query = new SQLinqUpdate<Person>(expected);
con.Execute(query, trans);
var actual = con.Query(from p in new SQLinq<Person>()
where p.ID == expected.ID
select p, trans).FirstOrDefault();
Assert.IsNotNull(actual);
Assert.AreEqual(expected.ID, actual.ID);
Assert.AreEqual(expected.FirstName, actual.FirstName);
Assert.AreEqual(expected.LastName, actual.LastName);
Assert.AreEqual(expected.Age, actual.Age);
}
finally
{
trans.Rollback();
con.Close();
}
}
}
public void ToSQL_002()
{
var data = new PersonView
{
FirstName = "Chris",
LastName = "Pietschmann"
};
var target = new SQLinqUpdate<PersonView>(data);
var actual = (SQLinqUpdateResult)target.ToSQL();
Assert.AreEqual("[vw_Person]", actual.Table);
Assert.AreEqual(4, actual.Fields.Count);
Assert.AreEqual("@sqlinq_1", actual.Fields["ID"]);
Assert.AreEqual("@sqlinq_2", actual.Fields["First_Name"]);
Assert.AreEqual("@sqlinq_3", actual.Fields["Last_Name"]);
Assert.AreEqual("@sqlinq_4", actual.Fields["Age"]);
Assert.AreEqual(4, actual.Parameters.Count);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_1"]);
Assert.AreEqual("Chris", actual.Parameters["@sqlinq_2"]);
Assert.AreEqual("Pietschmann", actual.Parameters["@sqlinq_3"]);
Assert.AreEqual(0, actual.Parameters["@sqlinq_4"]);
}
public void ToSQL_002()
{
var data = new PersonView
{
FirstName = "Chris",
LastName = "Pietschmann"
};
var target = new SQLinqUpdate <PersonView>(data);
var actual = (SQLinqUpdateResult)target.ToSQL();
Assert.AreEqual("[vw_Person]", actual.Table);
Assert.AreEqual(4, actual.Fields.Count);
Assert.AreEqual("@sqlinq_1", actual.Fields["ID"]);
Assert.AreEqual("@sqlinq_2", actual.Fields["First_Name"]);
Assert.AreEqual("@sqlinq_3", actual.Fields["Last_Name"]);
Assert.AreEqual("@sqlinq_4", actual.Fields["Age"]);
Assert.AreEqual(4, actual.Parameters.Count);
Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_1"]);
Assert.AreEqual("Chris", actual.Parameters["@sqlinq_2"]);
Assert.AreEqual("Pietschmann", actual.Parameters["@sqlinq_3"]);
Assert.AreEqual(0, actual.Parameters["@sqlinq_4"]);
}
