Today, we are going to write the client class, it is a pretty basic class which only will hold information abut the client. The class diagram is as below :
So this class will sit in the VeteransDBClasses project, and our web app as well as ou database access class will use this class to communicate with database. Watch out that the encrypted fields in the database is clear text here; so the functions that will use this class should do the encryption, html encodes so forth. I didnt even want to post the code for the class, as it is very easy and dummy object; however here it is :
public class obClient
{
public int ClientID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string MiddleName { get; set; }
public obAddress Address { get; set; }
public DateTime BirthDate { get; set; }
public string SSN { get; set; }
public obReferringAgency ReferringAgency { get; set; }
public string PrimaryDisability { get; set; }
public string SecondaryDisability { get; set; }
public string Notes { get; set; }
public string ServiceLocation { get; set; }
public obClient() { }
public obClient(int ClientID, string FirstName, string LastName, string MiddleName,
obAddress Address, DateTime BirthDate, string SSN,
string PrimaryDisability, string SecondaryDisability, string Notes, string ServiceLocation)
{
this.ClientID = ClientID;
this.FirstName = FirstName;
this.LastName = LastName;
this.MiddleName = MiddleName;
this.Address = Address;
this.BirthDate = BirthDate;
this.SSN = SSN;
//this.ReferringAgency = ReferringAgency;
this.PrimaryDisability = PrimaryDisability;
this.SecondaryDisability = SecondaryDisability;
this.Notes = Notes;
this.ServiceLocation = ServiceLocation;
}
public override string ToString()
{
return FirstName + " " + LastName;
}
}
So now we have to add a class to the VeteransDBAPI. This class will have functions such as add, edit, delete, list, search etc. Below is the class diagram:
All these functions either take a client class as a parameter, returns a client class, or a collection of client classes. So for example AddClient will take client class a parameter, call a stored procedure in the database and save the data in the database. Of course it will do the encryption and encoding stuff inside. This is same with GetAllClients, and other Get functions. So here is the code below; enjoy it :)
public static class ClientsTB
{
private static string ConnectionString = Properties.Settings.Default.DBConnString;
/// <summary>
/// Adds a client to the database
/// </summary>
/// <param name="Client">Client to be added to the system</param>
/// <returns>Client ID if successfull or -1 in failure</returns>
/// <seealso cref="obClient.cs"/>
/// <remarks>There is no html encoding, caller should encode the data</remarks>
public static int AddClient(VeteransDBClasses.obClient Client)
{
int ClientID = -1;
SqlConnection conn = new SqlConnection(ConnectionString);
try
{
SqlCommand cmd = new SqlCommand("spClientAdd", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ClientID", -1);
cmd.Parameters["@ClientID"].Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("@FirstName", Client.FirstName);
cmd.Parameters.AddWithValue("@LastName", Client.LastName);
cmd.Parameters.AddWithValue("@MiddleName", Client.MiddleName);
cmd.Parameters.AddWithValue("@StreetAddress", Client.Address.StreetAddress);
cmd.Parameters.AddWithValue("@City", Client.Address.City);
cmd.Parameters.AddWithValue("@State", Client.Address.State);
cmd.Parameters.AddWithValue("@Zip", Client.Address.Zip);
cmd.Parameters.AddWithValue("@Phone1", Client.Address.Phone1);
cmd.Parameters.AddWithValue("@Phone2", Client.Address.Phone2);
cmd.Parameters.AddWithValue("@Fax", Client.Address.Fax);
cmd.Parameters.AddWithValue("@Email", Client.Address.Email);
//TODO: for now it is always one, but in the future change this
cmd.Parameters.AddWithValue("@AgentID", 1);
cmd.Parameters.AddWithValue("@ServiceLocation", Client.ServiceLocation);
//now we have to encrypt some of the values
//first read the key
byte[] EncKey = Utilities.EncDec3DES.GetKeyFromFile(
Properties.Settings.Default.KeyFile);
byte[] DOB = Utilities.EncDec3DES.EncryptData(EncKey, Client.BirthDate.ToShortDateString());
byte[] SSN = Utilities.EncDec3DES.EncryptData(EncKey, Client.SSN);
byte[] PrimaryDisability = Utilities.EncDec3DES.EncryptData(EncKey,
Client.PrimaryDisability);
byte[] SecondaryDisability = Utilities.EncDec3DES.EncryptData(EncKey,
Client.SecondaryDisability);
byte[] Notes = Utilities.EncDec3DES.EncryptData(EncKey, Client.Notes);
cmd.Parameters.AddWithValue("@DOB", DOB);
cmd.Parameters.AddWithValue("@SSN", SSN);
cmd.Parameters.AddWithValue("@PrimaryDisability", PrimaryDisability);
cmd.Parameters.AddWithValue("@SecondaryDisability", SecondaryDisability);
cmd.Parameters.AddWithValue("@Notes", Notes);
conn.Open();
cmd.ExecuteNonQuery();
ClientID = Int32.Parse(cmd.Parameters["@ClientID"].Value.ToString());
}
catch
{
ClientID = -1;
}
finally
{
conn.Close();
}
return ClientID;
}
/// <summary>
/// Returns a list of clients in the collection of Clients
/// </summary>
/// <returns>if successfull list of clients, null in failure</returns>
/// <seealso cref="obClient.cs"/>
public static List<VeteransDBClasses.obClient> GetAllClients()
{
SqlConnection conn = new SqlConnection(ConnectionString);
List<VeteransDBClasses.obClient>Clients = new List<VeteransDBClasses.obClient>();
try
{
SqlCommand cmd = new SqlCommand("spClientGetAllClients", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int ClientID = (int)reader["ClientID"];
string FirstName = (string)reader["FirstName"];
string LastName = (string)reader["LastName"];
string MiddleName = (string)reader["MiddleName"];
string StreetAddress = (string)reader["StreetAddress"];
string City = (string)reader["City"];
string State = (string)reader["State"];
string Zip = (string)reader["Zip"];
string Phone1 = (string)reader["Phone1"];
string Phone2 = (string)reader["Phone2"];
string Fax = (string)reader["Fax"];
string Email = (string)reader["Email"];
int AgentID = 1;
string ServiceLocation = (string)reader["ServiceLocation"];
byte[] DOB = (byte[])reader["DOB"];
byte[] SSN = (byte[])reader["SSN"];
byte[] PrimaryDisability = (byte[])reader["PrimaryDisability"];
byte[] SecondaryDisability = (byte[])reader["SecondaryDisability"];
byte[] Notes = (byte[])reader["Notes"];
byte[] EncKey = Utilities.EncDec3DES.GetKeyFromFile(
Properties.Settings.Default.KeyFile);
string strDOB = Utilities.EncDec3DES.DecryptData(EncKey, DOB);
string strSSN = Utilities.EncDec3DES.DecryptData(EncKey, SSN);
string strPrimaryDisability = Utilities.EncDec3DES.DecryptData(EncKey, PrimaryDisability);
string strSecondaryDisability = Utilities.EncDec3DES.DecryptData(EncKey, SecondaryDisability);
string strNotes = Utilities.EncDec3DES.DecryptData(EncKey, Notes);
VeteransDBClasses.obAddress Address = new VeteransDBClasses.obAddress();
Address.StreetAddress = StreetAddress;
Address.City = City;
Address.State = State;
Address.Zip = Zip;
Address.Phone1 = Phone1;
Address.Phone2 = Phone2;
Address.Fax = Fax;
Address.Email = Email;
VeteransDBClasses.obClient Client = new VeteransDBClasses.obClient();
Client.Address = Address;
Client.FirstName = FirstName;
Client.LastName = LastName;
Client.MiddleName = MiddleName;
Client.ClientID = ClientID;
Client.PrimaryDisability = strPrimaryDisability;
Client.SecondaryDisability = strSecondaryDisability;
Client.Notes = strNotes;
Client.ServiceLocation = ServiceLocation;
Client.SSN = strSSN;
Client.BirthDate = DateTime.Parse(strDOB);
Clients.Add(Client);
}//while loop
}//try
catch
{
Clients = null;
}
finally
{
conn.Close();
}
return Clients;
}
/// <summary>
/// Returns a client by supplied client id
/// </summary>
/// <param name="ClientID">ID of the client</param>
/// <returns>if a client is found returns the client, else null</returns>
/// <seealso cref="obClient.cs"/>
public static VeteransDBClasses.obClient GetClientByClientID(int ClientID)
{
SqlConnection conn = new SqlConnection(ConnectionString);
VeteransDBClasses.obClient Client = new VeteransDBClasses.obClient();
try
{
SqlCommand cmd = new SqlCommand("spClientGetClientByClientID", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ClientID",ClientID);
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
{
int iClientID = (int)reader["ClientID"];
string FirstName = (string)reader["FirstName"];
string LastName = (string)reader["LastName"];
string MiddleName = (string)reader["MiddleName"];
string StreetAddress = (string)reader["StreetAddress"];
string City = (string)reader["City"];
string State = (string)reader["State"];
string Zip = (string)reader["Zip"];
string Phone1 = (string)reader["Phone1"];
string Phone2 = (string)reader["Phone2"];
string Fax = (string)reader["Fax"];
string Email = (string)reader["Email"];
int AgentID = 1;
string ServiceLocation = (string)reader["ServiceLocation"];
byte[] DOB = (byte[])reader["DOB"];
byte[] SSN = (byte[])reader["SSN"];
byte[] PrimaryDisability = (byte[])reader["PrimaryDisability"];
byte[] SecondaryDisability = (byte[])reader["SecondaryDisability"];
byte[] Notes = (byte[])reader["Notes"];
byte[] EncKey = Utilities.EncDec3DES.GetKeyFromFile(
Properties.Settings.Default.KeyFile);
string strDOB = Utilities.EncDec3DES.DecryptData(EncKey, DOB);
string strSSN = Utilities.EncDec3DES.DecryptData(EncKey, SSN);
string strPrimaryDisability = Utilities.EncDec3DES.DecryptData(EncKey, PrimaryDisability);
string strSecondaryDisability = Utilities.EncDec3DES.DecryptData(EncKey, SecondaryDisability);
string strNotes = Utilities.EncDec3DES.DecryptData(EncKey, Notes);
VeteransDBClasses.obAddress Address = new VeteransDBClasses.obAddress();
Address.StreetAddress = StreetAddress;
Address.City = City;
Address.State = State;
Address.Zip = Zip;
Address.Phone1 = Phone1;
Address.Phone2 = Phone2;
Address.Fax = Fax;
Address.Email = Email;
Client.Address = Address;
Client.FirstName = FirstName;
Client.LastName = LastName;
Client.MiddleName = MiddleName;
Client.ClientID = iClientID;
Client.PrimaryDisability = strPrimaryDisability;
Client.SecondaryDisability = strSecondaryDisability;
Client.Notes = strNotes;
Client.ServiceLocation = ServiceLocation;
Client.SSN = strSSN;
Client.BirthDate = DateTime.Parse(strDOB);
}
}//try
catch
{
Client = null;
}
finally
{
conn.Close();
}
return Client;
}
/// <summary>
/// Searches a client either using a last name of first name
/// </summary>
/// <param name="keyword">first name or last name</param>
/// <returns>client is successfull; null in failure</returns>
/// <seealso cref="VeteransDBClasses.obClient"/>
public static VeteransDBClasses.obClient GetClientByFirstNameOrLastName(string keyword)
{
SqlConnection conn = new SqlConnection(ConnectionString);
VeteransDBClasses.obClient Client = new VeteransDBClasses.obClient();
try
{
SqlCommand cmd = new SqlCommand("ClientGetByFirstNameOrLastName", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Keyword", keyword);
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
int iClientID = (int)reader["ClientID"];
string FirstName = (string)reader["FirstName"];
string LastName = (string)reader["LastName"];
string MiddleName = (string)reader["MiddleName"];
string StreetAddress = (string)reader["StreetAddress"];
string City = (string)reader["City"];
string State = (string)reader["State"];
string Zip = (string)reader["Zip"];
string Phone1 = (string)reader["Phone1"];
string Phone2 = (string)reader["Phone2"];
string Fax = (string)reader["Fax"];
string Email = (string)reader["Email"];
int AgentID = 1;
string ServiceLocation = (string)reader["ServiceLocation"];
byte[] DOB = (byte[])reader["DOB"];
byte[] SSN = (byte[])reader["SSN"];
byte[] PrimaryDisability = (byte[])reader["PrimaryDisability"];
byte[] SecondaryDisability = (byte[])reader["SecondaryDisability"];
byte[] Notes = (byte[])reader["Notes"];
byte[] EncKey = Utilities.EncDec3DES.GetKeyFromFile(
Properties.Settings.Default.KeyFile);
string strDOB = Utilities.EncDec3DES.DecryptData(EncKey, DOB);
string strSSN = Utilities.EncDec3DES.DecryptData(EncKey, SSN);
string strPrimaryDisability = Utilities.EncDec3DES.DecryptData(EncKey, PrimaryDisability);
string strSecondaryDisability = Utilities.EncDec3DES.DecryptData(EncKey, SecondaryDisability);
string strNotes = Utilities.EncDec3DES.DecryptData(EncKey, Notes);
VeteransDBClasses.obAddress Address = new VeteransDBClasses.obAddress();
Address.StreetAddress = StreetAddress;
Address.City = City;
Address.State = State;
Address.Zip = Zip;
Address.Phone1 = Phone1;
Address.Phone2 = Phone2;
Address.Fax = Fax;
Address.Email = Email;
Client.Address = Address;
Client.FirstName = FirstName;
Client.LastName = LastName;
Client.MiddleName = MiddleName;
Client.ClientID = iClientID;
Client.PrimaryDisability = strPrimaryDisability;
Client.SecondaryDisability = strSecondaryDisability;
Client.Notes = strNotes;
Client.ServiceLocation = ServiceLocation;
Client.SSN = strSSN;
Client.BirthDate = DateTime.Parse(strDOB);
}
catch
{
Client = null;
}
finally
{
conn.Close();
}
return Client;
}
}