使用DataSet，TableAdapter和DataTables将相关数据插入2个表中 [英] Inserting related data into 2 tables using DataSet, TableAdapter and DataTables

问题描述

我在MySQL数据库中有2个表，并结合了1:n关系.表1具有一个自动递增的id列.

I have 2 tables in a MySQL Database, combined with an 1:n relationship. Table1 has an auto incrementing id column.

我的目标是在table1中插入一行，并在table2中插入任意数量的行. table2中的行应链接到table1中的行.表2应该绑定到DataGrid以便于插入.为了尽量减少我想将DataSet与TableAdapters一起使用的工作， DataTables和DataViews(因此不需要手动查询编写).

My goal is to insert a row in table1 and as many rows as I want in table2. The rows in table2 should be linked to the one in table1. Table2 should be bound to a DataGrid for easy inserting. To minimize the work I'd like to use a DataSet with TableAdapters, DataTables and DataViews (so no manual query-writing is needed).

我已经尝试了几种方法，但到目前为止还没有真正起作用.目前，我正在尝试在table1中插入一行，以某种方式获取ID，并将此ID绑定到与table1关联的table2中的列.但是我无法从行中获取ID.插入行 在table1中，我将UI元素绑定到新的DataRowView上，如下所示:

I've tried several approaches, but nothing really worked so far. At the moment I'm trying to insert a row in table1, get the id somehow and bind this id to the column in table2 associated with table1. But I cant get the id from the row. To insert the row in table1 I bind the UI-elements to a new DataRowView like this:

ClientManagerDataSet.CompanyDataTable companyDataTable = DataSetManager.CompanyTA.GetData();
companyDataRowView = companyDataTable.DefaultView.AddNew();
UiGrid.DataContext = companyDataRowView;
companyDataRowView.BeginEdit();

ClientManagerDataSet.CompanyDataTable companyDataTable = DataSetManager.CompanyTA.GetData();
companyDataRowView = companyDataTable.DefaultView.AddNew();
UiGrid.DataContext = companyDataRowView;
companyDataRowView.BeginEdit();

DataSetManager.CompanyTA//这是CompanyTableAdapter类型的静态变量

DataSetManager.CompanyTA //this is a static variable of the type CompanyTableAdapter

如果您能告诉我插入后如何获取ID或如何使DataSet完成工作，那就太好了.我已经在这个问题上呆了10个小时了，我真的不知道该怎么办.

It would be so great if you could tell me how to get the id after inserting or how to get the DataSet to do the work. I'm stuck with this problem for 10 hours now and I really don't know what I should do.

非常感谢！

推荐答案

>>如果您能告诉我插入后如何获取ID的话，那就太好了

>>It would be so great if you could tell me how to get the id after inserting

我们可以这样获得列值:

We can get column value like this:

DataTable dt = new DataTable();
dt.Rows[index]["ColumnName"]

我创建了一个示例，展示了如何从刚刚插入到DataTable中的行中获取ID:

I create a sample to show how to get the id from a row which just be inserted into DataTable:

XAML:

<Window.Resources>
        <CollectionViewSource x:Key="cvs" Source="{Binding}" />
    </Window.Resources>
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition />
            <RowDefinition Height="50" />
        </Grid.RowDefinitions>
        <DataGrid Name="dg"  ItemsSource="{Binding Source={StaticResource cvs}}" Grid.Row="0" />
        <StackPanel Grid.Row="1" Orientation="Horizontal" >
            <Button Content="Add Row" Width="100" Margin="50,10,0,10" Click="AddButton_Click"  />
            <Button Content="Get Row"  Width="100" Click="GetButton_Click" Margin="210,10,0,10"/>
        </StackPanel>
    </Grid>

背后的代码:

DataTable dt = new DataTable();

public MainWindow()
{
            InitializeComponent();
            
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Age", typeof(int));
            
            dt.Rows.Add(new object[3] { 1, "Mary", 22 });
            dt.Rows.Add(new object[3] { 2, "Peter", 24 });
            dt.Rows.Add(new object[3] { 3, "Rose", 17 });
            dt.Rows.Add(new object[3] { 4, "John", 19 });
            dt.Rows.Add(new object[3] { 5, "Steven", 20 });
            dt.Rows.Add(new object[3] { 6, "Tom", 20 });
            this.DataContext = dt.AsDataView();
}

private void GetButton_Click(object sender, RoutedEventArgs e)
{
            DataRowView row = (DataRowView)dg.SelectedItems[0];
            MessageBox.Show(row.Row["ID"].ToString());
}

private void AddButton_Click(object sender, RoutedEventArgs e)
{
            dt.Rows.Add(new object[3] { dt.Rows.Count+1, "Person", 20 });
            MessageBox.Show(dt.Rows[dt.Rows.Count-1]["ID"].ToString());
}

截屏:

如您所见，添加新行后，将弹出ID值为ID的消息框.

As you see, after adding a new row, the messagebox with ID value will pop up.

请检查以下参考文献:

#WPF SelectedData在两个数据网格之间的绑定
> http://www.codeproject.com/Questions/541721/WPFplusSelectedItemplusbindingplusbetweenplustwopl

这篇关于使用DataSet，TableAdapter和DataTables将相关数据插入2个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！