Пример #1
0
        protected virtual string ToSingleSqlString(List <IGrouping <int, DbColumnInfo> > groupList)
        {
            string columnsString = string.Join(",", groupList.First().Where(it => it.IsPrimarykey == false && (it.IsIdentity == false || (IsOffIdentity && it.IsIdentity))).Select(it =>
            {
                if (SetValues.IsValuable())
                {
                    var setValue = SetValues.Where(sv => it.IsPrimarykey == false && (it.IsIdentity == false || (IsOffIdentity && it.IsIdentity))).Where(sv => sv.Key == Builder.GetTranslationColumnName(it.DbColumnName) || sv.Key == Builder.GetTranslationColumnName(it.PropertyName));
                    if (setValue != null && setValue.Any())
                    {
                        return(setValue.First().Value);
                    }
                }
                var result = Builder.GetTranslationColumnName(it.DbColumnName) + "=" + this.Context.Ado.SqlParameterKeyWord + it.DbColumnName;
                return(result);
            }));
            string whereString = null;

            if (this.WhereValues.HasValue())
            {
                foreach (var item in WhereValues)
                {
                    var isFirst = whereString == null;
                    whereString += (isFirst ? " WHERE " : " AND ");
                    whereString += item;
                }
            }
            else if (PrimaryKeys.HasValue())
            {
                if (IsWhereColumns == false)
                {
                    foreach (var item in PrimaryKeys)
                    {
                        var isFirst = whereString == null;
                        whereString += (isFirst ? " WHERE " : " AND ");
                        whereString += Builder.GetTranslationColumnName(item) + "=" + this.Context.Ado.SqlParameterKeyWord + item;
                    }
                }
            }
            if (PrimaryKeys.HasValue() && IsWhereColumns)
            {
                foreach (var item in PrimaryKeys)
                {
                    var isFirst = whereString == null;
                    whereString += (isFirst ? " WHERE " : " AND ");
                    whereString += Builder.GetTranslationColumnName(item) + "=" + this.Context.Ado.SqlParameterKeyWord + item;
                }
            }
            return(string.Format(SqlTemplate, GetTableNameString, columnsString, whereString));
        }
Пример #2
0
        protected virtual string TomultipleSqlString(List <IGrouping <int, DbColumnInfo> > groupList)
        {
            Check.Exception(PrimaryKeys == null || PrimaryKeys.Count == 0, " Update List<T> need Primary key");
            int           pageSize       = 200;
            int           pageIndex      = 1;
            int           totalRecord    = groupList.Count;
            int           pageCount      = (totalRecord + pageSize - 1) / pageSize;
            StringBuilder batchUpdateSql = new StringBuilder();

            while (pageCount >= pageIndex)
            {
                StringBuilder updateTable = new StringBuilder();
                string        setValues   = string.Join(",", groupList.First().Where(it => it.IsPrimarykey == false && (it.IsIdentity == false || (IsOffIdentity && it.IsIdentity))).Select(it =>
                {
                    if (SetValues.IsValuable())
                    {
                        var setValue = SetValues.Where(sv => sv.Key == Builder.GetTranslationColumnName(it.DbColumnName));
                        if (setValue != null && setValue.Any())
                        {
                            return(setValue.First().Value);
                        }
                    }
                    var result = string.Format("S.{0}=T.{0}", Builder.GetTranslationColumnName(it.DbColumnName));
                    return(result);
                }));
                batchUpdateSql.AppendFormat(SqlTemplateBatch.ToString(), setValues, GetTableNameStringNoWith, TableWithString);
                int i = 0;
                foreach (var columns in groupList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList())
                {
                    var isFirst = i == 0;
                    if (!isFirst)
                    {
                        updateTable.Append(SqlTemplateBatchUnion);
                    }
                    updateTable.Append("\r\n SELECT " + string.Join(",", columns.Select(it => string.Format(SqlTemplateBatchSelect, FormatValue(it.Value), Builder.GetTranslationColumnName(it.DbColumnName)))));
                    ++i;
                }
                pageIndex++;
                updateTable.Append("\r\n");
                string whereString = null;
                if (this.WhereValues.HasValue())
                {
                    foreach (var item in WhereValues)
                    {
                        var isFirst = whereString == null;
                        whereString += (isFirst ? null : " AND ");
                        whereString += Regex.Replace(item, "\\" + this.Builder.SqlTranslationLeft, "S." + this.Builder.SqlTranslationLeft);
                    }
                }
                if (PrimaryKeys.HasValue())
                {
                    foreach (var item in PrimaryKeys)
                    {
                        var isFirst = whereString == null;
                        whereString += (isFirst ? null : " AND ");
                        whereString += string.Format("S.{0}=T.{0}", Builder.GetTranslationColumnName(item));
                    }
                }
                batchUpdateSql.AppendFormat(SqlTemplateJoin, updateTable, whereString);
            }
            return(batchUpdateSql.ToString());
        }
Пример #3
0
        protected override string TomultipleSqlString(List <IGrouping <int, DbColumnInfo> > groupList)
        {
            Check.Exception(PrimaryKeys == null || PrimaryKeys.Count == 0, " Update List<T> need Primary key");
            int           pageSize       = 200;
            int           pageIndex      = 1;
            int           totalRecord    = groupList.Count;
            int           pageCount      = (totalRecord + pageSize - 1) / pageSize;
            StringBuilder batchUpdateSql = new StringBuilder();

            while (pageCount >= pageIndex)
            {
                StringBuilder updateTable = new StringBuilder();
                string        setValues   = string.Join(",", groupList.First().Where(it => it.IsPrimarykey == false && (it.IsIdentity == false || (IsOffIdentity && it.IsIdentity))).Select(it =>
                {
                    if (SetValues.IsValuable())
                    {
                        var setValue = SetValues.Where(sv => sv.Key == Builder.GetTranslationColumnName(it.DbColumnName));
                        if (setValue != null && setValue.Any())
                        {
                            return(setValue.First().Value);
                        }
                    }
                    var result = string.Format("{0}=T.{0}", Builder.GetTranslationColumnName(it.DbColumnName));
                    return(result);
                }));
                string tempColumnValue = string.Join(",", groupList.First().Select(it =>
                {
                    if (SetValues.IsValuable())
                    {
                        var setValue = SetValues.Where(sv => sv.Key == Builder.GetTranslationColumnName(it.DbColumnName));
                        if (setValue != null && setValue.Any())
                        {
                            return(setValue.First().Value);
                        }
                    }
                    var result = Builder.GetTranslationColumnName(it.DbColumnName);
                    return(result);
                }));
                batchUpdateSql.AppendFormat(SqlTemplateBatch.ToString(), setValues, GetTableNameStringNoWith, TableWithString);
                int i = 0;
                var tableColumnList = this.Context.DbMaintenance.GetColumnInfosByTableName(GetTableNameStringNoWith);

                foreach (var columns in groupList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList())
                {
                    var isFirst = i == 0;
                    if (!isFirst)
                    {
                        updateTable.Append(SqlTemplateBatchUnion);
                    }
                    updateTable.Append("\r\n (" + string.Join(",", columns.Select(it =>
                    {
                        var columnInfo = tableColumnList.FirstOrDefault(x => x.DbColumnName.Equals(it.DbColumnName, StringComparison.OrdinalIgnoreCase));
                        var dbType     = columnInfo?.DataType;
                        if (dbType == null)
                        {
                            var typeName = it.PropertyType.Name.ToLower();
                            if (typeName == "int32")
                            {
                                typeName = "int";
                            }
                            if (typeName == "int64")
                            {
                                typeName = "long";
                            }
                            if (typeName == "int16")
                            {
                                typeName = "short";
                            }

                            var isAnyType = PostgreSQLDbBind.MappingTypesConst.Where(x => x.Value.ToString().ToLower() == typeName).Any();
                            if (isAnyType)
                            {
                                dbType = PostgreSQLDbBind.MappingTypesConst.Where(x => x.Value.ToString().ToLower() == typeName).FirstOrDefault().Key;
                            }
                            else
                            {
                                dbType = "varchar";
                            }
                        }
                        return(string.Format("CAST({0} AS {1})", FormatValue(it.Value), dbType));
                    })) + ")");
                    ++i;
                }
                pageIndex++;
                updateTable.Append("\r\n");
                string whereString = null;
                if (this.WhereValues.HasValue())
                {
                    foreach (var item in WhereValues)
                    {
                        var isFirst = whereString == null;
                        whereString += (isFirst ? null : " AND ");
                        whereString += item;
                    }
                }
                else if (PrimaryKeys.HasValue())
                {
                    foreach (var item in PrimaryKeys)
                    {
                        var isFirst = whereString == null;
                        whereString += (isFirst ? null : " AND ");
                        whereString += string.Format("{0}.{1}=T.{1}", GetTableNameStringNoWith, Builder.GetTranslationColumnName(item));
                    }
                }
                var format = string.Format(SqlTemplateJoin, updateTable, whereString, tempColumnValue);
                batchUpdateSql.Replace("${0}", format);
                batchUpdateSql.Append(";");
            }
            return(batchUpdateSql.ToString());
        }