VB.NET

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

Offline

Newbie Poster

Program loses response during large DB queries VB.NET

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

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
Public db_name As String
Public db_username As String
Public db_userpassword As String
Public db_server As String
 
Public connStr As String
Public sqlCommand As OdbcCommand
Public sqlConn As OdbcConnection
Public Rs As OdbcDataReader
 
Public Sub DoQuery(ByVal tmpSQL as String)
    connStr = "Driver={PostgreSQL ANSI};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
 
              sqlCommand = New OdbcCommand
              sqlConn = New OdbcConnection(connStr)
              If sqlConn.State = ConnectionState.Closed Then
                  sqlConn.Open()
              End If
 
              sqlCommand.CommandType = CommandType.Text
              sqlCommand.Connection = sqlConn
              sqlCommand.CommandText = tmpSQL
 
              Rs = sqlCommand.ExecuteReader
 
End Sub
Public db_name As String
Public db_username As String
Public db_userpassword As String
Public db_server As String

Public connStr As String
Public sqlCommand As OdbcCommand
Public sqlConn As OdbcConnection
Public Rs As OdbcDataReader

Public Sub DoQuery(ByVal tmpSQL as String)
    connStr = "Driver={PostgreSQL ANSI};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
    
              sqlCommand = New OdbcCommand
              sqlConn = New OdbcConnection(connStr)
              If sqlConn.State = ConnectionState.Closed Then
                  sqlConn.Open()
              End If

              sqlCommand.CommandType = CommandType.Text
              sqlCommand.Connection = sqlConn
              sqlCommand.CommandText = tmpSQL

              Rs = sqlCommand.ExecuteReader

End Sub

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

Thanks all!

•

Join Date: Jun 2006

Posts: 62

Reputation: Oxiegen is an unknown quantity at this point

Solved Threads: 3

Oxiegen

Offline

Junior Poster in Training

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

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:

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
While Rs.Read
If Not IsDBNull(Rs.Item("some_column_name")) Then repository = Rs.Item("some_column_name")
End While
While Rs.Read
If Not IsDBNull(Rs.Item("some_column_name")) Then repository = Rs.Item("some_column_name")
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.

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
Public db_name As String
Public db_username As String
Public db_userpassword As String
Public db_server As String
 
Public connStr As String
Public sqlCommand As OdbcCommand
Public sqlConn As OdbcConnection
Public Rs As OdbcDataReader  '''Remove this
Public Da as OdbcDataAdapter '''Replace with this
Public sqlComBuilder as OdbcCommandBuilder '''Add this
Public Ds As DataSet '''Add this too
 
Public Sub DoQuery(ByVal tmpSQL as String)
    connStr = "Driver={PostgreSQL ANSI};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword
 
              sqlConn = New OdbcConnection(connStr)
              Da = New OdbcDataAdapter(tmpSQL, sqlConn)
              sqlComBuilder = New OdbcCommandBuilder(Da)
              Ds = New DataSet()
 
              sqlConn.Open()
              Da.Fill(Ds,"name_of_table")
              sqlConn.Close()
End Sub
Public db_name As String
Public db_username As String
Public db_userpassword As String
Public db_server As String

Public connStr As String
Public sqlCommand As OdbcCommand
Public sqlConn As OdbcConnection
Public Rs As OdbcDataReader  '''Remove this
Public Da as OdbcDataAdapter '''Replace with this
Public sqlComBuilder as OdbcCommandBuilder '''Add this
Public Ds As DataSet '''Add this too

Public Sub DoQuery(ByVal tmpSQL as String)
    connStr = "Driver={PostgreSQL ANSI};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpassword

              sqlConn = New OdbcConnection(connStr)
              Da = New OdbcDataAdapter(tmpSQL, sqlConn)
              sqlComBuilder = New OdbcCommandBuilder(Da)
              Ds = New DataSet()

              sqlConn.Open()
              Da.Fill(Ds,"name_of_table")
              sqlConn.Close()
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.