public static DbCommand CreateCommand(DbConnection conn, string commandText)
{
CmdParams dbNull = null;
DbCommand result = CreateCommand(conn, commandText, dbNull);
return(result);
}
public static object ExecuteScalar(DbConnection conn, string commandText, CmdParams cmdParams)
{
using (DbCommand cmd = CreateCommand(conn, commandText, cmdParams))
{
return(cmd.ExecuteScalar());
}
}
public static DbCommand CreateCommand(DbConnection conn, CommandType type, string commandText,
CmdParams cmdParams)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
DbCommand result = conn.CreateCommand();
result.CommandText = commandText;
result.CommandType = type;
if (cmdParams != null)
{
foreach (var param in cmdParams)
{
object value;
if (param.Value is Guid)
{
value = ((Guid)param.Value).ToGuidStr();
}
else if (param.Value is bool)
{
value = ((bool)param.Value).ToShort();
}
else
{
value = param.Value;
}
result.AddParam(param.Key, value);
}
}
return(result);
}
public static int ExecuteNonQuery(DbConnection conn, string commandText, CmdParams cmdParams = null)
{
using (DbCommand cmd = CreateCommand(conn, commandText, cmdParams))
{
return(cmd.ExecuteNonQuery());
}
}
public static IEnumerable <T> ReadCollection <T>(
DbConnection conn,
string commandText,
CmdParams cmdParams,
Func <DbDataReader, T> func = null) where T : class
{
return(ReadCollection(conn, commandText, cmdParams, null, null, func));
}
public static IEnumerable <T> ReadCollection <T>(
DbConnection conn,
string commandText,
CmdParams cmdParams,
string[] excludeFields,
Func <DbDataReader, T> func = null)
{
return(ReadCollection(conn, commandText, cmdParams, excludeFields, null, func));
}
public void Add(string constraint, CmdParams cmdParams)
{
_Constraints.Add(constraint);
if (cmdParams != null)
{
foreach (var p in cmdParams)
{
this.Params.Add(p.Key, p.Value);
}
}
}
public static T ExecuteReader <T>(DbConnection conn, string commandText, CmdParams cmdParams = null,
Func <DbDataReader, T> func = null) where T : class
{
if (func == null)
{
func = (r => r.ToObject <T>());
}
using (DbCommand cmd = CreateCommand(conn, commandText, cmdParams))
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(func(reader));
}
return(null);
}
}
public static T ExecuteScalar <T>(DbConnection conn, string commandText, CmdParams cmdParams)
{
object result = ExecuteScalar(conn, commandText, cmdParams);
if (typeof(T) == typeof(Guid))
{
if (result == null)
{
return((T)((object)Guid.Empty));
}
return((T)((object)new Guid(result.ToString())));
}
if (result is DBNull || result == null)
{
return(default(T));
}
return((T)Convert.ChangeType(result, typeof(T)));
}
public static IEnumerable <T> ReadCollection <T>(
DbConnection conn,
string commandText,
CmdParams cmdParams,
string[] excludeFields,
ObjectsChangeTracker changeTracker,
Func <DbDataReader, T> func = null)
{
using (DbCommand cmd = CreateCommand(conn, commandText, cmdParams))
using (DbDataReader reader = cmd.ExecuteReader())
{
if (func == null)
{
func = (r => r.ToObject <T>(null, excludeFields, changeTracker));
}
while (reader.Read())
{
yield return(func(reader));
}
}
}
public FilterConstraints()
{
_Constraints = new List <string>();
Params = new CmdParams();
}
public static DbDataReader ExecuteReader(DbConnection conn, string commandText, CmdParams cmdParams)
{
using (DbCommand cmd = CreateCommand(conn, commandText, cmdParams))
{
return(cmd.ExecuteReader());
}
}
public static DbCommand CreateStoredProcedureCommand(DbConnection conn, string spName, CmdParams cmdParams)
{
using (DbCommand cmd = CreateCommand(conn, CommandType.StoredProcedure, spName, cmdParams))
{
return(cmd);
}
}
public static async Task <int> ExecuteNonQueryAsync(DbConnection conn, string commandText, CmdParams cmdParams = null)
{
using (DbCommand cmd = await CreateCommandAsync(conn, commandText, cmdParams))
{
return(cmd.ExecuteNonQuery());
}
}
public static IEnumerable <T> ExecuteReaderEnum <T>(DbConnection conn, string commandText, CmdParams cmdParams,
Func <DbDataReader, T> func)
{
using (DbCommand cmd = CreateCommand(conn, commandText, cmdParams))
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
yield return(func(reader));
}
}
}
public static T ExecuteReaderStruct <T>(DbConnection conn, string commandText, CmdParams cmdParams,
Func <DbDataReader, T> func) where T : struct
{
using (DbCommand cmd = CreateCommand(conn, commandText, cmdParams))
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(func(reader));
}
return(default(T));
}
}