如何在包含外键的表中插入数据 [英] How to insert data in a table which contain foreign keys
问题描述
Hello Guys。
我正在开发一个图书管理系统项目并面临一个问题。我有一个表，如作者，出版商，类别，bookauthor（BookAuthorId，AuthorId，BookId），Bookcategory（BookCategoryId，BookId，CategoryId），BookPublisher（BookPublisherId，BookId，PublisherId）以及大多数包含属性的书表（BookId，BookTitle ，BookEdition，BookAuthorId，BookPublisherId，BookCategoryId）。希望你能理解我的数据库。
问题是，无论何时我要为新书插入数据，我首先要插入BookTitle，BookEdition和BookQuantity的值，以便获取新插入的书的ID，然后把它放入像BookAuthor（BookId，Combobox1.selectedvalue）这样的外键表中。
但它不起作用。告诉我如何在单击按钮时添加新书并处理这些外键，我正在共享代码，希望你能接受我的观点。
Hello Guys.
I'm developing a project of Library Management System and facing a problem. I have a tables like author, publisher, category, bookauthor(BookAuthorId,AuthorId,BookId), Bookcategory(BookCategoryId,BookId,CategoryId), BookPublisher(BookPublisherId, BookId,PublisherId) and most of all book table which contain attributes(BookId,BookTitle, BookEdition, BookAuthorId,BookPublisherId,BookCategoryId). Hope you'll understand my DB.
The question is, whenever i'm going to insert data for a new book, i firstly insert values of BookTitle,BookEdition and BookQuantity, for the idea to fetch the id of freshly inserted book and then put it into foreign keys tables like BookAuthor(BookId,Combobox1.selectedvalue).
But it doesn't working. Tell me how can i add new book and handle these foreign keys at one button click, i'm sharing the code, hope you'll pick up my point.
DatabaseConnect db = new DatabaseConnect();
DataSet ds = new DataSet();
db.executequery("Insert into Book(BookTitle, BookEdition,BookQuantity,BookStatus)Values('" + textBox1.Text + "','" + textBox2.Text + "''" + textBox3.Text + "','1')");
ds = db.executedataset("Select Max(BookId) from Book");
int i = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
db.executequery("Insert into BookCategory(BookId,CategoryId)Values('" + i + "','" + comboBox1.SelectedValue + "')");
db.executequery("Insert into BookAuthor(BookId,AuthorId)Values('" + i + "','" + comboBox2.SelectedValue + "')");
db.executequery("Insert into BookPublisher(BookId,PublisherId)Values('" + i + "','" + comboBox3.SelectedValue + "')");
MessageBox.Show(comboBox1.SelectedText, " Book Successfully inserted");
请帮帮我。
编辑 - 来自评论的表结构
表名= Book
BookId int NotNull
BookTitile varchar NotNull
BookEdition varchar NotNull
BookQuantity varchar NotNull
BookStatus int NotNull
BookAuthorId int NotNull
BookCategoryId int NotNull
BookPublisherId int NotNull
BookAuthor =表
BookAuthorId int NotNull
BookId int NotNull
AuthorId int NotNull
BookPublisher = table
BookPublisherId int NotNull
BookId int NotNull
PublisherId int Not Null
作者=表
AuthorId int notNull
AuthorName varchar notNull
发布者= table
PublisherId int notnull
PublisherName Varchar notNull
Help me out please.
Edit - table structure from comments
Table Name= Book
BookId int NotNull
BookTitile varchar NotNull
BookEdition varchar NotNull
BookQuantity varchar NotNull
BookStatus int NotNull
BookAuthorId int NotNull
BookCategoryId int NotNull
BookPublisherId int NotNull
BookAuthor =Table
BookAuthorId int NotNull
BookId int NotNull
AuthorId int NotNull
BookPublisher =table
BookPublisherId int NotNull
BookId int NotNull
PublisherId int Not Null
Author =Table
AuthorId int notNull
AuthorName varchar notNull
Publisher = table
PublisherId int notnull
PublisherName Varchar notNull
推荐答案
你必须使用 SCOPE_IDENTITY [ ^ ]以获取插入的最新标识值并将其返回给应用程序。我不知道DatabaseConnect类，但它看起来应该是这样的：
You must use SCOPE_IDENTITY[^] in your SQL to get the latest identity value inserted and return that to the application. I don't know the DatabaseConnect class but it should look something like this:
var bookId = db.executescalar("Insert into Book(BookTitle, BookEdition,BookQuantity,BookStatus)Values('" + textBox1.Text + "','" + textBox2.Text + "''" + textBox3.Text + "','1'); select SCOPE_IDENTITY()");
请注意，您的代码容易受到SQL注入攻击。您应该使用参数将值从UI传递到数据库。
编辑
我重复使用您的代码提取数据集中的第一个值以绕过不存在的executioncalar（）。如果SCOPE_IDENTITY不起作用，可以尝试 @@ IDENTITY [ ^ ]。
Please note that your code is vulnerable to SQL injection. You should use parameters to pass the values from UI to the database.
Edit
I reused your code to extract first value in the dataset to get around the non-existing executescalar(). If SCOPE_IDENTITY is not working for you can try @@IDENTITY[^].
DataSet ds = db.executedataset("Insert into Book(BookTitle, BookEdition,BookQuantity,BookStatus)Values('" + textBox1.Text + "','" + textBox2.Text + "''" + textBox3.Text + "','1'); select @@IDENTITY");
int bookId = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
编辑2 - 解决实际问题
您需要为所有非空列提供如下值：
Edit 2 - to solve the real problem
You need to provide values for all not null columns like this:
DataSet ds = db.executedataset("Insert into Book (BookTitle, BookEdition, BookQuantity, BookStatus, BookCategoryId, BookAuthorId, BookPublisherId) Values('" + textBox1.Text + "','" + textBox2.Text + "''" + textBox3.Text + "','1','" + comboBox1.SelectedValue + "','" + comboBox2.SelectedValue + "','" + comboBox3.SelectedValue + "'); select SCOPE_IDENTITY()");
int bookId = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
我不知道你的DatabaseConnect类是否支持参数，但是你应该真正研究它。
编辑3 - 更改表格
我之前没有太注意其他表格。问题无法解决 - 有鸡蛋问题。要插入预订你需要BookPublisherId。但是要插入BookPublisher表你需要BookId。你需要改变你的表结构。让书直接参考出版商，作者，类别：
I don't know if your DatabaseConnect class supports parameter, but it's something you should really look into.
Edit 3 - change tables
I wasn't paying much attention to the other tables previously. The issue cannot be resolved - there is a chicken-egg problem. To insert a Book you need BookPublisherId. But to insert into the BookPublisher table you need BookId. You need to change your table structure. Let Book reference Publisher, Author, Category directly:
Book table
BookId indentity
AuthorId FK referencing Author table
PublisherId FK referencing Publisher table
CategoryId FK referencing Category table
... other columns ...
删除BookCategory，BookAuthor，BookPublisher它们在当前形式中是无用的。这样，最后一个查询将按预期工作。
Drop tables BookCategory, BookAuthor, BookPublisher they are useless in their current form. This way the last query will work as expected.
这篇关于如何在包含外键的表中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
