需要有关数据库连接和查询代码的帮助 [英] Need help with database connection and query code

问题描述

在下面的代码中，cmdquery有效，而hrquery无效.如何获取另一个查询以填充网格视图?我需要建立一个新的连接还是使用相同的连接?你们能帮我吗?我是C#和ASP的新手.这是我整理的一些意大利面条代码.可能全都错了，因此，如果您有更好的方法来分享此内容，就可以随意分享.

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

推荐答案

您的数据访问代码通常应如下所示:

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

请注意，这只是常规模板.您将需要针对您的确切需求对其进行一些调整.从中获得的重要信息是using块，可以正确创建和处理您的连接对象，以及防止SQL注入的参数.

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.

关于连接问题，有一些例外，但是通常一次只能将一个连接用于一个活动结果集.因此，您可以可以重用原始代码中的同一个conn对象，但前提是您必须完全按照上一条命令完成操作.如果需要，也可以打开两个连接.不过，最佳选项是在可能的情况下将相关查询合并到单个sql语句中.

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.

这篇关于需要有关数据库连接和查询代码的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！