using System.Data;
using System.Data.SqlClient;
namespace DAL
{
public static class PersonManager
{
public struct Person
{
public int PersonId;
public string FirstName;
public DateTime DOB;
public int DepartmentId;
}
public static Person GetPerson(int personId)
{
Person personData = new Person();
personData.PersonId = personId;
using (SqlConnection cn = new SqlConnection(Database.PersonPortalString))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "GetPerson";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@personId", personData.PersonId);
using (SqlDataReader dr = cm.ExecuteReader())
{
if (dr.Read())
{
personData = ReadPerson(dr);
}
else
{
throw new ApplicationException(string.Format("Person({0}) not found.", personData.PersonId));
}
}
}
}
return personData;
}
private static Person ReadPerson(SqlDataReader dr)
{
Person personData = new Person();
personData.DOB = (DateTime)dr["DOB"];
return personData;
}
public static List<Person> SearchPersons(Person personData)
{
List<Person> persons = new List<Person>();
using (SqlConnection cn = new SqlConnection(Database.PersonPortalString))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SearchPersons";
cm.CommandType = CommandType.StoredProcedure;
if (string.IsNullOrEmpty(personData.PersonCode) == true)
{
cm.Parameters.AddWithValue("@personCode", Convert.DBNull);
}
else
{
cm.Parameters.AddWithValue("@personCode", personData.PersonCode);
}
if(personData.DateOfJoining== DateTime.MinValue)
cm.Parameters.AddWithValue("@dateOfJoining", Convert.DBNull);
else
cm.Parameters.AddWithValue("@dateOfJoining", personData.DateOfJoining);
if (personData.DepartmentId == 0)
{
cm.Parameters.AddWithValue("@departmentID", Convert.DBNull);
}
else
{
cm.Parameters.AddWithValue("@departmentID", personData.DepartmentId);
}
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
persons.Add(ReadSearchedPerson(dr));
}
}
}
}
return persons;
}
}
}
using System.Data.SqlClient;
namespace DAL
{
public static class PersonManager
{
public struct Person
{
public int PersonId;
public string FirstName;
public DateTime DOB;
public int DepartmentId;
}
public static Person GetPerson(int personId)
{
Person personData = new Person();
personData.PersonId = personId;
using (SqlConnection cn = new SqlConnection(Database.PersonPortalString))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "GetPerson";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.AddWithValue("@personId", personData.PersonId);
using (SqlDataReader dr = cm.ExecuteReader())
{
if (dr.Read())
{
personData = ReadPerson(dr);
}
else
{
throw new ApplicationException(string.Format("Person({0}) not found.", personData.PersonId));
}
}
}
}
return personData;
}
private static Person ReadPerson(SqlDataReader dr)
{
Person personData = new Person();
personData.DOB = (DateTime)dr["DOB"];
return personData;
}
public static List<Person> SearchPersons(Person personData)
{
List<Person> persons = new List<Person>();
using (SqlConnection cn = new SqlConnection(Database.PersonPortalString))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SearchPersons";
cm.CommandType = CommandType.StoredProcedure;
if (string.IsNullOrEmpty(personData.PersonCode) == true)
{
cm.Parameters.AddWithValue("@personCode", Convert.DBNull);
}
else
{
cm.Parameters.AddWithValue("@personCode", personData.PersonCode);
}
if(personData.DateOfJoining== DateTime.MinValue)
cm.Parameters.AddWithValue("@dateOfJoining", Convert.DBNull);
else
cm.Parameters.AddWithValue("@dateOfJoining", personData.DateOfJoining);
if (personData.DepartmentId == 0)
{
cm.Parameters.AddWithValue("@departmentID", Convert.DBNull);
}
else
{
cm.Parameters.AddWithValue("@departmentID", personData.DepartmentId);
}
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
persons.Add(ReadSearchedPerson(dr));
}
}
}
}
return persons;
}
}
}
No comments:
Post a Comment