Generating .NET POCO Classes for SQL Query Results: Listing 1
Using ADO.NET to retrieve the schema of a dynamic query.
Private Function GetQuerySchema(ByVal strconn As String, ByVal strSQL As String) As DataTable
Dim sconQuery As New SqlConnection
Dim scmdQuery As New SqlCommand
Dim srdrQuery As SqlDataReader = Nothing
Dim intRowsCount As Integer = 0
Dim dtSchema As New Data.DataTable
Try
sconQuery.ConnectionString = strconn
sconQuery.Open()
scmdQuery.Connection = sconQuery
scmdQuery.CommandText = strSQL
srdrQuery = scmdQuery.ExecuteReader(Data.CommandBehavior.SchemaOnly)
dtSchema = srdrQuery.GetSchemaTable
Catch ex As Exception
Err.Raise(-1000, , "Error = '" & ex.Message & " ': sql = " & strSQL)
Finally
If Not IsNothing(srdrQuery) Then
If Not srdrQuery.IsClosed Then srdrQuery.Close()
End If
scmdQuery.Dispose()
sconQuery.Close()
sconQuery.Dispose()
End Try
Return dtSchema
End Function
About the Author
Joe Kunk is a Microsoft MVP in Visual Basic, three-time president of the Greater Lansing User Group for .NET, and developer for Dart Container Corporation of Mason, Michigan. He's been developing software for over 30 years and has worked in the education, government, financial and manufacturing industries. Kunk's co-authored the book "Professional DevExpress ASP.NET Controls" (Wrox Programmer to Programmer, 2009). He can be reached via email at [email protected].