• 一个主从表,母子表更新示例


    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

  • 相关阅读:
    Django+nginx+uwsgi静态文件permission denied!
    部署Django项目到Centos6.9服务器
    Django Rest Framework 使用summernote上传图片(二)
    Django Rest Framework 使用summernote上传图片(一)
    基于DRF的企业级流程管理系统
    js获取cookie里保存的中文
    web Worker使js实现‘多线程’?
    css3折叠效果
    移动端适配方案研究
    css before,after伪元素妙用
  • 原文地址:https://www.cnblogs.com/eugenewu0808/p/RelationChild.html
Copyright © 2020-2023  润新知