public IList <Client> GetAllByStatus(IList <int> idsStatus)
{
var clientes = new List <Client>();
var dbContext = new SQLDbContext();
var SQL = @$ "SELECT * FROM VW_Clientes
WHERE IdStatusAtual IN ({String.Join(',', idsStatus)})
public void Save(RegisterModel model)
{
SQLDbContext dbContext = new SQLDbContext();
dbContext.Registers.Add(model);
dbContext.SaveChanges();
}
public UnitOfWork(
SQLDbContext context,
ICarStateRepository carStateRepository,
ICarTypeRepository carTypeRepository,
IClientTypeRepository clientTypeRepository,
ICarHireRepository carHireRepository,
ICarRepository carRepository,
IClientRepository clientRepository,
INewsRepository newRepository,
ILogsRepository logsRepository,
UserManager <User> userManager,
SignInManager <User> signInManager,
RoleManager <Role> roleManager)
{
this.context = context;
CarStateRepository = carStateRepository;
CarTypeRepository = carTypeRepository;
ClientTypeRepository = clientTypeRepository;
CarHireRepository = carHireRepository;
CarRepository = carRepository;
ClientRepository = clientRepository;
NewRepository = newRepository;
LogsRepository = logsRepository;
UserManager = userManager;
SignInManager = signInManager;
RoleManager = roleManager;
}
public IList <State> GetStates(int countryId)
{
var states = new List <State>();
var dbContext = new SQLDbContext();
string SQL = @"SELECT * FROM Estados WHERE IdPais = @IdPais";
var parametros = new SqlParameter[] {
new SqlParameter("@IdPais", countryId)
};
DataTable dtResult = dbContext.ExecutarConsulta(SQL, parametros);
foreach (DataRow dataRow in dtResult.Rows)
{
var state = new State(
Convert.ToInt32(dataRow["Id"]),
Convert.ToString(dataRow["Estado"]),
Convert.ToInt32(dataRow["IdPais"])
);
states.Add(state);
}
return(states);
}
public IList <City> GetCities(int stateId)
{
var cities = new List <City>();
var dbContext = new SQLDbContext();
string SQL = @"SELECT * FROM Cidades WHERE IdEstado = @IdEstado";
var parametros = new SqlParameter[] {
new SqlParameter("@IdEstado", stateId)
};
DataTable dtResult = dbContext.ExecutarConsulta(SQL, parametros);
foreach (DataRow dataRow in dtResult.Rows)
{
var city = new City(
Convert.ToInt32(dataRow["Id"]),
Convert.ToString(dataRow["Cidade"]),
Convert.ToInt32(dataRow["IdEstado"])
);
cities.Add(city);
}
return(cities);
}
public User GetById(int id)
{
User user = null;
var dbContext = new SQLDbContext();
string SQL = @"SELECT * FROM VW_Usuarios WHERE Id = @Id";
var parametros = new SqlParameter[] {
new SqlParameter("@Id", id)
};
DataTable dtResult = dbContext.ExecutarConsulta(SQL, parametros);
if (dtResult.Rows.Count > 0)
{
DataRow dataRow = dtResult.Rows[0];
user = new User(
Convert.ToInt32(dataRow["Id"]),
dataRow["Nome"].ToString(),
dataRow["Login"].ToString(),
dataRow["Senha"].ToString(),
Convert.ToDateTime(dataRow["DataCadastro"])
);
var roles = _perfilRepository.GetById(user.Id);
user.Roles = roles;
}
return(user);
}
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
using (SQLDbContext context = new SQLDbContext()) { };
}
private void ExecutarProcedureDoFluxo(string nomeProcedure, int clienteId, int userId)
{
var dbContext = new SQLDbContext();
var parametros = new SqlParameter[] {
new SqlParameter("@IdCliente", clienteId),
new SqlParameter("@IdUsuarioResponsavel", userId),
};
dbContext.ExecutarProcedure(nomeProcedure, parametros);
}
public IList <Fluxo> ListarFluxo(DateTime startDate, DateTime endDate, int statusId = 0, string cpf = "", string name = "")
{
var fluxos = new List <Fluxo>();
var dbContext = new SQLDbContext();
SqlParameter[] parametros = new SqlParameter[] {
new SqlParameter("@StartDate", startDate.ToShortDateString()),
new SqlParameter("@EndDate", endDate.AddDays(1).ToShortDateString()),
new SqlParameter("@IdStatus", statusId),
new SqlParameter("@CPF", cpf ?? ""),
new SqlParameter("@Name", name ?? ""),
};
string SQL = "SELECT * FROM VW_Fluxo_Aprovacao WHERE DataCriacao >= @StartDate AND DataCriacao <= @EndDate";
if (statusId > 0)
{
SQL += " AND IdStatus = @IdStatus";
}
if (!string.IsNullOrEmpty(cpf))
{
SQL += " AND CPF = @CPF";
}
if (!string.IsNullOrEmpty(name))
{
SQL += " AND Cliente LIKE '%@Name%'";
}
DataTable dtResult = dbContext.ExecutarConsulta(SQL, parametros);
foreach (DataRow dataRow in dtResult.Rows)
{
var userId = Convert.ToInt32(dataRow["IdUsuario"]);
var user = _userRepository.GetById(userId);
var clientId = Convert.ToInt32(dataRow["IdCliente"]);
var client = _clientRepository.GetById(clientId);
var fluxo = new Fluxo(
client,
user,
status: EnumHelper.StatusIdParaStatus(dataRow["IdStatus"].ToString()),
createDate: Convert.ToDateTime(dataRow["DataCriacao"])
);
fluxos.Add(fluxo);
}
return(fluxos);
}
static void Main(string[] args)
{
string connectionString =
@"Data Source=(localdb)\ProjectsV13;Initial Catalog=17bang;Integrated Security=True;";
DbContextOptionsBuilder <BLL.repoistory.SQLDbContext> optionsBuilder = new DbContextOptionsBuilder <BLL.repoistory.SQLDbContext>();
optionsBuilder
.UseLoggerFactory(MyLoggerFactory)
.UseSqlServer(connectionString);
DatabaseFacade db = new SQLDbContext().Database;
db.EnsureDeleted(); //如果存在数据库，就删除之//db.EnsureCreated(); 和Migration有可能冲突，不要混合使用
db.Migrate();
RegisterFactory.Create();
}
public User GetByCredentials(string login, string password)
{
try
{
User user = null;
var dbContext = new SQLDbContext();
var parametros = new SqlParameter[] {
new SqlParameter("@Login", login),
new SqlParameter("@Senha", password),
};
DataTable dtResult = dbContext.ExecutarProcedure("SP_Logar", parametros);
if (dtResult.Rows.Count > 0)
{
DataRow dataRow = dtResult.Rows[0];
user = new User(
id: Convert.ToInt32(dataRow["Id"]),
name: dataRow["Nome"].ToString(),
login,
password,
Convert.ToDateTime(dataRow["DataCadastro"])
);
var roles = _perfilRepository.GetById(user.Id);
user.Roles = roles;
}
return(user);
}
catch (SqlException ex)
{
if (ex.Number == 50000)
{
throw new InvalidLoginException(ex.Message, ex);
}
throw ex;
}
}
public void Delete(int userId)
{
try
{
var dbContext = new SQLDbContext();
var parametros = new SqlParameter[] {
new SqlParameter("@Id", userId),
};
dbContext.ExecutarProcedure("SP_DeletarUsuario", parametros);
}
catch (SqlException ex)
{
if (ex.Number > 50000)
{
throw new InvalidClientException(ex.Message);
}
throw ex;
}
}
public IList <int> ListarIdsClientesIndisponiveis(int userId)
{
var ids = new List <int>();
var dbContext = new SQLDbContext();
SqlParameter[] parametros = new SqlParameter[] {
new SqlParameter("@IdUsuario", userId)
};
string SQL = @"SELECT * FROM FC_Listar_IdsClientes_Indisponiveis(@IdUsuario)";
DataTable dtResult = dbContext.ExecutarConsulta(SQL, parametros);
foreach (DataRow dataRow in dtResult.Rows)
{
ids.Add(Convert.ToInt32(dataRow["IdCliente"]));
}
return(ids);
}
public void Insert(User user)
{
try
{
var dbContext = new SQLDbContext();
var parametros = new SqlParameter[] {
new SqlParameter("@Nome", user.Name),
new SqlParameter("@Login", user.Login),
new SqlParameter("@Senha", user.Password),
};
dbContext.ExecutarProcedure("SP_InserirUsuario", parametros);
}
catch (SqlException ex)
{
if (ex.Number > 50000)
{
throw new InvalidClientException(ex.Message);
}
throw ex;
}
}
public IList <User> GetAll()
{
var users = new List <User>();
var dbContext = new SQLDbContext();
var SQL = @$ "SELECT * FROM VW_Usuarios ORDER BY Id DESC";
DataTable dtresult = dbContext.ExecutarConsulta(SQL);
foreach (DataRow dataRow in dtresult.Rows)
{
var user = new User(
Convert.ToInt32(dataRow["Id"]),
dataRow["Nome"].ToString(),
dataRow["Login"].ToString(),
dataRow["Senha"].ToString(),
Convert.ToDateTime(dataRow["DataCadastro"])
);
users.Add(user);
}
return(users);
}
public IList <Country> GetCountries()
{
var countries = new List <Country>();
var dbContext = new SQLDbContext();
string SQL = @"SELECT * FROM Paises";
DataTable dtResult = dbContext.ExecutarConsulta(SQL);
foreach (DataRow dataRow in dtResult.Rows)
{
var country = new Country(
Convert.ToInt32(dataRow["Id"]),
Convert.ToString(dataRow["Pais"]),
Convert.ToString(dataRow["Sigla"])
);
countries.Add(country);
}
return(countries);
}
public bool VerifyIfUserInFluxo(int clientId, int userId)
{
try
{
var dbContext = new SQLDbContext();
var parametros = new SqlParameter[] {
new SqlParameter("@IdCliente", clientId),
new SqlParameter("@IdUsuario", userId)
};
dbContext.ExecutarProcedure("SP_VerificarSeUsuarioEstaEnvolvidoNoFluxo", parametros);
return(false);
}
catch (SqlException ex)
{
if (ex.Number > 50000)
{
return(true);
}
throw ex;
}
}
public SQLLogsRepository(SQLDbContext dbContext) : base(dbContext)
{
}
public ClientesController(SQLDbContext sQLDbContext)
{
_sQLDbContext = sQLDbContext;
}
public SQLNewsRepository(SQLDbContext myDBContext) : base(myDBContext)
{
}
public SQLCarRepository(SQLDbContext myDBContext, ISortHelper <Car> sortHelper) : base(myDBContext)
{
this.sortHelper = sortHelper;
}
public SQLBaseRepository(SQLDbContext context)
{
_context = context;
}
