public void CreateSchemaAndExportData(ExtractDataSetMappingModel mapping, DataTable dataTable)
{
//创建行转列的表
_logger.LogInformation("创建行转列的表");
CreateSchema(mapping.DataSetId, dataTable);
//插入DF_Field表
_logger.LogInformation("插入DF_Field表");
InsertDF(mapping.DataSetId, mapping.TableId, dataTable);
//插入行转列的数据
_logger.LogInformation("插入行转列的数据");
BulkInsertEngineData(dataTable);
//更新分析数据
_logger.LogInformation("更新分析数据");
//这个地方必须要从数据库里面取，之前的datatable column 没有具体类型，全是string
var dt = _dbResearch.Ado.GetDataTable($"select * from dbo.[{dataTable.TableName}]");
GenerateColumnData(dt, mapping.ResultDataId);
}
public int ExtractEngineDataForService(string projectTypeStr, string projectName, string userName, DataTable dtFormat)
{
var extractDataSetMappingModel = new ExtractDataSetMappingModel();
try
{
_logger.LogInformation($"projectTypeStr:{projectTypeStr},projectName:{projectName},userName:{userName}");
string tableName = Guid.NewGuid().ToString("N");
string datasetName = projectName + "_" + DateTime.Now.ToString("yyyyMMddhhmmssfff");
_logger.LogInformation("开始在sql server创建mapping关系");
//ms sql server 创建dataset table_config的mapping关系
var createMappingSql = GetSqlText("ExtractEngineData-CreateMappingForService.sql");
extractDataSetMappingModel = _dbKyStatic.Ado.SqlQuerySingle <ExtractDataSetMappingModel>(createMappingSql, new List <SugarParameter>()
{
new SugarParameter("@projectTypeStr", projectTypeStr),
new SugarParameter("@projectName", projectName),
new SugarParameter("@userName", userName),
new SugarParameter("@tableName", tableName),
new SugarParameter("@datasetName", datasetName),
new SugarParameter("@dataCount", dtFormat.Rows.Count)
});
_logger.LogInformation("开始导入数据");
LegacyCodeHandler legacyCodeHandler = new LegacyCodeHandler(_legacyCodeHandler);
//创建数据表 将所有列插入到data_field 表
dtFormat.TableName = tableName;
legacyCodeHandler.CreateSchemaAndExportData(extractDataSetMappingModel, dtFormat);
_logger.LogInformation($"抽取结束datasetId:{extractDataSetMappingModel.DataSetId},tableId:{extractDataSetMappingModel.TableId}");
}
catch (Exception ex)
{
_logger.LogError("接口导入到sql server失败：" + ex.ToString());
DeleteDataSet(extractDataSetMappingModel.DataSetId);
extractDataSetMappingModel.DataSetId = 0;
}
return(extractDataSetMappingModel.DataSetId);
}
public void ExtractEngineData(int exportDataId, int projectId, string userName)
{
var extractDataSetMappingModel = new ExtractDataSetMappingModel();
try
{
_logger.LogInformation($"开始抽取export_id:{exportDataId},projectId:{projectId},userName:{userName}");
string tableName = Guid.NewGuid().ToString("N");
var engineData = _dbMySql.Queryable <SearchEngineDataModel>().Where(x => x.Id == exportDataId).First();
var extractDataListSql = string.Format(GetSqlText("ExtractEngineData-GetExportDataList.sql"), engineData.DataType, exportDataId);
//获取原始数据
DataTable dtSource = _dbMySql.Ado.GetDataTable(extractDataListSql);
_logger.LogInformation("开始数据表格式处理");
LegacyCodeHandler legacyCodeHandler = new LegacyCodeHandler(_legacyCodeHandler);
//数据转成表格
DataTable dtFormat = legacyCodeHandler.ConvertToStandardTable(dtSource, engineData.DataType);
dtFormat.TableName = tableName;
_logger.LogInformation("开始在sql server创建mapping关系");
//ms sql server 创建dataset table_config的mapping关系
var createMappingSql = string.Format(GetSqlText("ExtractEngineData-CreateMapping.sql"), engineData.DiseaseId, engineData.DataName, engineData.Id, userName, tableName, projectId, engineData.DataType, dtFormat.Rows.Count);
extractDataSetMappingModel = _dbKyStatic.Ado.SqlQuerySingle <ExtractDataSetMappingModel>(createMappingSql);
_logger.LogInformation("开始导入数据");
//创建数据表 将所有列插入到data_field 表
legacyCodeHandler.CreateSchemaAndExportData(extractDataSetMappingModel, dtFormat);
_logger.LogInformation($"抽取结束datasetId:{extractDataSetMappingModel.DataSetId},tableId:{extractDataSetMappingModel.TableId}");
}
catch (Exception ex)
{
_logger.LogError("搜索引擎数据导入到sql server失败：" + ex.ToString());
DeleteDataSet(extractDataSetMappingModel.DataSetId);
}
}
