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)); } }