void CompareRows(DMSRow left, DMSRow right, int[] keyFields)
{
bool isSame = false;
if (left.ValueHash == right.ValueHash)
{
isSame = true;
if (!ignoreDates)
{
isSame = left.DateHash == right.DateHash;
}
if (!ignoreVersion && isSame)
{
isSame = left.VersionHash == right.VersionHash;
}
}
if (isSame)
{
left.CompareResult = DMSCompareResult.SAME;
}
else
{
if (left.KeyHash == right.KeyHash)
{
left.CompareResult = DMSCompareResult.UPDATE;
}
else
{
left.CompareResult = DMSCompareResult.NEW;
}
}
}
public LongDataViewer(string content, DataViewer viewerForm, DMSRow row, int columnIndex) : this(content)
{
tableRow = row;
colIndex = columnIndex;
this.viewerForm = viewerForm;
textBox1.ReadOnly = false;
this.Text = "Data Editor";
button1.Text = "Save Changes";
}
private void EditValue_Click(object sender, EventArgs e)
{
var menuItem = (MenuItem)sender;
var hitTest = (DataGridView.HitTestInfo)menuItem.Tag;
var content = dataGridView1.Rows[hitTest.RowIndex].Cells[hitTest.ColumnIndex].Value.ToString();
DMSRow curRow = viewerTable.Rows[hitTest.RowIndex];
new LongDataViewer(content, this, curRow, hitTest.ColumnIndex).ShowDialog(this);
}
private void SetOracleParamValue(OracleParameter param, FieldTypes type, DMSRow curRow, int index)
{
switch (type)
{
case FieldTypes.CHAR:
param.OracleDbType = OracleDbType.Varchar2;
param.Value = curRow.GetValue(index);
break;
case FieldTypes.LONG_CHAR:
param.OracleDbType = OracleDbType.Clob;
param.Value = curRow.GetValue(index);
if ((string)param.Value == "\0")
{
param.Value = null;
}
break;
case FieldTypes.NUMBER:
param.OracleDbType = OracleDbType.Int64;
param.Value = curRow.GetValue(index);
break;
case FieldTypes.SIGNED_NUMBER:
param.OracleDbType = OracleDbType.Int64;
param.Value = curRow.GetValue(index);
break;
case FieldTypes.DATE:
param.OracleDbType = OracleDbType.Date;
param.Value = curRow.GetValue(index);
break;
case FieldTypes.DATETIME:
param.OracleDbType = OracleDbType.TimeStamp;
param.Value = curRow.GetValue(index);
break;
case FieldTypes.TIME:
param.OracleDbType = OracleDbType.TimeStamp;
param.Value = curRow.GetValue(index);
break;
case FieldTypes.IMG_OR_ATTACH:
param.OracleDbType = OracleDbType.Blob;
param.Value = curRow.GetValue(index);
break;
default:
Debugger.Break();
break;
}
}
private void DeleteRow_Click(object sender, EventArgs e)
{
var menuItem = (MenuItem)sender;
var hitTest = (DataGridView.HitTestInfo)menuItem.Tag;
var result = MessageBox.Show(this, "Are you sure you want to remove this row?", "Confirm Row Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
DMSRow curRow = viewerTable.Rows[hitTest.RowIndex];
viewerTable.Rows.Remove(curRow);
DrawDataTable();
}
}
private void CopyAsInsert_Click(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder();
foreach (DataGridViewRow row in dataGridView1.SelectedRows)
{
DMSRow curRow = viewerTable.Rows[row.Index];
sb.Append("-- INSERT INTO ").Append(curRow.OwningTable.DBName).Append(" (");
foreach (var c in curRow.OwningTable.Columns)
{
sb.Append(c.Name);
if (c.Equals(curRow.OwningTable.Columns.Last()) == false)
{
sb.Append(", ");
}
}
sb.Append(") VALUES (");
sb.AppendLine("");
}
Clipboard.SetText(sb.ToString());
}
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;
}
}
}
}
