protected override string GetCreateTableSql()
{
StringBuilder builder = new StringBuilder($"CREATE TABLE IF NOT EXISTS `{Schema.Database }`.`{Schema.TableName}` (");
string columNames = string.Join(", ", Columns.Select(p => $"`{p.Name}` {ConvertToDbType(p.DataType.ToLower())} {GetAutoIncrementString(p.Name)}"));
builder.Append(columNames.Substring(0, columNames.Length));
foreach (var index in Indexs)
{
string name = string.Join("_", index.Select(c => c));
string indexColumNames = string.Join(", ", index.Select(c => $"`{c}`"));
builder.Append($", KEY `index_{name}` ({indexColumNames.Substring(0, indexColumNames.Length)})");
}
foreach (var unique in Uniques)
{
string name = string.Join("_", unique.Select(c => c));
string uniqueColumNames = string.Join(", ", unique.Select(c => $"`{c}`"));
builder.Append($", UNIQUE KEY `unique_{name}` ({uniqueColumNames.Substring(0, uniqueColumNames.Length)})");
}
string primaryColumNames = string.Join(", ", Primary.Select(c => $"`{c}`"));
builder.Append($", PRIMARY KEY ({primaryColumNames.Substring(0, primaryColumNames.Length)})");
builder.Append(") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8");
string sql = builder.ToString();
Logger.Info(sql);
return(sql);
}
/// <summary>
/// In HTML konvertieren
/// </summary>
/// <param name="context">Der Kontext, indem das Steuerelement dargestellt wird</param>
/// <returns>Das Control als HTML</returns>
public override IHtmlNode Render(RenderContext context)
{
if (Header.Content.Count == 0 && Preferences.Count == 0 && Primary.Count == 0 && Secondary.Count == 0)
{
return(null);
}
var elements = new List <IHtmlNode>
{
Header.Render(context)
};
elements.AddRange(Preferences.Select(x => x.Render(context)));
elements.AddRange(Primary.Select(x => x.Render(context)));
elements.AddRange(Secondary.Select(x => x.Render(context)));
return(new HtmlElementTextContentDiv(elements)
{
ID = ID,
Class = Css.Concatenate("footer", GetClasses()),
Style = Style.Concatenate("display: block;", GetStyles()),
Role = Role
});
}
protected override string GetUpdateSql()
{
string setParamenters = string.Join(", ", UpdateColumns.Select(p => $"`{p.Name}`=@{p.Name}"));
string primaryParamenters = string.Join(" AND ", Primary.Select(p => $"`{p.Name}`=@{p.Name}"));
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("UPDATE `{0}`.`{1}` SET {2} WHERE {3};",
Schema.Database,
Schema.TableName,
setParamenters, primaryParamenters);
return(sqlBuilder.ToString());
}
protected override string GetSelectSql()
{
string selectParamenters = string.Join(", ", UpdateColumns.Select(p => $"[{p.Name}]"));
string primaryParamenters = string.Join(" AND ", Primary.Select(p => $"[{p.Name}]=@{p.Name}"));
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("USE {0}; SELECT {1} FROM [{2}] WHERE {3};",
Schema.Database,
selectParamenters,
Schema.TableName,
primaryParamenters);
return(sqlBuilder.ToString());
}
/// <summary>
/// In HTML konvertieren
/// </summary>
/// <param name="context">Der Kontext, indem das Steuerelement dargestellt wird</param>
/// <returns>Das Control als HTML</returns>
public override IHtmlNode Render(RenderContext context)
{
if (Preferences.Count == 0 && Primary.Count == 0 && Secondary.Count == 0)
{
return(null);
}
var preferences = new HtmlElementTextContentDiv(Preferences.Select(x => x.Render(context)));
var primary = new HtmlElementTextContentDiv(Primary.Select(x => x.Render(context)));
var secondary = new HtmlElementTextContentDiv(Secondary.Select(x => x.Render(context)));
return(new HtmlElementTextContentDiv(preferences, primary, secondary)
{
ID = ID,
Class = Css.Concatenate("proterty", GetClasses()),
Style = Style.Concatenate("display: block;", GetStyles()),
Role = Role
});
}
protected override string GetCreateTableSql()
{
StringBuilder builder = new StringBuilder($"CREATE TABLE IF NOT EXISTS `{Schema.Database }`.`{Schema.TableName}` (");
string columNames = string.Join(", ", Columns.Select(p => $"`{p.Name}` {ConvertToDbType(p.DataType)} "));
builder.Append(columNames);
builder.Append(Primary == null || Primary.Count == 0 ? (string.IsNullOrEmpty(columNames) ? "" : ",") + "`__id` bigint AUTO_INCREMENT" : "");
foreach (var index in Indexs)
{
string name = string.Join("_", index.Select(c => c));
string indexColumNames = string.Join(", ", index.Select(c => $"`{c}`"));
builder.Append($", KEY `index_{name}` ({indexColumNames.Substring(0, indexColumNames.Length)})");
}
foreach (var unique in Uniques)
{
string name = string.Join("_", unique.Select(c => c));
string uniqueColumNames = string.Join(", ", unique.Select(c => $"`{c}`"));
builder.Append($", UNIQUE KEY `unique_{name}` ({uniqueColumNames.Substring(0, uniqueColumNames.Length)})");
}
string primaryColumNames = Primary == null || Primary.Count == 0 ? "`__id` " : string.Join(", ", Primary.Select(c => $"`{c.Name}`"));
builder.Append($", PRIMARY KEY ({primaryColumNames.Substring(0, primaryColumNames.Length)})");
builder.Append(") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8");
string sql = builder.ToString();
return(sql);
}
protected override string GetCreateTableSql()
{
var identity = "IDENTITY(1,1)";
StringBuilder builder = new StringBuilder($"USE {Schema.Database}; IF OBJECT_ID('{Schema.TableName}', 'U') IS NULL CREATE table {Schema.TableName} (");
StringBuilder columnNames = new StringBuilder();
if (Primary.Count == 0)
{
foreach (var p in Columns)
{
columnNames.Append($",[{p.Name}] {ConvertToDbType(p.DataType)} NULL");
}
}
else
{
foreach (var p in Columns)
{
var identityPart = AutoIncrement.Contains(p.Name) ? identity : "";
var nullPart = Primary.Any(k => k.Name == p.Name) ? "NOT NULL" : "NULL";
columnNames.Append($",[{p.Name}] {ConvertToDbType(p.DataType)} {identityPart} {nullPart}");
}
}
builder.Append(columnNames.ToString().Substring(1, columnNames.Length - 1));
builder.Append(Primary == null || Primary.Count == 0 ? (columnNames.Length == 0 ? "" : ",") + "[__id] [int] IDENTITY(1,1) NOT NULL," : ",");
string primaryKey = Primary == null || Primary.Count == 0 ? "[__id] ASC" : string.Join(", ", Primary.Select(p => $"[{p.Name}] ASC"));
builder.Append(
$" CONSTRAINT [PK_{Schema.TableName}] PRIMARY KEY CLUSTERED ({primaryKey.Substring(0, primaryKey.Length)})WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]) ON[PRIMARY];");
foreach (var index in Indexs)
{
string name = string.Join("_", index.Select(c => c));
string indexColumNames = string.Join(", ", index.Select(c => $"[{c}]"));
builder.Append($"CREATE NONCLUSTERED INDEX [index_{name}] ON {Schema.TableName} ({indexColumNames.Substring(0, indexColumNames.Length)});");
}
foreach (var unique in Uniques)
{
string name = string.Join("_", unique.Select(c => c));
string uniqueColumNames = string.Join(", ", unique.Select(c => $"[{c}]"));
builder.Append($"CREATE UNIQUE NONCLUSTERED INDEX [unique_{name}] ON {Schema.TableName} ({uniqueColumNames.Substring(0, uniqueColumNames.Length)});");
}
return(builder.ToString());
}
