Populate DropDownList with List of Stored Procedures in Database in ASP.Net MVC

Last Reply 5 months ago By dharmendr

Posted 5 months ago

i want to bind store procedures list names in dropdown using mvc.if i add new storeprocedures in sql database then automatically it will be add in my dropdown when i open my page in mvc.

Posted 5 months ago

Hi varun.p,

Check this example. Now please take its reference and correct your code.

Namespaces

using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        List<SelectListItem> items = new List<SelectListItem>();
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        string query = "SELECT SPECIFIC_NAME FROM information_schema.routines WHERE ROUTINE_TYPE = 'PROCEDURE' ORDER BY SPECIFIC_NAME";
        using (SqlConnection con = new SqlConnection(conString))
        {
            SqlCommand cmd = new SqlCommand(query);
            cmd.Connection = con;
            con.Open();
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.Read())
            {
                items.Add(new SelectListItem { Text = sdr["SPECIFIC_NAME"].ToString(), Value = sdr["SPECIFIC_NAME"].ToString() });
            }
        }
        TempData["Procedures"] = items;
        return View();
    }
}

View

<%:Html.DropDownList("ddlProcedures",(IEnumerable<SelectListItem>)TempData["Procedures"],"Select") %>

Screenshot