Bind dropdownlist with database using DataSet,SqlDataReader and entity framework in asp .net

In this step by step asp .net tutorial we will learn how to bind dropdownlist with database using DataSet,SqlDataReader and entity framework. For this demonstration purpose I am using Northwind database to bind my dropdownlist. You are free to use your own database.

For complete overview of asp .net dropdownlist control you can follow this article: 

Dropdownlist Example in asp .net

Step1: Create a new asp .net website and add Default.aspx page.

Step2: Create a new dropdownlist control in your page.

   

        

            
Using SqlDataReader
            

                
                
        
        

            
Using DataSet
            

                
                
        
        

            
Using EntityFrameWork
            

                
                
        
    

Step3: Add connection string  in web.config file of your website.

For More information about how to use connection string in web .config you can refer: 

Connection string example in asp .net

Bind dropdownlist using DataSet:

 public void bindUsingDataSet()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
        SqlCommand cmd = new SqlCommand("Select * from Country", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);

        ddlTest2.DataSource = ds;
        ddlTest2.DataTextField = "CountryName";
        ddlTest2.DataValueField = "Id";
        ddlTest2.DataBind();

        ds.Dispose();
    }

Bind dropdownlist using SqlDataReader: 

 public void bindUsingDataReader()
    {
        //====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

        //======= Select Query.
        string cmdText = "SELECT * FROM country";

        //====== Providning information to SQL command object about which query to 
        //====== execute and from where to get database connection information.
        SqlCommand cmd = new SqlCommand(cmdText, con);

        //===== To check current state of the connection object. If it is closed open the connection
        //===== to execute the insert query.
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }


        //===== Execute Query and bind data to dropdownlist.
        ddlTest.DataSource = cmd.ExecuteReader();
        ddlTest.DataTextField = "CountryName";
        ddlTest.DataValueField = "Id";

        ddlTest.DataBind();

        //--- Close the connection
        con.Close();
    }

Bind dropdownlist using Entity Framework:

 public void bindUsingEntityFrameWork()
    {
        using (ForTestingEntities context = new ForTestingEntities())
        {
            ddlTest3.DataSource = (from r in context.Countries select r).ToList();
            ddlTest3.DataTextField = "CountryName";
            ddlTest3.DataValueField = "Id";
            ddlTest3.DataBind();
        }
    }

Step:4 Bind values into dropdownlist. Put this code inside PageLoad method of Default.aspx.cs page.

protected void Page_Load(object sender, EventArgs e)
    {
        //--- Using Data Reader.
        bindUsingDataReader();

        //--- Using DataSet.
        bindUsingDataSet();

        //--- Using Entity Framework.
        bindUsingEntityFrameWork();
    }

Note: Always make sure you bind your dropdownlist after checking Page postback. 

You may like: How to change look of the dropdownlist and add search feature to dropdownlist control