private List <string> GetNewIndices(MigrationTable poTbl, List <MigrationTable> poTables)
{
List <String> xsNewIdxs = new List <string>(0);
string xsTbl = poTbl.Name.Trim().ToLower();
for (int xii = 0; xii < poTables.Count; xii++)
{
if (poTables[xii].Name.Trim().ToLower() == xsTbl)
{
for (int xiIdx = 0; xiIdx < poTbl.Indices.Count; xiIdx++)
{
string xsIdx = poTbl.Indices[xiIdx].Name.Trim().ToLower();
bool xbFound = false;
for (int xi = 0; xi < poTables[xii].Indices.Count; xi++)
{
if (poTables[xii].Indices[xi].Name.ToLower() == xsIdx)
{
xbFound = true;
break;
}
}
if (!xbFound)
{
// index is in "as they should be", but not currently an index for the table in the actual database
xsNewIdxs.Add(xsIdx);
}
}
break;
}
}
return(xsNewIdxs);
}
private List <string> GetNewColumns(MigrationTable poTbl, List <MigrationTable> poTables)
{
List <String> xsNewColumns = new List <string>(0);
string xsTbl = poTbl.Name.Trim().ToLower();
for (int xii = 0; xii < poTables.Count; xii++)
{
if (poTables[xii].Name.Trim().ToLower() == xsTbl)
{
for (int xiColm = 0; xiColm < poTbl.Columns.Count; xiColm++)
{
string xsColm = poTbl.Columns[xiColm].Name.Trim().ToLower();
bool xbFound = false;
for (int xi = 0; xi < poTables[xii].Columns.Count; xi++)
{
if (poTables[xii].Columns[xi].Name.ToLower() == xsColm)
{
xbFound = true;
break;
}
}
if (!xbFound)
{
// column is in "as they should be", but not currently a column for the table in the actual database
xsNewColumns.Add(xsColm);
}
}
break;
}
}
return(xsNewColumns);
}
// constructor
public Migrator(DatabaseConnectionInfo poDbConnInfo, List <Table> poTables)
{
Database = poDbConnInfo;
TablesAsTheyShouldBe = new List <MigrationTable>(0);
for (int xii = 0; xii < poTables.Count; xii++)
{
MigrationTable xoTbl = new MigrationTable();
xoTbl.Name = poTables[xii].Name;
xoTbl.Columns = poTables[xii].Columns;
xoTbl.Indices = poTables[xii].Indices;
TablesAsTheyShouldBe.Add(xoTbl);
}
}
private List <ColumnSizeChange> GetSizeChangeColumns(MigrationTable poTbl, List <MigrationTable> poTables)
{
List <ColumnSizeChange> xrSizeChangeColumns = new List <ColumnSizeChange>(0);
string xsTbl = poTbl.Name.Trim().ToLower();
for (int xii = 0; xii < poTables.Count; xii++)
{
if (poTables[xii].Name.Trim().ToLower() == xsTbl)
{
for (int xiColm = 0; xiColm < poTbl.Columns.Count; xiColm++)
{
string xsColm = poTbl.Columns[xiColm].Name.Trim().ToLower();
for (int xi = 0; xi < poTables[xii].Columns.Count; xi++)
{
if (poTables[xii].Columns[xi].Name.ToLower() == xsColm)
{
if (poTbl.Columns[xiColm].Type == ColumnType.Char && poTbl.Columns[xiColm].Type == poTables[xii].Columns[xi].Type)
{
if (poTbl.Columns[xiColm].Size != poTables[xii].Columns[xi].Size)
{
ColumnSizeChange xo = new ColumnSizeChange();
xo.Name = poTbl.Columns[xiColm].Name;
xo.NewSize = poTbl.Columns[xiColm].Size;
xo.Type = poTbl.Columns[xiColm].Type;
xrSizeChangeColumns.Add(xo);
}
}
break;
}
}
}
break;
}
}
return(xrSizeChangeColumns);
}
private List <MigrationTable> GetDatabaseTables(OleDbConnection poConn, out string psErrMsg)
{
string xsErrMsg = "";
List <MigrationTable> xoTables = new List <MigrationTable>(0);
try
{
DataTable xoData = poConn.GetSchema("TABLES");
int xiTblTypeColm = -1, xiTblNameColm = -1;
for (int xiCol = 0; xiCol < xoData.Columns.Count; xiCol++)
{
string xsColmName = xoData.Columns[xiCol].ColumnName;
if (xiTblTypeColm < 0)
{
if (xsColmName.Trim().ToUpper() == "TABLE_TYPE")
{
xiTblTypeColm = xiCol;
}
}
if (xiTblNameColm < 0)
{
if (xsColmName.Trim().ToUpper() == "TABLE_NAME")
{
xiTblNameColm = xiCol;
}
}
}
for (int xii = 1; xii <= xoData.Rows.Count; xii++)
{
string xsTblNm = "";
bool xbIsTbl = false;
string xsLine = "";
for (int xi2 = 0; xi2 < xoData.Columns.Count; xi2++)
{
var xv = xoData.Rows[xii - 1][xi2];
if (xi2 > 0)
{
xsLine += "\t";
}
xsLine += xv.ToString();
if (xi2 == xiTblTypeColm)
{
string xs = xv.ToString().Trim();
if (xs == "TABLE" || xs == "BASE TABLE")
{
xbIsTbl = true;
}
}
else
{
if (xi2 == xiTblNameColm)
{
xsTblNm = xv.ToString().Trim();
}
}
}
if (xbIsTbl)
{
MigrationTable xoTbl = new MigrationTable();
xoTbl.Columns = new List <Column>(0);
xoTbl.Name = xsTblNm;
//
// get columns in table
//
try
{
DataTable xoColms = poConn.GetSchema("Columns", new[] { null, null, xsTblNm });
int xiColm = -1, xiMaxColm = -1, xiDataTypeColm = -1;
for (int xiCol = 0; xiCol < xoColms.Columns.Count; xiCol++)
{
string xsColm = xoColms.Columns[xiCol].Caption.ToUpper();
if (xsColm == "COLUMN_NAME")
{
xiColm = xiCol;
//break;
}
else
{
if (xsColm == "CHARACTER_MAXIMUM_LENGTH")
{
xiMaxColm = xiCol;
}
else
{
if (xsColm == "DATA_TYPE")
{
xiDataTypeColm = xiCol;
}
}
}
}
for (int xiRow = 0; xiRow < xoColms.Rows.Count; xiRow++)
{
Column xoCol = new Column();
xoCol.Name = xoColms.Rows[xiRow][xiColm].ToString();
ColumnType xiDataType = ColumnType.Unknown;
int xiSize = 0;
try
{
int xi = Convert.ToInt32(xoColms.Rows[xiRow][xiDataTypeColm]);
if (xi == 130)
{
xiDataType = ColumnType.Char;
xiSize = Convert.ToInt32(xoColms.Rows[xiRow][xiMaxColm]);
}
}
catch { }
xoCol.Type = xiDataType;
xoCol.Size = xiSize;
xoTbl.Columns.Add(xoCol);
}
xoColms.Dispose();
}
catch (Exception xoExc)
{
xsErrMsg = xoExc.Message;
break;
}
//
// get any indices for the table
//
string xsErr = "";
List <string> xsIdxs = GetTableIndices(poConn, xoTbl.Name, out xsErr);
if (xsErr.Length == 0)
{
for (int xiIdx = 0; xiIdx < xsIdxs.Count; xiIdx++)
{
xoTbl.Indices.Add(new Index(xsIdxs[xiIdx], ""));
}
}
xoTables.Add(xoTbl);
}
}
xoData.Dispose();
}
catch (Exception xoExc)
{
xsErrMsg = xoExc.Message;
}
psErrMsg = xsErrMsg;
return(xoTables);
}