none
Discovery if I should use ExecuteNonQuery or ExecuteReader RRS feed

  • Question

  • I have a admin screen in a website.

    In this screen is possible to execute sql commands (selects, updates... whatever)

    I have a method like this: public object ExecuteSql(string sql)
    today, this method works with selects commands, because I'm using ExecuteReader. 
    But I would like to perform updates and deletes.
    Are there a way to determine which ado.net method use: ExecuteNonQuery or ExecuteReader?

    At first moment I thought to verify if sql string starts with "select". But, for me, it sounds a little "crap".
    Ideas?

    Wednesday, March 4, 2015 1:50 PM

Answers

  • 1) This question is about ADO.NET, which is the "low level" data access API.  If you adopt Entity Framework you won't have to deal with data access at such a low level.  It really is much easier than what you are doing.

    2) The answer to your question is that ExecuteReader will always work.  It is the most general method, and will work fine whether or not the query returns results.  You can check the HasRows property to see if the command returned at least one resultset.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, March 4, 2015 3:20 PM

All replies

  • 1) This question is about ADO.NET, which is the "low level" data access API.  If you adopt Entity Framework you won't have to deal with data access at such a low level.  It really is much easier than what you are doing.

    2) The answer to your question is that ExecuteReader will always work.  It is the most general method, and will work fine whether or not the query returns results.  You can check the HasRows property to see if the command returned at least one resultset.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, March 4, 2015 3:20 PM
  • >>At first moment I thought to verify if sql string starts with "select". But, for me, it sounds a little "crap".

    How else are you supposed to know if it is a SELECT or UPDATE or INSERT statement that gets passed to the method? What you should do here is to expose one Select method that for example uses the ExecuteReader method and another Update method that uses the ExecuteNonQuery method.

    If you choose to stick with a single method for both reading and writing data you are stuck with your "crap" approach of trying to evaluate the string and look for the SELECT keyword in it. I agree it is not a very good solution and neither is it to expose a single method for both reading and writing data.

    Bottom line: Use two separate methods or you will have to determine if it is a SELECT statement that gets passed by evaluating the string argument one way or another.


    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question.

    Wednesday, March 4, 2015 3:49 PM
  • I tested here, and realized that ExecuteReader executes commands like insert or update...

    the code is something like that:

               
                var context = new DefaultContext();
                var initialState = context.Database.Connection.State;
    
                if (initialState != ConnectionState.Open)
                    context.Database.Connection.Open();
    
                using (var command = context.Database.Connection.CreateCommand())
                {
                    command.CommandText = sql;
    
                    using (var reader = command.ExecuteReader())
                    {
                        var result = new List<Dictionary<string, object>>();
    
                        foreach (var item in reader)
                        {
                            IDictionary<string, object> expando = new ExpandoObject();
    
                            foreach (PropertyDescriptor propertyDescriptor in TypeDescriptor.GetProperties(item))
                            {
                                var obj = propertyDescriptor.GetValue(item);
                                expando.Add(propertyDescriptor.Name, obj);
                            }
    
                            result.Add(new Dictionary<string, object>(expando));
                        }
    
                        if (initialState != ConnectionState.Open)
                            context.Database.Connection.Close();
    
                        return result;

    thx

    Wednesday, March 4, 2015 5:50 PM
  • I wouldn't read using the PropertyDescriptor stuff.  The column order isn't guaranteed, and the performance impact is unknown.  Also you might be vulnerable to bugs in the ICustomPropertyDescriptor implementation for various ADO.NET providers. 

    The gold-standard way to use the DataReader would be:

    using (var rdr = cmd2.ExecuteReader())
    {
    	var result = new List<Dictionary<string, object>>();
    	while (rdr.Read())
    	{
    		IDictionary<string, object> expando = new ExpandoObject();
    
    		for (int i = 0; i < rdr.FieldCount; i++)
    		{
    			var obj = rdr[i];
    			expando.Add(rdr.GetName(i), obj);
    		}
    		result.Add(new Dictionary<string, object>(expando));
    	}
    	. . .
    
    }
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, March 4, 2015 6:30 PM
  • command.CommandText =sql;

    That's a pure SQL injection attack if there ever was one if you plan on doing it up at th UI. If nothing else, you should learn how to use paramterized T-SQL or Stored Procedures.

    BTW, your post is off topic, as this is the ADO.NET Entity Framework ORM forum and not the ADO.NET  SQL Command Object and T_SQL forum.

    You may want to use the below forum

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

    Wednesday, March 4, 2015 6:39 PM