Joe Joe - 1 month ago 15
ASP.NET (C#) Question

C# ASP.NET - SImplify number of SQL queries written in code

Here's a little background on what I'm doing...

I'm writing a C# web app. On the main page, I have a data input form with about 25 individual Dropdownlists. I created a table called options, and it's pretty simple (ID, Category, Option). Each option I create is categorized so my query will only include options that match the category I'm looking up. Each Category matches one of the 25 Dropdownlists I need to populate.

So I'm able to get a few of these populated on the form and they work great. I'm concerned that the re-writing of this code (with slight variation of the DDlist name and category name) will cause the code to be much longer. Is there a way I can create a class of it's own and pass parameters to the class so it only returns me data from the correct category and populates the correct Dropdownlist? Here's some sample code I have so far for 2 DD fields. The DDStationList and DDReqeustType are the names of 2 of the 25 Dropdownlists I have created:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;
namespace TEST
{
public partial class _Default : Page
{
//Main connection string
string SqlConn = ConfigurationManager.AppSettings["SqlConn"];
string qryRequestType = ConfigurationManager.AppSettings["qryRequestTypes"];
string qryStationNumbers = ConfigurationManager.AppSettings["qryStationNumbers"];
protected void Page_Load(object sender, EventArgs e)
{}
protected void BtnAddNew_Click(object sender, EventArgs e)
{
//GET Request Types
DataTable RequestTypes = new DataTable();
SqlConnection Conn = new SqlConnection(SqlConn);
{
SqlDataAdapter adapter = new SqlDataAdapter(qryRequestType, Conn);
adapter.Fill(RequestTypes);
DDRequestType.DataSource = RequestTypes;
DDRequestType.DataTextField = "Option";
DDRequestType.DataValueField = "Option";
DDRequestType.DataBind();
}
// Get Stations
DataTable Stations = new DataTable();
SqlConnection Conn = new SqlConnection(SqlConn);
{
SqlDataAdapter adapter = new SqlDataAdapter(qryStationNumbers, Conn);
adapter.Fill(Stations);
DDStationList.DataSource = Stations;
DDStationList.DataTextField = "Option";
DDStationList.DataValueField = "Option";
DDStationList.DataBind();
}
}
protected void BtnSubmit_Click(object sender, EventArgs e)
{
//More stuff to do here for submit code
}
}
}


Example queries from my config file that correspond to above code:

SELECT [Option] FROM Table WHERE Category = 'RequestType';
SELECT [Option] FROM Table WHERE Category = 'Station';


So, is it possible I can create a class that I can pass the Option's Category into that runs the query Like this:

SELECT [Option] FROM Table WHERE Category = @Category;


...and then populate the correct Dropdownlist (need to do this 25 times)?

If I'm not clear on my question, I'll be happy to explain further.

Answer

Why not create a stored procedure instead?

using (SqlConnection con = new SqlConnection(dc.Con)) {
    using (SqlCommand cmd = new SqlCommand("sp_GetCategory", con)) {
        cmd.CommandType = CommandType.StoredProcedure;    
        cmd.Parameters.Add("@Category", SqlDbType.VarChar).Value = txtCategory.Text;

        con.Open();
        var results = cmd.ExecuteReader();
    }
}

OR include the parameter

string sql = "SELECT [Option] FROM Table WHERE Category = @Category";

using (SqlConnection con = new SqlConnection(dc.Con)) {
    using (SqlCommand cmd= new SqlCommand(sql, con)) {
        cmd.Parameters.Add("@Category", SqlDbType.VarChar).Value = txtCategory.Text;
        con.Open();
        var results = cmd.ExecuteReader();
    }
}

EDIT

class Category
{
    /* properties */

    /* method */
    public List<Category> GetCategory(string selectedCategory) 
    { /* Method statements here */ }
}
Comments