protected internal Dictionary <string, StoredProcMetadata> GetFilteredStoredProcNames(StoredProxSearchCriteria filterOnNamesLike)
{
var allStoredProx = Sorting.AllStoredProx;
if (allStoredProx == null || allStoredProx.Count <= 0)
{
return(null);
}
//apply filter if any
if (filterOnNamesLike == null)
{
return(allStoredProx);
}
var matchedProx = new Dictionary <string, StoredProcMetadata>();
List <string> matchedKeys = null;
if (!string.IsNullOrWhiteSpace(filterOnNamesLike.ExactName))
{
matchedKeys =
allStoredProx.Keys.Where(
x => string.Equals(x, filterOnNamesLike.ExactName, StringComparison.OrdinalIgnoreCase)).ToList();
var exactNameEntries = allStoredProx.Keys.Where(matchedKeys.Contains)
.ToDictionary(pName => pName, pName => allStoredProx[pName]);
foreach (var k in exactNameEntries.Keys.Where(k => !matchedProx.ContainsKey(k)))
{
matchedProx.Add(k, exactNameEntries[k]);
}
}
if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyStartingWith))
{
matchedKeys =
allStoredProx.Keys.Where(
x =>
Regex.IsMatch(x, string.Format("^{0}.*", filterOnNamesLike.AnyStartingWith),
RegexOptions.IgnoreCase)).ToList();
var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains)
.ToDictionary(pName => pName, pName => allStoredProx[pName]);
foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k)))
{
matchedProx.Add(k, nameEntries[k]);
}
}
if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyNameEndingWith))
{
matchedKeys =
allStoredProx.Keys.Where(
x =>
Regex.IsMatch(x, string.Format(".*{0}$", filterOnNamesLike.AnyStartingWith),
RegexOptions.IgnoreCase)).ToList();
var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains)
.ToDictionary(pName => pName, pName => allStoredProx[pName]);
foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k)))
{
matchedProx.Add(k, nameEntries[k]);
}
}
if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyNameContaining))
{
matchedKeys =
allStoredProx.Keys.Where(
x =>
Regex.IsMatch(x, string.Format(".*{0}.*", filterOnNamesLike.AnyStartingWith),
RegexOptions.IgnoreCase)).ToList();
var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains)
.ToDictionary(pName => pName, pName => allStoredProx[pName]);
foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k)))
{
matchedProx.Add(k, nameEntries[k]);
}
}
allStoredProx = matchedProx;
if (filterOnNamesLike.SqlInjOnParamNamesLike == null || filterOnNamesLike.SqlInjOnParamNamesLike.Count <= 0)
{
return(allStoredProx);
}
foreach (var param in allStoredProx.SelectMany(x => x.Value.Parameters))
{
foreach (var namedLike in filterOnNamesLike.SqlInjOnParamNamesLike)
{
if (!param.ParamName.Contains(namedLike))
{
continue;
}
param.IsOpenToSqlInj = true;
break;
}
}
return(allStoredProx);
}
/// <summary>
/// The synchronous version of the function which gets
/// the returned datasets from stored prox at the location specified
/// at <see cref="connectionString"/>.
/// </summary>
/// <param name="filterOnNamesLike"> A proc(s) search criteria. </param>
/// <param name="connectionString">
/// The calling assembly is forced to specify what the connection string is.
/// This value should bare a semblance to the current connection values at
/// <see cref="NfConfig.SqlServer"/> and
/// <see cref="NfConfig.SqlCatalog"/>
/// but its value is not inspected verifying this.
/// </param>
/// <remarks>
/// The excessive level of indirection here is an attempt to gain control
/// over the fact that any stored proc which itself calls 'sp_executesql' does
/// not honor the timeout value assigned on the <see cref="System.Data.SqlClient.SqlCommand"/>.
/// Since every proc is invoked with random values, a stored proc
/// may run endlessly and comsume the entire machines resources.
/// Placing all this under the control of an autonomous process allows for the process, as a whole,
/// to be shutdown, and SqlServer has to clean up the mess.
/// </remarks>
public void GetSpResultSetXsd(StoredProxSearchCriteria filterOnNamesLike, string connectionString)
{
if (string.IsNullOrWhiteSpace(_nfHbmInvokeProxExePath) && !string.IsNullOrWhiteSpace(NfConfig.BinDirectories.Root))
{
_nfHbmInvokeProxExePath = Path.Combine(NfConfig.BinDirectories.Root, "NoFuture.Hbm.InvokeStoredProc");
}
//blow up for this
if (!string.IsNullOrWhiteSpace(_nfHbmInvokeProxExePath) || !File.Exists(_nfHbmInvokeProxExePath))
{
throw new ItsDeadJim(string.Format("The required console app is missing at '{0}'", _nfHbmInvokeProxExePath));
}
BroadcastProgress(new ProgressMessage
{
Activity = "Loading current connection",
Status = Status
});
Settings.LoadOutputPathCurrentSettings();
BroadcastProgress(new ProgressMessage
{
Activity = "Getting list of stored proc targets",
Status = Status
});
//copy into local copy the global sql inj param names
if (Settings.SqlInjParamNames.Any())
{
filterOnNamesLike = filterOnNamesLike ?? new StoredProxSearchCriteria();
filterOnNamesLike.SqlInjOnParamNamesLike.AddRange(Settings.SqlInjParamNames);
}
//check the current connection has any procs present
var allStoredProx = GetFilteredStoredProcNames(filterOnNamesLike);
if (allStoredProx == null || allStoredProx.Count <= 0)
{
BroadcastProgress(new ProgressMessage
{
Activity = "There are no prox at NoFuture.Hbm.Sorting.AllStoredProx",
Status = Status
});
return;
}
//begin async listening for messages from autonomous processes
var socketListeningTask = _taskFactory.StartNew(BeginReceiveMsgFromProcess);
var counter = 0;
foreach (var spItemKey in allStoredProx.Keys)
{
//check for the name being on the black list
if (Settings.DoNotReference.Contains(spItemKey))
{
counter += 1;
continue;
}
//assign to instance level
_currentSp = allStoredProx[spItemKey];
//serialize to disk - invoked process will read from this location
_currentSp.SerializeToDisk();
//start a P\Invoke to NoFuture.Hbm.InvokeStoredProc.exe
using (_currentProcess = new Process
{
StartInfo =
new ProcessStartInfo(_nfHbmInvokeProxExePath, ConstructCmdLineArgs(_currentSp, connectionString))
{
CreateNoWindow = true,
UseShellExecute = false,
RedirectStandardOutput = true,
RedirectStandardError = true
}
})
{
BroadcastProgress(new ProgressMessage
{
Activity = string.Format("At sp '{0}'", _currentSp.ProcName),
Status = Status,
ProgressCounter = Etc.CalcProgressCounter(counter, allStoredProx.Count),
ProcName = _currentSp.ProcName
});
try
{
Settings.WriteToStoredProcLog(string.Format("working '{0}'", _currentSp.ProcName));
//fire off the exe
_currentProcess.Start();
//wait for it to finish or timeout
_currentProcess.WaitForExit((Settings.HbmStoredProcXsdTimeOut + PInvokePaddingInSeconds) * 1000);
//presumes it timed out
if (!_currentProcess.HasExited)
{
//the only sure way to end some p.o.s. proc
_currentProcess.Kill();
Settings.WriteToStoredProcLog(string.Format("Stored proc '{0}' is not responding is will be shutdown", _currentSp.ProcName));
Sorting.KilledProx.Add(_currentSp.ProcName);
}
}
catch (Exception ex)
{
Settings.WriteToStoredProcLog(ex, string.Format("unknown error at '{0}'", _currentSp.ProcName));
}
counter += 1;
}
}
//communicate that the has completed
BroadcastProgress(new ProgressMessage
{
Activity = "GetSpResultSetXsd has completed",
Status = COMPLETED_STATUS_STRING,
ProgressCounter = 100,
});
if (socketListeningTask.IsCompleted)
{
socketListeningTask.Dispose();
}
}
/// <summary>
/// The asynchronous version of the function which gets
/// the returned datasets from stored prox at the location specified
/// </summary>
/// <param name="filterOnNamesLike"></param>
/// <param name="connectionString"></param>
/// <remarks>
/// See the details on <see cref="GetSpResultSetXsd"/> for
/// more information on the manner an purpose of this function.
/// </remarks>
public void BeginGetSpResultSetXsd(StoredProxSearchCriteria filterOnNamesLike, string connectionString)
{
GetSpResultSetXsdTask = _taskFactory.StartNew(() => GetSpResultSetXsd(filterOnNamesLike, connectionString));
}
protected internal Dictionary<string, StoredProcMetadata> GetFilteredStoredProcNames(StoredProxSearchCriteria filterOnNamesLike)
{
var allStoredProx = Sorting.AllStoredProx;
if (allStoredProx == null || allStoredProx.Count <= 0)
return null;
//apply filter if any
if (filterOnNamesLike == null)
{
return allStoredProx;
}
var matchedProx = new Dictionary<string, StoredProcMetadata>();
List<string> matchedKeys = null;
if (!string.IsNullOrWhiteSpace(filterOnNamesLike.ExactName))
{
matchedKeys =
allStoredProx.Keys.Where(
x => string.Equals(x, filterOnNamesLike.ExactName, StringComparison.OrdinalIgnoreCase)).ToList();
var exactNameEntries = allStoredProx.Keys.Where(matchedKeys.Contains)
.ToDictionary(pName => pName, pName => allStoredProx[pName]);
foreach (var k in exactNameEntries.Keys.Where(k => !matchedProx.ContainsKey(k)))
matchedProx.Add(k, exactNameEntries[k]);
}
if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyStartingWith))
{
matchedKeys =
allStoredProx.Keys.Where(
x =>
Regex.IsMatch(x, string.Format("^{0}.*", filterOnNamesLike.AnyStartingWith),
RegexOptions.IgnoreCase)).ToList();
var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains)
.ToDictionary(pName => pName, pName => allStoredProx[pName]);
foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k)))
matchedProx.Add(k, nameEntries[k]);
}
if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyNameEndingWith))
{
matchedKeys =
allStoredProx.Keys.Where(
x =>
Regex.IsMatch(x, string.Format(".*{0}$", filterOnNamesLike.AnyStartingWith),
RegexOptions.IgnoreCase)).ToList();
var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains)
.ToDictionary(pName => pName, pName => allStoredProx[pName]);
foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k)))
matchedProx.Add(k, nameEntries[k]);
}
if (!string.IsNullOrWhiteSpace(filterOnNamesLike.AnyNameContaining))
{
matchedKeys =
allStoredProx.Keys.Where(
x =>
Regex.IsMatch(x, string.Format(".*{0}.*", filterOnNamesLike.AnyStartingWith),
RegexOptions.IgnoreCase)).ToList();
var nameEntries = allStoredProx.Keys.Where(matchedKeys.Contains)
.ToDictionary(pName => pName, pName => allStoredProx[pName]);
foreach (var k in nameEntries.Keys.Where(k => !matchedProx.ContainsKey(k)))
matchedProx.Add(k, nameEntries[k]);
}
allStoredProx = matchedProx;
if (filterOnNamesLike.SqlInjOnParamNamesLike == null || filterOnNamesLike.SqlInjOnParamNamesLike.Count <= 0)
{
return allStoredProx;
}
foreach (var param in allStoredProx.SelectMany(x => x.Value.Parameters))
{
foreach (var namedLike in filterOnNamesLike.SqlInjOnParamNamesLike)
{
if (!param.ParamName.Contains(namedLike))
{
continue;
}
param.IsOpenToSqlInj = true;
break;
}
}
return allStoredProx;
}
/// <summary>
/// The synchronous version of the function which gets
/// the returned datasets from stored prox at the location specified
/// at <see cref="connectionString"/>.
/// </summary>
/// <param name="filterOnNamesLike"> A proc(s) search criteria. </param>
/// <param name="connectionString">
/// The calling assembly is forced to specify what the connection string is.
/// This value should bare a semblance to the current connection values at
/// <see cref="NfConfig.SqlServer"/> and
/// <see cref="NfConfig.SqlCatalog"/>
/// but its value is not inspected verifying this.
/// </param>
/// <remarks>
/// The excessive level of indirection here is an attempt to gain control
/// over the fact that any stored proc which itself calls 'sp_executesql' does
/// not honor the timeout value assigned on the <see cref="System.Data.SqlClient.SqlCommand"/>.
/// Since every proc is invoked with random values, a stored proc
/// may run endlessly and comsume the entire machines resources.
/// Placing all this under the control of an autonomous process allows for the process, as a whole,
/// to be shutdown, and SqlServer has to clean up the mess.
/// </remarks>
public void GetSpResultSetXsd(StoredProxSearchCriteria filterOnNamesLike, string connectionString)
{
//blow up for this
if(!File.Exists(_nfHbmInvokeProxExePath))
throw new ItsDeadJim(string.Format("The required console app is missing at '{0}'", _nfHbmInvokeProxExePath));
BroadcastProgress(new ProgressMessage
{
Activity = "Loading current connection",
Status = Status
});
Settings.LoadOutputPathCurrentSettings();
BroadcastProgress(new ProgressMessage
{
Activity = "Getting list of stored proc targets",
Status = Status
});
//copy into local copy the global sql inj param names
if (Settings.SqlInjParamNames.Any())
{
filterOnNamesLike = filterOnNamesLike ?? new StoredProxSearchCriteria();
filterOnNamesLike.SqlInjOnParamNamesLike.AddRange(Settings.SqlInjParamNames);
}
//check the current connection has any procs present
var allStoredProx = GetFilteredStoredProcNames(filterOnNamesLike);
if (allStoredProx == null || allStoredProx.Count <= 0)
{
BroadcastProgress(new ProgressMessage
{
Activity = "There are no prox at NoFuture.Hbm.Sorting.AllStoredProx",
Status = Status
});
return;
}
//begin async listening for messages from autonomous processes
var socketListeningTask = _taskFactory.StartNew(BeginReceiveMsgFromProcess);
var counter = 0;
foreach (var spItemKey in allStoredProx.Keys)
{
//check for the name being on the black list
if (Settings.DoNotReference.Contains(spItemKey))
{
counter += 1;
continue;
}
//assign to instance level
_currentSp = allStoredProx[spItemKey];
//serialize to disk - invoked process will read from this location
_currentSp.SerializeToDisk();
//start a P\Invoke to NoFuture.Hbm.InvokeStoredProc.exe
using (_currentProcess = new Process
{
StartInfo =
new ProcessStartInfo(_nfHbmInvokeProxExePath, ConstructCmdLineArgs(_currentSp, connectionString))
{
CreateNoWindow = true,
UseShellExecute = false,
RedirectStandardOutput = true,
RedirectStandardError = true
}
})
{
BroadcastProgress(new ProgressMessage
{
Activity = string.Format("At sp '{0}'", _currentSp.ProcName),
Status = Status,
ProgressCounter = Etc.CalcProgressCounter(counter, allStoredProx.Count),
ProcName = _currentSp.ProcName
});
try
{
Settings.WriteToStoredProcLog(string.Format("working '{0}'", _currentSp.ProcName));
//fire off the exe
_currentProcess.Start();
//wait for it to finish or timeout
_currentProcess.WaitForExit((Settings.HbmStoredProcXsdTimeOut + PInvokePaddingInSeconds) * 1000);
//presumes it timed out
if (!_currentProcess.HasExited)
{
//the only sure way to end some p.o.s. proc
_currentProcess.Kill();
Settings.WriteToStoredProcLog(string.Format("Stored proc '{0}' is not responding is will be shutdown", _currentSp.ProcName));
Sorting.KilledProx.Add(_currentSp.ProcName);
}
}
catch (Exception ex)
{
Settings.WriteToStoredProcLog(ex, string.Format("unknown error at '{0}'", _currentSp.ProcName));
}
counter += 1;
}
}
//communicate that the has completed
BroadcastProgress(new ProgressMessage
{
Activity = "GetSpResultSetXsd has completed",
Status = COMPLETED_STATUS_STRING,
ProgressCounter = 100,
});
if(socketListeningTask.IsCompleted)
socketListeningTask.Dispose();
}
/// <summary>
/// The asynchronous version of the function which gets
/// the returned datasets from stored prox at the location specified
/// </summary>
/// <param name="filterOnNamesLike"></param>
/// <param name="connectionString"></param>
/// <remarks>
/// See the details on <see cref="GetSpResultSetXsd"/> for
/// more information on the manner an purpose of this function.
/// </remarks>
public void BeginGetSpResultSetXsd(StoredProxSearchCriteria filterOnNamesLike, string connectionString)
{
GetSpResultSetXsdTask = _taskFactory.StartNew(() => GetSpResultSetXsd(filterOnNamesLike, connectionString));
}