C# ado.net DataSet使用(五)

一、填充dataset

 class Program
    {
        private static string constr = "server=.;database=northwnd;integrated security=sspi";
        static void Main(string[] args)
        {
            string sql = "select contactname,companyname from customers";
            using (SqlConnection con=new SqlConnection(constr))
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, constr);
                DataSet ds = new DataSet();
                sda.Fill(ds, "Customers");
                foreach (DataRow row in ds.Tables["Customers"].Rows)
                    Console.WriteLine("'{0}' from {1}",
                        row[0],
                        row[1]);

                con.Close();
            }
            Console.ReadLine();
        } 
    }

二、dataset主外键关系

class Program
    {
        private static string constr = "server=.;database=northwnd;integrated security=sspi";
        static void Main(string[] args)
        {
            string select = "SELECT * FROM Products";
            string sel2 = "SELECT * FROM Categories";
            using (SqlConnection con=new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter(select, con);

                DataSet ds = new DataSet();

              
                ManufactureProductDataTable(ds);

                da.Fill(ds, "Products");

                foreach (DataRow row in ds.Tables["Products"].Rows)
                    Console.WriteLine("'{0}' from {1}",
                        row[0],
                        row[1]);

                SqlDataAdapter da2 = new SqlDataAdapter(sel2, con);

             
                ManufactureCategoryTable(ds);

                da2.Fill(ds, "Categories");
                 
                AddForeignKeyConstraint(ds);

                con.Close();
            }
        }
        public static void ManufactureProductDataTable(DataSet ds)
        {
            DataTable products = new DataTable("Products");
            products.Columns.Add(new DataColumn("ProductID", typeof(int)));
            products.Columns.Add(new DataColumn("ProductName", typeof(string)));
            products.Columns.Add(new DataColumn("SupplierID", typeof(int)));
            products.Columns.Add(new DataColumn("CategoryID", typeof(int)));
            products.Columns.Add(new DataColumn("QuantityPerUnit", typeof(string)));
            products.Columns.Add(new DataColumn("UnitPrice", typeof(decimal)));
            products.Columns.Add(new DataColumn("UnitsInStock", typeof(short)));
            products.Columns.Add(new DataColumn("UnitsOnOrder", typeof(short)));
            products.Columns.Add(new DataColumn("ReorderLevel", typeof(short)));
            products.Columns.Add(new DataColumn("Discontinued", typeof(bool)));

            ManufacturePrimaryKey(products);

            ds.Tables.Add(products);
        }
        public static void ManufacturePrimaryKey(DataTable dt)
        {
            DataColumn[] pk = new DataColumn[1];
            pk[0] = dt.Columns["ProductID"];
            dt.Constraints.Add(new UniqueConstraint("PK_Products", pk[0]));
            dt.PrimaryKey = pk;
        }
        public static void ManufactureCategoryTable(DataSet ds)
        {
            DataTable categories = new DataTable("Categories");

            categories.Columns.Add(new DataColumn("CategoryID", typeof(int)));
            categories.Columns.Add(new DataColumn("CategoryName", typeof(string)));
            categories.Columns.Add(new DataColumn("Description", typeof(string)));

            categories.Constraints.Add(new UniqueConstraint("PK_Categories", categories.Columns["CategoryID"]));

            categories.PrimaryKey = new DataColumn[1] { categories.Columns["CategoryID"] };

            ds.Tables.Add(categories);

        }
        public static void AddForeignKeyConstraint(DataSet ds)
        {
            DataColumn parent = ds.Tables["Categories"].Columns["CategoryID"];
            DataColumn child = ds.Tables["Products"].Columns["CategoryID"];

            ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_Product_CategoryID", parent, child);

            fk.UpdateRule = Rule.Cascade;
            fk.DeleteRule = Rule.SetNull;

            // Create the constraint
            // If this fails, you have a row in the products table with no associated category
            ds.Tables["Products"].Constraints.Add(fk);

        }
    }

三、数据关联关系

   class Program
    {
        static void Main(string[] args)
        {
          
            DataSet ds = CreateDataSetWithRelationships();

           
            DataRow aBuilding = ds.Tables["Building"].NewRow();

            aBuilding["BuildingID"] = 1;
            aBuilding["Name"] = "The Lowry";

            ds.Tables["Building"].Rows.Add(aBuilding);

        
            DataRow aRoom = ds.Tables["Room"].NewRow();

            aRoom["RoomID"] = 1;
            aRoom["Name"] = "Reception";
            aRoom["BuildingID"] = 1;

            ds.Tables["Room"].Rows.Add(aRoom);

     
            aRoom = ds.Tables["Room"].NewRow();
            aRoom["RoomID"] = 2;
            aRoom["Name"] = "The Modern Art Gallery";
            aRoom["BuildingID"] = 1;

            ds.Tables["Room"].Rows.Add(aRoom);

         
            foreach (DataRow theBuilding in ds.Tables["Building"].Rows)
            {
                DataRow[] children = theBuilding.GetChildRows("Rooms");
                int roomCount = children.Length;

                Console.WriteLine("Building {0} contains {1} room{2}",
                    theBuilding["Name"],
                    roomCount,
                    roomCount > 1 ? "s" : "");

         
                foreach (DataRow theRoom in children)
                    Console.WriteLine("Room: {0}", theRoom["Name"]);
            }

         
            foreach (DataRow theRoom in ds.Tables["Room"].Rows)
            {
                DataRow[] parents = theRoom.GetParentRows("Rooms");

                foreach (DataRow theBuilding in parents)
                    Console.WriteLine("Room {0} is contained in building {1}", theRoom["Name"], theBuilding["Name"]);
            }
        }
        public static DataSet CreateDataSetWithRelationships()
        {
            DataSet ds = new DataSet("Relationships");

            ds.Tables.Add(CreateBuildingTable());
            ds.Tables.Add(CreateRoomTable());

           
            ds.Relations.Add("Rooms", ds.Tables["Building"].Columns["BuildingID"], ds.Tables["Room"].Columns["BuildingID"]);

            return ds;
        }
        public static DataTable CreateBuildingTable()
        {
            DataTable aBuilding = new DataTable("Building");
            aBuilding.Columns.Add(new DataColumn("BuildingID", typeof(int)));
            aBuilding.Columns.Add(new DataColumn("Name", typeof(string)));
            aBuilding.Constraints.Add(new UniqueConstraint("PK_Building", aBuilding.Columns[0]));
            aBuilding.PrimaryKey = new DataColumn[] { aBuilding.Columns[0] };

            return aBuilding;
        }

        public static DataTable CreateRoomTable()
        {
            DataTable aRoom = new DataTable("Room");
            aRoom.Columns.Add(new DataColumn("RoomID", typeof(int)));
            aRoom.Columns.Add(new DataColumn("Name", typeof(string)));
            aRoom.Columns.Add(new DataColumn("BuildingID", typeof(int)));
            aRoom.Constraints.Add(new UniqueConstraint("PK_Room", aRoom.Columns[0]));
            aRoom.PrimaryKey = new DataColumn[] { aRoom.Columns[0] };

            return aRoom;
        }
    }