public static List <DataTableInfo> Execute(string sqlText) { DataTable dt; List <DataTableInfo> infoList = new List <DataTableInfo>(); foreach (string sql in GetSQL(sqlText)) { if (sql.Trim() != string.Empty) { if (sql.Trim().ToUpper().StartsWith("SELECT")) { dt = DBUtils.GetDataSet(_source, sql, null).Tables[0].Copy(); } else { dt = DBUtils.ExecuteNonQuery(_source, sql, null).Tables[0].Copy(); } dt.TableName = Guid.NewGuid().ToString(); _output.Tables.Add(dt); DataTableInfo info = new DataTableInfo(); info.Name = dt.TableName; info.SQL = sql.TrimStart(new char[1] { crChar }); info.numRows = dt.Rows.Count; infoList.Add(info); } } return(infoList); }
private void UpdateDiagram(ObservableCollection <DataTableInfo> _tables) { List <EntityEx> obcollection = new List <EntityEx>(); foreach (var table in _tables) { DataTableInfo dtInfo = table as DataTableInfo; if (dtInfo != null) { List <string> subNodes = dtInfo.Rows.Select(di => di.DataRow["ClassName"].ToString()).Distinct().ToList <string>(); if (subNodes.Count() > 0) { obcollection.Add(new EntityEx() { Key = dtInfo.TableName, RootName = String.Empty, Items = subNodes.Select(clsName => new Attribute(clsName.ToString(), false, NodeFigure.Decision, "Yellow")).ToArray() }); foreach (string clsname in subNodes) { obcollection.Add(new EntityEx() { Key = clsname, RootName = dtInfo.TableName, Items = dtInfo.Rows.Where(di => di.DataRow["ClassName"].ToString().Equals(clsname)).Select(c => new Attribute(c.DataRow["AttributeName"].ToString(), true, NodeFigure.Decision, "Yellow")).ToArray() }); } } } } MainDataCollection = obcollection; }
private void UpdateDiagram(ObservableCollection <DataTableInfo> _tables) { var model = new GraphLinksModel <Entity, String, String, Relationship>(); ObservableCollection <Entity> obcollection = new ObservableCollection <Entity>(); foreach (var table in _tables) { DataTableInfo dtInfo = table as DataTableInfo; if (dtInfo != null) { obcollection.Add(new Entity() { Key = dtInfo.TableName, Items = dtInfo.Rows.Select(c => new Attribute(c.DataRow["AttributeName"].ToString(), true, NodeFigure.Decision, "Yellow")).ToArray() }); } } model.NodesSource = obcollection; myDiagram.InitialCenteredNodeData = model.NodesSource.OfType <Entity>().First(); // model.LinksSource = new ObservableCollection<Relationship>() { // new Relationship() { From="Products", To="Suppliers", Text="0..N", ToText="1" }, // new Relationship() { From="Products", To="Categories", Text="0..N", ToText="1" }, // new Relationship() { From="Order Details", To="Products", Text="0..N", ToText="1" }, // new Relationship() { From="Order Details", To="Person", Text="0..N", ToText="1" } //}; model.HasUndoManager = true; myDiagram.Model = model; }
private void CreateDataSheet(ExcelHelp xls, DataTableInfo tableInfo, bool isTarget) { DatabaseAcsesser dba = new DatabaseAcsesser(); DatabaseAcsesser.DbConnections connType = isTarget ? DatabaseAcsesser.DbConnections.TargetDbConnection : DatabaseAcsesser.DbConnections.SourceDbConnection; TableLayoutInfo info = dba.GetTableLayout(connType, tableInfo.TableName); Excel.Worksheet templateSheet = xls.WorkBook.Sheets["Template"]; Excel.Worksheet sheet = xls.CreateSheet(tableInfo.SheetName, templateSheet); int colIndex = 0; xls.WriteValue(sheet, 1, 2, info.TableName); foreach (ColumnInfo column in info.Columns) { colIndex++; xls.WriteValue(sheet, STRAT_ROW, colIndex, column.ColumnName); if (!string.IsNullOrEmpty(column.DisplayName)) { string comment = column.DisplayName + "\n" + column.GetSqlType(); sheet.Range[xls.GetColumnName(colIndex, STRAT_ROW)].AddComment(comment); } if (column.IsPrimaryKey) { Excel.Range cell = sheet.Range[xls.GetColumnName(colIndex, STRAT_ROW)]; cell.Font.Color = Excel.XlRgbColor.rgbRed; } } xls.AddListObject(sheet, STRAT_ROW, STRAT_ROW + 1, colIndex, tableInfo.TableName); SetColumnFormat(info, sheet); sheet.Columns.AutoFit(); sheet.Tab.Color = isTarget ? Excel.XlRgbColor.rgbBlue : Excel.XlRgbColor.rgbRed; }
private void CreateDataSheet(ExcelHelp xls, DataTableInfo tableInfo) { DenshowDataAccesser dba = new DenshowDataAccesser(); NpgTableLayoutInfo info = dba.GetTableLayout(tableInfo.TableName); Excel.Worksheet templateSheet = xls.WorkBook.Sheets["Template"]; Excel.Worksheet sheet = xls.CreateSheet(tableInfo.SheetName, templateSheet); int colIndex = 0; xls.WriteValue(sheet, 1, 2, info.TableName); foreach (NpgColumnInfo column in info.Columns) { colIndex++; xls.WriteValue(sheet, STRAT_ROW, colIndex, column.ColumnName); if (!string.IsNullOrEmpty(column.DisplayName)) { string comment = column.DisplayName + "\n" + column.GetSqlType(); sheet.Range[xls.GetColumnName(colIndex, STRAT_ROW)].AddComment(comment); } else { string comment = column.GetSqlType(); sheet.Range[xls.GetColumnName(colIndex, STRAT_ROW)].AddComment(comment); } if (column.IsPrimaryKey) { Excel.Range cell = sheet.Range[xls.GetColumnName(colIndex, STRAT_ROW)]; cell.Font.Color = Excel.XlRgbColor.rgbRed; } } xls.AddListObject(sheet, STRAT_ROW, STRAT_ROW + 1, colIndex, tableInfo.TableName); SetColumnFormat(info, sheet); sheet.Columns.AutoFit(); }
public static DataSetData FromDataSet(DataSet ds) { DataSetData dsd = new DataSetData(); dsd.Tables = new ObservableCollection <DataTableInfo>(); foreach (DataTable t in ds.Tables) { DataTableInfo tableInfo = new DataTableInfo { TableName = t.TableName }; dsd.Tables.Add(tableInfo); tableInfo.Columns = new ObservableCollection <DataColumnInfo>(); foreach (DataColumn c in t.Columns) { DataColumnInfo col = new DataColumnInfo { ColumnName = c.ColumnName, ColumnTitle = c.ColumnName, DataTypeName = c.DataType.FullName, MaxLength = c.MaxLength, IsKey = c.Unique, IsReadOnly = (c.Unique || c.ReadOnly), IsRequired = !c.AllowDBNull }; if (c.DataType == typeof(System.Guid)) { col.IsReadOnly = true; col.DisplayIndex = -1; } tableInfo.Columns.Add(col); } } dsd.DataXML = ds.GetXml(); return(dsd); }
private void MaskDataColumnNames(dynamic data) { // flatten data object in of an array if (data.GetType() == typeof(JArray)) { data = data[0]; } // find merge fields ribbon menu button RibbonMenuButton ctlInsertMergeFields = ribbonReportingTab1.FindItem( RibbonReportingTab.RibbonItem.TXITEM_InsertMergeField) as RibbonMenuButton; // get the selected master table info DataTableInfo dataTableInfo = ribbonReportingTab1.DataSourceManager.MasterDataTableInfo; // select token in data object if (dataTableInfo.TableName != "RootTable") { data = data.SelectToken("$.." + dataTableInfo.TableName); } // change the strings ApplyMaskedString(ctlInsertMergeFields.DropDownItems, data); }
private string CreateSheetDataSql(DataTableInfo tInfo, DataTable dtt, bool isIdInsert) { NpgTableLayoutInfo tinfo = GetTableLayoutInfo(tInfo.TableName); StringBuilder sbSql = new StringBuilder(); sbSql.AppendFormat("/****【{0}】({1}) ****/", tInfo.DisplayName, tInfo.TableName); sbSql.AppendLine(); bool hasIdColumn = tinfo.Columns.Any(col => col.IsIdentity); if (isIdInsert && hasIdColumn) { //sbSql.AppendFormat("SET IDENTITY_INSERT [{0}] ON", tinfo.TableName); } foreach (DataRow row in dtt.Rows) { sbSql.AppendLine(tinfo.GetInsertScript(row, true)); } //if (isIdInsert && hasIdColumn) //{ // sbSql.AppendFormat("SET IDENTITY_INSERT [{0}] OFF", tinfo.TableName); // sbSql.AppendLine("GO"); //} return(sbSql.ToString()); }
public void CheckDatas(string filePath, string exportFolder) { base.Report("チェックテーブル一覧を取得しています"); if (string.IsNullOrEmpty(exportFolder) || !System.IO.Directory.Exists(exportFolder)) { exportFolder = Config.ReportExportFolder; } using (this.xlsReport = new ExcelHelp(Config.ReportTemplateFile)) { this.currentRow = STRAT_ROW; using (ExcelAccessor xlsAdo = new ExcelAccessor(filePath)) { DataTable dtt = GetCheckTableList(xlsAdo); base.SetStep(dtt.Rows.Count, "対象テーブルをチェックしています"); foreach (DataRow row in dtt.Rows) { DataTableInfo tabInfo = new DataTableInfo(row); CheckExpectData(xlsAdo, tabInfo); base.ReportStep("{0}\n{1}", tabInfo.DisplayName, tabInfo.TableName); } } base.Report("チェック結果報告を出力しています。"); //枠設定 Excel.Worksheet sheet = this.xlsReport.WorkBook.Sheets[REPORT_SHEET_NAME]; this.xlsReport.AddListObject(sheet, 2, currentRow, 5, "ERRORLIST"); //報告ファイルを保存する string reportFile = "DataCheckReport_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xlsx"; reportFile = System.IO.Path.Combine(exportFolder, reportFile); this.xlsReport.Save(reportFile); } }
public static DataTableResponseInfo <T> FromDataTableInfo(DataTableInfo <T> dataTableInfo) { return(new() { PagingInfo = dataTableInfo.PagingInfo, SummaryInfo = dataTableInfo.SummaryInfo }); }
protected IQueryable <TEntity> FilterData(IQueryable <TEntity> query, DataTableInfo <TEntitySummary> dataTable) { if (string.IsNullOrEmpty(dataTable.FilteringInfo.Query)) { return(query); } return(query.Where(dataTable.FilteringInfo.Query, dataTable.FilteringInfo.Parameters.ToArray())); }
protected IQueryable <TEntity> SortData(IQueryable <TEntity> query, DataTableInfo <TEntitySummary> dataTable) { if (string.IsNullOrEmpty(dataTable.SortByExpression)) { return(query); } return(query.OrderBy(dataTable.SortByExpression)); }
/// <summary> /// 填充加载表格失败事件 /// </summary> /// <param name="dataTableAssetName">数据表资源名称</param> /// <param name="errorMessage">错误信息</param> /// <param name="userData">用户自定义数据</param> /// <returns>加载数据表成功事件</returns> public LoadDataTableFailureGameEvent Fill(string dataTableAssetName, string errorMessage, object userData) { DataTableInfo loadDataTableInfo = (DataTableInfo)userData; UserData = loadDataTableInfo.UserData; DataTableName = loadDataTableInfo.DataTableName; DataTableAssetName = dataTableAssetName; ErrorMessage = errorMessage; return(this); }
/// <summary> /// 填充加载表格更新事件 /// </summary> /// <param name="dataTableAssetName">数据表资源名称</param> /// <param name="progress">加载数据表进度</param> /// <param name="userData">用户自定义数据</param> /// <returns>加载数据表更新事件</returns> public LoadDataTableUpdateGameEvent Fill(string dataTableAssetName, float progress, object userData) { DataTableInfo loadDataTableInfo = (DataTableInfo)userData; UserData = loadDataTableInfo.UserData; DataTableName = loadDataTableInfo.DataTableName; DataTableAssetName = dataTableAssetName; Progress = progress; return(this); }
/// <summary> /// 填充加载数据表成功事件 /// </summary> /// <param name="dataTableAssetName">数据表资源名称</param> /// <param name="duration">加载持续时间</param> /// <param name="userData">用户自定义数据</param> /// <returns>加载数据表成功事件</returns> public LoadDataTableSuccessGameEvent Fill(string dataTableAssetName, float duration, object userData) { DataTableInfo loadDataTableInfo = (DataTableInfo)userData; UserData = loadDataTableInfo.UserData; DataTableName = loadDataTableInfo.DataTableName; DataTableAssetName = dataTableAssetName; Duration = duration; return(this); }
/// <summary> /// 加载数据表时加载依赖资源事件 /// </summary> /// <param name="dataTableAssetName">数据表资源名称</param> /// <param name="dependencyAssetName">被加载的依赖资源名称</param> /// <param name="loadedCount">当前已加载依赖资源数量</param> /// <param name="totalCount">总共加载依赖资源数量</param> /// <param name="userData">用户自定义数据</param> public LoadDataTableDependencyAssetGameEvent Fill(string dataTableAssetName, string dependencyAssetName, int loadedCount, int totalCount, object userData) { DataTableInfo loadDataTableInfo = (DataTableInfo)userData; UserData = loadDataTableInfo.UserData; DataTableName = loadDataTableInfo.DataTableName; DataTableAssetName = dataTableAssetName; DependencyAssetName = dependencyAssetName; LoadedCount = loadedCount; TotalCount = totalCount; return(this); }
public void CreateDBInitData(string[] sheetNames, bool isTarget) { DataKind kind = isTarget ? DataKind.LiveInitData : DataKind.SeedInitData; using (ExcelAccessor xlsAdo = new ExcelAccessor(Config.GetTransferSettingFile(isTarget))) { //テーブル一覧取得 foreach (string sheetName in sheetNames) { using (ExcelHelp xls = new ExcelHelp(Config.TemplateFile)) { xls.BeginUpdate(); //接続文字列設定 string connStr = isTarget ? Config.TargetDbConnection : Config.SourceDbConnection; string adoConnstr = ChangeConnectStringForADO(connStr); Excel.Worksheet settingSheet = xls.WorkBook.Sheets["設定"]; xls.WriteValue(settingSheet, 1, 2, adoConnstr); //目次を取得 Excel.Worksheet indexSheet = xls.WorkBook.Sheets["目次"]; int rowNo = 2; base.Report("テーブル一覧を取得しています"); DataTable tableList = GetSettingTableList(xlsAdo, sheetName); //移行元データシート作成 base.SetStep(tableList.Rows.Count, "データシートを作成しています"); foreach (DataRow row in tableList.Rows) { DataTableInfo tabInfo = new DataTableInfo(row); CreateDataSheet(xls, tabInfo, isTarget); //目次作成 xls.WriteValue(indexSheet, rowNo, 1, tabInfo.DisplayName); xls.WriteValue(indexSheet, rowNo, 2, tabInfo.TableName); xls.WriteValue(indexSheet, rowNo, 4, tabInfo.SheetName); //リンク追加 Excel.Range anchor = xls.GetRange(indexSheet, 1, 1, rowNo, rowNo); indexSheet.Hyperlinks.Add(Anchor: anchor, Address: "", SubAddress: tabInfo.TableName, TextToDisplay: tabInfo.DisplayName); rowNo++; base.ReportStep("{0}\n{1}", tabInfo.DisplayName, tabInfo.TableName); } xls.AddListObject(indexSheet, 1, rowNo, 4, "Index"); indexSheet.Columns.AutoFit(); indexSheet.Columns["D:D"].EntireColumn.Hidden = true; indexSheet.Select(); base.Report("「{0}」のデータシートを保存しています", sheetName); xls.EndUpdate(); xls.Save(Config.GetDataSheetSavePath(sheetName), Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled); } } } }
public static IEnumerable GetDataList(DataSetData data) { if (data == null) { return(null); } if (data.Tables.Count == 0) { return(null); } DataTableInfo tableInfo = data.Tables[0]; System.Type dataType = BuildDataObjectType(tableInfo.Columns, "MyDataObject"); //ObservableCollection<DataObject> l = new ObservableCollection<DataObject>(); var listType = typeof(ObservableCollection <>).MakeGenericType(new[] { dataType }); var list = Activator.CreateInstance(listType); XDocument xd = XDocument.Parse(data.DataXML); var table = from row in xd.Descendants(tableInfo.TableName) select row.Elements().ToDictionary(r => r.Name, r => r.Value); foreach (var r in table) { var rowData = Activator.CreateInstance(dataType) as CustomerDataObject; if (rowData != null) { foreach (DataColumnInfo col in tableInfo.Columns) { if (r.ContainsKey(col.ColumnName) && col.DataTypeName != typeof(System.Byte[]).FullName && col.DataTypeName != typeof(System.Guid).FullName) { if (col.IsEncrypt) { rowData.SetFieldValue(col.ColumnName, SMT.SaaS.FrameworkUI.Common.Utility.AESDecrypt(r[col.ColumnName]), true); } else { rowData.SetFieldValue(col.ColumnName, r[col.ColumnName], true); } } } } listType.GetMethod("Add").Invoke(list, new[] { rowData }); } ObservableCollection <CustomerDataObject> l = list as ObservableCollection <CustomerDataObject>; return(list as IEnumerable); }
public void ShouldUpdateStatusBarWithRowCountWhenTabIsOpened() { var selectedTableName = "Customers"; var selectedTableInfo = new DataTableInfo { NumberOfRows = 12 }; var expectedStatusLabel = String.Format("Rows: {0}", selectedTableInfo.NumberOfRows); datasetProvider.Stub(d => d.GetTableInfo(selectedTableName)).Return(selectedTableInfo); var presenter = new DataEditorPresenter(applicationController, view, null, null, null, null, datasetProvider, null); RaiseTabSelected("Customers"); Assert.AreEqual(expectedStatusLabel, view.StatusLabel); }
/// <summary> /// 转换实体 /// </summary> /// <param name="row">数据行</param> /// <returns></returns> private DataTableInfo DataRowToModel(DataRow row) { var model = new DataTableInfo(); if (row != null) { if (!row["Name"].IsNullOrEmpty()) { model.Name = row["Name"].ToStringValue(); } if (!row["Description"].IsNullOrEmpty()) { model.Description = row["Description"].ToStringValue(); } } return(model); }
/// <summary> /// データ初期化する /// </summary> /// <param name="filePath"></param> /// <param name="isTarget"></param> public void DoInitlize(string filePath, string batchFile) { string outPutPath = System.IO.Path.ChangeExtension(filePath, "sql"); Logging.OutputFileName = outPutPath; try { base.Report("テーブル一覧を取得しています"); using (ExcelAccessor xlsAdo = new ExcelAccessor(filePath)) { DataTable tableList = GetSheetTableDatas(xlsAdo, "目次"); base.SetStep(tableList.Rows.Count, "データクリア用SQL文を作成しています。"); Logging.WriteLine("/*** データクリア ***/"); DataTableInfo tableInfo = null; Logging.WriteLine("set search_path to " + Config.DenshowDbSchema + " ;"); foreach (DataRow row in tableList.Rows) { tableInfo = new DataTableInfo(row); string sql = string.Format("TRUNCATE TABLE \"{0}\" CASCADE;", tableInfo.TableName); Logging.WriteLine(sql); base.ReportStep("{0}\n{1}", tableInfo.DisplayName, tableInfo.TableName); } base.SetStep(tableList.Rows.Count, "インサートSQL文を作成しています。"); foreach (DataRow row in tableList.Rows) { tableInfo = new DataTableInfo(row); DataTable dtt = xlsAdo.GetTableData(tableInfo.SheetName, tableInfo.TableName, null, 2, Config.MaxDataCount + 2); string sql = CreateSheetDataSql(tableInfo, dtt, true); Logging.WriteLine(sql); base.ReportStep("{0}\n{1}", tableInfo.DisplayName, tableInfo.TableName); } } string connStr = Config.DenshowDbConnection; //Batファイル作成する //string batFilePath = System.IO.Path.ChangeExtension(filePath, "bat"); //string cmdSqlBat = GetSqlCmdLine(connStr,batchFile); //System.IO.File.WriteAllText(batFilePath, cmdSqlBat, System.Text.Encoding.Default); base.Report("SQL文を作成しました。"); } finally { Logging.OutputFileName = ""; } }
protected async Task <List <TEntityLookupModel> > GetResult(IQueryable <TEntity> query, DataTableInfo <TEntitySummary> dataTable, CancellationToken cancellationToken) { if (dataTable.PagingInfo.PagingEnabled) { query = query .Skip((dataTable.PagingInfo.PageIndex - 1) * dataTable.PagingInfo.PageSize) .Take(dataTable.PagingInfo.PageSize); } var list = await query .ProjectTo <TEntityLookupModel>(Mapper.ConfigurationProvider) .ToListAsync(cancellationToken); dataTable.PagingInfo.TotalItems = list.Count(); return(list); }
private void CheckExpectData(ExcelAccessor xlsAdo, DataTableInfo info) { try { DatabaseAcsesser dbAdo = new DatabaseAcsesser(); DataTable dttResult = dbAdo.GetResultData(info.TableName); DataTable dttSchema = dttResult.Clone(); DataTable dttExpect = xlsAdo.GetTableData(info.SheetName, info.TableName, dttSchema, 2, Config.MaxDataCount + 2); //データ比較する DiffDataSouce(info, dttExpect, dttResult); } catch (Exception ex) { Logging.Exception("", ex); throw ex; } }
/// <summary> /// Constructs the source. /// </summary> /// <param name="caseInsensitive"></param> /// <param name="tableQuery"></param> /// <param name="uniqueName"></param> /// <param name="givenName"></param> /// <param name="rootName"></param> public FromTableDirectSource(bool caseInsensitive, ITableQueryInfo tableQuery, string uniqueName, ObjectName givenName, ObjectName rootName) { this.uniqueName = uniqueName; dataTableInfo = tableQuery.TableInfo; RootTableName = rootName; if (givenName != null) { GivenTableName = givenName; } else { GivenTableName = rootName; } // Is the database case insensitive? this.caseInsensitive = caseInsensitive; this.tableQuery = tableQuery; }
public void CreateDBScript(TableCreateInfo info) { string outPutPath = System.IO.Path.ChangeExtension(info.LayoutFileName, "sql"); Logging.OutputFileName = outPutPath; try { base.Report(Resource.StringTable.Messages.OpenDesignFile); using (ExcelHelp xls = new ExcelHelp(info.LayoutFileName)) { //int sheectCount = xls.WorkBook.Sheets.Count; base.Report(Resource.StringTable.Messages.ReadingTableList); System.Data.DataTable dttList = GetTableList(info.TableListSheetName); base.SetStep(dttList.Rows.Count, Resource.StringTable.Messages.CreatingSqlScript); foreach (System.Data.DataRow row in dttList.Rows) { DataTableInfo tbInfo = new DataTableInfo(row); try { TableLayoutInfo tableInfo = ReadTableLayout(xls, tbInfo.SheetName, info.LayoutKind); //Script出力 CreateSqlScript(info.Options, tableInfo); ReportStep(Resource.StringTable.Messages.CreatedTableSqlScript, tableInfo.DisplayName, tableInfo.TableName); } catch (Exception ex) { Logging.WriteLine("/*"); Logging.WriteLine(Resource.StringTable.Messages.ScriptCreateFailed, tbInfo.SheetName, tbInfo.TableName); Logging.Exception("", ex); Logging.WriteLine("*/"); } } Report(Resource.StringTable.Messages.ProcessFinished); xls.Close(); } } finally { Logging.OutputFileName = ""; } }
public static DataSetData FromDataSet(DataSet ds) { DataSetData dsd = new DataSetData(); dsd.Tables = new ObservableCollection<DataTableInfo>(); foreach (DataTable t in ds.Tables) { DataTableInfo tableInfo = new DataTableInfo {TableName = t.TableName}; dsd.Tables.Add(tableInfo); tableInfo.Columns = new ObservableCollection<DataColumnInfo>(); foreach (DataColumn c in t.Columns) { DataColumnInfo col = new DataColumnInfo { ColumnName = c.ColumnName, ColumnTitle = c.ColumnName, DataTypeName = c.DataType.FullName, MaxLength=c.MaxLength, IsKey=c.Unique, IsReadOnly=(c.Unique || c.ReadOnly), IsRequired = !c.AllowDBNull}; if (c.DataType == typeof(System.Guid)) { col.IsReadOnly = true; col.DisplayIndex = -1; } tableInfo.Columns.Add(col); } } dsd.DataXML = ds.GetXml(); return dsd; }
public void CreateDBScript(TableCreateInfo info) { string outPutPath = System.IO.Path.ChangeExtension(info.LayoutFileName, "sql"); Logging.OutputFileName = outPutPath; try { using (ExcelHelp xls = new ExcelHelp(info.LayoutFileName)) { //int sheectCount = xls.WorkBook.Sheets.Count; System.Data.DataTable dttList = GetTableList(xls, info.TemplateInfo); foreach (System.Data.DataRow row in dttList.Rows) { DataTableInfo tbInfo = new DataTableInfo(row); try { TableLayout tableInfo = ReadTableLayout(xls, tbInfo, info.TemplateInfo); //Script出力 CreateSqlScript(info.Options, tableInfo); } catch (Exception ex) { Logging.WriteLine("/*"); Logging.WriteLine(Resources.StringTable.ScriptCreateFailed, tbInfo.SheetName, tbInfo.TableName); Logging.Exception("", ex); Logging.WriteLine("*/"); } } xls.Close(); } } finally { Logging.OutputFileName = ""; } }
private TableLayout ReadTableLayout(ExcelHelp xls, DataTableInfo tabInfo, TemplateInfo.DocumentTemplateRow LiveLayoutInfo) { Excel.Worksheet sheet = xls.WorkBook.Sheets[tabInfo.SheetName]; string tableName = Utility.DBToString(sheet.Range[LiveLayoutInfo.TableName].Value); TableLayout tableInfo = new TableLayout(tableName) { DisplayName = sheet.Range[LiveLayoutInfo.TableDisplayName].Value, Comment = tabInfo.Comment }; //列作成 int rowIndex = LiveLayoutInfo.ColumnStartRow; string No = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnNo + rowIndex].Value); int columnId = 0; while (!string.IsNullOrEmpty(No) && int.TryParse(No, out columnId)) { //ColumnName ColumnInfo column = new ColumnInfo(); column.ColumnId = columnId; column.ColumnName = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnName + rowIndex].Value).Trim(); column.DisplayName = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnDisplayName + rowIndex].Value).Trim(); column.DataType = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnDataType + rowIndex].Value).Trim(); //Length string lenVal = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnDataLength + rowIndex].Value).Trim(); if (!string.IsNullOrWhiteSpace(lenVal)) { int length = 0; if (int.TryParse(lenVal, out length)) { column.Length = length; } else if (lenVal.Contains(",")) { int num = 0; string[] sect = lenVal.Split(','); if (int.TryParse(sect[0], out num)) { column.NumericPrecision = num; } if (int.TryParse(sect[1], out num)) { column.NumericScale = num; } } else { if (lenVal.ToLower().Equals("max")) { column.Length = -1; } } } //Nullable string nullable = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnNullable + rowIndex].Value); column.Nullable = (!string.IsNullOrWhiteSpace(nullable)); //InPrimary string InPrimary = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnIsPrimaryKey + rowIndex].Value); column.IsPrimaryKey = (!string.IsNullOrWhiteSpace(InPrimary)); //Index Key string indexNo = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnIndex + rowIndex].Value); int indexId = 0; if (!string.IsNullOrWhiteSpace(indexNo) && int.TryParse(indexNo, out indexId)) { column.IndexColumnId = indexId; } if (!string.IsNullOrWhiteSpace(column.ColumnName) && !string.IsNullOrWhiteSpace(column.DataType)) { tableInfo.Columns.Add(column); } else { Logging.WriteLine("-- ERROR:: {0}-{1} 行:{2} {3}", tabInfo.SheetName, tableInfo.TableName, No, string.IsNullOrWhiteSpace(column.ColumnName) ? "列名なし" : "型なし"); } //Comment column.Comment = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnComment + rowIndex].Value).Trim(); rowIndex++; No = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnNo + rowIndex].Value); } return(tableInfo); }
public abstract Task <List <TEntityLookupModel> > RequestData( IQueryable <TEntity> query, DataTableInfo <TEntitySummary> dataTable, CancellationToken cancellationToken);
private void DiffDataSouce(DataTableInfo info, DataTable dttExpect, DataTable dttResult) { int diffCount = 0; int addedCount = 0; int losedCount = 0; //変換処理定義より、データを変換する ChangeDataByCheckPattern(info.TableName, dttExpect, true); ChangeDataByCheckPattern(info.TableName, dttResult, false); //期待より多い行および差異あり行を抽出 DataTable dttCompare = dttExpect.Copy(); DataTable dttTarget = dttResult.Copy(); foreach (DataRow row in dttTarget.Rows) { row.SetAdded(); } //差異あり行を抽出 dttCompare.Merge(dttTarget, false); var changesRows = dttCompare.AsEnumerable().Where(row => { return(row.RowState == DataRowState.Modified && IsModified(row)); }); diffCount = changesRows.Count(); //期待よりたり多い行を抽出 var addedRows = dttCompare.AsEnumerable().Where(row => row.RowState == DataRowState.Added); addedCount = addedRows.Count(); //期待よりたりない行を抽出 dttCompare = dttResult.Copy(); dttTarget = dttExpect.Copy(); foreach (DataRow row in dttTarget.Rows) { row.SetAdded(); } dttCompare.Merge(dttTarget, false); var losedRows = dttCompare.AsEnumerable().Where(row => row.RowState == DataRowState.Added); losedCount = losedRows.Count(); //報告 Excel.Worksheet sheet = this.xlsReport.WorkBook.Sheets[REPORT_SHEET_NAME]; //テーブル名 this.xlsReport.WriteValue(sheet, this.currentRow, 1, info.DisplayName); //物理名 this.xlsReport.WriteValue(sheet, this.currentRow, 2, info.TableName); //期待より多い件数 this.xlsReport.WriteValue(sheet, this.currentRow, 3, addedCount.ToString()); //期待より少ない件数 this.xlsReport.WriteValue(sheet, this.currentRow, 4, losedCount.ToString()); //差異あり this.xlsReport.WriteValue(sheet, this.currentRow, 5, diffCount.ToString()); this.currentRow++; //報告詳細 if (addedCount == 0 && losedCount == 0 && diffCount == 0) { return; } Excel.Worksheet detailSheet = this.xlsReport.CreateSheet(info.DisplayName); int colIndex = 1; int rowIndex = 1; int dataCount = 0; //テーブルヘッダ this.xlsReport.WriteValue(detailSheet, rowIndex, colIndex, "差異"); foreach (DataColumn column in dttExpect.Columns) { colIndex++; this.xlsReport.WriteValue(detailSheet, rowIndex, colIndex, column.ColumnName); } //差異あり行 dataCount = 0; foreach (DataRow row in changesRows) { rowIndex++; dataCount++; if (dataCount > 100) { this.xlsReport.WriteValue(detailSheet, rowIndex, 1, "件数多過ぎ..."); break; } this.xlsReport.WriteValue(detailSheet, rowIndex, 1, "差異あり"); //差異行出力 WriteDiffRow(detailSheet, row, 2, rowIndex); } //期待より多い行 dataCount = 0; foreach (DataRow row in addedRows) { rowIndex++; dataCount++; if (dataCount > 100) { this.xlsReport.WriteValue(detailSheet, rowIndex, 1, "件数多過ぎ..."); break; } this.xlsReport.WriteValue(detailSheet, rowIndex, 1, "期待より多い"); //差異行出力 xlsReport.WriteValues(detailSheet, row, rowIndex, 2); } //期待よりたりない行 dataCount = 0; foreach (DataRow row in losedRows) { rowIndex++; dataCount++; if (dataCount > 100) { this.xlsReport.WriteValue(detailSheet, rowIndex, 1, "件数多過ぎ..."); break; } this.xlsReport.WriteValue(detailSheet, rowIndex, 1, "期待より足りない"); //差異行出力 this.xlsReport.WriteValues(detailSheet, row, rowIndex, 2); } //リンク追加 Excel.Range anchor = this.xlsReport.GetRange(sheet, 1, 1, this.currentRow - 1, this.currentRow - 1); sheet.Hyperlinks.Add(Anchor: anchor, Address: "", SubAddress: info.TableName, TextToDisplay: info.DisplayName); this.xlsReport.AddListObject(detailSheet, 1, rowIndex, colIndex, info.TableName); //フォーマット設定 SetColumnFormat(dttResult, detailSheet); }