|
|
I need to know how many records there are in the datareader, is the some way ?
...
...
Dim conexao As New SqlConnection("Database=xonline; Server=xsamcpd05x; user id=ox_sadmilson; pwd=cchinatown")
'Dim conexao As New SqlConnection("Database=online; Server=samcpd05; user id=onlineteste; pwd=onlineteste")
Dim query As String = "select prod_date,unit_id FROM rip_producao withwhere prod_date>=" & ts_init.Text & " and prod_date<=" & ts_end.Text & ""
Dim comando As New SqlCommand(query, conexao)
Dim dr As SqlDataReader
conexao.Open()
dr = comando.ExecuteReader()
...
...
|
|
|
|
|
sadmilson wrote:
I need to know how many records there are in the datareader, is the some way ?
You cannot find this out in advance. You would need to read through all the rows first.
One possible solution, if you need to know the answer in advance of reading the rows is to sent two SQL Statements into the SqlCommand object. The first would be a SELECT COUNT(*) FROM.... Then you would have two result sets in the Data Reader, the first result set would contain the number of rows in the second result set.
Also, I point this out any time I see a potential security concern with code, your code is susceptible to a SQL Injection Attack. You may wish to read this article[^] to find out what that means and how to mitigate the risks.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks Colin,
The problem is that I need to make two selects, each select returns about 2 milions records, it take some time to return, I beleave that your solution is good, i will try.
Thanks my friend
|
|
|
|
|
Just do one SELECT statement but return a DataView instead of SqlDataReader so you can do
DataView pData.Count
Cheers
Al
|
|
|
|
|
|
No I meant DataView that also user SqlDataReader!
Al
|
|
|
|
|
|
Why top make 2 requests into the database when you can just get the container DataView with properties like DataView.Count? So you don't make 2 requests, and yes you put it into memory. GC will take care of calling Dispose or you can do it too.
Al
|
|
|
|
|
Albert Pascual wrote:
Why top make 2 requests into the database when you can just get the container DataView with properties like DataView.Count?
I wasn't advocating 2 requests. I was suggesting one request with two SQL Statements in it. Something like this:
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM MyTable; SELECT col1, col2, etc FROM MyTable");
Albert Pascual wrote:
GC will take care of calling Dispose or you can do it too.
Why make two copies in the first place. If it is a large enough set of data that's going to hit memory fairly hard.
[EDIT]
Also, if the code is already written for using a DataReader, why make more modifications than is necessary.
[/EDIT]
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I thought that was 2 requests still! coming back in one SqlDataReader, however SQL still has to make every request between ; character
Al
|
|
|
|
|
Albert Pascual wrote:
I thought that was 2 requests still!
Nope. One outward request (with two commands in it). One lump of data back. On its return the DataReader splits the lump in to two ResultSets.
Albert Pascual wrote:
however SQL still has to make every request between ; character
Nope, it just sends out the string as given. The SQL Parser on the server will sort it out. It could be further optimised by adding a BEGIN TRANSACTION at the start and an COMIT TRANSACTION at the end, but that optimisation would be on the server end and not really affect communications.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hello everybody,
I have wrote a database application that connects via ODBC to MS Access databases. After running many SQL queries on the databases, their size grows considerably (a 320kb file can grow to MB sizes). If I run the 'Repair and compress'-operation in Access, the databases shrink back to their expected size.
I tried switching from ODBC to OLEDB, but the problem remained unsolved...
Has anyone had a similar problem with Access databases?
I'd really appreciate if you could help me solve this problem.
Thanks,
paul
|
|
|
|
|
rocas wrote:
Has anyone had a similar problem with Access databases?
Sounds like normal database behaviour to me.
As you perform actions on a database it creates transactions so that the database will be in a consistent state should anything go wrong. These transactions will be appended to the end of the file until you clear out the transaction log, in this case by running "Repair and Compress".
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I had no problems in accessing information via Access Database using VB.Net. I created several datagrid programs accessing the MS Access database without an exception. I am just having problems in authenticating a login page that I am trying to use to open a secured program, that continues to point to the ExecuteReader being closed.
bravo659
|
|
|
|
|
I thought of the same explanation.
But how can I solve this problem. Is there an option for the connectstring that I can use to disable the log, or a method to programmatically call "Repair and Compress"?
|
|
|
|
|
Can we Pass the Array to the Procedure as a Parameter so as to insert multiple records at a time
Ex. We collected some 15 product when we insert thease in one click in one transaction table by Product(15)
Mail me
dhakane@gmail.com
Jeetendra Dhakane
|
|
|
|
|
What in the world does "AFAIK" mean!!
|
|
|
|
|
|
it is an acronym for "As Far As I Know"
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Thanks! That makes sense. Every time I came across this acronym, I would think "hmm, there goes Microsoft again...".
TYFYH (Thank You For Your Help).
|
|
|
|
|
|
Colin Angus Mackay wrote:
You didn't get my little joke
lol...no I did not...
now I do...
As far as I know (AFAIK) this question has nothing to do with databases, SQL or ADO.NET
I honestly believed it was one of the feature within .NET (hence - the reference to microsoft) that I did not know about! Boy, do I feel dumb.
|
|
|
|
|
|
How do I rename a table in SQL Server 2000? I mean by script, I can do it easily enough in the Enterprise Manager.
Cheers,
Andy
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
