iDB2Commands in Visual Studio 2010

[xuexue]

Hi!

These are the basic things I know about iDB2Commands to be used in Visual Studio 2010. Could you please help me how could I extract data from DB2? I know INSERT, DELETE and Record Count. But SELECT or Extract Data and UPDATE I don't know.

Imports IBM.Data.DB2
Imports IBM.Data.DB2.iSeries

Public conn As New iDB2Connection
Public str As String = "Datasource=10.0.1.11;UserID=edith;password=edith;DefaultCollection=impexplib"

Dim cmdUpdate As New iDB2Command
Dim sqlUpdate As String

conn = New iDB2Connection(str)
conn.Open()

'*****Delete Records and working fine
sqlUpdate = "DELETE FROM expusers WHERE username<>@username"
cmdUpdate.Parameters.Add("username", iDB2DbType.iDB2Date)
cmdUpdate.Parameters("username").Value = ""

'*****Insert Records and working fine
sqlUpdate = "INSERT INTO expusers (username, password, fullname) VALUES (@username, @password, @fullname)"

cmdUpdate.Parameters.Add("username", iDB2DbType.iDB2VarChar)
cmdUpdate.Parameters.Add("password", iDB2DbType.iDB2VarChar)
cmdUpdate.Parameters.Add("fullname", iDB2DbType.iDB2VarChar)

cmdUpdate.Parameters("username").Value = txtUsername.Text
cmdUpdate.Parameters("password").Value = txtPassword.Text
cmdUpdate.Parameters("fullname").Value = "Editha D. Gacusana"

'*****Count Total Records and working fine
Dim sqlCount As String
Dim cmd As New iDB2Command

sqlCount = "SELECT COUNT(*) AS count FROM import"
cmd = New iDB2Command(Sql, conn)

Dim count As Integer
count = Convert.ToInt32(cmd.ExecuteScalar)

'*****Update Records and IT IS NOT WORKING AT ALL
sqlUpdate = "UPDATE expusers SET password = @password WHERE RECNO = @recno"

cmdUpdate.Parameters.Add("recno", iDB2DbType.iDB2Integer)
cmdUpdate.Parameters.Add("password", iDB2DbType.iDB2VarChar)

cmdUpdate.Parameters("recno").Value = 61
cmdUpdate.Parameters("password").Value = txtPassword.Text

cmdUpdate.Connection = conn
cmdUpdate.CommandText = sqlUpdate
cmdUpdate.ExecuteNonQuery()
conn.Close()

Please help me how to code the SELECT query wherein I could extract/fetch data from DB2 Database. Also, how could i update the records in the database.

Thanks!

[cgeier]

See the other post on how to use parameters. I am not able to test it on DB2, but I believe that the driver follows the same syntax rules as other database drivers.

[xuexue]

Thank you so much Sir! I already resolved the problem in UPDATE! I am really happy! :)

Could you help me with the fetching of records? Thanks in advance! :DDD

[cgeier]

You may find more help if you switch to C#. C# seems to be more popular and more examples seem to be available. See my post here on how to use a dataset and tableadapter. It is for C#, but the steps are similar.

[cgeier]

Also, when you are searching for information, do a general search rather than searching for info on DB2. You will get more results.

Example:

DB2: iDB2DataAdapter

Try searching for:

• OleDbDataAdapter
• SqlDataAdapter

DB2: iDB2Command

Try searching for:
SqlCommand
OleDbCommand

Basically, replace "iDB2" with "OleDb" or "Sql".

Here is some code I've adapted from this post.

doTasks:

Private Sub doTasks()
    Try
        Dim err As Exception = Nothing
        Dim dt As DataTable = Nothing

        'get data and put into a DataTable
        'if an exception occurs, it is 
        'returned in "err"
        dt = getData(err)

        If err IsNot Nothing Then
            MessageBox.Show("Error: " & err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            If dt IsNot Nothing Then
                showData(dt)
            Else
                MessageBox.Show("DataTable is Nothing.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End If
    Catch ex As Exception
        MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

getData:

Private Function getData(ByRef err As Exception) As DataTable

    Dim retDataTable As New DataTable

    Try

        Using cn As New iDB2Connection(str)


            Dim sqlText As String = String.Empty
            sqlText = "SELECT username, fullname "
            sqlText += "FROM expusers"

            Try
                ' Open Connection
                cn.Open()
                Using cmd As New iDB2Command(sqlText, cn)
                    Dim dA As New iDB2DataAdapter(sqlText, cn)
                    Try
                        dA.Fill(retDataTable)
                    Catch ex1 As iDB2Exception
                        Throw New Exception("Error in dataAdapter. " + ex1.Message)
                    Catch ex1 As Exception
                        Throw New Exception("Error in dataAdapter. " + ex1.Message)
                    End Try
                End Using
            Catch ex2 As iDB2Exception
                Throw New Exception("Error in connection. " + ex2.Message)
            Catch ex2 As Exception
                Throw New Exception("Error in connection. " + ex2.Message)
            End Try
        End Using
    Catch ex As iDB2Exception
        err = New Exception("Error (SQLDataReader): " + ex.Message)
    Catch ex As Exception
        err = New Exception("Error (SQLDataReader): " + ex.Message)
    End Try

    Return retDataTable
End Function

showData:

Private Sub showData(dt As DataTable)

    Try

        Dim username As String = String.Empty
        Dim fullname As String = String.Empty

        For Each row As DataRow In dt.Rows
            username = row("username").ToString()
            fullname = row("fullname").ToString()

            Console.WriteLine("username: " & username)
            Console.WriteLine("fullname: " & fullname)
        Next

    Catch ex As Exception
        MessageBox.Show("Error (showData): " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try

End Sub

[cgeier]

Below I've attached a C# version of the code so you can see how to do it in C#. The code is untested though, because I don't have a database to test it on.

I made the class static, so to use it, use <class name>.<method name>

ex: ClsDB2.doTasks()

Note: Change the namespace name to the name of the namespace for your project.

[cgeier]

I made a couple of changes to "ClsDB2", so I'm re-attaching it. Also attached below is the verion in VB .NET. Both are untested.

VB .NET version: ModuleDB2.zip

C# version: ClsDB2.zip

[xuexue]

Hi Sir! Thanks for the code. I think this will show the data in a table right? Is this the shortest code? Let's say what I would like to happen is just to access a single row of data and fetch its fields.

Like for example:
SELECT * FROM expusers WHERE username=@username I would like to know how will I code this in VB.Net then afterwards, fetch the whole row because I will have a condition: If (txtPassword.text = field.password something like that) that's why I wanted access the whole row and if possible get a specific field's value to verify if it matches the records.

Thansk!

[cgeier]

If you aren't updating any data, you can use "ExecuteScalar" or "ExecuteReader".

ExecuteScalar returns a single value. It can be used when you only need to get a single column from a single row.

ExecuteReader returns rows in a DataReader. It can be used to retrieve data from multiple columns and multiple rows.

You don't need to retrieve the password from the database, but rather send the password that the user submitted and see if it matches the one in the database. You really shouldn't store clear text passwords though. You should encrypt it before storing it in the database. Then encrypt the password that the user submitted, and see if it matches the encrypted password in the database.

checkPassword:

Public Function checkPassword(ByVal username As String, ByVal password As String) As Boolean

    Dim totalRows As Integer = 0
    Dim passwordVerified As Boolean = False

    Try
        Using cn As New iDB2Connection(connectStr)
            Dim sqlText As String = String.Empty

            sqlText = "SELECT COUNT(*) FROM " + usersTbl + " "
            sqlText += "WHERE username = @username and password = @password"

            'open connection
            cn.Open()

            Using cmd As New iDB2Command(sqlText, cn)

                'add username parameter
                cmd.Parameters.AddWithValue("@username", username)

                'add password parameter
                cmd.Parameters.AddWithValue("@password", password)

                'execute the command
                'ExecuteScalar returns a single value / single column
                totalRows = Convert.ToInt32(cmd.ExecuteScalar())

                If totalRows >= 1 Then
                    passwordVerified = True
                End If

            End Using
        End Using

    Catch ex As iDB2Exception
        Throw New Exception("Error (checkPassword): " + ex.Message)

    Catch ex As Exception
        Throw New Exception("Error (checkPassword): " + ex.Message)

    End Try

    Return passwordVerified
End Function

To return username, fullname, and password for a user:

First create a class to hold the information. I called it "UserInfo.vb".

UserInfo.vb:

Public Class UserInfo
    'Properties
    Public Property Username As String
    Public Property Fullname As String
    Public Property Password As String

    'constructor
    Public Sub New()
    End Sub

    'constructor
    Public Sub New(ByVal username As String, ByVal fullname As String, ByVal password As String)
        Me.Username = username
        Me.Fullname = fullname
        Me.Password = password
    End Sub
End Class

To get a single user's information:

Public Function getUserInfo(ByVal username As String) As UserInfo

    Dim myUserInfo As New UserInfo()

    Try
        Using cn As New iDB2Connection(connectStr)
            Dim sqlText As String = String.Empty
            Dim dataRdr As iDB2DataReader = Nothing

            sqlText = "SELECT username, fullname, password FROM " + usersTbl
            sqlText += "WHERE username = @username"

            'open connection
            cn.Open()

            Using cmd As New iDB2Command(sqlText, cn)

                'add username parameter
                cmd.Parameters.AddWithValue("@username", username)

                'execute the command
                dataRdr = cmd.ExecuteReader()

                'read each row using the
                'dataRdr. Should only be 1 row.
                While (dataRdr.Read)
                    myUserInfo.Username = dataRdr("username").ToString()
                    myUserInfo.Fullname = dataRdr("fullname").ToString()
                    myUserInfo.Password = dataRdr("password").ToString()
                End While

            End Using
        End Using

    Catch ex As iDB2Exception
        Throw New Exception("Error (getUserInfo): " + ex.Message)

    Catch ex As Exception
        Throw New Exception("Error (getUserInfo): " + ex.Message)

    End Try

    Return myUserInfo
End Function

To get multiple users information:

Public Function getUserInfo() As List(Of UserInfo)

    Dim myUserInfoList As New List(Of UserInfo)

    Try
        Using cn As New iDB2Connection(connectStr)
            Dim sqlText As String = String.Empty
            Dim dataRdr As iDB2DataReader = Nothing

            sqlText = "SELECT username, fullname, password FROM " + usersTbl

            'open connection
            cn.Open()

            Using cmd As New iDB2Command(sqlText, cn)

                'execute the command
                dataRdr = cmd.ExecuteReader()

                'read each row using the
                'dataRdr
                While (dataRdr.Read)
                    'create new instance of UserInfo
                    'each iteration
                    Dim myUserInfo As New UserInfo

                    myUserInfo.Username = dataRdr("username").ToString()
                    myUserInfo.Fullname = dataRdr("fullname").ToString()
                    myUserInfo.Password = dataRdr("password").ToString()

                    'add myUserInfo to myUserInfoList
                    myUserInfoList.Add(myUserInfo)
                End While

            End Using
        End Using

    Catch ex As iDB2Exception
        Throw New Exception("Error (getUserInfo): " + ex.Message)

    Catch ex As Exception
        Throw New Exception("Error (getUserInfo): " + ex.Message)

    End Try

    'return List
    Return myUserInfoList
End Function

[xuexue]

Hi Sir! Thank you so much for all your codes. It really helped a lot! :D Thumbs up!