/// <summary>
/// Gets the value of the specified xml column as a <see cref="XDocument"/>.
/// </summary>
/// <param name="sqlDr">The data reader.</param>
/// <param name="name">The column name.</param>
public static XDocument GetSqlXDocument(this SqlDataReader sqlDr, string name)
{
if (sqlDr is null)
{
throw new ArgumentNullException(nameof(sqlDr));
}
if (string.IsNullOrWhiteSpace(name))
{
throw new ArgumentNullException(nameof(name));
}
var ord = sqlDr.GetOrdinal(name); // Throws IndexOutOfRangeException
System.Data.SqlTypes.SqlXml val = sqlDr.GetSqlXml(ord);
if (val.IsNull)
{
return(null);
}
using (System.Xml.XmlReader xmr = val.CreateReader())
{
var xml = XDocument.Load(xmr);
return(xml);
}
}
/// <summary>
/// Implements the reservation of a flight ticket.
/// </summary>
/// <param name="Message"></param>
/// <param name="ConversationHandle"></param>
/// <param name="Connection"></param>
public void Execute(System.Data.SqlTypes.SqlXml Message, Guid ConversationHandle, SqlConnection Connection)
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(Message.Value);
try
{
// Construct the SqlCommand
SqlCommand cmd = new SqlCommand("INSERT INTO FlightTickets (ID, [From], [To], FlightNumber, Airline, Departure, Arrival) VALUES ("
+ "@ID, @From, @To, @FlightNumber, @Airline, @Departure, @Arrival)", Connection);
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.UniqueIdentifier));
cmd.Parameters.Add(new SqlParameter("@From", SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlParameter("@To", SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlParameter("@FlightNumber", SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlParameter("@Airline", SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlParameter("@Departure", SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlParameter("@Arrival", SqlDbType.NVarChar));
cmd.Parameters["@ID"].Value = Guid.NewGuid();
cmd.Parameters["@From"].Value = doc.GetElementsByTagName("From").Item(0).InnerText;
cmd.Parameters["@To"].Value = doc.GetElementsByTagName("To").Item(0).InnerText;
cmd.Parameters["@FlightNumber"].Value = doc.GetElementsByTagName("FlightNumber").Item(0).InnerText;
cmd.Parameters["@Airline"].Value = doc.GetElementsByTagName("Airline").Item(0).InnerText;
cmd.Parameters["@Departure"].Value = doc.GetElementsByTagName("Departure").Item(0).InnerText;
cmd.Parameters["@Arrival"].Value = doc.GetElementsByTagName("Arrival").Item(0).InnerText;
// Execute the query
cmd.ExecuteNonQuery();
}
finally
{
// End the ongoing conversation between the two services
new ServiceBroker(Connection).EndDialog(ConversationHandle);
}
}
protected override IEnumerable <object[]> GetDefaultResult(Query key)
{
IEnumerable <object[]> returnValue = base.GetDefaultResult(key);
foreach (object[] row in returnValue)
{
for (int i = 0; i < row.Length; i++)
{
if (row[i] is XElement)
{
row[i] = new System.Data.SqlTypes.SqlXml(((XElement)row[i]).CreateReader());
}
}
}
return(returnValue);
}
private void GrdClipboardItems_KeyDown(object sender, KeyEventArgs e)
{
if (e.Key == Key.V &&
(Keyboard.Modifiers & ModifierKeys.Control) == ModifierKeys.Control)
{
// 2-dim array containing clipboard data
string[][] clipboardData = ClipBoardItemsToArray();
DataTable dt = StringArrayToDataTable(clipboardData);
dt.TableName = "Row";
grdClipboardItems.ItemsSource = dt.DefaultView;
MemoryStream txtmem = new MemoryStream();
dt.WriteXml(txtmem);
rtfScript.AppendText(Encoding.UTF8.GetString(txtmem.ToArray()));
System.Data.SqlTypes.SqlXml sqlXml = new System.Data.SqlTypes.SqlXml(txtmem);
}
}
public static bool ExecuteXml(string sqlSpName, SqlParameter[] dbParams, System.Xml.XmlDocument dXml)
{
SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings.Get("connectionString"));
SqlCommand cmd = new SqlCommand(sqlSpName, cn);
cmd.CommandTimeout = Convert.ToInt16(ConfigurationManager.AppSettings.Get("connectionCommandTimeout"));
cmd.CommandType = CommandType.StoredProcedure;
if (dbParams != null)
{
foreach (SqlParameter dbParam in dbParams)
{
cmd.Parameters.Add(dbParam);
}
}
cn.Open();
bool bReturn;
try
{
//dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (dr.Read())
{
System.Data.SqlTypes.SqlXml oXml = dr.GetSqlXml(dr.GetOrdinal("Xml"));
dXml.LoadXml(oXml.Value);
bReturn = true;
}
else
{
bReturn = false;
}
}
}
catch (Exception)
{
throw;
}
return(bReturn);
}
public static bool ExecuteXml(string sqlSpName, SqlParameter[] dbParams, System.Xml.XmlDocument dXml)
{
SqlConnection cn = new SqlConnection("Data Source=(local);Initial Catalog=ServicioImpresion;Integrated Security=True");
SqlCommand cmd = new SqlCommand(sqlSpName, cn);
cmd.CommandTimeout = Convert.ToInt16("10000");
cmd.CommandType = CommandType.StoredProcedure;
if (dbParams != null)
{
foreach (SqlParameter dbParam in dbParams)
{
cmd.Parameters.Add(dbParam);
}
}
cn.Open();
bool bReturn;
try
{
//dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (dr.Read())
{
System.Data.SqlTypes.SqlXml oXml = dr.GetSqlXml(dr.GetOrdinal("Xml"));
dXml.LoadXml(oXml.Value);
bReturn = true;
}
else
{
bReturn = false;
}
}
}
catch (Exception)
{
throw;
}
return(bReturn);
}
/// <summary>
/// SAVING CHANGES OF POLICY BODY TO MS SQL SERVER (main procedure for saving changes in DB for XML policy)
/// </summary>
/// <param name="doc">XmlDOCUMENT To Be Saved</param>
/// <returns></returns>
public static bool SavingXmlPolicyChanging(XmlDocument changedDoc, Guid policyID)
{
bool result = false;
if (ConnectionState.Closed == VirtuDB.Connection.State)
{
VirtuDB.Connection.Open();
}
try
{
using (SqlCommand Command = Connection.CreateCommand())
{
System.Data.SqlTypes.SqlXml sx;
using (XmlNodeReader xnr = new XmlNodeReader(changedDoc))
{
sx = new System.Data.SqlTypes.SqlXml(xnr);
}
Command.CommandText = @"UPDATE inspolicy SET policy=@xml WHERE ID=@id";
Command.Parameters.Add(new SqlParameter(@"xml", SqlDbType.Xml)
{
Value = sx
});
Command.Parameters.AddWithValue("id", policyID);
var nRows = Command.ExecuteNonQuery();
result = nRows > 0;
}
}
catch (Exception)
{
}
finally
{
if (ConnectionState.Closed != VirtuDB.Connection.State)
{
VirtuDB.Connection.Close();
}
}
return(result);
}
/// <summary>
/// Gets the value of the specified xml column as a <see cref="System.String"/>.
/// </summary>
/// <param name="sqlDr">The data reader.</param>
/// <param name="name">The column name.</param>
public static string GetSqlXml(this SqlDataReader sqlDr, string name)
{
if (sqlDr is null)
{
throw new ArgumentNullException(nameof(sqlDr));
}
if (string.IsNullOrWhiteSpace(name))
{
throw new ArgumentNullException(nameof(name));
}
var ord = sqlDr.GetOrdinal(name); // Throws IndexOutOfRangeException
System.Data.SqlTypes.SqlXml val = sqlDr.GetSqlXml(ord);
if (val.IsNull)
{
return(null);
}
var xml = val.Value;
return(xml);
}
private object[] GetMappedValues() { // mode 0
if (null != _xmlMap) {
for(int i = 0; i < _xmlMap.Length; ++i) {
if (0 != _xmlMap[i]) {
// get the string/SqlString xml value
string xml = _readerDataValues[i] as string;
if ((null == xml) && (_readerDataValues[i] is System.Data.SqlTypes.SqlString)) {
System.Data.SqlTypes.SqlString x = (System.Data.SqlTypes.SqlString)_readerDataValues[i];
if (!x.IsNull) {
xml = x.Value;
}
else {
switch(_xmlMap[i]) {
case SqlXml:
// map strongly typed SqlString.Null to SqlXml.Null
_readerDataValues[i] = System.Data.SqlTypes.SqlXml.Null;
break;
default:
_readerDataValues[i] = DBNull.Value;
break;
}
}
}
if (null != xml) {
switch(_xmlMap[i]) {
case SqlXml: // turn string into a SqlXml value for DataColumn
System.Xml.XmlReaderSettings settings = new System.Xml.XmlReaderSettings();
settings.ConformanceLevel = System.Xml.ConformanceLevel.Fragment;
System.Xml.XmlReader reader = System.Xml.XmlReader.Create(new System.IO.StringReader(xml), settings, (string)null);
_readerDataValues[i] = new System.Data.SqlTypes.SqlXml(reader);
break;
case XmlDocument: // turn string into XmlDocument value for DataColumn
System.Xml.XmlDocument document = new System.Xml.XmlDocument();
document.LoadXml(xml);
_readerDataValues[i] = document;
break;
}
// default: let value fallthrough to DataSet which may fail with ArgumentException
}
}
}
}
switch(_mappedMode) {
default:
case MapExactMatch:
Debug.Assert(0 == _mappedMode, "incorrect mappedMode");
Debug.Assert((null == _chapterMap) && (null == _indexMap) && (null == _mappedDataValues), "incorrect MappedValues");
return _readerDataValues; // from reader to dataset
case MapDifferentSize:
Debug.Assert((null == _chapterMap) && (null == _indexMap) && (null != _mappedDataValues), "incorrect MappedValues");
MappedValues();
break;
case MapReorderedValues:
Debug.Assert((null == _chapterMap) && (null != _indexMap) && (null != _mappedDataValues), "incorrect MappedValues");
MappedIndex();
break;
case MapChapters:
Debug.Assert((null != _chapterMap) && (null == _indexMap) && (null != _mappedDataValues), "incorrect MappedValues");
MappedChapter();
break;
case MapChaptersReordered:
Debug.Assert((null != _chapterMap) && (null != _indexMap) && (null != _mappedDataValues), "incorrect MappedValues");
MappedChapterIndex();
break;
}
return _mappedDataValues;
}
public void Execute(System.Data.SqlTypes.SqlXml Message, Guid ConversationHandle)
{
SqlContext.Pipe.Send("MyCustomTask.Execute was executed. The supplied XML message had the following content:");
SqlContext.Pipe.Send(Message.Value);
}
/// <summary>
/// End the conversation identified by the given conversation handle.
/// </summary>
/// <param name="Message"></param>
/// <param name="ConversationHandle"></param>
public void Execute(System.Data.SqlTypes.SqlXml Message, Guid ConversationHandle)
{
new ServiceBroker("context connection=true;").EndDialog(ConversationHandle);
}
/// <summary>
/// Serialize object to a Byte[] array for use in your code
/// </summary>
/// <param name="frozen"></param>
/// <returns></returns>
public static T FromSqlXml(System.Data.SqlTypes.SqlXml frozen)
{
throw new NotImplementedException("SqlXml type not implemented");
}
/// <summary>
/// End the conversation identified by the given conversation handle.
/// </summary>
/// <param name="Message"></param>
/// <param name="ConversationHandle"></param>
public void Execute(System.Data.SqlTypes.SqlXml Message, Guid ConversationHandle, SqlConnection Connection)
{
new ServiceBroker(Connection).EndDialog(ConversationHandle);
}
