public static List <T> GetListFromSqlText <T>(this DatabaseDatacontex context, string sql) where T : class, new()
{
using (var connection = context.Database.GetDbConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sql;
using (SqlDataAdapter da = new SqlDataAdapter(command.CommandText, command.Connection.ConnectionString))
{
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
// Detach the SqlParameters from the command object, so they can be used again
command.Parameters.Clear();
// Close connection
connection.Close();
// Return the dataset
return(DataTableToList <T>(ds.Tables[0]));
}
}
}
}
public static T GetDataFromStoreProcedure <T>(this DatabaseDatacontex context, string sp, params object[] parameters) where T : class, new()
{
using (var connection = context.Database.GetDbConnection())
{
try
{
if (connection is SqlConnection)
{
var connect = connection as SqlConnection;
connect.Open();
using (var command = connect.CreateCommand())
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = sp;
if (parameters != null && parameters.Length > 0)
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(command.Connection.ConnectionString, sp);
AssignParameterValues(commandParameters, parameters);
AttachParameters(command, commandParameters);
}
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
// Detach the SqlParameters from the command object, so they can be used again
command.Parameters.Clear();
// Close connection
connection.Close();
// Return the dataset
return(DataRowToItem <T>(ds.Tables[0].Rows[0]));
}
}
}
return(null);
}
catch (Exception ex)
{
return(null);
}
}
}
public static int ExecuteFromStoreProcedure(this DatabaseDatacontex context, string sql)
{
int result = -1;
using (var connection = context.Database.GetDbConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sql;
result = command.ExecuteNonQuery();
}
connection.Close();
}
return(result);
}
public static int IntFromSQL(this DatabaseDatacontex context, string sql)
{
int count;
using (var connection = context.Database.GetDbConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
string result = command.ExecuteScalar().ToString();
int.TryParse(result, out count);
}
connection.Close();
}
return(count);
}
public Repository(DatabaseDatacontex dataContext, ILogger <T> logger)
{
_dataContext = dataContext;
_logger = logger;
}
public ProductCategoryService(DatabaseDatacontex context, ILogger <T> logger) : base(context, logger)
{
}
public BaseServices(DatabaseDatacontex context, ILogger <T> logger)
{
_repository = new Repository <T>(context, logger);
}
public Repository()
{
_dataContext = new DatabaseDatacontex();
}
