ADO.NET Multiple Queries execution on the fly

Question

I'm doing a little app where a user enters a block of queries and then executes them.

The thing is that I want to show relevant information, like for example if he inputs something like:

SELECT * FROM server;
UPDATE server SET name = 'Kojak';

It gets:

The rows selected
The number of rows affected by the UPDATE

My printing loop looks like:

reader = cmd.ExecuteReader();
do
{

    while (reader.Read())
    {
        if (!(reader.RecordsAffected > 0))
        {
            for (int i = 0; i < reader.FieldCount; i++)
                host.WriteLine("Field " + i + ": " + reader[i].ToString());
        }
        else {
            host.WriteLine(reader.RecordsAffected.ToString() + " Affected rows.");
        }
    }                    
} while (reader.NextResult());
host.WriteLine("Block executed successfuly");

The thing is that I can't manage to make the difference between SELECTs and UPDATEs, because reader.Read() returns FALSE when it reaches the second query. How can I solve that and be able to get the number of affected rows when there's a UPDATE/DELETE/INSERT query ?

Thanks.

David · Accepted Answer · 2010-11-19 16:32:45Z

1

You can't use cmd.ExecuteReader() to execute non queries. You need to use ExecuteNonQuery().

I would imagine you will need to parse the list of SQL statements to determnine what sort of comamnd it is (e.g. SELECT or UPDATE), and call the appropriate method.

answered Nov 19, 2010 at 16:32

David

16.2k23 gold badges96 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CoolStraw Over a year ago

Damn... that's what I was afraid of... Thank you David. By the way, I can still use ExecuteReader and I can also get the Affected rows value but it gets raised as long as the single statements are being executed so there's no way to know for example which query generated which number of affected rows. In JDBC this is possible.. hehe

David Over a year ago

If you're saying that ExecuteReader() will in fact execute a non-SELECT query (I've never tried it), then my answer is wrong. I guess the problem is that for non-queries, ExecuteReader() returns false, so the following line doesn't get executed. I can't help but wonder whether a different type of loop which always executes at least once would solve the problem...

abatishchev · Accepted Answer · 2010-11-22 09:41:22Z

@David: here's how I did it:

public int Execute(String block, dbStudio.components.DbsTab host)
{
    this.Connect();
    DbCommand cmd = this.dbConnection.CreateCommand();
    DbDataReader reader = null;

    cmd.CommandText = block;
    int ret = -1;

    try
    {
        long ticks = DateTime.Now.Ticks;
        reader = cmd.ExecuteReader();
        do
        {
            while (reader.Read())
            {
                String str = "";
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    //host.WriteLine("Field " + i + ": " + reader[i].ToString());
                    str = str + reader[i].ToString() + "    ";
                }
                host.WriteLine(str);
            }
        } while (reader.NextResult());

        host.WriteLine("Block executed successfuly");
        host.WriteLine(reader.RecordsAffected.ToString() + " Affected rows.");
        host.WriteLine(((DateTime.Now.Ticks - ticks) / 1000).ToString() + "ms");

        ret = reader.RecordsAffected;
    }
    catch (Exception e)
    {
        host.WriteLine(e.Message);
    }
    finally
    {
        this.Disconnect();
    }
    return ret;
}

Collectives™ on Stack Overflow

ADO.NET Multiple Queries execution on the fly

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related