低速ADO.NET [英] low speed ADO.NET

问题描述

我试图用我的C#代码连接到Access DB，但速度很慢
有谁知道为什么它这么慢?

I have tried to connect to an Access DB in my C# code but it is very slow
Does anyone have any idea about why it is so slow?

private void SaveButton_Click(object sender, RoutedEventArgs e)
        {
           //update the DataBase with the TeacherName and Description Columns
            try
            {
                //create a connnection
                //OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\users\\hosein\\db1.mdb");
                OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\\db\\db1.mdb");
                aConnection.Open(); //open the connection
                //create an adapter using a sql command text and a connection that is open
                OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM ComputerClasses", aConnection);
                #region check for compatibility between rows exist in the table and the rows we need
                adapter.SelectCommand = new OleDbCommand();
                adapter.SelectCommand.Connection = aConnection;
                adapter.SelectCommand.CommandText = "SELECT Count(ComputerClasses.RowNumber) AS CountOfRowNumber FROM ComputerClasses";
                byte a = Convert.ToByte(adapter.SelectCommand.ExecuteScalar()); // return the first row frist column element in the table that is returned
                //a contain the number of rows in the ComputerClass table
                adapter.InsertCommand = new OleDbCommand ();
                adapter.InsertCommand.Connection = aConnection;

                if (a < Settings.TotalClasses)
                {
                    adapter.SelectCommand.CommandText = "SELECT Max(ComputerClasses.RowNumber) AS MaxOfRowNumber FROM ComputerClasses";
                    byte b = Convert.ToByte(adapter.SelectCommand.ExecuteScalar()); // now b contain the maximum number in the RowNumber column in the table
                    // we use it to have the ascend form in the RowNumber column and it is essential because we use the order in the update phase
                    for (byte l = 0; l < (Settings.TotalClasses - a); l++)
                    {
                        adapter.InsertCommand.CommandText = string.Format("INSERT INTO ComputerClasses (RowNumber, TeacherName, Description )VALUES ({0},\"Teacher Name\", \"Description\")", ++b);
                        adapter.InsertCommand.ExecuteNonQuery();
                    }
                }
                #endregion
                // creating an update command
                adapter.UpdateCommand = new OleDbCommand();
                //allocating the connection to the comamand
                adapter.UpdateCommand.Connection = aConnection;
                for (byte i = 0; i < Settings.TotalClasses; i++) //iterating to each row and update each row with the database rows
                {
                    // creating the command text (SQL SYNTAX)
                    adapter.UpdateCommand.CommandText = "UPDATE ComputerClasses SET TeacherName = '" + RowArray[i].teacherName.Text.ToString() +
                        "', Description = '" + RowArray[i].description.Text.ToString() + "' WHERE RowNumber = " + (i + 1).ToString();
                    // tell to database to execute the command that is generated in the previous line
                    adapter.UpdateCommand.ExecuteNonQuery();
                }
                aConnection.Close(); //close the connection
                MessageBox.Show("Successfully Updated"); // show a message say to user that the operation is done successfully
            }
            catch
            {
                MessageBox.Show("Error Code 1, Connecting to DataBase"); //if any error occur during the operation to database
                // the try block prevent the system crash and just a message windows apear and say to user what happend with an
                // error code
            }
        }

推荐答案

不知道更多，这个问题无法回答.

慢"的定义是什么?您是否有将其与其他系统进行比较的指标.数据库中有多少行?有多少人被退回?表的结构如何等

您的代码编写得也很差.您要在构造函数中的OleDbDataAdapter上设置select语句，然后立即覆盖它.
最好执行select语句以分别获取计数，而不需要DataAdapter.
您还可以使用DataTable，相应地插入或更新行，然后使用DataAdapter.Update一次执行所有插入和更新，而不是每次都调用ExecuteNonQuery.

http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspx
从DataTable中为指定DataSet中的每个插入，更新或删除的行调用相应的INSERT，UPDATE或DELETE语句.
如果您阅读了文档，将看到不需要每次更新或插入都调用ExecuteNonQuery.

Without know much more this question can''t be answered.

What is definition of "slow"? Do you have metrics comparing this to other systems. How many rows are in the database? How many are being returned? How are your tables structured, etc.

Your code is also very poorly written. You are setting the select statement on the OleDbDataAdapter in the constructor then immediately overwriting it.
It would be better to execute the select statements to get the count separately, no DataAdapter necessary.
You can also use a DataTable, insert or update rows accordingly, then use DataAdapter.Update to execute all the inserts and updates at once rather than calling ExecuteNonQuery each time.

http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspx
Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable

If you read the documentation you will see calling ExecuteNonQuery for each update or insert is not necessary.

这篇关于低速ADO.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！