Developer/Programmer interview practical test and solution

Below is a simple practical test which may be asked in interviews and the candidate may be expected to solve the problem within a duration of 40 to 45 mins .

I have described the problem and given the solution for it.

Question: A list of users and the manager they report to are available in the database. You need to display
a). Count of all users who have a manager.
b). Count of all users who do not have a manager.
c). Display list of users and the managers they report to.


Answer:

When solving the problem , keep in mind the following:

a. Design solution with clean architecture.
- Properly layer your solution. Use separate project for data layer, business layer and presentation layer. Have a single solution file which combines all three project files.

b. The test is for you to exhibit your problem solving/coding skills.
Hence, avoid writing code which binds UI controls (ex: Dropdown,Repeater control) to DataSource controls(SQL, Xml or Object) . This will provide the required result, but will give very less opportunity for the interviewer to guage your coding style.

c. Include error handling code. try {} catch {} blocks.

d. Read your connection strings from web.config.


It is important to remember The important aspect to remember when writing solution for these kind of problems is that: Potential Employers look for the following
The plan is:

Step 1:

A stored procedure which returns all three results together.
Alternatively, it can be done by having a stored procedure for each result and then have a fourth stored procedure which returns the results of executing each stored procedure altogether.

The queries are

a). Count of all users who have a manager:

SELECT COUNT(*) AS UserWithManagers FROM Users Where Manager IS NOT NULL

b). Count of all users who do not have a manager

SELECT COUNT(*) AS UserWOManagers FROM Users Where Manager IS NULL

c). Display list of users and the managers they report to.

SELECT Users.UserID, Users.UserName, Managers.UserName, Managers.UserID FROM Users
LEFT JOIN
(SELECT u.UserName, u.UserID FROM users u WHERE userid IN
(SELECT Manager FROM Users WHERE Manager IS NOT NULL)
)
Managers ON users.Manager = managers.UserID


Step 2:

Create 2 classes, first class is for holding each User and another class for populating the user collection.


Code for Class1 :

User class

//Description : Class to hold user details

using System;
using System.Web;

///

/// Summary description for Users
///

public class Users
{
public string UserName { get; set; }
public int UserID { get; set; }
public string ManagerName { get; set; }

}




Code for Class 2:

UserManager Class

//Description : Class generates collection of users and also exposes //properties for getting the count of users with managers and without //managers.

using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;


///

/// Summary description for UserManager
///

public class UserManager
{
string _UsersWithoutManager;
string _UsersWithManager;

public List GetUsersList()
{
try
{
SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ToString());
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand("UserManagerReport", sqlConn);

SqlDataReader sqlRdr = sqlCmd.ExecuteReader();

if (sqlRdr.HasRows)
{
if (sqlRdr.Read())
{
_UsersWithManager = sqlRdr["UserWithManagers"].ToString();
}

sqlRdr.NextResult();
if (sqlRdr.Read())
{
_UsersWithoutManager = sqlRdr["UserWOManagers"].ToString();
}

sqlRdr.NextResult();
List colUsers = new List();

while (sqlRdr.Read())
{
Users tUser = new Users();
tUser.UserID = int.Parse(sqlRdr["UserID"].ToString());
tUser.UserName = sqlRdr["UserName"].ToString();
tUser.ManagerName = sqlRdr["ManagerName"].ToString();

colUsers.Add(tUser);
}

return colUsers;
}
return null;
}
catch (Exception ex)
{
return null;
}
}


public string UsersWithoutManager
{ get { return _UsersWithoutManager; } }


public string UsersWithManager { get { return _UsersWithManager; } }}

Step 3:

Bind the resulting User collection to the UI

Code below is for a page:

To get this code working, add 2 lable controls and a data grid control.
Name the lable and datagrid as

label1 id = lblUsersWithManager
label2 id = lblUsersWithOutManager
datagrid = datUsersAndManagers

protected void Page_Load(object sender, EventArgs e)
{
populateUsers();
}

private void populateUsers()
{
//Get users as collection
UserManager cUserManager = new UserManager();
List colUsers= cUserManager.GetUsersList();

//bind users to datagrid
datUsersAndManagers.DataSource = colUsers;
datUsersAndManagers.DataBind();

// update the user counts
lblUsersWithManager.Text = cUserManager.UsersWithManager;
lblUsersWithOutManager.Text = cUserManager.UsersWithoutManager;

}


This will populate data from the database, build a collection and then will bind the results to a datagrid.