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 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);
}
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);
}
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 DataTable GetFirstTableOfData(string spName, string connectionString,
Dictionary <string, string> parameters = null)
{
var exception = new DataException();
try
{
var dataSet = RunSp(spName, connectionString, parameters);
var defTable = dataSet.Tables[dataSet.Tables.Count - 1];
for (var i = 0; i < defTable.Rows.Count; i++)
{
if (defTable.Rows[i]["TableType"].ToString() == "D")
{
return(dataSet.Tables[i]);
}
if (defTable.Rows[i]["TableType"].ToString() == "S")
{
return(dataSet.Tables[i]);
}
if (defTable.Rows[i]["TableType"].ToString() != "E")
{
continue;
}
exception.Data.Add("ErrorTable", dataSet.Tables[i]);
throw exception;
}
return(null);
}
catch
{
exception.Add(0, ExceptionStoredMessage.NotAppliedFerdoStandard);
throw exception;
}
}
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);
}
public DataSet RunSp(string spName, string connectionString, Dictionary <string, string> parameters = null)
{
var getUserDefinedColumnsQuery = @"select c.Name from sys.table_types tt
inner join sys.columns c on c.object_id = tt.type_table_object_id
where tt.name = 'Parameters' order by c.column_id";
var tableList = new StoreProcdureManagement().RunQuery(connectionString, getUserDefinedColumnsQuery).Tables;
if (tableList.Count < 1)
{
return(new DataSet());
}
var paramTable = new DataTable();
//paramTable.Columns.Add("Type", typeof(string));
//paramTable.Columns.Add("Name", typeof(string));
//paramTable.Columns.Add("Value", typeof(string));
for (var i = 0; i < tableList[0].Rows.Count; i++)
{
paramTable.Columns.Add(tableList[0].Rows[i]["Name"].ToString(), typeof(string));
}
//paramTable.Columns.Add("Type", typeof(string));
//paramTable.Columns.Add("Value", typeof(string));
var connection = new SqlConnection {
ConnectionString = connectionString
};
var command = new SqlCommand(_runSpFullName, connection)
{
CommandType = CommandType.StoredProcedure
};
command.CommandTimeout = 3600;
var row = paramTable.NewRow();
row["Name"] = "_SpName";
row["Value"] = spName;
row["Type"] = "";
paramTable.Rows.Add(row);
if (parameters != null)
{
foreach (var parameter in parameters)
{
row = paramTable.NewRow();
row["Name"] = parameter.Key;
row["Type"] = "";
row["Value"] = parameter.Value;
paramTable.Rows.Add(row);
}
}
command.Parameters.AddWithValue("@params", paramTable);
//var param = new SqlParameter("@params", paramTable);
//command.Parameters.Add(param);
//command.Parameters.Add("@params", SqlDbType.Structured);
//command.Parameters["@params"].Value = paramTable;
try
{
var adapter = new SqlDataAdapter(command);
var dataSet = new DataSet();
adapter.Fill(dataSet);
if (dataSet.Tables.Count > 1)
{
for (var i = 0; i < dataSet.Tables.Count - 1; i++)
{
var tableName = "";
if (dataSet.Tables[dataSet.Tables.Count - 1].Rows[i]["Name"] != null && dataSet.Tables[dataSet.Tables.Count - 1].Rows[i]["Name"].ToString() != "")
{
tableName = dataSet.Tables[dataSet.Tables.Count - 1].Rows[i]["Name"].ToString();
}
else
{
switch (dataSet.Tables[dataSet.Tables.Count - 1].Rows[i]["TableType"].ToString())
{
case "M":
tableName = "Message";
break;
case "S":
tableName = "Schema";
break;
case "E":
var sqlSpException = new DataException();
sqlSpException.Add(dataSet.Tables[i]);
throw sqlSpException;
}
}
dataSet.Tables[i].TableName = tableName;
}
}
dataSet.Tables[dataSet.Tables.Count - 1].TableName = "TableDefination";
return(dataSet);
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
}