//////////////////////////////////////////////////////////////////////////////////////////////////////////// // 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); } }