public static void Main()
{
DataSet dsNorthwind = new DataSet();
//Create the connection string
String sConnect;
sConnect = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind";
//Create a connection object to connect to the northwind db.
SqlConnection nwconnect = new SqlConnection(sConnect);
//Create a command string to select all the customers in the WA region.
String sCommand = "Select * from Customers where Region='WA'";
//Create an adapter to load the DataSet.
SqlDataAdapter myDataAdapter = new SqlDataAdapter(sCommand, nwconnect);
//Fill the DataSet with the selected records.
myDataAdapter.Fill(dsNorthwind, "Customers");
//Load the document with the DataSet.
XmlDataDocument doc = new XmlDataDocument(dsNorthwind);
//Create an element representing the first customer record.
DataRow row = doc.DataSet.Tables[0].Rows[0];
XmlElement elem = doc.GetElementFromRow(row);
Console.WriteLine(elem.OuterXml);
}
public void EditingXmlTree()
{
XmlDataDocument doc = new XmlDataDocument();
doc.DataSet.ReadXmlSchema("Test/System.Xml/region.xsd");
doc.Load("Test/System.Xml/region.xml");
XmlElement Element = doc.GetElementFromRow(doc.DataSet.Tables [0].Rows [1]);
Element.FirstChild.InnerText = "64";
Assert.AreEqual("64", doc.DataSet.Tables [0].Rows [1] [0], "test#01");
DataSet Set = new DataSet();
Set.ReadXml("Test/System.Xml/region.xml");
doc = new XmlDataDocument(Set);
Element = doc.GetElementFromRow(doc.DataSet.Tables [0].Rows [1]);
Assert.IsNotNull(Element);
try {
Element.FirstChild.InnerText = "64";
Assert.Fail("test#02");
} catch (InvalidOperationException) {
}
Assert.AreEqual("2", doc.DataSet.Tables [0].Rows [1] [0], "test#05");
Set.EnforceConstraints = false;
Element.FirstChild.InnerText = "64";
Assert.AreEqual("64", doc.DataSet.Tables [0].Rows [1] [0], "test#06");
}
/// <summary>
/// Evaluates the specified query
/// </summary>
/// <typeparam name="T">Generic type</typeparam>
/// <param name="t">Object type</param>
/// <param name="dataSource">Datasource for evaluation</param>
/// <param name="condition">Query to evaluate</param>
/// <returns>Returns a XmlElement array which contains the selected data</returns>
public object Evaluate <T>(T t, object dataSource, string condition)
{
DataTableQueryEvaluator eval = null;
XmlElement[] nodes = null;
try
{
eval = new DataTableQueryEvaluator();
XmlDataDocument xdoc = (XmlDataDocument)dataSource;
DataRow[] rows = (DataRow[])eval.Evaluate <DataTable>(xdoc.DataSet.Tables[0], xdoc.DataSet.Tables[0], condition);
nodes = new XmlElement[eval.EvaluatorIndexes.Count];
for (int i = 0; i < eval.EvaluatorIndexes.Count; i++)
{
nodes[i] = xdoc.GetElementFromRow(xdoc.DataSet.Tables[0].Rows[eval.EvaluatorIndexes[i]]);
}
return((object)nodes);
}
catch
{
throw;
}
}
public static void XmlDataDocument_GetElementFromRow()
{
DataSet ds = Create();
XmlDataDocument doc = new XmlDataDocument(ds);
XmlElement element = doc.GetElementFromRow(ds.Tables[0].Rows[0]);
Assert.NotNull(element);
Assert.Equal("Test", element.Name);
}
public static void XmlDataDocument_GetRowFromElement()
{
DataSet ds = Create();
DataRow dr = ds.Tables[0].Rows[0];
XmlDataDocument doc = new XmlDataDocument(ds);
XmlElement xmlElement = doc.GetElementFromRow(dr);
Assert.Equal(dr, doc.GetRowFromElement(xmlElement));
}
public void GetElementFromRow()
{
XmlDataDocument doc = new XmlDataDocument();
doc.DataSet.ReadXmlSchema("Test/System.Xml/region.xsd");
doc.Load("Test/System.Xml/region.xml");
DataTable table = doc.DataSet.Tables ["Region"];
XmlElement element = doc.GetElementFromRow(table.Rows [2]);
Assert.AreEqual("Region", element.Name, "#D01");
Assert.AreEqual("3", element ["RegionID"].InnerText, "#D02");
try {
element = doc.GetElementFromRow(table.Rows [4]);
Assert.Fail("#D03");
} catch (IndexOutOfRangeException e) {
Assert.AreEqual(typeof(IndexOutOfRangeException), e.GetType(), "#D04");
Assert.AreEqual("There is no row at position 4.", e.Message, "#D05");
}
}
/// <summary>
/// Evaluates the minimum value.
/// </summary>
/// <param name="t">Type to evaluate</param>
/// <param name="dataSource">Data source</param>
/// <param name="fieldName">Field name foe which the evaluation is made</param>
/// <returns>XmlElement which contains the resulting field</returns>
public object EvaluateMin <T>(T t, object dataSource, string fieldName)
{
DataTableQueryEvaluator eval = null;
XmlElement element = null;
try
{
eval = new DataTableQueryEvaluator();
XmlDataDocument xdoc = (XmlDataDocument)dataSource;
DataRow row = (DataRow)eval.EvaluateMin <DataTable>(xdoc.DataSet.Tables[0], xdoc.DataSet.Tables[0], fieldName);
element = xdoc.GetElementFromRow(xdoc.DataSet.Tables[0].Rows[eval.EvaluatorIndexes[0]]);
return(element);
}
catch
{
throw;
}
}
public static void Main()
{
SqlConnection mySqlConnection =
new SqlConnection(
"server=localhost;database=Northwind;uid=sa;pwd=sa"
);
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT TOP 2 CustomerID, CompanyName, Country " +
"FROM Customers " +
"ORDER BY CustomerID";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
// step 1: create a DataSet object and fill it with the top 2 rows
// from the Customers table
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
mySqlDataAdapter.Fill(myDataSet, "Customers");
mySqlConnection.Close();
DataTable customersDT = myDataSet.Tables["Customers"];
// step 2: display the DataRow objects in customersDT using
// DisplayDataRows()
DisplayDataRows(customersDT);
// step 3: create an XmlDataDocument object, passing myDataSet
// to the constructor; this associates myDataSet with the
// XmlDataDocument
XmlDataDocument myXDD = new XmlDataDocument(myDataSet);
// step 4: display the XML document in myXDD
Console.WriteLine("\nXML document in myXDD:");
myXDD.Save(Console.Out);
// step 5: add a customer DataRow to customersDT with a CustomerID
// of J9COM
Console.WriteLine("\n\nAdding new DataRow to customersDT with CustomerID of J9COM");
DataRow myDataRow = customersDT.NewRow();
myDataRow["CustomerID"] = "J9COM";
myDataRow["CompanyName"] = "J9 Company";
myDataRow["Country"] = "UK";
customersDT.Rows.Add(myDataRow);
// step 6: retrieve the J9COM node using GetElementFromRow()
Console.WriteLine("\nRetrieving J9COM node using GetElementFromRow()");
XmlNode myXmlNode = myXDD.GetElementFromRow(myDataRow);
Console.WriteLine("CustomerID = " + myXmlNode.ChildNodes[0].InnerText);
Console.WriteLine("CompanyName = " + myXmlNode.ChildNodes[1].InnerText);
Console.WriteLine("Country = " + myXmlNode.ChildNodes[2].InnerText);
// step 7: set J9COM node's Country to USA, first setting
// EnforceConstraints to false
Console.WriteLine("\nSetting J9COM node's Country to USA");
myDataSet.EnforceConstraints = false;
myXmlNode.ChildNodes[2].InnerText = "USA";
// step 8: retrieve the ANATR XmlNode using SelectSingleNode()
Console.WriteLine("\nRetrieving ANATR node using SelectSingleNode()");
myXmlNode =
myXDD.SelectSingleNode(
"/NewDataSet/Customers[CustomerID=\"ANATR\"]"
);
// step 9: retrieve the ANATR DataRow using GetRowFromElement()
Console.WriteLine("\nRetrieving ANATR DataRow using GetRowFromElement()");
myDataRow =
myXDD.GetRowFromElement((XmlElement)myXmlNode);
foreach (DataColumn myDataColumn in customersDT.Columns)
{
Console.WriteLine(myDataColumn + " = " +
myDataRow[myDataColumn]);
}
// step 10: remove the ANATR node using RemoveAll()
Console.WriteLine("\nRemoving ANATR node");
myXmlNode.RemoveAll();
// step 11: display the XML document in myXDD using Save()
Console.WriteLine("\nXML document in myXDD:");
myXDD.Save(Console.Out);
// step 12: display the DataRow objects in customersDT using
// DisplayDataRows()
DisplayDataRows(customersDT);
}
