Getting records from a database in C # 2008

Question

I need syntax help with the following code logic:

I have a block of code that gets an email address from a database. Email addresses must be bound to strEmailAddress, a comma-separated string

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 scroll through records and save the results in strEmailAddress, separated by comma?

Answer 1

Use the method SqlDataReader.Read:

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

Answer 2

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

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

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

, , ( update: , ):

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

Answer 3

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

Answer 4

, ....

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

              }
           }
       }
   }

}

:

• conn...
• , NULL
• GetOrdinal emailAddr , SQL Select..., int) GetSqlString..

: John Saunders blooper!

# 2: ...

, , , .