Extending an ASP.NET Datagrid to Support Simple Cross Tab Reporting: The Structure - The core database routines
(Page 4 of 4 )
Following are the core database routines I used to work seamlessly with tables or stored procedures. Let us have a look at the first one:
Private Function getRowValue(ByVal SQL As String) As String
Try
Dim cmd As New SqlCommand(SQL, New SqlConnection
(getConnectionString))
cmd.Connection.Open()
Dim v As String = cmd.ExecuteScalar & ""
cmd.Connection.Close()
cmd.Dispose()
Return v
Catch ex As Exception
Throw New Exception(ex.Message & ": " & SQL)
End Try
End Function
The above method simply returns a single value based on the SELECT statement we send to it.
Private Function getDataTable(ByVal SQL As String) As DataTable
Dim da As New SqlDataAdapter(SQL, New SqlConnection
(getConnectionString))
Dim dt As New DataTable
da.Fill(dt)
da.Dispose()
Return dt
End Function
The above method simply returns a set of rows in the form of a data table based on the SELECT statement we send to it.
Private Function getConnectionString() As String
Return System.Configuration.ConfigurationSettings.AppSettings.Get
("ConnectionString")
End Function
I used the above method to simply grab the connection string from the web.config file.
The core database routines for working with stored procedures: the parameter cache class
To work with stored procedures, I developed my own class to hold all the parameters (or parameter cache). The following is the complete source code for the same class. You can even use the class to pass the values between several tiers, as I included the concept of serialization along with the class. You can have a look at it.
<XmlRoot("MSSQLProcParameter")> _
Public Class MSSQLProcParameter
<XmlElement("ParameterName", GetType(String))> _
Public ParameterName As String
<XmlElement("ParameterValue", GetType(Object))> _
Public ParameterValue As Object
<XmlElement("ParameterDataType", GetType(SqlDbType))> _
Public ParameterDataType As SqlDbType
<XmlElement("ParameterSize", GetType(Integer))> _
Public ParameterSize As Integer
<XmlElement("ParameterDirectionUsed", GetType
(ParameterDirection))> _
Public ParameterDirectionUsed As ParameterDirection
Public Sub New()
End Sub
Public Sub New(ByVal passedParameterName As String, _
ByVal passedValue As Object, _
Optional ByVal passedSQLType As SqlDbType = Nothing,
_
Optional ByVal passedSize As Integer = Nothing, _
Optional ByVal passedDirection As ParameterDirection
= ParameterDirection.Input)
ParameterName = passedParameterName
ParameterValue = passedValue
ParameterDataType = passedSQLType
ParameterSize = passedSize
ParameterDirectionUsed = passedDirection
End Sub
Public Function getSQLParameter() As SqlParameter
Dim returnSQLParameter As SqlParameter = New SqlParameter
returnSQLParameter.ParameterName = ParameterName
returnSQLParameter.Value = ParameterValue
returnSQLParameter.SqlDbType = ParameterDataType
returnSQLParameter.Size = ParameterSize
returnSQLParameter.Direction = ParameterDirectionUsed
Return returnSQLParameter
End Function
End Class
The class is mainly used to hold the parameters of stored procedures. We would use this class with both of the methods involved in accessing the stored procedures.
The upcoming article shall extend this article to complete it. Sign up for the newsletter to get notified when the next article gets published. 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. |
