Click here to Skip to main content
15,887,135 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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?
C#
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
            }
        }
Posted
Updated 29-Aug-10 0:21am
v3

1 solution

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.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900