public void MultipleExecReturnsCorrectCount()
{
var batchToParse = "exec dbo.ProcOne exec dbo.ProcTwo";
var procedures = BatchParser.GetExecutedProcedures(batchToParse);
Assert.AreEqual(2, procedures.Count);
}
public void AnEmptyStringReturnsNoProcedures()
{
var batchToParse = "";
var procedures = BatchParser.GetExecutedProcedures(batchToParse);
Assert.AreEqual(0, procedures.Count);
}
public void DefaultSchemaNameParsedCorrectly()
{
var batchToParse = "exec DB1..ProcOne";
var procedures = BatchParser.GetExecutedProcedures(batchToParse);
Assert.AreEqual(1, procedures.Count);
}
public void InvalidSQLReturnsNoProcedures()
{
var batchToParse = "this is not a valid pieceof SQL";
var procedures = BatchParser.GetExecutedProcedures(batchToParse);
Assert.AreEqual(0, procedures.Count);
}
public void ProcWithNoExecReturnsProcCount()
{
var batchToParse = "dbo.ProcOne;";
var procedures = BatchParser.GetExecutedProcedures(batchToParse);
Assert.AreEqual(1, procedures.Count);
}
public void ProcWithExecNoSchemaReturnsProcCount()
{
var batchToParse = "exec ProcOne;";
var procedures = BatchParser.GetExecutedProcedures(batchToParse);
Assert.AreEqual(1, procedures.Count);
}
public void BeginEndBlockCorrectlyHandled()
{
var batchToParse =
@"
begin
exec dbo.ProcOne;
end
exec dbo.ProcTwo;
";
var procedures = BatchParser.GetExecutedProcedures(batchToParse);
Assert.AreEqual(2, procedures.Count);
}
private void button1_Click(object sender, EventArgs e)
{
// BatchParser documentation: https://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.batchparser.batchparser.parse.aspx
var startTime = DateTime.Now;
var batches = BatchParser.Parse(textBox1.Text, new ParseOptions("GO"));
Parallel.ForEach(batches, batch =>
{
ProcessBatch(batch.Content);
});
var stopTime = DateTime.Now;
MessageBox.Show(
string.Format("Started at {0}; ended at {1}; runtime of {2} seconds",
startTime.ToString("yyyy-MM-dd HH:mm:ss"), stopTime.ToString("yyyy-MM-dd HH:mm:ss"),
stopTime.Subtract(startTime).TotalSeconds
));
}
//parse whole file, calls batch parser, returns new ach
public static ACH ParseStream(System.IO.StreamReader reader, out string messages)
{
//or leave null, return null on (initial, not all) error?
ACH ach = new ACH();
string internalString = "";
uint currentLineNumber = 1;
messages = "";
//set to false once we see a valid entry record type code
//used to determine when we should stop seeing internal lines that
//should only be at the beginning of the file
bool didNotRecieveEntry = true;
bool recievedAchControlEntry = false;
int recordType;
string internalNewLine = "";
while ((recordType = reader.Peek()) != -1)
{
//if we recieved the ach control entry then add message that another line not expected (except empty line)
//(but that should be handled by didNotRecieveEntry else case as error)
//make sure all paths consume at least one line or infinite loop will result
if (didNotRecieveEntry)
{
//check if first char is a valid record type
if (recordType != BATCH_HEADER_RECORD_TYPE_ASCII_VALUE &&
recordType != ENTRY_RECORD_TYPE_ASCII_VALUE &&
recordType != ADDENDA_RECORD_TYPE_ASCII_VALUE &&
recordType != BATCH_CONTROL_RECORD_TYPE_ASCII_VALUE &&
recordType != ACH_CONTROL_RECORD_TYPE_ASCII_VALUE)
{
//this is basically the only valid path, all others should be error that appends message
if (recordType != ACH_HEADER_RECORD_TYPE_ASCII_VALUE)
{
//still an internal message, just append to internal message (with newlines between)
internalString += reader.ReadLine();
if (internalString != null)//shouldn't get null if recordType peek was successful, but just in case
{
internalString = internalNewLine + internalString;
internalNewLine = "\n";
++currentLineNumber;
}
else
{
//TODO report error it was null
}
}
else
{
//got the ach header entry, set values, go into batch parse call
string achHeader = reader.ReadLine();
if (achHeader != null)//shouldn't be null if recordType was successful, but just in case
{
ParseHeader(achHeader, ach, internalString);
//TODO handle if there there's a message from setheader
//if setheadermessage not null or ws then say so and add to messages
++currentLineNumber;//update line number after possible use in error message
}
else
{
//TODO report unexpected null
}
//while next char is a new batch header (message if not a batch header or ach control record type, also error if end of stream -1)
//for each batch
while ((recordType = reader.Peek()) == BATCH_HEADER_RECORD_TYPE_ASCII_VALUE)
{
string batchMessages;
//TODO pass in a new line count int, add to total current line number count
uint linesRead = 0;
Batch batch = BatchParser.ParseStream(reader, out batchMessages, out linesRead, currentLineNumber);
messages += batchMessages;
if (batch != null)
{
ach.Batches.Add(batch);
}
}
//for now test batch parse that just consumes non-ach control entries
//when out of batch parse call, reader probably pointing at ach control entry
//set more values with that
if (recordType != ACH_CONTROL_RECORD_TYPE_ASCII_VALUE)
{
//TODO consume line and give error message
}
else
{
//get ach control data
string achControl = reader.ReadLine();
if (achControl != null)
{
ParseControl(achControl, ach);
recievedAchControlEntry = true;
//TODO if controlmessage is not null/ws then add error to message
//TODO check control values (number of entries, hash, etc) here or in SetControl? Probably SetControl
++currentLineNumber;
//TODO error if any non-null/ws lines still in file at this point
}
}
didNotRecieveEntry = false;
}
}
else
{
messages += "\nReceived what looks like a batch, entry, or ACH control unexpectedly before an ACH header on line " + currentLineNumber.ToString() + ": '" + reader.ReadLine() + "'";
++currentLineNumber;
}
}
else
{
messages += "\nError on line " + currentLineNumber.ToString() + ": '" + reader.ReadLine() + "'";
++currentLineNumber;
}
}
if (didNotRecieveEntry && !recievedAchControlEntry)
{
messages += "\nDid not recieve both an ACH header and control";
}
return(ach);
}
