.Net ADO Connection Class

Question

I created a connection class that should return datatables / datareaders, etc. to my web pages. I am worried that the connections will not be closed properly using this class. Here is the class:

Imports Microsoft.VisualBasic Namespace myConnection Public Class DB Public Shared Function GetConnStr() Return "server=foobar" End Function Public Shared Function OpenConn() Return New System.Data.SqlClient.SqlConnection( GetConnStr ) End Function Public Shared Function OpenReader(SQL As String) Dim conn conn = OpenConn conn.Open Return New System.Data.SqlClient.SqlCommand(SQL, conn).ExecuteReader(System.Data.CommandBehavior.CloseConnection) End Function Public Shared Function OpenTable(SQL As String) Dim conn conn = OpenConn conn.Open Dim dr As System.Data.SqlClient.SqlDataReader = New System.Data.SqlClient.SqlCommand(SQL, conn).ExecuteReader(System.Data.CommandBehavior.CloseConnection) Dim dt As System.Data.DataTable = New System.Data.DataTable() dt.Load(dr) Return dt End Function Public Shared Function ExecuteSQL(SQL As String) Dim conn conn = OpenConn conn.Open Return New System.Data.SqlClient.SqlCommand(SQL, conn).ExecuteNonQuery() End Function End Class End Namespace 

And here is how I use it:

 rst = conn.OpenReader(SQL) While rst.Read end while rst.close 

I am worried that as soon as I go into production, the connections will be closed incorrectly and my site will fail. I'm new to .net, is there something wrong with the leader of this class?

Answer 1

You are right: your connection will not be closed in this way. Worse, only by accepting strings for your sqlcommand do you open yourself up for sql injection security vulnerabilities. As an example of a better template, the code I use to populate the data table is as follows:

 Public Function GetDataTable(ByVal sql As String, ByVal AddParameters As Action(Of SqlParameterCollection)) As DataTable Dim result As New DataTable() Using cn As SqlConnection = OpenConn(), _ cmd As New SqlCommand(sql, cn) AddParameters(cmd.Parameters) Using rdr As SqlDataReader = cmd.ExecuteReader result.Load(rdr) End Using End Using Return result End Function 

Then I would call the code as follows:

 Dim data As DataTable = GetDataTable("SELECT * FROM SomeTable WHERE ID= @ID", _ Sub(p) p.Add("@ID", SqlDbType.Int).Value = 12345 End Sub ) 

I have similar code in C # for SqlDataReader, but it needs to use an iterator block, and this feature is not available for VB, it was just added to VB.Net with the service pack for visual studio 2010 and Async CTP a few weeks ago. The important thing to clean up here is that I have a sql connection correctly encapsulated by the Using block, and the code encourages the correct use of query parameters.

Answer 2

Unfortunately, I agree with one of the other comments. Why are you writing your own join classes?

Use ADO.NET EF or LINQ To SQL, which will manage the connections in the Context.

If you continue to do what you are doing, wrap your connection in the Use box.

Answer 3

I use a module to call a database, saves a lot of rows if your are a few different forms ...

This is my modular form:

 Public cn As OleDbConnection Public Sub InitDatabase() Dim sDBase As String = "DB.mdb" cn= New OleDbConnection cn.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0; Data Source=" & sDBase End Sub 

then for callin in the database use this:

 Private ds As New DataSet Private da As New OleDbDataAdapter modWijnen.InitDatabase() Dim cm As New OleDbCommand("Select * from Table1", cn) da = New OleDbDataAdapter(cm) If (ds.Tables.Contains("Table1") = False) Then da.Fill(ds, "Table1") End If 

I hope this was helpful to you ...