[return : SqlFacet(MaxSize = -1)] // hint Sql that the return valus is nvarchar(max), do about the same for the input parameter
public static SqlString Clr_XmlPrettyPrint([SqlFacet(MaxSize = -1)] SqlXml Xml)
{
/// Returns formatted xml string (indent and newlines) from XML parameter
/// for display in eg textboxes.
/// </summary>
//load unformatted xml into a dom
XmlDocument xd = new XmlDocument();
//xd.LoadXml(inString);
xd.Load(Xml.CreateReader());
//will hold formatted xml
StringBuilder sb = new StringBuilder();
//pumps the formatted xml into the StringBuilder above
using (StringWriter sw = new StringWriter(sb))
{
XmlTextWriter xtw = new XmlTextWriter(sw);
//we want the output formatted
xtw.Formatting = Formatting.Indented;
//get the dom to dump its contents into the xtw
xd.WriteTo(xtw);
}
//return the formatted xml
return(sb.ToString());
}
public static SqlXml Transform([SqlFacet(MaxSize = -1)] SqlXml input, [SqlFacet(MaxSize = -1)] SqlXml stylesheet)
{
if (input.IsNull || stylesheet.IsNull)
{
return(SqlXml.Null);
}
Encoding enc = new UnicodeEncoding(!BitConverter.IsLittleEndian, false);
XslCompiledTransform tr = GetStylesheetTransformer(stylesheet);
using (var ird = input.CreateReader())
using (var ms = new MemoryStream())
using (var owr = XmlWriter.Create(ms, new XmlWriterSettings()
{
Indent = false, Encoding = enc
}))
{
tr.Transform(ird, owr);
ms.Position = 0;
using (var resultReader = XmlReader.Create(ms, new XmlReaderSettings()
{
IgnoreWhitespace = true
}))
return(new SqlXml(resultReader));
}
}
private static AhoCorasick BuildAhoCorasick(SqlXml xml, SqlString culture)
{
var xe = XElement.Load(xml.CreateReader());
var words = xe.Elements().Select(e => e.FirstAttribute.Value);
var c = culture.Value.Split(':');
var ignoreCase = c.Length > 1 && c[1] == "i";
CharComparer cc;
switch (c[0])
{
case "c":
cc = CharComparer.Create(CultureInfo.CurrentCulture, ignoreCase);
break;
case "n":
cc = CharComparer.Create(CultureInfo.InvariantCulture, ignoreCase);
break;
case "o":
case "":
cc = ignoreCase ? CharComparer.OrdinalIgnoreCase : CharComparer.Ordinal;
break;
default:
cc = CharComparer.Create(CultureInfo.GetCultureInfo(c[0]), ignoreCase);
break;
}
var ac = new AhoCorasick(cc, words);
return(ac);
}
public static SqlBoolean EvaluateAsBoolean([SqlFacet(MaxSize = -1)] SqlXml input, [SqlFacet(MaxSize = -1)] SqlString path, SqlString nsMap)
{
if (input.IsNull || path.IsNull)
{
return(SqlBoolean.Null);
}
using (var reader = input.CreateReader())
{
var navigator = new XPathDocument(reader).CreateNavigator();
var resolver = GetNamespaceResolver(navigator.NameTable, !nsMap.IsNull ? nsMap.Value : null);
var value = navigator.Evaluate(XPathExpression.Compile(path.Value, resolver));
if (value == null)
{
return(SqlBoolean.Null);
}
var iterator = value as XPathNodeIterator;
if (iterator == null)
{
return(new SqlBoolean((Boolean)Convert.ChangeType(value, typeof(Boolean))));
}
var result = ((XPathNodeIterator)value).Cast <XPathNavigator>().FirstOrDefault();
return(result == null ? SqlBoolean.Null : new SqlBoolean((Boolean)result.ValueAs(typeof(Boolean), resolver)));
}
}
public ObservableCollection <Technique> getAllTechnique()
{
ObservableCollection <Technique> list = new ObservableCollection <Technique>();
if (ConnectionManager.ValidConnection == true)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnectionManager.getConnection))
{
conn.Open();
SqlCommand cmd = new SqlCommand("spr_RetrieveAllTechnique_v001", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader data = cmd.ExecuteReader();
while (data.Read())
{
if (list == null)
{
list = new ObservableCollection <Technique>();
}
Technique technique = new Technique();
technique.ID = data.GetInt32(data.GetOrdinal("ID"));
technique.Name = data.GetString(data.GetOrdinal("name"));
SqlXml xmltech = data.GetSqlXml(data.GetOrdinal("technique"));
technique.xml = XDocument.Load(xmltech.CreateReader());
list.Add(technique);
}
data.Close();
}
}
catch { }
}
return(list);
}
public static void CLRDemo(SqlXml metadata)
{
// Deserialize the report
ReportSerializer serializer = new ReportSerializer();
XmlReader reader = metadata.CreateReader();
RDL.Report report = (RDL.Report)serializer.Deserialize(reader);
// Generate the table schema
DataTable table = GenerateSchema(report);
// Generate preview results
GeneratePreviewData(table, report);
if (!SqlContext.IsAvailable)
{
return;
}
SqlDataRecord record = TableRowToSqlDataRecord(table);
SqlContext.Pipe.SendResultsStart(record);
foreach (DataRow row in table.Rows)
{
record.SetValues(row.ItemArray);
SqlContext.Pipe.SendResultsRow(record);
}
SqlContext.Pipe.SendResultsEnd();
return;
}
public void updateAlgorithmList(String imageUID)
{
using (SqlConnection conn = new SqlConnection(ConnectionManager.getConnection))
{
conn.Open();
SqlCommand cmd2 = new SqlCommand("spr_RetreiveTechniqueUsed_v001", conn);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.Add("@imageUID", SqlDbType.VarChar).Value = imageUID;
SqlDataReader data2 = cmd2.ExecuteReader();
if (data2.HasRows)
{
SelectedImage.AlgorithmCollection = new ObservableCollection <Technique>();
}
else
{
SelectedImage.AlgorithmCollection = null;
}
while (data2.Read())
{
Technique algo = new Technique();
algo.ID = data2.GetInt32(data2.GetOrdinal("ID"));
algo.Name = data2.GetString(data2.GetOrdinal("Algorithm"));
SqlXml xmltech = data2.GetSqlXml(data2.GetOrdinal("XML"));
algo.xml = XDocument.Load(xmltech.CreateReader());
SelectedImage.AlgorithmCollection.Add(algo);
}
data2.Close();
}
}
/// <summary>
/// Write_s the SQL XML.
/// </summary>
/// <param name="w">The w.</param>
/// <param name="value">The value.</param>
private static void XmlWrite_SqlXml(XmlWriter w, string id, SqlXml value)
{
if (value == null)
{
throw new ArgumentNullException("value");
}
bool isNull = value.IsNull;
if (!isNull)
{
string valueAsText;
switch (id)
{
case "":
w.WriteNode(value.CreateReader(), false);
return;
case "#":
valueAsText = value.Value;
if (!string.IsNullOrEmpty(valueAsText))
{
w.WriteString(valueAsText);
}
break;
default:
valueAsText = value.Value;
if (!string.IsNullOrEmpty(valueAsText))
{
w.WriteAttributeString(id, valueAsText);
}
break;
}
}
}
public List <StatystykaDTO> ListaStatystyk()
{
Database db = DatabaseFactory.CreateDatabase();
List <StatystykaDTO> lista = new List <StatystykaDTO>();
using (IDataReader dr = db.ExecuteReader("Statystyki.listaStatystyk"))
{
while (dr.Read())
{
SqlDataReader sdr = dr as SqlDataReader;
if (sdr == null)
{
throw new Exception("Nie uda³o siê pobraæ metadanych statystyki");
}
SqlXml metadane = sdr.GetSqlXml(3);
string xslt = sdr.IsDBNull(4)?string.Empty:sdr.GetSqlXml(4).Value;
List <ParametrStatystyki> parametry = new List <ParametrStatystyki>();
XmlReader xr = metadane.CreateReader();
XPathDocument xpd = new XPathDocument(xr);
XPathNavigator xpn = xpd.CreateNavigator();
string procedura = xpn.SelectSingleNode("/statystyka/procedura").Value;
XPathNodeIterator xpni = xpn.Select("/statystyka/parametry/parametr");
while (xpni.MoveNext())
{
parametry.Add(new DTO.ParametrStatystyki(xpni.Current.GetAttribute("etykieta", ""), xpni.Current.GetAttribute("typ", ""), xpni.Current.GetAttribute("wymagany", "").ToLower() == "tak" ? true : false, xpni.Current.GetAttribute("domyslnie", ""), xpni.Current.GetAttribute("zrodloDanych", "")));
}
xr.Close();
lista.Add(new StatystykaDTO(dr.GetInt32(0), procedura, dr.GetString(1), dr.IsDBNull(2)?"":dr.GetString(2), parametry, xslt == null?null: xslt));
}
}
return(lista);
}
public static void XmlTest()
{
string tempTable = "xml_" + Guid.NewGuid().ToString().Replace('-', '_');
string initStr = "create table " + tempTable + " (xml_col XML)";
string insertNormStr = "INSERT " + tempTable + " VALUES('<doc>Hello World</doc>')";
string insertParamStr = "INSERT " + tempTable + " VALUES(@x)";
string queryStr = "select * from " + tempTable;
using (SqlConnection conn = new SqlConnection(DataTestUtility.TCPConnectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = initStr;
cmd.ExecuteNonQuery();
try
{
cmd.CommandText = insertNormStr;
cmd.ExecuteNonQuery();
SqlCommand cmd2 = new SqlCommand(insertParamStr, conn);
cmd2.Parameters.Add("@x", SqlDbType.Xml);
XmlReader xr = XmlReader.Create("data.xml");
cmd2.Parameters[0].Value = new SqlXml(xr);
cmd2.ExecuteNonQuery();
cmd.CommandText = queryStr;
using (SqlDataReader reader = cmd.ExecuteReader())
{
int currentValue = 0;
string[] expectedValues =
{
"<doc>Hello World</doc>",
"<NewDataSet><builtinCLRtypes><colsbyte>1</colsbyte><colbyte>2</colbyte><colint16>-20</colint16><coluint16>40</coluint16><colint32>-300</colint32><coluint32>300</coluint32><colint64>-4000</colint64><coluint64>4000</coluint64><coldecimal>50000.01</coldecimal><coldouble>600000.987</coldouble><colsingle>70000.9</colsingle><colstring>string variable</colstring><colboolean>true</colboolean><coltimespan>P10675199DT2H48M5.4775807S</coltimespan><coldatetime>9999-12-30T23:59:59.9999999-08:00</coldatetime><colGuid>00000001-0002-0003-0405-060708010101</colGuid><colbyteArray>AQIDBAUGBwgJCgsMDQ4PEA==</colbyteArray><colUri>http://www.abc.com/</colUri><colobjectsbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"byte\">1</colobjectsbyte><colobjectbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedByte\">2</colobjectbyte><colobjectint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"short\">-20</colobjectint16><colobjectuint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedShort\">40</colobjectuint16><colobjectint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"int\">-300</colobjectint32><colobjectuint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedInt\">300</colobjectuint32><colobjectint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"long\">-4000</colobjectint64><colobjectuint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedLong\">4000</colobjectuint64><colobjectdecimal xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"decimal\">50000.01</colobjectdecimal><colobjectdouble xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"double\">600000.987</colobjectdouble><colobjectsingle xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"float\">70000.9</colobjectsingle><colobjectstring xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"string\">string variable</colobjectstring><colobjectboolean xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"boolean\">true</colobjectboolean><colobjecttimespan xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"duration\">P10675199DT2H48M5.4775807S</colobjecttimespan><colobjectdatetime xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"dateTime\">9999-12-30T23:59:59.9999999-08:00</colobjectdatetime><colobjectguid xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\" msdata:InstanceType=\"System.Guid\">00000001-0002-0003-0405-060708010101</colobjectguid><colobjectbytearray xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"base64Binary\">AQIDBAUGBwgJCgsMDQ4PEA==</colobjectbytearray><colobjectUri xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"anyURI\">http://www.abc.com/</colobjectUri></builtinCLRtypes><builtinCLRtypes><colbyte>2</colbyte><colint16>-20</colint16><colint32>-300</colint32><coluint32>300</coluint32><coluint64>4000</coluint64><coldecimal>50000.01</coldecimal><coldouble>600000.987</coldouble><colsingle>70000.9</colsingle><colboolean>true</colboolean><colobjectsbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"byte\">11</colobjectsbyte><colobjectbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedByte\">22</colobjectbyte><colobjectint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"short\">-200</colobjectint16><colobjectuint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedShort\">400</colobjectuint16><colobjectint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"int\">-3000</colobjectint32><colobjectuint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedInt\">3000</colobjectuint32><colobjectint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"long\">-40000</colobjectint64><colobjectuint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedLong\">40000</colobjectuint64><colobjectdecimal xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"decimal\">500000.01</colobjectdecimal><colobjectdouble xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"double\">6000000.987</colobjectdouble><colobjectsingle xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"float\">700000.9</colobjectsingle><colobjectstring xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"string\">string variable 2</colobjectstring><colobjectboolean xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"boolean\">false</colobjectboolean><colobjecttimespan xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"duration\">-P10675199DT2H48M5.4775808S</colobjecttimespan><colobjectdatetime xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"dateTime\">0001-01-01T00:00:00.0000000-08:00</colobjectdatetime><colobjectguid xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\" msdata:InstanceType=\"System.Guid\">00000002-0001-0001-0807-060504030201</colobjectguid><colobjectbytearray xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"base64Binary\">EA8ODQwLCgkIBwYFBAMCAQ==</colobjectbytearray></builtinCLRtypes></NewDataSet>"
};
while (reader.Read())
{
Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");
SqlXml sx = reader.GetSqlXml(0);
xr = sx.CreateReader();
xr.Read();
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], xr.ReadOuterXml(), "FAILED: Did not receive expected data");
}
}
}
finally
{
cmd.CommandText = "drop table " + tempTable;
cmd.ExecuteNonQuery();
}
}
}
protected void buildInventoryDetails_Graphicsold(string listName, SqlXml sqlXMLValue, PlaceHolder placeHolderName,int listNumber)
{
Literal invtabLI = new Literal();
invtabLI.Text = "<li><a href='#UpdatePanel" + listNumber + "'><span>" + CommonFunctions.CvtNullSpaces(listName) + "</span></a></li> ";
placeHolderInvContainer.Controls.Add(invtabLI);
int i = 0;
int row = 0;
if (!sqlXMLValue.IsNull)
{
XmlReader xmlreader = sqlXMLValue.CreateReader();
LiteralControl defListStart = new LiteralControl ( "<dl>");
placeHolderName.Controls.Add(defListStart);
while (xmlreader.Read())
{
if (xmlreader.MoveToContent() == XmlNodeType.Element && xmlreader.Name == "item")
{
LiteralControl itemLitrealControl = new LiteralControl
("<dt>" + xmlreader.ReadString() + "</dt>");
placeHolderName.Controls.Add(itemLitrealControl);
i += 1;
row += 1;
}
LiteralControl externalstaLitrealControl = new LiteralControl("<dd>");
placeHolderName.Controls.Add(externalstaLitrealControl);
if (xmlreader.MoveToContent() == XmlNodeType.Element && xmlreader.Name == "desc")
{
i += 1;
LiteralControl itemLitrealControl = new LiteralControl
(xmlreader.ReadString() );
placeHolderName.Controls.Add(itemLitrealControl);
}
if (xmlreader.MoveToContent() == XmlNodeType.Element && xmlreader.Name == "cost")
{
i += 1;
string xmlcost = xmlreader.ReadString();
if (string.IsNullOrEmpty(xmlcost))
{
LiteralControl itemLitrealControl = new LiteralControl
(xmlcost);
placeHolderName.Controls.Add(itemLitrealControl);
}
}
LiteralControl externalEndLitrealControl = new LiteralControl("</dd>");
placeHolderName.Controls.Add(externalEndLitrealControl);
}
xmlreader.Close();
LiteralControl defListend = new LiteralControl("</dl>");
placeHolderName.Controls.Add(defListend);
}
}
private static XslCompiledTransform GetStylesheetTransformer(SqlXml stylesheet)
{
XslCompiledTransform transform = new XslCompiledTransform();
using (var stylesheetReader = stylesheet.CreateReader())
transform.Load(stylesheetReader);
return(transform);
}
static int Main(string[] args)
{
MemoryStream ms = new MemoryStream();
var sqlXml = new SqlXml(ms);
var xmlReader = sqlXml.CreateReader();
return(xmlReader != null ? 100 : -1);
}
public static int SqlSafeParmsEx( ref SqlCommand cmd, SqlXml values ){
int n = 0;
if (!values.IsNull){
XPathDocument xp = new XPathDocument(values.CreateReader());
XPathNavigator xnav = xp.CreateNavigator();
string strArg = null;
/*
foreach(XPathNodeIterator k in xnav.Select("//values/value")){
k.Current;
}
*/
n = (int)(double)xnav.Evaluate("count(/values/value)");
for( int r = 1; r <= n; r++ ){
strArg = string.Format("@arg{0}", r);
XPathNavigator xcurr = xnav.SelectSingleNode(string.Format("/values/value[{0}]", r));
string val;
if( xcurr.SelectSingleNode("*[1]") != null ){ //xml
XmlDocument xd = new XmlDocument();
XmlNode root = xd.CreateElement("root");
root.InnerXml = xcurr.InnerXml;
cmd.Parameters.Add(new SqlParameter(strArg,
new SqlXml(new XmlNodeReader(root.SelectSingleNode("/*")))));
}
else if( xcurr.Value == null || (val = xcurr.Value).Length == 0 ){
SqlParameter parm = new SqlParameter(strArg, SqlDbType.VarChar, 10);
parm.IsNullable = true;
parm.SqlValue = SqlString.Null;
cmd.Parameters.Add(parm);
}
else if(val.StartsWith("N'")){
cmd.Parameters.Add(new SqlParameter(strArg, SqlDbType.NVarChar, val.Length ));
cmd.Parameters[strArg].Value = val.TrimStart(new char[] { 'N' }).Trim(new char[] { '\'' });
}
else if(val.StartsWith("'")){
cmd.Parameters.Add(new SqlParameter(strArg, SqlDbType.VarChar, val.Length ));
cmd.Parameters[strArg].Value = val.Substring(1,val.Length-2);
}
else{
try{
cmd.Parameters.Add(new SqlParameter(strArg, SqlDecimal.Parse(val)));
if (cmd.CommandText.Contains(strArg + " OUTPUT"))
cmd.Parameters[strArg].Direction = ParameterDirection.Output;
}
catch(Exception){
cmd.Parameters.Add(new SqlParameter(strArg, SqlDbType.VarChar, 10));
cmd.Parameters[strArg].IsNullable = true;
cmd.Parameters[strArg].SqlValue = SqlString.Null;
}
}
}
}
return n;
}//end SqlSafeParmsEx
public void SqlXml_fromZeroLengthStream_CreateReaderTest()
{
MemoryStream stream = new MemoryStream();
SqlXml xmlSql = new SqlXml(stream);
XmlReader xrdr = xmlSql.CreateReader();
Assert.Equal(false, xrdr.Read());
}
public void CreateReader_XmlReader_Null()
{
SqlXml xmlSql = new SqlXml((XmlReader)null);
try {
xmlSql.CreateReader();
Assert.Fail("#1");
} catch (SqlNullValueException) {
}
}
// <Snippet1>
// Example assumes the following directives:
// using System.Data.SqlClient;
// using System.Xml;
// using System.Data.SqlTypes;
static void GetXmlData(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// The query includes two specific customers for simplicity's
// sake. A more realistic approach would use a parameter
// for the CustomerID criteria. The example selects two rows
// in order to demonstrate reading first from one row to
// another, then from one node to another within the xml column.
string commandText =
"SELECT Demographics from Sales.Store WHERE " +
"CustomerID = 3 OR CustomerID = 4";
SqlCommand commandSales = new SqlCommand(commandText, connection);
SqlDataReader salesReaderData = commandSales.ExecuteReader();
// Multiple rows are returned by the SELECT, so each row
// is read and an XmlReader (an xml data type) is set to the
// value of its first (and only) column.
int countRow = 1;
while (salesReaderData.Read())
// Must use GetSqlXml here to get a SqlXml type.
// GetValue returns a string instead of SqlXml.
{
SqlXml salesXML =
salesReaderData.GetSqlXml(0);
XmlReader salesReaderXml = salesXML.CreateReader();
Console.WriteLine("-----Row " + countRow + "-----");
// Move to the root.
salesReaderXml.MoveToContent();
// We know each node type is either Element or Text.
// All elements within the root are string values.
// For this simple example, no elements are empty.
while (salesReaderXml.Read())
{
if (salesReaderXml.NodeType == XmlNodeType.Element)
{
string elementLocalName =
salesReaderXml.LocalName;
salesReaderXml.Read();
Console.WriteLine(elementLocalName + ": " +
salesReaderXml.Value);
}
}
countRow = countRow + 1;
}
}
}
public void CreateReader_Stream_Unicode()
{
string xmlStr = "<Employee><FirstName>Varadhan</FirstName><LastName>Veerapuram</LastName></Employee>";
MemoryStream stream = new MemoryStream(Encoding.Unicode.GetBytes(xmlStr));
SqlXml xmlSql = new SqlXml(stream);
XmlReader xrdr = xmlSql.CreateReader();
xrdr.MoveToContent();
Assert.Equal(xmlStr, xrdr.ReadOuterXml());
}
public void SqlXml_fromXmlReader_CreateReaderTest()
{
string xmlStr = "<Employee><FirstName>Varadhan</FirstName><LastName>Veerapuram</LastName></Employee>";
XmlReader rdr = new XmlTextReader(new StringReader(xmlStr));
SqlXml xmlSql = new SqlXml(rdr);
XmlReader xrdr = xmlSql.CreateReader();
xrdr.MoveToContent();
Assert.Equal(xmlStr, xrdr.ReadOuterXml());
}
public static SqlXml ExecXSLT(SqlXml Source, SqlXml XSLT)
{
var XslCompiledTransform = new XslCompiledTransform();
XslCompiledTransform.Load(XSLT.CreateReader());
var buffer = new MemoryStream();
var stream = new StreamWriter(buffer);
XslCompiledTransform.Transform(Source.CreateReader(), XmlWriter.Create(stream));
stream.Close();
return(new SqlXml(buffer));
}
public void SqlXml_fromZeroLengthXmlReader_CreateReaderTest_withFragment()
{
XmlReaderSettings xs = new XmlReaderSettings();
xs.ConformanceLevel = ConformanceLevel.Fragment;
XmlReader rdr = XmlReader.Create(new StringReader(string.Empty), xs);
SqlXml xmlSql = new SqlXml(rdr);
XmlReader xrdr = xmlSql.CreateReader();
Assert.Equal(false, xrdr.Read());
}
public void CreateReader_Stream_Null()
{
SqlXml xmlSql = new SqlXml((Stream)null);
try
{
xmlSql.CreateReader();
Assert.False(true);
}
catch (SqlNullValueException)
{
}
}
public static T GetValue <T>(this SqlXml sqlXml)
{
T value;
// using System.Xml;
using (XmlReader xmlReader = sqlXml.CreateReader())
{
// using System.Xml.Serialization;
XmlSerializer xmlSerializer = new XmlSerializer(typeof(T));
value = (T)xmlSerializer.Deserialize(xmlReader);
}
return(value);
}
public static SqlXml ApplyXslTransform(SqlXml XmlData, string xslPath)
{
// Load XSL transformation
XslCompiledTransform xform = new XslCompiledTransform();
xform.Load(xslPath);
// Load XML data
XPathDocument xDoc = new XPathDocument(XmlData.CreateReader());
XPathNavigator nav = xDoc.CreateNavigator();
// Apply the transformation
// using makes sure that we flush the writer at the end
using (XmlWriter writer = nav.AppendChild())
{
xform.Transform(XmlData.CreateReader(), writer);
}
// Return the transformed value
SqlXml retSqlXml = new SqlXml(nav.ReadSubtree());
return(retSqlXml);
}
internal static Object DeserializeDatabaseXML(SqlXml sqlXml, XmlSerializer serializer)
{
if (sqlXml == null || sqlXml.IsNull)
{
return(null);
}
using (XmlReader xmlReader = sqlXml.CreateReader())
{
lock (serializer)
{
return(serializer.Deserialize(xmlReader));
}
}
}
/// <summary>
/// Deserialize the contents of a SqlXml object into an object of type T.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="xml"></param>
/// <returns></returns>
public static T DeserializeXmlData <T>(SqlXml xml)
{
T objData = default(T);
if ((null != xml) && (!xml.IsNull))
{
using (XmlReader xmlReader = xml.CreateReader())
{
DataContractSerializer serializer = new DataContractSerializer(typeof(T));
objData = (T)serializer.ReadObject(xmlReader);
}
}
return(objData);
}
public SqlInt64 WriteAllXml(SqlXml xml)
{
if (IsNull)
{
return(SqlInt64.Null);
}
using (var fs = _fi.Create())
using (var xwr = XmlWriter.Create(fs, new XmlWriterSettings()))
using (var xrd = xml.CreateReader())
{
xwr.WriteNode(xrd, true);
xwr.Flush();
return(fs.Length);
}
}
public static int SqlSafeExecuteQryContext( SqlString sqlcmd, SqlString sqlcontext, ref SqlXml values,
out SqlXml xout ){
int n = 0, i = 0;
SqlConnection conn = new SqlConnection(sqlcontext.IsNull ? "context connection=true": sqlcontext.ToString());
SqlCommand cmd = new SqlCommand(sqlcmd.ToString().Replace("exec ","exec @rr="), conn);
conn.Open();
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 0;
SqlSafeParmsEx(ref cmd, values);
try{
cmd.Parameters.Add( new SqlParameter("@rr",new SqlInt32(0)) );
cmd.Parameters["@rr"].Direction = ParameterDirection.InputOutput;
SqlDataReader sr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
n = (int)(SqlInt32)cmd.Parameters["@rr"].SqlValue;
if( n == 0 && sr.HasRows && sr.Read() ){
// n = sr.RecordsAffected;
xout = sr.GetSqlXml(0);
}
else
xout = values;
sr.Close();
foreach( SqlParameter parm in cmd.Parameters ){
i++;
if( parm.Direction == ParameterDirection.Output ){
XPathDocument xp = new XPathDocument(values.CreateReader());
XmlDocument xd = new XmlDocument();
XmlNode root = xd.CreateElement("root");
root.InnerXml = xp.CreateNavigator().InnerXml;
root.SelectSingleNode(string.Format("/values/value[{0}]", i)).InnerXml = parm.Value.ToString();
values = new SqlXml(new XmlNodeReader(root.SelectSingleNode("/*")));
}
}
}
catch (Exception ed){
throw new Exception(ed.Message + "\n" +
@sqlcmd.ToString() );
// xnav.SelectSingleNode(string.Format("/values/value[{0}]", 19)).Value);
}
// conn.Close();
return n;
}//end SqlSafeExecuteQry
/// <summary>
/// Retrieves xml with a maximum number of bytes to return
/// </summary>
/// <param name="iCol">Column ordinal</param>
/// <param name="maxCharsToReturn">Number of chars to return at maximum</param>
/// <returns>String</returns>
public string GetXmlWithMaxCapacity(int iCol, int maxCharsToReturn)
{
if (maxCharsToReturn <= 0)
{
throw new ArgumentOutOfRangeException(nameof(maxCharsToReturn), SR.QueryServiceDataReaderXmlCountInvalid);
}
// If we're not in SQL XML mode, just return the entire thing as a string
if (!supportSqlXml)
{
object o = GetValue(iCol);
return(o?.ToString());
}
// We have SQL XML support, so write it properly
SqlXml sm = GetSqlXml(iCol);
if (sm == null)
{
return(null);
}
// Setup the writer so that we don't close the memory stream and can process fragments
// of XML
XmlWriterSettings writerSettings = new XmlWriterSettings
{
CloseOutput = false, // don't close the memory stream
ConformanceLevel = ConformanceLevel.Fragment
};
using (StringWriterWithMaxCapacity sw = new StringWriterWithMaxCapacity(null, maxCharsToReturn))
using (XmlWriter ww = XmlWriter.Create(sw, writerSettings))
using (XmlReader reader = sm.CreateReader())
{
reader.Read();
while (!reader.EOF)
{
ww.WriteNode(reader, true);
}
ww.Flush();
return(sw.ToString());
}
}
public void TestGetSqlXmlReader(TestCaseResult result)
{
InsertRowText();
VirtuosoCommand cmd = connection.CreateCommand();
cmd.CommandText = "select data from xmlt";
VirtuosoDataReader rdr = cmd.ExecuteReader();
rdr.Read();
SqlXml x = rdr.GetSqlXml(0);
XmlDocument doc = new XmlDocument();
doc.Load(x.CreateReader());
FailIfXmlNotEqual(result, doc.OuterXml, TheXml);
}