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 }); }