public MemoryStream ExportInsert(StepDataModel form, Func <TableModel, DataTable> func)
{
var stream = new MemoryStream();
using (var sw = new StreamWriter(stream, new UnicodeEncoding()))
{
foreach (var table in form.Tables.Where(x => x.Check).ToList())
{
if (ConnectionTest(form))
{
var data = func(table);
data.TableName = table.TableName;
var dataKeys = data.Columns;
if (data != null)
{
sw.Write($@"
/*[{table.TableCatalog}].[{table.TableSchema}].[{table.TableName}] */
SET IDENTITY_INSERT [{table.TableSchema}].[{table.TableName}] ON
Go
");
foreach (var dic in data.TranDictionarys())
{
sw.Write(GenMsSqlInsertScript($"[{table.TableSchema}].[{table.TableName}]", dic));
}
sw.Write($@"
SET IDENTITY_INSERT [{table.TableSchema}].[{table.TableName}] OFF
Go" + "\n\n");
}
}
}
sw.Flush();
stream.Seek(0, SeekOrigin.Begin);
return(stream);
}
}
public MemoryStream ExportExcel(StepDataModel form, Func <TableModel, DataTable> func)
{
MemoryStream stream = null;
//string filename = $"{form.DbName}-{DateTime.Now.ToString()}";
using (XLWorkbook wb = new XLWorkbook())
{
foreach (var table in form.Tables.Where(x => x.Check).ToList())
{
if (ConnectionTest(form))
{
var data = func(table);
data.TableName = table.TableName;
data.ClearField(3000);
if (data != null)
{
try
{
wb.Worksheets.Add(data);
wb.Worksheets.Worksheet(data.TableName).Columns().AdjustToContents();
}
catch (Exception ex)
{
Console.WriteLine("ERROR:" + ex.Message);
}
}
}
}
using (stream = new MemoryStream())
{
wb.SaveAs(stream);
}
}
return(stream);
}
public IActionResult Post([FromBody] StepDataModel form)
{
//https://www.c-sharpcorner.com/article/export-and-import-excel-file-using-closedxml-in-asp-net-mvc/
var result = _dbService.GetTableDataToExcel(form);
return(File(result.ToArray(),
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
private MemoryStream ExportEntityClass(StepDataModel form)
{
var stream = new MemoryStream();
using (var sw = new StreamWriter(stream, new UnicodeEncoding()))
{
foreach (var table in form.Tables.Where(x => x.Check).ToList())
{
if (ConnectionTest(form))
{
sw.Write(GenEntityClass(table));
}
}
}
return(stream);
}
public IActionResult Post([FromBody] StepDataModel form)
{
MemoryStream result = _dbService.GetTableDataToEntityClass(form);
return(File(result.ToArray(), "text/plain"));
}
public JsonResult Post([FromBody] StepDataModel form)
{
var result = _dbService.GetTables(form);
return(new JsonResult(result));
}
public MemoryStream GetTableDataToInsert(StepDataModel form)
{
return(ExportInsert(form, x => _dbUtil.GetDataTable($"SELECT * FROM [{x.TableSchema}].[{x.TableName}]")));
}
public MemoryStream GetTableDataToEntityClass(StepDataModel form)
{
return(ExportEntityClass(form));
}