c# - 从一个表插入到另一个表
问题描述
我正在使用SQL数据库在C#中创建一个项目,我需要从一个表(书籍)中获取数据并将其放入另一个表(用户)中。
我试图使它的功能称为“LoanBooks”,到目前为止,我设法通过复制名称列在用户中创建了一条新记录,但前提是我从代码中删除了这部分:AND (SELECT Username from dbo.Users WHERE Username like('%" + text + "%'))
我想要做的是通过文本框给出特定的“用户名”和“名称”,将“名称”列从“书籍”复制到“用户”。
class User
{
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter adpt;
DataTable dt;
string Username;
string Password;
string Name;
public User(String aUser, String aPassword)
{
Username = aUser;
Password = aPassword;
con = new SqlConnection(@"Data Source=SILVIU-ASUS-N55;Initial Catalog=BooksDB;Integrated Security=True;Pooling=False");
}
public User(String aUser, String aPassword,string aName)
{
Username = aUser;
Password = aPassword;
Name = aName;
con = new SqlConnection(@"Data Source=SILVIU-ASUS-N55;Initial Catalog=BooksDB;Integrated Security=True;Pooling=False");
}
public void SaveUser()
{
con.Open();
cmd = new SqlCommand("INSERT INTO dbo.Users(Username,Password) VALUES('" + Username + "','" + Password + "')", con);
cmd.ExecuteNonQuery();
con.Close();
}
public void DeleteUser(string Username)
{
con.Open();
cmd = new SqlCommand("DELETE FROM dbo.Users WHERE Username = '" + Username + "'", con);
cmd.ExecuteNonQuery();
con.Close();
}
public void ShowUsers(DataGridView data)
{
adpt = new SqlDataAdapter("Select * from Users", con);
dt = new DataTable();
adpt.Fill(dt);
data.DataSource = dt;
}
public void ShowLoanUsers(DataGridView data)
{
adpt = new SqlDataAdapter("Select Username,Name from Users", con);
dt = new DataTable();
adpt.Fill(dt);
data.DataSource = dt;
}
public void SearchUser(DataGridView data, string text)
{
try
{
adpt = new SqlDataAdapter("Select * from Users where Username like('%" + text + "%')", con);
dt = new DataTable();
adpt.Fill(dt);
data.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void SearchLoanUser(DataGridView data, string text)
{
try
{
adpt = new SqlDataAdapter("Select Username,Name from Users where Username like('%" + text + "%')", con);
dt = new DataTable();
adpt.Fill(dt);
data.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void LoanBook(string text,string text1)
{
con.Open();
cmd = new SqlCommand("INSERT INTO dbo.Users (Name) SELECT Name FROM dbo.Books WHERE Name like('%" + text1 + "%'), AND (SELECT Username from dbo.Users WHERE Username like('%" + text + "%'))", con);
cmd.ExecuteNonQuery();
con.Close();
}
}
这是主要的:
public partial class frmUser : Form
{
User temp;
Books temp1;
public frmUser()
{
InitializeComponent();
temp = new User("", "", "");
temp1 = new Books("", "", "", 0);
}
private void btnBack_Click(object sender, EventArgs e)
{
frmLogin f = new frmLogin();
this.Hide();
f.ShowDialog();
this.Close();
}
private void btnExit_Click(object sender, EventArgs e)
{
this.Close();
}
private void btnLoanBook_Click(object sender, EventArgs e)
{
temp.LoanBook(txtUsername.Text, txtBookName.Text);
}
private void btnSearchBook_Click(object sender, EventArgs e)
{
temp1.SearchBook(dgvBooks, txtSearchBook.Text);
}
private void btnSearchUser_Click(object sender, EventArgs e)
{
temp.SearchLoanUser(dgvUsers, txtSearchUser.Text);
}
private void btnShowUsers_Click(object sender, EventArgs e)
{
temp.ShowLoanUsers(dgvUsers);
}
private void btnShowBooks_Click_1(object sender, EventArgs e)
{
temp1.ShowBooks(dgvBooks);
}
}
解决方案
我不确定这个问题是什么,所以我会尽量用最好的方式来解释它。如果您能够提供您的表结构的外观,这将有所帮助。如果我正确理解了这个问题,您希望您的 LoanBook 函数更新用户表。虽然这是完全可行的,但我强烈建议您阅读第三范式。( https://en.wikipedia.org/wiki/Third_normal_form ) 对于任何关系数据库(SQL、Oracle 等),您的目标是限制重复数据的数量。现在有了这个例子,如果用户要查看多本书会发生什么?我想这是您希望发生的事情,但请考虑您将要重复的所有数据。
表用户
列
FirstName MiddleName LastName BookCheckedout 等等,等等,等等,
Silviu A Szabo Green Eggs and Ham
Silviu A Szabo Go Dog Go
Silviu A Szabo How the Grinch Stole Christmas
那么为什么这看起来非常好,发生的事情是您正在重复前 3 列(Silviu A Szabu),这会占用数据库中不需要或良好做法的空间。更好的选择是制作一个交叉引用表。一个 Users 表、一个 Books 表以及一个 UsersBooks 表或 LoanedBooks 表。用户表将只有 Silviu 的单个记录,而 Books 将为特定书籍创建单个记录。交叉引用表将包含已签出书籍的所有记录。
表用户
列
UserId(PrimaryKey) FirstName MiddleName LastName
1 Silviu A Szabo
2 Stephen A S
3 Darth A Vader
桌书
列
BookId(PrimaryKey) 书
1 Green Eggs and Ham
2 Go Dog Go
3 How the Grinch Stole Christmas
表 Users_Books_XRef
列
UserId(ForeignKey) BooksId(ForeignKey)
1 1
1 2
1 3
从那里您只需要将用户 ID 传递到已签出书籍的交叉引用表中。