How can I build a SQL query in to retrieve data from an external database?

Development Visual Studio Web Forms
1

Question

How can I build a SQL query to retrieve data from an external database?

When a user selects a username from a drop-down list, I would like to populate the email address text box with the selected user’s email from the Users table in my database.

Solution

To do this, you’ll need to:

  1. Add an event to your drop-down list to call the procedure you created (e.g. on selected index changed).

  2. Add a database connection string to your web form’s web.config file.

    Example:

    <add name="myConnectionName" connectionString="Data Source=myServerAddress;Initial Catalog=myDatabase;User ID=myUsername;Password=myPassword;" providerName="System.Data.SqlClient">

  3. Add a procedure in your C# code.

    Example:

    private void FindEmail() 
{
    try 
    { 
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionName"].ToString()); 

        try 
        {
            connection.Open();
        }
        catch (Exception e) 
        { 
            // Display error message in the form field named FRM_ERROR 
            this.FRM_ERROR.Text = e.Message;
            this.FRM_ERROR.Visible = true; 
        }
	
        SqlCommand selectcommand = connection.CreateCommand(); 
        selectcommand.CommandText = "select email from Users where username = '" + DROPDOWNLIST.SelectedValue + "'";
        SqlDataReader selectReader = Selectcommand.ExecuteReader (System.Data.CommandBehavior.CloseConnection); 
	
        if (selectReader.HasRows)
        { 
            while (selectReader.Read()) 
            { 
                this.SELECTED_EMAIL.Text = selectReader[0].ToString(); 
            }
        }

        connection.Close();
    } 
    catch (Exception e)
    { 
        this.FRM_ERROR.Text = e.Message;
        this.FRM_ERROR.Visible = true;
    }
}