Retreiving records from a database in C# 2008

Question

Hi, I need syntax help with the following code logic:

I have a code block that gets email address from the database. The email addresses need to be assigned to a string variable strEmailAddress with a comma seperation

My code is:

SqlConnection conn = new SqlConnection(strConn);
string sqlEmailAddress = "usp_Get_Email_Address";
SqlCommand cmdEmailAddr = new SqlCommand(sqlEmailAddress, conn);
cmdEmailAddr.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader sqlDREmailAddr = cmdEmailAddr.ExecuteReader();

How can I loop through the records and store the results in strEmailAddress seperated by comma?

Thanks

Answer 1

while (sqlDREmailAddr.Read())
{
    //...process each row here
}

I would also wrap the reader in a using statement to make sure it is closed properly:

using (SqlDataReader sqlDREmailAddr = cmdEmailAddr.ExecuteReader())
{
}

Depending on what the columns in your dataset is named, reading values from each record will look something like this (update: now with all addresses merged):

var emailAddress = new StringBuilder();
var emailAddressOrdinal = sqlDREmailAddr.GetOrdinal("EmailAddress");
while (sqlDREmailAddr.Read())
{
    if (emailAddress.Length > 0)
        emailAddress.Append(',');
    emailAddress.Append(sqlDREmailAddr.GetString(emailAddressOrdinal));
}

Answer 2

while (sqlDREmailAddr.Read())
  {
    // handle row here
  }

Answer 3

Use the SqlDataReader.Read method:

while (sqlDREmailAddr.Read())
{
 ...
 // Assumes only one column is returned with the email address
 strEmailAddress = sqlDREmailAddr.GetString(0);
}

Answer 4

This is what you're looking for....

using (SqlConnection conn = new SqlConnection(strConn)){
   string sqlEmailAddress = "usp_Get_Email_Address";

   using (SqlCommand cmdEmailAddr = new SqlCommand(sqlEmailAddress, conn)){
       cmdEmailAddr.CommandType = CommandType.StoredProcedure;

       conn.Open(); // Typo Glitch!

       using (SqlDataReader sqlDREmailAddr = cmdEmailAddr.ExecuteReader()){

           while(sqlDREmailAddr.Read()){

              if (!sqlDREmailAddr.IsDBNull(sqlDREmailAddr.GetOrdinal("emailAddr"))){

                 // HANDLE THE DB NULL...

              }else{

                 strEmailAddress = sqlDREmailAddr.GetSqlString(sqlDREmailAddr.GetOrdinal("emailAddr"));

                 // Do something with strEmailAddr...

              }
           }
       }
   }

}

Notice:

• A typo glitch on the conn variable...
• A check is made to ensure that the Database value returned is not NULL
• A call is made to GetOrdinal to return the column based on emailAddr string value that corresponds to the column from the query for SQL Select...which is an int type) as the parameter for GetSqlString..

Edit: Thanks to John Saunders for pointing out a blooper!

Edit#2: Thanks to Peter Lillevold for pointing out a mis-spelling...

Hope this helps, Best regards, Tom.