如何在C#中复制数据库表？

Question

请参阅此代码：

switch(someString)
{
case "11":  
    Con1_1.ConnectionString = conStr;
    Con1_1.Open();
    Dapt1_1 = new SqlDataAdapter("SELECT * From StdList", Con1_1);
    Dapt1_1.Fill(Dset1_1, "StdList");
    count = Dset1_1.Tables["StdList"].Rows.Count - 1;
    break;
case "12":
    Con1_2.ConnectionString = conStr;
    Con1_2.Open();
    Dapt1_2 = new SqlDataAdapter("SELECT * From StdList", Con1_2);
    Dapt1_2.Fill(Dset1_2, "StdList");
    count = Dset1_2.Tables["StdList"].Rows.Count - 1;
    break;
}

我想使用一个数据库表，可以复制2个案例。请帮我做到这一点。数据库中的一个表只能用于两个或更多个案例吗？或者我必须定义我所需要的尽可能多的表？

Answer 1

如果我理解正确的话，您是否希望在someString为'11‘时从一个表中获取一组记录，而如果someString的值为'12’，则从另一个表中获取一组不同的记录？

如果两个表中记录的结构相同，您只需在第一个表结构中添加一个字段(例如'SchoolClass‘)以区分记录，并在每条记录的列中存储适当的someString值。然后，您只需要一个表-将所有记录放入该表中。要访问它，请执行以下操作：

Con1_1.ConnectionString = conStr;
Con1_1.Open();
Dapt1_1 = new SqlDataAdapter("SELECT * From StdList WHERE SchoolClass = '" + someString + "'", Con1_1);
Dapt1_1.Fill(Dset1_1, "StdList");
count = Dset1_1.Tables["StdList"].Rows.Count - 1;

Answer 2

好了，我找到你了。

在表中再添加两列，分别用于班级和学校。然后，您可以按班级和学校进行过滤。但所有详细信息将存储在一个表中

Con1_1.ConnectionString = conStr;
Con1_1.Open();
Dapt1_1 = new SqlDataAdapter("SELECT * From StdList WHERE class= '" + m_class+ "' AND school='"+m_school+"'", Con1_1);

Dapt1_1.Fill(Dset1_1, "StdList");
count = Dset1_1.Tables["StdList"].Rows.Count - 1;

Answer 3

您的模式建议和代码意义不大。

我建议您使用类似如下的模式：

#Students
. Id
. ClassId
. Name
. Family
. Phone
. Etc...

#Classes
. Id
. ClassName
. More info you might want about a class...

然后使用以下命令构建style中的方法：

public DataTable GetStudentsByClass(int classId)
{
    //Set the connection string to the database and open the connection
    Con1_1.ConnectionString = conStr;
    Con1_1.Open();

    //Execute the SQL query
    databaseAdapter = new SqlDataAdapter("SELECT * From Students WHERE ClassId = " + classId, Con1_1);

    //Declare a datatable to fill
    DataTable students;

    //Fill the datatable with the results
    databaseAdapter.Fill(students, "Students");

    //Return the filled datatable
    return students;
}

public int GetStudentsInClassCount(int classId)
{
    //Here you can reuse the previous method to get the count, a more optimal way would be to use the SQL count though.
    return GetStudentsByClass(classId).Rows.Count;
}

要使用它，只需这样做：

DataTable students = GetStudentsByClass(1);