How to define variables while reading them from a SqlDataReader (returning all rows)

Question

I have a table that has 14 rows in it that i want to return as well as declare each cell as a variable. Here is what I have tried:

using (SqlConnection conn1 = new SqlConnection(ConfigurationManager.ConnectionStrings["2012SSMS"].ConnectionString))
        {
            SqlCommand cmd1 = new SqlCommand(@"SELECT [Question], [Answer] 
            from [MyTable]", conn1);
            conn1.Open();
            using (SqlDataReader reader1 = cmd1.ExecuteReader())
            {
                while (reader1.HasRows)
                {
                    reader1.GetName(0);
                    reader1.GetName(1);

                    while(reader1.Read())
                    {
                        string Question(1) = reader1.GetString(0); //errors here
                        string Answer(1) = reader1.GetString(1); //and here
                    }
                    reader1.NextResult();
                }
            }
}

my hope is to return the declared variables as Question1 - Question14 and Answer1 - Answer14. How do I go about doing this?

You have exactly 14 questions and each question has its answer in the same row? There is no possibility to have more or less questions? — Steve
– Steve, Commented Jun 2, 2014 at 19:36
//my hope is to return the declared variables as Question1 - Question14 and Answer1 - Answer14. // That is not a wise thing to desire. It's too fickle. — granadaCoder
– granadaCoder, Commented Jun 2, 2014 at 21:15

Steve · Accepted Answer · 2014-06-02 19:49:14Z

6

Well, if you want to use 14 different names, you could define 14 string variables for the questions and 14 string variable for the answers. But this is frankly ridicolous when you have at your disposal Lists and other collection classes.

For example

Define a class for your question/answer

public class QuestionAnswer
{
     public string Question {get;set;}
     public string Answer {get;set;}
}

then modify your code to create a List<QuestionAnswer>

List<QuestionAnswer> listOfQuestionsAndAnswers = new List<QuestionAnswer>();
using (SqlConnection conn1 = new SqlConnection(ConfigurationManager.ConnectionStrings["2012SSMS"].ConnectionString))
{
    SqlCommand cmd1 = new SqlCommand(@"SELECT [Question], [Answer] 
                                       from [MyTable]", conn1);
    conn1.Open();
    using (SqlDataReader reader1 = cmd1.ExecuteReader())
    {
           while(reader1.Read())
           {
               QuestionAnswer qa = new QuestionAnswer();
               qa.Question = reader1.GetString(0); 
               qa.Answer = reader1.GetString(1); 
               listOfQuestionsAndAnswers.Add(qa);
           }
    }
}

Now you could use any question in the List using the following syntax

string aQuestion = listOfQuestionsAndAnswers[0].Question;
string anAnswer = listOfQuestionsAndAnswers[0].Answer;

And, finally, I really suggest you to spend a bit of your time to learn how to use Dapper as shown in the answer of Mr. Gravell.

edited Jun 2, 2014 at 19:49

answered Jun 2, 2014 at 19:40

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marc Gravell Over a year ago

It makes me very happy that somebody else also couldn't think of a better name than QuestionAnswer - naming stuff is hard!

Steve Over a year ago

Well, I have not even tried, the only thing more difficult for me is to find the proper icon for a button.

TylerH · Accepted Answer · 2025-04-23 15:33:51Z

4

I have to give an answer using my Dapper library here... to show how much unnecessary code you are writing:

public class QuestionAnswer {
    public string Question {get;set;}
    public string Answer {get;set;}
}
...
var pairs = conn1.Query<QuestionAnswer>("select Question, Answer from MyTable")
       .ToList();

Now: pairs[0].Question is the first question, and pairs[3].Answer is the 4th answer.

edited Apr 23 at 15:33

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Jun 2, 2014 at 19:42

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

Servy · Accepted Answer · 2014-06-02 19:36:32Z

1

What you want to do here is return a collection of objects, where each of those objects has a Question and Answer property.

In each iteration of the loop you can create a new instance of the custom type that you have defined to hold onto these two values, and then you can add it to your data structure of choice.

answered Jun 2, 2014 at 19:36

Servy

204k27 gold badges347 silver badges470 bronze badges

Comments

TYY · Accepted Answer · 2014-06-02 19:49:24Z

0

You want to use a dynamic to do some thing of this nature. I have not tested the code, but conceptually this should work.

public class DynamicDataRecord : DynamicObject 
{
    private IDataRecord _dataRecordFromReader;

    public DynamicDataRecord(IDataRecord dataRecordFromReader) 
    { 
        _dataRecordFromReader = dataRecordFromReader; 
    }

    public override bool TryGetMember(GetMemberBinder binder, out dynamic result) 
    {
        result = _dataRecordFromReader[binder.Name];
        return result != null;
    }
}

And then in the place you iterate through the reader you can do something like the below

List<DynamicDataRecord> records = new List<DynamicDataRecord>();
using (SqlDataReader reader1 = cmd1.ExecuteReader()) 
{
    foreach(var record in reader)
    {
        records.Add( new DynamicDataRecord(record));
    }
}

And then you should be able to do something like this.

records[0].Question

record[0].Answer

edited Jun 2, 2014 at 19:49

answered Jun 2, 2014 at 19:41

TYY

2,7161 gold badge16 silver badges14 bronze badges

1 Comment

Olivier Jacot-Descombes Over a year ago

And where does the row data get into the DynamicDataRecord?

Collectives™ on Stack Overflow

How to define variables while reading them from a SqlDataReader (returning all rows)

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related