c sharp and sql

Question

I have a employee table which contains employee information and a Contact details table which contains the phone numbers of the employees. the employees have more than 2 phone numbers.

now, to display the employee information, i have a datagrid. what i want to do is display the first 2 numbers along with the employee information in the datagrid.

i use the following method to fill the data grid

    public static void SignUpControllerDay(DateTime Date, System.Windows.Forms.DataGridView PassedGrid)
    {
        string sql_SignUp = String.Format(@"SELECT e.Emp_ID as Emp_ID,
                                      e.First_Name+ ' ' +e.Last_Name as Name,
                                      sum(o.Quantity) as Sum
                                      FROM Employee e,OT_hours o,Position p,Signup_Sheet s
                                      WHERE e.Emp_ID=o.Emp_ID
                                      and e.Emp_ID = s.Employee_ID
                                      and s.Day_Shift = 1
                                      and e.Position_ID = p.Position_ID
                                      and p.Position_Name = 'Controller'
                                      and o.Quantity NOT IN(0.3)
                                            and s.Date = '{0}'
                                      and o.Date <= CONVERT(VARCHAR,'{0}',101) AND o.Date > CONVERT(VARCHAR,DATEADD(YYYY,-1,'{0}'),101)
                                      GROUP BY e.Emp_ID,e.First_Name+' '+e.Last_Name,p.Position_Name
                                      ORDER BY Sum", Date);

        SqlConnection sqlConn = null;
        SqlCommand cmd_SignUp;
        SqlDataReader dr_SignUp;
        try
        {
            sqlConn = new SqlConnection(databaseConnectionString);
            sqlConn.Open();
            cmd_SignUp = new SqlCommand(sql_SignUp, sqlConn);
            dr_SignUp = cmd_SignUp.ExecuteReader();

            while (dr_SignUp.Read())
            {
                PassedGrid.Rows.Add(dr_SignUp["Emp_ID"].ToString(), dr_SignUp["Name"].ToString(), dr_SignUp["Sum"].ToString());
            }

        }
        catch (Exception e)
        {
            MessageBox.Show("Error found in SignUpControllerDay..." + Environment.NewLine + e.ToString());
        }
        finally
        {
            if (sqlConn != null)
            {
                sqlConn.Close();
            }
        }
    }

the above method displays the empid,name,sum of the employees. all i want to do is display any 2 phone numbers from the contact_details table. i tried using a data reader to get the phone numbers based on the employee id, but it did not work.

please help....

Answer 1

You don't reference the contact_details table anywhere in the SQL. You need to join the Employee and contact_details tables to include the phone numbers.

Answer 2

I think I know what reggie wants to do but before I give you any help reggie lets take a look at a couple of things that you may want to look at:

1. As mentioned do not use String.Format or Dynamic SQL, if you are on SQL Server make use of stored procedures with parameterized SQL Command objects
2. You are having the client (web app or windows app) doing too much work for something that is really data intensive (SQL work rather then C# work)
3. You do not need to loop at all or even use a datareader as it is just bloating your code and unnecessary
4. In your finally clause you close the connection but you never set your connection or the actual sql command object to null. I know C# garbage collects but it is a good point of practice to do so.
5. SQL Server 2005 and up brings to us the PIVOT key word, look it up in BOL

In regards to your question if you are using SQL Server 2005+ you can use PIVOT. Given that you mentioned there can be 2 contact numbers let us assume one is work and one is home.

You could do this:

SELECT 
      FullName, 
      [Work], 
      [Home] 
FROM
(SELECT 
       l.FullName, 
       p.PhoneType, 
       p.PhoneNumber 
 FROM 
       Login l 
 INNER JOIN 
       Phone p 
 ON p.LoginID = l.LoginID) ps
PIVOT
(
 MAX(ps.PhoneNumber)
FOR
 ps.PhoneType IN ([Home], [Work])
) AS pvt

Login is simply your Employees table and Phone is your contacts table with the phone number. I assume an employee id can be found in the contacts table (that is Login.LoginID = Phone.LoginID) or in your case Employee.EmpID=Contacts.EmpID.

So this:

With a standard query not using pivot then becomes this...:

When using pivot.