示例#1
0
        public override string ToSqlString()
        {
            if (IsNoInsertNull)
            {
                DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
            }
            var    groupList     = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
            var    isSingle      = groupList.Count() == 1;
            string columnsString = string.Join(",", groupList.First().Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));

            if (isSingle)
            {
                string columnParametersString = string.Join(",", this.DbColumnInfoList.Select(it => Builder.SqlParameterKeyWord + it.DbColumnName));
                return(string.Format(SqlTemplate, GetTableNameString, columnsString, columnParametersString));
            }
            else
            {
                StringBuilder batchInsetrSql = new StringBuilder();
                int           pageSize       = 200;
                int           pageIndex      = 1;
                int           totalRecord    = groupList.Count;
                int           pageCount      = (totalRecord + pageSize - 1) / pageSize;
                while (pageCount >= pageIndex)
                {
                    batchInsetrSql.AppendFormat(SqlTemplateBatch, GetTableNameString, columnsString);
                    int i = 0;
                    foreach (var columns in groupList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList())
                    {
                        var isFirst = i == 0;
                        if (isFirst)
                        {
                            batchInsetrSql.Append(SqlTemplateBatchUnion);
                        }
                        batchInsetrSql.Append("\r\n ( " + string.Join(",", columns.Select(it =>
                        {
                            object value = null;
                            if (it.Value is DateTime)
                            {
                                value = ((DateTime)it.Value).ToString("O");
                            }
                            else
                            {
                                value = it.Value;
                            }
                            if (value == null || value == DBNull.Value)
                            {
                                return(string.Format(SqlTemplateBatchSelect, "NULL"));
                            }
                            return(string.Format(SqlTemplateBatchSelect, "'" + value.ObjToString().ToSqlFilter() + "'"));
                        })) + "),");
                        ++i;
                    }
                    pageIndex++;
                    batchInsetrSql.Remove(batchInsetrSql.Length - 1, 1).Append("\r\n;\r\n");
                }
                return(batchInsetrSql.ToString());
            }
        }
示例#2
0
        public virtual string ToSqlString()
        {
            if (IsNoInsertNull)
            {
                DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
            }
            var    groupList     = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
            var    isSingle      = groupList.Count() == 1;
            string columnsString = string.Join(",", groupList.First().Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));

            if (isSingle)
            {
                string columnParametersString = string.Join(",", this.DbColumnInfoList.Select(it => Builder.SqlParameterKeyWord + it.DbColumnName));
                return(string.Format(SqlTemplate, GetTableNameString, columnsString, columnParametersString));
            }
            else
            {
                StringBuilder batchInsetrSql = new StringBuilder();
                int           pageSize       = 200;
                if (this.EntityInfo.Columns.Count > 30)
                {
                    pageSize = 50;
                }
                else if (this.EntityInfo.Columns.Count > 20)
                {
                    pageSize = 100;
                }
                int pageIndex   = 1;
                int totalRecord = groupList.Count;
                int pageCount   = (totalRecord + pageSize - 1) / pageSize;
                while (pageCount >= pageIndex)
                {
                    batchInsetrSql.AppendFormat(SqlTemplateBatch, GetTableNameString, columnsString);
                    int i = 0;
                    foreach (var columns in groupList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList())
                    {
                        var isFirst = i == 0;
                        if (!isFirst)
                        {
                            batchInsetrSql.Append(SqlTemplateBatchUnion);
                        }
                        batchInsetrSql.Append("\r\n SELECT " + string.Join(",", columns.Select(it => string.Format(SqlTemplateBatchSelect, FormatValue(it.Value), Builder.GetTranslationColumnName(it.DbColumnName)))));
                        ++i;
                    }
                    pageIndex++;
                    batchInsetrSql.Append("\r\n;\r\n");
                }
                var result = batchInsetrSql.ToString();
                if (this.Context.CurrentConnectionConfig.DbType == DbType.SqlServer)
                {
                    result += "select @@identity;";
                }
                return(result);
            }
        }
示例#3
0
        public override string ToSqlString()
        {
            if (IsNoInsertNull)
            {
                DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
            }
            var    groupList     = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
            var    isSingle      = groupList.Count() == 1;
            string columnsString = string.Join(",", groupList.First().Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));

            if (isSingle)
            {
                string columnParametersString = string.Join(",", this.DbColumnInfoList.Select(it => Builder.SqlParameterKeyWord + it.DbColumnName));
                return(string.Format(SqlTemplate, GetTableNameString, columnsString, columnParametersString));
            }
            else
            {
                StringBuilder batchInsetrSql = new StringBuilder();
                batchInsetrSql.Append("INSERT INTO " + GetTableNameString + " ");
                batchInsetrSql.Append("(");
                batchInsetrSql.Append(columnsString);
                batchInsetrSql.Append(") VALUES");
                string insertColumns = "";
                int    i             = 0;
                foreach (var item in groupList)
                {
                    batchInsetrSql.Append("(");
                    insertColumns = string.Join(",", item.Select(it => FormatValue(i, it.DbColumnName, it.Value)));
                    batchInsetrSql.Append(insertColumns);
                    if (groupList.Last() == item)
                    {
                        batchInsetrSql.Append(") ");
                    }
                    else
                    {
                        batchInsetrSql.Append("),  ");
                    }
                    i++;
                }

                batchInsetrSql.AppendLine(";SELECT LAST_INSERT_ROWID();");
                var result = batchInsetrSql.ToString();
                return(result);
            }
        }
示例#4
0
        public virtual string ToSqlString()
        {
            if (IsNoUpdateNull)
            {
                DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
            }
            var groupList = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
            var isSingle  = groupList.Count() == 1;

            if (isSingle)
            {
                return(ToSingleSqlString(groupList));
            }
            else
            {
                return(TomultipleSqlString(groupList));
            }
        }
示例#5
0
        public virtual string ToSqlString()
        {
            if (IsNoUpdateNull)
            {
                DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
            }
            if (IsNoUpdateDefaultValue)
            {
                DbColumnInfoList = DbColumnInfoList.Where(it => it.Value.ObjToString() != UtilMethods.DefaultForType(it.PropertyType).ObjToString()).ToList();
            }
            var groupList = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
            var isSingle  = groupList.Count() == 1;

            if (isSingle)
            {
                return(ToSingleSqlString(groupList));
            }
            else
            {
                return(TomultipleSqlString(groupList));
            }
        }
示例#6
0
        public override string ToSqlString()
        {
            var identities = this.EntityInfo.Columns.Where(it => it.OracleSequenceName.HasValue()).ToList();

            if (IsNoInsertNull)
            {
                DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
            }
            var    groupList     = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
            var    isSingle      = groupList.Count() == 1;
            string columnsString = string.Join(",", groupList.First().Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));

            if (isSingle)
            {
                string columnParametersString = string.Join(",", this.DbColumnInfoList.Select(it => Builder.SqlParameterKeyWord + it.DbColumnName));
                if (identities.HasValue())
                {
                    columnsString          = columnsString.TrimEnd(',') + "," + string.Join(",", identities.Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
                    columnParametersString = columnParametersString.TrimEnd(',') + "," + string.Join(",", identities.Select(it => it.OracleSequenceName + ".nextval"));
                }
                return(string.Format(SqlTemplate, GetTableNameString, columnsString, columnParametersString));
            }
            else
            {
                StringBuilder batchInsetrSql = new StringBuilder();
                int           pageSize       = 200;
                int           pageIndex      = 1;
                int           totalRecord    = groupList.Count;
                int           pageCount      = (totalRecord + pageSize - 1) / pageSize;
                if (identities.HasValue())
                {
                    columnsString = columnsString.TrimEnd(',') + "," + string.Join(",", identities.Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
                }
                while (pageCount >= pageIndex)
                {
                    batchInsetrSql.AppendFormat(SqlTemplateBatch, GetTableNameString, columnsString);
                    int i = 0;
                    foreach (var columns in groupList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList())
                    {
                        var isFirst = i == 0;
                        if (!isFirst)
                        {
                            batchInsetrSql.Append(SqlTemplateBatchUnion);
                        }
                        var insertColumns = string.Join(",", columns.Select(it => string.Format(SqlTemplateBatchSelect, FormatValue(it.Value), Builder.GetTranslationColumnName(it.DbColumnName))));
                        if (identities.HasValue())
                        {
                            insertColumns = insertColumns.TrimEnd(',') + "," + string.Join(",", identities.Select(it =>
                            {
                                var seqValue = this.OracleSeqInfoList[it.OracleSequenceName];
                                this.OracleSeqInfoList[it.OracleSequenceName] = this.OracleSeqInfoList[it.OracleSequenceName] + 1;
                                return(seqValue + 1 + " AS " + it.DbColumnName);
                            }));
                        }
                        batchInsetrSql.Append("\r\n SELECT " + insertColumns + " FROM DUAL ");
                        ++i;
                    }
                    pageIndex++;
                    batchInsetrSql.Append("\r\n;\r\n");
                }
                return("BEGIN\r\n" + batchInsetrSql.ToString() + "\r\nEND;");
            }
        }
示例#7
0
        public override string ToSqlString()
        {
            var identities = this.EntityInfo.Columns.Where(it => it.OracleSequenceName.HasValue()).ToList();

            if (IsNoInsertNull)
            {
                DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
            }
            var    groupList     = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
            var    isSingle      = groupList.Count() == 1;
            string columnsString = string.Join(",", groupList.First().Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));

            if (isSingle && this.EntityInfo.EntityName != "Dictionary`2")
            {
                string columnParametersString = string.Join(",", this.DbColumnInfoList.Select(it => Builder.SqlParameterKeyWord + it.DbColumnName));
                if (identities.HasValue())
                {
                    columnsString          = columnsString.TrimEnd(',') + "," + string.Join(",", identities.Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
                    columnParametersString = columnParametersString.TrimEnd(',') + "," + string.Join(",", identities.Select(it => it.OracleSequenceName + ".nextval"));
                }
                return(string.Format(SqlTemplate, GetTableNameString, columnsString, columnParametersString));
            }
            else
            {
                StringBuilder batchInsetrSql = new StringBuilder();
                batchInsetrSql.AppendLine("INSERT ALL");
                foreach (var item in groupList)
                {
                    batchInsetrSql.Append("INTO " + GetTableNameString + " ");
                    string insertColumns = "";

                    batchInsetrSql.Append("(");
                    batchInsetrSql.Append(columnsString);
                    if (identities.HasValue())
                    {
                        batchInsetrSql.Append("," + string.Join(",", identities.Select(it => Builder.GetTranslationColumnName(it.DbColumnName))));
                    }
                    batchInsetrSql.Append(") VALUES");


                    batchInsetrSql.Append("(");
                    insertColumns = string.Join(",", item.Select(it => FormatValue(it.Value)));
                    batchInsetrSql.Append(insertColumns);
                    if (identities.HasValue())
                    {
                        batchInsetrSql.Append(",");
                        foreach (var idn in identities)
                        {
                            var seqvalue = this.OracleSeqInfoList[idn.OracleSequenceName];
                            this.OracleSeqInfoList[idn.OracleSequenceName] = this.OracleSeqInfoList[idn.OracleSequenceName] + 1;
                            if (identities.Last() == idn)
                            {
                                batchInsetrSql.Append(seqvalue);
                            }
                            else
                            {
                                batchInsetrSql.Append(seqvalue + ",");
                            }
                        }
                    }
                    batchInsetrSql.AppendLine(")  ");
                }
                if (identities.HasValue())
                {
                    batchInsetrSql.AppendLine("SELECT " + (this.OracleSeqInfoList.First().Value - 1) + "  FROM DUAL");
                }
                else
                {
                    batchInsetrSql.AppendLine("SELECT 1 FROM DUAL");
                }
                var result = batchInsetrSql.ToString();
                return(result);
            }
        }