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 void TestRead(out Note nt)
{
nt = null;
}