/// <summary>
/// This is the counterpart to <see cref="StoredProcMetadata.SerializeToDisk"/>
/// which turns the file's contents back into the object.
/// </summary>
/// <param name="path"></param>
/// <param name="spmOut"></param>
/// <returns>False if the object could not be deserialized.</returns>
public static bool TryDeserializeFromDisk(string path, out StoredProcMetadata spmOut)
{
spmOut = null;
if (!File.Exists(path))
{
return(false);
}
try
{
var binSer = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
using (var binRdr = new BinaryReader(File.Open(path, FileMode.Open)))
{
var spmBin = binSer.Deserialize(binRdr.BaseStream);
spmOut = spmBin as StoredProcMetadata;
if (spmOut == null)
{
return(false);
}
}
return(true);
}
catch (Exception ex)
{
Settings.WriteToStoredProcLog(ex, string.Format("Could not deserialize file at '{0}'", path));
return(false);
}
}
public void TestToHbmSql()
{
var testSubject = new NoFuture.Hbm.SortingContainers.StoredProcMetadata();
testSubject.ProcName = "dbo.MyStoredProc";
var testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
System.Diagnostics.Debug.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [dbo].[MyStoredProc]"));
testSubject.ProcName = "MyStoredProc";
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
System.Diagnostics.Debug.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [MyStoredProc]"));
testSubject.ProcName = "my.dot.schema.MyStoredProc";
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
System.Diagnostics.Debug.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [my.dot.schema].[MyStoredProc]"));
testSubject.ProcName = "MyStoredProc.";
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
System.Diagnostics.Debug.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [MyStoredProc]"));
testSubject.ProcName = ".MyStoredProc";
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
System.Diagnostics.Debug.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [MyStoredProc]"));
testSubject.ProcName = ".MyStoredProc.";
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
System.Diagnostics.Debug.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [MyStoredProc]"));
testSubject.Parameters = new List<StoredProcParamItem>
{
new StoredProcParamItem {ParamName = "@parameter1"},
new StoredProcParamItem {ParamName = "@parameter2"},
new StoredProcParamItem {ParamName = "@parameter3"}
};
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
System.Diagnostics.Debug.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [MyStoredProc]"));
Assert.IsTrue(testResult.Contains(":parameter1,"));
Assert.IsTrue(testResult.Contains(":parameter2,"));
Assert.IsTrue(testResult.Contains(":parameter3"));
}
protected internal void GetStoredProcNames()
{
var sqlNodes = _hbmXml.SelectNodes(CreateXpath(HbmXmlNames.HIBERNATE_MAPPING, HbmXmlNames.SQL_QUERY), _nsMgr);
if (sqlNodes == null)
{
return;
}
foreach (
var sp in
sqlNodes.Cast <XmlElement>()
.Where(
t =>
t != null && t.HasAttributes && t.Attributes[HbmXmlNames.NAME] != null &&
!string.IsNullOrWhiteSpace(t.Attributes[HbmXmlNames.NAME].Value)))
{
var spName = sp.Attributes[HbmXmlNames.NAME].Value;
var spSqlSyntax = sp.InnerText;
if (string.IsNullOrWhiteSpace(spSqlSyntax))
{
continue;
}
string spDbName;
string[] spParamNames;
if (!StoredProcMetadata.TryParseToHbmSql(spSqlSyntax, out spDbName, out spParamNames))
{
continue;
}
_spConstNames.Add(new HbmStoredProxNames
{
CallableName = spName,
DbName = spDbName,
ParamNames = spParamNames
});
}
}
public void TestToHbmSql()
{
var testSubject = new NoFuture.Hbm.SortingContainers.StoredProcMetadata();
testSubject.ProcName = "dbo.MyStoredProc";
var testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
Console.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [dbo].[MyStoredProc]"));
testSubject.ProcName = "MyStoredProc";
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
Console.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [MyStoredProc]"));
testSubject.ProcName = "my.dot.schema.MyStoredProc";
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
Console.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [my.dot.schema].[MyStoredProc]"));
testSubject.ProcName = "MyStoredProc.";
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
Console.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [MyStoredProc]"));
testSubject.ProcName = ".MyStoredProc";
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
Console.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [MyStoredProc]"));
testSubject.ProcName = ".MyStoredProc.";
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
Console.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [MyStoredProc]"));
testSubject.Parameters = new List <StoredProcParamItem>
{
new StoredProcParamItem {
ParamName = "@parameter1"
},
new StoredProcParamItem {
ParamName = "@parameter2"
},
new StoredProcParamItem {
ParamName = "@parameter3"
}
};
testResult = testSubject.ToHbmSql();
Assert.IsNotNull(testResult);
Console.WriteLine(testResult);
Assert.IsTrue(testResult.Contains("EXEC [MyStoredProc]"));
Assert.IsTrue(testResult.Contains(":parameter1,"));
Assert.IsTrue(testResult.Contains(":parameter2,"));
Assert.IsTrue(testResult.Contains(":parameter3"));
}
/// <summary>
/// This is the counterpart to <see cref="StoredProcMetadata.SerializeToDisk"/>
/// which turns the file's contents back into the object.
/// </summary>
/// <param name="path"></param>
/// <param name="spmOut"></param>
/// <returns>False if the object could not be deserialized.</returns>
public static bool TryDeserializeFromDisk(string path, out StoredProcMetadata spmOut)
{
spmOut = null;
if (!File.Exists(path))
return false;
try
{
var binSer = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
using (var binRdr = new BinaryReader(File.Open(path, FileMode.Open)))
{
var spmBin = binSer.Deserialize(binRdr.BaseStream);
spmOut = spmBin as StoredProcMetadata;
if (spmOut == null)
return false;
}
return true;
}
catch (Exception ex)
{
Settings.WriteToStoredProcLog(ex, string.Format("Could not deserialize file at '{0}'", path));
return false;
}
}
/// <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>
/// A helper function used to draft the entire command line switch (s)
/// which are passed to the NoFuture.Hbm.InvokeStoredProc.exe process.
///
/// The function handles wrapping values in double quotes when said value
/// contains a space. When the args are assigned to a <see cref="System.Diagnostics.ProcessStartInfo"/>
/// as a single string and, in turn, received by a console's Main statement as a
/// string array the values are being split on the space char (0x20) and the double-quotes
/// are removed.
/// </summary>
/// <param name="spItem"></param>
/// <param name="connectionString"></param>
/// <returns></returns>
public static string ConstructCmdLineArgs(StoredProcMetadata spItem, string connectionString)
{
var cmdArg = new StringBuilder();
cmdArg.Append(ConsoleCmd.ConstructCmdLineArgs(CONNECTION_STR_SWITCH, connectionString));
cmdArg.Append(" ");
cmdArg.Append(ConsoleCmd.ConstructCmdLineArgs(FILE_PATH_SWITCH, spItem.BinFilePath));
cmdArg.Append(" ");
cmdArg.Append(ConsoleCmd.ConstructCmdLineArgs(HBM_STORED_PROX_DIR_SWITCH, Settings.HbmStoredProcsDirectory));
cmdArg.Append(" ");
cmdArg.Append(ConsoleCmd.ConstructCmdLineArgs(SEND_MESSAGES_BACK_ON_SOCKET, Boolean.TrueString));
return cmdArg.ToString();
}