////////////////////////////////////////////////////////////////////////////////////////////////////////////
// CD: XML methods - should always be called from Reservation C(R)UD methods in other models (User?)
////////////////////////////////////////////////////////////////////////////////////////////////////////////
private String BuildScriptDataXML(Person person, Slot slot, List<string> machines)
{
// create document
XmlDocument doc = new XmlDocument();
// create root <command> node
XmlNode commandNode = doc.CreateElement("command");
doc.AppendChild(commandNode);
// create <username> child node and add to <command> node
XmlNode usernameNode = doc.CreateElement("username");
XmlText usernameTextNode = doc.CreateTextNode(escXML(person.Username));
usernameNode.AppendChild(usernameTextNode);
commandNode.AppendChild(usernameNode);
// create <startTime> child node and add to <command> node
XmlNode startTimeNode = doc.CreateElement("startTime");
XmlAttribute minutesAttribute = doc.CreateAttribute("minutes");
minutesAttribute.Value = escXML(slot.Start.ToString("mm"));
XmlAttribute hoursAttribute = doc.CreateAttribute("hours");
hoursAttribute.Value = escXML(slot.Start.ToString("hh"));
startTimeNode.Attributes.Append(hoursAttribute);
startTimeNode.Attributes.Append(minutesAttribute);
commandNode.AppendChild(startTimeNode);
// create <startDate> child node and add to <command> node
XmlNode startDateNode = doc.CreateElement("startDate");
string startDate = String.Format("{0:d.M.yyyy}", slot.Start);
startDateNode.AppendChild(doc.CreateTextNode(startDate)); // skipping escXML() here
commandNode.AppendChild(startDateNode);
// create <machines> node and <machine> child nodes and add to <command> node
XmlNode machinesNode = doc.CreateElement("machines");
machines.ForEach(delegate(string machine)
{
XmlNode machineNode = doc.CreateElement("machine");
XmlNode nameNode = doc.CreateElement("name");
nameNode.AppendChild(doc.CreateTextNode(escXML(machine)));
machineNode.AppendChild(nameNode);
machinesNode.AppendChild(machineNode);
});
commandNode.AppendChild(machinesNode);
// return a XML string representation of the <command> node
StringWriter sw = new StringWriter();
XmlTextWriter xw = new XmlTextWriter(sw);
doc.WriteTo(xw);
return sw.ToString();
}
public static Slot GetSlot(int id)
{
Slot slot = null;
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
SqlCommand cmd = new SqlCommand("SELECT [start], [end], [id_slotRange] " +
"FROM Slot WHERE id_slot=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
SqlDataReader rdr = cmd.ExecuteReader();
bool hasFound = false;
if (rdr.Read())
{
DateTime start = rdr.GetDateTime(rdr.GetOrdinal("start"));
DateTime end = rdr.GetDateTime(rdr.GetOrdinal("end"));
int id_slotRange = rdr.GetInt32(rdr.GetOrdinal("id_slotRange"));
slot = new Slot(id, start, end, id_slotRange);
//range.Timestamp = rdr.GetInt32(rdr.GetOrdinal("timestamp"));
//byte[] buffer = new byte[100];
//rdr.GetBytes(rdr.GetOrdinal("timestamp"), 0, buffer, 0, 100);
//slot.setTimestamp(buffer);
hasFound = true;
}
rdr.Close();
if (!hasFound)
{
return null;
}
transaction.Commit();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
transaction.Rollback();
throw new GrException(ex, Messages.errProd);
}
finally
{
db.Close();
}
}
catch (Exception ex)
{
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
return slot;
}
// CD: this is really an Update
public void InsertCommandXML(Person person, Slot slot, List<string> machines, SqlConnection db, SqlTransaction transaction)
{
if (machines == null)
{
machines = new List<string>();
}
byte[] timestamp = this.getByteTimestamp();
SqlCommand cmd = new SqlCommand("SELECT * FROM SlotRange R " +
"WHERE R.[id_slotRange]=@id AND R.timestamp=@timestamp;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = this.id_slotRange;
cmd.Parameters.Add("@timestamp", SqlDbType.Binary).Value = timestamp;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
rdr.Close();
// before inserting, delete any previously existing reservations
cmd = new SqlCommand("UPDATE SlotRange " +
"SET scriptDataXML.modify('delete (/script/command[username=sql:variable(\"@username\")])') " +
"WHERE id_slotRange=@id_slotRange", db, transaction);
cmd.Parameters.Add("@username", SqlDbType.Char).Value = person.Username;
cmd.Parameters.Add("@id_slotRange", SqlDbType.Int).Value = this.id_slotRange;
cmd.ExecuteNonQuery();
string xml_string = BuildScriptDataXML(person, slot, machines);
cmd = new SqlCommand("UPDATE SlotRange " +
"SET scriptDataXML.modify('insert sql:variable(\"@xml_string\") as last into (/script)[1]') " +
"WHERE id_slotRange = @id_slotRange ", db, transaction);
cmd.Parameters.Add("@id_slotRange", SqlDbType.Int).Value = this.id_slotRange;
cmd.Parameters.Add("@xml_string", SqlDbType.Xml).Value = xml_string;
cmd.ExecuteNonQuery();
//transaction.Commit();
}
else
{
rdr.Close();
throw new GrException(Messages.recommencerEdit);
}
}