private static async Task runSprocWithTran <T>(
string sprocName,
dynamic parameters,
NpgsqlTransaction tran,
List <T> results,
int max_rows = -1) where T : BaseModel, new()
{
T resObj = new T();
Type resObjType = resObj.GetType();
// Run the requested sproc
using (var cmd = new NpgsqlCommand(sprocName, tran.Connection, tran))
{
cmd.CommandType = CommandType.StoredProcedure;
addParamsBinding(cmd, parameters);
using (var reader = await CallSprocWithCursor(cmd))
{
if (reader != null)
{
while (await reader.ReadAsync())
{
T pItem = new T();
for (int iField = 0; iField < reader.FieldCount; iField++)
{
var fieldName = reader.GetName(iField);
var dbValue = reader[iField];
var property = resObjType.GetProperty(fieldName);
if (dbValue == DBNull.Value)
{
continue;
}
if (property != null)
{
var value = dbValue;
if (property.PropertyType == typeof(HtmlString))
{
value = (HtmlString)dbValue.ToString();
}
else
if (property.PropertyType == typeof(JsonString))
{
value = (JsonString)dbValue.ToString();
}
else
if (property.PropertyType == typeof(HashIdString))
{
value = new HashIdString((long)dbValue);
}
property.SetValue(pItem, value);
}
else
{
// Place all the unknown fields' values into the aux of BaseModel
pItem.aux.Add(new FieldValue {
name = fieldName, value = dbValue.ToString()
});
}
}
results.Add(pItem);
if (max_rows >= 0 && results.Count > max_rows)
{
break;
}
}
}
}
}
if (ContextHelper.GetContext().HasTransaction)
{
try
{
using (var cmd = new NpgsqlCommand("CLOSE ALL;", tran.Connection, tran))
{
cmd.CommandType = CommandType.Text;
await cmd.ExecuteNonQueryAsync();
}
}
catch
{
; // Ignore
}
}
}
private static async Task runSQL <T>(
string sql,
dynamic parameters,
NpgsqlConnection conn,
List <T> results,
int max_rows = -1) where T : new()
{
T resObj = new T();
Type resObjType = resObj.GetType();
using (var tran = conn.BeginTransaction())
{
try
{
// Add current user id and company
var ctx = ContextHelper.GetContext();
using (var cmd = new NpgsqlCommand(sql, conn, tran))
{
addParamsBinding(cmd, parameters);
using (var reader = await cmd.ExecuteReaderAsync())
{
if (reader != null)
{
while (await reader.ReadAsync())
{
T pItem = new T();
for (int iField = 0; iField < reader.FieldCount; iField++)
{
var fieldName = reader.GetName(iField);
var dbValue = reader[iField];
var property = resObjType.GetProperty(fieldName);
if (dbValue == DBNull.Value)
{
continue;
}
if (property != null)
{
var value = dbValue;
if (property.PropertyType == typeof(HtmlString))
{
value = (HtmlString)dbValue.ToString();
}
else
if (property.PropertyType == typeof(JsonString))
{
value = (JsonString)dbValue.ToString();
}
else
if (property.PropertyType == typeof(HashIdString))
{
value = new HashIdString((long)dbValue);
}
property.SetValue(pItem, value);
}
}
results.Add(pItem);
if (max_rows >= 0 && results.Count > max_rows)
{
break;
}
}
}
}
await tran.CommitAsync();
}
}
catch
{
await tran.RollbackAsync();
throw;
}
}
}