Need help with database connection and query code

Question

In my code below, the cmdquery works but the hrquery does not. How do I get another query to populate a grid view? Do I need to establish a new connection or use the same connection? Can you guys help me? I'm new to C# and asp. Here's some spaghetti code I put together. It may all be wrong so if you have a better way of doing this feel free to share.

if (Badge != String.Empty)
{
    string cmdquery = "SELECT * from Employees WHERE Badge ='" + Badge + "'";
    string hrquery = "SELECT CLOCK_IN_TIME, CLOCK_OUT_TIME FROM CLOCK_HISTORY   WHERE Badge ='" + Badge + "'";

    OracleCommand cmd = new OracleCommand(cmdquery);
    cmd.Connection = conn;
    cmd.CommandType = CommandType.Text;
    conn.Open();

    OracleDataReader reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        this.xUserNameLabel.Text += reader["EMPLOYEE_NAME"];
        this.xDepartmentLabel.Text += reader["REPORT_DEPARTMENT"];               
    }

    OracleCommand Hr = new OracleCommand(hrquery);
    Hr.Connection = conn;
    Hr.CommandType = CommandType.Text;

    OracleDataReader read = Hr.ExecuteReader();

    while (read.Read())
    {
        xHoursGridView.DataSource = hrquery;
        xHoursGridView.DataBind();
    }
}
conn.Close();

Answer 1

I'm not even going to get into how you should be using usings and methods :p

if (Badge != String.Empty)
    {

        string cmdquery = "SELECT * from Employees WHERE Badge ='" + Badge + "'";
        string hrquery = "SELECT CLOCK_IN_TIME, CLOCK_OUT_TIME FROM CLOCK_HISTORY   WHERE Badge ='" + Badge + "'";

        OracleCommand cmd = new OracleCommand(cmdquery);
        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        conn.Open();

        OracleDataReader reader = cmd.ExecuteReader();


            while (reader.Read())
            {
                this.xUserNameLabel.Text += reader["EMPLOYEE_NAME"];
                this.xDepartmentLabel.Text += reader["REPORT_DEPARTMENT"];

            }


            OracleCommand Hr = new OracleCommand(hrquery);
            Hr.Connection = conn;
            Hr.CommandType = CommandType.Text;


            OracleDataReader read = Hr.ExecuteReader();

            //What's this next line? Setting the datasource automatically
            // moves through the data.
            //while (read.Read())
            //{
                                          //I changed this to "read", which is the
                                          //datareader you just created.
                xHoursGridView.DataSource = read;
                xHoursGridView.DataBind();
            //}


    }
    conn.Close();

Answer 2

Your data access code should generally look like this:

string sql = "SELECT * FROM Employee e INNER JOIN Clock_History c ON c.Badge = e.Badge WHERE e.Badge = @BadgeID";
using (var cn = new OracleConnection("your connection string here"))
using (var cmd = new OracleCommand(sql, cn))
{
    cmd.Parameters.Add("@BadgeID", OracleDbType.Int).Value = Badge;

    cn.Open();

    xHoursGridView.DataSource = cmd.ExecuteReader();
    xHoursGridView.DataBind();
}

Note that this is just the general template. You'll want to tweak it some for your exact needs. The important things to take from this are the using blocks to properly create and dispose your connection object and the parameter to protect against sql injection.

As for the connection question, there are exceptions but you can typically only use a connection for one active result set at a time. So you could reuse your same conn object from your original code, but only after you've completely finished with it from the previous command. It is also okay to open up two connections if you need them. The best option, though, is to combine related queries into single sql statement when possible.