static string GetUpdateSql(AnyDbConnection cnn, Update query)
{
ISqlOmRenderer renderer = Qb.CreateRenderer(cnn.DatabaseProvider);
string sql = renderer.RenderUpdate(query);
return(sql);
}
public static void Initialise()
{
if (!_initialise)
{
Dapper.Contrib.Extensions.SqlMapperExtensions.GetDatabaseType = (con) =>
{
AnyDbConnection any = con as AnyDbConnection;
if (any != null)
{
switch (any.DatabaseProvider)
{
case DatabaseProvider.MySql:
return("mysqlconnection");
case DatabaseProvider.PostgreSql:
return("npgsqlconnection");
case DatabaseProvider.SqLite:
return("sqliteconnection");
case DatabaseProvider.SqlServer:
return("sqlconnection");
//case DatabaseProvider.SqlServerCe:
// return "sqlceconnection";
}
}
return(con.GetType().Name.ToLower());
};
_initialise = true;
}
}
static string GetInsertSelectSql(AnyDbConnection cnn, InsertSelect query)
{
ISqlOmRenderer renderer = Qb.CreateRenderer(cnn.DatabaseProvider);
string sql = renderer.RenderInsertSelect(query);
return(sql);
}
public IEnumerable <DocumentViewModel> FilterFilesByGuid(string currentSid)
{
_logger.LogDebug($"Return files from database by current sid");
Select select = Qb.Select(nameof(Document.path), nameof(Document.upload_time_stamp), nameof(Document.short_name), nameof(Document.picture), nameof(Document.doc_id))
.From(Tables.Document)
.Where(
Cond.Equal(nameof(Document.user_sid), currentSid),
Cond.Equal(nameof(Document.type), Convert.ToByte(IO.FileExtension.Extensions.Pdf))
);
IEnumerable <DocumentViewModel> documents;
try
{
using (AnyDbConnection connection = _factory.OpenConnection())
{
return(documents = connection.Query <DocumentViewModel>(select));
}
}
catch (Exception ex)
{
_logger.LogError(ex.Message);
}
_logger.LogDebug($"FileDbService.FilterFilesByGuid....OK");
return(null);
}
public void SelectTest()
{
Select sel = Qb.Select("*")
.From("customer").OrderBy("id");
using (AnyDbConnection con = _factory.OpenConnection())
{
var res = con.Query(sel);
Assert.Equal(100, res.Count());
}
}
public static async Task <int> ExecuteAsync(this AnyDbConnection cnn, Update query, IDbTransaction transaction = null, int?commandTimeout = default(int?))
{
string updateSql = GetUpdateSql(cnn, query);
if (!commandTimeout.HasValue)
{
commandTimeout = cnn.DefaultCommandTimeout;
}
DynamicParameters parameters = GetParameters(query.Query.CommandParams);
return(await SqlMapper.ExecuteAsync((IDbConnection)cnn, updateSql, (object)parameters, transaction, commandTimeout, (CommandType?)CommandType.Text));
}
public static async Task <object> QueryFirstOrDefaultAsync(this AnyDbConnection cnn, Type type, Select query, IDbTransaction transaction = null, int?commandTimeout = default(int?))
{
string selectSql = GetSelectSql(cnn, query);
if (!commandTimeout.HasValue)
{
commandTimeout = cnn.DefaultCommandTimeout;
}
DynamicParameters parameters = GetParameters(query.Query.CommandParams);
return(await SqlMapper.QueryFirstOrDefaultAsync((IDbConnection)cnn, type, selectSql, (object)parameters, transaction, commandTimeout, (CommandType?)CommandType.Text));
}
public void GetPageSqlTest()
{
Select sel = Qb.Select("*")
.From("customer").OrderBy("id").Page(1, 10);
string sql = _factory.GetSql(sel);
using (AnyDbConnection con = _factory.OpenConnection())
{
var res = con.Query(sel);
Assert.Equal(10, res.Count());
}
}
public static T QueryFirst <T>(this AnyDbConnection cnn, Select query, IDbTransaction transaction = null, int?commandTimeout = null)
{
string sql = GetSelectSql(cnn, query);
if (!commandTimeout.HasValue)
{
commandTimeout = cnn.DefaultCommandTimeout;
}
DynamicParameters parameters = GetParameters(query.Query.CommandParams);
return(cnn.QueryFirst <T>(sql, parameters, transaction, commandTimeout, CommandType.Text));
}
public static IEnumerable <dynamic> Query(this AnyDbConnection cnn, Select query, object param = null,
IDbTransaction transaction = null, bool buffered = true, int?commandTimeout = null)
{
string sql = GetSelectSql(cnn, query);
if (!commandTimeout.HasValue)
{
commandTimeout = cnn.DefaultCommandTimeout;
}
DynamicParameters parameters = GetParameters(query.Query.CommandParams);
return(cnn.Query(sql, parameters, transaction, buffered, commandTimeout, CommandType.Text));
}
public static IEnumerable <TReturn> Query <TFirst, TSecond, TThird, TReturn>(this AnyDbConnection cnn, Select query, Func <TFirst, TSecond, TThird, TReturn> func, IDbTransaction transaction = null,
bool buffered = true, int?commandTimeout = null, string splitOn = "Id")
{
string sql = GetSelectSql(cnn, query);
if (!commandTimeout.HasValue)
{
commandTimeout = cnn.DefaultCommandTimeout;
}
DynamicParameters parameters = GetParameters(query.Query.CommandParams);
return(cnn.Query <TFirst, TSecond, TThird, TReturn>(sql, func, parameters, transaction, buffered, commandTimeout: commandTimeout, splitOn: splitOn));
}
public static int Execute(this AnyDbConnection cnn, Update query, IDbTransaction transaction = null,
int?commandTimeout = null)
{
string sql = GetUpdateSql(cnn, query);
if (!commandTimeout.HasValue)
{
commandTimeout = cnn.DefaultCommandTimeout;
}
DynamicParameters parameters = GetParameters(query.Query.CommandParams);
return(cnn.Execute(sql, parameters, transaction, commandTimeout, CommandType.Text));
}
public static async Task <long> ExecuteAsync(this AnyDbConnection cnn, Insert query, IDbTransaction transaction = null, int?commandTimeout = default(int?))
{
string sql = GetInsertSql(cnn, query);
if (!commandTimeout.HasValue)
{
commandTimeout = cnn.DefaultCommandTimeout;
}
DynamicParameters parameters = GetParameters(query.Query.CommandParams);
if (string.IsNullOrEmpty(query.Query.IdentityField))
{
return(Convert.ToInt64(await SqlMapper.ExecuteAsync((IDbConnection)cnn, sql, (object)parameters, transaction, commandTimeout, (CommandType?)CommandType.Text)));
}
return(Convert.ToInt64(await SqlMapper.ExecuteScalarAsync((IDbConnection)cnn, sql, (object)parameters, transaction, commandTimeout, (CommandType?)CommandType.Text)));
}
public static long Execute(this AnyDbConnection cnn, Insert query, IDbTransaction transaction = null,
int?commandTimeout = null)
{
string sql = GetInsertSql(cnn, query);
if (!commandTimeout.HasValue)
{
commandTimeout = cnn.DefaultCommandTimeout;
}
DynamicParameters parameters = GetParameters(query.Query.CommandParams);
bool returnIdentity = !string.IsNullOrEmpty(query.Query.IdentityField);
if (!returnIdentity)
{
return(Convert.ToInt64(cnn.Execute(sql, parameters, transaction, commandTimeout, CommandType.Text)));
}
return(Convert.ToInt64(cnn.ExecuteScalar(sql, parameters, transaction, commandTimeout, CommandType.Text)));
}
public void ExecuteAsyncTest()
{
using (AnyDbConnection con = _factory.OpenConnection())
{
Insert ins = Qb.Insert("customer")
.Values(
Value.New("first_name", "123"),
Value.New("last_name", "456")
);
con.ExecuteAsync(ins).Wait();
Assert.Equal(101, GetAll(con).Count());
ins = Qb.Insert("customer")
.Values(
Value.New("first_name", "321"),
Value.New("last_name", "654")
);
con.ExecuteAsync(ins).Wait();
Assert.Equal(102, GetAll(con).Count());
Select sel_0 = Qb.Select("*").From("customer").Page(0, 200);
sel_0 = sel_0.OrderBy("first_name");
var page = con.QueryAsync(sel_0).Result;
Assert.Equal(102, page.Count());
Update upd = Qb.Update("customer")
.Values(
Value.New("first_name", "XXX")
);
int res = con.ExecuteAsync(upd).Result;
Assert.Equal(102, res);
Select sel = Qb.Select("*")
.From("customer");
IEnumerable <Customer> en = con.QueryAsync <Customer>(sel).Result;
Assert.Equal(102, en.Count());
Delete del = Qb.Delete("customer");
res = con.ExecuteAsync(del).Result;
Assert.Equal(102, res);
}
}
public void Add(Document document, IFormFile file, string path, Action <IFormFile, string> copyToDisc)
{
_logger.LogDebug($"Add to database file={file}");
using (AnyDbConnection connection = _factory.OpenConnection())
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
connection.Insert(document, transaction: transaction);
copyToDisc(file, path);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
_logger.LogDebug($"FileDbService.Add .... OK");
}
void InitDb()
{
if (_factory.Provider == DatabaseProvider.PostgreSql)
{
PgDbSetting ps = new PgDbSetting();
Npgsql.NpgsqlConnectionStringBuilder sb = new Npgsql.NpgsqlConnectionStringBuilder(ps.ConnectionString);
sb.Database = "postgres";
ps.ConnectionString = sb.ToString();
AnyDbFactory f = new AnyDbFactory(ps);
using (AnyDbConnection con = f.OpenConnection())
{
con.Execute("drop database if exists qb_test");
con.Execute("create database qb_test");
}
}
if (_factory.Provider == DatabaseProvider.SqLite)
{
Microsoft.Data.Sqlite.SqliteConnectionStringBuilder sb = new Microsoft.Data.Sqlite.SqliteConnectionStringBuilder(_factory.ConnectionString);
if (File.Exists(sb.DataSource))
{
File.Delete(sb.DataSource);
}
}
using (AnyDbConnection con = _factory.OpenConnection())
{
con.Execute(@"
CREATE TABLE customer (
id serial NOT NULL,
first_name varchar(50) NULL,
last_name varchar(50) NULL,
CONSTRAINT customer_pk PRIMARY KEY (id)
);");
for (int i = 0; i < 100; i++)
{
con.Execute($"insert into customer (first_name, last_name) values ('F_{i}', 'L_{i}')");
}
}
}
IEnumerable <Customer> GetAll(AnyDbConnection con)
{
Select sel = Qb.Select("*").From("customer");
return(con.Query <Customer>(sel));
}
public static async Task <IEnumerable <TReturn> > QueryAsync <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>(this AnyDbConnection cnn, Select query, Func <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn> func, IDbTransaction transaction = null, bool buffered = true, int?commandTimeout = default(int?), string splitOn = "Id")
{
string selectSql = GetSelectSql(cnn, query);
if (!commandTimeout.HasValue)
{
commandTimeout = cnn.DefaultCommandTimeout;
}
DynamicParameters parameters = GetParameters(query.Query.CommandParams);
return(await SqlMapper.QueryAsync <TFirst, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, TReturn>((IDbConnection)cnn, selectSql, func, (object)parameters, transaction, buffered, splitOn, commandTimeout, (CommandType?)null));
}
