How to save SELECT sql query results in an array in C# Asp.net

Question

How can I save the results of an SQL query into an array?

I want to use the values (located in col1 and col2) in an IF statement, leading to my thinking of saving them in an array.

var con = new SqlConnection("Data Source=local;Initial Catalog=Test;Integrated Security=True");          using (con) using (var command = new SqlCommand("SELECT col1,col2 FROM  some table", con)) {    con.Open();    command.ExecuteNonQuery(); } 

Answer 1

Normally i use a class for this:

public class ClassName {     public string Col1 { get; set; }     public int Col2 { get; set; } } 

Now you can use a loop to fill a list and ToArray if you really need an array:

ClassName[] allRecords = null; string sql = @"SELECT col1,col2                FROM  some table"; using (var command = new SqlCommand(sql, con)) {     con.Open();     using (var reader = command.ExecuteReader())     {         var list = new List<ClassName>();         while (reader.Read())             list.Add(new ClassName { Col1 = reader.GetString(0), Col2 = reader.GetInt32(1) });         allRecords = list.ToArray();     } } 

Note that i've presumed that the first column is a string and the second an integer. Just to demonstrate that C# is typesafe and how you use the DataReader.GetXY methods.

Answer 2

Instead of any Array you can load your data in DataTable like:

using System.Data;  DataTable dt = new DataTable(); using (var con = new SqlConnection("Data Source=local;Initial Catalog=Test;Integrated Security=True")) {     using (var command = new SqlCommand("SELECT col1,col2" +     {         con.Open();         using (SqlDataReader dr = command.ExecuteReader())         {             dt.Load(dr);         }     } } 

You can also use SqlDataAdapater to fill your DataTable like

SqlDataAdapter da = new SqlDataAdapter(command); da.Fill(dt); 

Later you can iterate each row and compare like:

foreach (DataRow dr in dt.Rows) {     if (dr.Field<string>("col1") == "yourvalue") //your condition     {     } }