public ICollection<Person> SearchExternalPeople(string searchTerm)
{
ICollection<Person> people = new List<Person>();//proWatchDB
var proWatchConnectionString = ConfigurationManager.ConnectionStrings["proWatchDB"];
string commandText = "SELECT BADGE_TRN AS TRN, FNAME AS FirstName, MI AS MiddleInitial, LNAME AS LastName FROM [ACS].[dbo].Employees WHERE FNAME LIKE @searchTerm OR "
+ "LNAME LIKE @searchTerm OR MI LIKE @searchTerm ";
using (SqlConnection connection = new SqlConnection(proWatchConnectionString.ConnectionString))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add(new SqlParameter("searchTerm", String.Format("%{0}%", searchTerm)));
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
// Check is the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
Person p = new Person();
//// To avoid unexpected bugs access columns by name.
//p.ID = reader.GetInt32(reader.GetOrdinal("ID"));
int firstNameIndex = reader.GetOrdinal("FirstName");
if (!reader.IsDBNull(firstNameIndex))
{
p.FirstName = reader.GetString(firstNameIndex);
}
int lastNameIndex = reader.GetOrdinal("LastName");
if (!reader.IsDBNull(lastNameIndex))
{
p.LastName = reader.GetString(lastNameIndex);
}
int middleInitalIndex = reader.GetOrdinal("MiddleInitial");
if (!reader.IsDBNull(middleInitalIndex))
{
p.MiddleInitial = reader.GetString(middleInitalIndex);
}
int TRNIndex = reader.GetOrdinal("TRN");
if (!reader.IsDBNull(TRNIndex))
{
p.IdNumber = reader.GetString(TRNIndex);
}
people.Add(p);
}
}
}
}
return people;
}
public ICollection<Person> GetExternalPeopleByTRN(ICollection<string> personTRNs)
{
ICollection<Person> people = new List<Person>();//proWatchDB
var listOfTRNAsString = string.Join(",", personTRNs.Select(c => string.Format("'{0}'", c)));
var proWatchConnectionString = ConfigurationManager.ConnectionStrings["proWatchDB"];
using (SqlConnection connection = new SqlConnection(proWatchConnectionString.ConnectionString))
{
var sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
string commandText = "SELECT BADGE_TRN AS TRN, FNAME AS FirstName, MI AS MiddleInitial, LNAME AS LastName FROM [ACS].[dbo].Employees WHERE BADGE_TRN IN ({0})";
var trnList = personTRNs;
var nameParameter = new List<string>();
var index = 0; // Reset the index
foreach (var trn in trnList)
{
var paramName = "@trnParam" + index;
sqlCommand.Parameters.AddWithValue(paramName, trn);
nameParameter.Add(paramName);
index++;
}
sqlCommand.CommandText = String.Format(commandText, string.Join(",", nameParameter));
connection.Open();
using (SqlDataReader reader = sqlCommand.ExecuteReader())
{
// Check is the reader has any rows at all before starting to read.
if (reader.HasRows)
{
// Read advances to the next row.
while (reader.Read())
{
Person p = new Person();
//// To avoid unexpected bugs access columns by name.
//p.ID = reader.GetInt32(reader.GetOrdinal("ID"));
int firstNameIndex = reader.GetOrdinal("FirstName");
if (!reader.IsDBNull(firstNameIndex))
{
p.FirstName = reader.GetString(firstNameIndex);
}
int lastNameIndex = reader.GetOrdinal("LastName");
if (!reader.IsDBNull(lastNameIndex))
{
p.LastName = reader.GetString(lastNameIndex);
}
int middleInitalIndex = reader.GetOrdinal("MiddleInitial");
if (!reader.IsDBNull(middleInitalIndex))
{
p.MiddleInitial = reader.GetString(middleInitalIndex);
}
int TRNIndex = reader.GetOrdinal("TRN");
if (!reader.IsDBNull(TRNIndex))
{
p.IdNumber = reader.GetString(TRNIndex);
}
people.Add(p);
}
}
}
}
return people;
}