# Handling of open database connection and calling shared function from WebAPI

I'm working on an ASP.NET project using VB.NET that uses Dapper and the code as implemented so far runs fine with just me testing. In the example below, Dapper calls a stored proc.

But I am wondering primarily right now if the method of generating an open connection (see dbConnFactory below) implemented in the Domain project is not good practice.

Also, can there be pitfalls to calling a Shared function from .NET's WebAPI, whether it the function is for GET, PUT, POST, or Delete?

AutomoblieDomain project

Imports System.Data.Common
Imports Dapper
Namespace DAL

Public Class DomainSettings
Public Shared Property CustomConnectionString As String
End Class

Public Class dbConnFactory
Public Shared Function GetOpenConnection() As DbConnection
Dim connection = New SqlClient.SqlConnection(CustomConnectionString)
connection.Open()
Return connection
End Function
End Class

Public Class CarTypes
Public Property CarTypeID As Integer
Public Property CarTypeText As String

Public Shared Function GetList() As IEnumerable(Of CarTypes)
Using conn = dbConnFactory.GetOpenConnection()
Dim _list = conn.Query(Of CarTypes)("dbo.CarTypes_GetList", CommandType.StoredProcedure).ToList()
Return _list
End Using
End Function
End Class

End Namespace


Web UI Project

Imports System.Net
Imports System.Web.Http
Imports AutomobileDomain

Public Class CarTypesController
Inherits ApiController

<HttpGet()>
Public Function GetList() As IEnumerable(Of DAL.CarTypes)
Return DAL.CarTypes.GetList()
End Function

End Class

• Just an opinion but if your GetList() method is returning a list then perhaps you should specify an IList instead of an IEnumerable. That way it's explict to the caller that there is no delayed execution occuring once they have the data back – dreza Apr 2 '13 at 8:38

• Thanks; I saw the msdn documentation, and was looking for confirmation as to whether or not it was completely foolish to use the factory method. It does seem like over optimization and I'll stay away from that implementation, but now I'm wondering about calling the Shared function from the web api controller... – mg1075 Apr 2 '13 at 1:09