public static void Main(string[] args)
{
var myDto = new MyDto {
Id = 1, IsDeleted = true
};
Spry.Select <MyDto>().Column(_ => myDto.Id).Column(_ => myDto.IsDeleted).From("tt")
.InnerJoin("table2", "audit").On("c1", "d1")
.InnerJoin("table3", "audit").On("c2", "d2")
.InnerJoin("table4", "audit").On("c4", "d2")
.InSchema("review")
.Where(_ => myDto.Id).EqualTo(1)
.AndWhere(_ => myDto.Id).InBetween(1, 10)
.AndWhere(_ => myDto.Id).GreaterThan(5)
.Build();
Spry.InsertInto("tableOne", "review")
.Value("One", 1)
.Value(_ => myDto.Id)
.OutputIdentity()
.Execute(null);
Spry.Update("tableOne")
.Set(_ => myDto.Id)
.Where <int>("id").EqualTo(1)
.Execute(null);
Console.ReadLine();
}
public void CleanUp()
{
using (var connection = _connectionFactory.CreateConnection())
{
Spry.Delete().From("CustomerAddress").Execute(connection);
Spry.Delete().From("Customer").Execute(connection);
}
}
public bool Delete(int customerId)
{
using (var connection = _connectionFactory.CreateConnection())
{
var rowsDeleted = Spry.Delete <Customer>()
.From(CUSTOMER_TABLE)
.Where(_ => customerId).EqualTo(customerId)
.Execute(connection);
return(rowsDeleted == 1);
}
}
public bool UpdateByName(string oldName, string name)
{
using (var connection = _connectionFactory.CreateConnection())
{
var rowsUpdated = Spry.Update <Customer>(CUSTOMER_TABLE)
.Set(_ => name)
.Where(_ => _.Name).EqualTo(oldName)
.Execute(connection);
return(rowsUpdated > 0);
}
}
public bool Update(int customerId, string name, DateTime dateOfBirth)
{
using (var connection = _connectionFactory.CreateConnection())
{
var rowsUpdated = Spry.Update <Customer>(CUSTOMER_TABLE)
.Set(_ => name)
.Set(_ => dateOfBirth)
.Where(_ => customerId).EqualTo(customerId)
.Execute(connection);
return(rowsUpdated > 0);
}
}
public Customer Read(int customerId)
{
using (var connection = _connectionFactory.CreateConnection())
{
return(Spry.Select <Customer>()
.Column(_ => _.CustomerId, "C")
.Column(_ => _.Name, "C")
.Column(_ => _.DateOfBirth, "C")
.From(CUSTOMER_TABLE).As("C").InSchema("dbo")
.Where(_ => _.CustomerId, "C").EqualTo(customerId)
.Query <Customer>(connection).SingleOrDefault());
}
}
public bool UpdateSqlInjection(int customerId, string name)
{
using (var connection = _connectionFactory.CreateConnection())
{
var rowsUpdated = Spry.Update <Customer>(CUSTOMER_TABLE)
.Set(_ => name)
.Where(_ => customerId).EqualTo(customerId)
.AndWhere <int>("1 =1;" +
"DELETE FROM CUSTOMER; --").EqualTo(1)
.Execute(connection);
return(rowsUpdated > 0);
}
}
public int CreateAddress(CustomerAddress address)
{
using (var connection = _connectionFactory.CreateConnection())
{
address.CustomerAddressId = Spry.InsertInto <CustomerAddress>(CUSTOMER_ADDRESS_TABLE)
.Value(_ => address.CustomerId)
.Value(_ => address.LineOne)
.Value(_ => address.City)
.Value(_ => address.Country)
.Value(_ => address.PostCode)
.ExecuteScalar <int>(connection);
return(address.CustomerAddressId);
}
}
public void Insert_CheckOutputInserted()
{
var customer = new Customer
{
DateOfBirth = DateTime.Today,
Name = "John Doe"
};
using (var connection = CreateConnection())
{
customer.CustomerId = Spry.InsertInto <Customer>(CUSTOMER_TABLE)
.Value(_ => customer.Name)
.Value(_ => customer.DateOfBirth)
.OutputInserted(_ => customer.CustomerId)
.ExecuteScalar <int>(connection);
}
Assert.IsTrue(customer.CustomerId > 0);
}
public int Create(string name, DateTime dateOfBirth)
{
using (var connection = _connectionFactory.CreateConnection())
{
var customer = new Customer
{
DateOfBirth = dateOfBirth,
Name = name
};
customer.CustomerId = Spry.InsertInto <Customer>(CUSTOMER_TABLE)
.Value(_ => customer.Name)
.Value(_ => customer.DateOfBirth)
.OutputIdentity()
.ExecuteScalar <int>(connection);
return(customer.CustomerId);
}
}
public Customer ReadComplete(int customerId)
{
using (var connection = _connectionFactory.CreateConnection())
{
var customer = Spry.Select <Customer>()
.Column(_ => _.CustomerId, "C")
.Column(_ => _.Name)
.Column(_ => _.DateOfBirth)
.Column(_ => _.Address.City)
.Column(_ => _.Address.Country)
.Column(_ => _.Address.PostCode)
.Column(_ => _.Address.CustomerAddressId)
.Column(_ => _.Address.LineOne)
.From(CUSTOMER_TABLE).As("C").InSchema("dbo")
.InnerJoin(CUSTOMER_ADDRESS_TABLE, "CA").On("CA.CustomerId", "C.CustomerId")
.Where(_ => _.CustomerId, "C").EqualTo(customerId)
.Query <dynamic>(connection).SingleOrDefault();
return(ToCustomer(customer));
}
}
