Fill Dropdown control from database in MVC 4/5 using ADO.NET without Entity Framework

Dear Reader, today we will see that how can we fill dropdown control from Database using ADO.NET without Entity Framework in MVC 4/5 application. I am using SQL Server for database and Visual Studio 2015 IDE for coding.

This blog is especially for those programmers who have not used Entity Framework till now and are into the habit of using old ADO.NET framework for database coding.

Note: Here I am considering that you are aware of basics of MVC application creation such as View, Model and Controller. If not, Please get some basics of MVC First Application.

So, let’s start with an example.

First of all, create a table in your database which contain data to show in the dropdown control. Here I am going to show list of countries in dropdown. Table name is CountryList.

Data is:

Create Model-

public class ShowCountry

    {

        public int CountryID { get; set; }

        public string cname { get; set; }

        public List<ShowCountry> CountryData { get; set; }

    }

Now create an action method in controller (I have taken HomeController). The code is –

public ActionResult ShowCountry()

        {

            DataTable dt = DAL.GetCountryData();

            List<ShowCountry> lst = new List<Models.ShowCountry>();

            foreach(DataRow dr in dt.Rows)

            {

                lst.Add(new Models.ShowCountry() {

                    CountryID = Convert.IsDBNull(dr["CountryID"]) ? default(int) : Convert.ToInt32(dr["CountryID"]),

                    cname = Convert.ToString(dr["Name"])

                });

            }

            ShowCountry objSC = new Models.ShowCountry();

            objSC.CountryData = lst;

            return View(objSC);

        }

In the above code, GetCountryData() is a method of DAL class which is used to retrieve the data from the database. It return DataTable type. DAL class is representing to Data Access Layer class. You can keep this code inside any class where you want. The code of this function is –

public static DataTable GetCountryData()

        {

            SqlConnection con = new SqlConnection("Data Source=myserver;Initial Catalog=DBName;Integrated Security=True");

            SqlDataAdapter da = new SqlDataAdapter("select * from CountryList", con);

            DataTable dt = new DataTable();

            da.Fill(dt);

            return dt;

        }

In the above code I have coded inline query to select data but I highly recommend to do it by using store procedures. And it is better to define your connection string inside web.config file. To shorten the code I have defined inside the function only.

Now create View by right click inside action and choose Add View… option then

The code of dropdown inside the View is-

<div>

        The List of Country is  :

        @Html.DropDownListFor(x => x.CountryID,new SelectList(Model.CountryData, "CountryID", "cname"),"-Select-")

    </div>

CountryData is the List type property of Model which contains List of countries coming from the database.

Build and run the project.

I hope it will be helpful for you.

Happy Coding.

Rudra Pratap Singh

Software Engineer

singhrudrapratap29[at]gmail[dot]com