public List <T> RunSp <T>(ConnectionNameEnum connectionNameEnum, string storeProcureName,
int recordCount, int pageNumber, out int allRecordCount)
{
var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value);
Parameters.Clear();
List <T> list;
var com = new GeneralMethods().MakeSqlCommand(connectionNameEnum, storeProcureName, param, ConnectionTimeout);
var dt = new DataTable();
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);
});
allRecordCount = list.Count;
return(list);
}
return(new GeneralMethods().ToViewModel <T>(result, recordCount, pageNumber, out allRecordCount));
//dt.Load(result);
////var tempTable = dt.n
////for(int i = 0; i <= )
//var l = dt.AsEnumerable(); //.Select();
//allRecordCount = l.Count();
//var newList = l.Skip((pageNumber - 1)*recordCount).Take(recordCount);
//list = new GeneralMethods().ToViewModel<T>(newList);
}
}
catch (DataException)
{
Parameters.Clear();
throw;
}
finally
{
ConnectionManager.ReleaseConnection(com.Connection);
}
}
public DataSet RunSpReturnedDirectDataSet(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 DataSet();
da.Fill(dt);
return(dt);
}
catch
{
return(new DataSet());
}
finally
{
ConnectionManager.ReleaseConnection(command.Connection);
Parameters.Clear();
}
}
/// <summary>
/// Runs the sp.
/// </summary>
/// <param name="connectionNameEnum">The connection name enum.</param>
/// <param name="storeProcureName">Name of the store procure.</param>
/// <returns>Scalar data of sp</returns>
/// <exception>SqlSPException ex
/// <cref>SqlSpException ex</cref>
/// </exception>
public static object RunSp(ConnectionNameEnum connectionNameEnum, string storeProcureName)
{
object returnedObject = null;
SqlCommand command = SqlGeneralMethods.MakeSqlCommand(connectionNameEnum, storeProcureName, Parameters);
SqlDataReader dr = command.ExecuteReader();
if (dr.Read())
{
var schemaTable = dr.GetSchemaTable();
if (schemaTable != null && schemaTable.Select("ColumnName='ErrorCode'").Length > 0)
{
var sqlSpException = new ShonizSqlSpException();
sqlSpException.Add((int)dr["ErrorCode"], dr["ErrorMessage"].ToString());
while (dr.Read())
{
sqlSpException.Add((int)dr["ErrorCode"], dr["ErrorMessage"].ToString());
}
ConnectionManager.ReleaseConnection(command.Connection);
Parameters.Clear();
throw sqlSpException;
}
returnedObject = dr[0];
}
ConnectionManager.ReleaseConnection(command.Connection);
Parameters.Clear();
return(returnedObject);
}
public List <T> GetTable <T>(ConnectionNameEnum connectionNameEnum, string tableName, string whereCondition)
{
List <T> list;
var com = GeneralMethods.MakeQuerySqlCommand(connectionNameEnum, "Select * from " + tableName + " " + whereCondition, ConnectionTimeout);
try
{
using (var result = com.ExecuteReader())
{
if (typeof(T).IsValueType || "".GetType() == typeof(T))
{
list = new List <T>();
Parallel.ForEach(SimpleParallelDataReader <T>(result), (data) =>
{
list.Add(data);
});
return(list);
}
list = new GeneralMethods().ToViewModel <T>(result);
}
}
catch (DataException)
{
Parameters.Clear();
throw;
}
finally
{
ConnectionManager.ReleaseConnection(com.Connection);
}
return(list);
}
public IList RunQuery(string assemblyName, string typeName, ConnectionNameEnum connectionNameEnum, string query)
{
MethodInfo method = typeof(StoreProcdureManagement).GetMethods().First(m => m.Name == "RunQuery" && m.GetGenericArguments().Length == 1);
var typeArgs = Assembly.Load(assemblyName).GetTypes().First(t => t.Name == typeName);
MethodInfo generic = method.MakeGenericMethod(typeArgs);
return((IList)generic.Invoke(this, new[] { (object)connectionNameEnum, (object)query }));
}
public async Task <List <T> > RunSpAsync <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);
}
using (var result = await com.ExecuteReaderAsync())
{
try
{
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;
}
finally
{
ConnectionManager.ReleaseConnection(com.Connection);
}
}
return(list);
}
/// <summary>
/// Gets the connection.
/// </summary>
/// <param name="connectionNameEnum">The connection name enum(An enum that has fields
/// which named by web.config connection string).</param>
/// <returns>New Connection</returns>
internal SqlConnection GetConnection(ConnectionNameEnum connectionNameEnum)
{
var connection = new SqlConnection(
System.Configuration.ConfigurationManager.
ConnectionStrings[connectionNameEnum.ToString()].ConnectionString);
connection.Open();
return(connection);
}
/// <summary>
/// Gets the connection.
/// </summary>
/// <param name="connectionNameEnum">The connection name enum(An enum that has fields
/// which named by web.config connection string).</param>
/// <returns>New Connection</returns>
public static SqlConnection GetConnection(ConnectionNameEnum connectionNameEnum)
{
SqlConnection connection = null;
connection = new SqlConnection(
System.Configuration.ConfigurationManager.
ConnectionStrings[connectionNameEnum.ToString()].ConnectionString);
connection.Open();
return(connection);
}
public List <dynamic> RunSpReturnDynamic(ConnectionNameEnum connectionNameEnum, string storeProcureName, int pageIndex = 0, int pageLength = 0)
{
var param = Parameters.Clone(); //.ToDictionary(x => x.Key, x => x.Value);
Parameters.Clear();
List <dynamic> list = new List <dynamic>();
SqlCommand com;
com = connectionNameEnum == ConnectionNameEnum.DirectConnectionString ?
new GeneralMethods().MakeSqlCommand(_ConnectionString, storeProcureName, param, ConnectionTimeout) :
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;
}
Parallel.ForEach(DynamicParallelDataReader(result, schemaTable),
(data) =>
{
list.Add(data);
});
}
}
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);
}
/// <summary>
/// Makes the SQL command.
/// </summary>
/// <param name="connectionNameEnum">The connection name enum.</param>
/// <param name="storeProcureName">Name of the store procure.</param>
/// <param name="parameters">The parameters.</param>
/// <returns></returns>
public static SqlCommand MakeSqlCommand(ConnectionNameEnum connectionNameEnum, string storeProcureName, Dictionary <string, object> parameters)
{
var sqlCommand = new SqlCommand {
CommandType = CommandType.StoredProcedure, CommandText = storeProcureName
};
foreach (var r in parameters)
{
sqlCommand.Parameters.Add(new SqlParameter(r.Key, r.Value));
}
sqlCommand.Connection = ConnectionManager.GetConnection(connectionNameEnum);
return(sqlCommand);
}
/// <summary>
/// Makes the SQL command.
/// </summary>
/// <param name="connectionNameEnum">The connection name enum.</param>
/// <param name="storeProcureName">Name of the store procure.</param>
/// <param name="parameters">The parameters.</param>
/// <param name="connectionTimeOut"></param>
/// <returns></returns>
internal SqlCommand MakeSqlCommand(ConnectionNameEnum connectionNameEnum, string storeProcureName, List <SqlParameter> parameters, int connectionTimeOut)
{
var sqlCommand = new SqlCommand {
CommandType = CommandType.StoredProcedure, CommandText = storeProcureName
};
sqlCommand.Parameters.AddRange(parameters.ToArray());
//foreach (var r in parameters)
//{
// sqlCommand.Parameters.Add(new SqlParameter(r.Key, r.Value));
//}
sqlCommand.Connection = new ConnectionManager().GetConnection(connectionNameEnum);
sqlCommand.CommandTimeout = connectionTimeOut;
return(sqlCommand);
}
/// <summary>
/// Runs the object list returned sp.
/// </summary>
/// <typeparam name="T">The model which sp result will be converted to it then they will be converted to object</typeparam>
/// <param name="connectionNameEnum">The connection name enum.</param>
/// <param name="storeProcureName">Name of the store procure.</param>
/// <returns>
/// The list of object. object is parent of T type
/// </returns>
/// <exception>SqlSPException ex
/// <cref>SqlSPException</cref>
/// </exception>
public static List <object> RunObjectListReturnedSp <T>(ConnectionNameEnum connectionNameEnum, string storeProcureName)
{
List <object> list;
try
{
var innerList = RunSp <T>(connectionNameEnum, storeProcureName);
list = innerList.Cast <object>().ToList();
}
catch (ShonizSqlSpException ex)
{
Parameters.Clear();
throw;
}
return(list);
}
/// <summary>
/// Makes the SQL command.
/// </summary>
/// <param name="connectionNameEnum">The connection name enum.</param>
/// <param name="query"></param>
/// <param name="connectionTimeOut"></param>
/// <returns></returns>
internal static SqlCommand MakeQuerySqlCommand(ConnectionNameEnum connectionNameEnum, string query, int connectionTimeOut, List <SqlParameter> parameters = null)
{
var sqlCommand = new SqlCommand
{
CommandText = query,
Connection = new ConnectionManager().GetConnection(connectionNameEnum)
};
if (parameters != null)
{
sqlCommand.Parameters.AddRange(parameters.ToArray());
}
sqlCommand.CommandTimeout = connectionTimeOut;
return(sqlCommand);
}
public List <T> RunQuery <T>(ConnectionNameEnum connectionNameEnum, string query)
{
List <T> list;
SqlCommand com;
if (connectionNameEnum == ConnectionNameEnum.DirectConnectionString)
{
com = GeneralMethods.MakeQuerySqlCommand(_ConnectionString, query, ConnectionTimeout, Parameters);
}
else
{
com = GeneralMethods.MakeQuerySqlCommand(connectionNameEnum, query, ConnectionTimeout, Parameters);
}
using (var result = com.ExecuteReader())
{
try
{
if (typeof(T).IsValueType || "".GetType() == typeof(T))
{
list = new List <T>();
Parallel.ForEach(SimpleParallelDataReader <T>(result), (data) =>
{
list.Add(data);
});
return(list);
}
list = new GeneralMethods().ToViewModel <T>(result);
}
catch (DataException)
{
Parameters.Clear();
throw;
}
finally
{
ConnectionManager.ReleaseConnection(com.Connection);
}
}
return(list);
}
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);
}
/// <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>
/// <returns>The list of T object type</returns>
/// <exception>
/// <cref>SqlSpException ex</cref>
/// </exception>
public static List <T> RunSp <T>(ConnectionNameEnum connectionNameEnum, string storeProcureName)
{
List <T> list;
var com = SqlGeneralMethods.MakeSqlCommand(connectionNameEnum, storeProcureName, Parameters);
using (var result = com.ExecuteReader())
{
try
{
list = SqlGeneralMethods.ToViewModel <T>(result);
}
catch (ShonizSqlSpException ex)
{
Parameters.Clear();
throw;
}
finally
{
ConnectionManager.ReleaseConnection(com.Connection);
Parameters.Clear();
}
}
return(list);
}
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);
}
