General data access functions
What is the best way to code the following common data access functions (ADO.NET, C # or VB, SQLServer or OLEDB)
- SQL execution on connect
- Open DataReader
- Open the DataSet (any ideas on this?)
This way I can call these functions from anywhere in my program. I am not interested in data access patterns or data access layers unless they are directly applied to those functions. (i.e. a template to automatically close a connection or reader / dataset)
Examples of using
ExecuteSQL ("UPDATE tblTest SET x = 5 WHERE [ID] = 4")
Using rdr As OleDb.OleDbDataReader = OpenReader ("SELECT * FROM tblExecute")
While rdr.Read ()
End while
End Using
Function examples
Public Function ExecuteSQL (ByVal strSQL As String) As Boolean
Using cn As New OleDb.OleDbConnection (strConn)
cn.Open ()
Using cmd As New OleDb.OleDbCommand (strSQL, cn)
Return cmd.ExecuteNonQuery ()> 0
End Using
End Using
Return False
End Function
Public Function OpenReader (ByVal strSQL As String) As OleDb.OleDbDataReader
Dim cn As New OleDb.OleDbConnection (strConn)
cn.Open ()
If cn.State = ConnectionState.Open Then
Dim cmd As New OleDb.OleDbCommand (strSQL, cn)
Return cmd.ExecuteReader (CommandBehavior.CloseConnection)
Else
Throw New Exception ("Unable to connect to database.")
End If
End Function
source to share
Here's my Fill method , which, given a shared list and a lambda, fills the list with objects read from the IDataReader:
public static void Fill<T>(this IDbCommand cmd,
IList<T> list, Func<IDataReader, T> rowConverter)
{
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
list.Add(rowConverter(rdr));
}
}
}
You use it like this:
// var cmd = new SqlCommand(...);
// var things = new List<Thing>();
cmd.Fill(things, r => new Thing { ID = r.GetInt32(0), Name = r.GetString(1) });
It's really handy to be able to complete this ExecuteReader and Read cycle in one line.
source to share
If that's all you want, then the code you posted is essentially sufficient. As for the best ... Well, I suggest using one of these "data access patterns". But it does work, and there is still nothing to say. You add other functions for ExecuteScalar etc. if you like.
You are mostly using strings, if you are concatenating or building your SQL then it is very bad. If you are doing this, you really should be using Parameterized queries and extend your functions to use collections of parameters and the like.
source to share