Geek asks and answers

How can I populate combobox elements from a database and not copy the same code all over the world (how can I change the code to use OOP)?

SqlDataReader myreader = null;
    SqlConnection sqlConn = null;

     cmbCat.Items.Clear();
    sqlConn = new SqlConnection("Data Source=tin;Initial Catalog=sample;Trusted_Connection=yes;");
    sqlConn.Open();
    SqlCommand sqlComm = new SqlCommand("SELECT members FROM  dbo.tbl_Category", sqlConn);

    myreader = sqlComm.ExecuteReader();
    if (myreader != null)
    {
        while (myreader.Read())
        {

            cmbCat.Items.Add(myreader["members"]);

        }
    }

    {
        if (myreader != null)
            myreader.Close();
        if (sqlConn != null)
        {
            if (sqlConn.State == ConnectionState.Open)
                sqlConn.Close();
        }
    }

I have the following code to retrieve data from a database in combobox. I always put this code every time I need to fill in the data in the combo box from the database. What I want to do is to restore this code and put it in a class, and what I should do is just call the function that will be responsible for filling the data in the combobox. But I am confused about how to do this. Please help me. Thank you in advance. Have a nice day..

+3
source share
2 answers

, . , () , , .

-, COPY-PASTE. , .

public class DatabaseGateway
{
  public IList<T> RetrieveSqlAs<T>(string queryString, ITransformer<SqlDataReader, T> rowTransformer)
  {
    var result = new List<T>();
    using (var sqlConn = new SqlConnection(connectionString))  // you can use a CONSTANT, or call to a .config file here
    using (var sqlCommand = new SqlCommand(queryString, sqlConn))
    {
      var myreader = sqlComm.ExecuteReader();
      while (myreader.Read())
      {
         result.Add(rowTransformer.Transform(myreader));
      }
    }
    return result;
  }
}

public class MemberRowTransformer : ITransformer<SqlDataReader, string>
{
  public string Transform(SqlDataReader from)
  {
    from["members"];  // handle null and anything else here
  }
}

public interface ITransformer<TFrom, TTo>
{
  TTo Transform(TFrom)
}

:

var gateway = new DatabaseGateway();
var transformer = new MemberRowTransformer();

cmb.Items.Clear();
foreach (string i in gateway.RetrieveSqlAs("SELECT members FROM  dbo.tbl_Category", transformer))
{
  cmb.Items.Add(i);
}

, Repository Pattern. :

public class MembershipRepository
{
   public List<string> GetMembers()
   {
     var gateway = new DatabaseGateway();
     var transformer = new MemberRowTransformer();
     var result = new List<string>();

     foreach (string i in gateway.RetrieveSqlAs("SELECT members FROM  dbo.tbl_Category", transformer))
     {
       result.Add(i);
     }

     return result;
   }
}

:

var repository = new MembershipRepository();
cmb.Items.Clear();
foreach(string i in repository.GetMembers())
{
   cmb.Items.Add(i);
}

: Generics, Repository Pattern Data Transfer Objects (DTOs), , , .

.. , , , !

+4

, , /. , , DataTable - . :

public List<string> GetCategories()
{
    List<string> cats = new List<string>();
    SqlDataReader myreader = null;
    ...
    while (myreader.Read())
        cats.Add((string)myreader["members"]);
    ...
    return cats;
}

- :

protected void DoDataBinding()
{
    MyDataAccessClass dataAccess = new MyDataAccessClass();
    List<string> cats = dataAccess.GetCategories();
    cmb.Items.Clear();
    foreach (string cat in cats)
        cmb.Items.Add(cat);
}
+2

More articles:


All Articles