How to use value selected by Sql Data Source in C# code

Question

I am retrieving the email id of the user in SqlDataSource and want to send an email to this email ID. Below is the code for the SQL Data Source.

 <asp:SqlDataSource ID="UserDetailsSqlDataSource" runat="server" 
    ConnectionString="<%$ ConnectionStrings:UserDetailsConnectionString %>" 
    SelectCommand="SELECT [LoweredEmail] FROM [vw_aspnet_MembershipUsers] WHERE     ([UserName] = @UserName)">
   <SelectParameters>
        <asp:CookieParameter CookieName="userName" Name="UserName" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

Below is the code for sending the email.

   MailMessage mail = new MailMessage();
   mail.To.Add();

I am not getting how to get the email id retrieved in mail.To.Add();

Is there "MailMessage.To.Add()" method existing? – Apr 27 '12 at 02:28 — , Apr 27 '12 at 02:28

score 1 · Answer 1 · answered Apr 27 '12 at 02:04

1

You can programmatically call the Select method of a SqlDataSource, which will return one of the following:

DataView when DataSourceMode is set to DataSet
IDataReader when DataSourceMode is set to DataReader

Here's an example using a SqlDataReader:

var args = System.Web.UI.DataSourceSelectArguments.Empty;
var reader = (SqlDataReader) UserDetailsSqlDataSource.Select(args);

while(reader.Read()) {
   //do something with each returned record, e.g.
   MailMessage mail = new MailMessage();
   mail.To.Add(reader["LoweredEmail"].ToString());
}

answered Apr 27 '12 at 02:04

kaveman

4,339
25
44

I am getting following error at while(reader.Read()) line . System.NullReferenceException: Object reference not set to an instance of an object. – techblog Apr 27 '12 at 03:01
can you update your original post with the new code you are trying? I'll need some context – kaveman Apr 29 '12 at 05:55

score 1 · Answer 2 · 2012-04-30T18:10:11.753

1

Try this, you may modify the code to pass the parameter:

        string temp = string.Empty;
        string cmdText = "SELECT [LoweredEmail] FROM [vw_aspnet_MembershipUsers] WHERE     ([UserName] = @UserName)"
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                cmd.CommandType = CommandType.Text;
                         cmd.Parameters.AddWithValue(@UserName, UserName);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        temp = reader.GetValue(0).ToString(); // email ID
                    }
                }
            }
            conn.Close();

edited Apr 30 '12 at 18:10

answered Apr 27 '12 at 12:21

I am getting the email ID from SQL Data Source and then trying to feed that value in To field to send an email. Below is the code that I am writing for this. var args = System.Web.UI.DataSourceSelectArguments.Empty; SqlDataReader reader =(SqlDataReader)UserDetailsSqlDataSource.Select(args); reader.Read(); // I am getting error on this line that "System.NullReferenceException: Object reference not set to an instance of an object" String temp = reader.GetValue(0).ToString(); mail.To.Add(temp); – techblog Apr 29 '12 at 05:37
Sorry: it should be while(reader.Read()) etc. I already updated the code. – Apr 30 '12 at 18:11

How to use value selected by Sql Data Source in C# code

2 Answers2