public static int DoRegisterNewContact(ContactInfo contactDetails)
{
int returnVal = 0;
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "INSERT INTO contact(contactId,name,mobile,homePhone,officePhone,email,address,faxNumber,remark) "
+ "VALUES(@contactId,@name,@mobile,@homePhone,@officePhone,@email,@address,@faxNumber,@remark)";
msqlCommand.Parameters.AddWithValue("@contactId", contactDetails.id);
msqlCommand.Parameters.AddWithValue("@name", contactDetails.name);
msqlCommand.Parameters.AddWithValue("@mobile", contactDetails.mobileno);
msqlCommand.Parameters.AddWithValue("@homePhone", contactDetails.homeno);
msqlCommand.Parameters.AddWithValue("@officePhone", contactDetails.oficeno);
msqlCommand.Parameters.AddWithValue("@email", contactDetails.email);
msqlCommand.Parameters.AddWithValue("@address", contactDetails.address);
msqlCommand.Parameters.AddWithValue("@faxNumber", contactDetails.faxno);
msqlCommand.Parameters.AddWithValue("@remark", contactDetails.remark);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
private static List<ContactInfo> searchAllContactList(ContactInfo contactinfo)
{
List<ContactInfo> ContactList = new List<ContactInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
// msqlCommand.CommandText = "Select * From contact where id = @input name = @input or mobile = @input or homePhone = @input or officePhone = @input or email = @input address = @input or faxNumber = @input or officePhone = @input or remark = @input ; ";
msqlCommand.CommandText = "Select * From contact where name = @input; ";
msqlCommand.Parameters.AddWithValue("@input", contactinfo.name);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
ContactInfo Contact = new ContactInfo();
//Contact.id = msqlReader.GetString("id");
Contact.name = msqlReader.GetString("name");
//Contact.mobileno = msqlReader.GetString("mobile");
//Contact.homeno = msqlReader.GetString("homePhone");
//Contact.oficeno = msqlReader.GetString("officePhone");
//Contact.email = msqlReader.GetString("email");
//Contact.address = msqlReader.GetString("address");
//Contact.faxno = msqlReader.GetString("faxNumber");
//Contact.remark = msqlReader.GetString("remark");
ContactList.Add(Contact);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return ContactList;
}
private static List<ContactInfo> QueryAllContactList()
{
List<ContactInfo> ContactList = new List<ContactInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "Select * From contact ;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
ContactInfo Contact = new ContactInfo();
Contact.id = msqlReader.GetString("id");
Contact.name = msqlReader.GetString("name");
Contact.mobileno = msqlReader.GetString("mobile");
Contact.homeno = msqlReader.GetString("homePhone");
Contact.oficeno = msqlReader.GetString("officePhone");
Contact.email = msqlReader.GetString("email");
Contact.address = msqlReader.GetString("address");
Contact.faxno = msqlReader.GetString("faxNumber");
Contact.remark = msqlReader.GetString("remark");
ContactList.Add(Contact);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return ContactList;
}
public static List<ContactInfo> searchContactList(ContactInfo contactinfo)
{
return searchAllContactList(contactinfo);
}
private void saveContactBtn_Click(object sender, RoutedEventArgs e)
{
DNBSNData.ContactInfo newContact = new DNBSNData.ContactInfo();
newContact.id = GenerateId();
newContact.name = contactnameTB.Text;
newContact.mobileno = mobnoTB.Text;
newContact.homeno = homnoTB.Text;
newContact.oficeno = ofcNoTb.Text;
newContact.faxno = faxNoTb.Text;
newContact.address = addressTB.Text;
newContact.remark = remrkTB.Text;
newContact.email = emailTb.Text;
DNBSNDb.DbInteraction.DoRegisterNewContact(newContact);
contactnameTB.Text = mobnoTB.Text = homnoTB.Text = ofcNoTb.Text = faxNoTb.Text = addressTB.Text = remrkTB.Text = emailTb.Text = "";
fetchContactData();
contacXpndr.IsExpanded = true;
}
private void contactSearchBtn_Click(object sender, RoutedEventArgs e)
{
if (searchContactTB.Text == "")
fetchContactData();
else
{
ContactInfo contactinfo = new ContactInfo();
contactinfo.name = searchContactTB.Text;
List<ContactInfo> contacts = DbInteraction.searchContactList(contactinfo);
_contactCollection.Clear();
foreach (ContactInfo Contacts in contacts)
{
_contactCollection.Add(Contacts);
}
}
}