pass an object with a select where variable?

I'm taking my first steps in oop, and right now I'm re-building a practice project to make it n-tiered and oo. I have a query on my data layer that inner joins three tables and selects the row where SubmissionId = x; the business layer has a service object that returns the row to the presentation layer, but when I hit presentation I hit a snag. I've been assured that an un-assigned variable on the data layer will be fine as long as it's defined on the presentation layer, but I don't know how to call it properly. Thoughts? Code below:

Data layer //this falls under 开发者_运维知识库the public class 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;
}

Business Logic layer

public class SubmissionService

{

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

}

Presentation Layer

 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

SubmissionService.getSubmissionByID() does not take a parameter. so you can't pass x. Change it's signature in the Business Layer like this.

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

and in the Data layer like this

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

You simply need to declare the parameter of your data layer method:

public Submission GetSubmissionsByID()

becomes

public Submission GetSubmissionsByID(string x)

HOWEVER, this leaves your system wide open for SQL-injections Read about parameterized SQL statements before trying to write SQL embedded in C# (or another language)

Your getSubmissionByID does not take a paramter, but you are passing one to it. You need to take the ID you are passing to give to the database to do the query.

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

You also need to have data access take a parameter, and then use the parameter in your query. The '+ x' is not a best practice. You should use query parameters.

    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
   }