如何将我对DataTable所做的更改提交给我从中抓取的表？ [英] How do I commit the changes I have made to a DataTable to the Table from which I grabbed it?

问题描述

我正在使用一个极简主义的数据库接口，它从所有相关表中获取所有相关信息，并在本地的数据表中存储该信息，以便根据需要修改，更改和更新信息，而不需要维护

I'm working on a minimalist database interface that grabs all relevant information from all relevant tables and stores that information in data tables locally so that the information can be modified, changed, updated, and what not, as needed without maintaining a constant connection to the database.

这个方法运行得很好，所有的变化都反映并存储在表中，非常棒。

The method works perfectly, all changes are reflected and stored in the table, and that's great.

现在我需要知道如何将数据表中本地的变化反映到我从中抓取表的数据库中。

Now I need to know how to go about reflecting the changes made locally in the data tables to the database that I grabbed the tables from.

在我看来我说你需要维护一个连接来做，但因为它可能需要一些时间来实现对数据库的更改，我听说了最好的做法是只建立一个连接，做你需要的，然后出去，我在这里看到一种冲突。

Everything I've read seems to me to say "You need to maintain a connection to do that" but because it may take some amount of time to enact the changes to the datatables, and I've heard it's best practice to just establish a connection, do what you need to, and then get out, I'm seeing a kind of conflict here.

我引用的文章是这一个和这一个。

这是获取表的代码：

public static DataTable GetTable( string Table ) {
    string Query = "SELECT * FROM " + Table;
    return SQLLib.GetDataTable( Query, null );
}

private static DataTable GetDataTable( string CMD, object[] Params ) {
    DataSet DS = new DataSet( );
    using ( MySqlConnection MSQCon = new MySqlConnection( SQLLib.sqlconstr ) ) {
        try { MSQCon.Open( ); } catch ( MySqlException ) {
            Console.WriteLine( "Failed to open SQL Connection" );
        }
        MySqlDataAdapter MSQDA = new MySqlDataAdapter( );
        MySqlCommand MSQCom = new MySqlCommand( CMD, MSQCon );

        if (Params != null) for ( int c = 0; c < Params.Length; c++ )
            MSQCom.Parameters.AddWithValue( "@param_val_" + ( c + 1 ), Params[c] );
        MSQCom.CommandType = CommandType.Text;
        MSQDA.SelectCommand = MSQCom;
        MSQDA.Fill( DS );
        MSQCon.Close( );
    } try { return DS.Tables[0];
    } catch ( IndexOutOfRangeException ) { return SQLLib.GetDataTable( CMD, Params ); }
}

现在，有一个方法可以更新源表格与DataTable我保持本地，直到准备提交？

So, now, is there a method through which I can update the source table with the DataTable I keep locally until it's ready to be committed?

推荐答案

这不是关于保持连接打开，你只是必须使用Command Builder，它与MySql相同。

It is not about keeping the connection open, you just have to use the Command Builder, it's the same with MySql I believe.

private MySqlDataAdapter adapt;
private DataSet someDataSet;
someDataSet = new DataSet();

    public DataSet GetCustomerData(int customerId)
    {
        using(MySqlConnection connect = new MySqlConnection(ConnString))
        {
            connect.Open();
            MySqlCommand comm = new MySqlCommand("SELECT * FROM customers WHERE Id = @0", connect);
            someDataSet.Tables.Add("CustomersTable");
            comm.Parameters.AddWithValue("@0", customerId);
            adapt.SelectCommand = comm;
            adapt.Fill(someDataSet.Tables["CustomersTable"]);
        }

        return someDataSet;
   }

现在更新：
您可以使用一个新的适配器好了，但是你必须给它一个select命令，基于命令建立者将使用Insert，Update和Delete命令。

Now for the updating: you could use a new adapter as well, but then you have to give it a select command, based on that the commandbuilder will make the Insert,Update and Delete commands.

    public void UpdateTable(DataTable table, int customerId)
    {
        using (MySqlConnection connect = new MySqlConnection(ConnString))
        {
            connect.Open();
            MySqlCommandBuilder commbuilder = new MySqlCommandBuilder(adapt);
            adapt.SelectCommand = new MySqlCommand("SELECT * FROM customers WHERE Id = "+customerId, connect); //or use parameters.addwithvalue
            adapt.Update(table);
        }
    }

这篇关于如何将我对DataTable所做的更改提交给我从中抓取的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！