C# To Hold SQL Results

richard smith

How via C# can I query a SQL table for carName and store all results in a C# string Array variable? So something like:
(I know this isn't actual syntax) -- just needing an example of how to perform this action
string[] carName = "Select carName from OnsiteCars";

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes13y ago
Accepted answer
You'd need to modify the code as follows to get two columns from the database (you can get as many as you like using a single SELECT statement).

I've assumed that you still want the results in a (single) string array so I've just concatenated them with a space in between :

string queryString = "Select carMake, carName from OnsiteCars";
List tempList = new List();

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
if (!reader.IsDBNull(0))
{
tempList.Add(reader[0].ToString() + " " + reader[1].ToString())
}
}
reader.Close();
}

string[] cars = tempList.ToArray();

If you're including this code in a method which returns a string[], then you need to ensure that - whatever happens - the method will return a string[] or null, otherwise the compiler will complain.

The error message suggests that there's a problem in this regard at present.
Vulpes13y ago
If you're going to be returning two (or more) related values from a method then, rather than using two separate values, I'd define a class:

class Car
{
public string Make {get; set;}
public string Name {get; set;}
}

// ....

string queryString = "Select carMake, carName from OnsiteCars";
List tempList = new List();

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
if (!reader.IsDBNull(0))
{
Car c = new Car{Make = reader[0].ToString(), Name = reader[1].ToString()};
tempList.Add(c)
}
}
reader.Close();
}

Car[] cars = tempList.ToArray();

// ...

return cars; // need to change return type of method to Car[]
richard smith13y ago
If the need would ever arise, how would I store each field in a different variable as opposed to combining in one string?
richard smith13y ago
Question about the code...
How would I modify it to hold 2 variables so carName and also carMake? Or would I need two individual select statements?

And I am getting a compile error of not all code paths return a value?
Vulpes13y ago
For SQL Server, the C# code to do that would be something like this:

using System.Data.SqlClient;

// .....

string queryString = "Select carName from OnsiteCars";
List tempList = new List();

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
if (!reader.IsDBNull(0))
{
tempList.Add(reader[0].ToString())
}
}
reader.Close();
}

string[] carName = tempList.ToArray();