Databases
StackOverflow2013
Postsdbo
POCode Review: ADO.NET Data Access Utility Class (VB)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCode Review: ADO.NET Data Access Utility Class (VB)
    primarykey
    data
    text
    <p>When I started at my current employer I inherited a project from a previous developer and in that project was a data access utility class that tries to simplify a lot of the code involved in making calls and retrieving data back from the database. Over time it has been modified to add more overloaded forms of functions, and now I am looking at possible suggestions from the StackOverflow community.</p> <p>What do you think should be added? Removed? Modified?</p> <h3>Note:</h3> <p>It would be nice if this class could remain compatible to VB.NET in the .NET 2.0 framework. We are also developing in 3.5, but I would like to have something that is generally going to work across most frameworks (so no LINQ, etc.) Also, please refrain from unnecessary answers that consist of nothing but "Use nHibernate" or other tools.</p> <h3>My class:</h3> <pre><code>Public Class DataAccess Public Shared Function ReturnScalar(ByVal CmdStr As String) As String Dim Result As String Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As SqlCommand = New SqlCommand(CmdStr, con) cmd.CommandType = CommandType.StoredProcedure Result = cmd.ExecuteScalar con.Close() Return Result End Function Public Shared Function ReturnScalar(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As Integer) As String Dim Result As String Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As SqlCommand = New SqlCommand(CmdStr, con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New SqlParameter(ParamName, Param)) Result = cmd.ExecuteScalar con.Close() Return Result End Function Public Shared Function ReturnScalar(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As String) As String Dim Result As String Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As SqlCommand = New SqlCommand(CmdStr, con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New SqlParameter(ParamName, Param)) Result = cmd.ExecuteScalar con.Close() Return Result End Function Public Shared Function ReturnScalar(ByVal CmdStr As String, ByVal Params As Hashtable) As String Dim Result As String Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) cmd.CommandType = CommandType.StoredProcedure For Each entry As DictionaryEntry In Params cmd.Parameters.Add(New SqlParameter(entry.Key.ToString, entry.Value)) Next Result = cmd.ExecuteScalar con.Close() Return Result End Function Public Shared Function ReturnDataSet(ByVal CmdStr As String) As DataSet Dim DS As New DataSet Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) Dim adp As New SqlDataAdapter(cmd) cmd.CommandType = CommandType.StoredProcedure adp.Fill(DS) con.Close() Return DS End Function Public Shared Function ReturnDataSet(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As Integer) As DataSet Dim DS As New DataSet Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) Dim adp As New SqlDataAdapter(cmd) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New SqlParameter(ParamName, Param)) adp.Fill(DS) con.Close() Return DS End Function Public Shared Function ReturnDataSet(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As String) As DataSet Dim DS As New DataSet Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) Dim adp As New SqlDataAdapter(cmd) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New SqlParameter(ParamName, Param)) adp.Fill(DS) con.Close() Return DS End Function Public Shared Function ReturnDataSet(ByVal CmdStr As String, ByVal Params As Hashtable) As DataSet Dim DS As New DataSet Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) Dim adp As New SqlDataAdapter(cmd) cmd.CommandType = CommandType.StoredProcedure For Each entry As DictionaryEntry In Params cmd.Parameters.Add(New SqlParameter(entry.Key.ToString, entry.Value)) Next adp.Fill(DS) con.Close() Return DS End Function Public Shared Function ReturnDataTable(ByVal CmdStr As String) As DataTable Dim DT As New DataTable Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) Dim adp As New SqlDataAdapter(cmd) cmd.CommandType = CommandType.StoredProcedure adp.Fill(DT) con.Close() Return DT End Function Public Shared Function ReturnDataTable(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As Integer) As DataTable Dim DT As New DataTable Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) Dim adp As New SqlDataAdapter(cmd) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New SqlParameter(ParamName, Param)) adp.Fill(DT) con.Close() Return DT End Function Public Shared Function ReturnDataTable(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As Date) As DataTable Dim DT As New DataTable Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) Dim adp As New SqlDataAdapter(cmd) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New SqlParameter(ParamName, Param)) adp.Fill(DT) con.Close() Return DT End Function Public Shared Function ReturnDataTable(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As String) As DataTable Dim DT As New DataTable Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) Dim adp As New SqlDataAdapter(cmd) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New SqlParameter(ParamName, Param)) adp.Fill(DT) con.Close() Return DT End Function Public Shared Function ReturnDataTable(ByVal CmdStr As String, ByVal Params As Hashtable) As DataTable Dim DT As New DataTable Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) Dim adp As New SqlDataAdapter(cmd) cmd.CommandType = CommandType.StoredProcedure For Each entry As DictionaryEntry In Params cmd.Parameters.Add(New SqlParameter(entry.Key.ToString, entry.Value)) Next adp.Fill(DT) con.Close() Return DT End Function Public Shared Function DBExecute(ByVal CmdStr As String) As Integer Dim RowsAffected As Integer Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) cmd.CommandType = CommandType.StoredProcedure RowsAffected = cmd.ExecuteNonQuery con.Close() Return RowsAffected End Function Public Shared Function DBExecute(ByVal CmdStr As String, ByVal ParamName As String, ByVal Param As Integer) As Integer Dim RowsAffected As Integer Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New SqlParameter(ParamName, Param)) RowsAffected = cmd.ExecuteNonQuery con.Close() Return RowsAffected End Function Public Shared Function DBExecute(ByVal CmdStr As String, ByVal Params As Hashtable) As Integer Dim RowsAffected As Integer Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("RoutingConnectionString").ToString) con.Open() Dim cmd As New SqlCommand(CmdStr, con) cmd.CommandType = CommandType.StoredProcedure For Each entry As DictionaryEntry In Params cmd.Parameters.Add(New SqlParameter(entry.Key.ToString, entry.Value)) Next RowsAffected = cmd.ExecuteNonQuery con.Close() Return RowsAffected End Function End Class </code></pre> <h3>Note #2:</h3> <p>"RoutingConnectionString" just happens to be the name of the ConnectionString in this particular version of the class and is not something which would remain the same throughout all of our projects.</p> <h3>Note #3:</h3> <p>All of our projects utilize SQLServer2005, but I do not see anything wrong with modifications which could help make it more flexible with other database systems such as MySQL or older/newer versions of SQL Server.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGEOCHET
    1. USTheTXI
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCode Review: ADO.NET Data Access Utility Class (VB)
      1. USTheTXI
      1. VTFavorite
    2. VO
      singulars
      1. POCode Review: ADO.NET Data Access Utility Class (VB)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCode Review: ADO.NET Data Access Utility Class (VB)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI will try and go in soon and fix the code-formatting so it doesn't scroll so bad but it might be a little while. If there is another mod who wouldn't mind, I would definitely appreciate that.
      singulars
      1. POCode Review: ADO.NET Data Access Utility Class (VB)
      1. USTheTXI
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload