public bool Load(QDatabase database, string tableName)
{
bool retval = true;
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT ORDINAL_POSITION, COLUMN_NAME FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE TABLE_NAME = @table_name ORDER BY TABLE_NAME, ORDINAL_POSITION";
command.Parameters.Add(new SqlParameter("table_name", tableName));
Columns = database.ExecuteCommand(command);
return(retval);
}
public void FromXml(XmlNode node)
{
Name = node.Attributes["name"].Value.ToString();
XmlNodeList list = node.SelectNodes("databases/database");
foreach (XmlNode dbnode in list)
{
QDatabase db = new QDatabase(Parent.EnvType, Name);
db.Deserialize(dbnode);
if (!Databases.Any(D => D.FullName == db.FullName))
{
Databases.Add(db);
}
}
}
public QDatabase GetDatabase(QDatabaseName databaseName)
{
QDatabase retval = null;
if (this.ContainsKey(databaseName.EnvironmentType))
{
foreach (QSQLServer srv in this[databaseName.EnvironmentType].SqlServers)
{
retval = srv.Databases.FirstOrDefault(D => D.FullName.Equals(databaseName));
if (retval != null)
{
break;
}
}
}
return(retval);
}
public bool FromDB(QDatabase database)
{
bool retval = true;
try
{
string Sql = "SELECT TOP 1 CLIENT_MAJOR,CLIENT_MINOR,CLIENT_PATCH FROM TLK_DATABASE_VERSIONS ORDER BY CLIENT_MAJOR DESC,CLIENT_MINOR DESC,CLIENT_PATCH DESC";
DataSet ds = database.ExecuteQuery(Sql);
if (ds != null && ds.Tables.Count == 1)
{
Major = Convert.ToInt32(ds.Tables[0].Rows[0]["CLIENT_MAJOR"]);
Minor = Convert.ToInt32(ds.Tables[0].Rows[0]["CLIENT_MINOR"]);
Patch = Convert.ToInt32(ds.Tables[0].Rows[0]["CLIENT_PATCH"]);
}
}
catch (Exception ex)
{
retval = false;
}
return(retval);
}
public DataSet CompareQueryData(QDatabaseName sourceDBName,
string sourceStatement,
QDatabaseName destDBName,
string destStatement,
List <string> includeCols,
string orderBy,
out List <string> commonColumns)
{
DataSet retval = new DataSet();
QDatabase sourceDB = QInstance.Environments.GetDatabase(sourceDBName);
QDatabase destDB = QInstance.Environments.GetDatabase(destDBName);
DataSet sourceDS = sourceDB.ExecuteQuery(sourceStatement);
DataSet destDS = destDB.ExecuteQuery(destStatement);
DataTable sourceTb = sourceDS.Tables[0];
DataTable destTb = destDS.Tables[0];
DataTable initSourceTb = sourceDS.Tables[0].Copy();
DataTable initDestTb = destDS.Tables[0].Copy();
initSourceTb.TableName = "SOURCE_INITIAL";
initDestTb.TableName = "DEST_INITIAL";
DataTable sourceUnmatched = null;
DataTable destUnmatched = null;
DataTable matched = null;
DataTable info = new DataTable("INFORMATION");
info.Columns.Add("SOURCE_ONLY_COLS", typeof(string));
info.Columns.Add("DEST_ONLY_COLS", typeof(string));
info.Columns.Add("COMMON_COLS", typeof(string));
info.Columns.Add("SOURCE_MATCHED", typeof(string));
info.Columns.Add("DEST_MATCHED", typeof(string));
info.Columns.Add("SOURCE_NOT_MATCHED", typeof(string));
info.Columns.Add("DEST_NOT_MATCHED", typeof(string));
commonColumns = (sourceTb.Columns.OfType <DataColumn>().Intersect(destTb.Columns.OfType <DataColumn>(), new DataColumnComparer())).ToArray().Select(C => C.ColumnName).ToList();
var sourceColsOnly = (sourceTb.Columns.OfType <DataColumn>().Except(destTb.Columns.OfType <DataColumn>(), new DataColumnComparer())).ToArray().Select(C => C.ColumnName).ToArray();
var destColsOnly = (destTb.Columns.OfType <DataColumn>().Except(sourceTb.Columns.OfType <DataColumn>(), new DataColumnComparer())).ToArray().Select(C => C.ColumnName).ToArray();
if (includeCols != null && includeCols.Count > 0)
{
IncludeColumns(sourceTb, includeCols);
IncludeColumns(destTb, includeCols);
}
else
{
foreach (string colName in sourceColsOnly)
{
sourceTb.Columns.Remove(colName);
}
foreach (string colName in destColsOnly)
{
destTb.Columns.Remove(colName);
}
includeCols = commonColumns.Select(i => i).ToList();
}
try { sourceUnmatched = sourceTb.AsEnumerable().Except(destTb.AsEnumerable(), DataRowComparer.Default).CopyToDataTable(); }
catch { sourceUnmatched = new DataTable(); }
finally { sourceUnmatched.TableName = "SOURCE_UNMATCHED"; }
try { destUnmatched = destTb.AsEnumerable().Except(sourceTb.AsEnumerable(), DataRowComparer.Default).CopyToDataTable(); }
catch { destUnmatched = new DataTable(); }
finally { destUnmatched.TableName = "DEST_UNMATCHED"; }
try { matched = sourceTb.AsEnumerable().Intersect(destTb.AsEnumerable(), DataRowComparer.Default).CopyToDataTable(); }
catch { matched = new DataTable(); }
finally { matched.TableName = "MATCHED"; }
DataRow row = info.NewRow();
row["SOURCE_ONLY_COLS"] = string.Join(",", sourceColsOnly);
row["DEST_ONLY_COLS"] = string.Join(",", destColsOnly);
row["COMMON_COLS"] = string.Join(",", commonColumns);
row["SOURCE_MATCHED"] = string.Format("Matched {0} of {1}.", matched.Rows.Count, sourceTb.Rows.Count);
row["DEST_MATCHED"] = string.Format("Matched {0} of {1}.", matched.Rows.Count, destTb.Rows.Count);
row["SOURCE_NOT_MATCHED"] = string.Format("Source not matched {0} of {1}.", sourceUnmatched.Rows.Count, sourceTb.Rows.Count);
row["DEST_NOT_MATCHED"] = string.Format("Dest not matched {0} of {1}.", destUnmatched.Rows.Count, destTb.Rows.Count);
info.Rows.Add(row);
DataTable sourceUnmatchedFull = DeleteNotMatchedRows(sourceUnmatched, initSourceTb, includeCols);
DataTable destUnmatchedFull = DeleteNotMatchedRows(destUnmatched, initDestTb, includeCols);
DataTable sourceMatchedFull = DeleteNotMatchedRows(matched, initSourceTb, includeCols);
DataTable destMatchedFull = DeleteNotMatchedRows(matched, initDestTb, includeCols);
sourceUnmatchedFull.TableName = "SOURCE_UNMATCHED";
destUnmatchedFull.TableName = "DEST_UNMATCHED";
sourceMatchedFull.TableName = "SOURCE_MATCHED";
destMatchedFull.TableName = "DEST_MATCHED";
sourceUnmatchedFull.DefaultView.Sort = orderBy;
destUnmatchedFull.DefaultView.Sort = orderBy;
sourceMatchedFull.DefaultView.Sort = orderBy;
destMatchedFull.DefaultView.Sort = orderBy;
sourceUnmatchedFull = sourceUnmatchedFull.DefaultView.ToTable();
destUnmatchedFull = destUnmatchedFull.DefaultView.ToTable();
sourceMatchedFull = sourceMatchedFull.DefaultView.ToTable();
destMatchedFull = destMatchedFull.DefaultView.ToTable();
//retval.Tables.Add(sourceUnmatched);
//retval.Tables.Add(destUnmatched);
//retval.Tables.Add(matched);
retval.Tables.Add(sourceUnmatchedFull);
retval.Tables.Add(destUnmatchedFull);
retval.Tables.Add(sourceMatchedFull);
retval.Tables.Add(destMatchedFull);
retval.Tables.Add(info);
AddExtraColumns(sourceDBName, sourceStatement, sourceUnmatchedFull);
AddExtraColumns(sourceDBName, sourceStatement, sourceMatchedFull);
AddExtraColumns(destDBName, destStatement, destUnmatchedFull);
AddExtraColumns(destDBName, destStatement, destMatchedFull);
return(retval);
}
