public void CreateCatalog()
{
var connectionString = "Filename=./sqlite.db; Mode=ReadWriteCreate";
var catalog = new Catalog("test_db");
var schema = catalog.AddSchema(_schemaName);
var table = schema.AddTable("Test");
var colPk = table.AddColumn("TestId", DbType.Int64);
colPk.IsPrimaryKey = true;
var colName = table.AddColumn("Name", DbType.String);
colName.MaxLength = 256;
colName.IsNullable = false;
var builder = new SqliteScriptBuilder();
var sql = builder.BuildCreateScript(catalog);
IParameterFactory parameterFactory = new SqliteParameterFactory();
IConnectionFactory connectionFactory = new SqliteConnectionFactory();
using (var connection = connectionFactory.Open(connectionString))
using (var command = new Command(connection, parameterFactory))
{
var affected = command.Execute(sql);
//Assert.IsNotNull(dataSet);
//Assert.AreEqual(1, dataSet.Tables.Count);
//Assert.IsTrue(dataSet.Tables[0].Rows.Count > 0);
}
}
public void SelectAllProductsDto()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var product1 = new ProductDto {
Name = "Product #1"
};
dbConnection.Execute(new InsertProduct().Query(product1));
var product2 = new ProductDto {
Name = "Product #2"
};
dbConnection.Execute(new InsertProduct().Query(product2));
var product3 = new ProductDto {
Name = "Product #3"
};
dbConnection.Execute(new InsertProduct().Query(product3));
QueryObject all = new SelectProduct().All();
IEnumerable <ProductDto> productDtos = dbConnection.Query <ProductDto>(all);
Assert.AreEqual(3, productDtos.Count());
}
}
public DbUpdater(ILogger <DbUpdater> logger, SqliteConnectionFactory cnxFactory, IVersionProvider versionProvider)
{
_logger = logger;
_dbConnectionFactory = cnxFactory;
_versionProvider = versionProvider;
_resourcesAssembly = typeof(DbUpdater).Assembly;
}
public static void OpenConnection_WhenInvoked_ReturnsConnectionInOpenState()
{
var factory = new SqliteConnectionFactory("Data Source=:memory:");
using var connection = factory.OpenConnection();
Assert.That(connection.State, Is.EqualTo(ConnectionState.Open));
}
public static async Task OpenConnectionAsync_WhenInvoked_ReturnsConnectionInOpenState()
{
var factory = new SqliteConnectionFactory("Data Source=:memory:");
using var connection = await factory.OpenConnectionAsync().ConfigureAwait(false);
Assert.That(connection.State, Is.EqualTo(ConnectionState.Open));
}
public void SelectAllProductsDto()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var query = new SQLinq<ProductDto>();
Assert.AreEqual(3, dbConnection.Query(query).Count());
}
}
public void SelectAllProductsDto()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var query = new SQLinq <ProductDto>();
Assert.AreEqual(3, dbConnection.Query(query).Count());
}
}
public void SelectWithEndsWith()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var query = new SQLinq <ProductDto>()
.Where(x => x.Name.EndsWith(ThirdProductName));
Assert.AreEqual(1, dbConnection.Query(query).Count());
}
}
public void SelectWithEndsWith()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var query = new SQLinq<ProductDto>()
.Where(x => x.Name.EndsWith(ThirdProductName));
Assert.AreEqual(1, dbConnection.Query(query).Count());
}
}
public void Constructor()
{
IParameterFactory parameterFactory = new SqliteParameterFactory();
IConnectionFactory connectionFactory = new SqliteConnectionFactory();
using (var connection = connectionFactory.Open(_connectionString))
using (var command = new Command(connection, parameterFactory))
{
Assert.IsNotNull(command);
}
}
public void SelectProductDtoById()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var query = new SQLinq <ProductDto>()
.Where(x => x.Product_Id == 1);
var firstProduct = dbConnection.Query(query).First();
Assert.AreEqual(FirstProductName, firstProduct.Name);
}
}
public void InsertAndFetchNewId()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var insertProduct = new InsertProduct();
var product = new ProductDto {Name = "New Product"};
product.Id = (int) dbConnection.Query<Int64>(insertProduct.Query(product)).Single();
Assert.AreNotEqual(0, product.Id);
}
}
public void SelectProductDtoById()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var query = new SQLinq<ProductDto>()
.Where(x => x.Product_Id == 1);
var firstProduct = dbConnection.Query(query).First();
Assert.AreEqual(FirstProductName, firstProduct.Name);
}
}
public void SelectWithLogicalExpression()
{
using (IDbConnection dbConnection = SqliteConnectionFactory.Create())
{
var product1 = new ProductDto {
Name = "Product #1"
};
dbConnection.Insert(product1);
Assert.AreEqual(0, dbConnection.Select <ProductDto>(dto => dto.Name == "Other name").Count);
Assert.AreEqual(1, dbConnection.Select <ProductDto>(dto => dto.Name == "Product #1").Count);
}
}
public void SelectWithContains()
{
using (IDbConnection dbConnection = SqliteConnectionFactory.Create())
{
var product1 = new ProductDto {
Name = "Product #1"
};
dbConnection.Insert(product1);
Assert.AreEqual(1, dbConnection.Select <ProductDto>(dto => dto.Name.Contains("odu")).Count);
Assert.AreEqual(0, dbConnection.Select <ProductDto>(dto => dto.Name.Contains("odu ")).Count);
}
}
public void SelectProductDtoById()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var product1 = new ProductDto {Name = "Product #1"};
dbConnection.Execute(new InsertProduct().Query(product1));
QueryObject byId = new SelectProduct().ById(1);
ProductDto productDto = dbConnection.Query<ProductDto>(byId).SingleOrDefault();
Assert.AreEqual(1, productDto.Id);
Assert.AreEqual("Product #1", productDto.Name);
}
}
public void InsertAndFetchNewId()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var insertProduct = new InsertProduct();
var product = new ProductDto {
Name = "New Product"
};
product.Id = (int)dbConnection.Query <Int64>(insertProduct.Query(product)).Single();
Assert.AreNotEqual(0, product.Id);
}
}
public void DeleteProduct()
{
using (IDbConnection dbConnection = SqliteConnectionFactory.Create())
{
var product = new ProductDto {
Name = "New Product"
};
dbConnection.Insert(product);
dbConnection.Delete <ProductDto>(dto => dto.Id == dbConnection.LastInsertId());
Assert.IsNull(dbConnection.SingleById <ProductDto>(1));
}
}
public void SelectProductDtoById()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var product1 = new ProductDto {
Name = "Product #1"
};
dbConnection.Execute(new InsertProduct().Query(product1));
QueryObject byId = new SelectProduct().ById(1);
ProductDto productDto = dbConnection.Query <ProductDto>(byId).SingleOrDefault();
Assert.AreEqual(1, productDto.Id);
Assert.AreEqual("Product #1", productDto.Name);
}
}
public void Manages_borrowed_connection()
{
Func<string, IDbConnection> func = cs =>
{
var factory = new SqliteConnectionFactory { ConnectionString = cs };
return factory.CreateConnection();
};
Database.Install(CreateConnectionString(), func);
var db1 = UnitOfWork.Current;
var db2 = UnitOfWork.Current;
Assert.IsNotNull(db1);
Assert.IsNotNull(db2);
Assert.AreEqual(db1, db2);
}
public void UpdateProductByUpdateOnly()
{
using (IDbConnection dbConnection = SqliteConnectionFactory.Create())
{
var product = new ProductDto {
Name = "New Product"
};
dbConnection.Insert(product);
product.Name = "Changed name";
dbConnection.UpdateOnly(product, dto => new { dto.Name }, dto => dto.Id == 1);
Assert.NotNull(dbConnection.Single <ProductDto>(dto => dto.Name == product.Name));
}
}
static void Main(string[] args)
{
SqliteConnectionFactory sqliteConnection = new SqliteConnectionFactory("database");
IUserRepo userRepo = new UserRepository(sqliteConnection);
ICursaRepo cursaRepository = new CursaRepository(sqliteConnection);
IEchipaRepo echipaRepository = new EchipaRepository(sqliteConnection);
IParticipantRepo participantRepo = new ParticipantRepository(sqliteConnection);
IServices serviceImpl = new ServerImpl(cursaRepository, echipaRepository, participantRepo, userRepo);
// IChatServer serviceImpl = new ChatServerImpl();
SerialChatServer server = new SerialChatServer("127.0.0.1", 55555, serviceImpl);
server.Start();
Console.WriteLine("Server started ...");
//Console.WriteLine("Press <enter> to exit...");
Console.ReadLine();
}
public void UpdateProductName()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var insertProduct = new InsertProduct();
var product = new ProductDto {Name = "Product Name"};
product.Id = (int) dbConnection.Query<Int64>(insertProduct.Query(product)).Single();
var updateProduct = new UpdateProduct();
dbConnection.Execute(updateProduct.NameForAllProducts("new name"));
var selectProduct = new SelectProduct();
ProductDto result = dbConnection.Query<ProductDto>(selectProduct.ById(product.Id)).Single();
StringAssert.AreEqualIgnoringCase("new name", result.Name);
}
}
public void InsertAndFetchNewId()
{
using (IDbConnection dbConnection = SqliteConnectionFactory.Create())
{
var product = new ProductDto {
Name = "New Product"
};
dbConnection.Insert(product);
var result = dbConnection
.Select <ProductDto>(p => p.Name == product.Name)
.First();
Assert.AreNotEqual(0, result.Id);
}
}
public void ExecuteScalor()
{
IParameterFactory parameterFactory = new SqliteParameterFactory();
IConnectionFactory connectionFactory = new SqliteConnectionFactory();
ISqlStatementBuilder sqlBuilder = new SqliteStatementBuilder(parameterFactory);
using (var connection = connectionFactory.Open(_connectionString))
using (var command = new Command(connection, parameterFactory))
{
var sql = sqlBuilder.GetExistsStatement <Test>();
var exists = command.ExecuteScalar <bool>(sql);
Assert.IsTrue(exists);
sql = sqlBuilder.GetExistsStatement <DoesNotExist>();
exists = command.ExecuteScalar <bool>(sql);
Assert.IsFalse(exists);
}
}
public void UpdateProductName()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var insertProduct = new InsertProduct();
var product = new ProductDto {
Name = "Product Name"
};
product.Id = (int)dbConnection.Query <Int64>(insertProduct.Query(product)).Single();
var updateProduct = new UpdateProduct();
dbConnection.Execute(updateProduct.NameForAllProducts("new name"));
var selectProduct = new SelectProduct();
ProductDto result = dbConnection.Query <ProductDto>(selectProduct.ById(product.Id)).Single();
StringAssert.AreEqualIgnoringCase("new name", result.Name);
}
}
public void SelectAllProductsDto()
{
using (IDbConnection dbConnection = new SqliteConnectionFactory().Create())
{
var product1 = new ProductDto {Name = "Product #1"};
dbConnection.Execute(new InsertProduct().Query(product1));
var product2 = new ProductDto {Name = "Product #2"};
dbConnection.Execute(new InsertProduct().Query(product2));
var product3 = new ProductDto {Name = "Product #3"};
dbConnection.Execute(new InsertProduct().Query(product3));
QueryObject all = new SelectProduct().All();
IEnumerable<ProductDto> productDtos = dbConnection.Query<ProductDto>(all);
Assert.AreEqual(3, productDtos.Count());
}
}
public void SelectWithInExpression()
{
using (IDbConnection dbConnection = SqliteConnectionFactory.Create())
{
var product1 = new ProductDto {
Name = "Product #1"
};
dbConnection.Insert(product1);
var product2 = new ProductDto {
Name = "Product #2"
};
dbConnection.Insert(product2);
var product3 = new ProductDto {
Name = "Product #3"
};
dbConnection.Insert(product3);
Assert.AreEqual(3, dbConnection.Select <ProductDto>(dto => Sql.In(dto.Name, "Product #1", "Product #2", "Product #3", "Other")).Count);
}
}
public virtual DbConnection CreateDbConnection() => SqliteConnectionFactory.CreateAndOpen();
public SqliteTrackedPeriodService(SqliteConnectionFactory sqliteConnectionFactory)
: base(sqliteConnectionFactory)
{
}
public PersonsService(SqliteConnectionFactory connectionFactory)
{
_masterConnection = connectionFactory.CreateConnection();
}
public SqliteAccountService(SqliteConnectionFactory sqliteConnectionFactory)
: base(sqliteConnectionFactory)
{
}
//[Ignore]
public void SqlLite_Integration()
{
var logger = new ConsoleLogger(LogLevel.Debug);
// CORRECT DATA AND INITIAL LOAD
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2, cn.Execute(@"
UPDATE [Order Details] SET UnitPrice = 14.40, Quantity = 42 WHERE OrderId = 10253 AND ProductId = 39;
UPDATE Orders SET CustomerID = 'CHOPS', Freight = 22.98 WHERE OrderId = 10254;
"));
}
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile + "?Mode=init", logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new CSharpModule(), new SqlServerModule(), new SqliteModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqliteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindStar;"));
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT Inserts FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 1 LIMIT 1;"));
}
// FIRST DELTA, NO CHANGES
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new CSharpModule(), new SqlServerModule(), new SqliteModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqliteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(2155, cn.ExecuteScalar <int>("SELECT COUNT(*) FROM NorthWindStar;"));
Assert.AreEqual(0, cn.ExecuteScalar <int>("SELECT Inserts+Updates+Deletes FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 9 LIMIT 1;"));
}
// CHANGE 2 FIELDS IN 1 RECORD IN MASTER TABLE THAT WILL CAUSE CALCULATED FIELD TO BE UPDATED TOO
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
const string sql = @"UPDATE [Order Details] SET UnitPrice = 15, Quantity = 40 WHERE OrderId = 10253 AND ProductId = 39;";
Assert.AreEqual(1, cn.Execute(sql));
}
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new CSharpModule(), new SqlServerModule(), new SqliteModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqliteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT Updates FROM NorthWindControl WHERE Entity = 'Order Details' AND BatchId = 17 LIMIT 1;"));
Assert.AreEqual(15.0M, cn.ExecuteScalar <decimal>("SELECT OrderDetailsUnitPrice FROM NorthWindStar WHERE OrderDetailsOrderId= 10253 AND OrderDetailsProductId = 39;"));
Assert.AreEqual(40, cn.ExecuteScalar <int>("SELECT OrderDetailsQuantity FROM NorthWindStar WHERE OrderDetailsOrderId= 10253 AND OrderDetailsProductId = 39;"));
Assert.AreEqual(15.0 * 40, cn.ExecuteScalar <int>("SELECT OrderDetailsExtendedPrice FROM NorthWindStar WHERE OrderDetailsOrderId= 10253 AND OrderDetailsProductId = 39;"));
}
// CHANGE 1 RECORD'S CUSTOMERID AND FREIGHT ON ORDERS TABLE
using (var cn = new SqlServerConnectionFactory(InputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.Execute("UPDATE Orders SET CustomerID = 'VICTE', Freight = 20.11 WHERE OrderId = 10254;"));
}
using (var outer = new ConfigurationContainer(new CSharpModule()).CreateScope(TestFile, logger)) {
var process = outer.Resolve <Process>();
using (var inner = new Container(new CSharpModule(), new SqlServerModule(), new SqliteModule()).CreateScope(process, logger)) {
var controller = inner.Resolve <IProcessController>();
controller.Execute();
}
}
using (var cn = new SqliteConnectionFactory(OutputConnection).GetConnection()) {
cn.Open();
Assert.AreEqual(1, cn.ExecuteScalar <int>("SELECT Updates FROM NorthWindControl WHERE Entity = 'Orders' AND BatchId = 26;"));
Assert.AreEqual("VICTE", cn.ExecuteScalar <string>("SELECT OrdersCustomerId FROM NorthWindStar WHERE OrderDetailsOrderId= 10254;"));
Assert.AreEqual(20.11M, cn.ExecuteScalar <decimal>("SELECT OrdersFreight FROM NorthWindStar WHERE OrderDetailsOrderId= 10254;"));
}
}
