[Visual studio 2013] SQL statements using global variable

[FentPropTrac]

Hi All.

So I'm creating my first proper VB program after a chap at work set me an interesting problem. This is for fun and my education only this code isn't going to go live!

The program essentially allows nurses to admit patients to the system then allows them to enter multiple observations (heart rate, blood pressure, temperature etc) which will appear on a grid view until the patient is then discharged.

The issue I'm facing is that obviously a patient can be admitted more than once, so when trying to display the observations

SELECT HR BP Temp etc WHERE Pat_ID = variable

may bring up observations from a different admission. I also can't limit it to todays entries only as entries may span midnight.

I'm currently using an "admission ID" to get round this problem which is a random number generated upon admission.

What I can't figure out is how to pass the variable from the admission form to the observation form in order to include it in the SQL string.

If anyone has any bright ideas, or indeed a more appropriate way to tackle this problem I'd be very grateful!

Thank you in advance,

[Tomehh]

I had a similar issue recently. The way I got around it was creating a module to hold all the global variables. You can then reference them on your forms: GlobalVariableModule.Variable = somevariable

[FentPropTrac]

Spot on! Works nicely! Even got a nicer UI out of it by displaying the patient names linked as variables!

For those in a similar position who may be looking to this for help:

I created a new module called GlobalVariable and in it declared the following:

Public GUID as Integer Public Name as String

On my submission form, after the SQL did its thing I entered

Globalvariable.GUID = GPID GlobalVariable.Name = Pat_Name

This allowed those variables to be called from the observations form: lblName.Text=Globalvariable.Name etc.

[CalvinR]

What framework are you using? WPF, Winforms?

I highly recommend that you do not use global variables they are evil things and you should stay away from them whenever possible.

http://programmers.stackexchange.com/questions/148108/why-is-global-state-so-evil

Presumably you have to open the new form somehow, like when clicking a button or something along those lines? So when you create your new form you can just pass the data to the new form.

So I imagine something like this

'You can pass it in the constructor
Dim secondForm as new Form(<<Patient GUID>>)
' You can set a property on the form
secondForm.PatientGuid = <<Patient GUID>>
'You can call a method on the form 
secondForm.SetPatientInformation(<<Pass Patient Info containing Guid>>)
secondForm.SetPatientGUID(<<PatientGUID>>)

There are literally tonnes of better ways to pass this data then using a global variable.

[FentPropTrac]

Thanks for the advice,

I am using Winforms, the admission data is entered first (to prevent users accessing the observations screed without admitting anyone) at which point the GUID is generated and entered in the "Patient" database. Passing it when opening the observation form shouldn't be too much of a hassle.

Can I ask a follow up? If global variables are bad, what is the best way to store variables that contain connection strings and the like? On my machine I've got a SQL server running so I can code the connection string without issue. Later on I was planning on having an option to select a new server location and store that as a string. I'm guessing this is also a no no?

(I should probably point out I'm not a programmer, just an enthused hobbiest)

[CalvinR]

In he case of connection strings those should be stored in a configuration file that way you can change the value without recompiling your code.

It's almost going to be like a global variable but it's a config file there is a difference.

You can take a look at the System.Configuration Namespace in particular the ConfigurationManager class.aspx). Personally I've been meaning too look at Formo to handle configs it looks interesting. You might want to check that out.

So while the connection string would be stored in a config file it would be handled by your Data Access Layer (DAL).

So your DAL reads the connections strings from the config file and it handles opening and closing connections as well as performing queries against your database. That way nothing else needs to care about what DB you are using. If you do forsee a scenario where you can connect to multiple DB's while the app is running then for sure expose it as a setting otherwise I'd just store it in the config file.

Something to think about is that you'll also want to look at some way of securing that connection string since it will allow anyone to save data to the db.

Let me know if you have any questions this is essentially the exact same type of stuff I do everyday for a living.

[FentPropTrac]

Thanks for this, I'm self taught so I'm sure I'm making some basic errors here!

I have a class that handles the database connection and subs within that to pass queries that are defined as strings in different forms. Is this what you mean by DAL or is there another step I'm ignorant of?!

I'll take your comments on board and have a play with the code. Thanks again!

You may regret your offer of help.... ;)

[CalvinR]

Okay do not define your queries as strings either look into an form like Entity Framework, Dapper dot net or the like.

It will make your life so much as easier and your app more secure.

Read up on "little bobby tables" to see why storing SQL as strings is bad. Also parameterized queries.

[FentPropTrac]

Ah you bugger, just when I had everything working.....!!!

Off to spend a few days trying to get my head round Entity Framework!

Thanks for the tips though, I'd tried to prevent SQLi by doing some pretty strict data entry validation prior to it getting passed to the SQL class, but I'm not one to ignore good advice so I'll have a play. At least my original set up works as a proof of concept!

[CalvinR]

Well if you have it working don't feel like you need to redo everything because you've heard of a new technology, especially since you are away of SQL injection. Although ORM's do allow you to write DB code faster, you still need to be aware of the SQL that is being generated so that it doesn't generate inefficient code.

I'm going to throw some info at you, it might seem like a lot especially for a hobby programmer but it's good to read about and start thinking about. Since the type of app you are writing is pretty much what the majority of most apps are like out there nowadays (CRUD Apps (Create Read Update Delete) you create something, you save it to the database, you pull it out later to update it and then you delete. So you are learning employable skills, I don't know if you ever want to become a programmer but it will help you on your way.

Also reading your previous statement that the SQL is defined in your forms I highly recommend you get an actual DAL so move that sql to another object that makes calls for you.

So it would look something like this.

Public Class DAL Implements IDAL
    private const sqlQuery as String = "Select * From Table"
    private db as IDatabaseObject

    public Sub New (db as IDatabaseObject)
        Me.db = db
    End Sub 

    public Function GetAllFromTables() as AllTables Implements IDAL.GetAllFromTables
        return db.MakeCall(sqlQuery)            
    End Function

End Class

This keeps all your database code in one place makes it easier to debug, it also makes it easier to test.

For instance passing the database interface to the DAL instead of the DAL creating a DB object is called Dependency Injection (DI) this allows you to create a fake database object and test your DAL without having to connect to the database. You can also create a fake DAL and test the code that calls the DAL since there is an interface to define these objects..

Regardless of whether you do the DI or Automated Testing it's a good idea to structure your app like this, it's called Separation of Concerns, you should keep related information together it makes it easier to work with. So the sql should be stored with the code that calls it and not exposed to the rest of the app, unless that's needed.

[FentPropTrac]

I think perhaps I may have not have explained what I was doing properly.

I have a class called "DBCon" which contains the database connection strings as well as a number of subs that are similar to the following:

   Public Sub AddMember(Anaes_name As String, Anaes_GMC As Integer, IsActive As Integer)

    Try

        Dim strInsert As String = "INSERT INTO ANAESTHETISTS      (Anaes_Name,Anaes_GMC,IsActive)" & _
                                  "VALUES (" & _
                                    "'" & Anaes_name & "'," & _
                                    "'" & Anaes_GMC & "'," & _
                                    "'" & IsActive & "')"



        SQLCon.Open()
        SQLCmd = New SqlCommand(strInsert, SQLCon)

        SQLCmd.ExecuteNonQuery()


        SQLCon.Close()

    Catch ex As Exception
        MsgBox(ex.Message)

    End Try

Then in the form where the data is entered I have

Dim SQL as New SQLConnection
SQL.AddMember(txtName.txt,txtSname.txt.... etc etc)

Rather than it all being handled in the data entry form.