如何从数据库中填充combobox项，而不是在任何地方复制相同的代码(如何将代码更改为使用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 this following code in retrieving data from the database into the combobox. I always place this code everytime I need to populate a data into the combobox from the database. What I want to do is reconstruct this code and place it in a class and what will I do is just call the function that will be responsible in populating data into the combobox. But I am confused on how to do it. Please help me. Thank you in advanced. Have a good day..

我有以下代码从数据库检索数据到combobox。每次需要将数据填充到数据库的combobox时，我总是将此代码放置在其中。我要做的是重构这段代码并把它放到一个类中我要做的就是调用这个函数它负责将数据填充到combobox中。但是我不知道怎么做。请帮助我。谢谢你在发达。有一个美好的一天. .

2 个解决方案

#1

You have several concerns in the code you posted. I'll try to provide a simplified set of code, but if you separate the various needs (concerns) of this little bit of code, you will find many distinct classes, but I digress.

在您发布的代码中有几个问题。我将尝试提供一组简化的代码，但是如果您将这一小段代码的各种需求(关注点)分开，您将发现许多不同的类，但是我离题了。

First, you do NOT want to be spreading your connection string everywhere in your application with COPY-PASTE. You should create a dedicated class to handle this.

首先，您不希望在应用程序中到处使用复制粘贴扩展连接字符串。您应该创建一个专用类来处理这个问题。

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)
}

Then you use it like:

然后你可以这样使用:

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);
}

If you're with me so far, just go one more step towards a Repository Pattern. Add this class:

如果到目前为止您还支持我，那么只需再向存储库模式迈进一步。添加这个类:

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;
   }
}

Then your calling code becomes:

然后您的调用代码变成:

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

If you look into the topics of: Generics, Repository Pattern and Data Transfer Objects (DTOs), you will find many good ideas on how to create classes that you can re-use instead of duplicating code.

如果您研究一下泛型、存储库模式和数据传输对象(dto)的主题，您将会发现许多关于如何创建可以重用而不是重复代码的类的好主意。

Note: I have not gone through the entire exercise of splitting things out into Interfaces so that you can do easy Unit Testing and Dependency Injection, that is a larger topic, but a good thing to check out as well!

注意:我还没有完成将内容分解为接口的整个实践，这样您就可以轻松地进行单元测试和依赖项注入，这是一个更大的主题，但是检查一下也是一件好事!

#2

Generally you want to keep presentation logic separate from business/data access logic. So your data access code should return the data in a generic form, such as a DataTable or some sort of custom Collection. Then your calling code on the form would be responsible for populating it into the combobox:

通常，您希望将表示逻辑与业务/数据访问逻辑分开。因此，您的数据访问代码应该以通用的形式返回数据，比如DataTable或某种自定义集合。然后，你在表单上的调用代码将负责将其填充到combobox中:

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

Then in your form, you'd do something like this:

然后在你的表格里，你会做这样的事情:

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

#1