2

I have a list of id. Those are answer id. I hold as a list in the application. I just want to make request to SQL Server to get answers which matches the id's.

Do I need to use foreach loop in C#? How can I merge result in datatable or something? I need some help about this.

I want to get data with C# in an ASP.NET project.

List of id: <List>:

answer_id
---------
5
4
23
2
45
16

I want to get with C# and display in datatable: (expected result)

answer_id          answer
-------------------------------------
5                  It's true
4                  Maybe next time
23                 Man this is crazy
2                  Can i help you?
45                 Wtf..
16                 I can not believe..

Our select query must return the answer if there is a match. In this example let's say we found all the answers.

Simple query for one result:

SELECT answer_id, answer 
FROM answers 
WHERE answer_id = 5

Result:

  answer_id    answer
  -----------------------
    5          It's true
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hallowen
  • 133
  • 2
  • 14

2 Answers2

1

Assuming you are using ADO.NET, you will need to create a class that holds the properties of answer_id, and answer. In my example I am acting as if there is a class named Answer that has these values. I then start off by creating a DataAccess class like such: 

    public static class DataAccess
    {
        private static string reportConnectionString = "Data Source=;Initial Catalog=;Persist Security Info=;User ID=;Password=";

    public static class Answer
    {
       public int AnswerID {get; set;}
       public string Answer {get; set;}
    }

        public static bool GetRecords(int AnswerId, out List<Answer> lstAnswers, out string sMessage)
        {
            lstAnswers = new List <Answer>();
            AnswerID;
            sMessage = "";

            try
            {
                using(SqlConnection connection = new SqlConnection(reportConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = connection.CreateCommand())
                    {
command.CommandType = System.Data.CommandType.Text;
                        command.Parameters.AddWithValue("@id", AnswerID);

                        command.CommandText = @"select answer_id, answer from answers where answer_id = @id";

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.HasRows == false)
                            {
                                sMessage = "Could not find data";
                                return false;
                            }
                            while(reader.Read())
                            {
                                Answer answer= new Answer();

                                answer.answer_id= reader.GetInt32(reader.GetOrdinal("answer_id"));
                                answer.answer= reader.GetValue(reader.GetOrdinal("answer")).ToString();

                                // -- Adding single object to list 
                                lstAnswers.Add(log);
                            }
                        }
                    }
                }
                return true;
            }
            catch (Exception exc)
            {
                sMessage = exc.ToString();
                return false;
            }
        }
    }

If you have a list of answer_id you can create a foreach loop to loop through the list and concatenate the answer_id and answer to display.

foreach (var item in lstAnswers)
{
     txtTextBox.Text = item.answer_id + " " + item.answer;
}
Andrew Reese
  • 854
  • 1
  • 11
  • 27
  • i mean i have list of answer_id but firsty we need to submit a query to get answer and i want to display all the answers in datatable which is in the list. – Hallowen Oct 07 '19 at 20:19
  • I just added to my answer with example of how your function would look with the select statement. – Andrew Reese Oct 07 '19 at 20:29
  • Thanks for the update but i coudn't explain my self. "SELECT answer_id, answer from TABLE WHERE answer_id=@id" you can get if matches right? I have a list of @id. So i want to get all in one. – Hallowen Oct 07 '19 at 20:29
  • So you are using parameters? – Andrew Reese Oct 07 '19 at 20:31
  • 1
    I just wrote now to explain it. If there is no match i don't need to get all the data right? – Hallowen Oct 07 '19 at 20:32
  • I just updated the query where it is taking in an integer of AnswerID. Also, the reader.hasrows only runs when there is a match and data so it takes care of that. – Andrew Reese Oct 07 '19 at 20:34
  • @Hallowen were you able to accomplish what you were trying to do? – Andrew Reese Oct 08 '19 at 12:48
  • 1
    Not yet.. I will try to bind in a datatable and finally, i will try to display in gridview. Just need time.. – Hallowen Oct 08 '19 at 15:58
0

If you're using ADO.NET, it will require some extra steps.

You'll need to use an Extension Helper, since adding a List as a parameter is one of the limitations of ADO.NET, see : Passing-list-int-as-parameter

You could do a 

foreach(int x in myAnswerIdList){
//execute a sqlCommand for every number in the list and save it into an array
}

But this is really a bad and ugly approach... The 'easiest' way it will be using Entity Framework, since you have the List already they exist many approaches to achieve what you're looking for using EF.

Jose Polanco
  • 1
  • 2