public DataTable RunSpReturnedDirectTable(ConnectionNameEnum connectionNameEnum, string storeProcureName)
{
var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value);
Parameters.Clear();
var command = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout);
try
{
var da = new SqlDataAdapter(command);
var dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
if (dt.Columns["ErrorCode"] != null)
{
var sqlSpException = new DataException();
for (var i = 0; i < dt.Rows.Count; i++)
{
sqlSpException.Add((int)dt.Rows[i]["ErrorCode"], dt.Rows[i]["ErrorMessage"].ToString());
}
Parameters.Clear();
throw sqlSpException;
}
}
return(dt);
}
catch
{
return(new DataTable());
}
finally
{
ConnectionManager.ReleaseConnection(command.Connection);
Parameters.Clear();
}
}
/// <summary>
/// Runs the sp returned table.
/// </summary>
/// <param name="connectionNameEnum">The connection name enum.</param>
/// <param name="storeProcureName">Name of the store procure.</param>
/// <returns></returns>
public DataTable RunSpReturnedTable(ConnectionNameEnum connectionNameEnum, string storeProcureName)
{
var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value);
Parameters.Clear();
var command = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout);
try
{
var dr = command.ExecuteReader();
var dt = new DataTable();
dt.Load(dr);
if (dt.Rows.Count > 0)
{
if (dt.Columns.Contains("ErrorCode"))
{
var sqlSpException = new DataException();
for (var i = 0; i < dt.Rows.Count; i++)
{
sqlSpException.Add((int)dt.Rows[i]["ErrorCode"], dt.Rows[i]["ErrorMessage"].ToString());
}
ConnectionManager.ReleaseConnection(command.Connection);
Parameters.Clear();
throw sqlSpException;
}
return(dt);
}
}
finally
{
ConnectionManager.ReleaseConnection(command.Connection);
Parameters.Clear();
}
return(null);
}
public async Task <object> RunSpAsync(ConnectionNameEnum connectionNameEnum, string storeProcureName)
{
var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value);
Parameters.Clear();
object returnedObject = null;
SqlCommand command;
if (connectionNameEnum == ConnectionNameEnum.DirectConnectionString)
{
command = new GeneralMethods().MakeSqlCommand(_ConnectionString, storeProcureName, param, ConnectionTimeout);
}
else
{
command = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout);
}
try
{
if (param.Any(p => p.Direction == ParameterDirection.Output))
{
command.ExecuteNonQuery();
returnedObject = param.First(p => p.Direction == ParameterDirection.Output).Value;
}
else
{
var dr = await command.ExecuteReaderAsync();
if (dr.Read())
{
var schemaTable = dr.GetSchemaTable();
if (schemaTable != null && schemaTable.Select("ColumnName='ErrorCode'").Length > 0)
{
var sqlSpException = new DataException();
//sqlSpException.Add((int)dr["ErrorCode"], dr["ErrorMessage"].ToString());
sqlSpException.Add(int.Parse(dr["ErrorCode"].ToString()), dr["ErrorMessage"].ToString());
while (dr.Read())
{
sqlSpException.Add(int.Parse(dr["ErrorCode"].ToString()), dr["ErrorMessage"].ToString());
}
ConnectionManager.ReleaseConnection(command.Connection);
Parameters.Clear();
throw sqlSpException;
}
returnedObject = dr[0];
}
}
}
catch (Exception ex)
{
var exp = new DataException();
exp.Data.Add("Error", ex);
throw exp;
}
finally
{
ConnectionManager.ReleaseConnection(command.Connection);
}
return(returnedObject);
}
/// <summary>
/// Runs a store procedure.
/// </summary>
/// <typeparam name="T">The model which sp result will be converted to it</typeparam>
/// <param name="connectionNameEnum">The connection name enum.</param>
/// <param name="storeProcureName">Name of the store procure.</param>
/// <param name="pageIndex"></param>
/// <param name="pageLength"></param>
/// <returns>The list of T object type</returns>
/// <exception>
/// <cref>SqlSpException ex</cref>
/// </exception>
public List <T> RunSp <T>(ConnectionNameEnum connectionNameEnum, string storeProcureName, int pageIndex = 0, int pageLength = 0)
{
var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value);
Parameters.Clear();
List <T> list;
SqlCommand com;
if (connectionNameEnum == ConnectionNameEnum.DirectConnectionString)
{
com = new GeneralMethods().MakeSqlCommand(_ConnectionString, storeProcureName, param, ConnectionTimeout);
}
else
{
com = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout);
}
try
{
using (var result = com.ExecuteReader())
{
var schemaTable = result.GetSchemaTable();
if (schemaTable != null && schemaTable.Select("ColumnName='ErrorCode'").Length > 0)
{
var sqlSpException = new DataException();
//sqlSpException.Add((int)result["ErrorCode"], result["ErrorMessage"].ToString());
while (result.Read())
{
sqlSpException.Add((int)result["ErrorCode"], result["ErrorMessage"].ToString());
}
throw sqlSpException;
}
if (typeof(T).IsValueType || "".GetType() == typeof(T))
{
list = new List <T>();
Parallel.ForEach(SimpleParallelDataReader <T>(result), (data) =>
{
list.Add(data);
});
return(list);
}
var gm = new GeneralMethods();
list = gm.ToViewModel <T>(result, pageIndex, pageLength);
CurrentListLength = gm.RecordCount;
}
}
catch (DataException)
{
Parameters.Clear();
throw;
}
catch (Exception ex)
{
var exp = new DataException();
exp.Data.Add("Error", ex);
throw exp;
}
finally
{
ConnectionManager.ReleaseConnection(com.Connection);
}
return(list);
}