public static object ExecuteScalar(this IDbConnection mConn,
string sql, CommandType cmdType = CommandType.Text,
IDbTransaction mTrans = null,
Dictionary <string, object> inputArgs = null,
Dictionary <string, object> outputArgs = null)
{
object res = null;
try
{
using (IDbCommand cmd = mConn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = cmdType;
if (mTrans != null)
{
cmd.Transaction = mTrans;
}
DbCommandHelper.SetCommandParameters(cmd, inputArgs, outputArgs);
res = cmd.ExecuteScalar();
outputArgs = (Dictionary <string, object>)DbCommandHelper.GetOutParametersOfCommand(cmd);
}
}
catch (Exception e)
{
res = -1;
throw;
}
return(res);
}
public static DataSet GetResultSet(this IDbConnection mConn,
string sql, CommandType cmdType = CommandType.Text,
IDbTransaction mTrans = null,
Dictionary <string, object> inputArgs = null,
Dictionary <string, object> outputArgs = null)
{
DataSet ds = null;
try
{
Type adapterTyp = null;
var adapterTypes =
mConn.GetType().Assembly.GetExportedTypes().Where(
typ => typ.IsClass && typ.GetInterfaces().Contains(typeof(IDbDataAdapter)) &&
typ.IsAbstract == false && typeof(IDbDataAdapter).IsAssignableFrom(typ));
if (adapterTypes.Count() > 1)
{
var connTypeName = mConn.GetType().Name;
connTypeName = connTypeName.Substring(0, connTypeName.Length - 10).ToLower();
adapterTyp = adapterTypes.Where(typ => typ.Name.ToLower().StartsWith(connTypeName)).First();
}
else
{
adapterTyp = adapterTypes.First();
}
if (adapterTyp != null)
{
IDbDataAdapter d = null;
d = Activator.CreateInstance(adapterTyp) as IDbDataAdapter;
using (IDbCommand cmd = mConn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = cmdType;
if (mTrans != null)
{
cmd.Transaction = mTrans;
}
DbCommandHelper.SetCommandParameters(cmd, inputArgs, outputArgs);
d.SelectCommand = cmd;
ds = new DataSet();
var result = d.Fill(ds);
outputArgs = (Dictionary <string, object>)DbCommandHelper.GetOutParametersOfCommand(cmd);
}
}
}
catch (Exception e)
{
throw;
}
return(ds);
}
public static List <dynamic> GetDynamicResultSetWithPaging(this IDbConnection mConn,
string sql, CommandType cmdType,
IDbTransaction mTrans = null,
Dictionary <string, object> inputArgs = null,
Dictionary <string, object> outputArgs = null,
uint pageNumber = 1, uint pageItemCount = 10)
{
List <dynamic> list = new List <dynamic>();
IDataReader reader = null;
try
{
using (IDbCommand cmd = mConn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = cmdType;
if (mTrans != null)
{
cmd.Transaction = mTrans;
}
DbCommandHelper.SetCommandParameters(cmd, inputArgs, outputArgs);
reader = cmd.ExecuteReader();
outputArgs = (Dictionary <string, object>)DbCommandHelper.GetOutParametersOfCommand(cmd);
list = reader.GetDynamicResultSetWithPaging(pageNumber: pageNumber, pageItemCount: pageItemCount, closeAtFinal: false);
}
}
catch (Exception e)
{
throw;
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
return(list);
}
public static List <List <dynamic> > GetMultiDynamicResultSet(this IDbConnection mConn,
string sql, CommandType cmdType = CommandType.Text,
IDbTransaction mTrans = null,
Dictionary <string, object> inputArgs = null,
Dictionary <string, object> outputArgs = null)
{
List <List <dynamic> > list = new List <List <dynamic> >();
IDataReader reader = null;
try
{
using (IDbCommand cmd = mConn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = cmdType;
if (mTrans != null)
{
cmd.Transaction = mTrans;
}
DbCommandHelper.SetCommandParameters(cmd, inputArgs, outputArgs);
reader = cmd.ExecuteReader();
outputArgs = (Dictionary <string, object>)DbCommandHelper.GetOutParametersOfCommand(cmd);
list = reader.GetMultiDynamicResultSet();
}
}
catch (Exception e)
{
throw;
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
return(list);
}