Csharp/C Sharp/Database ADO.net/XML Database
Содержание
causes the the child rows to be nested within the parent rows in the output XML
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; class NestedXml {
public static void Main() {
SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText =
"SELECT TOP 2 CustomerID, CompanyName " +
"FROM Customers " +
"ORDER BY CustomerID;" +
"SELECT OrderID, CustomerID, ShipCountry " +
"FROM Orders " +
"WHERE CustomerID IN (" +
" SELECT TOP 2 CustomerID " +
" FROM Customers " +
" ORDER BY CustomerID " +
")";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
int numberOfRows = mySqlDataAdapter.Fill(myDataSet);
Console.WriteLine("numberOfRows = " + numberOfRows);
mySqlConnection.Close();
DataTable customersDT = myDataSet.Tables["Table"];
DataTable ordersDT = myDataSet.Tables["Table1"];
DataRelation customersOrdersDataRel =
new DataRelation(
"CustomersOrders",
customersDT.Columns["CustomerID"],
ordersDT.Columns["CustomerID"]
);
myDataSet.Relations.Add(
customersOrdersDataRel
);
myDataSet.WriteXml("nonNestedXmlFile.xml");
myDataSet.Relations["CustomersOrders"].Nested = true;
myDataSet.WriteXml("nestedXmlFile.xml");
}
}
</source>
Fill data in DateSet to XmlDocument
<source lang="csharp"> using System; using System.Data; using System.Data.SqlClient; using System.Xml; class UsingXmlDocument {
public static void Main() {
SqlConnection mySqlConnection =
new SqlConnection(
"server=localhost;database=Northwind;uid=sa;pwd=sa"
);
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText =
"SELECT TOP 2 CustomerID, CompanyName, Country " +
"FROM Customers " +
"ORDER BY CustomerID";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
mySqlDataAdapter.Fill(myDataSet, "Customers");
mySqlConnection.Close();
XmlDocument myXmlDocument = new XmlDocument();
myXmlDocument.LoadXml(myDataSet.GetXml());
Console.WriteLine("Contents of myXmlDocument:");
myXmlDocument.Save(Console.Out);
foreach (XmlNode myXmlNode in myXmlDocument.SelectNodes("/NewDataSet/Customers")) {
Console.WriteLine("CustomerID = " + myXmlNode.ChildNodes[0].InnerText);
Console.WriteLine("CompanyName = " + myXmlNode.ChildNodes[1].InnerText);
Console.WriteLine("Country = " + myXmlNode.ChildNodes[2].InnerText);
}
XmlNode myXmlNode2 = myXmlDocument.SelectSingleNode("/NewDataSet/Customers[CustomerID=\" ANATR\"]");
Console.WriteLine("CustomerID = " + myXmlNode2.ChildNodes[0].InnerText);
Console.WriteLine("CompanyName = " + myXmlNode2.ChildNodes[1].InnerText);
Console.WriteLine("Country = " + myXmlNode2.ChildNodes[2].InnerText);
}
}
</source>
illustrates how to write and read XML files
<source lang="csharp"> /* Mastering Visual C# .NET by Jason Price, Mike Gunderloy Publisher: Sybex; ISBN: 0782129110
- /
/*
Example23_8.cs illustrates how to write and read XML files
- /
using System; using System.Data; using System.Data.SqlClient; public class Example23_8 {
public static void Main()
{
// formulate a string containing the details of the
// database connection
string connectionString =
"server=localhost;database=Northwind;uid=sa;pwd=sa";
// create a SqlConnection object to connect to the
// database, passing the connection string to the constructor
SqlConnection mySqlConnection =
new SqlConnection(connectionString);
// formulate a SELECT statement to retrieve the
// CustomerID, CompanyName, ContactName, and Address
// columns for the first two rows from the Customers table
string selectString =
"SELECT CustomerID, CompanyName, ContactName, Address " +
"FROM Customers " +
"WHERE CustomerID IN ("ALFKI", "ANATR")";
// create a SqlCommand object to hold the SELECT statement
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
// set the CommandText property of the SqlCommand object to
// the SELECT string
mySqlCommand.rumandText = selectString;
// create a SqlDataAdapter object
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
// set the SelectCommand property of the SqlAdapter object
// to the SqlCommand object
mySqlDataAdapter.SelectCommand = mySqlCommand;
// create a DataSet object to store the results of
// the SELECT statement
DataSet myDataSet = new DataSet();
// open the database connection using the
// Open() method of the SqlConnection object
mySqlConnection.Open();
// use the Fill() method of the SqlDataAdapter object to
// retrieve the rows from the table, storing the rows locally
// in a DataTable of the DataSet object
Console.WriteLine("Retrieving rows from the Customers table");
mySqlDataAdapter.Fill(myDataSet, "Customers");
// get the DataTable object from the DataSet object
DataTable myDataTable = myDataSet.Tables["Customers"];
// use the WriteXml() method to write the DataSet out to an
// XML file
Console.WriteLine("Writing rows out to an XML file named " +
"myXmlFile.xml");
myDataSet.WriteXml("myXmlFile.xml");
// use the WriteXmlSchema() method to write the schema of the
// DataSet out to an XML file
Console.WriteLine("Writing schema out to an XML file named " +
"myXmlSchemaFile.xml");
myDataSet.WriteXmlSchema("myXmlSchemaFile.xml");
// use the Clear() method to clear the current rows in the DataSet
myDataSet.Clear();
// use the ReadXml() method to read the contents of the XML file
// into the DataSet
myDataSet.ReadXml("myXmlFile.xml");
// display the columns for each row in the DataTable,
// using a DataRow object to access each row in the DataTable
foreach (DataRow myDataRow in myDataTable.Rows)
{
Console.WriteLine("CustomerID = " + myDataRow["CustomerID"]);
Console.WriteLine("CompanyName = " + myDataRow["CompanyName"]);
Console.WriteLine("ContactName = " + myDataRow["ContactName"]);
Console.WriteLine("Address = " + myDataRow["Address"]);
}
// close the database connection using the Close() method
// of the SqlConnection object
mySqlConnection.Close();
}
}
</source>
Obtain an XML Document from a SQL Server Query
<source lang="csharp"> using System; using System.Xml; using System.Data; using System.Data.SqlClient; public class XmlQueryExample {
public static void Main() {
using (SqlConnection con = new SqlConnection()) {
con.ConnectionString = "Data Source = localhost;" +
"Database = Northwind; Integrated Security=SSPI";
SqlCommand com = con.CreateCommand();
com.rumandType = CommandType.Text;
com.rumandText = "SELECT CustomerID, CompanyName" +
" FROM Customers FOR XML AUTO";
XmlReader reader = null;
try {
con.Open();
reader = com.ExecuteXmlReader();
while (reader.Read()) {
Console.Write(reader.Name);
if (reader.HasAttributes) {
for (int i = 0; i < reader.AttributeCount; i++) {
reader.MoveToAttribute(i);
Console.Write(" {0}: {1}",reader.Name, reader.Value);
}
reader.MoveToElement();
}
}
} catch (Exception ex) {
Console.WriteLine(ex.ToString());
} finally {
if (reader != null) reader.Close();
}
}
}
}
</source>
Persisting A Dataset To An XML File
<source lang="csharp"> /*
* C# Programmers Pocket Consultant * Author: Gregory S. MacBeth * Email: gmacbeth@comporium.net * Create Date: June 27, 2003 * Last Modified Date: * Version: 1 */
using System; using System.Data; using System.Data.OleDb; namespace Client.Chapter_13___ADO.NET {
public class PersistingADatasetToAnXMLFile
{
static void Main(string[] args)
{
OleDbConnection MyConnection = new OleDbConnection(@"Provider=Microsft.Jet.OLEDB.4.0; Data Source = c:\MyAccessDB.mdb");
OleDbDataAdapter MyAdapter = new OleDbDataAdapter("SELECT Column1, Column2, Column3 FROM MyTable", MyConnection);
DataSet MyDataSet = new DataSet();
MyAdapter.Fill(MyDataSet, "MyTable");
MyDataSet.WriteXml(@"c:\MyDatSet.xml");
}
}
}
</source>
Reading An XML File Into A Dataset
<source lang="csharp"> /*
* C# Programmers Pocket Consultant * Author: Gregory S. MacBeth * Email: gmacbeth@comporium.net * Create Date: June 27, 2003 * Last Modified Date: * Version: 1 */
using System; using System.IO; using System.Data;
namespace Client.Chapter_13___ADO.NET {
public class ReadingAnXMLFileIntoADataset
{
static void Main(string[] args)
{
string MyXMLDoc = @"<?xml version="1.0">?
<title> MyExample</title>";
StringReader MyStringReader = new StringReader(MyXMLDoc);
DataSet MyDataSet = new DataSet();
MyDataSet.ReadXml(MyStringReader);
}
}
}
</source>
