C#代码:
/// <summary>
/// Summary description for RelationalClass.
/// </summary>
class RelationalClass
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
//
// TODO: Add code to start application here
//
// Create the DataSet object
DataSet oDS = new DataSet();
SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Orders;Integrated Security=SSPI");
conn.Open();
// Create the DataTable "Orders" in the Dataset and the OrdersDataAdapter
SqlDataAdapter oOrdersDataAdapter = new SqlDataAdapter(new SqlCommand("SELECT * FROM Orders", conn));
oOrdersDataAdapter.InsertCommand = new SqlCommand("proc_InsertOrder", conn);
SqlCommand cmdInsert = oOrdersDataAdapter.InsertCommand;
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.Add(new SqlParameter("@OrderId", SqlDbType.Int));
cmdInsert.Parameters["@OrderId"].Direction = ParameterDirection.Output;
cmdInsert.Parameters["@OrderId"].SourceColumn = "OrderId";
cmdInsert.Parameters.Add(new SqlParameter("@CustomerName", SqlDbType.VarChar,50,"CustomerName"));
cmdInsert.Parameters.Add(new SqlParameter("@ShippingAddress", SqlDbType.VarChar,50,"ShippingAddress"));
oOrdersDataAdapter.FillSchema(oDS, SchemaType.Source);
DataTable pTable = oDS.Tables["Table"];
pTable.TableName = "Orders";
// Create the DataTable "OrderDetails" in the Dataset and the OrderDetailsDataAdapter
SqlDataAdapter oOrderDetailsDataAdapter = new SqlDataAdapter(new SqlCommand("SELECT * FROM OrderDetails", conn));
oOrderDetailsDataAdapter.InsertCommand = new SqlCommand("proc_InsertOrderDetails", conn);
cmdInsert = oOrderDetailsDataAdapter.InsertCommand;
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.Add(new SqlParameter("@OrderId", SqlDbType.Int));
cmdInsert.Parameters["@OrderId"].SourceColumn = "OrderId";
cmdInsert.Parameters.Add(new SqlParameter("@ProductId", SqlDbType.Int));
cmdInsert.Parameters["@ProductId"].SourceColumn = "ProductId";
cmdInsert.Parameters.Add(new SqlParameter("@ProductName", SqlDbType.VarChar,50,"ProductName"));
cmdInsert.Parameters.Add(new SqlParameter("@UnitPrice", SqlDbType.Decimal));
cmdInsert.Parameters["@UnitPrice"].SourceColumn = "UnitPrice";
cmdInsert.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int ));
cmdInsert.Parameters["@Quantity"].SourceColumn = "Quantity";
oOrderDetailsDataAdapter.FillSchema(oDS, SchemaType.Source);
pTable = oDS.Tables["Table"];
pTable.TableName = "OrderDetails";
// Create the relationship between the two tables
oDS.Relations.Add(new DataRelation("ParentChild",
oDS.Tables["Orders"].Columns["OrderId"],
oDS.Tables["OrderDetails"].Columns["OrderId"]));
// Insert the Data
DataRow oOrderRow = oDS.Tables["Orders"].NewRow();
oOrderRow["CustomerName"] = "Customer ABC";
oOrderRow["ShippingAddress"] = "ABC street, 12345";
oDS.Tables["Orders"].Rows.Add(oOrderRow);
DataRow oDetailsRow = oDS.Tables["OrderDetails"].NewRow();
oDetailsRow["ProductId"] = 1;
oDetailsRow["ProductName"] = "Product 1";
oDetailsRow["UnitPrice"] = 1;
oDetailsRow["Quantity"] = 2;
oDetailsRow.SetParentRow(oOrderRow);
oDS.Tables["OrderDetails"].Rows.Add(oDetailsRow);
oOrdersDataAdapter.Update(oDS, "Orders");
oOrderDetailsDataAdapter.Update(oDS, "OrderDetails");
conn.Close();
}
}
SQL:
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Orders')
DROP DATABASE [Orders]
GO
CREATE DATABASE [Orders]
GO
exec sp_dboption N'Orders', N'autoclose', N'false'
GO
exec sp_dboption N'Orders', N'bulkcopy', N'false'
GO
exec sp_dboption N'Orders', N'trunc. log', N'false'
GO
exec sp_dboption N'Orders', N'torn page detection', N'true'
GO
exec sp_dboption N'Orders', N'read only', N'false'
GO
exec sp_dboption N'Orders', N'dbo use', N'false'
GO
exec sp_dboption N'Orders', N'single', N'false'
GO
exec sp_dboption N'Orders', N'autoshrink', N'false'
GO
exec sp_dboption N'Orders', N'ANSI null default', N'false'
GO
exec sp_dboption N'Orders', N'recursive triggers', N'false'
GO
exec sp_dboption N'Orders', N'ANSI nulls', N'false'
GO
exec sp_dboption N'Orders', N'concat null yields null', N'false'
GO
exec sp_dboption N'Orders', N'cursor close on commit', N'false'
GO
exec sp_dboption N'Orders', N'default to local cursor', N'false'
GO
exec sp_dboption N'Orders', N'quoted identifier', N'false'
GO
exec sp_dboption N'Orders', N'ANSI warnings', N'false'
GO
exec sp_dboption N'Orders', N'auto create statistics', N'true'
GO
exec sp_dboption N'Orders', N'auto update statistics', N'true'
GO
use [Orders]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_InsertOrder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_InsertOrder]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_InsertOrderDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_InsertOrderDetails]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrderDetails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OrderDetails]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Orders]
GO
CREATE TABLE [dbo].[OrderDetails] (
[OrderId] [int] NOT NULL ,
[ProductId] [int] NOT NULL ,
[ProductName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[UnitPrice] [decimal](18, 0) NOT NULL ,
[Quantity] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Orders] (
[OrderId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[ShippingAddress] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderDetails] WITH NOCHECK ADD
CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
[OrderId],
[ProductId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderId]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE proc_InsertOrder
(@OrderId int output,
@CustomerName varchar(50),
@ShippingAddress varchar(50)
)
AS
INSERT INTO Orders (CustomerName, ShippingAddress)
VALUES
(@CustomerName, @ShippingAddress)
SELECT @OrderId=@@IDENTITY
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE proc_InsertOrderDetails
(@OrderId int,
@ProductId int,
@ProductName varchar(50),
@UnitPrice decimal,
@Quantity int
)
AS
INSERT INTO OrderDetails
VALUES
(@OrderId,
@ProductId,
@ProductName,
@UnitPrice,
@Quantity)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
