public void CanCreateSimpleForeignKey()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<TypeWithSimpleForeignKey>(true);
}
}
public void Setup()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ReferencedType>(true);
}
}
public void datetime_tests__can_use_datetime2()
{
var dbFactory = new OrmLiteConnectionFactory(base.ConnectionString, SqlServerOrmLiteDialectProvider.Instance);
//change to datetime2 - check for higher range and precision
SqlServerOrmLiteDialectProvider.Instance.UseDatetime2(true);
using(var conn = dbFactory.OpenDbConnection()) {
var test_object_ValidForDatetime2 = Table_for_datetime2_tests.get_test_object_ValidForDatetime2();
var test_object_ValidForNormalDatetime = Table_for_datetime2_tests.get_test_object_ValidForNormalDatetime();
conn.CreateTable<Table_for_datetime2_tests>(true);
//normal insert
var insertedId = conn.Insert(test_object_ValidForDatetime2, selectIdentity:true);
//read back, and verify precision
var fromDb = conn.SingleById<Table_for_datetime2_tests>(insertedId);
Assert.AreEqual(test_object_ValidForDatetime2.ToVerifyPrecision, fromDb.ToVerifyPrecision);
//update
fromDb.ToVerifyPrecision = test_object_ValidForDatetime2.ToVerifyPrecision.Value.AddYears(1);
conn.Update(fromDb);
var fromDb2 = conn.SingleById<Table_for_datetime2_tests>(insertedId);
Assert.AreEqual(test_object_ValidForDatetime2.ToVerifyPrecision.Value.AddYears(1), fromDb2.ToVerifyPrecision);
//check InsertParam
conn.Insert(test_object_ValidForDatetime2);
}
}
public void CanCreateForeignWithOnDeleteRestrict()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<TypeWithOnDeleteRestrict>(true);
}
}
public void datetime_tests__check_default_behaviour()
{
var dbFactory = new OrmLiteConnectionFactory(base.ConnectionString, SqlServerOrmLiteDialectProvider.Instance);
//default behaviour: normal datetime can't hold DateTime values of year 1.
(SqlServerOrmLiteDialectProvider.Instance as SqlServerOrmLiteDialectProvider).UseDatetime2(false);
using(var conn = dbFactory.OpenDbConnection()) {
var test_object_ValidForDatetime2 = Table_for_datetime2_tests.get_test_object_ValidForDatetime2();
var test_object_ValidForNormalDatetime = Table_for_datetime2_tests.get_test_object_ValidForNormalDatetime();
conn.CreateTable<Table_for_datetime2_tests>(true);
//normal insert
var insertedId = conn.Insert(test_object_ValidForNormalDatetime, selectIdentity:true);
//insert works, but can't regular datetime's precision is not great enough.
var fromDb = conn.SingleById<Table_for_datetime2_tests>(insertedId);
Assert.AreNotEqual(test_object_ValidForNormalDatetime.ToVerifyPrecision, fromDb.ToVerifyPrecision);
var thrown = Assert.Throws<SqlTypeException>(() => {
conn.Insert(test_object_ValidForDatetime2);
});
Assert.That(thrown.Message.Contains("SqlDateTime overflow"));
//check InsertParam
conn.Insert(test_object_ValidForNormalDatetime);
//InsertParam fails differently:
var insertParamException = Assert.Throws<System.Data.SqlTypes.SqlTypeException>(() => {
conn.Insert(test_object_ValidForDatetime2);
});
Assert.That(insertParamException.Message.Contains("SqlDateTime overflow"));
}
}
public void Can_use_nested_transactions()
{
//must use the factory, because that returns an OrmLiteConnection, that can saves the current transaction
var factory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider);
//using(var outerConn = OpenDbConnection()) {
using(var outerConn = factory.OpenDbConnection()) {
//(re)create tables
outerConn.DropAndCreateTable<Can_use_nested_transactions_Table1>();
outerConn.DropAndCreateTable<Can_use_nested_transactions_Table2>();
//using(var innerConn = OpenDbConnection()) {//use the factory to get the connections
using(var innerConn = factory.OpenDbConnection()) {
using(var outerTran = outerConn.OpenTransaction()) {
outerConn.Insert(new Can_use_nested_transactions_Table1 { Dummy = DateTime.Now });
using(var innerTran = innerConn.OpenTransaction()) {
//The other transaction inserts into table1, Table2 is not locked
innerConn.Insert(new Can_use_nested_transactions_Table2 { Dummy = DateTime.Now });
//fails here, because innerTran has overwritten the ThreadStatic OrmLiteConfig.CurrentTransaction
outerConn.Insert(new Can_use_nested_transactions_Table1 { Dummy = DateTime.Now });
outerConn.Insert(new Can_use_nested_transactions_Table1 { Dummy = DateTime.Now });
}
}
}
}
}
public void Can_Delete_from_basic_persistence_provider()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
var basicProvider = new OrmLitePersistenceProvider(db);
var rowIds = new List<int> {1, 2, 3, 4, 5};
var rows = rowIds.ConvertAll(x => ModelWithFieldsOfDifferentTypes.Create(x));
rows.ForEach(x => db.Insert(x));
var deleteRowIds = new List<int> {2, 4};
foreach (var row in rows)
{
if (deleteRowIds.Contains(row.Id))
{
basicProvider.Delete(row);
}
}
var providerRows = basicProvider.GetByIds<ModelWithFieldsOfDifferentTypes>(rowIds).ToList();
var providerRowIds = providerRows.ConvertAll(x => x.Id);
var remainingIds = new List<int>(rowIds);
deleteRowIds.ForEach(x => remainingIds.Remove(x));
Assert.That(providerRowIds, Is.EquivalentTo(remainingIds));
}
}
public void Can_select_from_FileStream()
{
ConnectionString = "Data Source=localhost;Initial Catalog=test2;User Id=test;Password=test;Connect Timeout=120;MultipleActiveResultSets=True;Type System Version=SQL Server 2012;";
var dialectProvider = SqlServerConverters.Configure(SqlServer2012Dialect.Provider);
var dbFactory = new OrmLiteConnectionFactory(ConnectionString, dialectProvider);
using (var db = dbFactory.OpenDbConnection())
{
db.DropTable<TestFile>();
db.CreateTable<TestFile>();
db.Insert(new TestFile { Contents = "contents".ToUtf8Bytes() });
db.Select<TestFile>().PrintDump();
//db.DropTable<FileStream>();
//db.CreateTable<FileStream>();
//db.Insert(new FileStream
//{
// Name = "file.txt",
// Path = SqlHierarchyId.Parse("/1/2/3/"),
// ParentPath = SqlHierarchyId.Parse("/1/2/"),
// FileContent = "contents".ToUtf8Bytes(),
// FileType = MimeTypes.PlainText,
//});
//var q = db.From<FileStream>();
//db.Select(q);
}
}
public void Can_Perform_CRUD_Operations_On_Table_With_Schema()
{
var dbFactory = new OrmLiteConnectionFactory(
"User=SYSDBA;Password=masterkey;Database=ormlite-tests.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;",
FirebirdOrmLiteDialectProvider.Instance);
using (IDbConnection db = dbFactory.OpenDbConnection())
{
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 });
db.Insert(new User { Id = 3, Name = "B", CreatedDate = DateTime.Now });
var lastInsertId = db.GetLastInsertId();
Assert.That(lastInsertId, Is.GreaterThan(0));
var rowsB = db.Select<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.Select<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 void test_if_ormlite_sql_prepare_update_works_for_image_blobs()
{
var dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqlServerOrmLiteDialectProvider.Instance);
using (var db = dbFactory.OpenDbConnection())
using (var dbConn = db.CreateCommand())
{
dbConn.CreateTable<ImageBlobDto>(true);
var dto = new ImageBlobDto()
{
Image1 = ImageToBytes(ImageBlobResource.Bild),
Image2 = ImageToBytes(ImageBlobResource.Bild2),
Image3 = ImageToBytes(ImageBlobResource.Bild3),
Complex = new object[] { "Foo", "Bar", "Baz", 1 }
};
var provider = OrmLiteConfig.DialectProvider as OrmLiteDialectProviderBase;
Assert.NotNull(provider);
using (var insertCmd = db.CreateInsertStatement(dto))
{
var nInserted = insertCmd.ExecuteNonQuery();
Assert.Greater(nInserted, 0);
}
dto.Id = 1;
dto.Complex = new object[] { "Baz", "Bar", "Foo", 2 };
using (var updateCmd = db.CreateUpdateStatement(dto))
{
var nUpdated = updateCmd.ExecuteNonQuery();
Assert.Greater(nUpdated, 0);
}
}
}
public void SaveDateTimeToDatabase()
{
var dbFactory = new OrmLiteConnectionFactory(base.ConnectionString, SqlServerOrmLiteDialectProvider.Instance);
SqlServerOrmLiteDialectProvider.Instance.EnsureUtc(true);
using (var db = dbFactory.OpenDbConnection())
{
var dateTime = new DateTime(2012, 1, 1, 1, 1, 1, DateTimeKind.Local);
var x = InsertAndSelectDateTime(db, dateTime);
Assert.AreEqual(DateTimeKind.Utc, x.Test.Kind);
Assert.AreEqual(x.Test.ToUniversalTime(), dateTime.ToUniversalTime());
Assert.AreEqual(x.Test.ToLocalTime(), dateTime.ToLocalTime());
dateTime = new DateTime(2012, 1, 1, 1, 1, 1, DateTimeKind.Utc);
x = InsertAndSelectDateTime(db, dateTime);
Assert.AreEqual(DateTimeKind.Utc, x.Test.Kind);
Assert.AreEqual(x.Test.ToUniversalTime(), dateTime.ToUniversalTime());
Assert.AreEqual(x.Test.ToLocalTime(), dateTime.ToLocalTime());
dateTime = new DateTime(2012, 1, 1, 1, 1, 1, DateTimeKind.Unspecified);
x = InsertAndSelectDateTime(db, dateTime);
Assert.AreEqual(DateTimeKind.Utc, x.Test.Kind);
Assert.AreEqual(x.Test.ToUniversalTime(), dateTime);
Assert.AreEqual(x.Test.ToLocalTime(), dateTime.ToLocalTime());
}
}
public void Can_get_data_from_TableWithNamigStrategy_with_query_by_example()
{
OrmLite.OrmLiteConfig.DialectProvider.NamingStrategy =
OrmLite.OrmLiteConfig.DialectProvider.NamingStrategy = new PrefixNamingStrategy
{
TablePrefix = "tab_",
ColumnPrefix = "col_",
};
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithOnlyStringFields>(true);
ModelWithOnlyStringFields m = new ModelWithOnlyStringFields()
{
Id = "998",
AlbumId = "112",
AlbumName = "ElectroShip",
Name = "QueryByExample"
};
db.Save<ModelWithOnlyStringFields>(m);
var modelFromDb = db.Where<ModelWithOnlyStringFields>(new {Name = "QueryByExample"})[0];
Assert.AreEqual(m.Name, modelFromDb.Name);
}
OrmLite.OrmLiteConfig.DialectProvider.NamingStrategy = new OrmLiteNamingStrategyBase();
}
public void Can_query_using_float_in_alernate_culuture()
{
var dbFactory = new OrmLiteConnectionFactory(
GetFileConnectionString(),
FirebirdOrmLiteDialectProvider.Instance);
using (var db =dbFactory.OpenDbConnection())
using (var dbCmd = db.CreateCommand())
{
dbCmd.CreateTable<Point>(true);
dbCmd.Insert(new Point { Width = 4, Height = 1.123f, Top = 3.456d, Left = 2.345m});
var points = dbCmd.Select<Point>();
Console.WriteLine(points.Dump());
Assert.That(points[0].Width, Is.EqualTo(4));
Assert.That(points[0].Height, Is.EqualTo(1.123f));
Assert.That(points[0].Top, Is.EqualTo(3.456d));
Assert.That(points[0].Left, Is.EqualTo(2.345m));
points = dbCmd.Select<Point>("Height={0}", 1.123f); // returns no rows! FirebirdSql bug?
Assert.That(points.Count>0);
}
}
public void Can_Perform_CRUD_Operations_On_Table_With_Schema()
{
var dbFactory = new OrmLiteConnectionFactory(
@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Database1.mdf;Integrated Security=True;User Instance=True",
SqlServerOrmLiteDialectProvider.Instance);
using (IDbConnection db = dbFactory.OpenDbConnection())
using (IDbCommand dbCmd = db.CreateCommand())
{
CreateSchemaIfNotExists(dbCmd);
dbCmd.CreateTable<User>(true);
dbCmd.Insert(new User { Id = 1, Name = "A", CreatedDate = DateTime.Now });
dbCmd.Insert(new User { Id = 2, Name = "B", CreatedDate = DateTime.Now });
dbCmd.Insert(new User { Id = 3, Name = "B", CreatedDate = DateTime.Now });
var lastInsertId = dbCmd.GetLastInsertId();
Assert.That(lastInsertId, Is.GreaterThan(0));
var rowsB = dbCmd.Select<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 => dbCmd.Delete(x));
rowsB = dbCmd.Select<User>("Name = {0}", "B");
Assert.That(rowsB, Has.Count.EqualTo(0));
var rowsLeft = dbCmd.Select<User>();
Assert.That(rowsLeft, Has.Count.EqualTo(1));
Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
}
}
public void Transaction_rollsback_transactions_to_different_tables()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithIdAndName>(true);
db.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
db.CreateTable<ModelWithOnlyStringFields>(true);
db.DeleteAll<ModelWithIdAndName>();
db.Insert(new ModelWithIdAndName(0));
using (var dbTrans = db.BeginTransaction())
{
db.Insert(new ModelWithIdAndName(0));
db.Insert(ModelWithFieldsOfDifferentTypes.Create(3));
db.Insert(ModelWithOnlyStringFields.Create("id3"));
Assert.That(db.Select<ModelWithIdAndName>(), Has.Count.EqualTo(2));
Assert.That(db.Select<ModelWithFieldsOfDifferentTypes>(), Has.Count.EqualTo(1));
Assert.That(db.Select<ModelWithOnlyStringFields>(), Has.Count.EqualTo(1));
}
Assert.That(db.Select<ModelWithIdAndName>(), Has.Count.EqualTo(1));
Assert.That(db.Select<ModelWithFieldsOfDifferentTypes>(), Has.Count.EqualTo(0));
Assert.That(db.Select<ModelWithOnlyStringFields>(), Has.Count.EqualTo(0));
}
}
public void Can_create_ModelWithIdOnly_table()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithIdOnly>(true);
}
}
public void Can_create_ModelWithFieldsOfDifferentTypes_table()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
}
}
public void Simple_CRUD_example()
{
var path = Config.SqliteFileDb;
if (File.Exists(path))
File.Delete(path);
var connectionFactory = new OrmLiteConnectionFactory(path, SqliteOrmLiteDialectProvider.Instance.WithPassword("bob"));
using (var db = connectionFactory.OpenDbConnection())
{
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});
db.Insert(new User {Id = 3, Name = "B", CreatedDate = DateTime.Now});
var rowsB = db.Select<User>("Name = {0}", "B");
var rowsB1 = db.Select<User>(user => user.Name == "B");
Assert.That(rowsB, Has.Count.EqualTo(2));
Assert.That(rowsB1, 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.Select<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"));
}
File.Delete(path);
}
public void Can_create_TableWithNamigStrategy_table_nameUnderscoreCoumpound()
{
using (new TemporaryNamingStrategy(new UnderscoreSeparatedCompoundNamingStrategy()))
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithOnlyStringFields>(true);
}
}
public void SetUp()
{
//Setup SQL Server Connection Factory
dbFactory = new OrmLiteConnectionFactory(
@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Database1.mdf;Integrated Security=True;User Instance=True",
SqlServerOrmLiteDialectProvider.Instance);
dbFactory.Exec(dbCmd => dbCmd.CreateTable<User>(overwrite: true));
}
public void TestFixtureSetUp()
{
LogManager.LogFactory = new ConsoleLogFactory();
OrmLiteConfig.DialectProvider = OracleDialect.Provider;
OrmLiteConfig.ClearCache();
ConnectionString = GetFileConnectionString();
DbFactory = new OrmLiteConnectionFactory(ConnectionString, OracleDialect.Provider);
}
public void SetUp()
{
//Setup SQL Server Connection Factory
dbFactory = new OrmLiteConnectionFactory(
ConfigurationManager.ConnectionStrings["testDb"].ConnectionString,
MySqlDialectProvider.Instance);
dbFactory.Exec(dbCmd => dbCmd.CreateTable<User>(overwrite: true));
}
public void SetUp()
{
//Setup SQL Server Connection Factory
dbFactory = new OrmLiteConnectionFactory(
"~/App_Data/Database1.mdf".MapAbsolutePath(),
SqlServerOrmLiteDialectProvider.Instance);
dbFactory.Run(db => db.CreateTable<User>(overwrite: true));
}
public void Can_insert_and_select_from_ModelWithFieldsOfDifferentAndNullableTypes_table_compact_GUID()
{
Firebird.FirebirdOrmLiteDialectProvider dialect = new Firebird.FirebirdOrmLiteDialectProvider(true);
OrmLiteConnectionFactory factory = new OrmLiteConnectionFactory(ConnectionString, dialect);
using (var db = factory.CreateDbConnection())
{
db.Open();
Can_insert_and_select_from_ModelWithFieldsOfDifferentAndNullableTypes_table_impl(db);
}
}
public void SetUp()
{
//Setup SQL Server Connection Factory
dbFactory = new OrmLiteConnectionFactory(
ConfigurationManager.ConnectionStrings["testDb"].ConnectionString,
MySqlDialectProvider.Instance);
using (var db = dbFactory.Open())
db.DropAndCreateTable<User>();
}
public void Can_insert_into_ModelWithFieldsOfDifferentTypes_table()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithFieldsOfDifferentTypes>(true);
var row = ModelWithFieldsOfDifferentTypes.Create(1);
db.Insert(row);
}
}
public void WrappingWithSpecializedMiniProfilerSucceeds()
{
var factory = new OrmLiteConnectionFactory(ConnectionString, OracleDialect.Provider)
{
ConnectionFilter = x => new SpecializedProfiledDbConnection(x, Profiler.Current)
};
using (var db = factory.OpenDbConnection())
{
DoInsertUpdate(db);
}
}
public void TestFixtureSetUp()
{
LogManager.LogFactory = new ConsoleLogFactory();
OrmLiteConfig.DialectProvider = SqliteDialect.Provider;
//ConnectionString = ":memory:";
ConnectionString = GetFileConnectionString();
DbFactory = new OrmLiteConnectionFactory(ConnectionString, SqliteDialect.Provider);
//OrmLiteConfig.DialectProvider = SqlServerOrmLiteDialectProvider.Instance;
//ConnectionString = "~/App_Data/Database1.mdf".MapAbsolutePath();
}
public void Can_create_ModelWithCompositeIndexFields_table()
{
OrmLiteConfig.DialectProvider.DefaultStringLength=128;
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithCompositeIndexFields>(true);
var sql = OrmLiteConfig.DialectProvider.ToCreateIndexStatements(typeof(ModelWithCompositeIndexFields)).Join();
Assert.IsTrue(sql.Contains("idx_modelwcif_name"));
Assert.IsTrue(sql.Contains("idx_modelwcif_comp1_comp2"));
}
}
public void Can_get_data_from_TableWithNamigStrategy_with_GetById()
{
using (new TemporaryNamingStrategy(new PrefixNamingStrategy { TablePrefix = "tab_", ColumnPrefix = "col_" }))
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable<ModelWithOnlyStringFields>(true);
ModelWithOnlyStringFields m = new ModelWithOnlyStringFields() { Id= "999", AlbumId = "112", AlbumName="ElectroShip", Name = "MyNameIsBatman"};
db.Save<ModelWithOnlyStringFields>(m);
var modelFromDb = db.SingleById<ModelWithOnlyStringFields>("999");
Assert.AreEqual(m.Name, modelFromDb.Name);
}
}
public static OrmLiteConnectionFactory CreateMySqlDbFactory()
{
var dbFactory = new OrmLiteConnectionFactory(MySqlDb.DefaultConnection, MySqlDialect.Provider);
return(dbFactory);
}
public void Run()
{
//Setup FirebridSQL Server Connection Factory
var dbFactory = new OrmLiteConnectionFactory(
"User=SYSDBA;Password=masterkey;Database=ormlite-tests.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;",
FirebirdOrmLiteDialectProvider.Instance);
IDbConnection dbConn = dbFactory.OpenDbConnection();
IDbCommand dbCmd = dbConn.CreateCommand();
//Re-Create all table schemas:
dbCmd.DropTable <OrderDetail>();
dbCmd.DropTable <Order>();
dbCmd.DropTable <Customer>();
dbCmd.DropTable <Product>();
dbCmd.DropTable <Employee>();
dbCmd.CreateTable <Employee>();
dbCmd.CreateTable <Product>();
dbCmd.CreateTable <Customer>();
dbCmd.CreateTable <Order>();
dbCmd.CreateTable <OrderDetail>();
dbCmd.Insert(new Employee {
Id = 1, Name = "Employee 1"
});
dbCmd.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
};
dbCmd.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"
} },
},
Timestamp = DateTime.UtcNow,
};
dbCmd.Insert(customer);
var customerId = dbCmd.GetLastInsertId(); //Get Auto Inserted Id
customer = dbCmd.QuerySingle <Customer>(new { customer.Email }); //Query
Assert.That(customer.Id, Is.EqualTo(customerId));
//Direct access to System.Data.Transactions:
using (var trans = dbCmd.BeginTransaction(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"
},
};
dbCmd.Save(order); //Inserts 1st time
order.Id = (int)dbCmd.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,
}
};
dbCmd.Insert(orderDetails);
order.Total = orderDetails.Sum(x => x.UnitPrice * x.Quantity * x.Discount) + order.Freight;
dbCmd.Save(order); //Updates 2nd Time
trans.Commit();
}
}
public ProductsRepository(OrmLiteConnectionFactory factory) : base(factory)
{
_factory = factory;
}
public MBase()
{
conexion = new OrmLiteConnectionFactory(BD.Default.conexion,
SqlServerDialect.Provider);
db = conexion.Open();
}
/// <summary>
/// Application specific configuration
/// This method should initialize any IoC resources utilized by your web service classes.
/// </summary>
public override void Configure(Container container)
{
SetConfig(new HostConfig
{
#if DEBUG
DebugMode = true,
WebHostPhysicalPath = Path.GetFullPath(Path.Combine("~".MapServerPath(), "..", "..")),
#else
DebugMode = AppSettings.Get("DebugMode", Env.IsWindows),
#endif
/*
* Consentiamo l'estensione "json" per poter rendere eventualmente
* accessibili via web gli UrbanDataset registrati su file system.
*/
AllowFileExtensions = { "json" },
});
AppDomain.CurrentDomain.SetData("DataDirectory", Path.Combine(Config.WebHostPhysicalPath, "App_Data"));
/*
* Configuriamo il database SQL Server predefinito e registriamo
* una connessione opzionale SQLite denominata "Local".
*/
var defaultDbConn = AppSettings.GetConnectionString("DefaultConnection")
?? @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|aspnetdb.mdf;Integrated Security=True";
var dbFactory = new OrmLiteConnectionFactory(defaultDbConn, SqlServer2012Dialect.Provider);
dbFactory.RegisterConnection("Local", Path.Combine(Constants.ApplicationDataFolder, "db.sqlite"), SqliteDialect.Provider);
container.Register <IDbConnectionFactory>(dbFactory);
// Wrap all code in using statement to not forget about using db.Close()
using (var db = dbFactory.Open("Local"))
{
db.CreateTableIfNotExists <PushResponseLog>();
db.CreateTableIfNotExists <Collaboration>();
}
CsvConfig.ItemSeperatorString = CultureInfo.CurrentCulture.TextInfo.ListSeparator;
JsConfig.SkipDateTimeConversion = true;
JsConfig <DateTime> .SerializeFn = dateTime => dateTime.ToString(DateTimeSerializer.DateTimeFormatSecondsNoOffset);
container.RegisterAs <FileScanJob, IJob>(); // TODO: Come registrare gli altri "IJob"?
var quartzConfig = ConfigureQuartz();
var quartzFeature = new QuartzFeature {
Config = quartzConfig
};
Plugins.Add(quartzFeature);
Plugins.Add(new RazorFormat());
Plugins.Add(new AdminFeature());
Plugins.Add(new OpenApiFeature());
/*
* I validatori (ad esempio "UpdateDeviceValidator") sono definiti
* nell'assembly contenente le implementazioni dei servizi web.
*/
Plugins.Add(new ValidationFeature());
Plugins.Add(new AutoQueryFeature
{
IncludeTotal = true,
});
/*
* Per utilizzare AutoQuery CRUD implementiamo una mappatura con
* l'API Auto Mapping Populator incorporata di ServiceStack.
*/
AutoMapping.RegisterPopulator((Dictionary <string, object> target, UpdateDevice source) =>
{
target[nameof(Device.Location)] = source.ConvertTo <LocationInfo>();
});
}
public static OrmLiteConnectionFactory CreateSqlServerDbFactory()
{
var dbFactory = new OrmLiteConnectionFactory(Config.SqlServerBuildDb, SqlServerDialect.Provider);
return(dbFactory);
}
public override void Configure(Container container)
{
// ServiceStack config
SetConfig(new HostConfig()
{
//HandlerFactoryPath = "/api",
DebugMode = true,
DefaultRedirectPath = "index.html",
StrictMode = false
});
// register app settings in container for use later
container.Register <TextFileSettings>(new TextFileSettings("appsettings.txt"));
// master db
var dbFactory = new OrmLiteConnectionFactory(AppSettings.Get <string>("MySQL"), MySqlDialect.Provider);
container.Register <IDbConnectionFactory>(dbFactory);
// read-only db
dbFactory.RegisterConnection("ReadOnly", AppSettings.Get <string>("MySQLRead"), MySqlDialect.Provider);
// init tables if they don't exist
using (var db = dbFactory.OpenDbConnection())
{
db.CreateTableIfNotExists <Title>();
db.CreateTableIfNotExists <NZB>();
db.CreateTableIfNotExists <Download>();
db.CreateTableIfNotExists <Vote>();
db.CreateTableIfNotExists <APIKey>();
db.CreateTableIfNotExists <APIRequest>();
db.CreateTableIfNotExists <Category>();
db.CreateTableIfNotExists <Count>();
db.CreateTableIfNotExists <TVEpisode>();
db.CreateTableIfNotExists <Video>();
}
// auto query is used for paging/querying titles
Plugins.Add(new AutoQueryFeature {
MaxLimit = 100
});
// configure redis as cache
string redisHost = AppSettings.Get("Redis", defaultValue: "localhost");
var redisClient = new PooledRedisClientManager(new string[] { redisHost });
var cacheClient = new RedisClientManagerCacheClient(redisClient);
container.Register <ICacheClient>(cacheClient);
cacheClient.InitSchema();
// register auth repo
var authRepo = new FastNZBOrmLiteAuthRepository(dbFactory);
container.Register <IUserAuthRepository>(authRepo);
authRepo.InitSchema();
Plugins.Add(new AuthFeature(() => new FastNZBUserSession(),
new IAuthProvider[] {
new CredentialsAuthProvider(), //HTML Form post of UserName/Password credentials
})
{
HtmlRedirect = null,
IncludeRegistrationService = false,
MaxLoginAttempts = 5,//appSettings.Get("MaxLoginAttempts", 5),
IncludeAssignRoleServices = false,
ValidateUniqueEmails = false,
ValidateUniqueUserNames = false,
});
Plugins.Add(new RegistrationFeature()
{
AtRestPath = "/api/register"
});
}
public MUserType()
{
_conexion = new OrmLiteConnectionFactory(DBAccess.Default.connection, SqlServerDialect.Provider);
_db = _conexion.Open();
}
public async Task RunAsync(int iterations)
{
using (var connection = GetOpenConnection())
{
var tests = new Tests();
// Linq2SQL
Try(() =>
{
var l2scontext1 = GetL2SContext(connection);
tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq2Sql: Normal");
var l2scontext2 = GetL2SContext(connection);
var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id));
tests.Add(id => compiledGetPost(l2scontext2, id), "Linq2Sql: Compiled");
var l2scontext3 = GetL2SContext(connection);
tests.Add(id => l2scontext3.ExecuteQuery <Post>("select * from Posts where Id = {0}", id).First(), "Linq2Sql: ExecuteQuery");
}, "LINQ-to-SQL");
// Entity Framework
Try(() =>
{
var entityContext = new EFContext(connection);
tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity Framework");
var entityContext2 = new EFContext(connection);
tests.Add(id => entityContext2.Database.SqlQuery <Post>("select * from Posts where Id = {0}", id).First(), "Entity Framework: SqlQuery");
//var entityContext3 = new EFContext(connection);
//tests.Add(id => entityFrameworkCompiled(entityContext3, id), "Entity Framework CompiledQuery");
//var entityContext4 = new EFContext(connection);
//tests.Add(id => entityContext4.Posts.Where("it.Id = @id", new System.Data.Objects.ObjectParameter("id", id)).First(), "Entity Framework ESQL");
var entityContext5 = new EFContext(connection);
tests.Add(id => entityContext5.Posts.AsNoTracking().First(p => p.Id == id), "Entity Framework: No Tracking");
}, "Entity Framework");
// Dapper
Try(() =>
{
var mapperConnection = GetOpenConnection();
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Query (buffered)");
tests.Add(id => mapperConnection.Query <Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Query (non-buffered)");
tests.Add(id => mapperConnection.QueryFirstOrDefault <Post>("select * from Posts where Id = @Id", new { Id = id }), "Dapper: QueryFirstOrDefault");
var mapperConnection2 = GetOpenConnection();
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Dynamic Query (buffered)");
tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Dynamic Query (non-buffered)");
tests.Add(id => mapperConnection2.QueryFirstOrDefault("select * from Posts where Id = @Id", new { Id = id }), "Dapper: Dynamic QueryFirstOrDefault");
// dapper.contrib
var mapperConnection3 = GetOpenConnection();
tests.Add(id => mapperConnection3.Get <Post>(id), "Dapper.Contrib");
}, "Dapper");
// Massive
Try(() =>
{
var massiveModel = new DynamicModel(ConnectionString);
var massiveConnection = GetOpenConnection();
tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Massive: Dynamic ORM Query");
}, "Massive");
// PetaPoco
Try(() =>
{
// PetaPoco test with all default options
var petapoco = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");
petapoco.OpenSharedConnection();
tests.Add(id => petapoco.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Normal");
// PetaPoco with some "smart" functionality disabled
var petapocoFast = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");
petapocoFast.OpenSharedConnection();
petapocoFast.EnableAutoSelect = false;
petapocoFast.EnableNamedParams = false;
petapocoFast.ForceDateTimesToUtc = false;
tests.Add(id => petapocoFast.Fetch <Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Fast");
}, "PetaPoco");
// NHibernate
Try(() =>
{
var nhSession1 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession1.CreateSQLQuery(@"select * from Posts where Id = :id")
.SetInt32("id", id)
.List(), "NHibernate: SQL");
var nhSession2 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession2.CreateQuery(@"from Post as p where p.Id = :id")
.SetInt32("id", id)
.List(), "NHibernate: HQL");
var nhSession3 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession3.CreateCriteria <Post>()
.Add(Restrictions.IdEq(id))
.List(), "NHibernate: Criteria");
var nhSession4 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession4
.Query <Post>()
.First(p => p.Id == id), "NHibernate: LINQ");
var nhSession5 = NHibernateHelper.OpenSession();
tests.Add(id => nhSession5.Get <Post>(id), "NHibernate: Session.Get");
}, "NHibernate");
// Simple.Data
Try(() =>
{
var sdb = Simple.Data.Database.OpenConnection(ConnectionString);
tests.Add(id => sdb.Posts.FindById(id).FirstOrDefault(), "Simple.Data");
}, "Simple.Data");
// Belgrade
Try(() =>
{
var query = new Belgrade.SqlClient.SqlDb.QueryMapper(ConnectionString);
tests.AddAsync(id => query.ExecuteReader("SELECT TOP 1 * FROM Posts WHERE Id = " + id,
reader =>
{
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4);
post.Counter2 = reader.IsDBNull(5) ? null : (int?)reader.GetInt32(5);
post.Counter3 = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);
post.Counter4 = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7);
post.Counter5 = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8);
post.Counter6 = reader.IsDBNull(9) ? null : (int?)reader.GetInt32(9);
post.Counter7 = reader.IsDBNull(10) ? null : (int?)reader.GetInt32(10);
post.Counter8 = reader.IsDBNull(11) ? null : (int?)reader.GetInt32(11);
post.Counter9 = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12);
}), "Belgrade Sql Client");
}, "Belgrade Sql Client");
//Susanoo
var susanooDb = new DatabaseManager(connection);
var susanooPreDefinedCommand =
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize();
var susanooDynamicPreDefinedCommand =
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize();
tests.Add(Id =>
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <Post>()
.Realize()
.Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Cache Retrieval");
tests.Add(Id =>
CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
.DefineResults <dynamic>()
.Realize()
.Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Cache Retrieval");
tests.Add(Id => susanooDynamicPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Static");
tests.Add(Id => susanooPreDefinedCommand
.Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Static");
//ServiceStack's OrmLite:
Try(() =>
{
var dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider);
var db = dbFactory.Open();
tests.Add(id => db.SingleById <Post>(id), "ServiceStack.OrmLite: SingleById");
}, "ServiceStack.OrmLite");
// Hand Coded
var postCommand = new SqlCommand()
{
Connection = connection,
CommandText = @"select Id, [Text], [CreationDate], LastChangeDate,
Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id"
};
var idParam = postCommand.Parameters.Add("@Id", SqlDbType.Int);
tests.Add(id =>
{
idParam.Value = id;
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
var post = new Post();
post.Id = reader.GetInt32(0);
post.Text = reader.GetNullableString(1);
post.CreationDate = reader.GetDateTime(2);
post.LastChangeDate = reader.GetDateTime(3);
post.Counter1 = reader.GetNullableValue <int>(4);
post.Counter2 = reader.GetNullableValue <int>(5);
post.Counter3 = reader.GetNullableValue <int>(6);
post.Counter4 = reader.GetNullableValue <int>(7);
post.Counter5 = reader.GetNullableValue <int>(8);
post.Counter6 = reader.GetNullableValue <int>(9);
post.Counter7 = reader.GetNullableValue <int>(10);
post.Counter8 = reader.GetNullableValue <int>(11);
post.Counter9 = reader.GetNullableValue <int>(12);
}
}, "Hand Coded");
// Subsonic isn't maintained anymore - doesn't import correctly
//Try(() =>
// {
// // Subsonic ActiveRecord
// tests.Add(id => 3SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault");
// // Subsonic coding horror
// SubSonic.tempdbDB db = new SubSonic.tempdbDB();
// tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList<Post>(), "SubSonic Coding Horror");
//}, "Subsonic");
//// BLToolkit - doesn't import correctly in the new .csproj world
//var db1 = new DbManager(GetOpenConnection());
//tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList<Post>(), "BLToolkit");
#if !COREFX
var table = new DataTable
{
Columns =
{
{ "Id", typeof(int) },
{ "Text", typeof(string) },
{ "CreationDate", typeof(DateTime) },
{ "LastChangeDate", typeof(DateTime) },
{ "Counter1", typeof(int) },
{ "Counter2", typeof(int) },
{ "Counter3", typeof(int) },
{ "Counter4", typeof(int) },
{ "Counter5", typeof(int) },
{ "Counter6", typeof(int) },
{ "Counter7", typeof(int) },
{ "Counter8", typeof(int) },
{ "Counter9", typeof(int) },
}
};
tests.Add(id =>
{
idParam.Value = id;
object[] values = new object[13];
using (var reader = postCommand.ExecuteReader())
{
reader.Read();
reader.GetValues(values);
table.Rows.Add(values);
}
}, "DataTable via IDataReader.GetValues");
#endif
Console.WriteLine();
Console.WriteLine("Running...");
await tests.RunAsync(iterations).ConfigureAwait(false);
}
}
public void Can_connect_to_database()
{
using (var db = new OrmLiteConnectionFactory(ConnectionString, Firebird4Dialect.Provider).OpenDbConnection())
{
}
}
public override void Configure(Container container)
{
//ServiceStack.Text.JsConfig.EmitCamelCaseNames = true; ! DO NOT USE THIS !
//Feature disableFeatures = Feature.Xml | Feature.Jsv | Feature.Csv | Feature.Soap11 | Feature.Soap12 | Feature.Soap;
SetConfig(new EndpointHostConfig
{
DebugMode = false,
UseCustomMetadataTemplates = true,
DefaultContentType = ContentType.Json,
GlobalResponseHeaders =
{
{ "Access-Control-Allow-Origin", "*" },
{ "Access-Control-Allow-Methods", "GET, POST, PUT, DELETE, OPTIONS" },
{ "Access-Control-Allow-Headers", "Content-Type, Signature" }
},
EnableFeatures = Feature.Json | Feature.Metadata
//ServiceStackHandlerFactoryPath = "api"
});
//CorsFeature cf = new CorsFeature(allowedOrigins: "*", allowedMethods: "GET, POST, PUT, DELETE, OPTIONS", allowedHeaders: "Content-Type, Signature", allowCredentials: false);
//this.Plugins.Add(cf);
this.Plugins.Add(new SwaggerFeature());
//DB
string strConnectionString = GetConnectionString();
var dbConnectionFactory = new OrmLiteConnectionFactory(strConnectionString, SqlServerDialect.Provider)
{
ConnectionFilter =
x =>
new ProfiledDbConnection(x, Profiler.Current)
};
container.Register <IDbConnectionFactory>(dbConnectionFactory);
var connectString = new WebApi.ServiceModel.ConnectStringFactory(strConnectionString);
container.Register <WebApi.ServiceModel.IConnectString>(connectString);
var secretKey = new WebApi.ServiceModel.SecretKeyFactory(strSecretKey);
container.Register <WebApi.ServiceModel.ISecretKey>(secretKey);
//Auth
container.RegisterAutoWired <WebApi.ServiceModel.Auth>();
//WMS
container.RegisterAutoWired <WebApi.ServiceModel.Wms.Wms_Login_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Wms.List_Imgr1_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Wms.List_Impr1_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Wms.List_Imgr2_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Wms.List_Imgi1_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Wms.List_Imgi2_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Wms.List_Imsn1_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Wms.Confirm_Imgr1_Logic>();
//TMS
container.RegisterAutoWired <WebApi.ServiceModel.Tms.Tms_Login_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Tms.List_Jmjm6_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Tms.List_JobNo_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Tms.Update_Done_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Tms.List_Container_Logic>();
//Freight
container.RegisterAutoWired <WebApi.ServiceModel.Freight.Freight_Login_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Freight.Saus_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Freight.Rcbp_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Freight.Smsa_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Freight.Plvi_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Freight.Rcvy_Logic>();
container.RegisterAutoWired <WebApi.ServiceModel.Freight.Tracking_Logic>();
//Common
container.RegisterAutoWired <WebApi.ServiceModel.Common.List_Rcbp1_Logic>();
}
public MNoticiasDal()
{
cn = new SqlConnection(BD.Default.conexion);
_conex = new OrmLiteConnectionFactory(BD.Default.conexion, SqlServerDialect.Provider);
}
public static OrmLiteConnectionFactory CreateSqlServerDbFactory()
{
var dbFactory = new OrmLiteConnectionFactory(SqlServerDb.DefaultConnection, SqlServerDialect.Provider);
return(dbFactory);
}
public static void Main(string[] args)
{
if (args.Length != 2)
{
if (args.Length > 0)
{
("Invalid Arguments: " + args.Join(" ")).Print();
}
"Syntax:".Print();
"northwind-data [provider] [connectionString]".Print();
"\nAvailable Data Providers:".Print();
Providers.Each(x => $" - {x}".Print());
return;
}
var hasOrmLite = LicenseUtils.HasLicensedFeature(LicenseFeature.OrmLite);
var dbFactory = new OrmLiteConnectionFactory("~/../../apps/northwind.sqlite".MapProjectPath(), SqliteDialect.Provider);
var db = dbFactory.Open();
var categories = db.Select <Category>();
var customers = db.Select <Customer>();
var employees = db.Select <Employee>();
var orders = db.Select <Order>();
var orderDetails = db.Select <OrderDetail>();
var products = db.Select <Product>();
var regions = db.Select <Region>();
var shippers = db.Select <Shipper>();
var suppliers = db.Select <Supplier>();
var territories = db.Select <Territory>();
var employeeTerritories = hasOrmLite ? db.Select <EmployeeTerritory>() : null;
db.Dispose();
var provider = ResolveValue(args[0]);
var connectionString = ResolveValue(args[1]);
dbFactory = GetDbFactory(provider, connectionString);
if (dbFactory != null)
{
using (db = dbFactory.Open())
{
db.DropAndCreateTable <Category>();
$"Created table {nameof(Category)}".Print();
db.DropAndCreateTable <Customer>();
$"Created table {nameof(Customer)}".Print();
db.DropAndCreateTable <Employee>();
$"Created table {nameof(Employee)}".Print();
db.DropAndCreateTable <Order>();
$"Created table {nameof(Order)}".Print();
db.DropAndCreateTable <OrderDetail>();
$"Created table {nameof(OrderDetail)}".Print();
db.DropAndCreateTable <Product>();
$"Created table {nameof(Product)}".Print();
db.DropAndCreateTable <Region>();
$"Created table {nameof(Region)}".Print();
db.DropAndCreateTable <Shipper>();
$"Created table {nameof(Shipper)}".Print();
db.DropAndCreateTable <Supplier>();
$"Created table {nameof(Supplier)}".Print();
db.DropAndCreateTable <Territory>();
$"Created table {nameof(Territory)}".Print();
if (hasOrmLite)
{
db.DropAndCreateTable <EmployeeTerritory>();
$"Created table {nameof(EmployeeTerritory)}".Print();
}
"".Print();
db.InsertAll(categories);
$"Inserted {categories.Count} rows in {nameof(Category)}".Print();
db.InsertAll(customers);
$"Inserted {customers.Count} rows in {nameof(Customer)}".Print();
db.InsertAll(employees);
$"Inserted {employees.Count} rows in {nameof(Employee)}".Print();
db.InsertAll(orders);
$"Inserted {orders.Count} rows in {nameof(Order)}".Print();
db.InsertAll(orderDetails);
$"Inserted {orderDetails.Count} rows in {nameof(OrderDetail)}".Print();
db.InsertAll(products);
$"Inserted {products.Count} rows in {nameof(Product)}".Print();
db.InsertAll(regions);
$"Inserted {regions.Count} rows in {nameof(Region)}".Print();
db.InsertAll(shippers);
$"Inserted {shippers.Count} rows in {nameof(Shipper)}".Print();
db.InsertAll(suppliers);
$"Inserted {suppliers.Count} rows in {nameof(Supplier)}".Print();
db.InsertAll(territories);
$"Inserted {territories.Count} rows in {nameof(Territory)}".Print();
if (hasOrmLite)
{
db.InsertAll(employeeTerritories);
$"Inserted {employeeTerritories.Count} rows in {nameof(EmployeeTerritory)}".Print();
}
}
}
else if (provider == "redis")
{
var redisManager = new RedisManagerPool(connectionString);
using (var redis = redisManager.GetClient())
{
redis.StoreAll(categories);
redis.StoreAll(customers);
redis.StoreAll(employees);
redis.StoreAll(employeeTerritories);
redis.StoreAll(orders);
redis.StoreAll(orderDetails);
redis.StoreAll(products);
redis.StoreAll(regions);
redis.StoreAll(shippers);
redis.StoreAll(suppliers);
redis.StoreAll(territories);
}
}
else
{
$"Unknown Provider: {provider}".Print();
"Available Providers:".Print();
Providers.Join(", ").Print();
}
}
public AdoNetDatabase(String connectionString, IOrmLiteDialectProvider dialectProvider)
{
_dbFactory = new OrmLiteConnectionFactory(connectionString, dialectProvider);
}
internal VersioningRepository(OrmLiteConnectionFactory db)
{
this.db = db;
EnsureTableCreated();
}
public MUsuario()
{
_conexion = new OrmLiteConnectionFactory(BD.Default.conexion, SqlServerDialect.Provider);
_db = _conexion.Open();
}
public static OrmLiteConnectionFactory CreateSqliteMemoryDbFactory()
{
var dbFactory = new OrmLiteConnectionFactory(Config.SqliteMemoryDb, SqliteDialect.Provider);
return(dbFactory);
}
public override ServiceStackHost Init()
{
ServicePointManager.UseNagleAlgorithm = false;
ServicePointManager.DefaultConnectionLimit = 1000;
var conf = NLog.Config.ConfigurationItemFactory.Default;
conf.LayoutRenderers.RegisterDefinition("logsdir", typeof(LogsDir));
conf.LayoutRenderers.RegisterDefinition("Instance", typeof(Library.Logging.Instance));
NLog.LogManager.Configuration = new NLog.Config.XmlLoggingConfiguration($"{AppDomain.CurrentDomain.BaseDirectory}/logging.config");
LogManager.LogFactory = new global::ServiceStack.Logging.NLogger.NLogFactory();
AppDomain.CurrentDomain.UnhandledException += UnhandledExceptionTrapper;
AppDomain.CurrentDomain.FirstChanceException += ExceptionTrapper;
NServiceBus.Logging.LogManager.Use <NServiceBus.NLogFactory>();
IRedisClientsManager redisClient = null;
ICacheClient cacheClient;
IServerEvents serverEvents;
var connectionString = ConfigurationManager.ConnectionStrings["Redis"];
if (connectionString == null)
{
cacheClient = new MemoryCacheClient();
serverEvents = new MemoryServerEvents
{
IdleTimeout = TimeSpan.FromSeconds(30),
NotifyChannelOfSubscriptions = false
};
}
else
{
redisClient = new BasicRedisClientManager(connectionString.ConnectionString);
cacheClient = new RedisClientManagerCacheClient(redisClient);
serverEvents = new RedisServerEvents(redisClient)
{
Timeout = TimeSpan.FromSeconds(30),
NotifyChannelOfSubscriptions = false,
};
}
IDbConnectionFactory sql = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
var connectionStringSql = ConfigurationManager.ConnectionStrings["SQL"];
if (connectionStringSql != null)
{
sql = new OrmLiteConnectionFactory(connectionStringSql.ConnectionString, SqlServer2012Dialect.Provider)
{
ConnectionFilter = x => new ProfiledDbConnection(x, Profiler.Current)
};
}
var pulse = ConfigureDemo();
var rabbit = ConfigureRabbit();
ConfigureMetrics();
_container = new Container(x =>
{
if (redisClient != null)
{
x.For <IRedisClientsManager>().Use(redisClient);
}
x.For <IDbConnectionFactory>().Use(sql);
x.For <ISubscriptionStorage>().Use <MssqlStorage>();
x.For <IManager>().Use <Manager>();
x.For <IFuture>().Use <Future>().Singleton();
x.For <ICacheClient>().Use(cacheClient);
x.For <IServerEvents>().Use(serverEvents);
x.For <ISubscriptionManager>().Use <SubscriptionManager>().Singleton();
x.For <IDemo>().Use(pulse);
x.For <IConnection>().Use(rabbit);
x.Scan(y =>
{
y.TheCallingAssembly();
y.AssembliesFromApplicationBaseDirectory((assembly) => assembly.FullName.StartsWith("Presentation.ServiceStack"));
y.WithDefaultConventions();
y.AddAllTypesOf <ISetup>();
y.AddAllTypesOf <ICommandMutator>();
y.ConnectImplementationsToTypesClosing(typeof(Q.IChange <,>));
});
});
// Do this before bus starts
InitiateSetup();
SetupApplication().Wait();
var bus = InitBus().Result;
_container.Configure(x => x.For <IMessageSession>().Use(bus).Singleton());
return(base.Init());
}
public UnitOfWork(OrmLiteConnectionFactory factory)
{
_factory = factory;
}
public VersioningRepository(string connectionString)
{
db = new OrmLiteConnectionFactory(connectionString, new SqliteOrmLiteDialectProviderFixed());
EnsureTableCreated();
}
public DataRepository(OrmLiteConnectionFactory dbConnectionFactory)
{
DbConnectionFactory = dbConnectionFactory;
}
public DatabaseContext(OrmLiteConnectionFactory dbConnectionFactory)
{
this._dbConnectionFactory = dbConnectionFactory;
}
public MovieController(OrmLiteConnectionFactory db) : base(db)
{
}
static void Main(string[] args)
{
using (var db = new OrmLiteConnectionFactory("Data Source=MAHMOUD\\MAHMOUD;Initial Catalog=AdventureWorks2012;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False", new SqlServerOrmLiteDialectProvider()).OpenDbConnection())
{
//var ColorANdSaftey = db.Select<products>().Where(x => x.Color == "black" && x.SafetyStockLevel<1000);
//foreach (var item in ColorANdSaftey)
//{
// Console.WriteLine("the item name is ", item.Name);
//}
//var NumberOfEmployee = db.Scalar<int>(db.From<Employee>().Select(Sql.Count("*")));
//Console.WriteLine(NumberOfEmployee);
//var Departments = db.Select<EmployeeDepartmentHistory>().OrderBy(x => x.DepartmentID);
//foreach (var department in Departments)
//{
// Console.WriteLine(department.DepartmentID);
//}
//Console.WriteLine("\n");
//Console.WriteLine("");
//var product = db.Select<product>();
//var max = product.GroupBy(w => w.ProductID, w => w.StandardCost, (groupkey, invtotal) => new
// {
// key = groupkey,
// standarcost = invtotal.Max()
// });
// foreach (var item in max)
//{
// Console.WriteLine("{0} , {1}", item.key, item.standarcost);
//}
var productinventory = db.Select <ProductInventory>();
var query = productinventory.GroupBy(x => new
{
prouductid = x.ProductID,
locationid = x.LocationID
}, x => x.Quantity, (groupkey, Maximum) => new
{
key = groupkey,
Quantity = Maximum.Max()
});
foreach (var item in query)
{
Console.WriteLine("{0} , {1}, {2}", item.key.prouductid, item.key.locationid, item.Quantity);
}
//var NightShift = db.Select<Person>(db.From<Person>().Join<EmployeeDepartmentHistory>().Join<Shift, EmployeeDepartmentHistory>().Where<Shift>(o => o.Name == "Night").OrderBy(x => x.FirstName));
//foreach (var shift in NightShift)
//{
// Console.WriteLine(shift.FirstName);
//}
Console.ReadLine();
}
}
public MControlAuditoriasJefe()
{
_conexion = new OrmLiteConnectionFactory(BD.Default.Conexion, SqlServerDialect.Provider);
_db = _conexion.Open();
}
public DBUtility()
{
_factory = new OrmLiteConnectionFactory(ConfigurationManager.ConnectionStrings["StockScreenerConnection"].ConnectionString, SqlServerDialect.Provider);
//_factory.Run(db => db.CreateTable<FinancialMetric>(overwrite: false));
}
public void Can_get_data_from_TableWithNamigStrategy_AfterChangingNamingStrategy()
{
OrmLite.OrmLiteConfig.DialectProvider.NamingStrategy = OrmLite.OrmLiteConfig.DialectProvider.NamingStrategy = new PrefixNamingStrategy
{
TablePrefix = "tab_",
ColumnPrefix = "col_",
};
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithOnlyStringFields>(true);
ModelWithOnlyStringFields m = new ModelWithOnlyStringFields()
{
Id = "998", AlbumId = "112", AlbumName = "ElectroShip", Name = "QueryByExample"
};
db.Save <ModelWithOnlyStringFields>(m);
var modelFromDb = db.Where <ModelWithOnlyStringFields>(new { Name = "QueryByExample" })[0];
Assert.AreEqual(m.Name, modelFromDb.Name);
modelFromDb = db.SingleById <ModelWithOnlyStringFields>("998");
Assert.AreEqual(m.Name, modelFromDb.Name);
}
OrmLite.OrmLiteConfig.DialectProvider.NamingStrategy = new OrmLiteNamingStrategyBase();
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithOnlyStringFields>(true);
ModelWithOnlyStringFields m = new ModelWithOnlyStringFields()
{
Id = "998", AlbumId = "112", AlbumName = "ElectroShip", Name = "QueryByExample"
};
db.Save <ModelWithOnlyStringFields>(m);
var modelFromDb = db.Where <ModelWithOnlyStringFields>(new { Name = "QueryByExample" })[0];
Assert.AreEqual(m.Name, modelFromDb.Name);
modelFromDb = db.SingleById <ModelWithOnlyStringFields>("998");
Assert.AreEqual(m.Name, modelFromDb.Name);
}
OrmLite.OrmLiteConfig.DialectProvider.NamingStrategy = OrmLite.OrmLiteConfig.DialectProvider.NamingStrategy = new PrefixNamingStrategy
{
TablePrefix = "tab_",
ColumnPrefix = "col_",
};
using (var db = new OrmLiteConnectionFactory(ConnectionString, FirebirdDialect.Provider).Open())
{
db.CreateTable <ModelWithOnlyStringFields>(true);
ModelWithOnlyStringFields m = new ModelWithOnlyStringFields()
{
Id = "998", AlbumId = "112", AlbumName = "ElectroShip", Name = "QueryByExample"
};
db.Save <ModelWithOnlyStringFields>(m);
var modelFromDb = db.Where <ModelWithOnlyStringFields>(new { Name = "QueryByExample" })[0];
Assert.AreEqual(m.Name, modelFromDb.Name);
modelFromDb = db.SingleById <ModelWithOnlyStringFields>("998");
Assert.AreEqual(m.Name, modelFromDb.Name);
}
OrmLite.OrmLiteConfig.DialectProvider.NamingStrategy = new OrmLiteNamingStrategyBase();
}
public static OrmLiteConnectionFactory CreatePostgreSqlDbFactory()
{
var dbFactory = new OrmLiteConnectionFactory(Config.PostgreSqlDb, PostgreSqlDialect.Provider);
return(dbFactory);
}
private StorageService()
{
ConnectionString = "Server=127.0.0.1;Port=5432;User Id=postgres; Password=1337; Database=Booklette.Main;";
DbFactory = new OrmLiteConnectionFactory(ConnectionString, PostgreSqlDialect.Provider);
InitStorage();
}
