/**
* Method initContact
* Takes a Contact ID, fetches it from the DB
* and poplates the object with the data from the DB
*/
public bool initContact(int contactId)
{
SqlServer sqlsvr = new SqlServer();
DataSet ds;
string sql;
try
{
sql = "SELECT * FROM Contact WHERE id = " + contactId;
ds = sqlsvr.runSqlReturnDataSet(sql);
if (ds == null)
{
errorMessage = "Sql Server Error in Contact.initContact:" + sqlsvr.errorMessage;
return false;
}
if (ds.Tables[0].Rows.Count == 0)
{
errorMessage = "No record in Contact.initContact";
return false;
}
DataRow row = ds.Tables[0].Rows[0];
id = contactId; ;
customerId = int.Parse(row["CustomerId"].ToString());
createDate = DateTime.Parse(row["CreateDate"].ToString());
note = row["Note"].ToString();
return true;
}
catch (Exception ex)
{
errorMessage = "Exception in Contact.initContact:" + ex.Message + ex.StackTrace;
return false;
}
}
/**
* method initCustomer
* fetch from the database the Customer
* with the supplied id and populate the
* objects properties
*/
public bool initCustomer(int customerId)
{
SqlServer sqlsvr = new SqlServer();
DataSet ds;
string sql;
try
{
sql = "SELECT * FROM Customer WHERE id = " + customerId;
ds = sqlsvr.runSqlReturnDataSet(sql);
if (ds == null)
{
errorMessage = "Sql Server Error in Customer.initCustomer:" + sqlsvr.errorMessage;
return false;
}
if (ds.Tables[0].Rows.Count == 0)
{
errorMessage = "No record in Customer.initCustomer";
return false;
}
DataRow row = ds.Tables[0].Rows[0];
id = customerId;
firstName = row["FirstName"].ToString();
lastName = row["LastName"].ToString();
phoneNumber = row["PhoneNumber"].ToString();
email = row["Email"].ToString();
// fetch all Contacts for this Customer
// sorted by newest first
ContactCollection cc = new ContactCollection();
if (! cc.getAllContactsForCustomer(id))
{
errorMessage = "Error getting Contacts in in Customer.initCustomer:" + cc.errorMessage;
return false;
}
contacts = cc;
lastContact = cc[0].createDate; //Last Contact
return true;
}
catch (Exception ex)
{
errorMessage = "Exception in Customer.initCustomer:" + ex.Message + ex.StackTrace;
return false;
}
}
/**
* method insertContact
* inserts a new Contact into the DB and
* returns it's (newly created) id
* as well as populates its fields
* Returns 0 on error
*/
public int insertContact(int pCustomerId, DateTime pCreateDate, string pNote)
{
SqlServer sqlsvr = new SqlServer();
string sql;
try
{
SqlCommand comm = new SqlCommand();
sql = "INSERT INTO Contact ";
sql += "(CustomerId, CreateDate, Note) ";
sql += "VALUES ";
sql += "(@CustomerId, @CreateDate, @Note)";
sql += ";SELECT @@IDENTITY AS new_id FROM Contact";
comm.CommandText = sql;
comm.Parameters.Add(new SqlParameter("@CustomerId", SqlDbType.Int));
comm.Parameters["@CustomerId"].Value = pCustomerId;
comm.Parameters.Add(new SqlParameter("@CreateDate", SqlDbType.DateTime));
comm.Parameters["@CreateDate"].Value = pCreateDate;
comm.Parameters.Add(new SqlParameter("@Note", SqlDbType.VarChar,240));
comm.Parameters["@Note"].Value = pNote;
DataSet ds = sqlsvr.runCommandReturnDataSet(comm);
if (ds == null)
{
errorMessage = "Sql Server error in Contact.insertContact:" + sqlsvr.errorMessage;
return 0;
}
id = int.Parse(ds.Tables[0].Rows[0]["new_id"].ToString());
customerId = pCustomerId;
createDate = pCreateDate;
note = pNote;
return id;
}
catch (Exception ex)
{
errorMessage = "Exception in Contact.insertContact:" + ex.Message + ex.StackTrace;
return 0;
}
}
/**
* method insertCustomer
* insert a new Customer into the database
* and return the (newly created) id of that Customer
* as well as populate its properties
* returns 0 on error
*
* NOTE Every newly created Customer get a new Contact
* record with the DateTime of now and a note of "Created"
*/
public int insertCustomer(string pFirstName, string pLastName, string pPhoneNumber, string pEmail)
{
SqlServer sqlsvr = new SqlServer();
string sql;
try
{
SqlCommand comm = new SqlCommand();
sql = "INSERT INTO Customer ";
sql += "(FirstName, LastName, PhoneNumber, Email) ";
sql += "VALUES ";
sql += "(@FirstName, @LastName, @PhoneNumber, @Email)";
sql += ";SELECT @@IDENTITY AS new_id FROM Customer";
comm.CommandText = sql;
comm.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar,70));
comm.Parameters["@FirstName"].Value = pFirstName;
comm.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 70));
comm.Parameters["@LastName"].Value = pLastName;
comm.Parameters.Add(new SqlParameter("@PhoneNumber", SqlDbType.VarChar, 50));
comm.Parameters["@PhoneNumber"].Value = pPhoneNumber;
comm.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar, 50));
comm.Parameters["@Email"].Value = pEmail;
DataSet ds = sqlsvr.runCommandReturnDataSet(comm);
if (ds == null)
{
errorMessage = "Sql Server error in Customer.insertCustomer:" + sqlsvr.errorMessage;
return 0;
}
id = int.Parse(ds.Tables[0].Rows[0]["new_id"].ToString());
firstName = pFirstName;
lastName = pLastName;
phoneNumber = pPhoneNumber;
email = pEmail;
DateTime now = DateTime.Now;
Contact co = new Contact();
int res = co.insertContact(id, now, "Created");
if (res == 0)
{
errorMessage = "Customer.insertCustomer error creating initial Contact:" + co.errorMessage;
return 0;
}
lastContact = now;
return id;
}
catch (Exception ex)
{
errorMessage = "Exception in Customer.insertCustomer:" + ex.Message + ex.StackTrace;
return 0;
}
}
