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