传递一个带有select where变量的对象

Question

我正在进行oop的第一步，现在我正在重新构建一个实践项目，使其成为n层和oo。我在数据层上有一个查询，它内部连接三个表，并选择SubmissionId=x的行；业务层有一个服务对象，它将行返回到表示层，但当我遇到表示时，我遇到了一个障碍。我已经得到保证，只要在表示层上定义了数据层上未分配的变量，它就可以了，但我不知道如何正确调用它。想法？以下代码：

数据层//这属于公共类SubmissionDatabaseService//

  public Submission GetSubmissionsByID()
{

      string viewQuery = "SELECT Submission.SubmissionId, Customer.CustName, Customer.SicNaic, Customer.CustCity, Customer.CustAddress, Customer.CustState, Customer.CustZip, Broker.BroName, Broker.BroCity, Broker.BroAddress, Broker.BroState, Broker.BroZip, Broker.EntityType, Submission.Coverage, Submission.CurrentCoverage, Submission.PrimEx, Submission.Retention, Submission.EffectiveDate, Submission.Commission, Submission.Premium, Submission.Comments FROM Submission INNER JOIN Broker ON Broker.BroId = Submission.BroId INNER JOIN Customer ON Customer.CustId = Submission.CustId WHERE Submission.SubmissionId =" + x;
      string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
      SqlConnection conn = new SqlConnection(connectionString);

      conn.Open();
      SqlCommand viewCmd = new SqlCommand(viewQuery, conn);
      SqlDataReader dr = viewCmd.ExecuteReader();
      Submission tempSubmission = new Submission();
          tempSubmission.SubmissionId1 = dr.GetInt32(0);
          tempSubmission._Cust.CustName1 = dr.GetString(1);
          tempSubmission._Cust.SicNaic1 = dr.GetInt32(2);
          tempSubmission._Cust.CustCity1 = dr.GetString(3);
          tempSubmission._Cust.CustAddress1 = dr.GetString(4);
          tempSubmission._Cust.CustState1 = dr.GetString(5);
          tempSubmission._Cust.CustZip1 = dr.GetInt32(6);
          tempSubmission._Bro.BroName1 = dr.GetString(7);
          tempSubmission._Bro.BroCity1 = dr.GetString(8);
          tempSubmission._Bro.BroAddress1 = dr.GetString(9);
          tempSubmission._Bro.BroState1 = dr.GetString(8);
          tempSubmission._Bro.BroZip1 = dr.GetInt32(11);
          tempSubmission._Bro.Entity1 = dr.GetString(12);
          tempSubmission._SubCov.Coverage1 = dr.GetInt32(13);
          tempSubmission._SubCov.CurrentCoverage1 = dr.GetInt32(14);
          tempSubmission._SubCov.PrimEx1 = dr.GetInt32(15);
          tempSubmission._SubCov.Retention1 = dr.GetInt32(16);
          tempSubmission._SubCov.EffectiveDate1 = dr.GetDateTime(17);
          tempSubmission._SubCov.Commission1 = dr.GetInt32(18);
          tempSubmission._SubCov.Premium1 = dr.GetInt32(19);
          tempSubmission._SubCov.Comment1 = dr.GetString(20);
          return tempSubmission;
}

业务逻辑层

public class SubmissionService

｛

public Submission getSubmissionByID()
{
    SubmissionDatabaseService sds = new SubmissionDatabaseService();
    return sds.GetSubmissionsByID();
}

}

表示层

 protected void Page_Load(object sender, EventArgs e)
{
    string x = Request.QueryString["SubmissionId"];

    Submission sub = SubmissionService.getSubmissionByID(x); //Here is where I throw an overload error

Answer 1

SubmissionService.getSubmissionByID()不接受参数。所以你不能通过x。像这样更改它在业务层中的签名。

public Submission getSubmissionByID(string x)
{
    SubmissionDatabaseService sds = new SubmissionDatabaseService();
    return sds.GetSubmissionsByID(s);
}

在像这样的数据层

public Submission GetSubmissionsByID(string x)
{

      string viewQuery = "SELECT Submission.SubmissionId, Customer.CustName, Customer.SicNaic, Customer.CustCity, Customer.CustAddress, Customer.CustState, Customer.CustZip, Broker.BroName, Broker.BroCity, Broker.BroAddress, Broker.BroState, Broker.BroZip, Broker.EntityType, Submission.Coverage, Submission.CurrentCoverage, Submission.PrimEx, Submission.Retention, Submission.EffectiveDate, Submission.Commission, Submission.Premium, Submission.Comments FROM Submission INNER JOIN Broker ON Broker.BroId = Submission.BroId INNER JOIN Customer ON Customer.CustId = Submission.CustId WHERE Submission.SubmissionId =" + x;
      ...

Answer 2

您只需要声明数据层方法的参数：

public Submission GetSubmissionsByID()

成为

public Submission GetSubmissionsByID(string x)

但是，这使您的系统完全可以进行SQL注入在尝试编写嵌入C#（或其他语言）中的SQL之前，请阅读参数化SQL语句

Answer 3

您的getSubmissionByID不接受参数，但您正在向它传递一个参数。您需要接受要传递的ID，然后将其提供给数据库进行查询。

public Submission getSubmissionByID(string id)
{
    SubmissionDatabaseService sds = new SubmissionDatabaseService();
    return sds.GetSubmissionsByID(id);
}

您还需要让数据访问获取一个参数，然后在查询中使用该参数。"+x"不是最佳做法。您应该使用查询参数。

    public Submission GetSubmissionsByID(string id)
    {
          string viewQuery = @"SELECT Submission.SubmissionId, Customer.CustName, Customer.SicNaic, Customer.CustCity, Customer.CustAddress, Customer.CustState, Customer.CustZip, Broker.BroName, Broker.BroCity, Broker.BroAddress, Broker.BroState, Broker.BroZip, Broker.EntityType, Submission.Coverage, Submission.CurrentCoverage, Submission.PrimEx, Submission.Retention, Submission.EffectiveDate, Submission.Commission, Submission.Premium, Submission.Comments
    FROM Submission
        INNER JOIN Broker ON Broker.BroId = Submission.BroId INNER JOIN Customer ON Customer.CustId = Submission.CustId
    WHERE Submission.SubmissionId = @id";
          string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
          SqlConnection conn = new SqlConnection(connectionString);
    Conn.Open();
    SqlCommand viewCmd = new SqlCommand(viewQuery, conn);
    var parameter = new SqlParameter("@id", SqlDbType.VarChar)
    parameter.Value = id;
    cmd.Parameters.Add(parameter);
    // skip some stuff
   }