Example #1
0
 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);
        }
Example #5
0
        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);
        }
Example #6
0
        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 = "";
        }
    }
Example #8
0
 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);
            }
        }
Example #10
0
        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();
            }
        }
Example #12
0
        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();
            }
        }
Example #13
0
 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);
                }
            }
        }
Example #16
0
 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;
        }
Example #18
0
        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;
            }
        }
Example #20
0
        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();
            }
        }
Example #21
0
        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();
            }
        }
Example #22
0
        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);
            }
        }
Example #24
0
 public void Truncate(DbContext context, TableInfo tableInfo)
 {
     context.Database.ExecuteSqlRaw(SqlQueryBuilder.DeleteTable(tableInfo.FullTableName));
 }
Example #25
0
 public async Task TruncateAsync(DbContext context, TableInfo tableInfo)
 {
     await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DeleteTable(tableInfo.FullTableName));
 }
Example #26
0
        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)));
        }
Example #27
0
 public void UpdateTest()
 {
     var query = new SqlQueryBuilder <UpdateQuery>()
                 .AddParameter(new TableNameParameter("test"));
 }
Example #28
0
 public async Task TruncateAsync(DbContext context, TableInfo tableInfo, CancellationToken cancellationToken)
 {
     string sql = SqlQueryBuilder.DeleteTable(tableInfo.FullTableName);
     await context.Database.ExecuteSqlRawAsync(sql, cancellationToken).ConfigureAwait(false);
 }
Example #29
0
        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);
        }
Example #31
0
    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);
                    }
                }
            }
        }