Database Connectivity

[Ivan Weiss]

Hi all,

I am just getting started on a new app and am a rusty VB6 guy who is
looking to create something on the side to serve as a corporate
workbench for my company.

ADO.net is new to me so I have some books and need to do some learning
but I am seeing a few different ways to get started.

Would you recommend going with the controls built into Visual Studio,
coding within the subs as I need them, or creating a class to handle all
of my database connectivity.

I am guessing a class is the best way for code re-usability but how
would I go about keeping it generic?

-Ivan

*** Sent via Developersdex http://www.developersdex.com ***

[rowe_newsgroups]

Re: Database Connectivity

Would you recommend going with the controls built into Visual Studio,

coding within the subs as I need them, or creating a class to handle all
of my database connectivity.

I created a class that handles all my ADO.NET interactions. Basically,
I just created the general purpose routines (overloading can come in
handy here if you need mulitple version of a sub). Then I inherit the
class and override the subs if I need to need a more specific version
of the class.

I am guessing a class is the best way for code re-usability but how
would I go about keeping it generic?

I guess I didn't help much with the "how" part of your question, but
I'm not sure what you mean? Let me know what you need and I'll be glad
to help you out.

Thanks,

Seth Rowe

Ivan Weiss wrote:

Hi all,
>
I am just getting started on a new app and am a rusty VB6 guy who is
looking to create something on the side to serve as a corporate
workbench for my company.
>
ADO.net is new to me so I have some books and need to do some learning
but I am seeing a few different ways to get started.
>
Would you recommend going with the controls built into Visual Studio,
coding within the subs as I need them, or creating a class to handle all
of my database connectivity.
>
I am guessing a class is the best way for code re-usability but how
would I go about keeping it generic?
>
-Ivan
>
*** Sent via Developersdex http://www.developersdex.com ***

[Ivan Weiss]

Re: Database Connectivity

It does seem like a class is the way to go.

The how part is simple. I am very used to VB6 and "coding as you need"
or just creating Public Subs.

How would I go about designing a class that could handle my database
connectivity.

I guess for starters I am looking to create a login form. I have an
access database with fields UserName, Password, Roles and I need to
authenticate a user to allow them access to the application.

Once I get through that, I will have enough knowledge and have the class
created so the rest of the app should be a lot easier to learn through.

I am not even sure how to get started writing that class though to
access the database.

-Ivan

*** Sent via Developersdex http://www.developersdex.com ***

[Izzy]

Re: Database Connectivity

Here ya go Ivan, this will get you started.

There is probably 10 different ways to write this, I like this method:

*************** *************** *******
Imports System.Data.Ole Db

Public Class cls_AdoManager

Private strConString As String =
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
My.Settings.Dat abasePath & My.Settings.Dat abaseName & ";User
Id=admin;Passwo rd=;"
Private oleCmd As OleDbCommand
Private oleConn As OleDbConnection
Private oleReader As OleDbDataReader
Private strUserRole As String = ""
Private strSQL As String = ""

Public Property UserRole() As String
Get
Return strUserRole
End Get
Set(ByVal value As String)
strUserRole = value
End Set
End Property

Public Function ValidUser(ByVal UserName As String, ByVal Password
As String) As Boolean

Dim bolTemp As Boolean = False

Try

strSQL = "SELECT usr_DatabaseRol e " & _
"FROM tbl_Users " & _
"WHERE (usr_UserName = '" & UserName & "') " &
_
" AND (usr_Password = '" & Password &
"')"

oleConn = New OleDbConnection (strConString)
oleCmd = New OleDbCommand

oleConn.Open()

With oleCmd
.CommandText = strSQL
.CommandType = CommandType.Tex t
.Connection = oleConn
oleReader = .ExecuteReader
End With

While oleReader.Read
bolTemp = True
UserRole = oleReader(0)
End While

oleReader.Close ()

Catch ex As Exception
MsgBox(ex.Messa ge, MsgBoxStyle.OkO nly)
Finally
oleConn.Close()
End Try

Return bolTemp

End Function

End Class

*************** *************** *

Then the form code would look like this:

*************** *************** *

Public Class frm_Main

Dim clsDB As New cls_AdoManager
Dim strDbRole As String = ""

Private Sub cmd_Login_Click (ByVal sender As System.Object, ByVal e
As System.EventArg s) Handles cmd_Login.Click

If clsDB.ValidUser (txt_UserName.T ext, txt_Password.Te xt) Then
strDbRole = clsDB.UserRole
Else
MsgBox("Invalid User Name or Password!",
MsgBoxStyle.Inf ormation)
End If

End Sub

End Class

*************** *************** **

I hope this gets you started. Be careful of word wrap.

Izzy


Ivan Weiss wrote:

It does seem like a class is the way to go.
>
The how part is simple. I am very used to VB6 and "coding as you need"
or just creating Public Subs.
>
How would I go about designing a class that could handle my database
connectivity.
>
I guess for starters I am looking to create a login form. I have an
access database with fields UserName, Password, Roles and I need to
authenticate a user to allow them access to the application.
>
Once I get through that, I will have enough knowledge and have the class
created so the rest of the app should be a lot easier to learn through.
>
I am not even sure how to get started writing that class though to
access the database.
>
-Ivan
>
*** Sent via Developersdex http://www.developersdex.com ***

[Izzy]

Re: Database Connectivity

Also I didn't exactly make it clear what the connection string looks
like. I'm using VS 2005 which has the My.Settings XML settings file. VS
2003 does something slightly different.

The connection string should look like this:

Provider=Micros oft.Jet.OLEDB.4 .0;Data Source=C:\db1.m db;User
Id=admin;Passwo rd=;

or my favorite place to go for connection strings is:

http://www.connectionstrings.com/

Izzy wrote:

Here ya go Ivan, this will get you started.
>
There is probably 10 different ways to write this, I like this method:
>
*************** *************** *******
Imports System.Data.Ole Db
>
Public Class cls_AdoManager
>
Private strConString As String =
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
My.Settings.Dat abasePath & My.Settings.Dat abaseName & ";User
Id=admin;Passwo rd=;"
Private oleCmd As OleDbCommand
Private oleConn As OleDbConnection
Private oleReader As OleDbDataReader
Private strUserRole As String = ""
Private strSQL As String = ""
>
Public Property UserRole() As String
Get
Return strUserRole
End Get
Set(ByVal value As String)
strUserRole = value
End Set
End Property
>
Public Function ValidUser(ByVal UserName As String, ByVal Password
As String) As Boolean
>
Dim bolTemp As Boolean = False
>
Try
>
strSQL = "SELECT usr_DatabaseRol e " & _
"FROM tbl_Users " & _
"WHERE (usr_UserName = '" & UserName & "') " &
_
" AND (usr_Password = '" & Password &
"')"
>
oleConn = New OleDbConnection (strConString)
oleCmd = New OleDbCommand
>
oleConn.Open()
>
With oleCmd
.CommandText = strSQL
.CommandType = CommandType.Tex t
.Connection = oleConn
oleReader = .ExecuteReader
End With
>
While oleReader.Read
bolTemp = True
UserRole = oleReader(0)
End While
>
oleReader.Close ()
>
Catch ex As Exception
MsgBox(ex.Messa ge, MsgBoxStyle.OkO nly)
Finally
oleConn.Close()
End Try
>
Return bolTemp
>
End Function
>
End Class
>
*************** *************** *
>
Then the form code would look like this:
>
*************** *************** *
>
Public Class frm_Main
>
Dim clsDB As New cls_AdoManager
Dim strDbRole As String = ""
>
Private Sub cmd_Login_Click (ByVal sender As System.Object, ByVal e
As System.EventArg s) Handles cmd_Login.Click
>
If clsDB.ValidUser (txt_UserName.T ext, txt_Password.Te xt) Then
strDbRole = clsDB.UserRole
Else
MsgBox("Invalid User Name or Password!",
MsgBoxStyle.Inf ormation)
End If
>
End Sub
>
End Class
>
*************** *************** **
>
I hope this gets you started. Be careful of word wrap.
>
Izzy
>
>
Ivan Weiss wrote:

It does seem like a class is the way to go.

The how part is simple. I am very used to VB6 and "coding as you need"
or just creating Public Subs.

How would I go about designing a class that could handle my database
connectivity.

I guess for starters I am looking to create a login form. I have an
access database with fields UserName, Password, Roles and I need to
authenticate a user to allow them access to the application.

Once I get through that, I will have enough knowledge and have the class
created so the rest of the app should be a lot easier to learn through.

I am not even sure how to get started writing that class though to
access the database.

-Ivan

*** Sent via Developersdex http://www.developersdex.com ***

[Ivan Weiss]

Re: Database Connectivity

Izzy,

Thanks so much for posting all of that. I am going to load it in and go
through it with a fine tooth comb to make sure I see what you are doing
(not a problem for me).

However, I thought the approach would be different and correct me if I
am right or wrong from a general programming with OOP sense.

I had thought the approach would be to make a User object and have that
user have various methods such as validate, authenticate, etc... Am I
reading too much into the OOP approach? I just figured any object
(person or item) should be separated by a class but like I said this is
new to me.

-Ivan

*** Sent via Developersdex http://www.developersdex.com ***

[rowe_newsgroups]

Re: Database Connectivity

Are you using Access to store sensitive information? If so Access is
very easy to break into, a quick google search will return a few
different methods - just a warning. Anyway, depending on what you're
using the db for you might try to convince them to upgrade to SQL
Server. (This is especially true for things required by the Sarbanes
Oxley laws, in my experience the auditors don't like hearing this info
is stored in Access). Anyways, you might look into some mild encryption
routines to use on the sensitive tables, or at least on the user table.
Let us know if need anything else.

Thanks,

Seth Rowe

Izzy wrote:

Also I didn't exactly make it clear what the connection string looks
like. I'm using VS 2005 which has the My.Settings XML settings file. VS
2003 does something slightly different.
>
The connection string should look like this:
>
Provider=Micros oft.Jet.OLEDB.4 .0;Data Source=C:\db1.m db;User
Id=admin;Passwo rd=;
>
or my favorite place to go for connection strings is:
>

http://www.connectionstrings.com/

>
>
Izzy wrote:

Here ya go Ivan, this will get you started.

There is probably 10 different ways to write this, I like this method:

*************** *************** *******
Imports System.Data.Ole Db

Public Class cls_AdoManager

Private strConString As String =
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
My.Settings.Dat abasePath & My.Settings.Dat abaseName & ";User
Id=admin;Passwo rd=;"
Private oleCmd As OleDbCommand
Private oleConn As OleDbConnection
Private oleReader As OleDbDataReader
Private strUserRole As String = ""
Private strSQL As String = ""

Public Property UserRole() As String
Get
Return strUserRole
End Get
Set(ByVal value As String)
strUserRole = value
End Set
End Property

Public Function ValidUser(ByVal UserName As String, ByVal Password
As String) As Boolean

Dim bolTemp As Boolean = False

Try

strSQL = "SELECT usr_DatabaseRol e " & _
"FROM tbl_Users " & _
"WHERE (usr_UserName = '" & UserName & "') " &
_
" AND (usr_Password = '" & Password &
"')"

oleConn = New OleDbConnection (strConString)
oleCmd = New OleDbCommand

oleConn.Open()

With oleCmd
.CommandText = strSQL
.CommandType = CommandType.Tex t
.Connection = oleConn
oleReader = .ExecuteReader
End With

While oleReader.Read
bolTemp = True
UserRole = oleReader(0)
End While

oleReader.Close ()

Catch ex As Exception
MsgBox(ex.Messa ge, MsgBoxStyle.OkO nly)
Finally
oleConn.Close()
End Try

Return bolTemp

End Function

End Class

*************** *************** *

Then the form code would look like this:

*************** *************** *

Public Class frm_Main

Dim clsDB As New cls_AdoManager
Dim strDbRole As String = ""

Private Sub cmd_Login_Click (ByVal sender As System.Object, ByVal e
As System.EventArg s) Handles cmd_Login.Click

If clsDB.ValidUser (txt_UserName.T ext, txt_Password.Te xt) Then
strDbRole = clsDB.UserRole
Else
MsgBox("Invalid User Name or Password!",
MsgBoxStyle.Inf ormation)
End If

End Sub

End Class

*************** *************** **

I hope this gets you started. Be careful of word wrap.

Izzy


Ivan Weiss wrote:

It does seem like a class is the way to go.
>
The how part is simple. I am very used to VB6 and "coding as you need"
or just creating Public Subs.
>
How would I go about designing a class that could handle my database
connectivity.
>
I guess for starters I am looking to create a login form. I have an
access database with fields UserName, Password, Roles and I need to
authenticate a user to allow them access to the application.
>
Once I get through that, I will have enough knowledge and have the class
created so the rest of the app should be a lot easier to learn through.
>
I am not even sure how to get started writing that class though to
access the database.
>
-Ivan
>
*** Sent via Developersdex http://www.developersdex.com ***

[Ivan Weiss]

Re: Database Connectivity

Izzy, another question.

Wouldnt you need to destroy the object or dispose of it or anything
after utilizing? Or will all resources be freed and database connection
be closed automatically once the functions run?

-Ivan

*** Sent via Developersdex http://www.developersdex.com ***

[Ivan Weiss]

Re: Database Connectivity

It is an access database. Ultimately we might upgrade but for starters
this is a side project I am expecting to take a long time to write and
will only be used by in office personnel (about 30 people). It will not
contain any sensitive information such as payroll or social security. I
basically want to initially create an order entry system so that
salesmen have a history of orders by their accounts. Ultimately I want
it to grow into project management and other features but one giant leap
at a time lol

-Ivan

*** Sent via Developersdex http://www.developersdex.com ***

[rowe_newsgroups]

Re: Database Connectivity

>It will not contain any sensitive information such as payroll or social security

Good to here! It sounds like a great project to get your feet wet in
VB.NET. Have fun with the new language features!

Thanks,

Seth Rowe


Ivan Weiss wrote:

It is an access database. Ultimately we might upgrade but for starters
this is a side project I am expecting to take a long time to write and
will only be used by in office personnel (about 30 people). It will not
contain any sensitive information such as payroll or social security. I
basically want to initially create an order entry system so that
salesmen have a history of orders by their accounts. Ultimately I want
it to grow into project management and other features but one giant leap
at a time lol
>
-Ivan
>
*** Sent via Developersdex http://www.developersdex.com ***

[GS]

Re: Database Connectivity

great if the order history contains no personal nor credit info?
"Ivan Weiss" <ivan.weiss@eli tefse.comwrote in message
news:eJaYFXe3GH A.3516@TK2MSFTN GP06.phx.gbl...

It is an access database. Ultimately we might upgrade but for starters
this is a side project I am expecting to take a long time to write and
will only be used by in office personnel (about 30 people). It will not
contain any sensitive information such as payroll or social security. I
basically want to initially create an order entry system so that
salesmen have a history of orders by their accounts. Ultimately I want
it to grow into project management and other features but one giant leap
at a time lol
>
-Ivan
>
*** Sent via Developersdex http://www.developersdex.com ***

[Cor Ligthert [MVP]]

Re: Database Connectivity

Ivan,

Use this, not primary to use it, but as template how ADONET is working.

I hope this helps,

Cor

"Ivan Weiss" <ivan.weiss@eli tefse.comschree f in bericht
news:O1qwWoc3GH A.2096@TK2MSFTN GP05.phx.gbl...

Hi all,
>
I am just getting started on a new app and am a rusty VB6 guy who is
looking to create something on the side to serve as a corporate
workbench for my company.
>
ADO.net is new to me so I have some books and need to do some learning
but I am seeing a few different ways to get started.
>
Would you recommend going with the controls built into Visual Studio,
coding within the subs as I need them, or creating a class to handle all
of my database connectivity.
>
I am guessing a class is the best way for code re-usability but how
would I go about keeping it generic?
>
-Ivan
>
*** Sent via Developersdex http://www.developersdex.com ***

[Izzy]

Re: Database Connectivity

Hay Ivan,

Sorry for the late response.

Yes you could make an object out of the user like you described. What I
sent, I just wrote to give you a brief overview of ADO.NET, and how you
could query an Access file.

The connection to the database is closed when I called the "Finally"
method of the Try statement. Even if an error occurs the database
connection gets closed.

This way regardless of weather the user was able to login or not the
connection was closed. Plus I wanted you to see how error handling is
different from VB6.

And yes destroying objects when they are no longer needed is good.

Have fun,

Izzy

Ivan Weiss wrote:

Izzy,
>
Thanks so much for posting all of that. I am going to load it in and go
through it with a fine tooth comb to make sure I see what you are doing
(not a problem for me).
>
However, I thought the approach would be different and correct me if I
am right or wrong from a general programming with OOP sense.
>
I had thought the approach would be to make a User object and have that
user have various methods such as validate, authenticate, etc... Am I
reading too much into the OOP approach? I just figured any object
(person or item) should be separated by a class but like I said this is
new to me.
>
-Ivan
>
*** Sent via Developersdex http://www.developersdex.com ***

[Izzy]

Re: Database Connectivity

Something to remember is when you destroy an object the memory doesn't
get released back to the OS instantly. It's not until the GC (garbage
collector) comes along and cleans up any unused resources.

I've had instances where I had to tell the garbage collecter to run
manually: gc.Collect()

You'll need that at some point.

Additionally Seth was right, passwords should be encrypted when stored
in an Access file. I have a routine for that too, it will encrypt it
with 128bit encryption then store it in the Access file. I didn't write
and I can't remember who did.

But if you want it I'll post it for you.

Izzy

Ivan Weiss wrote:

Izzy, another question.
>
Wouldnt you need to destroy the object or dispose of it or anything
after utilizing? Or will all resources be freed and database connection
be closed automatically once the functions run?
>
-Ivan
>
*** Sent via Developersdex http://www.developersdex.com ***

[Miro]

Re: Database Connectivity

Izzy,

Id be interested in that 128 bit encryption - just to see how you do it. -
If you wouldnt mind.

Ivan,
I am in the same boat you are, just started learning vb.net
My code is probably all over the place. I started one step behind and
create my access table with adox.
There isnt much info out there. If you need something like that, let me
know. I found some good examples out there,
but were super hard to find.

Miro

"Izzy" <israel.richner @gmail.comwrote in message
news:1158951171 .143852.231990@ b28g2000cwb.goo glegroups.com.. .

Something to remember is when you destroy an object the memory doesn't
get released back to the OS instantly. It's not until the GC (garbage
collector) comes along and cleans up any unused resources.
>
I've had instances where I had to tell the garbage collecter to run
manually: gc.Collect()
>
You'll need that at some point.
>
Additionally Seth was right, passwords should be encrypted when stored
in an Access file. I have a routine for that too, it will encrypt it
with 128bit encryption then store it in the Access file. I didn't write
and I can't remember who did.
>
But if you want it I'll post it for you.
>
Izzy
>
Ivan Weiss wrote:

>Izzy, another question.
>>
>Wouldnt you need to destroy the object or dispose of it or anything
>after utilizing? Or will all resources be freed and database connection
>be closed automatically once the functions run?
>>
>-Ivan
>>
>*** Sent via Developersdex http://www.developersdex.com ***

>