static void Main(string[] args)
{
var acc = new GeneralAccount();
acc.Name = "Sobuj";
acc.Email = "*****@*****.**";
acc.GeneralAccountId = 0;
acc.LastPasswordChanged = DateTime.Now;
SqlQueryBuilder sb = new SqlQueryBuilder();
sb.GetUpdateQuery(acc, "Name,Email,LastPasswordChanged", "GeneralAccountId="+ 1);
}
// In Sqlite if table has AutoIncrement then InsertOrUpdate is not supported in one call,
// we can not simultaneously Insert without PK(being 0,0,...) and Update with PK(1,2,...), separate calls Insert, Update are required.
public static string InsertIntoTable(TableInfo tableInfo, OperationType operationType, string tableName = null)
{
tableName ??= tableInfo.InsertToTempTable ? tableInfo.TempTableName : tableInfo.TableName;
var tempDict = tableInfo.PropertyColumnNamesDict;
if (operationType == OperationType.Insert && tableInfo.PropertyColumnNamesDict.Any()) // Only OnInsert omit colums with Default values
{
tableInfo.PropertyColumnNamesDict = tableInfo.PropertyColumnNamesDict.Where(a => !tableInfo.DefaultValueProperties.Contains(a.Key)).ToDictionary(a => a.Key, a => a.Value);
}
List <string> columnsList = tableInfo.PropertyColumnNamesDict.Values.ToList();
List <string> propertiesList = tableInfo.PropertyColumnNamesDict.Keys.ToList();
bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(SqlBulkCopyOptions.KeepIdentity);
if (operationType == OperationType.Insert && !keepIdentity && tableInfo.HasIdentity)
{
var identityPropertyName = tableInfo.PropertyColumnNamesDict.SingleOrDefault(a => a.Value == tableInfo.IdentityColumnName).Key;
columnsList = columnsList.Where(a => a != tableInfo.IdentityColumnName).ToList();
propertiesList = propertiesList.Where(a => a != identityPropertyName).ToList();
}
var commaSeparatedColumns = SqlQueryBuilder.GetCommaSeparatedColumns(columnsList);
var commaSeparatedColumnsParams = SqlQueryBuilder.GetCommaSeparatedColumns(propertiesList, "@").Replace("[", "").Replace("]", "").Replace(".", "_");
var q = $"INSERT INTO [{tableName}] " +
$"({commaSeparatedColumns}) " +
$"VALUES ({commaSeparatedColumnsParams})";
if (operationType == OperationType.InsertOrUpdate)
{
List <string> primaryKeys = tableInfo.PrimaryKeysPropertyColumnNameDict.Select(k => tableInfo.PropertyColumnNamesDict[k.Key]).ToList();
var commaSeparatedPrimaryKeys = SqlQueryBuilder.GetCommaSeparatedColumns(primaryKeys);
var commaSeparatedColumnsEquals = SqlQueryBuilder.GetCommaSeparatedColumns(columnsList, equalsTable: "", propertColumnsNamesDict: tableInfo.PropertyColumnNamesDict).Replace("]", "").Replace(" = .[", "] = @").Replace(".", "_");
var commaANDSeparatedPrimaryKeys = SqlQueryBuilder.GetANDSeparatedColumns(primaryKeys, equalsTable: "@", propertColumnsNamesDict: tableInfo.PropertyColumnNamesDict).Replace("]", "").Replace(" = @[", "] = @").Replace(".", "_");
q += $" ON CONFLICT({commaSeparatedPrimaryKeys}) DO UPDATE" +
$" SET {commaSeparatedColumnsEquals}" +
$" WHERE {commaANDSeparatedPrimaryKeys}";
}
tableInfo.PropertyColumnNamesDict = tempDict;
return(q + ";");
}
public void OracleTestSimpleQuery()
{
long id = -1;
SqlQueryBuilder builder = new SqlQueryBuilder(new OracleDAOHelper("Oracle.ManagedDataAccess.Client"));
builder
.Select(new string[] { "IDINSTANCE" })
.From("BTINSTANCES", "B")
.Where()
.Condition("B", "BARCODE", WhereOperator.EqualTo, "0003517");
string query = builder.ToString();
string oracleConnectionStr = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.42)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=ORCL)));User Id=QUALITYX_INDUSTRIES_DEV;Password=QUALITYX_INDUSTRIES_DEV;enlist=false";
var factory = DbProviderFactories.GetFactory("Oracle.ManagedDataAccess.Client");
using (var conn = factory.CreateConnection())
{
conn.ConnectionString = oracleConnectionStr;
conn.Open();
var command = conn.CreateCommand();
command.CommandText = query;
foreach (var builderParam in builder.Parameters)
{
var param = command.CreateParameter();
param.ParameterName = builderParam.Name;
param.Value = builderParam.Value;
command.Parameters.Add(param);
}
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
id = dr.GetInt64(0);
}
}
conn.Close();
}
Assert.IsTrue(id != -1);
}
public void SqlServerTestSimpleQuery()
{
long id = -1;
SqlQueryBuilder builder = new SqlQueryBuilder(new SqlServerDAOHelper("System.Data.SqlClient"));
builder
.Select(new string[] { "IDINSTANCE" })
.From("BTINSTANCES", "B")
.Where()
.Condition("B", "BARCODE", WhereOperator.EqualTo, "5050925927567");
string query = builder.ToString();
string sqlServerConnectionStr = @"Data Source=192.168.100.42,9433\DEV2016;Initial Catalog=WmX_Kostelia_AH_Test;Persist Security Info=True;User Id=sa;Password=Sql2016$";
var factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
using (var conn = factory.CreateConnection())
{
conn.ConnectionString = sqlServerConnectionStr;
conn.Open();
var command = conn.CreateCommand();
command.CommandText = query;
foreach (var builderParam in builder.Parameters)
{
var param = command.CreateParameter();
param.ParameterName = builderParam.Name;
param.Value = builderParam.Value;
command.Parameters.Add(param);
}
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
id = dr.GetInt64(0);
}
}
conn.Close();
}
Assert.IsTrue(id != -1);
}
protected void BaseSetup()
{
i = 0;
IDbConnectionProvider dbConnectionProvider = new InMemoryDbConnectionProvider();
//dbConnectionProvider.AddConnectionFactory("default", new MsSql2014ConnectionMaker(ConnectionString));
dbConnectionProvider.AddConnectionFactory("default", new PostgreSqlConnectionMaker(ConnectionString));
IDbValueConverter dbValueConverter = new StrategiesDbValueConverter();
IEntityDatabaseMapProvider entityDatabaseMapProvider = new EntityDatabaseMapProvider(new DirectPropertyEntityMapper());
IDataReaderToPoco dataReaderToPoco = new DataReaderToPoco(entityDatabaseMapProvider);
entityDatabaseMapProvider.AddMap <Post>();
ISqlQueryCutter sqlQueryCutter = new SqlQueryCutter();
ISqlQueryBuilder sqlQueryBuilder = new SqlQueryBuilder(entityDatabaseMapProvider, sqlQueryCutter);
_db = new Db(dbConnectionProvider, dbValueConverter, dataReaderToPoco, sqlQueryBuilder);
}
public string GetIdentityClientName(int clientId)
{
if (clientId == 0 | clientId == -1)
{
return("Ошибка заполнения клиента.");
}
var identityClientName = SqlQueryBuilder.SelectIdentityClientName(clientId, DBConnection);
var result = identityClientName.ExecuteScalar();
if (result != null)
{
return(result.ToString());
}
else
{
return(null);
}
}
protected void UpdateSql()
{
try {
SqlQueryBuilder builder = new SqlQueryBuilder(query);
builder.BuildSQL();
SqlTextBox.Text = builder.Result.SQL;
QueryTextFormats formats = new QueryTextFormats();
formats.UseHtml = true;
formats.UseMathSymbolsForOperators = true;
literalQueryText.Text = query.GetConditionsText(formats);
ResultGrid.Visible = false;
}
catch {
SqlTextBox.Text = "";
literalQueryText.Text = "";
}
}
public static void FillSumsOfTextBoxes(this List <CostFillingModel> costModels)
{
using (SqlConnection connect = new SqlConnection(GlobalSettings.ConnectionString))
{
connect.Open();
foreach (var model in costModels)
{
SqlCommand cmd = SqlQueryBuilder.SumByDateQuery(model.SumType, model.From, model.By, connect);
try
{
model.TBox.Text = cmd.ExecuteScalar().ToString();
}
catch
{
model.TBox.Text = "Ошибка вызова функции";
}
}
}
}
public static async Task BatchDeleteAsync <T, TK>(
this DbSet <T> set, List <TK> ids
) where T : class
{
if (ids.Count < 1)
{
return;
}
var idBatches = ids.Batches(BatchSize);
var ctx = set.GetDbContext();
foreach (var idBatch in idBatches)
{
var sql = SqlQueryBuilder.BuildDeleteQuery(
set.GetTableName(), idBatch.ToList()
);
await ctx.Database.ExecuteSqlRawAsync(sql);
}
}
void SetSql()
{
SqlQueryBuilder builder = new SqlQueryBuilder((DbQuery)queryPanel.Query);
builder.Formats.SetDefaultFormats(FormatType.MsSqlServer);
builder.Formats.OrderByStyle = OrderByStyles.Aliases;
builder.Formats.DateFormat = "MM/dd/yyyy";
builder.Formats.DateTimeFormat = "MM/dd/yyyy HH:mm";
if (!builder.CanBuild)
{
return;
}
builder.BuildSQL();
string sql = builder.Result.SQL;
textBoxSql.Text = sql;
buttonExecute.IsEnabled = !string.IsNullOrEmpty(sql);
}
public void SubmitUser()
{
Database db_ = Database.Instance;
try
{
db_.DefaultConnect();
var dicUser = userinfo.user.FieldNotNullAsDictionary();
var dicStaff = userinfo.staff.FieldNotNullAsDictionary();
var dicMotoDriver = userinfo.motoDriver.FieldNotNullAsDictionary();
SqlQueryBuilder Insert = new SqlQueryBuilder(new SqlInsert());
//Using Builder
string Userquery = Insert.Fields(dicUser.Keys.ToArray())
.Table("User")
.GetQueryString();
string Staffquery = Insert.Fields(dicStaff.Keys.ToArray())
.Table("Staff")
.GetQueryString();
string MotoDriverquery = Insert.Fields(dicMotoDriver.Keys.ToArray())
.Table("MotoDriver")
.GetQueryString();
//Using Single Tone
db_.Transaction(Userquery, dicUser);
db_.Transaction(Staffquery, dicStaff);
db_.Transaction(MotoDriverquery, dicMotoDriver);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
db_.Disconnect();
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
try
{
db_.DefaultConnect();
var item = new Product()
{
ID = op.NewID(),
Name = tbProductName.Text,
Description = tbDescribtion.Text,
//ProductType=
SalerID = Program.main.user.ID,
SalerName = Program.main.user.LastName,
CheckInDate = DateTime.Now,
ToLocation = tbToLocation.Text,
CusPhone = tbCusPhone.Text,
CusAddress = tbCusAddress.Text,
//TakeBy
//TakeDate
//CloseDate
Status = "Hold",
Note = ""
}.FieldNotNullAsDictionary();
SqlQueryBuilder Insert = new SqlQueryBuilder(new SqlInsert());
//Using Builder
string query = Insert.Fields(item.Keys.ToArray())
.Table("Product")
.GetQueryString();
//Using Single Tone
db_.Transaction(query, item);
RefreshProduct();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
db_.Disconnect();
}
}
private static void Main(string[] args)
{
using (var queryBuilder = new SqlQueryBuilder())
{
var query = queryBuilder
.Select(
("c", "id").As("CompanyID"),
("c", "CompanyName"),
("u", "Id").As("UserID"))
.From("Companies", "c")
.WithNoLock()
.Join("Users", "u")
.On(("u", "Name").IsEqualTo(("c", "UserName")))
.Where(
("u", "id").IsEqualTo(4)
.And(("c", "CompanyName").IsEqualTo("'Test'")));
Console.WriteLine(query.ToString());
Console.ReadKey();
}
}
public static void Merge <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
tableInfo.InsertToTempTable = true;
tableInfo.CheckHasIdentity(context);
context.Database.CommandTimeout = 960;
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName));
if (tableInfo.BulkConfig.SetOutputIdentity)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName));
}
try
{
Insert(context, entities, tableInfo, progress);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.MergeTable(tableInfo, operationType));
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
if (!tableInfo.BulkConfig.SetOutputIdentity || !tableInfo.HasSinglePrimaryKey)
{
return;
}
try
{
tableInfo.UpdateOutputIdentity(context, entities);
var dp = SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName);
context.Database.ExecuteSqlCommand(dp);
}
catch (Exception)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
throw;
}
}
catch (Exception)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
throw;
}
}
public async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress,
CancellationToken cancellationToken) where T : class
{
Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context);
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);
try
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
tableInfo.PropertyColumnNamesDict = tableInfo.OutputPropertyColumnNamesDict;
var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;
List <T> existingEntities;
if (typeof(T) == type)
{
Expression <Func <DbContext, IQueryable <T> > > expression = tableInfo.GetQueryExpression <T>(sqlQuery, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).ToList();
}
else
{
Expression <Func <DbContext, IEnumerable> > expression = tableInfo.GetQueryExpression(type, sqlQuery, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).Cast <T>().ToList();
}
tableInfo.UpdateReadEntities(type, entities, existingEntities);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
}
}
}
void SampleSelect()
{
try
{
db_.DefaultConnect();
SqlQueryBuilder Select = new SqlQueryBuilder(new SqlSelect());
//Using Builder
string query = Select.Fields("")
.Table("")
.Where("")
.GetQueryString();
//Using Single Tone
db_.Read(query);
}
catch (Exception)
{
}
finally
{
db_.Disconnect();
}
}
public EdmEntityObjectCollection Get(IEdmCollectionType collectionType, ODataQueryOptions queryOptions)
{
var entityType = collectionType.ElementType.Definition as EdmEntityType;
var collection = new EdmEntityObjectCollection(new EdmCollectionTypeReference(collectionType, true));
if (entityType != null)
{
using (var connection = new SqlConnection(_connectionString))
{
var sqlBuilder = new SqlQueryBuilder(queryOptions);
IEnumerable<dynamic> rows = connection.Query<dynamic>(sqlBuilder.ToSql());
foreach (dynamic row in rows)
{
var entity = CreateEdmEntity(entityType, row);
collection.Add(entity);
}
}
}
return collection;
}
public static async Task BatchUpdateAsync <T, TK>(
this DbSet <T> set, List <TK> ids, T obj
) where T : class
{
if (obj == null || ids.Count < 1)
{
return;
}
var ctx = set.GetDbContext();
var entityType = ctx.Model.FindEntityType(typeof(T));
var props = entityType.GetProperties().Select(p => p.PropertyInfo);
var idBatches = ids.Batches(BatchSize);
foreach (var idBatch in idBatches)
{
var sql = SqlQueryBuilder.BuildUpdateQuery(
set.GetTableName(), idBatch.ToList(), props.ToList(), obj
);
await ctx.Database.ExecuteSqlRawAsync(sql);
}
}
public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
tableInfo.InsertToTempTable = true;
await tableInfo.CheckHasIdentityAsync(context).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName)).ConfigureAwait(false);
if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
}
try
{
await InsertAsync(context, entities, tableInfo, progress).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType)).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false);
if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
{
await tableInfo.UpdateOutputIdentityAsync(context, entities).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
}
}
catch (Exception)
{
if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
}
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false);
throw;
}
}
void SampleUpdate()
{
Dictionary <string, dynamic> fields = new Dictionary <string, dynamic>();
try
{
db_.DefaultConnect();
SqlQueryBuilder Update = new SqlQueryBuilder(new SqlUpdate());
//Using Builder
string query = Update.Fields(fields.Keys.ToArray())
.Table("")
.Where("")
.GetQueryString();
//Using Single Tone
db_.Transaction(query, fields);
}
catch (Exception)
{
}
finally
{
db_.Disconnect();
}
}
void LoadUserRole()
{
try
{
db_.DefaultConnect();
SqlQueryBuilder Select = new SqlQueryBuilder(new SqlSelect());
//Using Builder
string query = Select.Fields("*")
.Table("UserRole")
.GetQueryString();
//Using Single Tone
var userRoles_ = db_.Read(query);
if (userRoles_.Rows.Count > 0)
{
foreach (DataRow role in userRoles_.Rows)
{
UserRole userrole = new UserRole()
{
ID = role.Field <string>("ID"),
Role = role.Field <string>("Role"),
Desc = role.Field <string>("Desc")
};
UserRoles.Add(userrole);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
db_.Disconnect();
}
}
void RefreshDatasource()
{
Product product = null;
string condition = "";
if (Program.main.user.RoleID != "423DAC41-7E15-4EAC-BAF1-7B2421BE5CBB") //Check RolID != RoleAdmin
{
condition = "SalerID = '" + Program.main.user.ID + "'";
}
try
{
db_.DefaultConnect();
SqlQueryBuilder Select = new SqlQueryBuilder(new SqlSelect());
//Using Builder
string query = Select.Fields("*")
.Table("Product")
.Where(condition)
.GetQueryString();
//Using Single Tone
var product_ = db_.Read(query);
if (product_.Rows.Count > 0)
{
lstProducts = new List <Product>();
foreach (DataRow item in product_.Rows)
{
product = new Product()
{
ID = item.Field <string>("ID"),
Name = item.Field <string>("Name"),
Description = item.Field <string>("Description"),
ProductType = item.Field <string>("ProductType"),
SalerID = item.Field <string>("SalerID"),
SalerName = item.Field <string>("SalerName"),
CheckInDate = item.Field <DateTime?>("CheckInDate"),
ToLocation = item.Field <string>("ToLocation"),
CusPhone = item.Field <string>("CusPhone"),
CusAddress = item.Field <string>("CusAddress"),
TakerID = item.Field <string>("TakerID"),
TakeBy = item.Field <string>("TakeBy"),
TakeDate = item.Field <DateTime?>("TakeDate"),
CloseDate = item.Field <DateTime?>("CloseDate"),
Status = item.Field <string>("Status"),
Note = item.Field <string>("Note")
};
lstProducts.Add(product);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
db_.Disconnect();
}
}
// Publish Async and NonAsync are merged into single operation flow with protected method using arg: bool isAsync (keeps code DRY)
// https://docs.microsoft.com/en-us/archive/msdn-magazine/2015/july/async-programming-brownfield-async-development#the-flag-argument-hack
protected async Task InsertAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities);
if (isAsync)
{
await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.OpenConnection();
}
var connection = context.GetUnderlyingConnection(tableInfo.BulkConfig);
try
{
var transaction = context.Database.CurrentTransaction;
using var sqlBulkCopy = GetSqlBulkCopy((SqlConnection)connection, transaction, tableInfo.BulkConfig);
bool setColumnMapping = false;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
var dataTable = GetDataTable(context, type, entities, sqlBulkCopy, tableInfo);
if (isAsync)
{
await sqlBulkCopy.WriteToServerAsync(dataTable, cancellationToken).ConfigureAwait(false);
}
else
{
sqlBulkCopy.WriteToServer(dataTable);
}
}
catch (InvalidOperationException ex)
{
if (ex.Message.Contains(BulkExceptionMessage.ColumnMappingNotMatch))
{
bool tableExist = isAsync ? await tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken, isAsync : true).ConfigureAwait(false)
: tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken, isAsync: false).GetAwaiter().GetResult();
if (!tableExist)
{
var sqlCreateTableCopy = SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo);
var sqlDropTable = SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlCreateTableCopy, cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(sqlDropTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlCreateTableCopy);
context.Database.ExecuteSqlRaw(sqlDropTable);
}
}
}
throw;
}
}
finally
{
if (isAsync)
{
await context.Database.CloseConnectionAsync().ConfigureAwait(false);
}
else
{
context.Database.CloseConnection();
}
}
if (!tableInfo.CreatedOutputTable)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities, reset: true);
}
}
public void Truncate(DbContext context, TableInfo tableInfo)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DeleteTable(tableInfo.FullTableName));
}
public async Task TruncateAsync(DbContext context, TableInfo tableInfo)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DeleteTable(tableInfo.FullTableName));
}
public override int Count(Type type, IEnumerable <ICriterion> criteria, params QueryOption[] options)
{
var queryBuilder = new SqlQueryBuilder <Domain.ContactContactType>(criteria, options, PropertyColumnMappings, GetSubQueryMappings);
return((int)ExecuteScalar(queryBuilder.GenerateCountQuery("[ContactContactTypes] AS C"), System.Data.CommandType.Text, GenerateParameters(queryBuilder.Parameters)));
}
public void UpdateTest()
{
var query = new SqlQueryBuilder <UpdateQuery>()
.AddParameter(new TableNameParameter("test"));
}
public async Task TruncateAsync(DbContext context, TableInfo tableInfo, CancellationToken cancellationToken)
{
string sql = SqlQueryBuilder.DeleteTable(tableInfo.FullTableName);
await context.Database.ExecuteSqlRawAsync(sql, cancellationToken).ConfigureAwait(false);
}
protected async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync) where T : class
{
SqliteConnection connection = isAsync ? await OpenAndGetSqliteConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false)
: OpenAndGetSqliteConnection(context, tableInfo.BulkConfig);
bool doExplicitCommit = false;
SqliteTransaction transaction = null;
try
{
if (context.Database.CurrentTransaction == null)
{
//context.Database.UseTransaction(connection.BeginTransaction());
doExplicitCommit = true;
}
transaction = doExplicitCommit ? connection.BeginTransaction()
: (SqliteTransaction)context.Database.CurrentTransaction.GetUnderlyingTransaction(tableInfo.BulkConfig);
SqliteCommand command = connection.CreateCommand();
command.Transaction = transaction;
// CREATE
command.CommandText = SqlQueryBuilderSqlite.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName);
if (isAsync)
{
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
}
else
{
command.ExecuteNonQuery();
}
tableInfo.BulkConfig.OperationType = OperationType.Insert;
tableInfo.InsertToTempTable = true;
tableInfo.SqliteConnection = connection;
tableInfo.SqliteTransaction = transaction;
// INSERT
if (isAsync)
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
}
else
{
InsertAsync(context, type, entities, tableInfo, progress, cancellationToken, isAsync: false).GetAwaiter().GetResult();
}
// JOIN
List <T> existingEntities;
var sqlSelectJoinTable = SqlQueryBuilder.SelectJoinTable(tableInfo);
Expression <Func <DbContext, IQueryable <T> > > expression = tableInfo.GetQueryExpression <T>(sqlSelectJoinTable, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).ToList();
tableInfo.UpdateReadEntities(type, entities, existingEntities);
// DROP
command.CommandText = SqlQueryBuilderSqlite.DropTable(tableInfo.FullTempTableName);
if (isAsync)
{
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
}
else
{
command.ExecuteNonQuery();
}
if (doExplicitCommit)
{
transaction.Commit();
}
}
finally
{
if (doExplicitCommit)
{
if (isAsync)
{
await transaction.DisposeAsync();
await context.Database.CloseConnectionAsync().ConfigureAwait(false);
}
else
{
transaction.Dispose();
context.Database.CloseConnection();
}
}
}
}
// Truncate
public void Truncate(DbContext context, TableInfo tableInfo)
{
var sqlTruncateTable = SqlQueryBuilder.TruncateTable(tableInfo.FullTableName);
context.Database.ExecuteSqlRaw(sqlTruncateTable);
}
protected void UpdateSql()
{
try {
SqlQueryBuilder builder = new SqlQueryBuilder(query);
builder.BuildSQL();
SqlTextBox.Text = builder.Result.SQL;
QueryTextFormats formats = new QueryTextFormats();
formats.UseHtml = true;
formats.UseMathSymbolsForOperators = true;
ResultGrid.Visible = false;
}
catch {
SqlTextBox.Text = "";
}
}
protected async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync) where T : class
{
Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo();
var sqlCreateTableCopy = SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlCreateTableCopy, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlCreateTableCopy);
}
try
{
if (isAsync)
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
}
else
{
InsertAsync(context, type, entities, tableInfo, progress, cancellationToken, isAsync: false).GetAwaiter().GetResult();
}
tableInfo.PropertyColumnNamesDict = tableInfo.OutputPropertyColumnNamesDict;
var sqlSelectJoinTable = SqlQueryBuilder.SelectJoinTable(tableInfo);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict; // TODO Consider refactor and integrate with TimeStampPropertyName, also check for Calculated props.
// Output only PropertisToInclude and for getting Id with SetOutputIdentity
if (tableInfo.TimeStampPropertyName != null && !tableInfo.PropertyColumnNamesDict.ContainsKey(tableInfo.TimeStampPropertyName))
{
tableInfo.PropertyColumnNamesDict.Add(tableInfo.TimeStampPropertyName, tableInfo.TimeStampColumnName);
}
List <T> existingEntities;
if (typeof(T) == type)
{
Expression <Func <DbContext, IQueryable <T> > > expression = tableInfo.GetQueryExpression <T>(sqlSelectJoinTable, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).ToList();
}
else // TODO: Consider removing
{
Expression <Func <DbContext, IEnumerable> > expression = tableInfo.GetQueryExpression(type, sqlSelectJoinTable, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).Cast <T>().ToList();
}
tableInfo.UpdateReadEntities(type, entities, existingEntities);
if (tableInfo.TimeStampPropertyName != null && !tableInfo.PropertyColumnNamesDict.ContainsKey(tableInfo.TimeStampPropertyName))
{
tableInfo.PropertyColumnNamesDict.Remove(tableInfo.TimeStampPropertyName);
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
var sqlDropTable = SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlDropTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlDropTable);
}
}
}
}