storing result of select statement...

[saneeha.nust]

I am using a select statement for selecting certain rows of database table.

the statement returns multiple rows. please guide me how can i store all the
rows so that they can be used latter for comparison. each row contains one
variable of type string...

I was using
string user = com.ExecuteScalar().toString();

but it returns first value ...

please help....

[kamilacbe]

hi..

When you go for execute scalar method..obviously it displays first row's first column value...so why don't you try execute reader ..to read values ...

[ChrisHunter]

As kamilacbe said you need to use execute reader, your code should be something like this, try to understand it and change it to suit:

public List<string> getName()
        {
            //SQL statement to select Name from MyTable
            string sql = "SELECT Name FROM Mytable.Name";
	    
           //SQL command establishes database connection and executes SQL command
            SqlCommand cmd = new SqlCommand(sql, db.MyDBConnectMethod());

            //Creates list of strings
            List<string> NameList = new List<string>();

            try
            {
                //Creates SQLdatareader
                SqlDataReader rdr;

                //Reads the result of cmd 
                rdr = cmd.ExecuteReader();

                //Executes contents while there is data to available to read
                while (rdr.Read())
                {
                    //Adds each name read to a list of strings
                    NaameList.Add(rdr["Name"].ToString());
                }
                //Closes reader
                rdr.Close();
            }
            //Executed if try cannot
            catch
            {
            }
            //Returns string list
            return NameList;
}

[Mitja Bonca]

Where do you want to store the read rows from database? And why? This sounds a bit strange.

[thines01]

How you choose to store the data should really depend on what you're going to do with it when you get it out of the database.
My code example would be pretty-much like ChrisHunter's example.

I recommend custom classes for the data where you fill a collection of those classes by passing a DataReader to the constructor of the new object and then add that to the collection. This is of best use to me when I need to use the result in many different places and don't want to rely on human memory as to what the values were that came back.

Some folks really believe in DataSets, DataAdapters and DataTables.

[deceptikon]

Some folks really believe in DataSets, DataAdapters and DataTables.

The implication being that you think they're not a good solution? If so, please explain why. I always enjoy hearing different perspectives. :)

[thines01]

I've never studied the use of the DataTable and DataAdapter enough to determine if it is better than doing it the other way.

I do see a lot of new programmers completely confused by it though especially when changing from the select to the update.

My background brought me through MFC (with C++), so using the Recordset was the preferred method but required the programmer to be EXTREMELY familiar with the framework before expecting success. MFC did, however, bring back the columns in individually named member variables inside the class (because the wizard generated them from the DB schema, if you wanted). You could modify the structure, but you MUST be familiar with what it's going to do under the hood. When I look back at my legacy code, I have to "remember" what pieces did certain functions.

With the DataTable construction, it feels kind-of like MFC did.
Right now, I prefer to spell-it-out more explicitly.

I think in the end, it will be just as much code either way (to do what I do with classes).

[saneeha.nust]

Thanks Alot for the code.. It works perfect, The only change I made was
that instead of SqlDataReader I used :

OleDbDataReader rdr;
rdr = com.ExecuteReader();

because ExecuteReader() was raising an error
"cannot implicitly convert from OleDbDataReader to SqlDataReader "

I works fine with OleDbDataReader .. thanks for the help...