Skip to content

Instantly share code, notes, and snippets.

@Larry57
Created June 6, 2011 07:51
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Larry57/1009894 to your computer and use it in GitHub Desktop.
Save Larry57/1009894 to your computer and use it in GitHub Desktop.
Minimal Db Helper
namespace DbHelper
{
public class OleDbHelper : DbHelper<OleDbConnection>
{
public static DbParameter CreateParameter(string name, object value)
{
return new OleDbParameter(name, value);
}
}
public class SqlDbHelper : DbHelper<SqlConnection>
{
public static DbParameter CreateParameter(string name, object value)
{
return new SqlParameter(name, value);
}
}
public class DbHelper<TC> where TC : DbConnection
{
public static DbDataReader ExecuteReader(string connectionString, string sql, params DbParameter[] pars)
{
return ExecuteCommand<TC, DbDataReader>(
connectionString, sql,
(Cx, Cd) =>
{
Cx.Open();
return Cd.ExecuteReader(CommandBehavior.CloseConnection);
},
pars);
}
public static int Execute(string connectionString, string sql, params DbParameter[] pars)
{
return ExecuteCommand<TC, int>(
connectionString, sql,
(Cx, Cd) =>
{
Cx.Open();
try
{
return Cd.ExecuteNonQuery();
}
finally
{
Cx.Close();
}
},
pars);
}
public static T ExecuteScalar<T>(string connectionString, string sql, params DbParameter[] pars)
{
return ExecuteCommand<TC, T>(
connectionString, sql,
(Cx, Cd) =>
{
Cx.Open();
try
{
var Obj = Cd.ExecuteScalar();
if (Obj == DBNull.Value)
return default(T);
else
{
return (T)Obj;
}
}
finally
{
Cx.Close();
}
},
pars);
}
static T3 ExecuteCommand<T, T3>(string connectionString, string sql, Func<T, DbCommand, T3> action, params DbParameter[] pars) where T : DbConnection
{
T3 ReturnValue;
var Cx = Activator.CreateInstance<T>();
Cx.ConnectionString = connectionString;
using (var Cd = Cx.CreateCommand())
{
Cd.CommandText = sql;
if (sql.StartsWith("sp", StringComparison.InvariantCultureIgnoreCase))
Cd.CommandType = CommandType.StoredProcedure;
if (pars != null && pars.Length > 0)
Cd.Parameters.AddRange(pars);
ReturnValue = action(Cx, Cd);
}
return ReturnValue;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment