如何使用Windows Forms C＃而不是SQL在SQL Server中合并两个表

I have two tables:

我有两张桌子:

StudentSection1 with the following columns and information:

StudentSection1包含以下列和信息:

StudentID (int)(PK)     StudentName (varchar(100))     StudentGender (varchar(100))
-----------------------------------------------------------------------------------
1                       Henry                          Male    
2                       Scarlette                      Female

StudentSection2 with the following columns and information:

StudentSection2包含以下列和信息:

StudentID (int)(PK)     StudentName (varchar(100))     StudentGender (varchar(100))
-----------------------------------------------------------------------------------
1                       Jack                           Male    
2                       Elizabeth                      Female

Desired Result:

 StudentID    StudentName     StudentGender
 ------------------------------------------
     1        Henry           Male    
     2        Scarlette       Female
     1        Jack            Male    
     2        Elizabeth       Female

Name of database: StudentDB

数据库名称:StudentDB

I know that there are related questions here, but all I see are answers that only cater to manipulating SQL directly and not Windows Forms and C#. And I know that there Student ID's should be different, but let's just say each section has it's own ID numbers for it's students (as impossible as it may seem)

我知道这里有相关的问题,但我看到的只是满足于直接操作SQL而不是Windows Forms和C#的答案。而且我知道学生ID应该是不同的,但我们只是说每个部分都有自己的身份证号码给学生(尽管看起来不太可能)

Anyway, what I want to know is how I can manipulate this in Winforms (not in SQL Server) in such a way that when I press a button in windows form, it will merge both tables together PERMANENTLY in SQL Server. I would prefer if maybe StudentSection2 is combined into StudentSection1, but any workaround is fine.

无论如何,我想知道的是我如何在Winforms(而不是在SQL Server中)中操作它,以便当我在Windows窗体中按下一个按钮时,它将在SQL Server中永久地合并两个表。我更喜欢将StudentSection2组合成StudentSection1,但任何解决方法都没问题。

I'm pretty sure this involves code which contains a connection string etc, but I'm not really sure about this area. Any help would be really useful, thank you very much guys.

我很确定这涉及包含连接字符串等的代码,但我不确定这个区域。任何帮助都非常有用,非常感谢你们。

2 个解决方案

#1

You can directly use some SQL within your C# code that executes on the server, or you can use Linq To SQL, Linq To EF which in turn would write the SQL for you behind the scenes. Thinking about efficiency, doing this with some SQL would be the desired way. Let's say you want that result in a new table called AllSections, your code would look like:

您可以直接在服务器上执行的C#代码中使用一些SQL,也可以使用Linq To SQL,Linq To EF,后者将在后台为您编写SQL。考虑效率,使用某些SQL执行此操作将是理想的方法。假设您希望将结果放在名为AllSections的新表中,您的代码将如下所示:

using(var con = new SqlConnection(@"server=.\SQLExpress;Trusted_Connection=yes;Database=myDatabase"))
{
    string sql = @"Select * into AllSections
    from (
        select * from StudentSection1
        union all
        select * from StudentSection2
    ) tmp;";

    con.Open();
    new SqlCommand(sql, con).ExecuteNonQuery();
    con.Close();
}

#2

In order to execute an SQL in C#, you will need to know about SqlConnection. It take an connection string in a constructor which is basically a string telling how you will connect to the database.

要在C#中执行SQL,您需要了解SqlConnection。它在构造函数中使用连接字符串,它基本上是一个字符串,告诉您如何连接到数据库。

For example

string connectionString="Data Source=.;Initial Catalog=mydb;Integrated Security=True";

Data Source tell you where your database server is located.

数据源告诉您数据库服务器的位置。

Initial Catalog is the name of the database you want to connect to and the last part is the security component to connect the database server.

Initial Catalog是要连接的数据库的名称,最后一部分是连接数据库服务器的安全组件。

After you have the created your SqlConnection object , you can use SqlCommand which allow you to execute your SQL command.

创建SqlConnection对象后,可以使用允许执行SQL命令的SqlCommand。

Do not forget to close your SqlConnection object after you have finish using it.

完成使用后,不要忘记关闭SqlConnection对象。

#1

using(var con = new SqlConnection(@"server=.\SQLExpress;Trusted_Connection=yes;Database=myDatabase"))
{
    string sql = @"Select * into AllSections
    from (
        select * from StudentSection1
        union all
        select * from StudentSection2
    ) tmp;";

    con.Open();
    new SqlCommand(sql, con).ExecuteNonQuery();
    con.Close();
}

#2