|
Anmar_Code wrote: i need to figure out, is Open/Query/Close connections worse than
Open/alot of queries/close. i think yes, but i need a professional opinion.
No, it is not worse - it is the recommended design. (Aquire connection, execute query, release connection)
[ADDITIONAL]
The main thing that reduces performance is the round trips to the server, not the opening and closing of connections. You should reduce the number of queries you have to make.
[/ADDITIONAL]
Now, if you have a method that performs two queries one after another then keep the connection open a little longer to make the second query - if you can you can even put two SQL Queries into on SqlCommand object (just separate them with semi-colons) or even better use stored procedures.
However, if you are calling 400 queries for one action then I really do recommend that you look at what you are doing and seeing if they can be done in less queries. Stored Procedure can help becuase they can do many things at once (including conditional stuff, so you INSERT in to table A, and if some value is this then INSERT also in Table B, but if not then UPDATE table C, etc.)
It looks to me if you are doing stuff like
func2(a,b)
func3(b,c)
func4(a,b,c,d,e)
And all those methods do is run SQL Queries then you could rationalise them into Stored Procedures.
|
|
|
|
|
Thank u for ur information
i will trace the queries and see what i can do...
i might however, ask some other questions although they might sound a little silly...
Thanks alot...
Anmar
|
|
|
|
|
You may also want to look at the Data Application blocks for your connection to the database which gives you an instant DAL (Data Application Layer), some people link them some people don't
Search the MS site for "Data Application Block" under patterns and practices.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
I've a DataSet with lots of DataTableAdapters which all work fine, but i'm getting a very strange behavior when merging two unchanged DataSets:
pseudo code:
MyDataSet ds1 = new MyDataSet();
new MyTableAdapter().Fill(ds1.MyTable);
MyDataSet ds2 = new MyDataSet();
new MyTableAdapter().Fill(ds2.MyTable);
ds1.Merge(ds2, true, MissingSchemaAction.Error) // Need to preserve changes because there might be some, but not in this test case.
The Select statement isn't anything special either: 'SELECT Column1, Column2, ... FROM MyTable' and the Database entries are garanted to be unchanges between queries.
Problem:
After merging those two MyDataSets all rows of MyTable are marked as changed, but really aren't (i'm absolutly positive about that).
On the other hand modifying one row in ds1.MyTable before merging results in one changed entry afterwards, which is correct...
I'm disecting this for hours now, but can't find a solution for it.
Funny that nobody else does seem to have such problems, all articles and forum entries i've found releate to changes NOT reflected within the DataTable, which works perfectly well in my case...
Does anyone have an idea or suggestions on how this problem came to be?
Thank's in advance!
-- modified at 11:40 Wednesday 6th December, 2006
Variables won't; constants aren't. (Osborne's Law)
|
|
|
|
|
Who can give me some advice about the following:
I have to make a table for keeping information about clients. Because a minority of the clients have two addresses e.g. invoice address and delivering address, i thought that for reason of efficiecy it would be better to put the necessary fields for keeping the address information in a separate table,so i built three tables :
1) tblClients with the fields : ClientID, Name, Client since
2) tblClientAddresses with the fields: ClientId, AddressId, Street, Postcode, City
3) tblAddressTypes with the fields: AddressID, Description.
AddressID = 1 stands for DeleveringAddress , AddressID = 2 stands for invoicing address and 3 stands for delvering address and invoicing address is the same.
There's a one to many relation between tblClients and tblClientAddresses based on the field ClientID.
There's also a one to many relation between tblAddressTypes and tblClientAddresses.
I would like to build a class Clients with the fields :ClientID, Name and Client Since, and within this class two other classes : class ClientAddresses and Addrestypes ( both nested classes)
If all the neccerary fields for keeping the information of the client would be in on table and thus in one single class the the following function would be possible te make and work:
Public shared Function GetClient() As List (of Client)
Dim cnn As SqlConnection = GetConnection()
Dim selectstatement As String = "Select ... From tblClients"
Dim selectCommand As New SqlCommand(Selectstatement, cnn)
cnn.Open()
Dim reader As SqlDataReader = selectCommand.ExecuteReader()
Dim client As New List (Of Client)
Do While Reader.Read
Dim c As new Client
c.ClientId = reader("ClientId").ToString
...
The question is : How can i do the same with this three tables instead of one
in order to fill a list with all the fields out of this three tables?
ODRAP
|
|
|
|
|
There are many ways. You could use a DataSet with two tables and a data relation, or you could loop through the outer list and then read in matching items from the inner list or... You get the idea anyway.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi
I am trying to copy SQL Schema to ORACLE and other RDBMS using C# without using any third party tools. Right now i can able to copy SQL schema to SQL can anybody help me to sort out this problem please very urgent. Also by mistake I posted my question in articles(.NET) tell me how to delete my posted message from the article(I am sorry).
With Regards
Kesavan
kesavan
|
|
|
|
|
NK7 wrote: Also by mistake I posted my question in articles(.NET) tell me how to delete my posted message from the article(I am sorry).
You will see a delete button on each of your posts. Press it.
|
|
|
|
|
Colin Angus Mackay wrote:
You will see a delete button on each of your posts. Press it.
 Could be a candidate for signature material :->
If you try to write that in English, I might be able to understand more than a fraction of it. - Guffa
|
|
|
|
|
PaulC1972 wrote: Could be a candidate for signature material
Go for it! 
|
|
|
|
|
Colin Angus Mackay wrote: You will see a delete button on each of your posts. Press it.
Consider it done
You will see a delete button on each of your posts. Press it. - Colin Angus Mackay
|
|
|
|
|
PaulC1972 wrote: Consider it done
Woo hoo!!! I'm famous! 
|
|
|
|
|
Colin Angus Mackay wrote: Woo hoo!!! I'm famous!
I'd like to help but I don't feel like Googling it for you.
|
|
|
|
|
PaulC1972 wrote: I'd like to help but I don't feel like Googling it for you.
Ah.... Fame is such a fickle thing indeed. I guess that's my 15 minutes over with.
|
|
|
|
|
Colin Angus Mackay wrote: Ah.... Fame is such a fickle thing indeed. I guess that's my 15 minutes over with.
No, you're still famous. The sig is on my laptop with CPHog and the message I just posted is in my faculty office using IE 6 which has an old sig. You'll see more of the other sig when I am using my laptop
I'd like to help but I don't feel like Googling it for you.
|
|
|
|
|
hello guys my main problem is how to copy schema. could you reply to that please
kesavan
|
|
|
|
|
hi,
anyone know how to search the date by "dd/mm/yyyy" in sql query!
my code is :
Select datamonth from datemonthtest where datamonth >= #" & strdatefrom & "# and datamonth <=#" & strdateto "#
and data type i set it to date/time!
The result is not efficient to select the month!
Example: when i select the date is from 01/10/2006 to date 30/10/2006 but all the previous month will be come out! 09/08/2006......
Please help!
|
|
|
|
|
Select datamonth from datemonthtest where datamonth
BETWEEN strdatefrom AND strdateto
only two letters away from being an asset
|
|
|
|
|
charleslau2855 wrote: anyone know how to search the date by "dd/mm/yyyy" in sql query!
select convert(varchar(10), getdate(),101)
"My advice to you is to get married. If you find a good wife, you will be happy; if not, you will become a philosopher." Socrates
|
|
|
|
|
albCode wrote: charleslau2855 wrote:
anyone know how to search the date by "dd/mm/yyyy" in sql query!
select convert(varchar(10), getdate(),101)
That does not "search". It formats.
The original question has a false lead in it by requesting a specific format. Date searches are format free because SQL Server does not store the date in a specific locale.
|
|
|
|
|
Use parameters then you don't need to worry about date formats and it reduces your worries about a SQL Injection attack. Also, if you are going to put a date into a SQL string it is better to use the ISO format which is yyyy-mm-dd becuase it is locale independent.
|
|
|
|
|
You have too many items in your GROUP BY clause. The GROUP BY clause should contain only the non-aggregate items that are also in the SELECT clause. If your GROUP BY clause contains more items then it will appear to be producing duplicates (although it isn't, it is just throwing away the information the distinguishes one row from the other).
|
|
|
|
|
Don't you think it is increadibly rude when someone asks a question, you then answer it and a little while later they delete the original question. That means there is now an orphaned answer to a question no one now knows. This really is NOT helpful to anyone searching the forum for the answer to a similar problem.
I really have to dispair sometimes - I really do. 
|
|
|
|
|
Colin Angus Mackay wrote: I really have to dispair sometimes - I really do.
Keep your chin up Colin...you do a fine job around here (IMO).
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Colin Angus Mackay wrote: you then answer it and a little while later they delete the original question.
That is why I make the attempt to put the question into my reply. Then they can delete it all they want but their question still sits there in my reply :->
You will see a delete button on each of your posts. Press it. - Colin Angus Mackay
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
