Update & Insert in Database very slow

Dennis Reyes 0
Viewed 69

I have 2 databases: Database1 & Database2, I set it on the button. When I click the button I will update and insert at the same time.

In Database2 I want to INSERT a record. In Database1 I want to UPDATE a record.

Here's my code:

Database2

connection1.Open();
OleDbCommand cmd = connection1.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into TestTable2 values ('" + textBox1.Text + "','" + comboBox3.Text + "','" + textBox3.Text + "_" + comboBox2.Text + "_" + textBox4.Text.Replace("/", "") + ".pdf" + "','" + comboBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + newPath + textBox3.Text + "_" + comboBox2.Text + "_" + textBox4.Text.Replace("/", "") + ".pdf" + "','" + comboBox1.Text + "','" + textBox33.Text + "','" + textBox45.Text + "','" + textBox8.Text + "','" + textBox9.Text + "','" + textBox7.Text + "')";
cmd.ExecuteNonQuery();
connection1.Close();

Database1

connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "update TestTable1 set Status='" + "Done" + "' where Directory='" + textBox2.Text + "'";
command.CommandText = query;
command.ExecuteNonQuery();
connection.Close();

My problem is when doing update and insert it's very slow. it keeps loading at least 12 to 18 secs. I am using MS Access.

ms-access-2010 c# sql
1 Answers

#1 Fix the possible sql injection issue...

Status should have an index...but probably does not because I'm assuming that the directory can be a long string that includes the path.

Maybe create a table for directory that has a primary key and then create a foreign key back to the directories table. Then update using the key (which should be indexed)

userIPa10
Related