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