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();

What is your question? If your code works, what do you want to know? — JustLoren
– JustLoren, Commented Jun 23, 2010 at 13:02
Not an answer to your question, but you should really read this: stackoverflow.com/questions/72394/… (primarily thinking about the SQL injection vulnerability that your code currently suffers from). — Fredrik Mörk
– Fredrik Mörk, Commented Jun 23, 2010 at 13:02

Joel Coehoorn · Accepted Answer · 2010-06-23 13:37:33Z

3

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.

edited Jun 23, 2010 at 13:37

answered Jun 23, 2010 at 13:19

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

1 Comment

Michael Quiles Over a year ago

Thank you for the response it's like Greek to me now, but maybe I'll understand it better down the road, I start advanced programming and advanced database design in college today so maybe that will help clear things up bit more.

JustLoren · Accepted Answer · 2010-06-23 13:05:03Z

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();

Please elaborate I would like to learn your time won't be wasted.

Collectives™ on Stack Overflow

Need help with database connection and query code

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related