private static string GetProcedureTypeUpdateCommand(ProcedureOperate operate, IEntitySchema view)
{
var builder = new StringBuilder();
builder.AppendFormat("{0} Procedure [dbo].[Update{1}Type]", operate.ToString(), view.Name);
builder.AppendLine();
builder.AppendFormat(" @{0}Type [{0}Type] Readonly", view.Name);
builder.AppendLine();
builder.AppendLine("As");
builder.AppendLine("Begin");
builder.AppendLine(" Set NoCount ON;");
builder.AppendLine();
foreach (var table in view.Tables)
{
builder.AppendFormat(" Update [dbo].[{0}]", table.Name);
builder.AppendLine();
builder.Append(" Set ");
var aryColumn = table.Columns;
var columnList = aryColumn.Where(p => p.Mode != ColumnMode.ReadOnly).ToList();
if (columnList.Count(p => p.IsPrimary) != 1)
{
throw new ArgumentException(string.Format("table [{0}] primary error!", table.Name));
}
var primaryColumn = columnList.First(p => p.IsPrimary);
columnList.Remove(primaryColumn);
columnList.RemoveAll(p => p.IsIdentity);
if (columnList.Count > 0)
{
var firstColumn = columnList.First();
builder.AppendFormat("[{0}] = [ec].[{0}]", firstColumn.Name);
}
for (int i = 1; i < columnList.Count; i++)
{
var column = columnList[i];
builder.AppendLine();
builder.AppendFormat(" ,[{0}] = [ec].[{0}]", column.Name);
}
builder.AppendLine();
builder.AppendFormat(" From [dbo].[{0}]", table.Name);
builder.AppendLine();
builder.AppendFormat(" Inner Join @{0}Type As [ec] On [dbo].[{1}].[{2}] = [ec].[{2}];", view.Name, table.Name, primaryColumn.Name);
builder.AppendLine();
builder.AppendLine();
}
builder.AppendLine("End");
return(builder.ToString());
}
private static string GetProcedureTypeInsertCommand(ProcedureOperate operate, IEntitySchema view)
{
var builder = new StringBuilder();
builder.AppendFormat("{0} Procedure [dbo].[Insert{1}Type]", operate.ToString(), view.Name);
builder.AppendLine();
builder.AppendFormat(" @{0}Type [{0}Type] Readonly", view.Name);
builder.AppendLine();
builder.AppendLine("As");
builder.AppendLine("Begin");
builder.AppendLine(" Set NoCount ON;");
builder.AppendLine();
var columnBuilder = new StringBuilder();
foreach (var table in view.Tables)
{
builder.AppendFormat(" Insert Into [dbo].[{0}]", table.Name);
builder.AppendLine();
builder.Append(" (");
var columns = table.Columns.Where(p => p.Mode != ColumnMode.ReadOnly && !p.IsIdentity).ToList();
if (columns.Count > 0)
{
var column = columns.First();
columnBuilder.AppendFormat("[{0}]", column.Name);
for (int i = 1; i < columns.Count; i++)
{
column = columns[i];
columnBuilder.AppendLine();
columnBuilder.AppendFormat(" ,[{0}]", column.Name);
}
}
builder.Append(columnBuilder.ToString());
builder.AppendLine();
builder.Append(" ) Select ");
builder.AppendLine(columnBuilder.ToString());
builder.AppendFormat(" From @{0}Type;", view.Name);
builder.AppendLine();
builder.AppendLine();
columnBuilder.Clear();
}
builder.AppendLine("End");
return(builder.ToString());
}
private static string GetProcedureCommand(IEntitySchema view, ProcedureOperate operate, ProcedureUsage usage)
{
switch (usage)
{
case ProcedureUsage.Insert:
return(GetProcedureInsertCommand(operate, view));
case ProcedureUsage.Update:
return(GetProcedureUpdateCommand(operate, view));
case ProcedureUsage.TypeInsert:
return(GetProcedureTypeInsertCommand(operate, view));
case ProcedureUsage.TypeUpdate:
return(GetProcedureTypeUpdateCommand(operate, view));
default:
throw new InvalidOperationException("unknown procedure type!");
}
}
private static string GetProcedureUpdateCommand(ProcedureOperate operate, IEntitySchema view)
{
var builder = new StringBuilder();
builder.AppendFormat("{0} Procedure [dbo].[Update{1}]", operate.ToString(), view.Name);
builder.AppendLine();
var viewColumns = view.Columns;
var validColumns = viewColumns.Where(p => p.Mode != ColumnMode.ReadOnly).ToList();
if (validColumns.Count(p => p.IsPrimary) != 1)
{
throw new ArgumentException("view primary error!");
}
var primaryColumn = validColumns.First(p => p.IsPrimary);
validColumns.RemoveAll(p => !p.IsPrimary && p.IsIdentity);
if (validColumns.Count > 1)
{
var firstColumn = validColumns.First();
builder.AppendFormat(" @{0} {1}", firstColumn.Name, MsSqlHelper.GetDbTypeString(firstColumn));
if (!string.IsNullOrEmpty(firstColumn.DefaultValue))
{
builder.AppendFormat(" = {0}", firstColumn.DefaultValue);
}
}
for (int i = 1; i < validColumns.Count; i++)
{
var column = validColumns[i];
builder.AppendLine(",");
builder.AppendFormat(" @{0} {1}", column.Name, MsSqlHelper.GetDbTypeString(column));
if (!string.IsNullOrEmpty(column.DefaultValue))
{
builder.AppendFormat(" = {0}", column.DefaultValue);
}
}
builder.AppendLine();
builder.AppendLine("As");
builder.AppendLine("Begin");
builder.AppendLine(" Set NoCount ON;");
builder.AppendLine();
var builderColumn = new StringBuilder();
validColumns.Remove(primaryColumn);
var groupColumnList = validColumns.Where(p => !p.IsPrimary).GroupBy(p => p.Table).ToList();
foreach (var groupColumn in groupColumnList)
{
var tableColumnList = groupColumn.ToList();
if (tableColumnList.Count > 0)
{
var firstColumn = tableColumnList.First();
builderColumn.AppendFormat("[{0}] = @{0}", firstColumn.Name);
}
for (int i = 1; i < tableColumnList.Count; i++)
{
var column = tableColumnList[i];
builderColumn.AppendLine();
builderColumn.AppendFormat(" ,[{0}] = @{0}", column.Name);
}
builder.AppendFormat(" Update [dbo].[{0}]", groupColumn.Key);
builder.AppendLine();
builder.Append(" Set ");
builder.AppendLine(builderColumn.ToString());
builder.AppendFormat(" Where [{0}] = @{0};", primaryColumn.Name);
builder.AppendLine();
builder.AppendLine();
builderColumn.Clear();
}
builder.AppendLine("End");
return(builder.ToString());
}
private static string GetProcedureInsertCommand(ProcedureOperate operate, IEntitySchema view)
{
var builder = new StringBuilder();
builder.AppendFormat("{0} Procedure [dbo].[Insert{1}]", operate.ToString(), view.Name);
builder.AppendLine();
var viewColumns = view.Columns;
var viewColumnList = viewColumns.Where(p => p.Mode != ColumnMode.ReadOnly && !p.IsIdentity).ToList();
if (viewColumnList.Count > 1)
{
var firstColumn = viewColumnList.First();
builder.AppendFormat(" @{0} {1}", firstColumn.Name, MsSqlHelper.GetDbTypeString(firstColumn));
if (!string.IsNullOrEmpty(firstColumn.DefaultValue))
{
builder.AppendFormat(" = {0}", firstColumn.DefaultValue);
}
}
for (int i = 1; i < viewColumnList.Count; i++)
{
var column = viewColumnList[i];
builder.AppendLine(",");
builder.AppendFormat(" @{0} {1}", column.Name, MsSqlHelper.GetDbTypeString(column));
if (!string.IsNullOrEmpty(column.DefaultValue))
{
builder.AppendFormat(" = {0}", column.DefaultValue);
}
}
builder.AppendLine();
builder.AppendLine("As");
builder.AppendLine("Begin");
builder.AppendLine(" Set NoCount ON;");
builder.AppendLine();
var builderParam = new StringBuilder();
var builderColumn = new StringBuilder();
foreach (var table in view.Tables)
{
var aryTableColumn = table.Columns;
var tableColumnList = aryTableColumn.Where(p => p.CanRead && p.Mode != ColumnMode.ReadOnly && !p.IsIdentity).ToList();
if (tableColumnList.Count > 0)
{
var firstColumn = tableColumnList.First();
builderColumn.AppendFormat("[{0}]", firstColumn.Name);
builderParam.AppendFormat("@{0}", firstColumn.Name);
}
for (int i = 1; i < tableColumnList.Count; i++)
{
var column = tableColumnList[i];
builderColumn.AppendLine();
builderColumn.AppendFormat(" ,[{0}]", column.Name);
builderParam.AppendLine();
builderParam.AppendFormat(" ,@{0}", column.Name);
}
builder.AppendFormat(" Insert Into [dbo].[{0}]", table.Name);
builder.AppendLine();
builder.Append(" (");
builder.AppendLine(builderColumn.ToString());
builder.AppendLine(" ) Values");
builder.Append(" (");
builder.AppendLine(builderParam.ToString());
builder.AppendLine(" );");
builder.AppendLine();
builderColumn.Clear();
builderParam.Clear();
}
builder.AppendLine("End");
return(builder.ToString());
}
public static string GetViewCommand(IEntitySchema view, ProcedureOperate operate)
{
if (view == null)
{
throw new ArgumentNullException(nameof(view));
}
var columns = view.Columns;
if (columns.Count == 0)
{
throw new ArgumentException("not column");
}
if (columns.Count(p => p.IsPrimary) != 1)
{
throw new ArgumentException("primary error!");
}
var groupColumnList = columns.GroupBy(p => p.Table).OrderByDescending(p => p.Any(t => t.IsPrimary)).ToList();
if (groupColumnList.Count <= 1)
{
throw new ArgumentException("table count <= 1");
}
var primaryColumn = columns.First(p => p.IsPrimary);
var builder = new StringBuilder();
builder.AppendFormat("{0} View [dbo].[v_{1}]", operate.ToString(), view.Name);
builder.AppendLine();
builder.AppendLine("As");
var fristGroupColumn = groupColumnList.First();
builder.Append("Select ");
using (var ce = groupColumnList.GetEnumerator())
{
ce.MoveNext();
using (var e = ce.Current.GetEnumerator())
{
e.MoveNext();
builder.AppendFormat("[dbo].[{0}].[{1}]", ce.Current.Key, e.Current.Name);
while (e.MoveNext())
{
builder.AppendLine().AppendFormat(", [dbo].[{0}].[{1}]", ce.Current.Key, e.Current.Name);
}
}
while (ce.MoveNext())
{
using (var e = ce.Current.GetEnumerator())
{
while (e.MoveNext())
{
builder.AppendLine().AppendFormat(", [dbo].[{0}].[{1}]", ce.Current.Key, e.Current.Name);
}
}
}
}
builder.AppendLine();
builder.AppendFormat("From [dbo].[{0}]", fristGroupColumn.Key);
for (int i = 1; i < groupColumnList.Count; i++)
{
var groupColumn = groupColumnList[i];
builder.AppendLine();
builder.AppendFormat("Inner Join [dbo].[{0}] On [{0}].[{1}] = [{2}].[{1}]",
groupColumn.Key, primaryColumn.Name, fristGroupColumn.Key);
}
return(builder.ToString());
}
