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