Anders Hesselbom

Parse Transact SQL

2016-01-07

To parse T-SQL from C#, add a reference to the following libraries (listed under Extensions, not under Framework):

Microsoft.Data.Schema.ScriptDom
Microsoft.Data.Schema.ScriptDom.Sql

My examples imports the following namespaces:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;
using System.IO;

This code shows how to parse a boolean expression:

//Parse
var p = new TSql100Parser(false);
IList<ParseError> err;
var sql = "(a > 10) and (i < 10)";
var exp = p.ParseBooleanExpression(new StringReader(sql), out err);

//Errors? Empty if none.
Console.WriteLine($"{err.Count} errors.");
err.ToList().ForEach(x =>
   Console.WriteLine(
      $"Error \"{x.Message}\" at line {x.Line}."));

//Result. Null if errors.
if (!(exp == null))
   exp.ScriptTokenStream.ToList().ForEach(x =>
      Console.WriteLine(
         $"Token type: {x.TokenType}, source: {x.Text}"));

If you want to parse a complete Transact SQL program, call the ParseStatementList function instead of the ParseBooleanExpression function.

//Parse
var p = new TSql100Parser(false);
IList<ParseError> err;
var sql = "SELECT EmployeeID, FirstName FROM dbo.Employees WHERE FirstName LIKE 'N%'";
var exp = p.ParseStatementList(new StringReader(sql), out err);

Console.WriteLine($"{err.Count} errors.");
err.ToList().ForEach(x =>
   Console.WriteLine(
      $"Error \"{x.Message}\" at line {x.Line}."));

//Result. Null if errors.
if (!(exp == null))
   exp.ScriptTokenStream.ToList().ForEach(x =>
      Console.WriteLine(
         $"Token type: {x.TokenType}, source: {x.Text}"));

This is the expected output:

Categories: C#