private void button1_Click(object sender, EventArgs e)
{
DMSTable curTable = tableList.SelectedItems[0].Tag as DMSTable;
RecordMetadataViewer viewer = new RecordMetadataViewer(curTable.Metadata);
viewer.ShowDialog(this);
}
public DataViewer(DMSTable table)
{
InitializeComponent();
viewerTable = table;
this.Text = "Data Viewer: " + table.DBName;
DrawDataTable();
IsRunningMono = Type.GetType("Mono.Runtime") != null;
}
public DataViewer(DMSTable table, string ConnectedDBName)
{
InitializeComponent();
viewerTable = table;
this.Text = "Data Viewer: " + table.DBName;
if (table.CompareResult != DMSCompareResult.SAME && ConnectedDBName.Length > 0)
{
this.Text += " - " + ConnectedDBName;
}
DrawDataTable();
IsRunningMono = Type.GetType("Mono.Runtime") != null;
}
private void EditField_Click(object sender, EventArgs e)
{
var menuItem = (MenuItem)sender;
var hitTest = (ListViewHitTestInfo)menuItem.Tag;
var column = (DMSColumn)hitTest.Item.Tag;
DMSTable curTable = tableList.SelectedItems[0].Tag as DMSTable;
var columnMetadata = curTable.Metadata.FieldMetadata.Where(p => p.FieldName == column.Name).First();
FieldMetadataViewer viewer = new FieldMetadataViewer(column, columnMetadata);
viewer.ShowDialog(this);
DrawColumns();
}
public DataViewer(DMSTable table, string ConnectedDBName)
{
InitializeComponent();
typeof(DataGridView).InvokeMember("DoubleBuffered", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.SetProperty, null, this.dataGridView1, new object[] { true });
viewerTable = table;
this.Text = "Data Viewer: " + table.DBName;
if (table.CompareResult != DMSCompareResult.SAME && ConnectedDBName.Length > 0)
{
this.Text += " - " + ConnectedDBName;
}
InitDataTable();
//FillDataTable();
dataGridView1.RowCount = viewerTable.Rows.Count;
IsRunningMono = Type.GetType("Mono.Runtime") != null;
}
private DataTable BuildDataTable(DMSTable tbl)
{
DataTable dt = new DataTable();
foreach (var c in tbl.Columns)
{
var dc = new DataColumn(c.Name);
dt.Columns.Add(dc);
}
foreach (var r in tbl.Rows)
{
var dr = dt.NewRow();
var items = r.GetValuesAsString();
dr.ItemArray = items;
dt.Rows.Add(dr);
}
return(dt);
}
private void CompareRow(DMSTable table, List <DMSRecordFieldMetadata> keys, DMSRow curRow)
{
int[] keyIndexes = new int[keys.Count];
for (var x = 0; x < keys.Count; x++)
{
var fieldName = keys[x].FieldName;
var fieldIndex = table.Columns.FindIndex(c => c.Name == fieldName);
keyIndexes[x] = fieldIndex;
}
/* create SQL statement for this item */
/* SELECT 'Y' FROM DBNAME WHERE KEY1 = :1 */
var sqlBuilder = new StringBuilder($"SELECT 'Y' FROM {table.DBName} WHERE ");
for (var x = 0; x < keys.Count; x++)
{
if (x > 0)
{
sqlBuilder.Append(" AND ");
}
sqlBuilder.Append($"{keys[x].FieldName} = :{x + 1}");
}
/* DEBUG */
//using (var dbg = new OracleCommand("SELECT IS_DX_INPUT_VALUE FROM PS_IS_DX_USERINPUT WHERE ROWNUM = 1 AND dbms_lob.compare(IS_DX_INPUT_VALUE, :1) != 0", dbConn))
//using (var dbg = new OracleCommand("SELECT IS_DX_LAST_UPDT FROM PS_IS_DX_INSTANCE WHERE IS_DX_LAST_UPDT != :1 AND ROWNUM = 1", dbConn))
/*using (var dbg = new OracleCommand("SELECT CONTDATA FROM PSCONTENT WHERE ROWNUM = 1", dbConn))
* {
*
* using (var dbgRead = dbg.ExecuteReader())
* {
* dbgRead.Read();
* var fieldType = dbgRead.GetFieldType(0);
* var oracleBlob = dbgRead.GetOracleBlob(0);
* byte[] blobData = new byte[oracleBlob.Length];
* oracleBlob.Read(blobData, 0, blobData.Length);
* Console.WriteLine(BitConverter.ToString(blobData));
* }
*
* }*/
using (var cmd = new OracleCommand(sqlBuilder.ToString(), dbConn))
{
/* set bind parameters */
for (var x = 0; x < keys.Count; x++)
{
OracleParameter keyParam = new OracleParameter();
SetOracleParamValue(keyParam, keys[x].FieldType, curRow, keyIndexes[x]);
cmd.Parameters.Add(keyParam);
}
/* execute */
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
/* if a row came back, it exists... */
/* we need to check if the row that exists matches on each and every column */
StringBuilder diffCheck = new StringBuilder($"SELECT 'Y' FROM {table.DBName} WHERE ");
for (var x = 0; x < table.Metadata.FieldMetadata.Count; x++)
{
var column = table.Metadata.FieldMetadata[x];
if (column.FieldType == FieldTypes.LONG_CHAR ||
column.FieldType == FieldTypes.IMG_OR_ATTACH)
{
/* dbms_lob.compare */
diffCheck.Append(
$"dbms_lob.compare(nvl({column.FieldName},'Null'),nvl(:{x + 1},'Null')) = 0 ");
}
else
{
if ((column.FieldType == FieldTypes.DATE || column.FieldType == FieldTypes.DATETIME || column.FieldType == FieldTypes.TIME) && ignoreDates)
{
/* skip this one... */
continue;
}
if (column.FieldType == FieldTypes.NUMBER && column.FieldName.Equals("VERSION") && ignoreVersion)
{
continue;
}
diffCheck.Append($"{column.FieldName} = :{x + 1} ");
}
if (x + 1 < table.Metadata.FieldMetadata.Count)
{
diffCheck.Append(" AND ");
}
}
using (var diffCheckCmd = new OracleCommand(diffCheck.ToString(), dbConn))
{
for (var x = 0; x < table.Metadata.FieldMetadata.Count; x++)
{
var column = table.Metadata.FieldMetadata[x];
OracleParameter fieldParam = new OracleParameter();
SetOracleParamValue(fieldParam, column.FieldType, curRow, x);
diffCheckCmd.Parameters.Add(fieldParam);
}
try
{
using (var diffCheckReader = diffCheckCmd.ExecuteReader())
{
if (diffCheckReader.Read())
{
curRow.CompareResult = DMSCompareResult.SAME;
}
else
{
curRow.CompareResult = DMSCompareResult.UPDATE;
}
}
}
catch (Exception ex)
{
/* failed to do the diff, likely due to column changes */
/* mark as an update */
curRow.CompareResult = DMSCompareResult.UPDATE;
}
}
}
else
{
curRow.CompareResult = DMSCompareResult.NEW;
}
}
}
}
