コード例 #1
0
        public IActionResult DropInitialize()
        {
            RawSql rawSql = new RawSql(_db);

            try
            {
                object[]      pram   = new object[] { };
                List <string> sql_ls = new List <string>();
                sql_ls.Add(@"DROP DATABASE hmsDb");

                //sql_ls.Add(@"DELETE FROM US_USER_ROLE");
                //sql_ls.Add(@"DELETE FROM US_ROLE_MENU");
                //sql_ls.Add(@"DELETE FROM US_USER");
                //sql_ls.Add(@"DELETE FROM US_TYPE");
                //sql_ls.Add(@"DELETE FROM US_ROLE");
                //sql_ls.Add(@"DELETE FROM US_RELIGION");
                //sql_ls.Add(@"DELETE FROM US_MARITAIL_STATUS");
                //sql_ls.Add(@"DELETE FROM US_GENDER");
                //sql_ls.Add(@"DELETE FROM US_BLOOD_GROUP");
                //sql_ls.Add(@"DELETE FROM US_CHILD_MENU");
                //sql_ls.Add(@"DELETE FROM US_PARENT_MENU");
                //sql_ls.Add(@"DELETE FROM US_MODULE");
                //sql_ls.Add(@"DELETE FROM US_EMAIL_SERVER");
                //sql_ls.Add(@"DELETE FROM US_EMAIL_BOX");

                foreach (string sql in sql_ls)
                {
                    rawSql.ExecuteSqlCommand(sql, pram);
                }
                return(Content("Data Config Drop Iniliazation Succeeded"));
            }
            catch (Exception ex) { return(Content(ex.Message)); }
        }
コード例 #2
0
ファイル: MyDbTestController.cs プロジェクト: IbneKayesh/hms
        public IActionResult ExecuteSqlCommand()
        {
            RawSql obj = new RawSql(db);
            var    p1  = new SqlParameter("@p1", "New Value");

            obj.ExecuteSqlCommand(query: @"update US_UNIT SET UNIT_NAME=@p1 WHERE ID=2", parameters: new object[] { p1 });
            return(View());
        }
コード例 #3
0
        public override void Up()
        {
            Alter.Table("User")
            .AddColumn("CreationDate").AsDateTimeOffset().Nullable();

            Update.Table("User").Set(new { CreationDate = RawSql.Insert("SYSDATETIMEOFFSET()") }).AllRows();

            Alter.Table("User")
            .AlterColumn("CreationDate").AsDateTimeOffset().NotNullable();
        }
コード例 #4
0
        public void CanInsertAtAtIdentity()
        {
            var expression = new InsertDataExpression {
                TableName = "TestTable"
            };

            expression.Rows.Add(new InsertionDataDefinition
            {
                new KeyValuePair <string, object>("Id", 1),
                new KeyValuePair <string, object>("Name", RawSql.Insert("@@IDENTITY")),
                new KeyValuePair <string, object>("Website", "codethinked.com")
            });

            var result = Generator.Generate(expression);

            result.ShouldBe("INSERT INTO [dbo].[TestTable] ([Id], [Name], [Website]) VALUES (1, @@IDENTITY, N'codethinked.com')");
        }
コード例 #5
0
        public void CanInsertScopeIdentity()
        {
            var expression = new InsertDataExpression();

            expression.TableName = "TestTable";
            expression.Rows.Add(new InsertionDataDefinition
            {
                new KeyValuePair <string, object>("Id", 1),
                new KeyValuePair <string, object>("Name", RawSql.Insert("SCOPE_IDENTITY()")),
                new KeyValuePair <string, object>("Website", "codethinked.com")
            });

            var sql = generator.Generate(expression);

            var expected = "INSERT INTO [dbo].[TestTable] ([Id], [Name], [Website]) VALUES (1, SCOPE_IDENTITY(), 'codethinked.com')";

            sql.ShouldBe(expected);
        }
コード例 #6
0
ファイル: BaseVisitor.cs プロジェクト: susch19/querybuilder
 public void Visit(RawSql rawSql) => Result.Append(rawSql.Sql).Append(" ");
コード例 #7
0
 protected virtual InsertionDataDefinition CreateVersionInfoInsertionData(long version, string description)
 {
     return(new InsertionDataDefinition
     {
         new KeyValuePair <string, object>(VersionTableMetaData.ColumnName, version),
         new KeyValuePair <string, object>(VersionTableMetaData.AppliedOnColumnName, RawSql.Insert("CURRENT_TIMESTAMP")),
         new KeyValuePair <string, object>(VersionTableMetaData.DescriptionColumnName, description)
     });
 }
コード例 #8
0
        /// <summary>
        /// 解析 SQL 命令
        /// <para>
        /// 返回的已经解析语义中执行批次用 null 分开
        /// </para>
        /// </summary>
        /// <param name="dbQueryables">查询语句</param>
        /// <returns></returns>
        public override List <RawCommand> Resolve(List <object> dbQueryables)
        {
            bool              haveBegin = false;
            ResolveToken      token     = null;
            List <RawCommand> sqlList   = new List <RawCommand>();

            for (int i = 0; i < dbQueryables.Count; i++)
            {
                object obj = dbQueryables[i];
                if (obj == null)
                {
                    continue;
                }

                if (obj is IDbQueryable)
                {
                    IDbQueryable dbQueryable = (IDbQueryable)obj;
                    dbQueryable.Parameterized = true;
                    if (token == null)
                    {
                        token = new ResolveToken();
                    }
                    if (token.Parameters == null)
                    {
                        token.Parameters = new List <IDbDataParameter>(8);
                    }

                    var cmd2 = dbQueryable.Resolve(0, true, token);
                    if (cmd2 is MappingCommand)
                    {
                        // 查询单独执行
                        if (sqlList.Count > 0 && (i - 1) >= 0 && sqlList[sqlList.Count - 1] != null)
                        {
                            sqlList.Add(null);
                        }

                        sqlList.Add(cmd2);
                        sqlList.Add(null);
                        token            = new ResolveToken();
                        token.Parameters = new List <IDbDataParameter>(8);
                    }
                    else
                    {
                        // 增删改
                        if (!haveBegin)
                        {
                            sqlList.Add(new RawCommand("BEGIN"));
                            haveBegin = true;
                        }
                        sqlList.Add(cmd2);
                        if (cmd2.Parameters != null && cmd2.Parameters.Count > 1000)
                        {
                            // 1000个参数,就要重新分批
                            if (haveBegin)
                            {
                                sqlList.Add(new RawCommand("END;"));
                                haveBegin = false;
                                sqlList.Add(null);
                            }
                            token            = new ResolveToken();
                            token.Parameters = new List <IDbDataParameter>(8);
                        }

                        if (i + 1 < dbQueryables.Count)
                        {
                            // 检查下一条是否是选择语句
                            bool isQuery = false;
                            if (dbQueryables[i + 1] is IDbQueryable)
                            {
                                var queryInfo = ((IDbQueryable)dbQueryables[i + 1]).Parse();
                                isQuery = queryInfo is IDbQueryableInfo_Select;
                            }
                            else if ((dbQueryables[i + 1] is string))
                            {
                                string sql    = dbQueryables[i + 1].ToString();
                                string method = string.Empty;
                                if (sql.Length > 6)
                                {
                                    method = sql.Substring(0, 6).Trim().ToUpper();
                                }
                                isQuery = method == "SELECT";
                            }
                            else if (dbQueryables[i + 1] is RawSql)
                            {
                                string sql    = ((RawSql)dbQueryables[i + 1]).CommandText;
                                string method = string.Empty;
                                if (sql.Length > 6)
                                {
                                    method = sql.Substring(0, 6).Trim().ToUpper();
                                }
                                isQuery = method == "SELECT";
                            }

                            // 如果下一条是SELECT 语句,则需要结束当前语句块
                            if (isQuery)
                            {
                                if (haveBegin)
                                {
                                    sqlList.Add(new RawCommand("END;"));
                                    haveBegin = false;
                                    sqlList.Add(null);
                                }
                                token            = new ResolveToken();
                                token.Parameters = new List <IDbDataParameter>(8);
                            }
                        }
                    }
                }
                else if (obj is RawSql || obj is string)
                {
                    string sql = string.Empty;
                    if (obj is string)
                    {
                        sql = obj.ToString();
                    }
                    else
                    {
                        RawSql rawSql = (RawSql)obj;
                        // 解析参数
                        object[] args = null;
                        if (rawSql.Parameters != null)
                        {
                            args = rawSql.Parameters.Select(x => this.DbValue.GetSqlValue(x, token)).ToArray();
                        }
                        sql = rawSql.CommandText;
                        if (args != null && args.Length > 0)
                        {
                            sql = string.Format(sql, args);
                        }
                    }


                    string methodName = string.Empty;
                    if (sql.Length > 6)
                    {
                        methodName = sql.Substring(0, 6).Trim().ToUpper();
                    }
                    if (methodName == "SELECT")
                    {
                        if (sqlList.Count > 0 && (i - 1) >= 0 && sqlList[sqlList.Count - 1] != null)
                        {
                            sqlList.Add(null);
                        }
                    }


                    var cmd2 = new RawCommand(sql, token.Parameters, CommandType.Text);
                    sqlList.Add(cmd2);

                    if (methodName == "SELECT")
                    {
                        sqlList.Add(cmd2);
                        sqlList.Add(null);
                        token            = new ResolveToken();
                        token.Parameters = new List <IDbDataParameter>(8);
                    }
                    else if (cmd2.Parameters != null && cmd2.Parameters.Count > 1000)
                    {
                        // 1000个参数,就要重新分批
                        if (haveBegin)
                        {
                            sqlList.Add(new RawCommand("END;"));
                            sqlList.Add(null);
                            haveBegin = false;
                        }
                        token            = new ResolveToken();
                        token.Parameters = new List <IDbDataParameter>(8);
                    }
                }
                else
                {
                    if (!haveBegin)
                    {
                        sqlList.Add(new RawCommand("BEGIN"));
                        haveBegin = true;
                    }
                    // 解析批量插入操作
                    List <IDbQueryable> bulkList = obj as List <IDbQueryable>;
                    if (bulkList != null && bulkList.Count > 0)
                    {
                        this.ResolveBulk(sqlList, bulkList);
                    }
                }

                if (haveBegin && i == dbQueryables.Count - 1)
                {
                    sqlList.Add(new RawCommand("END;"));
                }
            }

            return(sqlList);
        }
コード例 #9
0
        public override void Up()
        {
            Create.Table("usuarios")
            .WithColumn("id").AsString(36).NotNullable().PrimaryKey().WithDefaultValue(RawSql.Insert("gen_random_uuid()"))
            .WithColumn("nome").AsAnsiString(80).NotNullable()
            .WithColumn("email").AsAnsiString(250).Unique().NotNullable()
            .WithColumn("criadoem").AsDateTime().NotNullable()
            .WithColumn("alteradoem").AsDateTime().Nullable();

            Create.Table("leiloes")
            .WithColumn("id").AsString(36).NotNullable().PrimaryKey().WithDefaultValue(RawSql.Insert("gen_random_uuid()"))
            .WithColumn("titulo").AsString(250).NotNullable()
            .WithColumn("descricao").AsString().Nullable()
            .WithColumn("leiloadoporid").AsString(36).NotNullable()
            .WithColumn("lanceminimo").AsDecimal(10, 2)
            .WithColumn("datainicio").AsDateTime().NotNullable()
            .WithColumn("datafim").AsDateTime().Nullable()
            .WithColumn("ispublico").AsBoolean().NotNullable().WithDefaultValue(false)
            .WithColumn("status").AsInt32().Nullable()
            .WithColumn("lanceganhadorid").AsString(36).Nullable()
            .WithColumn("criadoem").AsDateTime().NotNullable()
            .WithColumn("alteradoem").AsDateTime().Nullable();

            Create.Table("lances")
            .WithColumn("id").AsString(36).NotNullable().PrimaryKey().WithDefaultValue(RawSql.Insert("gen_random_uuid()"))
            .WithColumn("valor").AsDecimal(10, 2).NotNullable()
            .WithColumn("interessadoid").AsString(36).NotNullable()
            .WithColumn("leilaoid").AsString(36).NotNullable()
            .WithColumn("criadoem").AsDateTime().NotNullable();


            Create.ForeignKey("FK_LEILOES_USUARIOS")
            .FromTable("leiloes").ForeignColumn("leiloadoporid")
            .ToTable("usuarios").PrimaryColumn("id");

            Create.ForeignKey("FK_LANCES_LEILOES")
            .FromTable("lances").ForeignColumn("leilaoid")
            .ToTable("leiloes").PrimaryColumn("id");

            Create.ForeignKey("FK_LANCES_USUARIOS")
            .FromTable("lances").ForeignColumn("interessadoid")
            .ToTable("usuarios").PrimaryColumn("id");
        }
コード例 #10
0
 public static ICreateTableColumnOptionOrWithColumnSyntax WithTimeStamps(
     this ICreateTableWithColumnSyntax tableWithColumnSyntax)
 {
     return(tableWithColumnSyntax
            .WithColumn("created_at").AsDateTime().NotNullable().WithDefaultValue(RawSql.Insert("now()"))
            .WithColumn("updated_at").AsDateTime().NotNullable().WithDefaultValue(RawSql.Insert("now()")));
 }