Get SQL query from user and run it against SQL Server

Question

I want to develop very small application by C# which get some SQL query from user and execute it against specified SQL Server.

SQL Server and database must specified by user thus every things can change. My problem is user can enter various type of SQL queries, and every king of it should run in its own way.

For example

SELECT * FROM mytable

and

UPDATE mytable 
SET city = "NY" 
WHERE name = "tom"

can not execute in the same way.

I think I need to recognize user query type in my code, Is there any way to recognize it or any better way to run any possible query?

Bogdan Sahlean · Accepted Answer · 2015-08-01 14:43:41Z

I would use TSqlPerser.ParseStatementList method from Microsoft.SqlServer.TransactSql.ScriptDom namespace (reference | download #1 - Nuget download #2 - Microsoft® SQL Server® 2014 Transact-SQL ScriptDom ):

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.TransactSql.ScriptDom;
using System.IO;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            TextReader rdr = new StreamReader(new MemoryStream(
                Encoding.UTF8.GetBytes(
                    @"/* comment */ 
                    WITH CteUpdate 
                    AS
                    (
                        SELECT * FROM Table1 
                        WHERE Col1 = 1
                    )
                    UPDATE CteUpdate SET city = ""NY"" WHERE name = ""tom""")
                ));
            TSql110Parser parser = new TSql110Parser(true);

            IList<ParseError> errors;

            StatementList stmtList = parser.ParseStatementList(rdr, out errors);
            // Process errors
            foreach(TSqlStatement stmt in stmtList.Statements)
            {
                Console.WriteLine("Statement type {0}", stmt.GetType());
                if (stmt is SelectStatement)
                {
                    //Process SELECT statment
                }
                else if (stmt is UpdateStatement)
                {
                    //Process UPDATE statment
                    UpdateStatement stmtUpdate = (UpdateStatement)stmt;
                    NamedTableReference tabRef = (NamedTableReference)stmtUpdate.UpdateSpecification.Target;
                    Console.Write(" > UPDATE statement > target object {0}", tabRef.SchemaObject.BaseIdentifier.Value);
                }
                else //Process other statments
                {
                    throw new NotImplementedException();
                }

            }
        }
    }

}

Output:

Statement type Microsoft.SqlServer.TransactSql.ScriptDom.UpdateStatement > UPDATE statement > target object CteUpdate

Glorfindel · Accepted Answer · 2015-08-01 11:04:37Z

2

(This answer assumes you use the classes available in the System.Data.SqlClient namespace.)

The simplest, but not fool-proof method would be to check the first word - if it is (case insensitive) equal to SELECT, you can use ExecuteReader() - if it is not, you can use ExecuteNonQuery().

Another option is just to ExecuteNonQuery() anyway. This will return -1 if you pass a SELECT statement as CommandText. Upon detecting this, you could call ExecuteReader(). However, other queries like BEGIN TRANSACTION will also return -1.

answered Aug 1, 2015 at 11:04

Glorfindel

22.8k13 gold badges97 silver badges124 bronze badges

Comments

Dan Guzman · Accepted Answer · 2015-08-01 12:14:34Z

2

You don't need to create different query types in your code. The SqlCommand ExecuteReader method can be used for all DML and DDL statements. If no results are returned by a statement, the SqlDataReader Read method will return false. You also need to invoke the SqlDataReader.NextResult method until it returns false to return the results from multiple statements.

var reader = command.ExecuteReader();
do{
    while(reader.Read())
    {
        //process resultset here
    }
}while(reader.NextResult());

edited Aug 1, 2015 at 12:14

answered Aug 1, 2015 at 12:06

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

Comments

Zohar Peled · Accepted Answer · 2015-08-01 11:10:37Z

0

I would simply give the user 2 execute buttons, one for non-query and one for reader.
If you trust the user to write sql statements, you can trust the user to know what button to click.

The reason for not attempting to anayze the sql statement yourself is very simple: it's possible to write something like exec stp_doesThisProcedureSelectsOrUpdates, so you simply can't tell...

answered Aug 1, 2015 at 11:10

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

Collectives™ on Stack Overflow

Get SQL query from user and run it against SQL Server

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related