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