CODEWITHSUNDEEP
c#sql-serverc#-4.0sqldatareader
Skullomania
Skullomania

Reputation: 2215

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

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?

Upvotes: 1

Views: 1423

Answers (4)

TYY
TYY

Reputation: 2716

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

Upvotes: 0

Steve
Steve

Reputation: 216303

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.

Upvotes: 6

Marc Gravell
Marc Gravell

Reputation: 1063198

I have have to give a dapper answer 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.

Upvotes: 4

Servy
Servy

Reputation: 203836

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.

Upvotes: 1

Related Questions