title: Display data on grid from multiple tables in c# while using join in stored procedure
body: <p>I am writing code in C# where I want to select data in datagrid from multiple tables with a relation... Here I have a <code>Client</code> &amp; <code>Item_Configuration</code> as parent table and <code>Item_Order</code> as child table which has the foreign keys from <code>Client</code> and <code>Item_Configuration</code>, I just want to fetch data from all three tables and display on a datagrid</p>
  
  <p>My stored procedure is:</p>
  
  <pre><code>ALTER PROC [dbo].[Full_SP]
      @clientName varchar(50) = null,
      @itemName varchar(50) = null,
      @clientId_FK varchar(50) = null,
      @operation int
  AS
  BEGIN
      SET NOCOUNT ON;
  
      if @operation = 2
      BEGIN
          SELECT 
              Client.clintName, Item_Configuration.itemName,
              Item_Order.orderId, Item_Order.orderDate, Item_Order.quantity,
              Item_Order.status, Item_Order.totalPrice 
          FROM
              Item_Order 
          INNER JOIN 
              Client ON Item_Order.clintId_FK = Client.clientId
          JOIN
              Item_Configuration ON Item_Order.itemId_FK = Item_Configuration.itemId
       END
  END
  </code></pre>
  
  <p>And my function of search to data grid is in C# i.e.</p>
  
  <pre><code>private void btnSrchFull_Click(object sender, EventArgs e)
  {
          SqlConnection conn1 = new SqlConnection();
  
          try
          {
              conn1.ConnectionString = "server=.\\ms2k5;database=Info_Connect;Trusted_Connection=true";
              conn1.Open();
  
              SqlCommand selectFull = new SqlCommand("Full_SP", conn1);
              selectFull.CommandType = CommandType.StoredProcedure;
  
              selectFull.Parameters.Add("@operation", SqlDbType.VarChar);
              selectFull.Parameters["@operation"].Value = 2;
  
              SqlDataReader myReader = selectFull.ExecuteReader();
  
              List&lt;FullFill&gt; list = new List&lt;FullFill&gt;();
  
              while (myReader.Read())
              {
                  if (myReader.HasRows)
                  {
                      FullFill fullfill = new FullFill();
                      fullfill = MapFullfill(myReader, fullfill);
                      list.Add(fullfill);
                  }
              }
  
              myReader.NextResult();
  
              foreach (FullFill ffll in list)
              {
                  if (myReader.Read() &amp;&amp; myReader.HasRows)
                  {
                         MapClient(myReader, ffll);
                  }
              }
  
              myReader.NextResult();
  
              foreach (FullFill ffll1 in list)
              {
                  if (myReader.Read() &amp;&amp; myReader.HasRows)
                  {
                      MapItem(myReader, ffll1);
                  }
              }
  
              dataGridView1.DataSource = list;
              double totPrice = 0;
  
              for (int i = 0; i &lt; dataGridView1.RowCount; i++)
              {
                      totPrice = totPrice +
                      Convert.ToDouble(dataGridView1.Rows[i].Cells[5].Value);
                      totCost.Text = totPrice.ToString();
              }
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.StackTrace + MessageBoxIcon.Error);
          }
          finally
          {
              if (conn1.State != ConnectionState.Closed)
              {
                  conn1.Close();
              }
          }
      }
  
      private FullFill MapItem(SqlDataReader myReader, FullFill itemName)
      {
          itemName.ItemName =myReader["itemName"].ToString();
          return itemName;
      }
  
      private FullFill MapClient(SqlDataReader myReader, FullFill clientName)
      {
          clientName.ClientName = myReader["clientName"].ToString();
          return clientName;
      }
  
      private FullFill MapFullfill(SqlDataReader myReader, FullFill fullfill)
      {
          fullfill.OrderNo = myReader["orderId"].ToString();
  
          fullfill.OrderDate = Convert.ToDateTime(myReader["orderDate"]);
          fullfill.Quantity = Convert.ToInt32(myReader["quantity"]);
          fullfill.Status = myReader["status"].ToString();
          fullfill.TotalPrice = Convert.ToDouble(myReader["totalPrice"]);
  
          return fullfill;
      }
  </code></pre>
  
  <p>and I created a class for property i.e.</p>
  
  <pre><code>class FullFill
  {
      public string orderNo;
      public string clientName;
      public DateTime orderDate;
      public string itemName;
      public int quantity;
      public double totCost;
      public string status;
  
      public string OrderNo { get { return orderNo; } set { orderNo = value; } }
      public string ClientName { get { return clientName; } set { clientName = value; } }
      public DateTime OrderDate { get { return orderDate; } set { orderDate = value; } }
      public string ItemName { get { return itemName; } set { itemName = value; } }
      public int Quantity { get { return quantity; } set { quantity = value; } }
      public double TotalPrice { get { return totCost; } set { totCost = value; } }
      public string Status { get { return status; } set { status = value; } }
      }
   }
  </code></pre>
  
  <p>The problem is that I am only able to find data from child table(Item_Order) I am not getting data from parent Tables.</p>