问题描述
大家好,
我目前正在努力将一些值从寄存器表单插入到数据库中。我有2个表,我想在其中插入数据:RegisterTeacher和TeacherCourses.Now,我在这里遇到了逻辑问题,虽然我已经通过互联网搜索了不同的可能性。这些是表格:
Hello everyone,
I'm currently struggling with inserting some values from the register form into the database.I have 2 tables in which I want to insert data:RegisterTeacher and TeacherCourses.Now,I have a problem with the logic here,although I have searched over the internet to see different possibilities of doing this.These are the tables:
CREATE TABLE [dbo].[RegisterTeacher] (
[SNTeacher] INT NOT NULL,
[UserName] NVARCHAR (50) NOT NULL,
[pwd] INT NOT NULL,
[fullName] NVARCHAR (MAX) NOT NULL,
[education] NVARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([SNTeacher] ASC)
);
CREATE TABLE [dbo].[AvailableCourses] (
[courseName] NVARCHAR (MAX) NOT NULL,
[education] NVARCHAR (50) NOT NULL,
[ClassID] NVARCHAR (50) NULL,
[courseID] INT NOT NULL,
PRIMARY KEY CLUSTERED ([courseID] ASC)
);
CREATE TABLE [dbo].[TeacherCourses] (
[courseID] INT NOT NULL,
[SNTeacher] INT NOT NULL,
[IDKey] NCHAR (10) NOT NULL,
PRIMARY KEY CLUSTERED ([IDKey] ASC),
CONSTRAINT [FK_TeacherCourses_ToTable] FOREIGN KEY ([SNTeacher]) REFERENCES [dbo].[RegisterTeacher] ([SNTeacher]),
CONSTRAINT [FK_TeacherCourses_ToTable_1] FOREIGN KEY ([courseID]) REFERENCES [dbo].[AvailableCourses] ([courseID])
);
现在我要做的是以下内容:对于RegisterTeacher表,它适用于保存值,因为我这样做:
Now what i what to achieve is the following:For the RegisterTeacher table it works to save the values,as I am doing it like this:
connectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\User\\Desktop\\BackupVersion\\LastAndFinalVersion\\DatabaseStudents.mdf;Integrated Security=True";
using (SqlConnection con = new SqlConnection(connectionString))
{
sql = "Insert into RegisterTeacher(SNTeacher,UserName,pwd,fullName,education)values(@SNTeacher,@UserName,@pwd,@fullName,@education)";
con.Open();
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@SNTeacher", SqlDbType.Int).Value=SNTeacher;
cmd.Parameters.AddWithValue("@UserName", SqlDbType.NVarChar).Value = UserName;
cmd.Parameters.AddWithValue("@pwd", SqlDbType.Int).Value = pwd ;
cmd.Parameters.AddWithValue("@fullName", SqlDbType.NVarChar).Value = fullName ;
cmd.Parameters.AddWithValue("@education", SqlDbType.NVarChar).Value=education;
cmd.ExecuteNonQuery();
}
我的问题是当我想将courseName保存到表TeacherCourses.I想要保存SNTeacher和courseName-其中courseName取自AvailableCourses并使用courseC引用传递给TeacherCourses表以及来自RegisterTeacher表的SNTeacher。语法如何?
什么我试过了:
我尝试的是以下内容:我已经单独查询将数据插入到TeacherCourses中:
My problem is when I want to save the courseName into the table TeacherCourses.I would like to both save the SNTeacher and courseName-where courseName is taken from AvailableCourses and passed with the courseID reference to the TeacherCourses table along with SNTeacher from RegisterTeacher table.How would the syntax look like?
What I have tried:
What I have tried is the following:I have made a separate query to insert data into TeacherCourses:
using (SqlCommand com = new SqlCommand(query, con))
{
query = "Insert into TeacherCourses (courseID,SNTeacher) select courseID,SNTeacher from RegisterTeacher p inner join AvailableCourses c ON p.courseID=c.courseID AND p.SNTeacher=c.SNTeacher";
SelectedCourses = new ObservableCollection<AvailableCours>();
foreach (var item in Courses)
{
if (item.IsChecked)
{
SelectedCourses.Add(item);
}
com.Parameters.AddWithValue("@courseName", SqlDbType.NVarChar).Value = item.courseName;
com.ExecuteNonQuery();
推荐答案
using (SqlCommand com = new SqlCommand("", con))
{
var query = new System.Text.StringBuilder();
query.Append("INSERT INTO TeacherCourses (courseID, SNTeacher) SELECT courseID, @SNTeacher FROM AvailableCourses WHERE courseName IN (");
bool started = false;
foreach (var item in Courses)
{
if (item.IsChecked)
{
string name = "@courseName" + com.Parameters.Count;
com.Parameters.AddWithValue(name, item.courseName);
if (started) query.Append(',');
query.Append(name);
started = true;
}
}
if (started)
{
query.Append(')');
com.CommandText = query.ToString();
com.Parameters.AddWithValue("@SNTeacher", SNTeacher);
com.ExecuteNonQuery();
}
}
这篇关于如何将两个表中的值插入第三个表中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!