private static Compiler GetCompiler <T>()
where T : IDbConnection, new()
{
Compiler result;
var name = typeof(T).Name;
switch (name)
{
case "SqliteConnection ":
result = new SqliteCompiler();
break;
case "FbConnection":
result = new FirebirdCompiler();
break;
case "MySqlConnection":
result = new MySqlCompiler();
break;
case "OracleConnection":
result = new OracleCompiler();
break;
case "NpgsqlConnection":
result = new PostgresCompiler();
break;
default:
result = new SqlServerCompiler();
break;
}
return(result);
}
public QueryBuilderTest()
{
_sqlsrv = new SqlServerCompiler();
_mysql = new MySqlCompiler();
_pg = new PostgresCompiler();
_sqlite = new SqliteCompiler();
}
private static QueryFactory SqlLiteQueryFactory()
{
var compiler = new SqliteCompiler();
var connection = new SQLiteConnection("Data Source=Demo.db");
var db = new QueryFactory(connection, compiler);
db.Logger = result =>
{
Console.WriteLine(result.ToString());
};
if (!File.Exists("Demo.db"))
{
Console.WriteLine("db not exists creating db");
SQLiteConnection.CreateFile("Demo.db");
db.Statement("create table accounts(id integer primary key autoincrement, name varchar, currency_id varchar, balance decimal, created_at datetime);");
for (var i = 0; i < 10; i++)
{
db.Statement("insert into accounts(name, currency_id, balance, created_at) values(@name, @currency, @balance, @date)", new
{
name = $"Account {i}",
currency = "USD",
balance = 100 * i * 1.1,
date = DateTime.UtcNow,
});
}
}
return(db);
}
public static SqlResult Compile(this Query query, string databaseType)
{
Compiler compiler = null;
if (DataBaseType == ConnectionType.MYSQL)
{
compiler = new MySqlCompiler();
}
else if (DataBaseType == ConnectionType.SQL)
{
compiler = new MySqlCompiler();
}
else if (DataBaseType == ConnectionType.ORACLE)
{
compiler = new OracleCompiler();
}
else if (DataBaseType == ConnectionType.SQLITE)
{
compiler = new SqliteCompiler();
}
else if (DataBaseType == ConnectionType.POSTRGRESQL)
{
compiler = new PostgresCompiler();
}
return(compiler.Compile(query));
}
public static QueryFactory Build(DatabaseProvider provider = DatabaseProvider.SQLite, string connectionString = null)
{
System.Data.IDbConnection connection = null;
Compiler compiler = null;
switch (provider)
{
case DatabaseProvider.SQLite:
compiler = new SqliteCompiler();
connection = new SqliteConnection(connectionString);
break;
case DatabaseProvider.SQLServer:
compiler = new SqlServerCompiler();
connection = new SqlConnection(connectionString);
break;
default:
throw new NotImplementedException();
}
var db = new QueryFactory(connection, compiler);
db.Logger = compiled =>
{
Console.WriteLine(compiled.RawSql);
System.Diagnostics.Debug.WriteLine(compiled.ToString());
};
return(db);
}
public static Query OpenSqlite()
{
var appDirectory = Settings.AppDirectory;
DBFile = Path.Combine(appDirectory, "Storage/Common/LocalStorage.db");
if (!File.Exists(DBFile))
{
SQLiteConnection.CreateFile(DBFile);
// throw new Exception($"DB file is not exist. File {dbFile}.");
}
var compiler = new SqliteCompiler();
var connBuilder = new SQLiteConnectionStringBuilder()
{
DataSource = DBFile,
CacheSize = 1024,
SyncMode = SynchronizationModes.Full
};
var connection = new SQLiteConnection(connBuilder.ConnectionString);
var query = new Query();
var factory = new QueryFactory(connection, compiler);
factory.Logger = result => Log.Debug("SQLiteExec: {0}", result);
return(factory.FromQuery(query));
}
public static string GetCurrentDatabaseName(SqlDbConnectionType strType)
{
SqlResult result = new SqliteCompiler().Compile(GetQueryConfig(strType).Select("query_string").Where("name", "current_db"));
//return RunQueryTable("SELECT FROM query_configs WHERE name = '' AND ");
return "";
}
//public static QueryFactory BuildQueryFactory(CustomQueryConfig queryConfig)
//{
// return BuildQueryFactory(queryConfig.Provider, queryConfig.ConnectionString);
//}
public static QueryFactory BuildQueryFactory(DatabaseProvider provider = DatabaseProvider.SQLite, string connectionString = null)
{
System.Data.IDbConnection connection = null;
Compiler compiler = null;
switch (provider)
{
case DatabaseProvider.SQLite:
compiler = new SqliteCompiler();
connection = new SqliteConnection(connectionString ?? GlobalVariables.Connection);
break;
case DatabaseProvider.SQLServer:
compiler = new SqlServerCompiler();
connection = new SqlConnection(connectionString ?? GlobalVariables.Connection);
break;
}
var db = new QueryFactory(connection, compiler);
db.Logger = compiled => {
Console.WriteLine(compiled.RawSql);
System.Diagnostics.Debug.WriteLine(compiled.ToString());
};
return(db);
}
private List <T> Query <T>(Query query, string databasePath = null) where T : new()
{
using (SQLiteConnection connection = CreateConnection(databasePath))
{
SqlResult result = new SqliteCompiler().Compile(query);
return(connection.Query <T>(result.Sql, result.Bindings.ToArray()));
}
}
public static QueryFactory BuildForSqlite(string fileName)
{
string connStr = $"Data Source={fileName};";
var connection = new SQLiteConnection(connStr);
var compiler = new SqliteCompiler();
var DB = new QueryFactory(connection, compiler);
return(DB);
}
static void Main(string[] args)
{
var connection = new SQLiteConnection("Data Source=MyDb.db");
var compiler = new SqliteCompiler();
var db = new QueryFactory(connection, compiler);
var sqlResult = db.Query("Users").Where("Id", 1).Where("Status", "Active").First();
var user = db.Query("Users").Select("Id", "Status").Where("Id", 1).Where("Status", "Active").Get <Entity.User>();
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers();
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo {
Title = "BaseCamp_Web_API", Version = "v1"
});
});
services.AddScoped(services =>
{
var connection = new SQLiteConnection("DataSource=..\\sneakers.db;Mode=ReadWrite;Foreign Keys=True");
var compiler = new SqliteCompiler();
return(new QueryFactory(connection, compiler));
});
}
public SQLiteDatabase(string path)
{
if (!File.Exists(path))
{
throw new FileNotFoundException(path);
}
var connectionstring = new SQLiteConnectionStringBuilder
{
DataSource = path
};
SQLiteConnection connection = new SQLiteConnection(connectionstring.ToString());
SqliteCompiler compiler = new SqliteCompiler();
connection.Open();
this.db = new QueryFactory(connection, compiler);
}
private static QueryFactory SqlLiteQueryFactory()
{
var compiler = new SqliteCompiler();
var connection = new SQLiteConnection("Data Source=Demo.db");
var db = new QueryFactory(connection, compiler);
//db.Logger = result => { Console.WriteLine(result.ToString()); };
if (!File.Exists("Demo.db"))
{
Console.WriteLine("db not exists creating db");
SQLiteConnection.CreateFile("Demo.db");
db.Statement(
"create table accounts(id integer primary key autoincrement, name varchar, currency_id varchar, created_at datetime);");
}
return(db);
}
protected BaseRepository(ConnectionStringSettings connectionString)
{
Connection = new SQLiteConnection(connectionString.ConnectionString);
Compiler = new SqliteCompiler();
}
public SqliteLimitTests()
{
compiler = Compilers.Get <SqliteCompiler>(EngineCodes.Sqlite);
}
public async Task <bool> ExportToSQLite3(string path)
{
List <Brand> brands = await this.GetAllBrands();
List <NovelGame> games = await this.GetAllGames();
var connectionstring = new SQLiteConnectionStringBuilder
{
DataSource = path
};
SQLiteConnection connection = new SQLiteConnection(connectionstring.ToString());
SqliteCompiler compiler = new SqliteCompiler();
connection.Open();
QueryFactory db = new QueryFactory(connection, compiler);
SQLiteCommand command = new SQLiteCommand(connection);
command.CommandText = "CREATE TABLE IF NOT EXISTS games(" +
"Id INTEGER, " +
"Title TEXT, " +
"Kana TEXT, " +
"SellDay TIMESTAMP, " +
"BrandId INTEGER, " +
"Median INTEGER, " +
"Stdev INTEGER, " +
"Getchu INTEGER, " +
"OHP TEXT, " +
"Model TEXT, " +
"Rating INTEGER, " +
"Gyutto INTEGER, " +
"Fanza TEXT)";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS brands(" +
"Id INTEGER, " +
"Name TEXT, " +
"Kana TEXT, " +
"Maker TEXT, " +
"MakerKana TEXT, " +
"Url TEXT, " +
"Kind INTEGER, " +
"Lost INTEGER, " +
"DirectLink INTEGER, " +
"Median INTEGER, " +
"Twitter TEXT)";
command.ExecuteNonQuery();
foreach (NovelGame game in games)
{
int affected = db.Query("games").Insert(game);
if (!Convert.ToBoolean(affected))
{
return(false);
}
}
foreach (Brand brand in brands)
{
int affected = db.Query("brands").Insert(brand);
if (!Convert.ToBoolean(affected))
{
return(false);
}
}
return(true);
}
