How do you get the values out of SqlDataReader?

Question

I have a stored procedure that returns a list, but how can I put the result into a list in C#?

 using(SqlConnection sqlConnectionString = new SqlConnection(ConfigurationManager.ConnectionStrings["EfenKaConnectionString"].ConnectionString))
 using(SqlCommand cmd = new SqlCommand("SP_ListMonths", sqlConnectionString))
 {
     SqlDataReader reader;
     cmd.CommandType = CommandType.StoredProcedure;

     cmd.Parameters.Add("@MONTH", SqlDbType.Int).Direction = ParameterDirection.Output;

     sqlConnectionString.Open();

     cmd.ExecuteNonQuery();

     int defualtMonth = Convert.ToInt32(cmd.Parameters["@MONTH"].Value);
     reader = cmd.ExecuteReader();
     sqlConnectionString.Close();
 }

How can I acces the values from reader?

There is plenty of examples on Google. Please do your research — Alex, Apr 27 '16 at 07:49
Why are you calling `ExecuteNonQuery` and then `ExecuteReader` on the same `SqlCommand` object? Also, your `sqlConnectionString.Close();` is unnecessary (the `using` block will take care of that) and could result in an "Object already disposed" error. — Tim, Apr 27 '16 at 07:49
Side note: You might want to rename your stored proc. [`CREATE PROCEDURE`](https://msdn.microsoft.com/en-GB/library/ms187926.aspx): "Avoid the use of the **sp_** prefix when naming procedures. This prefix is used by SQL Server to designate system procedures." — Damien_The_Unbeliever, Apr 27 '16 at 07:51
and here's the [t-sql rule](https://msdn.microsoft.com/en-us/library/dd172115(v=vs.100).aspx) according to this naming issue. Longer article: [Is the sp_ prefix still a no-no?](http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix) — Tim Schmelter, Apr 27 '16 at 07:53

score 3 · Accepted Answer · answered Apr 27 '16 at 08:00

3

Don't call cmd.ExecuteNonQuery(); Try this

    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        object obj1 = reader["ColumnName1"];
        object obj2 = reader["ColumnName2"];  
        object obj3 = reader["ColumnName3"];
    }

answered Apr 27 '16 at 08:00

CodeGuru

2,722
6
36
52

2

Just to add to Popeyes good answer as I think you don't understand the difference between a query and non-query. A Query in SQL talk is to put simply a "select" statement, so it returns a result set of data from the DB. A Non Query is other types of commands that don't data from the db. So this includes "insert", "update", "delete" etc. So basically you should only ever execute one or the other and not both on the same statement. – jason.kaisersmith Apr 27 '16 at 08:50
1

Thanks @jason.kaisersmith for details. Its very clear and good. – CodeGuru Apr 27 '16 at 08:52

How do you get the values out of SqlDataReader?

1 Answers1