|
Sorry if title bit abrupt. I have an Access Database, in this database are two tables, one is a table of teams and the other a table of players. My table of teams contains 11 index numbers (1 for each player).
My program currently loads all the teams from the database, while loading each teams, it search's the other table for the players details using the index number.
I know this will query the database 11 times for each team. This seems to really slow down the program to an unmanagable speed.
What is the best and fastest way to talk to an access database and am i going about it the right way? A sample of how I am currently querying the database is below. Thanks
string strQuery = "SELECT * FROM t_DT_Teams;<br />
OleDbCommand dbsCommand = new OleDbCommand(strQuery, dbsConnection); <br />
OleDbDataAdapter dbsAdapter = new OleDbDataAdapter();<br />
dbsAdapter.SelectCommand = dbsCommand;<br />
<br />
DataSet dbsDataSet = new DataSet();<br />
dbsAdapter.Fill(dbsDataSet, "t_DT_Teams");<br />
DataTable dbsDataTable = dbsDataSet.Tables["t_DT_Teams"];<br />
<br />
// i then loop through each team and do roughly the same to find a player<br />
<br />
dbsConnection.Close
|
|
|
|
|
Instead of using * you should try to list the column names directly. I can't tell you what the exact performance hit is, but I know you should always try to list the columns you want.
I don't know if there is any better way to do this then how you are currently doing it. Do you use the dataset, you can set the datatable in place of the dataset if you don't need it. Also, try using the using(connectionType connection = new connetiontype(connectionstring){}. You don't have to close your connection when you are done as this will do it for you.
Wish I could help More.
|
|
|
|
|
avanwieren wrote: You don't have to close your connection when you are done as this will do it for you.
  Very bad advice. You should always close the connection because it immediately frees up the resources on the database so it can be accessed by another application, or even a different part of the same application.
If you don't call close you are at the mercy of the garbage collector as to when the connection is finally closed. Each time you open a connection you'll need a new one as the previous one isn't closed yet. This might cause the application to fail if the database is too stretched to open a new connection. The cause won't be immediately apparent because the cause of the error isn't at the point the connection is opened, it is at the point the connection should have been closed, but wasn't.
|
|
|
|
|
Actually,
when you hit the final end bracket of the using statement,a dispose call is made closing and disposing the connection. Using the using statement does the exact same thing as opening and then closing a connection manually, it just is way easier to read. You should also do the same thing with the command object as it is also IDisposable type.
using (SqlConnection connection = new SqlConnection(connectionString))
{
Do some thing with the database here!
}
Is the same as
connection.open();
connection.close();
Pardon the berevety.\
Aaron
|
|
|
|
|
avanwieren wrote: when you hit the final end bracket of the using statement
On re-reading your original post, I see that I'd missed that you were suggesing using a using block. Now that you've shown this formatted version is it much easier to understand. I should have been more careful in reading your post. 
|
|
|
|
|
I agree with you on the importance of closing connections and understand the misgivings about what I originally wrote as I was not very clear in some ways. I especially love using the using block as it is much easier to read and really helps separate the code into a more manageable chunk. Where I work our code base was/is in VB6, tended to have alot of open connections that never got closed and had provide numerous nightmares performance wise.
I should have put the using statement into a code bracket, easy to misinterpret.
No biggie.
|
|
|
|
|
I would use column names instead of the *, but there are only 5 fields and I need them all.
On the connection thing, i tried to be clever by opening the connection at the begining and using the same connection to do everything, I then close it at the end when I have finished.
Surely c# talking to an access database can't be this slow! VB6 was much faster than this and is putting c# to shame at the moment. It is currently taking between 30-60 seconds do get info from a database in c# where vb6 seemed to do it instantly.
|
|
|
|
|
Do you need a dataset or will a datatable work? IHO datasets tend to be overkill when all you need is a single datatable. The slow down could also be from the fact that vb6 is unmanaged code and does not require compilation into MSIL where as the .Net(not just c#) tends to run slower initially because it is managed code. You could create a test harness and run this process a couple of times to see if it is just initially slower.
Without knowing too much of your code, but from the description the other thing I would try would be to get all the tables you need, close the connection, then do your select statement on the datatable for what you want. You then can work with just the set of data at that time that is selected by the select statement.
Just some thoughts.
|
|
|
|
|
Thanks for your reply  .
No I dont need a dataset, I just thought that is what I needed to do to get it to work. Didnt know you could just use a datatable, although I have just tried this and it didnt help with the speed.
The idea of loading everything then querying sounds good, but how do you query a DataTable?  I've never done that before.
|
|
|
|
|
Another question. Are you just reading the data from the database or are you altering the data? If you are just reading the data you could use the datareader which is much faster but only processes in a forward direction.
In regards to the select statement it is a method of the DataTable. The select statement takes an expression that is like value=1. Your code would look somehting like this:
DataTable dbsDataTable_= new DataTable("DT_Teams");
string strQuery = "SELECT * FROM t_DT_Teams;
string connectionstring = "YourConnectionString";
using(OleDbConnection connection = new OleDbConnection(connectionString)
{
OleDbCommand dbsCommand = connection.CreateCommand();
dbsCommand.CommandText = strQuery;
OleDbDataAdapter dbsAdapter = new OleDbDataAdapter(dbsCommand);
dbsAdapter.Fill(dbsDataTable);
}
From here you can work with the datatable and query the results:
DataRow[] result;
string expression= "column (= > <)value)";
result = dbsDataTable.Select(expression);
You then can work with the datarows with a foreach clause to process or retrieve your results. The best part of this is that you only access the database and get the data you want once, then you can select, sort, add columns and rows and compute your data disconnected from the database. The down side is that datatables are stored in memory.
Let me know if this works.
|
|
|
|
|
At the moment I am just reading from the database. I will need to write to it later on but not during this part of the program.
I was originally using a datareader but because i look at the team table and then search the players table, it wouldnt let me open more than one datareader at a time and wanted me to close any existing ones, which I couldnt do because I needed to go back to it.
when you do a select on a datatable, can you specify which column you want to search in? Are the expressions the same as sqlquerys?
|
|
|
|
|
deanpugh wrote:
I was originally using a datareader but because i look at the team table and then search the players table, it wouldnt let me open more than one datareader at a time and wanted me to close any existing ones, which I couldnt do because I needed to go back to it.
There are ways to save the results and work with them, but I am not as familiar with the DataReader as the DataAdapters. It is always good practice to close your connections as soon as possible, this is where the disconnected datatables are really good.
deanpugh wrote: when you do a select on a datatable, can you specify which column you want to search in? Are the expressions the same as sqlquerys?
In most ways yes. The expression is very similar to a where clause.
A good resource on what all can be done:
http://msdn2.microsoft.com/en-us/library/system.data.datatable.select.aspx
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx
These should give you good references regarding the select itself as well as the expression parameters.
Hope this helps.
|
|
|
|
|
Thanks. I will give that a go and see if it solves my problem and will post back when I can.
|
|
|
|
|
Thanks avanwieren that seemed to speed things up alot. I moved what I could into DataTables and DataSets and got my information from there instead of querying the database. I had to move alot of things around and modify a few things, but it seems to have done the trick!
Dont suppose you know how to add Tables to a current Access Database do you?
Thanks again
Dean
|
|
|
|
|
Can anyone help me on how to create a whiteboard application in .Net.
I have to create a whiteboard application and to integrate it further with a web application.
Any suggestion / reference is welcome.
pankaj
|
|
|
|
|
You could first start by explaining what you have tried and what you need help with. This seems as though it is a school project, is it?
only two letters away from being an asset
|
|
|
|
|
I did a chart app.
in the chart I wanted to put in every point
a label that shows text
I made an ArrayList and put the Label control in there
when I run the app. I saw only one label .
p.s. I did the same for Point objects and I didn't has any problem- I saw all the points.
|
|
|
|
|
Hi,
Please, submit your code (when you create the array and the labels) because without it is very difficult make a suggestion...
|
|
|
|
|
ArrayList al = new ArrayList();
Label mylabel =new Label();
al.Add(mylabel);
al.Add(mylabel);
// now I have two objects in the array...
((Label)al[0]).Location = new Point(100,100);
((Label)al[1]).Location = new Point(100,200);
// that's it
when the program run i see only one label.
why?
|
|
|
|
|
Hi,
The problem is that you're are adding two times the same isntance of label...
Try to create a new label when you're adding it to the arraylist. Try this:
ArrayList al = new ArrayList();<br />
for( int i = 0; i< 10; i++)<br />
al.Add(new Label());<br />
((Label)al[0]).Location = new Point(100,100);<br />
((Label)al[1]).Location = new Point(100,200);<br />
...<br />
((Label)al[9]).Location = new Point(100,200);
Too, You can create an array of labels instead of an arraylist...
|
|
|
|
|
did that
still
i don't see my labels on the form
maybe I need to use:
controls.add ?
|
|
|
|
|
Yes. You're right. The code is the following above:
ArrayList al = new ArrayList();<br />
for( int i = 0; i< 10; i++)<br />
{<br />
Label oLabel = new Label();<br />
al.Add(new Label());<br />
this.Controls.Add(oLabel);<br />
}<br />
((Label)al[0]).Location = new Point(100,100);<br />
((Label)al[1]).Location = new Point(100,200);<br />
...<br />
((Label)al[9]).Location = new Point(100,200);
|
|
|
|
|
From PC1 I am trying to connect to a webservice on my machine.
This path works fine on my machine.
http://gbws-00201234/codeservice/inger.asmx
It lets me see all the functions.
But when browsing from any other machine on the network, it gives an error:
The page can not be displayed. page is unavailable...
On my machine I have got firewall turned off.
Other pcs can ping my machine and vice versa.
Any ideas please?
Thanks
|
|
|
|
|
Sir/Madam
I am Final Year Engineering student. I want to get information about connection of computers at different cites.
Actually if I want to computerize a department of any company which have its offices at several cities then what steps I should follow.
Means how I can connect that no of computers in appropriate manner to get desired output.
I want to make it just like railway reservation system through in which if I enter data at same time then database must be able to prioritize data in sequence to enter in database.
Which database & platform or language I should use to code this kind of project.
Thank You
Ashish Porwal
|
|
|
|
|
I to all,
is it possible to create a c# applicatiom and put it in a browser like an activeX to install??
any examples or resource?
thanks a lot
Teo
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
