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)); }
}
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());
}
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();
}
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')");
}
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);
}
public void Visit(RawSql rawSql) => Result.Append(rawSql.Sql).Append(" ");
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)
});
}
/// <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);
}
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");
}
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()")));
}