Use inner join with C# SQL Server (Retrieving data from multiple table using SQL query and inner join in C# ADO.NET)

Question

Am trying to retrieve data from two different tables into a datatable and to assign into textboxes in C# WinForms. Though I succeeded with this query when I tried it in SQL Server but implementing it on WinForms is what I don't know. This is what have tried in my code:

 string supplier_id = dataGridView1.CurrentRow.Cells[1].Value.ToString();
                    SqlDataAdapter cmd = new SqlDataAdapter("select New_Supplier.Supplier_Name, New_Supplier.Address, New_Supplier.City, New_Supplier.Contact_No ,Purchase_Entry.Balance from New_Supplier,Purchase_Entry where New_Supplier.'" + supplier_id + "' = Purchase_Entry.'" + supplier_id + "' ", con);
                    DataTable dt = new DataTable(); cmd.Fill(dt);
                    Purchase_Entry.Instance.txtsuppliername.Text = dt.Rows[0][0].ToString();
                    Purchase_Entry.Instance.txtaddress.Text = dt.Rows[0][1].ToString();
                    Purchase_Entry.Instance.txtcity.Text = dt.Rows[0][2].ToString();
                    Purchase_Entry.Instance.txtcontactno.Text = dt.Rows[0][3].ToString();
                    Purchase_Entry.Instance.lblbalance.Text = dt.Rows[0][4].ToString();

Tried this also:

 string supplier_id = dataGridView1.CurrentRow.Cells[1].Value.ToString(); MessageBox.Show(supplier_id);
                    SqlDataAdapter cmd = new SqlDataAdapter($"select New_Supplier.Supplier_Name, New_Supplier.Address, New_Supplier.City, New_Supplier.Contact_No ,Purchase_Entry.Balance from New_Supplier,Purchase_Entry where New_Supplier.{supplier_id} = Purchase_Entry.{supplier_id} ", con);
                    DataTable dt = new DataTable(); cmd.Fill(dt);
                    Purchase_Entry.Instance.txtsuppliername.Text = dt.Rows[0][0].ToString();
                    Purchase_Entry.Instance.txtaddress.Text = dt.Rows[0][1].ToString();
                    Purchase_Entry.Instance.txtcity.Text = dt.Rows[0][2].ToString();
                    Purchase_Entry.Instance.txtcontactno.Text = dt.Rows[0][3].ToString();
                    Purchase_Entry.Instance.lblbalance.Text = dt.Rows[0][4].ToString();

But am having error concatenating the query when fetching data Tried this in SQL Server and it works

select New_Supplier.Supplier_Name, New_Supplier.Address, New_Supplier.City, New_Supplier.Contact_No ,Purchase_Entry.Balance from New_Supplier,Purchase_Entry where New_Supplier.supplier_id = Purchase_Entry.supplier_id

and it returned me back what I want:

Answer 1

I think you should write like this

SqlDataAdapter cmd =new SqlDataAdapter(
                    "select 
                    New_Supplier.Supplier_Name,
                    New_Supplier.Address,
                    New_Supplier.City,
                    New_Supplier.Contact_No,
                    Purchase_Entry.Balance
                    from New_Supplier,Purchase_Entry 
                    where New_Supplier.supplier_id = Purchase_Entry.supplier_id
                    and New_Supplier.supplier_id='"+supplier_id +"'
                 ", con);

or

SqlDataAdapter cmd =new SqlDataAdapter(
                    "select 
                    New_Supplier.Supplier_Name,
                    New_Supplier.Address,
                    New_Supplier.City,
                    New_Supplier.Contact_No,
                    Purchase_Entry.Balance
                    from New_Supplier
                    inner join Purchase_Entry on New_Supplier.supplier_id = 
                    Purchase_Entry.supplier_id
                    where New_Supplier.supplier_id='"+supplier_id +"'
                 ", con);

but it's better to use SQL parameter instead of concate string because of SQL injection as shown below:

    SqlCommand cmd = new SqlCommand(
     @"select 
            New_Supplier.Supplier_Name,
            New_Supplier.Address,
            New_Supplier.City,
            New_Supplier.Contact_No,
            Purchase_Entry.Balance
            from New_Supplier
            inner join Purchase_Entry on New_Supplier.supplier_id = 
            Purchase_Entry.supplier_id
            where New_Supplier.supplier_id= @supplier_id", con);
    
    cmd.Parameters.Add("@supplier_id",SqlDbType.Int).Value = supplier_id;
    cmd.CommandType = System.Data.CommandType.Text;
    SqlDataAdapter sda = new SqlDataAdapter(cmd);