extracting single value from datareader without using while loop 1

[AzizKamal]

I need to extract column values from one row of datareader but it did not work without using while loop.

In ADODB, I used to achieve it like this:

Dim rs1 As ADODB.Recordset
Sub loadrec()
sql = "select * from Region where code=" & Request.QueryString("mcode")
rs1.Open(sql, cn)
mregionname = rs1.Fields("name").Value
mregionlocation = rs1.Fields("location").Value
End Sub

In ADO.NET, my coding was as follows:

Imports System.Data
Imports System.Data.OracleClient
Imports System.Xml

Dim cn As OracleConnection = New OracleConnection
Dim dr As OracleDataReader
Dim cmd As OracleCommand

Sub loadrec()
Dim sql = New OracleCommand
sql.Connection = cn
sql.CommandText = "select * from Region where code=" & Request.QueryString("mcode")
dr = sql.ExecuteReader
mregionname = dr.Item("name")
mregionlocation = dr.Item("location")
End Sub

I watched the contents of dr.Item(“name”) through watches window but it shows nothing at the line mregionname=dr.Item(“name”). So, I had to use a while loop to accomplish this, but there will surely be another clean approach to achieve this.

Sub loadrec()
Dim sql = New OracleCommand
sql.Connection = cn
sql.CommandText = "select * from Region where code=" & Request.QueryString("mcode")
dr = sql.ExecuteReader
While dr.Read
mregionname = dr.Item("name")
mregionlocation = dr.Item("location")
End While
End Sub

 

[jmeckley]

ADODB is not ado.net the reader is an enumerable and will not advance to the next record until it's asked to do so. thus the [tt]while[/tt] loop.

you can also load the reader into a datatable like this

var table = new Datatable();
table.Load(sql.ExecuteReader());
var row = table.Rows[0];
var name = row["name"];
var location = row["location"];

the while loop is still executed when you call table.Load(), but you don't have to call it manually.

I would also recommend researching the differences between adodb and ado.net. most notably parameterized queries and disconnected data source.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[AzizKamal]

Thanks Jason.

This code worked perfectly. As mentioned by you that while loop will still execute when I call table.Load(), but the code looks better and cleaner. I wrote it in VB.NET:

Imports System.Data
Imports System.Data.OracleClient
Imports System.Xml

Dim cn As OracleConnection = New OracleConnection

Sub loadrec()
On Error Resume Next
Dim sql = New OracleCommand
Dim table = New DataTable
sql.Connection = cn
sql.CommandText = "select * from Region where code=" & Request.QueryString("mcode")
table.Load(sql.ExecuteReader())
Dim row = table.Rows(0)
mregionname = row("name")
mregionlocation = row("location")
End Sub

 

[jmeckley]

there are still some serious issues with the code though. it looks like you took vb6 and just moved it to vb.net. while it might work for happy days scenarios (no errors). there will be problem if an exception occurs. the 3 big issues I see are
1. On Error Resume Next
2. Not disposing of disposable objects
3. injected sql.
remedies
1. understand exceptions and how they work
2. use the using keyword or try/finally blocks to clean up the disposable objects
3. use parameterized queries.

I wrote an FAQ on database connection management as well. I would recommend this to anyone starting out with asp.net and ado.net. the link is in my signature below.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[AzizKamal]

Thanks Jason.

I am studying the topics and FAQ mentioned by you. I will modify the code based on this. I will then post the modified code.

 

[AzizKamal]

I modified the code. I used editGASCode.aspx.vb (Edit General Accounting System Code) for this exercise. The code in this file uses the following oracle table:

Tablename: pf_gascode
field1 (name=code, datatype=number, size=10)
field2 (name=name, datatype=varchar2, size=30)
field3 (name=gascode, datatype=varchar2, size=20)

Upon clicking the Edit link, code in editGASCode.aspx.vb executes and updates name and gascode fields based on the code of the table grid row. Code also sets variables mcode, mname, mgascode that are used to display values in table grid.

Complete code listing of editGASCode.aspx.vb is as follows:

Imports System.Data
Imports System.Data.OracleClient
Imports System.Xml
Partial Class editGASCode
    Inherits System.Web.UI.Page
    Protected mcode, mname, mgascode
    
#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not Page.IsPostBack Then

            Dim cn As OracleConnection
            cn = New OracleConnection
            Dim sql As OracleCommand
            sql = New OracleCommand
            Dim CodeParam, NameParam, GASCodeParam As OracleParameter
            CodeParam = New OracleParameter
            NameParam = New OracleParameter
            GASCodeParam = New OracleParameter

            Try
                cn.ConnectionString = "Data Source=" & Session("servername") & ";User Id=" & Session("username") & ";Password=" & Session("password") & ";Integrated Security=no;"
                cn.Open()
                sql.Connection = cn

                If Request.Form("Submit") = "Update" Then
                    mcode = CInt(Request.Form("id"))
                    mname = Request.Form("name")
                    mgascode = Request.Form("gascode")
                    sql.CommandText = "update " & Session("bookcode") & ".pf_gascode set "
                    sql.CommandText = sql.CommandText & "name=:name, "
                    sql.CommandText = sql.CommandText & "gascode=:gascode "
                    sql.CommandText = sql.CommandText & " where code=:code"
                    sql.CommandType = CommandType.Text
                    sql.Connection = cn

                    CodeParam.ParameterName = ":code"
                    CodeParam.OracleType = OracleType.Number
                    CodeParam.Size = 10
                    CodeParam.Direction = ParameterDirection.Input
                    CodeParam.Value = CInt(Request.Form("id"))

                    NameParam.ParameterName = ":name"
                    NameParam.OracleType = OracleType.VarChar
                    NameParam.Size = 30
                    NameParam.Direction = ParameterDirection.Input
                    NameParam.Value = Request.Form("name")

                    GASCodeParam.ParameterName = ":gascode"
                    GASCodeParam.OracleType = OracleType.VarChar
                    GASCodeParam.Size = 20
                    GASCodeParam.Direction = ParameterDirection.Input
                    GASCodeParam.Value = Request.Form("gascode")

                    sql.Parameters.Add(CodeParam)
                    sql.Parameters.Add(NameParam)
                    sql.Parameters.Add(GASCodeParam)
                    
                    sql.ExecuteNonQuery()
                    Response.Write("<script>window.opener.location=window.opener.location;window.close()</script>")

                Else
                    Dim table As DataTable
                    sql.CommandText = "select * from " & Session("bookcode") & ".pf_gascode where code= :code"
                    sql.CommandType = CommandType.Text
                    sql.Connection = cn

                    CodeParam = New OracleParameter
                    CodeParam.ParameterName = ":code"
                    CodeParam.OracleType = OracleType.Number
                    CodeParam.Size = 10
                    CodeParam.Direction = ParameterDirection.Input
                    CodeParam.Value = Request.QueryString("mcode")

                    sql.Parameters.Add(CodeParam)

                    table = New DataTable
                    table.Load(sql.ExecuteReader())

                    Dim row = table.Rows(0)

                    mname = row("name")
                    mgascode = row("gascode")
                End If

                sql.Dispose()
                cn.Dispose()

            Catch ex As Exception
                Response.Write(ex.Message)
                Response.End()
            End Try
        End If
    End Sub
    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub
End Class

This code:
1. uses exceptions.
2. cleans up the disposable objects sql and cn
3. uses parameterized queries.

As far as database connection management is concerned, this approach is not suited in our scenario because it will reset the application pool. More details are in this thread:
thread855-1610339

Also in the statement:

NameParam.OracleType = OracleType.VarChar

I observed that varchar2 was not available in the intellisense and hence I selected VarChar.

 

[jmeckley]

there are 2 things i would change.
1. move the Dispose() calls to the finally block. in this instance they are only called if everything is successful up to this point. if an error occurs after the connection/command but before dispose the connection/command are not released

var connection = new Connection();
var command = new Command();
try
{
   connection.Open();
   ...
}
finally
{
   command.Dispose();
   connection.Dispose();
}

you can simplify that code to

using(var connection = new Connection())
using(var command = new Command())
{
   connection.Open();
   ...
}

2. you will want the full stack trace, type and message of the exception, not just the message. All of this can be collected through the ToString() function.

Catch(Exception e)
{
   var details = e.ToString();
   Log(details);
}

if you incorporate a logging library like log4net logging becomes 1 line of code.

LogManager.GetLogger(GetType()).Error(e.Message, e);

Normally I let exceptions bubble up the stack and log the error at the top level. if something breaks so bad an exception is thrown, I will stop everything and abandon what the user is doing, displaying a generic "error" message if necessary.

With this approach, I let my code solve the business problem and the infrastructure solve the error handling problem.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[AzizKamal]

Thanks Jason.

1. I moved the Dispose() calls to the finally block.
2. ex.Message changed to ex.ToString

Can you please further elaborate on how to achieve Log(ex.ToString) in Visual Basic. Is it a built-in method or I need to write it. In Catch block, when I type Log, it does not appear in intellisense. When I type ex., I get three entries; InnerException, Message, ToString. At which location, it will log details...

Imports System.Data
Imports System.Data.OracleClient
Imports System.Xml
Partial Class editGASCode
    Inherits System.Web.UI.Page
    Protected mcode, mname, mgascode
    
#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not Page.IsPostBack Then

            Dim cn As OracleConnection
            cn = New OracleConnection
            Dim sql As OracleCommand
            sql = New OracleCommand
            Dim CodeParam, NameParam, GASCodeParam As OracleParameter
            CodeParam = New OracleParameter
            NameParam = New OracleParameter
            GASCodeParam = New OracleParameter

            Try
                cn.ConnectionString = "Data Source=" & Session("servername") & ";User Id=" & Session("username") & ";Password=" & Session("password") & ";Integrated Security=no;"
                cn.Open()
                sql.Connection = cn

                If Request.Form("Submit") = "Update" Then
                    mcode = CInt(Request.Form("id"))
                    mname = Request.Form("name")
                    mgascode = Request.Form("gascode")
                    sql.CommandText = "update " & Session("bookcode") & ".pf_gascode set "
                    sql.CommandText = sql.CommandText & "name=:name, "
                    sql.CommandText = sql.CommandText & "gascode=:gascode "
                    sql.CommandText = sql.CommandText & " where code=:code"
                    sql.CommandType = CommandType.Text
                    
                    CodeParam.ParameterName = ":code"
                    CodeParam.OracleType = OracleType.Number
                    CodeParam.Size = 10
                    CodeParam.Direction = ParameterDirection.Input
                    CodeParam.Value = CInt(Request.Form("id"))

                    NameParam.ParameterName = ":name"
                    NameParam.OracleType = OracleType.VarChar
                    NameParam.Size = 30
                    NameParam.Direction = ParameterDirection.Input
                    NameParam.Value = Request.Form("name")

                    GASCodeParam.ParameterName = ":gascode"
                    GASCodeParam.OracleType = OracleType.VarChar
                    GASCodeParam.Size = 20
                    GASCodeParam.Direction = ParameterDirection.Input
                    GASCodeParam.Value = Request.Form("gascode")

                    sql.Parameters.Add(CodeParam)
                    sql.Parameters.Add(NameParam)
                    sql.Parameters.Add(GASCodeParam)
                    
                    sql.ExecuteNonQuery()
                    Response.Write("<script>window.opener.location=window.opener.location;window.close()</script>")

                Else
                    Dim table As DataTable
                    sql.CommandText = "select * from " & Session("bookcode") & ".pf_gascode where code= :code"
                    sql.CommandType = CommandType.Text
                    
                    CodeParam = New OracleParameter
                    CodeParam.ParameterName = ":code"
                    CodeParam.OracleType = OracleType.Number
                    CodeParam.Size = 10
                    CodeParam.Direction = ParameterDirection.Input
                    CodeParam.Value = Request.QueryString("mcode")

                    sql.Parameters.Add(CodeParam)

                    table = New DataTable
                    table.Load(sql.ExecuteReader())

                    Dim row = table.Rows(0)

                    mname = row("name")
                    mgascode = row("gascode")
                End If
            Catch ex As Exception
                Response.Write(ex.ToString)
                Response.End()
            Finally
                sql.Dispose()
                cn.Dispose()
            End Try
        End If
    End Sub
    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub
End Class

 

[jmeckley]

Log() is a function/object you would create to encapsulate the details of logging. I have used log4net exclusively ever since I found it. configuration can be tedious at times, but the power and flexibility make it works the work. Logging configuration is similar across projects as well. so if you get one configuration in place, you can tweak it for the next project as necessary.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259