MVC: How to populate dropdownlist from sql query stored in a db field

Question

We're using mvc to create a dynamic web form. One of the controls on the form is a dropdown. In order to populate this dynamic dropdown control, we've stored the sql statement in a single database field. It's a long story as to why we're doing it this way, but the general issue we're having is how to get our dropdown to dynamically populate using this sql field value.

For example, here's a sample record from our database. This image may be too small to read. If so, try right-clicking & opening the image in a new window (the field we're using is called "Sql"):

As you can see, we have a field called "Sql". In this record, we need to pull the "product_id" as the dropdown's id field & the "product_name" as the dropdown's text field. One known, is within the sql statement, the first field will ALWAYS be the dropdown's id. The 2nd field will ALWAYS be the dropdown's text.

And here's my dropdownlist so far:

@(Html.Kendo().DropDownList()
      .Name("Filterdropdown")
      .DataTextField("Text")
      .DataValueField("Value")
          .OptionLabel("Select")
                                    //.Events(e => e.Change("change"))
      .BindTo(new List<SelectListItem>() {
          new SelectListItem() {
              Text = "",
              Value = "1"
          },


      })

As you can see, currently, I'm not using that field to populate my dropdownlist. So my question is, how can I set this up to do so?

Thanks

Answer 1

Here I understand that you are trying to populate a dropdown with some data. But, I am not sure of the reason why you are saving SQL query in a DB table. Possible reasons could be that the query to extract data for your dropdown is not always the same and you want that flexibility to modify the query through DB. Though this may not be the best of appraoches to achieve your requirement, hope it serves your purpose for now.

Now, coming to your requirement of extracting data for a dropdown, you could have easily achieved that through a simple subquery/function if you were using ADO.NET with SQL for querying. But, here as your application is build on MVC, you might be using Entity framework for data access which makes you think of only LINQ for querying.

Fortunately, though LINQ is the only direct way of querying DB entities via Entity Framework, you can also execute raw SQL queries through LINQ.! So, all you need to do here is:

Step 1) Get the SQL query stored in DB using LINQ.

sql = DbContextObj.YourQueryTable.ConditionToGetQuery..();

Step 2) Execute the query obtained in step 1 using .SqlQuery(sql) method on your data entity:

DataForDropDown = DbContextObj.YourDataTable.SqlQuery(sql).ToList..(); 

Now, you can use a HTML helper to generate a Dropdown control binded with your data.

Answer 2

According to your requirement you have to first get the query from db and than run it and with the result of this query you will bind the dropdown. Here is working code i have tried in same scenario.

Controller Code

Entities ent = new Entities();//This is my dbcontext object
//First i will query the db to get the query stored in table
Query query = ent.Queries.FirstOrDefault();
string sql = query.Query;//I have assigned the query to a string and now i will execute this

//Here i run the query in db against the table employee you need to change this to products.
var list = ent.Employees.SqlQuery(sql).ToList<Employee>();        
//Create List of SelectListItem
List<SelectListItem> selectlist = new List<SelectListItem>();
foreach (Employee emp in list)
{
 //Adding every record to list  
 selectlist.Add(new SelectListItem { Text = emp.Name, Value = emp.Id.ToString() });
}

ViewBag.SelectList = selectlist;//Assign list to ViewBag will access this in view
return View(list);

View Code

@Html.DropDownList("ddlname",(IEnumerable<SelectListItem>)ViewBag.SelectList)

Here is i have just assigned the ViewBag value to dropdown. I have not used Kendo but i hope this will help you to get on track.

Update

Instead of hitting sql server twice you can get your required data in just one query.These are two calls to sql server

Query query = ent.Queries.FirstOrDefault();
var list = ent.Employees.SqlQuery(sql).ToList<Employee>();    

These two can be combined to one like this

var list = ent.Employees.SqlQuery(ent.Queries.FirstOrDefault().Query).ToList<Employee>();