program.cs

讲述如何使用c#访问关系数据库

#region Using directives

using System;
using System.Data;            // Use ADO.NET namespace
using System.Data.SqlClient;  // Use SQL Server data provider namespace
using System.Collections.Generic;
using System.Text;

#endregion

namespace DataRelationExample
{
	class Program
	{
		static void Main(string[] args)
		{
			// Specify SQL Server-specific connection string
			SqlConnection thisConnection = new SqlConnection(
				 @"Server=(local)\sqlexpress;Integrated Security=True;" +
				 "Database=northwind");

			// Create DataAdapter object for update and other operations
			SqlDataAdapter thisAdapter = new SqlDataAdapter(
				 "SELECT CustomerID, CompanyName FROM Customers", thisConnection);
			// Create CommandBuilder object to build SQL commands
			SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

			// Create DataSet to contain related data tables, rows, and columns
			DataSet thisDataSet = new DataSet();

			// Set up DataAdapter objects for each table and fill
			SqlDataAdapter custAdapter = new SqlDataAdapter(
				 "SELECT * FROM Customers", thisConnection);
			SqlDataAdapter orderAdapter = new SqlDataAdapter(
				 "SELECT * FROM Orders", thisConnection);
			custAdapter.Fill(thisDataSet, "Customers");
			orderAdapter.Fill(thisDataSet, "Orders");

			// Set up DataRelation between customers and orders
			DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders",
				 thisDataSet.Tables["Customers"].Columns["CustomerID"],
				 thisDataSet.Tables["Orders"].Columns["CustomerID"]);

			// Print out nested customers and their order ids
			foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows)
			{
				Console.WriteLine("Customer ID: " + custRow["CustomerID"] +
								  " Name: " + custRow["CompanyName"]);
				foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
				{
					Console.WriteLine("  Order ID: " + orderRow["OrderID"]);
				}
			}
			/*
             * to implement writexml as instructed in chapter 24 Writing XML from a DataSet
             * custOrderRel.Nested = true;
             *
			 * thisDataSet.WriteXml(@"c:\tmp\nwinddata.xml");
			 * Console.WriteLine(
			 *	 @"Successfully wrote XML output to file c:\tmp\nwinddata.xml");
            */
			
			thisConnection.Close();
			Console.Write("Program finished, press Enter/Return to continue:");
			Console.ReadLine();
		}
	}
}