using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace dataClasses
{
/// <summary>
/// Summary description for database.
/// </summary>
public class dataFunctions
{
#region Local Variable Initialization
//These are the local variables used throughout the class
//Most of these are the vars used for the accessor methods
//In region "Getter/Setter Things"
//Each on is a field for the SP
private string _address_line1;
private string _address_line2;
private string _address_line3;
private string _city;
private int _state_id;
private int _postal_code;
private string _country;
private int _prefix_id;
private string _first_name;
private string _last_name;
private string _home_phone;
private string _cell_phone;
private string _work_phone;
private string _fax;
private string _email;
private DateTime _dob;
private int _address_id;
private int _person_id;
//Used to return results for each IUD method
private int results;
#endregion
#region Getter/Setter Things
//All of these are pretty much self explanatory.
//These are all accessible by using, for example
// dataClasses.dataFunctions df = new dataClasses.dataFunctions();
// df.Address_ID = "whatever";
public int Address_ID
{
get { return _address_id; }
set { _address_id = value; }
}
public int Person_ID
{
get { return _person_id; }
set { _person_id = value; }
}
public string Address_Line1
{
get { return _address_line1; }
set { _address_line1 = value; }
}
public string Address_Line2
{
get { return _address_line2; }
set { _address_line2 = value; }
}
public string Address_Line3
{
get { return _address_line3; }
set { _address_line3 = value; }
}
public string City
{
get { return _city; }
set { _city = value; }
}
public int State_ID
{
get { return _state_id; }
set { _state_id = value; }
}
public int Postal_Code
{
get { return _postal_code; }
set { _postal_code = value; }
}
public string Country
{
get { return _country; }
set { _country = value; }
}
public int Prefix_ID
{
get { return _prefix_id; }
set { _prefix_id = value; }
}
public string First_Name
{
get { return _first_name; }
set { _first_name = value; }
}
public string Last_Name
{
get { return _last_name; }
set { _last_name = value; }
}
public string Home_Phone
{
get { return _home_phone; }
set { _home_phone = value; }
}
public string Cell_Phone
{
get { return _cell_phone; }
set { _cell_phone = value; }
}
public string Work_Phone
{
get { return _work_phone; }
set { _work_phone = value; }
}
public string Fax
{
get { return _fax; }
set { _fax = value; }
}
public string Email
{
get { return _email; }
set { _email = value; }
}
public DateTime DOB
{
get { return _dob; }
set { _dob = value; }
}
#endregion
#region Constructor Logic
public dataFunctions()
{
//Constructor. Set everything to nothing. Har!
_address_line1 = "";
_address_line2 = "";
_address_line3 = "";
_city = "";
_state_id = 0;
_postal_code = 0;
_country = "";
_prefix_id = 0;
_first_name = "";
_last_name = "";
_home_phone = "";
_cell_phone = "";
_work_phone = "";
_fax = "";
_email = "";
_dob = System.DateTime.Now; //I just set something safe in here.
_address_id = 0;
_person_id = 0;
}
#endregion
#region InsertCommand
public int dbInsert()
{
//Create generic connection hooplah
//Get the connection string from the web.config file
//For easy portability
SqlConnection cnInsert = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
SqlCommand cmInsert = new SqlCommand("sp_IUD", cnInsert);
cmInsert.Connection = cnInsert;
cmInsert.CommandType = CommandType.StoredProcedure;
#region Parameters
//All the massive amounts of parameters to input
//The IUD values are hardcoded.
//0 -- Insert
//1 -- Update
//2 -- Delete
cmInsert.Parameters.Add("@IUD", SqlDbType.Int);
cmInsert.Parameters["@IUD"].Value = 0;
cmInsert.Parameters.Add("@ADDRESS_LINE1", SqlDbType.Char);
cmInsert.Parameters["@ADDRESS_LINE1"].Value = _address_line1;
cmInsert.Parameters.Add("@ADDRESS_LINE2", SqlDbType.Char);
cmInsert.Parameters["@ADDRESS_LINE2"].Value = _address_line2;
cmInsert.Parameters.Add("@ADDRESS_LINE3", SqlDbType.Char);
cmInsert.Parameters["@ADDRESS_LINE3"].Value = _address_line3;
cmInsert.Parameters.Add("@CITY", SqlDbType.Char);
cmInsert.Parameters["@CITY"].Value = _city;
cmInsert.Parameters.Add("@STATE_ID", SqlDbType.Int);
cmInsert.Parameters["@STATE_ID"].Value = _state_id;
cmInsert.Parameters.Add("@POSTAL_CODE", SqlDbType.BigInt);
cmInsert.Parameters["@POSTAL_CODE"].Value = _postal_code;
cmInsert.Parameters.Add("@COUNTRY", SqlDbType.Char);
cmInsert.Parameters["@COUNTRY"].Value = _country;
cmInsert.Parameters.Add("@PREFIX_ID", SqlDbType.Int);
cmInsert.Parameters["@PREFIX_ID"].Value = _prefix_id;
cmInsert.Parameters.Add("@FIRST_NAME", SqlDbType.Char);
cmInsert.Parameters["@FIRST_NAME"].Value = _first_name;
cmInsert.Parameters.Add("@LAST_NAME", SqlDbType.Char);
cmInsert.Parameters["@LAST_NAME"].Value = _last_name;
cmInsert.Parameters.Add("@HOME_PHONE", SqlDbType.Char);
cmInsert.Parameters["@HOME_PHONE"].Value = _home_phone;
cmInsert.Parameters.Add("@WORK_PHONE", SqlDbType.Char);
cmInsert.Parameters["@WORK_PHONE"].Value = _work_phone;
cmInsert.Parameters.Add("@CELL_PHONE", SqlDbType.Char);
cmInsert.Parameters["@CELL_PHONE"].Value = _cell_phone;
cmInsert.Parameters.Add("@FAX", SqlDbType.Char);
cmInsert.Parameters["@FAX"].Value = _fax;
cmInsert.Parameters.Add("@EMAIL", SqlDbType.Char);
cmInsert.Parameters["@EMAIL"].Value = _email;
cmInsert.Parameters.Add("@DOB", SqlDbType.DateTime);
cmInsert.Parameters["@DOB"].Value = _dob;
#endregion
//Test to see if any errors happen when actually making the connection
try
{
cnInsert.Open();
results = cmInsert.ExecuteNonQuery();
}
catch(Exception e)
{
throw(e);
}
//return an int representing how many rows were affected.
//Combined with the error handling, this gives a good idea
//As to whether or not it worked
return results;
}
#endregion
#region Update Command
public int dbUpdate()
{
//Generic Connection info
SqlConnection cnUpdate = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
SqlCommand cmUpdate = new SqlCommand("sp_IUD", cnUpdate);
cmUpdate.Connection = cnUpdate;
cmUpdate.CommandType = CommandType.StoredProcedure;
#region Parameters
//These next two parameters define which rows to update in the db
cmUpdate.Parameters.Add("@ADDRESS_ID", SqlDbType.Int);
cmUpdate.Parameters["@ADDRESS_ID"].Value = _address_id;
cmUpdate.Parameters.Add("@PERSON_ID", SqlDbType.Int);
cmUpdate.Parameters["@PERSON_ID"].Value = _person_id;
//More parameters
cmUpdate.Parameters.Add("@IUD", SqlDbType.Int);
cmUpdate.Parameters["@IUD"].Value = 1;
cmUpdate.Parameters.Add("@ADDRESS_LINE1", SqlDbType.Char);
cmUpdate.Parameters["@ADDRESS_LINE1"].Value = _address_line1;
cmUpdate.Parameters.Add("@ADDRESS_LINE2", SqlDbType.Char);
cmUpdate.Parameters["@ADDRESS_LINE2"].Value = _address_line2;
cmUpdate.Parameters.Add("@ADDRESS_LINE3", SqlDbType.Char);
cmUpdate.Parameters["@ADDRESS_LINE3"].Value = _address_line3;
cmUpdate.Parameters.Add("@CITY", SqlDbType.Char);
cmUpdate.Parameters["@CITY"].Value = _city;
cmUpdate.Parameters.Add("@STATE_ID", SqlDbType.Int);
cmUpdate.Parameters["@STATE_ID"].Value = _state_id;
cmUpdate.Parameters.Add("@POSTAL_CODE", SqlDbType.BigInt);
cmUpdate.Parameters["@POSTAL_CODE"].Value = _postal_code;
cmUpdate.Parameters.Add("@COUNTRY", SqlDbType.Char);
cmUpdate.Parameters["@COUNTRY"].Value = _country;
cmUpdate.Parameters.Add("@PREFIX_ID", SqlDbType.Int);
cmUpdate.Parameters["@PREFIX_ID"].Value = _prefix_id;
cmUpdate.Parameters.Add("@FIRST_NAME", SqlDbType.Char);
cmUpdate.Parameters["@FIRST_NAME"].Value = _first_name;
cmUpdate.Parameters.Add("@LAST_NAME", SqlDbType.Char);
cmUpdate.Parameters["@LAST_NAME"].Value = _last_name;
cmUpdate.Parameters.Add("@HOME_PHONE", SqlDbType.Char);
cmUpdate.Parameters["@HOME_PHONE"].Value = _home_phone;
cmUpdate.Parameters.Add("@WORK_PHONE", SqlDbType.Char);
cmUpdate.Parameters["@WORK_PHONE"].Value = _work_phone;
cmUpdate.Parameters.Add("@CELL_PHONE", SqlDbType.Char);
cmUpdate.Parameters["@CELL_PHONE"].Value = _cell_phone;
cmUpdate.Parameters.Add("@FAX", SqlDbType.Char);
cmUpdate.Parameters["@FAX"].Value = _fax;
cmUpdate.Parameters.Add("@EMAIL", SqlDbType.Char);
cmUpdate.Parameters["@EMAIL"].Value = _email;
cmUpdate.Parameters.Add("@DOB", SqlDbType.DateTime);
cmUpdate.Parameters["@DOB"].Value = _dob;
#endregion
//Same as above
try
{
cnUpdate.Open();
results = cmUpdate.ExecuteNonQuery();
}
catch(Exception e)
{
throw(e);
}
//Yup, you guessed it. Same thing.
return results;
}
#endregion
#region Delete Command
public int dbDelete()
{
//Generic Connection info
SqlConnection cnDelete = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
SqlCommand cmDelete = new SqlCommand("sp_IUD", cnDelete);
cmDelete.Connection = cnDelete;
cmDelete.CommandType = CommandType.StoredProcedure;
//The only parameter needed is the Person_ID
//The Proc takes care of deleting based on whatever PERSON_ID is given
cmDelete.Parameters.Add("@PERSON_ID", SqlDbType.Int);
cmDelete.Parameters["@ADDRESS_ID"].Value = _person_id;
cmDelete.Parameters.Add("@IUD", SqlDbType.Int);
cmDelete.Parameters["@IUD"].Value = 2;
//Error Handling
try
{
cnDelete.Open();
results = cmDelete.ExecuteNonQuery();
}
catch(Exception e)
{
throw(e);
}
//Gimmie some results ^_^
return results;
}
#endregion
#region Get Everything Command ^_^
public SqlDataReader dbGetAll()
{
//Generic Connection Info
SqlConnection cnGetAll = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
//SQL Statement to select everything from the Address and Person table.
//Used to fill simple info in a small datagrid
//Datagrid has a details button so that you can click again and get all the info if needed
SqlCommand cmGetAll = new SqlCommand("SELECT * FROM tbl_address INNER JOIN tbl_person ON tbl_address.address_id = tbl_person.address_id ", cnGetAll);
cmGetAll.Connection = cnGetAll;
cmGetAll.CommandType = CommandType.Text;
SqlDataReader dr;
//Error handling
try
{
dr = cmGetAll.ExecuteReader();
}
catch(Exception e)
{
throw e;
}
//Gimmie my reader!!
return dr;
}
#endregion
#region Cleanup Local Variables
//I provided this method just in case
//The values will stay unless otherwise told to leave, heh
//And if the programmer used the same instance of the object
//In the same method of the page, it could be disastrous. So,
//If the programmer wishes to do that, he doesn't have to set
//All the values to nothing again. This does it for him/her.
public void dbCleanup()
{
_address_line1 = "";
_address_line2 = "";
_address_line3 = "";
_city = "";
_state_id = 0;
_postal_code = 0;
_country = "";
_prefix_id = 0;
_first_name = "";
_last_name = "";
_home_phone = "";
_cell_phone = "";
_work_phone = "";
_fax = "";
_email = "";
_dob = System.DateTime.Now;
_address_id = 0;
_person_id = 0;
}
#endregion
}
}