Visual Basic

22-Sep-10 0:56

Hi all, I have a vb.net application that has a odbc.odbcconnection to a (Solid) database. Within the app, the connection can switch between different databases depending on which they are logged into at a particular time.

Throughout the app the database is queried and written to on a regular basis in lots of different places and so rather than putting try/catch blocks around all of the operations on the database, is there a way of globally detecting a disconnection (due to network failure etc) so I can then tell the user they can't work on the app any more and it needs to close? I thought of possibly having a background thread which constantly checks the connection but I was wondering if there was an easier more straightforward method?

I've got a handler for any unhandled exceptions that occur in the app which writes the error to a text file, but it's a bit late for a disconnection.

any help appreciated,

Chris

Simon_Whale22-Sep-10 1:09

Simon_Whale

22-Sep-10 1:09

have a look at the state methods in the odbc connection object

if conn.State = System.Data.ConnectionState.Open then
  conn.Close()
end if

As barmey as a sack of badgers

Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.

chrispowell1234522-Sep-10 1:44

22-Sep-10 1:44

I've seen the state methods and tried this in the applicaions unhandled exception handler event:

If solid.State = ConnectionState.Broken Or solid.State = ConnectionState.Closed Then
            Dim tryconnect As Boolean = True
            While tryconnect = True And (solid.State = ConnectionState.Broken Or solid.State = ConnectionState.Closed)
                If MsgBox("Connection to the database has closed unexpectedly. Retry to connect?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                    Try
                        solid.Open()
                    Catch ex As Exception

                    End Try
                Else
                    tryconnect = False
                    Application.Exit()
                End If
            End While
        End If>

However it's too late and the app has exceptioned giving the user the windows error box. any way to disable this?

Dave Kreskowiak22-Sep-10 2:04

22-Sep-10 2:04

You would normally just have one method that returns a Connection object in your data layer. That connection object would check the state of the connection before it queries and the try/catch block around it would also grab exceptions just like you have here. Also, it's not a good idea to leave the Catch block blank. It really should log failure information somewhere or at least show a dialog that the exception happened.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

David Mujica22-Sep-10 3:26

David Mujica

22-Sep-10 3:26

How about implementing a timer control on the main form which checks the connection to the database periodically ? You could even implement a stoplight (red / green) and design limited functionality into your application where some transactions could be stored locally (xml ?) until the database connection has been resumed.

In an ideal world, you would have built a data access layer where you can centralize your access to the database, implement try-catch blocks, logging, administrative notifications, etc.

Good luck and tell us which way you decided to go.
Thumbs Up | :thumbsup:

chrispowell1234522-Sep-10 3:55

22-Sep-10 3:55

Thanks to all....

I've got a timer which runs every second to update a clock so I could put it there, or I could put try/catch around all the datareaders as it's not a huge app.

However, I'm going to have a look into building a data access layer into the application. I'm still fairly new to programming and it's something I'll have to teach myself eventually anyway so I might as well start now whilst the data access in this app is fairly limited!

Thanks again,

Chris

chrispowell1234522-Sep-10 5:37

22-Sep-10 5:37

ok, so let me ask if I'm getting this right! I've added a class to act as the DAL:

Public Class data

    Public Function opendatabase(ByVal solid As Odbc.OdbcConnection, ByVal dsn As String)
        Try
            solid.ConnectionString = "dsn=" & dsn & ";uid=xxxx;pwd=xxxx"
            solid.Open()
            desktop.connectionstring = "dsn=" & dsn & ";uid=xxxx;pwd=xxxx"
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function readdata(ByVal solid As Odbc.OdbcConnection, ByVal sqlquery As String, ByRef datareader As Odbc.OdbcDataReader)
        Try
            Dim command As New Odbc.OdbcCommand(sqlquery, solid)
            datareader = command.ExecuteReader
            Return True
        Catch ex As Exception
            Return False
        End Try
        datareader.Close()
    End Function


    Public Function storedata(ByVal solid As Odbc.OdbcConnection, ByVal sqlcommand As String)
        Try
            Dim command = New Odbc.OdbcCommand(sqlcommand, solid)
            command.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
End Class

So whenever I need to read/write/open the database I call these functions. I now just need to put the error messages asking them whether they want to try to re-connect etc where these three functions are returning false.

I'm guessing adding a business logic layer would be to process these results (from the data reader anyway) in a seperate class to the UI and passing it only the detail it needs to affect the UI?

btw this helped explain the tiers quite well.

Chris

David Mujica22-Sep-10 8:35

David Mujica

22-Sep-10 8:35

Check this article ...

http://www.code-magazine.com/article.aspx?quickid=0603011&page=1[^]

Tom Deketelaere22-Sep-10 3:57

Tom Deketelaere

22-Sep-10 3:57

If you want to check connectivity due to network failure there is a event in the application events called NetworkAvailabilityChanged (the e parameter has a property IsNetworkAvailable )

You can use that one but this does not catch the event where your database server is down but network is still working.
So you will still need to implement some sort of polling the database to see if it is still up.

Eddy Vluggen22-Sep-10 9:17

Eddy Vluggen

22-Sep-10 9:17

Are you using a single connection to the database that's continuously open? If so, why?

I are Troll Suspicious | :suss:

chrispowell1234523-Sep-10 0:02

23-Sep-10 0:02

Yes, there is a single connection that is always open. I'm not doing it this way for one particular reason apart from it's easier to manage I guess. Also, the application itself manages a series of other Uniface apps, which makes up 98% of the software, which each have a constantly open connection to the database....

Dave Kreskowiak23-Sep-10 2:01

23-Sep-10 2:01

That's a really bad practice. SQL connection licenses are usually pretty expensive. The accepted normal practice is to open your connection, execute your query, and close the connection - for each db operation.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

chrispowell1234523-Sep-10 2:35

23-Sep-10 2:35

well we've got no choice with Uniface....well, i guess we could close and open connections for the thousands of clear and retrieves the system uses but It would be considered bad practise in the Uniface environment. Our database licenses restricts us to 5 connection per user which is enough (we did have unlimited connections for a long time!) so cost isn't a factor. I'm sure if we were to change from the Solid database we use we may need to change things

Re: Detecting disconnection from database [modified]

Ian Shlasko23-Sep-10 2:58

Ian Shlasko

23-Sep-10 2:58

Not to sidetrack this thread, but I hadn't heard this argument before.

When you say "expensive", are you referring to the financial cost of the licenses, or in terms of performance? We run off Oracle databases here, that are licensed per-CPU instead of per-connection, and my software works entirely with persistent connections (A login takes a couple seconds).

Proud to have finally moved to the A-Ark. Which one are you in?
Author of the Guardians Saga (Sci-Fi/Fantasy novels)

Dave Kreskowiak23-Sep-10 5:28

VBScript - Gathering WMI & Registry information from networked computers - SOLVED [modified]

23-Sep-10 5:28

Licenses can either be per-CPU or per-Seat and they cost a small fortune per.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

modified on Thursday, September 23, 2010 5:52 PM

eeffoc4221-Sep-10 4:30

eeffoc42

21-Sep-10 4:30

I've been working on a script that will collect information from the computers on our network.

First I generated a list of computer names by getting the information from Active Directory and put it into a text file

Second I've got a script that will go through the list of computer names and gather the information and put it into a .csv file.

So far everything is working just fine except for when I need to read information from the registry.

I get all the correct information for the WMI stuff for each computer but for the registry information, what I get is the information from the computer running the script rather than the remote computer.

What I need help with is modifying the code so I can access the registry on the remote computers.

		Next
			
		' Now get all the Monitor Info
		objOutputFile.Write ", ," & GetMonitorInfo() & vbNewLine
							
	Else
		'WScript.Echo strComputer & " not found..."
		objOutputFile.Write "*** " & strComputer & " *** NOT Found" & vbCrLf
		Err.Clear
		End If
	Next
	
	
	End If

End If

End Sub


'DISPLAY_REGKEY sets the regkey where displays are found. Don't change except for debugging
'I only change it when I am looking at a .REG file that someone sent me saying that the
'code doesn't work.
	Const DISPLAY_REGKEY="HKLM\SYSTEM\CurrentControlSet\Enum\DISPLAY\"
'sets the debug outfile (use format like c:\debug.txt)
	Const DEBUGFILE="NUL"
'if set to 1 then output debug info to DEBUGFILE (also writes debug to screen if running under cscript.exe)
	Const DEBUGMODE=0
'The ForceCscript subroutine forces execution under CSCRIPT.EXE/Prevents execution
'under WSCRIPT.EXE -- useful when debugging
'ForceCScript
 
	DebugOut "Execution Started " & cstr(now)
'wscript.echo GetMonitorInfo() 'just write the output to screen
	DebugOut "Execution Completed " & cstr(now)
 
'This is the main function. It calls everything else
'in the correct order.
Function GetMonitorInfo()
 
	debugout "Getting all display devices"
	arrAllDisplays=GetAllDisplayDevicesInReg()
	debugout "Filtering display devices to monitors"
	arrAllMonitors=GetAllMonitorsFromAllDisplays(arrAllDisplays)
	debugout "Filtering monitors to active monitors"
	arrActiveMonitors=GetActiveMonitorsFromAllMonitors(arrAllMonitors)
 
	if ubound(arrActiveMonitors)=0 and arrActiveMonitors(0)="{ERROR}" Then
		debugout "No active monitors found"
		strFormattedMonitorInfo="[Monitor_1]" & vbcrlf & "Monitor=Not Found" & vbcrlf & vbcrlf
	else
		debugout "Found active monitors"
		debugout "Retrieving EDID for all active monitors"
		arrActiveEDID=GetEDIDFromActiveMonitors(arrActiveMonitors)
		debugout "Parsing EDID/Windows data"
		arrParsedMonitorInfo=GetParsedMonitorInfo(arrActiveEDID,arrActiveMonitors)
		debugout "Formatting parsed data"
		strFormattedMonitorInfo=GetFormattedMonitorInfo(arrParsedMonitorInfo)
	end If

Here is a link to the full code:
http://pastebin.com/n3JRYdUP[^]

Re: VBScript - Gathering WMI & Registry information from networked computers

Dave Kreskowiak21-Sep-10 6:32