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

```csharp
// 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

```csharp
var collection = new SqlBreakdownCollection();

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

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

### Removing Items

```csharp
// 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

```csharp
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:

```csharp
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:

```csharp
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

```csharp
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

```csharp
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

```csharp
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

```csharp
// 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

```csharp
// 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

```csharp
// 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

```csharp
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

```csharp
// 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

```csharp
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:

```csharp
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