VB.NET RSS VB.NET RSS

Program loses response during large DB queries VB.NET

Please support our VB.NET advertiser: Intel Parallel Studio Home
Thread Solved

•
•
Join Date: May 2008
Posts: 15
Reputation: danielgee is an unknown quantity at this point 
Solved Threads: 2
danielgee danielgee is offline Offline
Newbie Poster

Program loses response during large DB queries VB.NET

 
0
  #1
May 4th, 2008
Hi,
When I execute queries which have a lot of data returned in the recordset my VB app seems to hang (IE: This program is not responding)

I'm using ODBC to connect to the database and am using Postgres DB

VB.NET Syntax (Toggle Plain Text) 
  1. Public db_name As String
  2. Public db_username As String
  3. Public db_userpassword As String
  4. Public db_server As String
  5.  
  6. Public connStr As String
  7. Public sqlCommand As OdbcCommand
  8. Public sqlConn As OdbcConnection
  9. Public Rs As OdbcDataReader
  10.  
  11. Public Sub DoQuery(ByVal tmpSQL as String)
  12.     connStr = "Driver={PostgreSQL ANSI};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
  13.  
  14.               sqlCommand = New OdbcCommand
  15.               sqlConn = New OdbcConnection(connStr)
  16.               If sqlConn.State = ConnectionState.Closed Then
  17.                   sqlConn.Open()
  18.               End If
  19.  
  20.               sqlCommand.CommandType = CommandType.Text
  21.               sqlCommand.Connection = sqlConn
  22.               sqlCommand.CommandText = tmpSQL
  23.  
  24.               Rs = sqlCommand.ExecuteReader
  25.  
  26. End Sub

I'm not sure that there is a way to sort this out, I hope there is?

Thanks all!
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2006
Posts: 45
Reputation: Oxiegen is an unknown quantity at this point 
Solved Threads: 3
Oxiegen Oxiegen is offline Offline
Light Poster

Re: Program loses response during large DB queries VB.NET

 
0
  #2
May 6th, 2008
I would try to limit the amount of data for each query.
Using the reader in order to populate some kind of repository do take a long time.
The way the reader works is that it only reads one line of data at a time.
Ie:
VB.NET Syntax (Toggle Plain Text) 
  1. While Rs.Read
  2. If Not IsDBNull(Rs.Item("some_column_name")) Then repository = Rs.Item("some_column_name")
  3. End While
However, if you limit the amount of data to read with a WHERE clause this will go faster.

Or, you could use a dataadapter/dataset and basically get a mirror copy of the data.
Then you can search and select from there.
VB.NET Syntax (Toggle Plain Text) 
  1. Public db_name As String
  2. Public db_username As String
  3. Public db_userpassword As String
  4. Public db_server As String
  5.  
  6. Public connStr As String
  7. Public sqlCommand As OdbcCommand
  8. Public sqlConn As OdbcConnection
  9. Public Rs As OdbcDataReader  '''Remove this
  10. Public Da as OdbcDataAdapter '''Replace with this
  11. Public sqlComBuilder as OdbcCommandBuilder '''Add this
  12. Public Ds As DataSet '''Add this too
  13.  
  14. Public Sub DoQuery(ByVal tmpSQL as String)
  15.     connStr = "Driver={PostgreSQL ANSI};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
  16.  
  17.               sqlConn = New OdbcConnection(connStr)
  18.               Da = New OdbcDataAdapter(tmpSQL, sqlConn)
  19.               sqlComBuilder = New OdbcCommandBuilder(Da)
  20.               Ds = New DataSet()
  21.  
  22.               sqlConn.Open()
  23.               Da.Fill(Ds,"name_of_table")
  24.               sqlConn.Close()
  25. End Sub
The CommandBuilder is used to automatically create INSERT/DELETE/UPDATE queries when you need to update, insert or delete information in the database. Good thing to use if you don't want to create long and complicated queries manually.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the VB.NET Forum
Thread Tools Search this Thread



.net .net2008 2008 access add advanced application array basic beginner browser button buttons center click code combo cpu cuesent data database datagrid datagridview date datetimepicker designer dissertation dissertations dissertationtopic employees excel exists fade filter forms generatetags html images input intel internet listview map mobile module monitor msaccess mysql net number objects open pan panel pdf picturebox picturebox2 port position print printing printpreview problem regex reuse right-to-left save search searchvb.net serial settings shutdown socket sqldatbase sqlserver storedprocedure survey temperature textbox timer timespan transparency txttoxmlconverter user usercontol vb vb.net vba vbnet vista visual visualbasic visualbasic.net visualstudio.net web winforms wpf wrapingcode xml year
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC