Return values from SQL Server to C# convert ToArray?

Question

I am working on an assignment at the moment, trying to get values from a SQL Server database and storing them in an array. My connection is fine, but I am having trouble putting the returned values into an array.

Here is what I've got, have changed it a bit since I asked the question:

public int Bay;
int temp;

[DataContract]
public Garage()
{
    List<Garage> Bays = new List<Garage>();

    SqlConnection connection = new SqlConnection("Data   Source=fastapps04.qut.edu.au;Initial Catalog=*******;User ID=******;Password=******");
    connection.Open();

    SqlCommand command = new SqlCommand("SELECT Bay FROM Garage", connection);

    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        temp = reader.GetOrdinal("Bay");
        Bays.Add(temp);
    }

    Bays.ToArray();

    reader.Close();
    connection.Close();
}

Getting the error at

Bays.Add(temp)

Welcome to SO! Note that it is odd to do data access in a data contract. A data contract is for accepting or returning data, not typically for data access. — J0e3gan, Oct 25 '14 at 07:28
You are trying to build a List of Garage so you should give the complete definition of the Garage object not only its constructor and also what are the fields in the datatable Garage in your database?. You need to read that fieds one by one and add them to your Garage model properties — Steve, Oct 25 '14 at 07:30
`reader.GetOrdinal("Bay")` is just going to get the column ordinal (i.e. number) of the `Bay` column - for every row read. See MSDN documentation on [`SqlDataReader.GetOrdinal`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getordinal(v=vs.110).aspx). — J0e3gan, Oct 25 '14 at 07:34
The `temp` variable you're reading from the query is an `int` - however, the `Bays` is a list of `Garage` object. Those aren't compatible. You need to create a `Garage` instance, fill its values from the reader, and then add the `Garage` object to the `Bays` list. — marc_s, Oct 25 '14 at 08:09
@christiandev Garage is just a SQL database with one column of int for bay numbers — Andrejs Bicevskis, Oct 25 '14 at 08:14
@christiandev pretty much, i'm working with SQL Server Management Studio, I changed the list to , but that just opened up a whole bucket of other problems in other classes — Andrejs Bicevskis, Oct 25 '14 at 08:21
I need to keep the list as a Garage type, to integrate with everything else. Jeez I wish the tutorials for this unit made a bit more sense — Andrejs Bicevskis, Oct 25 '14 at 08:37
Are you sure the query is correct? maybe you need to return all fields from the Garage table? what does that table look like? — Christian Phillips, Oct 25 '14 at 08:54
Datareader returns fields like an array. Is it not enough? Reader[0] - first value. If you want all rows in array you can add them manually or set them to data table. Other side the best way is not using any of this. It's Bettter to use some of ORM — slava, Oct 25 '14 at 12:15

score 0 · Answer 1 · edited Oct 25 '14 at 07:36

0

Each call to reader.Read() will advance the reader to the next row of your result set. You seem to have assumed that you can get all rows into your array from a single read.

If you change your code so that you just add temp to your array on each iteration of the while loop you should find that it works.

I'd type the code, but I'm on my phone. :)

edited Oct 25 '14 at 07:36

J0e3gan

8,740
10
53
80

answered Oct 25 '14 at 07:27

Nat Wallbank

1,377
12
12

score 0 · Answer 2 · answered Oct 25 '14 at 08:22

Change the Bays to a List<int>, and return either a list or call .ToArray() on the list. Also, use Using statements with connections and commands.

   List<int> Bays = new List<int>(); 

   using(SqlConnection connection = new SqlConnection("connString"))
    {
        connection.Open();

        using (SqlCommand command = new SqlCommand("SELECT Bay FROM Garage", 
                                                                       connection))
        {
            using (SqlDataReader reader= command.ExecuteReader())
            {
                while (reader.Read()) 
                {
                    Bays.Add(reader.GetInt32(reader.GetOrdinal("Bay")));
                }         
            }
        } 
     }
     .....

score -1 · Answer 3 · answered Oct 25 '14 at 08:00

Little modification need to be done in your code in order to get the desired result needed by you.

 public Garage()
{

    SqlConnection connection = new SqlConnection("Data   Source=fastapps04.qut.edu.au;Initial Catalog=*******;User ID=******;Password=******");
    connection.Open();

    SqlCommand command = new SqlCommand("SELECT Bay FROM Garage", connection);

    SqlDataReader reader = command.ExecuteReader();
    List<Garage> listBays =new List<Garage>();
    while (reader.Read())
    {
        temp = reader.GetInt32(reader.GetOrdinal("Bay"));
        listBays.Add(temp);
    }
    Garage[] Bays=listBays.ToArray();
    reader.Close();
    connection.Close();

}

Return values from SQL Server to C# convert ToArray?

3 Answers3