Step 1 : First, we create a function designed to interact with the database through a stored procedure, enabling efficient data retrieval. This approach ensures a consistent method for fetching data from the database, simplifying the process for binding dropdown lists dynamically in ASP.NET MVC.
Add a common class for making function : as given below .
To add : right click on your model folder >> and add >> New Item >>choose class .
Now create an static function for fetching the data from DB.
public static DataTable ExecuteProcedure(string ExecuteProcedure)
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("Db"));
SqlCommand cmd = new SqlCommand(ExecuteProcedure, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
Step 2 : Next, open SQL Server and create a stored procedure that handles the table name, as well as the text and value parts for the dropdown list. This procedure allows for dynamic binding, ensuring flexibility in fetching data based on different table structures and column mappings.
CREATE Proc USP_FetchDataForDropDown
(
@TableName Varchar(100),
@ValuePart Varchar(50),
@TextPart Varchar(50),
@myCond Varchar(50)
)
As
Begin
Declare @Msg Varchar(Max)='',@Query Nvarchar(Max)=''
Begin Try
If @myCond=''
Begin
Set @Query='Select '+@ValuePart+' As [ValuePart],'+@TextPart+' As [TextPart] From '+@TableName+''
End
Else
Begin
Set @Query='Select '+@ValuePart+' As [ValuePart],'+@TextPart+' As [TextPart] From '+@TableName+' '+@myCond
End
Execute(@Query)
End Try
Begin Catch
Set @Msg=ERROR_MESSAGE()+' On the Line No :'+ Cast(Error_Line() As Varchar)
End Catch
Select @msg as msg
End
Above stored procedure in SQL Server designed to dynamically fetch data for a dropdown list, based on the table name, column names for values and text, and an optional condition
Step 3 : Now need to call this procedure .
To implement dynamic dropdown binding in ASP.NET MVC, we first create a function in the
CommonMethod class that interacts with the database through a stored procedure. This function fetches data by executing the procedure with parameters for the table name, value part, text part, and an optional condition. This centralized approach allows us to reuse the same function for different tables, ensuring consistency and simplifying the binding process for dropdown lists across the application.
public static DataTable BindDropDown(string TableName, string TextPart, string ValuePart, string mycond)
{
string[,] Param = new string[,]
{
{"@TableName",TableName.Trim()},
{"@ValuePart",ValuePart.Trim()},
{"@TextPart",TextPart.Trim()},
{"@myCond",mycond.Trim()}
};
DataTable dt = CommonMethod.ExecuteProcedure(Param, "USP_FetchDataForDropDown");
return dt;
}
public static DataTable ExecuteProcedure(string[,] Param, string ExecuteProcedure)
{
try
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("DB"));
SqlCommand cmd = new SqlCommand(ExecuteProcedure, con);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < Param.Length / 2; i++)
{
cmd.Parameters.AddWithValue(Param[i, 0], Param[i, 1]);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw;
}
}
Step 3 : Now, you can call the
BindDropdown method within your application to dynamically bind dropdown lists with data fetched from the database, ensuring a consistent and reusable approach for populating dropdowns throughout your application.Now, we have a page named
CityMaster.cshtml and need to load data from the CountryMaster table.Just call CommonMethod class to your page .
Now your method is accessible to the page .
<select id="ddlCountry" name="ddlCountry" class="form-control col-md-7 col-xs-12" tabindex="1">
<option value="0">--Select Country--</option>
@{
DataTable dtC = CommonMethod.BindDropDown("CountryMaster", "CountryName", "CountryId", "");
StringBuilder sbC = new StringBuilder();
for (int j = 0; j < dtC.Rows.Count; j++)
{
sbC.Append("<option value='" + dtC.Rows[j]["ValuePart"] + "'>" + dtC.Rows[j]["TextPart"] + "</option>");
}
@Html.Raw(sbC.ToString());
}
</select>
The above code is sufficient to dynamically bind a dropdown from the database. You can simply call the method and pass the
TableName, ValuePart (the value of the dropdown option), and TextPart (the text displayed for each option) to retrieve and populate the dropdown list accordinglyWe hope you have gained a clear understanding of the entire process. Thank you for taking the time to read this blog. If you have any feedback or questions, feel free to leave a message in the comments section.