C# & SQL Server : fetch data and then delete returns error

Question

I have the following script to fetch me some data from a database and then remove it:

public void checkDB()
{
    string query = "SELECT * FROM dbt";

    SqlCommand sqlCommand = new SqlCommand(query, conn);

    SqlDataReader reader;
    int id = -1;

    using (reader = sqlCommand.ExecuteReader())
    {
            if (reader.Read())
            {
                String data= reader["sdata"].ToString();
                Order o = new Order(reader["sdata"].ToString());
                o.prepareForScript();
                id = reader.GetSqlInt32(1).Value;
            }

            reader.Close();
    }

    if (id != -1)
    {
        string removeQuery = "DELETE FROM data WHERE ID=" + id;

        SqlCommand removeCMD = new SqlCommand(removeQuery, conn);

        removeCMD.ExecuteNonQuery();
    }
}

This code results in an exception

unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

with the aditional information that a reader is already associated with this connection. However as you can see the reader is both closed and inside a using loop meaning that it should definitly be closed. Anybody know how to fix this?

Try wrapping your SQL commands in a using also. see stackoverflow.com/questions/16985876/… — Mark
– Mark, Commented Aug 5, 2015 at 8:46
May be the conn problem. Have you tried to close the conn after SELECT query & open for DELETE query. — Harshit
– Harshit, Commented Aug 5, 2015 at 8:49
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Aug 5, 2015 at 9:05
@Marc_s I'm aware of the risk however I'm pulling an int from a database and then sending it back for deletion that shouldn't be a risk here right? — Thijser
– Thijser, Commented Aug 5, 2015 at 9:15

Neel · Accepted Answer · 2015-08-05 09:19:30Z

4

You need to dispose first SqlCommand as below :

using (SqlCommand sqlCommand = new SqlCommand(query, conn))
{
        SqlDataReader reader;
        int id = -1;

        using (reader = sqlCommand.ExecuteReader())
        {
            if (reader.Read())
            {


                String data= reader["sdata"].ToString();
                Order o = new Order(reader["sdata"].ToString());
                o.prepareForScript();
                id = reader.GetSqlInt32(1).Value;
            }
            reader.Close();
        }
}

Or

enable MultipleActiveResultSets. This way SQL Server allows several open data readers on a single connection.

But i would suggest not to use your SQL statements together to avoid Sql Injection.

edited Aug 5, 2015 at 9:19

answered Aug 5, 2015 at 8:43

Neel

11.7k3 gold badges46 silver badges63 bronze badges

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

Collectives™ on Stack Overflow

C# & SQL Server : fetch data and then delete returns error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related