/*
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.CommandText = 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();
}
}