public async Task <Response> Handle(Request request, CancellationToken token)
{
string connectionString = "Server=LAPTOP-RD9P71LP\\SQLEXPRESS;Database=TEST1;UID=sa;PWD=1234;";
DbSchema.Table tableSchema = CodingHelper.GetDbTableSchema(connectionString, "Article");
CsSchema.Class csClass = mapper.Map <CsSchema.Class>(tableSchema);
CsSchema.Unit unit = new CsSchema.Unit();
CsSchema.Namespace @namespace = new CsSchema.Namespace();
@namespace.Name = "QQ";
@namespace.Classes = new CsSchema.Class[] { csClass };
unit.Namespaces = new CsSchema.Namespace[] { @namespace };
var syntax = mapper.Map <CompilationUnitSyntax>(unit);
return(new Response
{
Code = syntax
.NormalizeWhitespace()
.ToFullString()
});
}
public async Task <Response> Handle(Request request, CancellationToken token)
{
List <string> lines = new List <string>();
DbSchema.Table tableSchema = CodingHelper.GetDbTableSchema(databaseConfig.ConnectionString, request.TableName);
if (request.ContainsIdentity && tableSchema.Identity != null)
{
lines.Add($"SET IDENTITY_INSERT [{tableSchema.Name}] ON");
}
string sql = @"
SELECT $Fields
FROM (SELECT Row_number()
OVER(ORDER BY [$OrderBy]) AS [__RowNumber], *
FROM [$TableName]) t
WHERE [__RowNumber] BETWEEN @Start AND @End
ORDER BY [__RowNumber] ";
string orderby = null;
if (tableSchema.Identity != null)
{
orderby = tableSchema.Identity.Name;
}
else if (tableSchema.PrimaryKeys.Any())
{
orderby = tableSchema.PrimaryKeys.First().Name;
}
else
{
orderby = tableSchema.Fields.FirstOrDefault(x => mapper.Map <CsSchema.Property>(x).TypeName == "int")?.Name;
if (string.IsNullOrWhiteSpace(orderby))
{
orderby = tableSchema.Fields.FirstOrDefault().Name;
}
}
sql = sql.Replace("$Fields", string.Join(", ", tableSchema.Fields.Select(x => $"[{x.Name}]")));
sql = sql.Replace("$OrderBy", orderby);
sql = sql.Replace("$TableName", tableSchema.Name);
await sqlHelper.ExecuteReaderEachAsync(sql,
new SqlParameter[]
{
new SqlParameter("@Start", request.StartRowNumber),
new SqlParameter("@End", request.EndRowNumber)
},
reader =>
{
List <string> fields = new List <string>();
List <string> values = new List <string>();
foreach (var field in tableSchema.Fields)
{
if (field.IsIdentity && (request.ContainsIdentity == false))
{
continue;
}
fields.Add($"[{field.Name}]");
if (reader[field.Name] == DBNull.Value || reader[field.Name] == null)
{
values.Add("null");
}
else if (field.TypeName.In("bigint", "decimal", "float", "int", "money", "numeric", "real", "smallint", "smallmoney"))
{
values.Add($"{reader[field.Name]}");
}
else if (field.TypeName.In("date"))
{
values.Add($"'{reader[field.Name]:yyyy/MM/dd}'");
}
else if (field.TypeName.In("datetime"))
{
values.Add($"'{reader[field.Name]:yyyy/MM/dd HH:mm:ss}'");
}
public async Task <Response> Handle(Request request, CancellationToken token)
{
List <string> lines = new List <string>();
DbSchema.Table tableSchema = CodingHelper.GetDbTableSchema(databaseConfig.ConnectionString, request.TableName);
if (request.ContainsIdentity && tableSchema.Identity != null)
{
lines.Add($"SELECT 'SET IDENTITY_INSERT [{tableSchema.Name}] ON'");
}
List <string> fields = new List <string>();
List <string> values = new List <string>();
foreach (var field in tableSchema.Fields)
{
var csProperty = mapper.Map <CsSchema.Property>(field);
if (field.IsIdentity && (request.ContainsIdentity == false))
{
continue;
}
fields.Add($"[{field.Name}]");
string value = $"[{field.Name}]";
if (csProperty.TypeName != "string")
{
value = $"CAST({value} AS NVARCHAR)";
}
if (csProperty.TypeName == "string")
{
value = $"({value} COLLATE Chinese_Taiwan_Stroke_CI_AS)";
}
if (csProperty.TypeName.In("DateTime", "DateTime?"))
{
value = $"'''' + {value} + ''''";
}
if (csProperty.TypeName == "string")
{
value = $"'''' + REPLACE({value}, '''', '''''') + ''''";
}
if (field.IsNullable)
{
value = $"ISNULL({value}, 'null')";
}
values.Add(value);
}
string sql = $@"
SELECT '
INSERT INTO [{tableSchema.Name}] ({string.Join(", ", fields)})
VALUES (' + {string.Join(" + ', ' + ", values)} + ');'
FROM [{tableSchema.Name}]".DecreaseIndent();
lines.Add(sql);
if (request.ContainsIdentity && tableSchema.Identity != null)
{
lines.Add($"SELECT 'SET IDENTITY_INSERT [{tableSchema.Name}] OFF'");
}
return(new Response()
{
Lines = lines
});
}
