Parsing SQL Code in C#

Parsing SQL code in C#

[Warning: answer may no longer apply as of 2021]

Use Microsoft Entity Framework (EF).

It has a "Entity SQL" parser which builds an expression tree,

using System.Data.EntityClient;
...
EntityConnection conn = new EntityConnection(myContext.Connection.ConnectionString);
conn.Open();
EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = @"Select t.MyValue From MyEntities.MyTable As t";
var queryExpression = cmd.Expression;
....
conn.Close();

Or something like that, check it out on MSDN.

And it's all on Ballmers tick :-)

There is also one on The Code Project, SQL Parser.

Good luck.

C# parse SQL statement to find all INSERT/UPDATE/DELETE tables used in stored procedures

The SMO model exposes elements of the syntax tree. So instead of assuming a token by position, as in

UpdateSpecification?.Children?.FirstOrDefault();

look up the corresponding property in the documentation. For the update clause, the target table (or updatable view) can occur in different positions. Take this syntax:

UPDATE tablename SET column=value WHERE conditions

which is represented as

var targettable = ins?.UpdateSpecification?.Target?.ScriptTokenStream?.FirstOrDefault()?.Text;

in the SMO model. Whereas, a syntax unique to tsql,

UPDATE t SET t.columnname=value FROM tablename t WHERE conditions 

will have its list of tables in the FROM clause.

Regarding the other two DML statements you mentioned: DELETE is the same because they share a common base class, DeleteInsertSpecification (Target).

For INSERT, there is the Target as well, and if its InsertSource is of type SelectInsertSource, this may be based on any number of tables and views too.

Parsing a SQL string in c#

If you just want to validate the syntax. You can use Microsoft.Data.Schema.ScriptDom for this.

using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;

.....

        string sql = "SELECT * FROM SomeTable WHERE (1=1";
        var p = new TSql100Parser(true);
        IList<ParseError> errors;

        p.Parse(new StringReader(sql), out errors);


        if (errors.Count == 0)
            Console.Write("No Errors");
        else
            foreach (ParseError parseError in errors)
                Console.Write(parseError.Message);

C# Parse SQL statement to use parameters

If your assignment is just writing a wrapper around a database so that other developers can send in their own SQL and get results then SQL injections are the "normal use case". There is just no way of knowing if a request is malicious or not. If you are allowed to run "good" code, you'll always be able to run "evil" code.

Parsing SQL Query and pull out column name and Table name

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!

How parse SQL to Datatable or array in c#?

var str = "select 'a1,a2' f1, 2 f2;";
Regex.Matches(str, "(^|[\\s,\\,]){1}(\\'.*\\'\\s*\\w+|[^\\,^']*)[\\,,$,;]")
        .Cast<Match>()
        .Select(m => m.Value.Split(new[] { ' '}, StringSplitOptions.RemoveEmptyEntries)
                            .First().Replace("\'",""))
        .ToArray();

---

Related Topics