SQL Query of a single column into Array or Collection in C#?

Question

How can I move results from a SQL Query into Array or Collection in C#?

My SQL Query (selecting LastName values with non-null values from LastName column in mytable):

SELECT LastName FROM mytable WHERE LastName IS NOT NULL

And here is code that I was executing before to generate new Dataset with that column:

string connectionString = @"Data Source='/path-to-datatable/ASTS.sdf';";
string strSql = "SELECT LastName FROM mytable WHERE LastName IS NOT NULL";
SqlCeConnection mConnection = new SqlCeConnection(connectionString);
SqlCeCommand mCommand = new SqlCeCommand(strSql, mConnection);
PartNumberDataSet = new MyDataBase();
// Read all rows from the table into a dataset
SqlCeDataAdapter PNadapter = new SqlCeDataAdapter(mCommand);
PNadapter.Fill(PartNumberDataSet, "mytable ");

However, I want to minimize amount of data that I will have to store in the memory (I have a lot of 20 or so columns in that table). How can I save this query into some array or collection instead? I only need LastName values.

Your code is already loading only the column required. What's the problem? — Steve
– Steve, Commented Oct 25, 2013 at 21:33

Mike Dinescu · Accepted Answer · 2013-10-25 21:35:00Z

A data set and data adapter may, or may not be overkill. You really have to ask yourself if you need the DataSet (i.e. are you passing it down to an API designed to work with DataSet). If you do need it then there's probably very little incentive to try to do anything about it (unless you profile and determine that it's too heavy).

However, if you don't need the DataSet and you just need to get a list of last names, or other data from your database then you could use a SqlCeDataReader to read the result set from the database and to populate your collection.

Use a while loop to read each record and extract the last name from the data reader. For each last name, add it to a collection.

Something along the lines of the following should work:

 List<string> lastNames = new List<string>();
 using(SqlCeDataReader dr = mCommand.ExecuteReader())
 {
     while(dr.Read())
     {
         string lastName = (string)dr["LastName"];

         lastNames.Add(lastName);
     }
 }

Collectives™ on Stack Overflow

SQL Query of a single column into Array or Collection in C#?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related