public void Can_Create_Tables_With_Schema_in_SqlServer()
{
var dbFactory = OrmLiteTestBase.CreateSqlServerDbFactory();
using (IDbConnection db = dbFactory.OpenDbConnection())
{
CreateSchemaIfNotExists(db);
db.DropAndCreateTable <User>();
var tables = db.Column <string>(@"SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable FROM sys.tables");
//sql server dialect should create the table in the schema
Assert.That(tables.Contains("[Security].[Users]"));
}
}
public void Can_Perform_CRUD_Operations_On_Table_With_Schema()
{
var dbFactory = OrmLiteTestBase.CreateSqlServerDbFactory();
using (IDbConnection db = dbFactory.OpenDbConnection())
{
CreateSchemaIfNotExists(db);
db.CreateTable <User>(true);
db.Insert(new User {
Id = 1, Name = "A", CreatedDate = DateTime.Now
});
db.Insert(new User {
Id = 2, Name = "B", CreatedDate = DateTime.Now
});
var user = new User {
Id = 3, Name = "B", CreatedDate = DateTime.Now
};
user.Id = (int)db.Insert(user, selectIdentity: true);
Assert.That(user.Id, Is.GreaterThan(0));
var rowsB = db.SelectFmt <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(2));
var rowIds = rowsB.ConvertAll(x => x.Id);
Assert.That(rowIds, Is.EquivalentTo(new List <long> {
2, 3
}));
rowsB.ForEach(x => db.Delete(x));
rowsB = db.SelectFmt <User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(0));
var rowsLeft = db.Select <User>();
Assert.That(rowsLeft, Has.Count.EqualTo(1));
Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
}
}
public ReturnAttributeTests()
{
ConnectionString = OrmLiteTestBase.GetConnectionString();
DialectProvider = SqlServer2012Dialect.Provider;
}
public void Run()
{
//Setup SQL Server Connection Factory
var dbFactory = OrmLiteTestBase.CreateSqlServerDbFactory();
//var dbFactory = new OrmLiteConnectionFactory(
// @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Database1.mdf;Integrated Security=True;User Instance=True",
// SqlServerDialect.Provider);
//Use in-memory Sqlite DB instead
//var dbFactory = new OrmLiteConnectionFactory(
// ":memory:", false, SqliteDialect.Provider);
//Non-intrusive: All extension methods hang off System.Data.* interfaces
using (IDbConnection db = dbFactory.OpenDbConnection())
{
//Re-Create all table schemas:
db.DropTable <OrderDetail>();
db.DropTable <Order>();
db.DropTable <Customer>();
db.DropTable <Product>();
db.DropTable <Employee>();
db.CreateTable <Employee>();
db.CreateTable <Product>();
db.CreateTable <Customer>();
db.CreateTable <Order>();
db.CreateTable <OrderDetail>();
db.Insert(new Employee {
Id = 1, Name = "Employee 1"
});
db.Insert(new Employee {
Id = 2, Name = "Employee 2"
});
var product1 = new Product {
Id = 1, Name = "Product 1", UnitPrice = 10
};
var product2 = new Product {
Id = 2, Name = "Product 2", UnitPrice = 20
};
db.Save(product1, product2);
var customer = new Customer {
FirstName = "Orm",
LastName = "Lite",
Email = "*****@*****.**",
PhoneNumbers =
{
{ PhoneType.Home, "555-1234" },
{ PhoneType.Work, "1-800-1234" },
{ PhoneType.Mobile, "818-123-4567" },
},
Addresses =
{
{ AddressType.Work, new Address {
Line1 = "1 Street", Country = "US", State = "NY", City = "New York", ZipCode = "10101"
} },
},
CreatedAt = DateTime.UtcNow,
};
db.Insert(customer);
var customerId = db.GetLastInsertId(); //Get Auto Inserted Id
customer = db.QuerySingle <Customer>(new { customer.Email }); //Query
Assert.That(customer.Id, Is.EqualTo(customerId));
//Direct access to System.Data.Transactions:
using (IDbTransaction trans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
var order = new Order {
CustomerId = customer.Id,
EmployeeId = 1,
OrderDate = DateTime.UtcNow,
Freight = 10.50m,
ShippingAddress = new Address {
Line1 = "3 Street", Country = "US", State = "NY", City = "New York", ZipCode = "12121"
},
};
db.Save(order); //Inserts 1st time
order.Id = (int)db.GetLastInsertId(); //Get Auto Inserted Id
var orderDetails = new[] {
new OrderDetail {
OrderId = order.Id,
ProductId = product1.Id,
Quantity = 2,
UnitPrice = product1.UnitPrice,
},
new OrderDetail {
OrderId = order.Id,
ProductId = product2.Id,
Quantity = 2,
UnitPrice = product2.UnitPrice,
Discount = .15m,
}
};
db.Insert(orderDetails);
order.Total = orderDetails.Sum(x => x.UnitPrice * x.Quantity * x.Discount) + order.Freight;
db.Save(order); //Updates 2nd Time
trans.Commit();
}
}
}