Пример #1
0
        public static string BuildUpsertQuery(string tableName, string[] columns, string values, string[] columnsToUpsert)
        {
            var upsertStatement = new StringBuilder();

            foreach (string propertyName in columnsToUpsert)
            {
                upsertStatement.Append($"{propertyName}=VALUES({propertyName}),");
            }
            MySqlFormatter.RemoveLastCharacter(upsertStatement);
            return(string.Format(upsertQuery, tableName, string.Join(",", columns), values, upsertStatement));
        }
Пример #2
0
        private void BuildValueQuery <T>(T value, string[] dbColumnList, ref StringBuilder queryBuilder)
            where T : IDbTable
        {
            queryBuilder.Append("(");

            foreach (var column in dbColumnList)
            {
                var propertyValue = value.GetType().GetProperty(column)?.GetValue(value, null);
                queryBuilder.Append(MySqlFormatter.ConvertValueToMySqlFormatstring(propertyValue));
                queryBuilder.Append(",");
            }

            MySqlFormatter.RemoveLastCharacter(queryBuilder);
            queryBuilder.Append("),");
        }
Пример #3
0
        public void Delete <T>(IEnumerable <T> items) where T : IDbTable
        {
            var           firstItem = items.FirstOrDefault();
            string        keyColumn = firstItem.GetIdColumn();
            string        tableName = firstItem.GetTableName();
            List <string> values    = new List <string>();
            var           builder   = new StringBuilder();

            foreach (var item in items)
            {
                BuildValueQuery(item, new string[] { keyColumn }, ref builder);
            }
            MySqlFormatter.RemoveLastCharacter(builder);
            var deleteQuery = MySqlQuery.BuildDeleteQuery(tableName, keyColumn, builder.ToString());

            ExecuteNonQuery(deleteQuery);
        }
Пример #4
0
        public void Upsert <T>(IEnumerable <T> items) where T : IDbTable
        {
            var firstItem = items.FirstOrDefault();

            if (firstItem != null)
            {
                var      columnList    = GetOrBuildColumnList(firstItem);
                string[] objColumnList = columnList.Keys.ToArray();
                string[] dbColumnList  = columnList.Values.ToArray();
                string[] ignoreColumns = firstItem.GetIgnoreColumnsOnUpdate().Split(',')?.ToArray();
                string   tableName     = firstItem.GetTableName();
                string[] dbColumnListWithIgnoreColumnList = GetColumnListWithIgnoreProperties(firstItem.GetType(), firstItem, ignoreColumns).ToArray();
                var      builder = new StringBuilder();
                foreach (var item in items)
                {
                    BuildValueQuery(item, objColumnList, ref builder);
                }
                MySqlFormatter.RemoveLastCharacter(builder);
                var upsertQuery = MySqlQuery.BuildUpsertQuery(tableName, dbColumnList, builder.ToString(), dbColumnListWithIgnoreColumnList);
                ExecuteNonQuery(upsertQuery);
            }
        }