async private void DoIt(int ID)
{
cust = General.database.FindCustomerByID(ID);
this.DataContext = cust;
if (cust.isOpen)
{
await MainWindow.mainWindow.ShowMessage("This customer is open by another user. It will be opened in Read-Only mode", "Already Open", MahApps.Metro.Controls.Dialogs.MessageDialogStyle.Affirmative);
btnSave.IsEnabled = false;
btnAddNote.IsEnabled = false;
}
LoadNotes();
General.database.LockCustomer(ID);
}
public bool UpdateCustomer(Customer cust)
{
using (SQLiteCommand command = m_dbConnection.CreateCommand())
{
try
{
return true;
}
catch { return false; }
}
}
public bool AddNote(Customer cust, Note nt)
{
using (SQLiteCommand command = m_dbConnection.CreateCommand())
{
try
{
if (cust.ID == 0)
return false;
sql = "INSERT INTO Notes (Data) VALUES(@data)";
var xml = "";
command.CommandText = @sql;
command.CommandType = CommandType.Text;
XmlSerializer xsSubmit = new XmlSerializer(typeof(Note));
using (StringWriter sww = new StringWriter())
using (XmlWriter writer = XmlWriter.Create(sww))
{
xsSubmit.Serialize(writer, nt);
xml = sww.ToString(); // Your XML
}
command.Parameters.AddWithValue("@data", xml);
int i = command.ExecuteNonQuery();
command.Parameters.Clear();
sql = "SELECT ID FROM Notes WHERE Data = @data";
command.CommandText = @sql;
command.Parameters.AddWithValue("@data", xml);
var da = new SQLiteDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
string ntindex = "";
if (string.IsNullOrEmpty(cust.Notes))
ntindex = ds.Tables[0].Rows[0].ItemArray[0].ToString();
else
ntindex = cust.Notes + ";" + ds.Tables[0].Rows[0].ItemArray[0].ToString();
if (i == 0)
return false;
sql = "UPDATE Customers SET Notes = @notes WHERE ID = @id;";
command.Parameters.Clear();
command.CommandText = @sql;
command.Parameters.AddWithValue("@notes", ntindex);
command.Parameters.AddWithValue("@id", cust.ID);
command.ExecuteNonQuery();
return true;
}
catch { return false; }
}
}
public List<Note> OpenNotes(Customer cust)
{
if (string.IsNullOrEmpty(cust.Notes))
return null;
List<Note> tmpnt = new List<Note>();
string[] nts = cust.Notes.Split(';');
DataSet ds = new DataSet();
using (SQLiteCommand command = m_dbConnection.CreateCommand())
{
try
{
sql = "SELECT Data FROM Notes WHERE ID = @id;";
command.CommandText = @sql;
command.CommandType = CommandType.Text;
for (int i = 0; i < nts.Length; i++)
{
ds.Clear();
command.Parameters.Clear();
command.Parameters.AddWithValue("@id", nts[i]);
var da = new SQLiteDataAdapter(command);
da.Fill(ds);
XmlSerializer xsopen = new XmlSerializer(typeof(Note));
var sread = new StringReader(ds.Tables[0].Rows[0].ItemArray[0].ToString());
var reader = XmlReader.Create(sread, new XmlReaderSettings() { CheckCharacters = false });
var tn = (Note)xsopen.Deserialize(reader);
tmpnt.Add((Note)tn);
}
return tmpnt;
}
catch { throw; return null; }
}
}
/***************************************/
public Customer FindCustomerByID(int id)
{
using (SQLiteCommand command = m_dbConnection.CreateCommand())
{
try
{
DataSet ds = new DataSet();
Customer tmpc = new Customer();
sql = "SELECT * FROM Customers WHERE ID = @id";
command.CommandText = @sql;
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@id", id);
var da = new SQLiteDataAdapter(command);
da.Fill(ds);
tmpc.FirstName = ds.Tables[0].Rows[0].ItemArray[0].ToString();
tmpc.LastName = ds.Tables[0].Rows[0].ItemArray[1].ToString();
tmpc.Company = ds.Tables[0].Rows[0].ItemArray[2].ToString();
tmpc.PhoneNumber = ds.Tables[0].Rows[0].ItemArray[3].ToString();
tmpc.Email = ds.Tables[0].Rows[0].ItemArray[4].ToString();
tmpc.Notes = ds.Tables[0].Rows[0].ItemArray[5].ToString();
tmpc.isOpen = Convert.ToBoolean(ds.Tables[0].Rows[0].ItemArray[7]);
tmpc.ID = Convert.ToInt32(ds.Tables[0].Rows[0].ItemArray[8].ToString());
return tmpc;
}
catch { return null; }
}
}
