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;
}
share|improve this question
It looks like what you have is a stored procedure to extract column names from a given table. Are you trying, instead, to extract column values from that tables? If so, do you have a table schema available for it? It will be easier to adequately answer your question with that. – Michael Todd Jan 4 '10 at 0:38
The @TableName parameter contains the name of the table that I want to retrieve column names from. So I am trying to get the column values from the @TableName table. That is why I call it column names, because the column values I get from this table are actually column names. Make sense? I'm not sure what u mean by table schema. What do u recommend? I'm also trying to get the count for total number of columns. – salvationishere Jan 4 '10 at 0:50
Just to clarify, if you use the code above you will NOT be getting the values from the columns, you will only be getting the column names. If that's not what you want, you need to do things differently – Michael Todd Jan 4 '10 at 1:07
I'm not sure what u mean, Michael. My column values are column names. See my above comment for explanation of this. – salvationishere Jan 4 '10 at 1:10
Ah. Got it. Just making sure you knew that the values you're getting are actually column names. – Michael Todd Jan 4 '10 at 1:14

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
I tried that before, but I got the following exception: + dr.GetString(0) 'dr.GetString(0)' threw an exception of type 'System.InvalidOperationException' string {System.InvalidOperationException} – salvationishere Jan 4 '10 at 0:45
No, I actually should have more than one row cause should have one row for each table column, so a total of four rows. Should I make this a "while" instead? – salvationishere Jan 4 '10 at 0:56
I tried this but it threw an exception: - base {"Invalid attempt to read when no data is present."} System.SystemException {System.InvalidOperationException} And: - dr[0] 'dr[0]' threw an exception of type 'System.InvalidOperationException' object {System.InvalidOperationException} Here is the code I added to my DisplayTableColumns method just before the "return dr" statement at the end: while (dr.Read()) { string ocn = dr.GetString(0); int nr = dr.GetInt32(1); } – salvationishere Jan 4 '10 at 1:07
Thanks, Matt! This worked for me now! – salvationishere Jan 4 '10 at 1:34

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.