SQL JOIN query in Asp.net

Question

I am using join query in Asp.net to join 5 tables. When i click on GridView row it gives id of that row and bind gridView on another page using sql join. But when click on any grid View row it throughs the correrct id but all the fields in sql join query shows null. Here is my .aspx page

<asp:GridView ID="GrdUni" runat="server" AutoGenerateColumns="False" DataKeyNames="u_id" DataSourceID="UniversityInfo">
    <Columns>
        <asp:BoundField DataField="u_id" HeaderText="u_id" ReadOnly="True" SortExpression="u_id" HtmlEncode="false" DataFormatString="<a target='_blank' href='Details.aspx?u_id={0}'>u_id</a>" />
        <asp:BoundField DataField="uni_name" HeaderText="uni_name" SortExpression="uni_name" />
        <asp:BoundField DataField="Type" HeaderText="Type" SortExpression="Type" />
        <asp:BoundField DataField="location" HeaderText="location" SortExpression="location" />
        <asp:BoundField DataField="specialization" HeaderText="specialization" SortExpression="specialization" />
        <asp:BoundField DataField="VC" HeaderText="VC" SortExpression="VC" />
        <asp:BoundField DataField="contact" HeaderText="contact" SortExpression="contact" />
    </Columns>
 </asp:GridView>

Here is the page which i open when click on id.

 <asp:GridView ID="GridView1" runat="server">
 </asp:GridView>

CS file:

protected void Page_Load(object sender, EventArgs e)
{
   Utility utl = new Utility();
   try
   {
       UniModel uniM = new UniModel();
       UniID = Convert.ToInt32(Request.QueryString["u_id"].ToString());
    //   GridView1.DataSource = uniM.GetById_University(UniID);
       GridView1.DataSource = utl.JoinTables(UniID);
       GridView1.DataBind();
    }
    catch (Exception exc)
    {
        throw exc;
    }
}

and here is my utility class that contains JoinTables() Method.

public DataTable JoinTables(int UniID)
{
  try
  {               
    return DAL.Get("select '" 
                   + facM.f_name + "' , '" 
                   + departM.depart_name + "' , '" 
                   + ProgM.p_name + "' ,'" 
                   + ProgM.Eligible_criteria + "','" 
                   + ProgM.seats_allocated 
             + "' from dbo.University JOIN dbo.faculties ON " 
                                  + facM.u_id + " = " + UniID 
             + " JOIN dbo.departments ON " + departM.f_id + " = " + facM.f_id 
             + " JOIN dbo.programs ON  " + ProgM.d_id + " = " + departM.d_id + " ");
        }

        catch (Exception exc)
        {
            throw exc;
        }
    }
}

My problem is that every field in join query passing null value instead of UniID.

Answer 1

I agree with other comments: it would be wise to look into using an ORM tool (e.g. Entity Framework, NHibernate, OrmLite, etc), or at least parameterized queries (as shown by @Joro), to help you create this SQL in a better way.

But barring that, you have a problem with the initial Universities and Faculties join. Basically, you are not using the University table in any way: it is not part of a join, and it is not part of the select or a where clause. You are also mixing together C# and SQL with the concatenations. I think you want something like this:

 return DAL.Get(@"select facM.f_name, 
                         departM.depart_name,
                         ProgM.p_name,
                         ProgM.Eligible_criteria,
                         ProgM.seats_allocated 
             from dbo.faculties facM 
             JOIN dbo.departments departM ON departM.f_id = facM.f_id 
             JOIN dbo.programs ProgM ON ProgM.d_id departM.d_id 
             WHERE facM.u_id = " + UniID;

Since UniId is already typed as an integer, this is safe from SQL injection attacks. But if you ever change the type to a string for some reason, then it is open to attack.

Answer 2

Your column names in the Grid do not match the query...

<Columns>
        <asp:BoundField DataField="u_id" HeaderText="u_id" ReadOnly="True" SortExpression="u_id" HtmlEncode="false" DataFormatString="<a target='_blank' href='Details.aspx?u_id={0}'>u_id</a>" />
        <asp:BoundField DataField="uni_name" HeaderText="uni_name" SortExpression="uni_name" />
        <asp:BoundField DataField="Type" HeaderText="Type" SortExpression="Type" />
        <asp:BoundField DataField="location" HeaderText="location" SortExpression="location" />
        <asp:BoundField DataField="specialization" HeaderText="specialization" SortExpression="specialization" />
        <asp:BoundField DataField="VC" HeaderText="VC" SortExpression="VC" />
        <asp:BoundField DataField="contact" HeaderText="contact" SortExpression="contact" />
    </Columns>

The columns in the query here...

"select '" 
                   + facM.f_name + "' , '" 
                   + departM.depart_name + "' , '" 
                   + ProgM.p_name + "' ,'" 
                   + ProgM.Eligible_criteria + "','" 
                   + ProgM.seats_allocated 

...are, f_name, depart_name etc. You need to make sure they match up, unless you are showing the wrong grid above?

Also, as mentioned in the comments, the code really needs to be altered to prevent SQL injection attacks.

Answer 3

I think you must rewrite the "JoinTables" method. This is only example how should look.

public DataTable JoinTables(int uniId)
{
    using (SqlDataAdapter adapter = new SqlDataAdapter() )
    {
        using (SqlCommand dbCommand = new SqlCommand())
        {
            DataTable table = new DataTable("University");
            try
            {
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandText = @"SELECT u.column1, t2.column2 FROM University u, table t2 WHERE t1.coulmn1 = t2.column2 and u.unitid = @UnitId";
                dbCommand.Parameters.AddWithValue("@UnitId", uniId);

                adapter.SelectCommand = dbCommand;
                adapter.SelectCommand.Connection = GetConnection();

                adapter.Fill(table);
            }
            finally
            {
                dbCommand.Parameters.Clear();

                if (null != dbCommand.Connection)
                {
                    if (dbCommand.Connection.State != System.Data.ConnectionState.Closed)
                        dbCommand.Connection.Close();
                    dbCommand.Connection = null;
                }
            }

            return table;
        }
    }
}

public SqlConnection GetConnection()
{
    SqlConnection connection = new SqlConnection(_connectionString);
    connection.Open();
    return connection;
}