Something I had around there:
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace Blabla.Data { public interface IDataAccess { SqlDataReader GetReader(string sql, List<SqlParameter> pl = null); object GetScalar(string sql, List<SqlParameter> pl = null); DataSet GetDataset(string sql, List<SqlParameter> paramsList = null); int ExecuteNonQuery(string query, List<SqlParameter> paramsList = null, CommandType commandType = CommandType.Text, bool avoidTransaction = true); } public class DaoTemplate : IDataAccess { public const string DefaultConnectionString = "DefaultConn"; private SqlConnection GetConnection() { return new SqlConnection(ConfigurationManager.ConnectionStrings[DefaultConnectionString].ConnectionString); } public SqlDataReader GetReader(string sql, List<SqlParameter> paramsList = null) { SqlConnection conex = GetConnection(); try { conex.Open(); SqlCommand cmd = conex.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.StoredProcedure; if (paramsList != null) { cmd.Parameters.AddRange(paramsList.ToArray()); } return cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (InvalidCastException e) { throw (e); } } public object GetScalar(string sql, List<SqlParameter> paramsList = null) { using (SqlConnection conex = GetConnection()) { try { conex.Open(); SqlCommand cmd = conex.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.StoredProcedure; if (paramsList != null) { cmd.Parameters.AddRange(paramsList.ToArray()); } object res = cmd.ExecuteScalar(); return res; } catch (InvalidCastException e) { throw (e); } } } public DataSet GetDataset(string sql, List<SqlParameter> paramsList = null) { using (SqlConnection conex = GetConnection()) { try { conex.Open(); SqlCommand cmd = conex.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.StoredProcedure; if (paramsList != null) { cmd.Parameters.AddRange(paramsList.ToArray()); } SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); return ds; } catch (InvalidCastException e) { throw (e); } } } public int ExecuteNonQuery(string query, List<SqlParameter> paramsList = null, CommandType commandType = CommandType.Text, bool avoidTransaction = true) { if (query == null || query.Length.Equals(0)) { throw new ArgumentNullException("query"); } SqlTransaction trans = null; using (SqlConnection conex = GetConnection()) { try { conex.Open(); SqlCommand cmd = conex.CreateCommand(); cmd.CommandText = query; cmd.CommandType = commandType; if (paramsList != null) { cmd.Parameters.AddRange(paramsList.ToArray()); } if (!avoidTransaction) { trans = conex.BeginTransaction(); cmd.Transaction = trans; } int res = cmd.ExecuteNonQuery(); if (!avoidTransaction) trans.Commit(); return res; } catch (Exception e) { if (!avoidTransaction) trans.Rollback(); throw (e); } finally { if (!avoidTransaction) trans.Dispose(); } } } } }