从 SqlDataReader 命令中选择列值

发布于 2024-08-16 11:31:24 字数 1433 浏览 7 评论 0原文

我有一个表，其中包含 SQL 中的两列，我想从中提取值。但问题是我不知道如何在 C# 代码中检索第 2 列的值。我使用的是 SQL Server 2008 和 VS。我必须在 SP 中使用 OUTPUT 参数吗？如果是这样，我该如何指定这个 SP？我已经尝试过 OUTPUT 参数，但无法无错误地创建 OUTPUT 参数，因为我还尝试选择 COLUMN_NAME。此查询返回 4 列，我也只想获取这个列数。应该有一种更简单的方法来做到这一点，不是吗？这适用于 SQL，但不适用于 C#。

这是我的 SP：

CREATE PROCEDURE [dbo].[getColumnNames]
    @TableName varchar(25) 
AS
    SELECT name 'COLUMN_NAME', (
         SELECT COUNT(ID)
         FROM syscolumns WHERE id = (
             SELECT id
             FROM sysobjects
             WHERE name= @TableName)) 'ROW_COUNT'
    FROM syscolumns
    WHERE id = (SELECT id FROM sysobjects WHERE name= @TableName)
    RETURN

这是我调用此 SP 的 C# 代码：

public static SqlDataReader DisplayTableColumns(string tt)
{
    SqlDataReader dr = null;
    string TableName = tt;
    string connString = "Data Source=.;AttachDbFilename=\"C:\\Program Files\\Microsoft SQL Server\\...";
    string errorMsg;

    try
    {
        SqlConnection conn2 = new SqlConnection(connString);
        SqlCommand cmd = conn2.CreateCommand();
        cmd.CommandText = "dbo.getColumnNames";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn2;
        cmd.Parameters.Add(new SqlParameter("@TableName", TableName));
        conn2.Open();
        dr = cmd.ExecuteReader();
    }
    catch (Exception ex)
    {
        errorMsg = ex.Message;
    }
    return dr;
}

原文

I have a table containing two columns in SQL that I want to extract values from. The problem though is I do not know how to retrieve column 2's values in C# code. I am using SQL Server 2008 and VS. Do I have to use an OUTPUT parameter in the SP? If so, how would I specify this SP? I have experimented with OUTPUT parameters, but I am not able to create an OUTPUT parameter without errors because I am also trying to select the COLUMN_NAME. There are 4 columns returned from this query and I just want to get this number of columns also. There should be a much simpler way of doing this, no? This works in SQL, but not in C#.

Here is my SP:

CREATE PROCEDURE [dbo].[getColumnNames]
    @TableName varchar(25) 
AS
    SELECT name 'COLUMN_NAME', (
         SELECT COUNT(ID)
         FROM syscolumns WHERE id = (
             SELECT id
             FROM sysobjects
             WHERE name= @TableName)) 'ROW_COUNT'
    FROM syscolumns
    WHERE id = (SELECT id FROM sysobjects WHERE name= @TableName)
    RETURN

And here is my C# code calling this SP:

public static SqlDataReader DisplayTableColumns(string tt)
{
    SqlDataReader dr = null;
    string TableName = tt;
    string connString = "Data Source=.;AttachDbFilename=\"C:\\Program Files\\Microsoft SQL Server\\...";
    string errorMsg;

    try
    {
        SqlConnection conn2 = new SqlConnection(connString);
        SqlCommand cmd = conn2.CreateCommand();
        cmd.CommandText = "dbo.getColumnNames";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn2;
        cmd.Parameters.Add(new SqlParameter("@TableName", TableName));
        conn2.Open();
        dr = cmd.ExecuteReader();
    }
    catch (Exception ex)
    {
        errorMsg = ex.Message;
    }
    return dr;
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

樱娆 2024-08-23 11:31:24

要从数据读取器读取列，您只需指定其在结果集中的索引即可。因此，要从上面编写的查询中获取值，您可以编写：

// straight after this line:
dr = cmd.ExecuteReader();

if (dr.Read()) // you only have one row so you can use "if" instead of "while"
{
    var columnName = dr.GetString(0);
    var rowCount = dr.GetInt32(1);
}

编辑糟糕！你需要先阅读。我已经更新了代码。

To read a column from a data reader, you can simply specify its index in the result set. So to get the values from the query you've written above, you can write:

// straight after this line:
dr = cmd.ExecuteReader();

if (dr.Read()) // you only have one row so you can use "if" instead of "while"
{
    var columnName = dr.GetString(0);
    var rowCount = dr.GetInt32(1);
}

Edit Oops! You need to read first. I have updated the code.

回复收藏 0 原文

~没有更多了~