sql-utilities/docs/SqlBreakdownCollection_Usage.md

SqlBreakdownCollection Usage Guide

Overview

The SqlBreakdownCollection class provides a convenient way to manage multiple SQL breakdown objects and parse batch SQL statements. It offers LINQ support, StringBuilder-based optimization, and flexible parsing capabilities.

Basic Usage

Creating a Collection

// Create an empty collection
var collection = new SqlBreakdownCollection();

// Create with initial breakdowns
var breakdowns = new List<ISqlBreakdown> { breakdown1, breakdown2 };
var collection = new SqlBreakdownCollection(breakdowns);

Adding Breakdowns

var collection = new SqlBreakdownCollection();

// Add single breakdown
collection.Add(breakdown1);

// Add multiple breakdowns
collection.AddRange(new[] { breakdown2, breakdown3, breakdown4 });

Removing Items

// Remove a specific breakdown
collection.Remove(breakdown1);

// Clear all items
collection.Clear();

Batch Parsing

The ParseBatch method allows you to split a batch SQL statement into individual statements separated by GO keywords (or other separators).

Parsing with GO Separators

var collection = new SqlBreakdownCollection();

string batchSql = @"
    SELECT * FROM Customers
    GO
    SELECT * FROM Orders WHERE Status = 'Pending'
    GO
    UPDATE Inventory SET Quantity = 0 WHERE ProductID = 123
";

collection.ParseBatch(batchSql);

// Access raw statements
foreach (var statement in collection.RawStatements)
{
    Console.WriteLine(statement);
    Console.WriteLine("---");
}

// Output:
// SELECT * FROM Customers
// ---
// SELECT * FROM Orders WHERE Status = 'Pending'
// ---
// UPDATE Inventory SET Quantity = 0 WHERE ProductID = 123
// ---

Handling GO Case-Insensitivity

The parser handles GO statements regardless of case:

string batchSql = @"
    SELECT * FROM Table1
    go
    SELECT * FROM Table2
    GO
    SELECT * FROM Table3
    Go
";

collection.ParseBatch(batchSql);
// Correctly parses into 3 statements

Handling Whitespace

GO statements with surrounding whitespace are correctly recognized:

string batchSql = @"
    SELECT * FROM Table1
       GO
    SELECT * FROM Table2
    	GO
    SELECT * FROM Table3
";

collection.ParseBatch(batchSql);
// Correctly parses into 3 statements

Combining SQL Statements

Getting Combined SQL from Breakdowns

var collection = new SqlBreakdownCollection(new[]
{
    new QueryBreakdown { SelectClause = "col1, col2", FromClause = "table1" },
    new QueryBreakdown { SelectClause = "col3, col4", FromClause = "table2" }
});

// Get combined SQL with GO separator (default)
string combinedSql = collection.GetCombinedSql();
// SELECT col1, col2 FROM table1
// GO
// SELECT col3, col4 FROM table2

// Get combined SQL with custom separator
string customSql = collection.GetCombinedSql(separator: ";");
// SELECT col1, col2 FROM table1
// ;
// SELECT col3, col4 FROM table2

// Get combined SQL without setup/finish clauses
string basicSql = collection.GetCombinedSql(includeSetupFinish: false);

Getting Batch SQL from Raw Statements

collection.ParseBatch(batchSql);

// Combine raw statements back into batch format
string reassembledBatch = collection.GetBatchSql();

// Use custom separator
string customBatch = collection.GetBatchSql(separator: ";");

LINQ Integration

Filtering with Where

var collection = new SqlBreakdownCollection(breakdowns);

// Find all SELECT queries
var selectQueries = collection.Where(b => b.ToString().Contains("SELECT"))
    .ToList();

// Count queries
int queryCount = collection.Where(b => b.ToString().Contains("SELECT")).Count();

Projecting with Select

// Get SQL lengths
var queryLengths = collection.Select(b => b.ToString().Length).ToList();

// Get first 100 characters of each query
var summaries = collection.Select(b =>
    b.ToString().Length > 100
        ? b.ToString().Substring(0, 100) + "..."
        : b.ToString())
    .ToList();

// Get query strings
var sqlStatements = collection.Select(b => b.GetSql()).ToList();

Finding Specific Items

// Get first breakdown matching criteria
var firstSelectQuery = collection.FirstOrDefault(b =>
    b.ToString().Contains("SELECT"));

// Get by index
var secondBreakdown = collection.GetAt(1);

// Get raw statement by index
var secondStatement = collection.GetRawStatementAt(1);

Practical Examples

Example 1: Processing Multiple SQL Files

// Read multiple SQL files and combine
var collection = new SqlBreakdownCollection();

string[] sqlFiles = Directory.GetFiles(@"C:\sql-scripts", "*.sql");

foreach (var file in sqlFiles)
{
    var content = File.ReadAllText(file);
    collection.ParseBatch(content);
    collection.AddRange(ParseBreakdowns(collection.RawStatements));
}

// Generate combined output
string output = collection.GetCombinedSql();
File.WriteAllText("combined_output.sql", output);

Example 2: Filtering and Processing Specific Queries

var collection = new SqlBreakdownCollection(allBreakdowns);

// Get all DELETE queries (with caution!)
var deleteQueries = collection.Where(b =>
    b.ToString().ToUpper().Contains("DELETE"))
    .ToList();

// Log them for review
foreach (var query in deleteQueries)
{
    logger.Warn($"Potentially dangerous query: {query.GetSql()}");
}

// Get only safe SELECT queries
var safeQueries = collection.Where(b =>
    !b.ToString().ToUpper().Contains("DELETE") &&
    !b.ToString().ToUpper().Contains("DROP") &&
    !b.ToString().ToUpper().Contains("TRUNCATE"))
    .ToList();

// Execute safe queries
foreach (var query in safeQueries)
{
    ExecuteQuery(query.GetSql());
}

Example 3: Batch Processing with Setup/Finish Clauses

// Create breakdowns with setup and finish clauses
var breakdown1 = new QueryBreakdown();
breakdown1.SelectClause.Clause = "* ";
breakdown1.FromClause.Clause = "Customers";
breakdown1.SetupClauses.Add("SET NOCOUNT ON;");
breakdown1.FinishClauses.Add("PRINT 'Customers query executed'");

var breakdown2 = new QueryBreakdown();
breakdown2.SelectClause.Clause = "*";
breakdown2.FromClause.Clause = "Orders";
breakdown2.FinishClauses.Add("PRINT 'Orders query executed'");

var collection = new SqlBreakdownCollection(new[] { breakdown1, breakdown2 });

// Generate SQL with all setup and finish clauses
string fullBatch = collection.GetCombinedSql(includeSetupFinish: true);
// Output includes all PRINT and NOCOUNT statements

Example 4: Analyzing Query Complexity

var collection = new SqlBreakdownCollection(allBreakdowns);

// Find complex queries
var complexQueries = collection
    .Where(b =>
    {
        var sql = b.ToString();
        return sql.Contains("JOIN") && sql.Contains("GROUP BY");
    })
    .Select(b => new
    {
        Statement = b.ToString(),
        Length = b.ToString().Length
    })
    .OrderByDescending(x => x.Length)
    .ToList();

foreach (var query in complexQueries)
{
    Console.WriteLine($"Complex query ({query.Length} chars): {query.Statement}");
}

Collection Properties and Methods

Member	Description
Count	Gets the number of breakdowns in the collection
IsEmpty	Gets whether the collection has no items
Breakdowns	Gets a read-only list of all breakdowns
RawStatements	Gets a read-only list of raw SQL statements
Add(breakdown)	Adds a single breakdown
AddRange(breakdowns)	Adds multiple breakdowns
Remove(breakdown)	Removes a breakdown
Clear()	Removes all items
ParseBatch(sqlBatch)	Parses batch SQL into statements
GetCombinedSql()	Gets formatted SQL from all breakdowns
GetBatchSql()	Gets batch format from raw statements
Where(predicate)	Filters breakdowns using LINQ
Select<T>(selector)	Projects breakdowns using LINQ
GetAt(index)	Gets breakdown at index
FirstOrDefault(predicate)	Gets first matching breakdown
GetRawStatementAt(index)	Gets raw statement at index
ToString()	Gets combined SQL string

Performance Considerations

• StringBuilder Usage: The class uses StringBuilder for efficient string concatenation when combining multiple SQL statements
• LINQ Compatibility: All LINQ operations are supported for maximum flexibility
• Lazy Evaluation: LINQ operations using Where and Select support deferred execution
• Memory Efficiency: Raw statements and breakdowns are stored separately to reduce duplication

Error Handling

The class includes robust error handling:

try
{
    collection.Add(null); // Throws ArgumentNullException
}
catch (ArgumentNullException ex)
{
    Console.WriteLine("Cannot add null breakdown");
}

try
{
    collection.GetAt(100); // Throws IndexOutOfRangeException
}
catch (IndexOutOfRangeException ex)
{
    Console.WriteLine("Index out of range");
}

try
{
    collection.ParseBatch(null); // Throws ArgumentNullException
}
catch (ArgumentNullException ex)
{
    Console.WriteLine("Batch cannot be null");
}

Related Classes

• SqlBreakdownBase: Base class for all SQL breakdown implementations
• QueryBreakdown: Represents SELECT queries with full clause support
• InsertBreakdown: Represents INSERT statements
• UpdateBreakdown: Represents UPDATE statements
• DeleteBreakdown: Represents DELETE statements
• ISqlBreakdown: Interface for breakdown objects