Parsing SQL Query and pull out column name and Table name

Question

I have a Query Script like this:

SELECT View1.OrderDate,View1.Email,SUM(View1.TotalPayments) FROM dbo.View1
WHERE (View1.OrderStatus = 'Completed') 
GROUP BY View1.OrderDate,View1.Email
HAVING
(SUM(View1.TotalPayments) > 75);

Is there any approach that we can pull some key information out from SQL query? such as table name and column name ,I have 2 question:

1. I did search I found some parser such as ANTLR , but I could not find documentation that explain the using of this parser in C# language.
2. Is there any way we can use Entity Frame Work to parsing sql query? My queries are fully dynamic and they are created at run time

Answer 1

I think the best answer is going to be to use the Irony parser: http://irony.codeplex.com/

Hanselman has a great link to how to use it to parse SQL: http://www.hanselman.com/blog/TheWeeklySourceCode59AnOpenSourceTreasureIronyNETLanguageImplementationKit.aspx

I hope this helps, and best of luck!

Answer 2

You could use some of the system tables to get at the information you are looking for.

select p.name ParentTable, r.name ReferencedTable, k.name KeyName
from sys.foreign_keys k
join sys.tables p on k.parent_object_id = p.object_id
join sys.tables r on k.referenced_object_id = r.object_id

Depending on how consistent your database is you can make assumptions to what the Key Name would be. So if the reference table was [User] you could assume that you are referencing the UserId, if you have multiple keys in your table this wouldn't be the answer you are looking for.

Answer 3

Elements can be retrieved and edited by using the SQLParser "Carbunql".

https://github.com/mk3008/Carbunql

Sample

Below is an example to get the column name and table name.

using Carbunql;

var sq = new SelectQuery(@"SELECT View1.OrderDate,View1.Email,SUM(View1.TotalPayments) FROM dbo.View1
WHERE (View1.OrderStatus = 'Completed') 
GROUP BY View1.OrderDate,View1.Email
HAVING
(SUM(View1.TotalPayments) > 75);");

Console.WriteLine("columns");
sq.SelectClause!.Items.ForEach(item =>
{
    if (string.IsNullOrEmpty(item.Alias))
    {
        //If there is no alias name, return the command
        Console.WriteLine("    " + item.ToOneLineCommand().CommandText);
    }
    else
    {
        Console.WriteLine("    " + item.Alias);
    }
});

Console.WriteLine("table");
var table = sq.FromClause!.Root;
if (string.IsNullOrEmpty(table.Alias))
{
    Console.WriteLine("    " + table.ToOneLineCommand().CommandText);
}
else
{
    Console.WriteLine("    " + table.ToOneLineCommand().CommandText + " as " + table.Alias);
}

Results

columns
    OrderDate
    Email
    SUM(View1.TotalPayments)
table
    dbo.View1 as View1

Remarks

• The third column is a function and has no alias name, so there is no column name.
• Getting the table name is cumbersome (because the FROM clause can also specify a subquery).

Answer 4

You can build dynamic queries in entity framework like this

If(case1)
{
    var query = db.x.where(x => x).toList();
}
else
{
   var query = db.x.where(y => y).toList()
}