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();
}
}
}
}
}