E:\web\learnaspcom\htdocs\freebook\learn\ubtoc.xml LearnAsp.com - ASP ASP.net Free Lessons
Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll 		<Asp.net blog>
<personal site>
xxx

ASP.net 2.0 VB.NET Generics Execute Scalar Helper
by Ryan Olshan of StrongTypes.com

You code your application to pass any type of SqlDataReader and SqlConnection objects the helper function, eliminating the need for multiple helper functions for different database types.

This page demonstrates using Generics in VB.NET to execute a Scalar function for any database provider using 1 helper function. Here is the code sample:

Web Form:

   filename=/aspnet2/experiments/generics/vb_GenericsDbScalar.aspx

<Test Script Below>

 

<%@ Page Language="VB" Debug="false" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        litRes.Text = dbTest
    End Sub
    
    Private Function dbTest() As String
        Dim strCmdText As String = "SELECT COUNT(Color) FROM tblCars WHERE Color='Red';"
        Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Domains\learnasp.com\wwwroot\aspnet2\experiments\generics\TestDatabase.mdb;")
        
        Dim cmd As OleDbCommand = New OleDbCommand(strCmdText)
        
        Return IIf(DbHelper.ExecuteScalar(Of Integer)(conn, cmd) = 0, "Result returned 0", "Result returned was not 0")
    End Function
    
    Public Class DbHelper
        Private Shared Sub msgError(ByVal strException As String)
            Dim pageCurrent As New Page()
            pageCurrent = HttpContext.Current.Handler
            pageCurrent.Controls.Add(New LiteralControl("Error: " & strException & "<br />"))
        End Sub

        Public Shared Function ExecuteScalar(Of TScalar)(ByVal paramConn As IDbConnection, ByVal paramCmd As IDbCommand) As TScalar
            Dim val As TScalar
            
            Try
                If CType(paramConn, Object).GetType().Name.Replace("Connection", "") = CType(paramCmd, Object).GetType().Name.Replace("Command", "") Then
                    paramCmd.Connection = paramConn
            
                    paramConn.Open()
                    val = paramCmd.ExecuteScalar()
                Else
                    msgError("Connection and command object types do not match.")
                End If
            Catch ex As Exception
                msgError(ex.ToString())
            Finally
                paramConn.Close()
            End Try
            
            Return val
        End Function
    End Class

</script>
<html>
<body>
<asp:Literal ID="litRes" runat="server" />
</body>
</html>
There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.