Heloo,
A have a program with Microsoft access as database.
On first run if i do a search in my database, the result are returned very fast, but in time after multiple search, the search in slower, and slower.
This is my database class connection:
class MyAccessConnection : DataConnection
{
OleDbConnection Connection;
OleDbCommand Command;
OleDbTransaction OperatieDate;
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataSource + ";Persist Security Info=True;Jet OLEDB:Database Password=" + Password + ";";
public MyAccessConnection()
{
(Command = new OleDbCommand()).Connection = Connection = new OleDbConnection(ConnectionString);
ConnectionDistructor = new System.Timers.Timer(60000);
GC.KeepAlive(ConnectionDistructor);
}
public override void InceputTranzactie()
{
OperatieDate = Connection.BeginTransaction(IsolationLevel.ReadCommitted);
Command.Connection = Connection;
Command.Transaction = OperatieDate;
}
public override void CommitTranzaction()
{
OperatieDate.Commit();
}
public override void RollBackTranzaction()
{
OperatieDate.Rollback();
}
public override void FillDataTable(DataTable Table, bool Decrypt)
{
ConnectionDistructor.Enabled = false;
if (Connection.State == System.Data.ConnectionState.Closed) Connection.Open();
Status.Text = "1";
try
{
Table.Rows.Clear();
Table.Columns.Clear();
new OleDbDataAdapter(Command).Fill(Table);
int StartIndex = 1;
try
{
if (Table.Rows.Count > 0)
Table.Rows[0][0].ToString();
if (Table.Rows.Count > 1)
Table.Rows[1][0].ToString();
}
catch { StartIndex++; }
for (int i = StartIndex; i < Table.Columns.Count; i++) for (int j = 0; j < Table.Rows.Count; j++)
Table.Rows[j][i] = Table.Rows[j][i].ToString();
}
catch (Exception Ex) {
string msg = "Cod eroare: 001. \n\nEroare: \n" + Ex.Message;
DoHandleException(msg);
}
finally { Command.Parameters.Clear(); ConnectionDistructor.Enabled = true; }
}
public override object ExecuteNonQuery()
{
ConnectionDistructor.Enabled = false;
if (Connection.State == System.Data.ConnectionState.Closed) Connection.Open();
Status.Text = "1";
try
{
return Command.ExecuteNonQuery();
}
catch (Exception Ex) { throw Ex; }
finally { Command.Parameters.Clear(); ConnectionDistructor.Enabled = true; }
}
public override string CommandText
{
set
{
Command.CommandText = value;
}
}
public override void AddParameter(string Name, object Value)
{
AddParameter(Name, Value, true);
}
public override void AddParameter(string Name, object Value, bool Encrypt)
{
Command.Parameters.Add(new OleDbParameter(Name, Value));
}
public override void Open()
{
Connection.Open();
}
public override void Close()
{
Connection.Close();
}
}
And my search code:
private void CautareContractClick(object sender, EventArgs e)
{
int NumarContractCautat;
if (int.TryParse(this.txtCautaContract.Text, out NumarContractCautat))
{
Contract contract = InterogariDB.IncarcaContract(NumarContractCautat);
if (contract == null)
{
MessageBox.Show(this, string.Format("Contractul {0} nu a fost gasit.", NumarContractCautat), "Cautare", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
btnContractNou.Visible = true;
btnModifica.Visible = true;
grDateClient.Visible = true;
IncarcaClienti(" WHERE ClientID=" + contract.ClientId.ToString() + "", false);
int rowIndex = -1;
foreach (DataGridViewRow row in dvContracteActive.Rows)
{
if (row.Cells[0].Value.ToString().Equals(contract.ContractId.ToString()))
{
rowIndex = row.Index;
dvContracteActive.FirstDisplayedScrollingRowIndex = rowIndex;
tabContracte.SelectedTab = contracteActive;
dvContracteActive.Rows[rowIndex].Selected = true;
break;
}
}
foreach (DataGridViewRow row in dvContracteInchise.Rows)
{
if (row.Cells[0].Value.ToString().Equals(contract.ContractId.ToString()))
{
rowIndex = row.Index;
dvContracteInchise.FirstDisplayedScrollingRowIndex = rowIndex;
tabContracte.SelectedTab = contracteIncheiate;
dvContracteInchise.Rows[rowIndex].Selected = true;
break;
}
}
}
}
}
internal static Contract IncarcaContract(int contractId)
{
Program.Connection.CommandText = "select * from Contracts where ContractId=@ContractId";
Program.Connection.AddParameter("@ContractId", contractId);
DataTable Table = new DataTable();
Program.Connection.FillDataTable(Table, true);
if (Table.Rows.Count == 0)
{
throw new Exception(string.Format("Contractul {0} nu a fost gasit.", contractId));
}
return new Contract(Table);
}
Where i need to look, haw to make my search from my program to work at the same speed.