CODEWITHSUNDEEP
c#asp.net-3.5
user279521
user279521

Reputation: 4807

Retrieving records from a database in C# 2008

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?

Upvotes: 3

Views: 237

Answers (4)

t0mm13b
t0mm13b

Reputation: 34592

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.

Upvotes: 1

Peter Lillevold
Peter Lillevold

Reputation: 33910

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));
}

Upvotes: 5

Gabe
Gabe

Reputation: 50483

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

Upvotes: 1

Oded
Oded

Reputation: 498914

Use the SqlDataReader.Read method:

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

Upvotes: 3

Related Questions