System.Data.SQLite

Hello

1st

I use  "this.dataSet1.User_Data_Table.Rows.Add(drow);"   to add 300 rows;

2nd.                                              

    I use such code to write the data to the database:
                    this.Validate();
                    this.userDataTableBindingSource.EndEdit();
                    this.user_Data_TableTableAdapter.Update(this.dataSet1.User_Data_Table);  // --> this line took many time 

How can I solve this problem ?

thx in advance !

How is your table defined?

 CREATE TABLE [User_Data_Table] (
    [c1] integer PRIMARY KEY NOT NULL,
    [c2] text NOT NULL UNIQUE,
    [c3] ntext NOT NULL,
    [c4] integer NOT NULL,
    [c5] integer NOT NULL,
    [c6] integer NOT NULL,
    [c7] integer NOT NULL,
    [c8] integer NOT NULL,
    [c9] integer NOT NULL,
    [c10] integer NOT NULL,
    [c11] integer NOT NULL,
    [c12] integer NOT NULL,
    [c13] integer NOT NULL,
    [c14] integer NOT NULL,
    [c15] integer NOT NULL,
    [c16] integer NOT NULL,
    [c17] integer NOT NULL,
    [c18] integer NOT NULL,
    [c19] integer NOT NULL,
    [c20] integer NOT NULL,
    [c21] integer NOT NULL,
    [c22] integer NOT NULL,
    [c23] text NOT NULL,
    [c24] text NOT NULL,
    [c25] integer NOT NULL,
    [c26] integer NOT NULL,
    [c27] integer NOT NULL,
    [c28] integer NOT NULL,
    [c29] integer NOT NULL,
    [c30] integer NOT NULL,
    [c31] integer NOT NULL,
    [c32] integer NOT NULL,
    [c33] integer NOT NULL,
    [c34] integer NOT NULL,
    [c36] integer NOT NULL,
    [c37] integer NOT NULL,
    [c38] integer NOT NULL,
    [c39] integer NOT NULL,
    [c40] integer NOT NULL,
);

I use vs2005 to auto-generate the DataSet.xsd and DataSet.Designer.cs .

I reviewed the code of  DataSet.Designer.cs , and found that its Update() method doesn't use Transcation , how can I add transcation to DataSet.Designer.cs ? This file was generated by vs2005 and I can't modify its code.

Are you running everything inside a transaction?

Are the fields you're matching your updated rows on indexed? 

I recommend you start a transaction in your code before you call the .Update() method, and then commit it yourself afterwards.

Robert

I don't run everything inside a transaction 

 for ( ... )

{

   // add 300 row in the table

    this.dataSet1.User_Data_Table.Rows.Add(drow); 

}

this.user_Data_TableTableAdapter.Update_Trans(this.dataSet1.User_Data_Table);  // Update_Trans added by myself

btw:

I add a new method using transaction but the speed still slow:

        public virtual int Update_Trans(CXconfig_DataSet.User_Data_TableDataTable dataTable)
        {
            if (((this.Adapter.UpdateCommand.Connection.State & System.Data.ConnectionState.Open)
                        != System.Data.ConnectionState.Open))
            {
                this.Adapter.UpdateCommand.Connection.Open();
            }
            System.Data.SQLite.SQLiteTransaction transaction = this.Adapter.UpdateCommand.Connection.BeginTransaction();
            this.Adapter.UpdateCommand.Transaction = transaction;
            this.Adapter.InsertCommand.Transaction = transaction;
            int ret = this.Adapter.Update(dataTable);
            transaction.Commit();
            return ret;
        }

How slow is it?

 hello all!

Thank u for ur advice first, I have found the cause:

I bind the data to a DataGridView control, and when I execute  "this.user_Data_TableTableAdapter.Update_Trans()" , I guess it will call a callback function to add a new row to the DataGridView ..... If I don't bind the data to the DataGridView, the update  procedure very fast.

 I found this problem , but how can I solve it ? 

This is my code:

 this.UserDataView.DataSource = this.user_Data_TableBindingSource;

 hello all!

I have found an ugly method to fixthis problem:

            this.UserDataView.DataSource = null;
            this.user_Data_TableTableAdapter.Update_Trans(this.userDataSet.User_Data_Table);
            this.UserDataView.DataSource = this.user_Data_TableBindingSource;

I think it mustn't the best method , but I don't know others yet .