C# To Hold SQL Results

Question

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";

Vulpes · 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.

Vulpes · Answer

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 smith · Answer

If the need would ever arise, how would I store each field in a different variable as opposed to combining in one string?

richard smith · Answer

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?

Vulpes · Answer

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();

C# To Hold SQL Results

Answers (5)