Creating a class to use for populating drop-down lists, grids, etc., in C#

Question

Please keep in mind that I'm new to C# and OOP so I apologize in advance if this seems like an easy question to some. I'm going back through my code and looking for ways to objectify repetitive code and create a class for it so that I can simply reuse the class. That being said, I'm not looking to learn NHibernate or any other ORM just yet. I'm not even looking to learn LINQ. I want to hack through this to learn.

Basically I use the same bit of code to access my database and populate a drop-down list with the values that I get. An example:

   protected void LoadSchools()
    {
        SqlDataReader reader;

        var connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
        var conn = new SqlConnection(connectionString);
        var comm = new SqlCommand("SELECT * FROM [Schools] ORDER BY [SchoolName] ASC", conn);

        try
        {
            conn.Open();
            reader = comm.ExecuteReader();
            cmbEditSchool.DataSource = reader;
            cmbEditSchool.DataBind();


            cmbEditSchool.Text = "Please select an existing school to edit...";
            if (reader != null) reader.Close();
        }

        finally
        {
            conn.Dispose();
        }
    }

I use this same bit of code, over and over again throughout my program, on different pages. Most often, I'm populating a drop-down list or combo box, but sometimes I will populate a gridview, only slightly altering the query.

My question is how can I create a class that will allow me to call a stored procedure, instead of manually using queries like my example, and populate my different controls? Is it possible to do with only 1 method? I only need to start with selects. I've been reading up on IEnumerable which seems like the appropriate interface to use, but how do I use it?

Edited to add:

I marked Rorschach's answer as THE answer because s/he addressed my IEnumerable question. I also understand the need for a proper DAL and perhaps BLL layer. What I was trying to get to was that. I can build a DAL using datasets and table adapters which, in the end, gets me a strongly typed dataset. However, I feel a bit removed from the code. I was after a straight-forward way of building the DAL myself starting with the code that I gave above. Perhaps I'm not wording my question, or what I'm after, correctly.

At any rate, Rorschach came closest to answering my actual question. Thanks.

Answer 1

You should take a look at ADO.NET and use things like DataSets. Linq-to-SQL would probably also be useful for you. But you probably won't get away with just one method call to replace all your data operations I'm afraid.

Answer 2

This is a down and dirty method of reducing your code duplication. It's not really the right way to go about setting up a Data Access Layer (DAL) and a Business Logic Layer (BLL), which I'd suggest learning about instead.

protected void FillFromDatabase( string sql, BaseDataBoundControl dataControl)
{
SqlDataReader reader = null;

var connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
var conn = new SqlConnection( connectionString );
var comm = new SqlCommand( sql, conn );

try
{
 conn.Open();
 reader = comm.ExecuteReader();
 dataControl.DataSource = reader;
 dataControl.DataBind();
}

finally
{
 if( reader != null )
  reader.Dispose();

 conn.Dispose();
}
}

then you could call it like

const string sql = "SELECT * FROM [Schools] ORDER BY [SchoolName] ASC";
FillFromDatabase( sql, cmbEditSchool );

Answer 3

You can create a class that lets you call stored procedures (this is known as a Data Access Component (DAC) class, which is usually referenced by a Business Component (BC) class, but it is outside the scope of your question).

There are a few objects you will want to use in this new class: Microsoft.Practices.EnterpriseLibrary.Data.Database Microsoft.EnterpriseLibrary.Data.DatabaseFactory System.Data.Common.DBCommand

The DAC class will look similar to what you have:

public class DataAccess
{
    public DataAccess()
    {
    }
    public System.Collections.IEnumerable GetSchoolData()
    {
        string connectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
        Database db = DatabaseFactory.CreateDatabase(connectionString);
        string sqlCommand = "GetSchoolData";
        DbCommand comm = db.GetStoredProcCommand(sqlCommand);
        //db.AddInParameter(comm, "SchoolId", DbType.Int32); // this is in case you want to add parameters to your stored procedure

        return db.ExecuteDataSet(comm);
    }
}

And your page code will look like this:

public class SchoolPage : Page
{
  public void Page_Init(object sender, EventArgs e)
  {
    DataAccess dac = new  DataAccess();
    cmbEditSchool.DataSource = dac.GetSchoolData();
    cmbEditSchool.DataBind();
  }
}

Note that this is just to help you learn how to do this. It is not a good approach to development because you are opening up your Data Access Layer to the outside world (which is bad).