Tuesday, May 1, 2012

Generic Scalar And List type SQL Select Function (VB.NET)

Here are a couple of utility functions I wrote to select basic types from a database, given that you have an open SqlConnection object. 

SqlSelect returns a single element of the specified type.

   1:      Public Function SqlSelect(Of T)(ByVal CN As SqlConnection, ByVal szSQL As String, ByRef szError As String) As T
   2:   
   3:          Dim T_value As T
   4:   
   5:          szError = String.Empty
   6:   
   7:          Try
   8:              Using sqlCmd As SqlCommand = New SqlCommand(szSQL, CN)
   9:                  T_value = CType(sqlCmd.ExecuteScalar(), T)
  10:              End Using
  11:          Catch ex As Exception
  12:              szError = ex.Message()
  13:          End Try
  14:   
  15:          Return T_value
  16:   
  17:      End Function

 

SqlSelectList returns a List of the specified type.

   1:      Public Function SqlSelectList(Of T)(ByVal CN As SqlConnection, ByVal szSQL As String, ByRef szError As String) As List(Of T)
   2:   
   3:          Dim T_LIST As New List(Of T)
   4:          szError = String.Empty
   5:   
   6:          Try
   7:              Using sqlCmd As SqlCommand = New SqlCommand(szSQL, CN)
   8:   
   9:                  Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader()
  10:                  If sqlReader.HasRows() = True Then
  11:                      Do While sqlReader.Read() = True
  12:                          T_LIST.Add(CType(sqlReader.GetValue(0), T))
  13:                      Loop
  14:                  End If
  15:                  sqlReader.Close()
  16:   
  17:              End Using
  18:          Catch ex As Exception
  19:              szError = ex.Message()
  20:          End Try
  21:   
  22:          Return T_LIST
  23:   
  24:      End Function

Now, with an open SqlConnection object CN, you can easily retrieve a scalar or list of scalar values like this:

   1:  szSQL = "SELECT COUNT(*) FROM [Users]"
   2:  Dim n As Integer = SqlSelect(Of Integer)(CN, szSQL, szError)
   3:   
   4:  szSQL = "SELECT name FROM sysobjects where type='U' and name <> 'sysdiagrams' order by name"
   5:  Dim TableList As List(Of String) = SqlSelectList(Of String)(CN, szSQL, szError)

enso

No comments: