Problem with multiple SqlDataReader and SqlCommand in a Class

Question

I have a problem with C#, I have a Class with a function for SqlDataReader and another for SqlCommand (the first one is just for read values from a DataBase and the second one is for INSERT, UPDATE, DELETE ... in the same DB).

The problem is, for the first part of the code (the login), I have to search the values from an Active Directory (it works), then I must see if the user has username and password in my own DB (it works), and then, if the user is not in the DB then I have to create it and get the ID, if it is already created then I just have to get the ID.

The problem is that I get this message :

InvalidOperationException was unhandled by user code

There already exist an Open DataReader associated with this Command, who as to be closed first.

There is the code :

Class.cs :

    private static string MyConnectionString = "THIS IS MY CONNECTION";
    private SqlConnection MyConnection = new SqlConnection(MyConnectionString);
    public SqlCommand MyCommand = new SqlCommand();
    public SqlDataReader MyReader = null;

    public void DBMyReader(String SqlQuery)
    {
        if (MyConnection.State != ConnectionState.Open)
            MyConnection.Open();

        MyCommand.Connection = MyConnection;
        MyCommand.CommandText = SqlQuery;
        MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public void DBMyUpdate(String SqlQuery)
    {
        if (MyConnection.State != ConnectionState.Open)
            MyConnection.Open();

        var cmdTest = new SqlCommand();

        cmdTest.Connection = MyConnection;
        cmdTest.CommandText = SqlQuery;
        cmdTest.ExecuteNonQuery();
    }

    public void DBMyInsert(String SqlQuery)
    {
        DBMyUpdate(SqlQuery);
    }

** Login.aspx.cs: **

                    MyClass.DBMyReader("SELECT util_codi,util_logi,util_nome FROM Tgep_util WHERE util_logi='"
                            + Session["username"].ToString() + "'");
            MyClass.MyReader.Read();

            if (!MyClass.MyReader.HasRows)
            {
                MyClass.MyReader.Close();
                MyClass.DBMyInsert("INSERT INTO Tgep_util(util_logi,util_nome) "
                            + "VALUES ('" + Session["username"].ToString() + "','" + Session["nome"].ToString() + "')");
            }

            MyClass.DBMyReader("SELECT util_codi,util_logi,util_nome FROM Tgep_util WHERE util_logi='"
            + Session["username"].ToString() + "'");

            MyClass.MyReader.Read();

            Session["user_id"] = MyClass.MyReader["util_codi"].ToString();

            Response.Redirect("FRM_Principal.aspx");

Edit : Update Code (Works for now)

Answer 1

The error means exactly what it says.. You have loaded a SQLCommand and started reading rows, and are now trying to do an insert. You need to close out that reader first, or use a new command.

in the DBMyUpdate function you could just create a new command:

public void DBMyUpdate(String SqlQuery)
{
    if (MyConnection.State != ConnectionState.Open)
        MyConnection.Open();
    var cmdUpdate = new SqlCommand();

    cmdUpdate.Connection = MyConnection;
    cmdUpdate.CommandText = SqlQuery;
    cmdUpdate.ExecuteNonQuery(CommandBehavior.CloseConnection);
}

edit: based on comments to this answer, it required using separate connections which seems odd/incorrect.

Answer 2

You can't use the same DataReader for insert/update that you used for insertion. You have to close the DataReader first before associating some other command with the DataReader.