Database Code Sample

[Begginnerdev]

Hello everyone!

I am posting this code for anyone who may be having issues with connecting to a database. Feel free to use this code as you wish.

This will be using the OLEDB library.

'Imports
Imports System.Data.OleDb


Public Class Form1

    'Declarations
    Dim con As OleDbConnection
    Dim cmd As OleDbCommand
    Dim sqls As String
    Dim sqlcmd As String




    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'In form_load event, but can be used on button click.

        'For hard coded connection string
        sqls = "Provider=SQLOLEDB;Datasource=YOURSERVERNAMEHERE;" & _
               "Initial Catalog=YOURDATABASENAMEHERE;Integrated Security=SSPI"
        'For dynamic connection string
        sqls = "Provider=SQLOLEDB;Datasource=" & txtServerName.Text & ";Initial Catalog=" & _
               txtDatabaseName.Text & ";Integrated Security=SSPI"

        'Place all connection code in try/catch blocks.
        Try
            'Connect using the string we have just created
            If con.State <> ConnectionState.Open Then
                con.ConnectionString = sqls
                con.Open()
            End If

            'Alternative way is:
            If con.State <> ConnectionState.Open Then
                con = New OleDbConnection(sqls)
                con.Open()
            End If


            'Selecting everything from a database
            sqlcmd = "SELECT * FROM tablename"

            'Selecting a specific value when you have a reference
            sqlcmd = "SELECT * FROM tablename WHERE columnname = referencevalue"

            'Non hardcoded method
            sqlcmd = "SELECT * FROM tablename WHERE columnname = '" & txtDataToSearchBy.Text & "'"

            'Setting the command
            cmd.Connection = con
            cmd.CommandText = sqlcmd

            'Alternative way is:
            cmd = New OleDbCommand(sqlcmd, con)

            'Querying the database
            'Many differny ways.

            'Returns number of rows
            cmd.ExecuteNonQuery()

            'Returns only the first column of first row.
            cmd.ExecuteScalar()

            'Builds a data reader with the current command.
            cmd.ExecuteReader()

            'Good practice is to close and dispose of a connection.
            'This is more effecient than waiting for the 'garbage collector' to come around and dispose of it for you.
            If con.State = ConnectionState.Open Then
                con.Close()
                con.Dispose()
            End If

        Catch ex As Exception
            'For a descript message, use the ex.message method.
            MsgBox(ex.Message)
        End Try

    End Sub
End Class

Hope this helps anyone who may be stuck.

[phoenix911]

Hi,

Great post, However, Over the past years I have learnt not to use a catch that catches Generic exceptions.

Catching the right exceptions and then handling those exceptions properly is the best practice.

eg.

Catch SqlEx as SqlException

or even

Catch OleDbEx as OleDbException

for a list of the different exception types, google will help, but for the lazy ones go through MSDN DbException class

[Reverend Jim]

I liked the post. Simple examples that illustrate one or two (at most) techniques are the best. I learned ADO as an access technique when I started developing DB apps years ago. Recently I stumbled upon an article which reflected my opinion on Microsoft's data access philosophy. It stated (in part)

When I was first working for Microsoft, I expressed as an issue for developers outside the Redmond Bubble. I called it ‘Shiny new toy syndrome’. It was a problem in that Microsoft every few months would release a new way of doing the same thing, or that they’d suddenly drop something that they’d been pushing hard, and move in a new direction. Think data access from JET Engine, to OLEDb, to DAO, ADO, EF, ODBC yadda yadda yadda. Each of these technologies did the same thing - give programmers the ability to access data, but MS would release each one with a huge fanfare, with evangelists, trainers etc. all teaching folks how to do it. In the end, developers would get sick of it, and the meme would be ‘Just show me how to use data’.

The entire post can be found at http://www.netnavi.tv/2011/12/27/12-things-microsoft-should-do-in-2012/

[Begginnerdev]

Catching the right exceptions and then handling those exceptions properly is the best practice.

Roger, thanks for the help.

Likewise Reverend. Most people have their own 'flavor' they like the most. :)

Mine just happens to be OLEDB due to current situations which I cannot disclose....or I might have to break your legs.... lol jk

[vinodvinu]

I need to work with OleDB Connection. What changes do i need to make with this code ?

[Reverend Jim]

None. It already is an OleDB connection.