private ResultSet SelectExternalData(ExternalQueryInfo Query) { ResultSet rs = null; try { //Make the queries and then argh! rs = new ResultSet(); ResultSet internalRs = ProcessInternalQuery(_dab, Query.InternalQuery, false); SetExternalDatabase(Query); ResultSet externalRs = ProcessInternalQuery(_externalDab, Query.ExternalQuery, false); DataSet joinDs = new DataSet(); joinDs.Tables.Add(internalRs.Data); joinDs.Tables.Add(externalRs.Data); joinDs.Tables[0].TableName = Query.InternalAlias; joinDs.Tables[1].TableName = Query.ExternalAlias; List<QueryColumn> cols = BuildColumns(Query, rs.Data, internalRs.Data, externalRs.Data); JoinClause jc = BuildJoinClause(Query, internalRs.Data, externalRs.Data); if (Query.JoinType.ToLower() == "inner" && jc != null) { var rows = from a in externalRs.Data.AsEnumerable() join b in internalRs.Data.AsEnumerable() on a[jc.ExternalCol].ToString().ToUpper() equals b[jc.InternalCol].ToString().ToUpper() select new { a, b }; foreach (var row in rows) { List<object> Data = new List<object>(); foreach (QueryColumn col in cols) { if (col.Table.Equals(internalRs.Data)) Data.Add(row.b[col.Name]); else Data.Add(row.a[col.Name]); } rs.Data.LoadDataRow(Data.ToArray(), false); } } else if (Query.JoinType.ToLower() == "left" && jc != null) { var rows = from b in internalRs.Data.AsEnumerable() join a in externalRs.Data.AsEnumerable() on b[jc.InternalCol].ToString().ToUpper() equals a[jc.ExternalCol].ToString().ToUpper() into abTemp from ab in abTemp.DefaultIfEmpty() select new { b, ab }; foreach (var row in rows) { List<object> Data = new List<object>(); foreach (QueryColumn col in cols) { if (col.Table.Equals(internalRs.Data)) Data.Add(row.b[col.Name]); else Data.Add(row.ab != null ? row.ab[col.Name] : null); } rs.Data.LoadDataRow(Data.ToArray(), false); } } return rs; } catch { throw; } finally { } }
/// <summary>Parses and executes an external query</summary> /// <param name="Query">Query to process</param> /// <returns>ResultSet object containing data and messages as a result of the given query</returns> private ResultSet ProcessExternalQuery(string Query) { CheckCancel(); ResultSet rs = new ResultSet(); ExternalQueryInfo myQuery = new ExternalQueryInfo(); string pattern = string.Empty; if (Query.Trim().Substring(0, 6).ToLower() == "select") { pattern = @"^\s*select(.*?)from\s*\(\s*(select.*?)\)\s*([a-z0-9]+)\s+(inner|outer|left|right|cross|full)\s+join\s+\(" + BASE_PATTERN + @"\)\s*([a-z0-9]+)\s+on(.*?)$"; Match mt = RunRegEx(pattern, Query); if (mt.Success) { myQuery.Server = mt.Groups[5].Value; myQuery.Database = mt.Groups[6].Value; myQuery.ColumnList = mt.Groups[1].Value; myQuery.InternalQuery = mt.Groups[2].Value; myQuery.InternalAlias = mt.Groups[3].Value; myQuery.JoinType = mt.Groups[4].Value; myQuery.ExternalQuery = mt.Groups[7].Value; myQuery.ExternalAlias = mt.Groups[8].Value; myQuery.JoinClause = mt.Groups[9].Value; rs = SelectExternalData(myQuery); } } else if (Query.Trim().Substring(0, 6).ToLower() == "insert") { pattern = @"^\s*insert\s+into\s+([a-z0-9\._\-#]+?)\s+(\(\s*([a-z0-9\,\s_\-]+?)\s*\)\s+)*?" + BASE_PATTERN + @"\s?;\s?$"; Match mt = RunRegEx(pattern, Query); if (mt.Success) { myQuery.Server = mt.Groups[4].Value; myQuery.Database = mt.Groups[5].Value; myQuery.ColumnList = mt.Groups[3].Value; myQuery.InternalQuery = mt.Groups[1].Value; myQuery.ExternalQuery = mt.Groups[6].Value; rs = InsertExternalData(myQuery); } } else { throw new Exception("Invalid extraserver query syntax."); } return rs; }
/// <summary>Processes an public query, relative to the data access object</summary> /// <param name="Dab">DataAccessBase object to execute the query against</param> /// <param name="Query">Query to run</param> /// <returns>ResultSet object containing data and messages as a result of the given query</returns> private ResultSet ProcessInternalQuery(DataAccessBase Dab, string Query, bool ReportProgress) { IDataReader reader = null; try { if (_cancelPending) { throw new Exception("Operation cancelled"); } reader = Dab.ExecuteReader(Query); ResultSet rs = new ResultSet(); rs.Messages = reader.RecordsAffected + " rows affected"; LoadResult(rs.Data, reader, Dab, ReportProgress); return rs; } catch { CheckCancel(); throw; } finally { if (reader != null) reader.Close(); } }
/// <summary>Worker function which actually runs the query</summary> /// <param name="Query">Query to execute</param> /// <returns>ResultSet that is the result of the given query</returns> private ResultSet RunQueryWorker(string Query) { ResultSet res = null; if (_dab == null) { res = new ResultSet(); res.Messages = "No active connection"; } if (!string.IsNullOrEmpty(Query) && res == null) { res = _dab.GetDataSet(Query, ref _browser); } return res; }