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)
Posts
