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.CommandText = "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"); } }
Author: coder
Obtain an XML Document from a SQL Server Query
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.CommandType = CommandType.Text;
com.CommandText = “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();
}
}
}
}
[/csharp]
Fill data in DateSet to XmlDocument
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.CommandText = "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); } }
Update Data Using Sql Statements 2
/* * 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.SqlClient; namespace Client.Chapter_13___ADO.NET { public class UpdatingDataUsingSqlStatements { static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true"); MyConnection.Open(); String MyString = "UPDATE Test SET Contact = 'Lee'"; SqlCommand MyCmd = new SqlCommand(MyString, MyConnection); MyCmd.ExecuteScalar(); MyConnection.Close(); } } }
Update Data Using Commond Builder
/* * 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.SqlClient; namespace Client.Chapter_13___ADO.NET { public class UpdatingDataUsingCommondBuilder { static void Main(string[] args) { SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true"); SqlDataAdapter MyDataAdapter = new SqlDataAdapter("SELECT ID, Contact, Email FROM Test", MyConnection); DataSet MyDataSet = new DataSet(); MyDataAdapter.Fill(MyDataSet); MyDataSet.Tables[0].Rows[0][0] = 55; SqlCommandBuilder MyCmd = new SqlCommandBuilder(MyDataAdapter); MyDataAdapter.Update(MyDataSet); } } }
Update A DataSource
/* * 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 UpdatingADataSource { 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.Tables[0].Rows[3]["Column3"] = "Test"; OleDbCommandBuilder MyBuilder = new OleDbCommandBuilder(MyAdapter); MyAdapter.Update(MyDataSet.Tables[0]); } } }
Using Data Table Mappings
/* * 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.Common; using System.Data.SqlClient; namespace Client.Chapter_13___ADO.NET { public class UsingDataTableMappings { static void Main(string[] args) { SqlConnection SConn = new SqlConnection("Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true"); SqlDataAdapter da = new SqlDataAdapter("SELECT ID, Contact, Email FROM CaseInfo", SConn); DataSet ds = new DataSet(); DataTableMapping custMap = da.TableMappings.Add("CaseInfo", "MyDatabase"); custMap.ColumnMappings.Add("ID", "CaseNumber"); custMap.ColumnMappings.Add("Contact", "MyContact"); custMap.ColumnMappings.Add("Email", "Email Address"); da.Fill(ds); } } }