使用具有外键的表将数据从asp.net页插入到我的数据库中 [英] insert data from asp.net page to my database with tables which have foreign key

问题描述

我是asp.net初学者，我的项目是在线购物课程我有问题

I'm a beginner asp.net programmer and my project is online shopping classes I have some problem in it

我有4个表，它们之间有一些外键...

I have 4 tables with some foreign key between them...

CREATE TABLE [dbo].[orderdetails] 
(
    [orderid] INT NOT NULL,
    [classid] INT NOT NULL,

   CONSTRAINT [PK_orderdetails] 
      PRIMARY KEY CLUSTERED ([orderid] ASC, [classid] ASC)
);

CREATE TABLE [dbo].[order] 
(
    [orderid]    INT IDENTITY (300, 1) NOT NULL,
    [customerid] INT NOT NULL,

    CONSTRAINT [PK_order] 
       PRIMARY KEY CLUSTERED ([orderid] ASC)
);

CREATE TABLE [dbo].[customer] 
(
    [customerid] INT IDENTITY (200, 1) NOT NULL,
    [firstname]  NVARCHAR (50) NOT NULL,
    [lastname]   NVARCHAR (50) NOT NULL,
    [phone]      INT           NOT NULL,

    CONSTRAINT [PK_Table_1] 
       PRIMARY KEY CLUSTERED ([customerid] ASC)
);

CREATE TABLE [dbo].[class] 
(
    [classid]    INT IDENTITY (100, 1) NOT NULL,
    [numofclass] INT NOT NULL,
    [numofstud]  INT NOT NULL,
    [totalprice] INT NOT NULL,

    CONSTRAINT [PK_class] 
       PRIMARY KEY CLUSTERED ([classid] ASC)
);

FK_orderdetails_order
FK_order_customer
FK_orderdetails_class

我有三页，在第一页中，我将一些数据传递到另一页，在第二页中，我将数据设置到数据库中.

I have three pages and in first page I pass some data to another page and in second page I set my data to my DB.

首页代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;  

public partial class _Default : System.Web.UI.Page
{
       static int totalprice = 0;

       protected void Page_Load(object sender, EventArgs e)
       {
           int studprice = Convert.ToInt32(Numofstud.SelectedValue) * 6;
           int classprice = Convert.ToInt32(Numofclass.SelectedValue) * 190;

           totalprice = studprice + classprice;
           lblTotalprice.Text = string.Format("{0}", totalprice);
       }

       protected void Numofstud_SelectedIndexChanged(object sender, EventArgs e)
       {
           int studprice = Convert.ToInt32(Numofstud.SelectedValue) * 6;
           int classprice = Convert.ToInt32(Numofclass.SelectedValue) * 190;

           totalprice = studprice + classprice;
           lblTotalprice.Text = string.Format("{0}", totalprice);
       }

       protected void Numofclass_SelectedIndexChanged(object sender, EventArgs e)
       {
                int studprice = Convert.ToInt32(Numofstud.SelectedValue) * 6;
                int classprice = Convert.ToInt32(Numofclass.SelectedValue) * 190;

                totalprice = studprice + classprice;
                lblTotalprice.Text = string.Format("{0}", totalprice);
            }

            protected void Registerbtn_Click(object sender, EventArgs e)
            {
                Session["Numofclass"] = Numofclass.SelectedItem.Value;
                Session["totalprice"] = totalprice;
                Session["Numofstud"] = Numofstud.SelectedItem.Value;

                Response.Redirect("account.aspx");
            }  
        }

第二页代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;  


public partial class account : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }

    protected void buybtn_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["miztahrirtest2DB"].ToString());

        SqlCommand cmd = new SqlCommand("insert into customer (firstname, lastname, phone) values (@firstname, @lastname, @phone)", con);
        cmd.Parameters.AddWithValue("firstname", firstnametxt.Text);
        cmd.Parameters.AddWithValue("lastname", lastnametxt.Text);
        cmd.Parameters.AddWithValue("phone", phonetxt.Text);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

        SqlCommand cmd2 = new SqlCommand("insert into class (numofstud, numofclass, totalprice) values (@numofstud, @numofclass, @totalprice)", con);
        cmd2.Parameters.AddWithValue("numofclass", Session["Numofclass"]);
        cmd2.Parameters.AddWithValue("numofstud", Session["Numofstud"]);
        cmd2.Parameters.AddWithValue("totalprice", Session["totalprice"]);

        con.Open();
        cmd2.ExecuteNonQuery();
        con.Close();

        SqlCommand cmd3 = new SqlCommand("insert into order ....

        Response.Redirect("bank.aspx");
    }
}

我的问题是我不知道如何将值插入具有外键和主键的表中.cmd1和cmd2正常工作，但是我无法编写任何东西来设置order table和orderdetails table，它们具有来自另一个表的外键...

My problem is that I don't know how to insert value into tables which have foreign key and primary key.cmd1 and cmd2 are working correctly but I can't write something to set order table and orderdetails table which They have a foreign key from another table...

推荐答案

插入父表时，应将scope_identity()放在插入命令的末尾.并且您应该使用execute标量.范围标识通过执行标量为您提供插入的ID.之后，您可以使用此父ID插入子项.

When you inserting parent table you should put scope_identity() end of insert command. And you should use execute scalar. Scope identity give you inserted id by execute scalar. After then you can insert child items with this parent id.

这篇关于使用具有外键的表将数据从asp.net页插入到我的数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！