Developing a Data Access Layer for Sybase Using ADO.NET: Essentials - Getting a single value from Sybase database using ADO.NET
(Page 5 of 5 )
This is also one of the most frequently used methods (“getRowValue”) in the DAL. This mainly accepts a SQL SELECT statement as a parameter and simply returns the output (only the value of the first row belonging to the first column value) of the SELECT statement in the form of a “string” in ADO.NET.
This function (or method) is mostly suitable for SELECT commands like the following:
SELECT ename FROM emp WHERE empno=1001
SELECT count(*) FROM emp
SELECT avg(sal) FROM emp
Let us walk through the code first:
Public Function getRowValue(ByVal sqlSELECT As String) As String
Dim Conn As AseConnection
Dim cmd As AseCommand
Dim value As String = ""
Try
Conn = New AseConnection(_ConnectionString)
cmd = New AseCommand(sqlSELECT, Conn)
With cmd
.Connection.Open()
value = .ExecuteScalar() & "" 'concatenating an
empty string..to eliminate null or nothing
.Connection.Close()
.Dispose()
End With
Return value
Catch ex As Exception
'LibError.WriteToEventLog
("mrDB::CoreDBLibSQL::getRowValue", ex.Message, ". SQL Statement:
" & sqlSELECT)
Try
If cmd.Connection.State = ConnectionState.Open Then
cmd.Connection.Close()
cmd.Dispose()
End If
Catch e As Exception
'do nothing...if still error persists
End Try
Throw New Exception(ex.Message & ". SQL Statement: "
& sqlSELECT)
End Try
End Function
The main native Sybase objects I used are “AseConnection” (to work with the database connection) and “AseCommand” (to execute any SQL command at the database and return the result). I declare both of those objects as follows:
Dim Conn As AseConnection
Dim cmd As AseCommand
Let us consider the following two statements:
Conn = New AseConnection(_ConnectionString)
cmd = New AseCommand(sqlSELECT, Conn)
The first statement creates a new Sybase database connection (natively) using the connection string defined in the web.config file. The second statement creates a command object, which tries to execute our SELECT statement (passed in the form of a parameter) based on the previous database connection.
With cmd
.Connection.Open()
value = .ExecuteScalar() & "" 'concatenating an
empty string..to eliminate null or nothing
.Connection.Close()
.Dispose()
End With
Return value
From the above code fragment, we open the database connection using the command object first. We get the command to be executed at the database using “ExecuteScalar.” “ExecuteScalar” always returns only one value from the command it has executed. Finally, we close the connection, dispose (clear the resources occupied) the command object and return the value retrieved by the command object.
I created a new “datatable” object, which tries to hold all the data retrieved by the data adapter. The second statement (in the fragment above) executes our SELECT statement and the output (or result) is filled into the data table, which is finally returned from the function.
I shall cover the rest of the methods in the upcoming articles, so make sure that you sign up for a newsletter to notify you. Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
