CODEWITHSUNDEEP
c#sql.netsql-serverexecute
Ashkan Rahmany
Ashkan Rahmany

Reputation: 132

Get SQL query from user and run it against SQL Server

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?

Upvotes: 0

Views: 324

Answers (4)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

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

Upvotes: 2

Dan Guzman
Dan Guzman

Reputation: 46233

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());

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82504

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...

Upvotes: 0

Glorfindel
Glorfindel

Reputation: 22651

(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.

Upvotes: 2

Related Questions