public int GetIdFromName(string name)
{
string[] names = name.Split(' ');
string lastname = ""; //this is needed for cases where the employee has a last name with spaces i.e. 'van dyke'
for (int i = 1; i < names.Length; i++)
{
if (i == 1)
{
lastname += names[i];
}
else
{
lastname += " " + names[i];
}
}
SqlConnection connection = new SqlConnection(_connectionString);
string commandString = "select PersonID from dbo.Person AS P where P.FirstName = @Fname AND P.LastName = @Lname";
SqlCommand command = new SqlCommand(commandString);
try
{
command.Parameters.AddWithValue("@Fname", names[0]);
command.Parameters.AddWithValue("@Lname", lastname);
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
//people can have the same name, it could return a bunch of people
//with the same name, so get all those people
List<int> ids = new List<int>();
while (reader.Read())
{
ids.Add((int)reader["PersonID"]);
}
connection.Close();
//next iterate over all those people to see which employees are actually active employees
foreach (int currentID in ids)
{
SqlCommand secondCommand = new SqlCommand("select EmployeeID from dbo.InternalEmployee where EmployeeID=" + currentID);
secondCommand.Connection = new SqlConnection(_connectionString);
secondCommand.Connection.Open();
var result = secondCommand.ExecuteScalar();
secondCommand.Connection.Close();
if (result == null || result == DBNull.Value)
{
//do nothing and keep looking for a valid employee
}
else
{
//returns the id of the first valid employee
//it's possible this function returns the id of someone other than the person you were looking for
return currentID;
}
}
//if we got here, we didnt' find it in the internal employee, so return -1
return -1;
}
catch (NullReferenceException ex)
{
throw new Exception(ex.Message);
}
}
