Using stream reader to catch values returned by sql statemtnt c#

Question

guys i have an SQL statement returning more than 1 value. I am trying to use the StreamReader to get the values into an array as below

 string sql = "select distinct COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME=' " + table + "' and CONSTRAINT_NAME like 'PK_%'";
            SqlConnection conn2 = new SqlConnection(cnstr.connectionString(cmbDatabase.Text));
            SqlCommand cmd_server2 = new SqlCommand(sql);
            cmd_server2.CommandType = CommandType.Text;
            cmd_server2.Connection = conn2;
            conn2.Open();

            //reader_sql = new StreamReader();
            SqlDataReader reader_sql = null;
            string[] colName = new string[200];
            reader_sql = cmd_server2.ExecuteReader();
            while (reader_sql.Read());

            for (int rr = 0; rr < 20; rr++)
            {
                colName[rr] = reader_sql["COLUMN_NAME"].ToString();
            }

It is not working, what am I doing wrong guys ?

How it's not working? No results are returned? Exception is thrown? — Kamil Budziewski, Sep 02 '13 at 07:56
Why are you adding the same `COLUMN_NAME` value 20 times to a string array for each row returned? This is why I asked if you are getting more than 10 `COLUMN_NAME` values back, because that will blow up the `string[]` set to 200 items. — Karl Anderson, Sep 02 '13 at 08:00

score 3 · Answer 1 · answered Sep 02 '13 at 07:58

3

You've got a stray ; turning your while into a tight loop, so instead try:

        while (reader_sql.Read())

        for (int rr = 0; rr < 20; rr++)
        {
            colName[rr] = reader_sql["COLUMN_NAME"].ToString();
        }

answered Sep 02 '13 at 07:58

Rowland Shaw

37,700
14
97
166

Steve · Answer 2 · 2013-09-02T08:43:19.067

2

Perhaps you should remove the semicolon at the end of Read

while (reader_sql.Read())
{
    for (int rr = 0; rr < 20; rr++)
       colName[rr] = reader_sql["COLUMN_NAME"].ToString();
}

However, if your intention is to retrieve the columns belonging to the primary key, your code is wrong because you add 20 times the same primary key column, then repeat the same for the remaining columns ending with an array of 20 strings all equals to the last column in the primary key set. I think you should change your code to use a List(Of String) instead of a fixed length array and let the reader loop correctly on the primary key columns retrieved

List<string> pks = new List<string>();
while (reader_sql.Read())
{
    pks.Add(reader_sql["COLUMN_NAME"].ToString());
}

EDIT: I have just noticed that your query contains a space before the table name. The string concatenation then produces an invalid table name, the query is syntactically right but doesn't return any data

string sql = "select distinct COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE " + 
             "where TABLE_NAME='" + table + "' and CONSTRAINT_NAME like 'PK_%'";
                                ^ space removed here

And while you are at it, remove the string concatenation and use a parameterized query.....

string sql = "select distinct COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE " + 
             "where TABLE_NAME=@tName and CONSTRAINT_NAME like 'PK_%'";
SqlCommand cmd_server2 = new SqlCommand(sql, connection);
connection.Open();
cmd_server2.Parameters.AddWithValue("@tName", table);

edited Sep 02 '13 at 08:43

answered Sep 02 '13 at 07:58

Steve

213,761
22
232
286

the program exits after the reader_sql = cmd_server2.ExecuteReader(); line. – Anoushka Seechurn Sep 02 '13 at 08:16
Are you sure there is a Primary Key in your table? And the name begins with the letters 'PK'? – Steve Sep 02 '13 at 08:21
Yes, i tested the sql on the sql studio it returns 2 names. – Anoushka Seechurn Sep 02 '13 at 08:34
Just noticed now. Your query contains a space before the table name. Remove it – Steve Sep 02 '13 at 08:40
Done .. but still the progress jumps away the while loop.Still not working. – Anoushka Seechurn Sep 02 '13 at 08:47
1

I have no more idea then. The last explanation that comes to mind is that you are working on a different database than the one you test with Sql Management Studio. Try to check your connection strings. I have tested your code on a database of mine and it works correctly after the changes suggested in my answer. – Steve Sep 02 '13 at 09:18
i solved it. I had a problem indeed at the level of my table name. Thanks for consideration. – Anoushka Seechurn Sep 02 '13 at 09:35

score 2 · Answer 3 · answered Sep 02 '13 at 08:00

2

You get the exception because

while (reader_sql.Read());

should be

while (reader_sql.Read())
{
    for (int rr = 0; rr < 20; rr++)
     {
         colName[rr] = reader_sql["COLUMN_NAME"].ToString();
     }
}

answered Sep 02 '13 at 08:00

Ehsan

31,833
6
56
65

The progress of the program doest not enter this while loop. It hops to the close connection part. Still no results !! – Anoushka Seechurn Sep 02 '13 at 08:10

Using stream reader to catch values returned by sql statemtnt c#

3 Answers3