Database Connectivity

microsoft.public.dotnet.languages.vb

21 Sep 2006 10:05 PM

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 ***

21 Sep 2006 10:53 PM

rowe_newsgroups

> 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:
Show quoteHide quote

> 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 ***

21 Sep 2006 11:07 PM

Ivan Weiss

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 ***

22 Sep 2006 12:20 AM

Izzy

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.OleDb

Public Class cls_AdoManager

    Private strConString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
My.Settings.DatabasePath & My.Settings.DatabaseName & ";User
Id=admin;Password=;"
    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_DatabaseRole " & _
                     "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.Text
                .Connection = oleConn
                oleReader = .ExecuteReader
            End With

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

            oleReader.Close()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly)
        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.EventArgs) Handles cmd_Login.Click

        If clsDB.ValidUser(txt_UserName.Text, txt_Password.Text) Then
            strDbRole = clsDB.UserRole
        Else
            MsgBox("Invalid User Name or Password!",
MsgBoxStyle.Information)
        End If

    End Sub

End Class

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

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

Izzy

Ivan Weiss wrote:
Show quoteHide quote

> 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 ***

22 Sep 2006 12:37 AM

Izzy

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=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;User
Id=admin;Password=;

or my favorite place to go for connection strings is:

http://www.connectionstrings.com/

Izzy wrote:
Show quoteHide quote

> 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.OleDb
>
> Public Class cls_AdoManager
>
>     Private strConString As String =
> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> My.Settings.DatabasePath & My.Settings.DatabaseName & ";User
> Id=admin;Password=;"
>     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_DatabaseRole " & _
>                      "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.Text
>                 .Connection = oleConn
>                 oleReader = .ExecuteReader
>             End With
>
>             While oleReader.Read
>                 bolTemp = True
>                 UserRole = oleReader(0)
>             End While
>
>             oleReader.Close()
>
>         Catch ex As Exception
>             MsgBox(ex.Message, MsgBoxStyle.OkOnly)
>         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.EventArgs) Handles cmd_Login.Click
>
>         If clsDB.ValidUser(txt_UserName.Text, txt_Password.Text) Then
>             strDbRole = clsDB.UserRole
>         Else
>             MsgBox("Invalid User Name or Password!",
> MsgBoxStyle.Information)
>         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 ***

22 Sep 2006 1:02 AM

rowe_newsgroups

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:
Show quoteHide quote

> 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=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;User
> Id=admin;Password=;
>
> 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.OleDb
> >
> > Public Class cls_AdoManager
> >
> >     Private strConString As String =
> > "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> > My.Settings.DatabasePath & My.Settings.DatabaseName & ";User
> > Id=admin;Password=;"
> >     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_DatabaseRole " & _
> >                      "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.Text
> >                 .Connection = oleConn
> >                 oleReader = .ExecuteReader
> >             End With
> >
> >             While oleReader.Read
> >                 bolTemp = True
> >                 UserRole = oleReader(0)
> >             End While
> >
> >             oleReader.Close()
> >
> >         Catch ex As Exception
> >             MsgBox(ex.Message, MsgBoxStyle.OkOnly)
> >         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.EventArgs) Handles cmd_Login.Click
> >
> >         If clsDB.ValidUser(txt_UserName.Text, txt_Password.Text) Then
> >             strDbRole = clsDB.UserRole
> >         Else
> >             MsgBox("Invalid User Name or Password!",
> > MsgBoxStyle.Information)
> >         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 ***

22 Sep 2006 1:23 AM

Ivan Weiss

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 ***

22 Sep 2006 1:42 AM

rowe_newsgroups

>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:
Show quoteHide quote

> 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 ***

22 Sep 2006 2:02 AM

great if the order history contains no personal nor credit info?
Show quoteHide quote

"Ivan Weiss" <ivan.we***@elitefse.com> wrote in message
news:eJaYFXe3GHA.3516@TK2MSFTNGP06.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 ***

22 Sep 2006 12:48 AM

Ivan Weiss

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 ***

22 Sep 2006 1:21 AM

Ivan Weiss

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 ***

22 Sep 2006 6:52 PM

Izzy

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:
Show quoteHide quote

> 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 ***

23 Sep 2006 2:45 AM

Miro

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

Show quoteHide quote

"Izzy" <israel.rich***@gmail.com> wrote in message
news:1158951171.143852.231990@b28g2000cwb.googlegroups.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 ***
>

23 Sep 2006 11:10 PM

Izzy

Will post the encryption code on Monday, I don't remember who wrote,
but it works great. I use it to store passwords in an access table.

Check back Monday after 8AM Central Time.

Izzy

Miro wrote:
Show quoteHide quote

> 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.rich***@gmail.com> wrote in message
> news:1158951171.143852.231990@b28g2000cwb.googlegroups.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 ***
> >

24 Sep 2006 3:57 PM

Miro

Thanks Izzy,
Dont look too hard for it,

I created my own "encryption array" and flush out certain chars with others
depending on what value it links up to
with my encryption table.

Just wondering if there is an easier way. :)

I have a work around though.

Example ( and keep in mind that i have all the alphabet here )
EncryptionKey1 = { A, C, B, D, E, .... }
EncryptionKey2 = { C, D, B, A, E, .... ) 'includes all letters upper and
lower, and also some ascii chars.
and so on ..

Basically if I have a Password that allows for a length of 10. I actually
store the database as an 11 char field and the first
char is a 123...
If I have the #2 stored in that field and and A was in the pword, then it
gets replaced with a letter C.
If its a B, a D gets put in its spot, and an E, ( in this case stays as an
E )

I select a random number from 1 to how many encryption keys i have and thats
the one i use to encrypt it.
Whenever I have to write to the database and I de-crypt it, I re-encrypt it
with a different Encryption key.

Its simple, and with 30 encryption keys, it does its job.
Any letters that are not in the Encryption key or something like a special
char, doesnt get convrted and stays as is.

M.

Show quoteHide quote

"Izzy" <israel.rich***@gmail.com> wrote in message
news:1159053014.114809.148840@i42g2000cwa.googlegroups.com...
> Will post the encryption code on Monday, I don't remember who wrote,
> but it works great. I use it to store passwords in an access table.
>
> Check back Monday after 8AM Central Time.
>
> Izzy
>
>
> Miro wrote:
>> 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.rich***@gmail.com> wrote in message
>> news:1158951171.143852.231990@b28g2000cwb.googlegroups.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 ***
>> >
>

25 Sep 2006 1:22 PM

Izzy

Here it is, I have no idea how it works, but it works great. I use it
to encrypt passwords stored in an access file.

To call it:

'This will encrypt a value
Variable = EncryptString128Bit(txt_Password.Text, EncryptionKey)

'This will decrypt a value
Variable = DecryptString128Bit([Password stored in DB goes here],
EncryptionKey)

Have fun,
Izzy

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

Imports System.Security.Cryptography
Imports System.Text

Module mod_Globals

Public EncryptionKey As String = "justsomewordstobeusedasacryptionkey"

Public Function EncryptString128Bit(ByVal vstrTextToBeEncrypted As
String, ByVal vstrEncryptionKey As String) As String

        Dim bytValue() As Byte
        Dim bytKey() As Byte
        Dim bytEncoded() As Byte
        Dim bytIV() As Byte = {121, 241, 10, 1, 132, 74, 11, 39, 255,
91, 45, 78, 14, 211, 22, 62}
        Dim intLength As Integer
        Dim intRemaining As Integer
        Dim objMemoryStream As New MemoryStream
        Dim objCryptoStream As CryptoStream
        Dim objRijndaelManaged As RijndaelManaged

        vstrTextToBeEncrypted =
StripNullCharacters(vstrTextToBeEncrypted)

        bytValue =
Encoding.ASCII.GetBytes(vstrTextToBeEncrypted.ToCharArray)

        intLength = Len(vstrEncryptionKey)

        If intLength >= 32 Then
            vstrEncryptionKey = Strings.Left(vstrEncryptionKey, 32)
        Else
            intLength = Len(vstrEncryptionKey)
            intRemaining = 32 - intLength
            vstrEncryptionKey = vstrEncryptionKey &
Strings.StrDup(intRemaining, "X")
        End If

        bytKey = Encoding.ASCII.GetBytes(vstrEncryptionKey.ToCharArray)

        objRijndaelManaged = New RijndaelManaged

        Try
            objCryptoStream = New CryptoStream(objMemoryStream,
objRijndaelManaged.CreateEncryptor(bytKey, bytIV),
CryptoStreamMode.Write)
            objCryptoStream.Write(bytValue, 0, bytValue.Length)
            objCryptoStream.FlushFinalBlock()
            bytEncoded = objMemoryStream.ToArray
            objMemoryStream.Close()
            objCryptoStream.Close()
        Catch

        End Try

        Return Convert.ToBase64String(bytEncoded)

    End Function

    Public Function DecryptString128Bit(ByVal vstrStringToBeDecrypted
As String, ByVal vstrDecryptionKey As String) As String

        Dim bytDataToBeDecrypted() As Byte
        Dim bytTemp() As Byte
        Dim bytIV() As Byte = {121, 241, 10, 1, 132, 74, 11, 39, 255,
91, 45, 78, 14, 211, 22, 62}
        Dim objRijndaelManaged As New RijndaelManaged
        Dim objMemoryStream As MemoryStream
        Dim objCryptoStream As CryptoStream
        Dim bytDecryptionKey() As Byte
        Dim intLength As Integer
        Dim intRemaining As Integer
        Dim intCtr As Integer
        Dim strReturnString As String = String.Empty
        Dim achrCharacterArray() As Char
        Dim intIndex As Integer

        bytDataToBeDecrypted =
Convert.FromBase64String(vstrStringToBeDecrypted)

        intLength = Len(vstrDecryptionKey)

        If intLength >= 32 Then
            vstrDecryptionKey = Strings.Left(vstrDecryptionKey, 32)
        Else
            intLength = Len(vstrDecryptionKey)
            intRemaining = 32 - intLength
            vstrDecryptionKey = vstrDecryptionKey &
Strings.StrDup(intRemaining, "X")
        End If

        bytDecryptionKey =
Encoding.ASCII.GetBytes(vstrDecryptionKey.ToCharArray)

        ReDim bytTemp(bytDataToBeDecrypted.Length)

        objMemoryStream = New MemoryStream(bytDataToBeDecrypted)

        Try

            objCryptoStream = New CryptoStream(objMemoryStream,
objRijndaelManaged.CreateDecryptor(bytDecryptionKey, bytIV),
CryptoStreamMode.Read)
            objCryptoStream.Read(bytTemp, 0, bytTemp.Length)
            objCryptoStream.FlushFinalBlock()
            objMemoryStream.Close()
            objCryptoStream.Close()

        Catch

        End Try

        Return StripNullCharacters(Encoding.ASCII.GetString(bytTemp))

    End Function

    Public Function StripNullCharacters(ByVal vstrStringWithNulls As
String) As String

        Dim intPosition As Integer
        Dim strStringWithOutNulls As String

        intPosition = 1
        strStringWithOutNulls = vstrStringWithNulls

        Do While intPosition > 0
            intPosition = InStr(intPosition, vstrStringWithNulls,
vbNullChar)

            If intPosition > 0 Then
                strStringWithOutNulls = Left$(strStringWithOutNulls,
intPosition - 1) & _
                                  Right$(strStringWithOutNulls,
Len(strStringWithOutNulls) - intPosition)
            End If

            If intPosition > strStringWithOutNulls.Length Then
                Exit Do
            End If
        Loop

        Return strStringWithOutNulls

    End Function

End Module

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

Miro wrote:
Show quoteHide quote

> Thanks Izzy,
> Dont look too hard for it,
>
> I created my own "encryption array" and flush out certain chars with others
> depending on what value it links up to
> with my encryption table.
>
> Just wondering if there is an easier way. :)
>
> I have a work around though.
>
> Example ( and keep in mind that i have all the alphabet here )
> EncryptionKey1 = { A, C, B, D, E, .... }
> EncryptionKey2 = { C, D, B, A, E, .... ) 'includes all letters upper and
> lower, and also some ascii chars.
> and so on ..
>
> Basically if I have a Password that allows for a length of 10. I actually
> store the database as an 11 char field and the first
> char is a 123...
> If I have the #2 stored in that field and and A was in the pword, then it
> gets replaced with a letter C.
> If its a B, a D gets put in its spot, and an E, ( in this case stays as an
> E )
>
> I select a random number from 1 to how many encryption keys i have and thats
> the one i use to encrypt it.
> Whenever I have to write to the database and I de-crypt it, I re-encrypt it
> with a different Encryption key.
>
> Its simple, and with 30 encryption keys, it does its job.
> Any letters that are not in the Encryption key or something like a special
> char, doesnt get convrted and stays as is.
>
> M.
>
>
>
> "Izzy" <israel.rich***@gmail.com> wrote in message
> news:1159053014.114809.148840@i42g2000cwa.googlegroups.com...
> > Will post the encryption code on Monday, I don't remember who wrote,
> > but it works great. I use it to store passwords in an access table.
> >
> > Check back Monday after 8AM Central Time.
> >
> > Izzy
> >
> >
> > Miro wrote:
> >> 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.rich***@gmail.com> wrote in message
> >> news:1158951171.143852.231990@b28g2000cwb.googlegroups.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 ***
> >> >
> >

25 Sep 2006 7:27 PM

Miro

Looks like something simillar to what I wrote....... NOT!!! :-)

Thanks... I will be going back to the old code sometime at the end of this
week or early next week once I finish something up what Im doing now and
give her a run. I gotta fix up my Icon/Graphic mess I have created myself.

I will probably have a comment somewhere in there
'Encrypt data 128 bits - it just works ;-)

Thanks agian,

Miro

Show quoteHide quote

"Izzy" <israel.rich***@gmail.com> wrote in message
news:1159190558.498861.156470@k70g2000cwa.googlegroups.com...
> Here it is, I have no idea how it works, but it works great. I use it
> to encrypt passwords stored in an access file.
>
> To call it:
>
> 'This will encrypt a value
> Variable = EncryptString128Bit(txt_Password.Text, EncryptionKey)
>
> 'This will decrypt a value
> Variable = DecryptString128Bit([Password stored in DB goes here],
> EncryptionKey)
>
> Have fun,
> Izzy
>
> ****************************************************************************
>
> Imports System.Security.Cryptography
> Imports System.Text
>
> Module mod_Globals
>
> Public EncryptionKey As String = "justsomewordstobeusedasacryptionkey"
>
> Public Function EncryptString128Bit(ByVal vstrTextToBeEncrypted As
> String, ByVal vstrEncryptionKey As String) As String
>
>        Dim bytValue() As Byte
>        Dim bytKey() As Byte
>        Dim bytEncoded() As Byte
>        Dim bytIV() As Byte = {121, 241, 10, 1, 132, 74, 11, 39, 255,
> 91, 45, 78, 14, 211, 22, 62}
>        Dim intLength As Integer
>        Dim intRemaining As Integer
>        Dim objMemoryStream As New MemoryStream
>        Dim objCryptoStream As CryptoStream
>        Dim objRijndaelManaged As RijndaelManaged
>
>        vstrTextToBeEncrypted =
> StripNullCharacters(vstrTextToBeEncrypted)
>
>        bytValue =
> Encoding.ASCII.GetBytes(vstrTextToBeEncrypted.ToCharArray)
>
>        intLength = Len(vstrEncryptionKey)
>
>        If intLength >= 32 Then
>            vstrEncryptionKey = Strings.Left(vstrEncryptionKey, 32)
>        Else
>            intLength = Len(vstrEncryptionKey)
>            intRemaining = 32 - intLength
>            vstrEncryptionKey = vstrEncryptionKey &
> Strings.StrDup(intRemaining, "X")
>        End If
>
>        bytKey = Encoding.ASCII.GetBytes(vstrEncryptionKey.ToCharArray)
>
>        objRijndaelManaged = New RijndaelManaged
>
>        Try
>            objCryptoStream = New CryptoStream(objMemoryStream,
> objRijndaelManaged.CreateEncryptor(bytKey, bytIV),
> CryptoStreamMode.Write)
>            objCryptoStream.Write(bytValue, 0, bytValue.Length)
>            objCryptoStream.FlushFinalBlock()
>            bytEncoded = objMemoryStream.ToArray
>            objMemoryStream.Close()
>            objCryptoStream.Close()
>        Catch
>
>        End Try
>
>        Return Convert.ToBase64String(bytEncoded)
>
>    End Function
>
>    Public Function DecryptString128Bit(ByVal vstrStringToBeDecrypted
> As String, ByVal vstrDecryptionKey As String) As String
>
>        Dim bytDataToBeDecrypted() As Byte
>        Dim bytTemp() As Byte
>        Dim bytIV() As Byte = {121, 241, 10, 1, 132, 74, 11, 39, 255,
> 91, 45, 78, 14, 211, 22, 62}
>        Dim objRijndaelManaged As New RijndaelManaged
>        Dim objMemoryStream As MemoryStream
>        Dim objCryptoStream As CryptoStream
>        Dim bytDecryptionKey() As Byte
>        Dim intLength As Integer
>        Dim intRemaining As Integer
>        Dim intCtr As Integer
>        Dim strReturnString As String = String.Empty
>        Dim achrCharacterArray() As Char
>        Dim intIndex As Integer
>
>        bytDataToBeDecrypted =
> Convert.FromBase64String(vstrStringToBeDecrypted)
>
>        intLength = Len(vstrDecryptionKey)
>
>        If intLength >= 32 Then
>            vstrDecryptionKey = Strings.Left(vstrDecryptionKey, 32)
>        Else
>            intLength = Len(vstrDecryptionKey)
>            intRemaining = 32 - intLength
>            vstrDecryptionKey = vstrDecryptionKey &
> Strings.StrDup(intRemaining, "X")
>        End If
>
>        bytDecryptionKey =
> Encoding.ASCII.GetBytes(vstrDecryptionKey.ToCharArray)
>
>        ReDim bytTemp(bytDataToBeDecrypted.Length)
>
>        objMemoryStream = New MemoryStream(bytDataToBeDecrypted)
>
>        Try
>
>            objCryptoStream = New CryptoStream(objMemoryStream,
> objRijndaelManaged.CreateDecryptor(bytDecryptionKey, bytIV),
> CryptoStreamMode.Read)
>            objCryptoStream.Read(bytTemp, 0, bytTemp.Length)
>            objCryptoStream.FlushFinalBlock()
>            objMemoryStream.Close()
>            objCryptoStream.Close()
>
>        Catch
>
>        End Try
>
>        Return StripNullCharacters(Encoding.ASCII.GetString(bytTemp))
>
>    End Function
>
>
>    Public Function StripNullCharacters(ByVal vstrStringWithNulls As
> String) As String
>
>        Dim intPosition As Integer
>        Dim strStringWithOutNulls As String
>
>        intPosition = 1
>        strStringWithOutNulls = vstrStringWithNulls
>
>        Do While intPosition > 0
>            intPosition = InStr(intPosition, vstrStringWithNulls,
> vbNullChar)
>
>            If intPosition > 0 Then
>                strStringWithOutNulls = Left$(strStringWithOutNulls,
> intPosition - 1) & _
>                                  Right$(strStringWithOutNulls,
> Len(strStringWithOutNulls) - intPosition)
>            End If
>
>            If intPosition > strStringWithOutNulls.Length Then
>                Exit Do
>            End If
>        Loop
>
>        Return strStringWithOutNulls
>
>    End Function
>
> End Module
>
> ****************************************************************************************
>
> Miro wrote:
>> Thanks Izzy,
>> Dont look too hard for it,
>>
>> I created my own "encryption array" and flush out certain chars with
>> others
>> depending on what value it links up to
>> with my encryption table.
>>
>> Just wondering if there is an easier way. :)
>>
>> I have a work around though.
>>
>> Example ( and keep in mind that i have all the alphabet here )
>> EncryptionKey1 = { A, C, B, D, E, .... }
>> EncryptionKey2 = { C, D, B, A, E, .... ) 'includes all letters upper and
>> lower, and also some ascii chars.
>> and so on ..
>>
>> Basically if I have a Password that allows for a length of 10. I
>> actually
>> store the database as an 11 char field and the first
>> char is a 123...
>> If I have the #2 stored in that field and and A was in the pword, then it
>> gets replaced with a letter C.
>> If its a B, a D gets put in its spot, and an E, ( in this case stays as
>> an
>> E )
>>
>> I select a random number from 1 to how many encryption keys i have and
>> thats
>> the one i use to encrypt it.
>> Whenever I have to write to the database and I de-crypt it, I re-encrypt
>> it
>> with a different Encryption key.
>>
>> Its simple, and with 30 encryption keys, it does its job.
>> Any letters that are not in the Encryption key or something like a
>> special
>> char, doesnt get convrted and stays as is.
>>
>> M.
>>
>>
>>
>> "Izzy" <israel.rich***@gmail.com> wrote in message
>> news:1159053014.114809.148840@i42g2000cwa.googlegroups.com...
>> > Will post the encryption code on Monday, I don't remember who wrote,
>> > but it works great. I use it to store passwords in an access table.
>> >
>> > Check back Monday after 8AM Central Time.
>> >
>> > Izzy
>> >
>> >
>> > Miro wrote:
>> >> 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.rich***@gmail.com> wrote in message
>> >> news:1158951171.143852.231990@b28g2000cwb.googlegroups.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 ***
>> >> >
>> >
>

22 Sep 2006 6:40 PM

Izzy

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:
Show quoteHide quote

> 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 ***

22 Sep 2006 5:01 AM

Cor Ligthert [MVP]

Ivan,

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

I hope this helps,

Cor

Show quoteHide quote

"Ivan Weiss" <ivan.we***@elitefse.com> schreef in bericht
news:O1qwWoc3GHA.2096@TK2MSFTNGP05.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 ***

26 Sep 2006 8:46 PM

Spam Catcher

Ivan Weiss <ivan.we***@elitefse.com> wrote in news:O1qwWoc3GHA.2096
@TK2MSFTNGP05.phx.gbl:

> 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?

Check out LLBLGen for your data layer - they basically provide .NET 3.0
LINQ, OR/M features now ;-)