public void ReadNorthwindSchema() { var connectionString = ConnectionStrings.Northwind; DatabaseSchema schema; //using the newer Microsoft Sql client using (var connection = new Microsoft.Data.SqlClient.SqlConnection(connectionString)) { try { connection.ConnectionString = connectionString; connection.Open(); } catch (Exception exception) { Assert.Inconclusive("Cannot access database for provider Microsoft.Data.SqlClient message= " + exception.Message); } var dbReader = new DatabaseReader(connection); dbReader.Owner = "dbo"; dbReader.ReadAll(); schema = dbReader.DatabaseSchema; } //password is removed in SqlServer 2017 //Assert.AreEqual(ConnectionStrings.Northwind, schema.ConnectionString, "Connection string is in the schema"); Assert.IsNotNull(schema.ConnectionString, "Connection string is in the schema"); Assert.AreEqual("dbo", schema.Owner, "Schema/owner is in the schema"); }
public UnitTestHelpers() { _configPath = DirectoryLocator.GetTargetConfigurationPath(); var msSqlPath = Path.Combine(DirectoryLocator.GetTargetProjectPath(typeof(Program)), @"..\..\MsSqlDb"); // For the unit tests we _tenantContext = new TenantContext { DbContext = { Catalog = "LeagueIntegration", Schema = "dbo", ConnectionKey = "dummy" } }; _tenantContext.DbContext.ConnectionString = $"Server=(LocalDB)\\MSSQLLocalDB;AttachDbFilename={msSqlPath}\\LeagueIntegrationTest.mdf;Database={_tenantContext.DbContext.Catalog};Integrated Security=true"; // Make sure we can connect to the database using (var connection = new Microsoft.Data.SqlClient.SqlConnection(_tenantContext.DbContext.ConnectionString)) try { connection.Open(); } finally { connection.Close(); } InitializeLlBlGenPro(); }
public async Task <IActionResult> PromoteStudents(EnrStud input) { SqlConnection conn = new SqlConnection(_connectionString); SqlCommand com = new SqlCommand(); com.Connection = conn; com.CommandText = @"select e.IdEnrollment, e.Semester, e.StartDate, s.IdStudy, s.Name from Enrollment e left join Studies s on s.IdStudy = e.IdStudy where s.Name = @studyName and e.Semester = @semester"; com.Parameters.AddWithValue("studyName", input.Studies); com.Parameters.AddWithValue("semester", input.Semester); conn.Open(); SqlDataReader dataReader = await com.ExecuteReaderAsync(); await dataReader.ReadAsync(); Enrollment enr = new Enrollment { IdEnrollment = int.Parse(dataReader["IdEnrollment"].ToString()), Semester = int.Parse(dataReader["Semester"].ToString()), StartDate = DateTime.Parse(dataReader["StartDate"].ToString()), Study = new Study { IdStudy = int.Parse(dataReader["IdStudy"].ToString()), Name = dataReader["Name"].ToString(), } }; if (enr == null) { return(NotFound()); } conn = new SqlConnection(_connectionString); com = new SqlCommand(); com.Connection = conn; com.CommandType = System.Data.CommandType.StoredProcedure; com.CommandText = "PromoteStudents"; com.Parameters.AddWithValue("studies", input.Studies); com.Parameters.AddWithValue("semester", input.Semester); conn.Open(); await com.ExecuteNonQueryAsync(); return(StatusCode(200)); }
public int Save(PCAxis.Query.SavedQuery query, int?id) { query.Stats = null; string pxsjson = JsonConvert.SerializeObject(query); using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString)) { conn.Open(); var cmd = new Microsoft.Data.SqlClient.SqlCommand( @"insert into SavedQueryMeta ( DataSourceType, DatabaseId, DataSourceId, [Status], StatusUse, StatusChange, OwnerId, MyDescription, CreatedDate, SavedQueryFormat, SavedQueryStorage, QueryText, Runs, Fails ) values ( @databaseType, @databaseId, @mainTable, 'A', 'P', 'P', 'Anonymous', @title, @creationDate, 'PXSJSON', 'D', @query, 0, 0 ); SELECT @@IDENTITY AS 'Identity';", conn); cmd.Parameters.AddWithValue("databaseType", query.Sources[0].Type); cmd.Parameters.AddWithValue("databaseId", query.Sources[0].DatabaseId); cmd.Parameters.AddWithValue("mainTable", GetMaintable(query.Sources[0])); cmd.Parameters.AddWithValue("title", ""); cmd.Parameters.AddWithValue("creationDate", DateTime.Now); cmd.Parameters.AddWithValue("query", pxsjson); int newid = Convert.ToInt32(cmd.ExecuteScalar()); return(newid); } return(-1); }
protected override IDbConnection GetDbConnection(string connectionString) { var conn = new Microsoft.Data.SqlClient.SqlConnection(connectionString); if (conn.State != ConnectionState.Open) { conn.Open(); } return(conn); }
public bool MarkAsFailed(int id) { using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString)) { conn.Open(); var cmd = new Microsoft.Data.SqlClient.SqlCommand("update SavedQueryMeta set UsedDate = @lastUsed, Runs = Runs + 1, Fails = Fails + 1 where QueryId = @queryId", conn); cmd.Parameters.AddWithValue("queryId", id); cmd.Parameters.AddWithValue("lastUsed", DateTime.Now); return(cmd.ExecuteNonQuery() == 1); } }
public override int CountActiveSessions(string applicationName) { Invariant.Require(applicationName.Length <= this.MaxApplicationNameLength); using var connection = new Microsoft.Data.SqlClient.SqlConnection(DefaultConnectionString); connection.Open(); using var command = connection.CreateCommand(); command.CommandText = $@"SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE program_name = @applicationName"; command.Parameters.AddWithValue("applicationName", applicationName); return((int)command.ExecuteScalar()); }
/// <summary> /// Executes the current collection of registered commands /// </summary> /// <remarks> /// Opens the connection, begins a transaction, initializes all command, then executes each command. Finally commits transaction. /// Upon failure, transaction is rolled-back. Null commands automatically return 1 for execution. /// </remarks> /// <returns>List of integers returned from each registered command</returns> public List <int> Execute() { List <int> retVal = new List <int>(); using (Microsoft.Data.SqlClient.SqlConnection conn = new Microsoft.Data.SqlClient.SqlConnection(_ConnectionString)) { conn.Open(); Microsoft.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction(); try { int initialized = _Commands.Count; for (int i = 0; i < initialized; i++) { if (_Commands[i] != null) { _Commands[i].Initialize(conn, trans); } } for (int i = 0; i < _Commands.Count; i++) { if (_Commands[i] != null) { // This following line allows for chaining. // in other words, a executing command can add more commands. if (i >= initialized) { _Commands[i].Initialize(conn, trans); } retVal.Add(_Commands[i].Execute()); } else { retVal.Add(1); } } trans.Commit(); } catch (Exception) { try { trans.Rollback(); } catch (Exception) { } throw; } finally { _Commands.Clear(); } } return(retVal); }
public PCAxis.Query.SavedQuery Load(int id) { using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString)) { conn.Open(); var cmd = new Microsoft.Data.SqlClient.SqlCommand("select QueryText from SavedQueryMeta where QueryId = @queryId", conn); cmd.Parameters.AddWithValue("queryId", id); string query = cmd.ExecuteScalar() as string; PCAxis.Query.SavedQuery sq = JsonHelper.Deserialize <PCAxis.Query.SavedQuery>(query) as PCAxis.Query.SavedQuery; return(sq); } return(null); }
public IEnumerable <IndicadoresCorporativos> ObterIndicadoresCorporativos(long idProjeto) { var result = new List <IndicadoresCorporativos>(); Exception excSql = null; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); string sql = @"select a.Id, a.Identificador, a.Nome, a.TipoCalculo, case when b.IdIndicador is null then 0 else 1 end Vinculado, B.Id ID2 from Indicador a left join (select IdIndicador, Id from ProjetoEstruturaOrganizacional where IdProjeto = @p1 and Tipo = 2) b on (a.Id = b.IdIndicador) where a.Corporativo = 1 order by a.Nome"; using (var command = new Microsoft.Data.SqlClient.SqlCommand(sql, connection)) { try { command.Parameters.AddWithValue("p1", idProjeto); using var reader = command.ExecuteReader(); while (reader.Read()) { result.Add(new IndicadoresCorporativos { Id = reader.GetInt64(0), Identificador = reader.GetString(1), Nome = reader.GetString(2), TipoCalculo = reader.GetInt32(3), Vinculado = reader.GetInt32(4) == 1, IdProjetoEstruturaOrganizacional = reader.IsDBNull(5) ? null : (long?)reader.GetInt64(5) }); } } catch (Exception exc) { excSql = exc; } } connection.Close(); if (excSql != null) { throw excSql; } return(result); }
public int SmoExecute(string database, string procName, string text) { //https://www.cnblogs.com/long-gengyun/archive/2012/05/25/2517954.html //http://www.smochen.com/detail?aid=1280 //string connectonstring = this.db.Configuration.GetConnectionString("ApiDocConnStr"); string ServerIP = this.db.Configuration.GetConnectionString("ServerIP"); string pwd = this.db.Configuration.GetConnectionString("pwd"); string connectonstring = string.Format("server={0};uid=sa;pwd={1};database={2}", ServerIP, pwd, database); Microsoft.Data.SqlClient.SqlConnection sqlConnection = new Microsoft.Data.SqlClient.SqlConnection(connectonstring); sqlConnection.Open(); ServerConnection server1 = new ServerConnection(sqlConnection); Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(server1); Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database]; if (db.StoredProcedures.Contains(procName)) { text = text.Replace("CREATE PROCEDURE", "ALTER PROCEDURE", System.StringComparison.OrdinalIgnoreCase); } else { text = text.Replace("ALTER PROCEDURE", "CREATE PROCEDURE", System.StringComparison.OrdinalIgnoreCase); } int start = text.IndexOf("USE [") + 5; if (start != -1) { int end = text.IndexOf("]", start); if (end != -1) { string dbName = text.Substring(start, end - start); text = text.Replace(dbName, database); } } int i = server.ConnectionContext.ExecuteNonQuery(text); sqlConnection.Close(); return(1); }
private void CorrigirOrdens(long idSuperior) { var itens = Filter(item => item.IdSuperior == idSuperior).OrderBy(it => it.Ordem).ToList(); var strBuilder = new StringBuilder(); short ordem = 0; foreach (var it in itens) { ordem++; if (ordem != it.Ordem) { strBuilder.AppendLine($"update ProjetoEstruturaOrganizacional set Ordem = {ordem} where Id = {it.Id};"); } } if (strBuilder.Length > 0) { Exception excSql = null; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); var transaction = connection.BeginTransaction(); using (var command = new Microsoft.Data.SqlClient.SqlCommand(strBuilder.ToString(), connection, transaction)) { try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception exc) { excSql = exc; transaction.Rollback(); } } connection.Close(); if (excSql != null) { throw excSql; } } }
protected override void AfterUpdate(Indicador oldValue, Indicador newValue) { if (oldValue.Identificador != newValue.Identificador && oldValue.TipoCalculo == TipoCalculo.NaoCalculado) { Exception excSql = null; string termoPesquisa = $"[{oldValue.Identificador}]"; string novoIdentificador = $"[{newValue.Identificador}]"; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); var transaction = connection.BeginTransaction(); string sqlUpdate = $"update Indicador set Formula = replace(Formula, '{termoPesquisa}', '{novoIdentificador}') where TipoCalculo <> 1 and Formula like '%{termoPesquisa}%'"; using (var command = new Microsoft.Data.SqlClient.SqlCommand(sqlUpdate, connection, transaction)) { try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception exc) { excSql = exc; transaction.Rollback(); } } connection.Close(); if (excSql != null) { throw excSql; } } if (oldValue.Corporativo && !newValue.Corporativo) { var lista = projetoEstruturaOrganizacionalRepository.Filter(item => item.Tipo == TipoProjetoEstruturaOrganizacional.Corporativo && item.IdIndicador == newValue.Id); var listaIds = new List <long>(); lista?.ToList()?.ForEach(item => listaIds.Add(item.Id)); projetoEstruturaOrganizacionalRepository.DeleteMany(listaIds); } }
public void AvancarNivel(long id, long idSuperior) { CorrigirOrdens(idSuperior); var itens = Filter(item => item.IdSuperior == idSuperior).OrderBy(it => it.Ordem).ToList(); if (itens.Last().Id == id) { throw new Exception("Item não pode avançar."); } short ordem = itens.First(it => it.Id == id).Ordem; long idAfterElement = itens.First(it => it.Ordem == ordem + 1).Id; Exception excSql = null; using var connection = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); connection.Open(); var transaction = connection.BeginTransaction(); string sql = $"update ProjetoEstruturaOrganizacional set Ordem = {ordem + 1} where Id = {id}; update ProjetoEstruturaOrganizacional set Ordem = {ordem} where Id = {idAfterElement};"; using (var command = new Microsoft.Data.SqlClient.SqlCommand(sql, connection, transaction)) { try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception exc) { excSql = exc; transaction.Rollback(); } } connection.Close(); if (excSql != null) { throw excSql; } }
public static void SaveResults(string connStr, CalcRec.HydrCalcDataRec[] recs, ulong[] edgesIDs, DateTime Calc_Time, Guid Calculation_ID) { var dictO2P = new Dictionary <ulong, Guid>(); using (new StopwatchMs("Загрузка справочника преобразования ID OIS в ID PPM")) using (var conn = new Microsoft.Data.SqlClient.SqlConnection(connStr)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT OIS_ID, PPM_ID FROM OIS_2_PPM WHERE OIS_TABLE_NAME = 'PipeProstoyUchastok'"; using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { dictO2P[Convert.ToUInt64(rdr[0])] = rdr.GetGuid(1); } } } } using (new StopwatchMs("Bulk save into HYDRAULIC_CALCULATION_RESULT")) using (var loader = new Microsoft.Data.SqlClient.SqlBulkCopy(connStr)) { loader.DestinationTableName = "HYDRAULIC_CALCULATION_RESULT"; //loader.BatchSize = 1; var reader = new BulkDataReader <CalcRec.HydrCalcDataRec>(recs, (iEdge, r, vals) => { int i = 0; var pu_id = edgesIDs[iEdge]; vals[i++] = dictO2P.TryGetValue(pu_id, out var g) ? g : Guid.Empty; vals[i++] = Calc_Time; vals[i++] = Calculation_ID; r.GetValues(vals, ref i); }, 44); loader.WriteToServer(reader); } }
public IList <RelatorioFiltroResultado> ObterLancamentosParaRelatorio(RelatorioFiltro filtro) { var builder = new System.Text.StringBuilder(); builder.AppendLine("select e.*, f.Nome, g.Nome, h.Nome from ( "); builder.AppendLine("select a.IdIndicador, a.IdSuperior, a.IdProjeto, b.Ano, b.Mes, c.Nome, c.Identificador, d.Sigla, c.ValorPercentualCriterio, c.ValorPercentualPeso, "); builder.AppendLine("b.ValorMeta, b.ValorRealizado, "); builder.AppendLine("(select IdUsuario from ProjetoEstruturaOrganizacional where Id = a.IdSuperior) Usuario, "); builder.AppendLine("(select IdNivelOrganizacional from ProjetoEstruturaOrganizacional where Id = (select IdSuperior from ProjetoEstruturaOrganizacional where Id = a.IdSuperior)) Cargo "); builder.AppendLine("from ProjetoEstruturaOrganizacional a "); builder.AppendLine("inner join IndicadorLancamento b on (a.IdIndicador = b.IdIndicador and a.IdProjeto = b.IdProjeto) "); builder.AppendLine("inner join Indicador c on (a.IdIndicador = c.Id) "); builder.AppendLine("inner join UnidadeMedida d on (c.IdUnidadeMedida = d.Id) "); builder.AppendLine("where a.Tipo = 7 "); builder.AppendLine($"and b.Ano >= {filtro.AnoInicial} and b.Ano <= {filtro.AnoFinal} "); builder.AppendLine($"and b.Mes >= {filtro.MesInicial} and b.Mes <= {filtro.MesFinal}) e "); builder.AppendLine("inner join Usuario f on (e.Usuario = f.Id) "); builder.AppendLine("inner join NivelOrganizacional g on (e.Cargo = g.Id) "); builder.AppendLine("inner join Projeto h on (e.IdProjeto = h.Id) "); builder.AppendLine("where 1 = 1 "); if (filtro.IdProjeto.HasValue) { builder.AppendLine($"and e.IdProjeto = {filtro.IdProjeto.Value} "); } if (filtro.IdIndicador.HasValue) { builder.AppendLine($"and e.IdIndicador = {filtro.IdIndicador.Value} "); } if (filtro.IdUsuario.HasValue) { builder.AppendLine($"and e.Usuario = {filtro.IdUsuario.Value} "); } if (filtro.IdCargo.HasValue) { builder.AppendLine($"and e.Cargo = {filtro.IdCargo.Value} "); } using var context = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); context.Open(); using var command = new Microsoft.Data.SqlClient.SqlCommand(builder.ToString(), context); using var reader = command.ExecuteReader(); var result = new List <RelatorioFiltroResultado>(); while (reader.Read()) { result.Add(new RelatorioFiltroResultado { IdIndicador = reader.GetInt64(0), IdSuperior = reader.GetInt64(1), IdProjeto = reader.GetInt64(2), Ano = reader.GetInt32(3), Mes = reader.GetInt32(4), NomeIndicador = reader.GetString(5), Identificador = reader.GetString(6), UnidadeMedida = reader.GetString(7), ValorPercentualCriterio = reader.GetDecimal(8), ValorPercentualPeso = reader.GetDecimal(9), ValorMeta = reader.GetDecimal(10), ValorRealizado = reader.GetDecimal(11), IdUsuario = reader.GetInt64(12), IdCargo = reader.GetInt64(13), NomeUsuario = reader.GetString(14), NomeCargo = reader.GetString(15), NomeProjeto = reader.GetString(16) }); } try { context.Close(); } catch { } return(result); }
static void Main(string[] args) { { var lstnr = new TextLogTraceListener("Trace.log"); NetCalc.Logger.Listeners.Add(lstnr); System.Diagnostics.Trace.Listeners.Add(lstnr); } //OracleConfiguration.TraceFileLocation = @"C:\temp\traces"; //OracleConfiguration.TraceLevel = 6; var CalcBeg_Time = DateTime.UtcNow; CalcRec.HydrCalcDataRec[] edgeRec; ulong[] edgeOisPipeID = null; try { var cache = new FileCache(nameof(FileCache), new PipeNetCalc.ObjectBinder()) { DefaultPolicy = new CacheItemPolicy() { AbsoluteExpiration = ObjectCache.InfiniteAbsoluteExpiration }, PayloadReadMode = FileCache.PayloadMode.Serializable, PayloadWriteMode = FileCache.PayloadMode.Serializable, }; var wellKinds = NetCalc.WellKind.Oil | NetCalc.WellKind.Water; var cacheKey = $"{nameof(HydrCalcData)}_{wellKinds}"; var data = (HydrCalcData)cache.MyGet(cacheKey); if (data == null) { var nodeWell = LoadWellsData(wellKinds); data = LoadHydrCalcData(nodeWell); cache[cacheKey] = data; //cache.Flush(); } edgeOisPipeID = data.edgeID; var subnets = GetSubnets(data.edges, data.nodes); edgeRec = HydrCalc(data.edges, data.nodes, subnets, data.nodeWell, data.nodeName, $"TGF {wellKinds}"); #if DEBUG { // todo: remove debug code var usedWells = data.nodeWell.Where(p => p.Value.nUsed > 0).ToDictionary(p => p.Key, p => p.Value); var unusedWells = data.nodeWell.Where(p => p.Value.nUsed == 0).ToDictionary(p => p.Key, p => p.Value); //var iNode = Enumerable.Range(0, data.nodes.Length).Where(i => data.nodes[i].Node_ID == "5093939").First(); //var myEdges = data.edges.Where(e => unusedWells.ContainsKey(e.iNodeA) || unusedWells.ContainsKey(e.iNodeB)).ToArray(); var unusedWellNodeIDs = string.Join(",", unusedWells.Select(p => data.nodes[p.Key].Node_ID)); var usedWellNodeIDs = string.Join(",", usedWells.Select(p => data.nodes[p.Key].Node_ID)); } #endif } catch (Exception ex) { Console.WriteLine($"ERROR: {ex.Message}"); edgeRec = null; } return; const string csPPM = @"Data Source = alferovav; Initial Catalog = PPM.Ugansk.Test; User ID = ppm; Password = 123; Pooling = False"; //обходим баг? загрузки "Microsoft.Data.SqlClient.resources" using (var c = new Microsoft.Data.SqlClient.SqlConnection(csPPM)) { c.Open(); c.Close(); } Guid Calc_ID = SaveToDB.CreateCalculationRec(csPPM, CalcBeg_Time, edgeRec); if (edgeRec != null) { SaveToDB.SaveResults(csPPM, edgeRec, edgeOisPipeID, CalcBeg_Time, Calc_ID); } }
// This method gets called by the runtime. Use this method to add services to the container. // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940 public void ConfigureServices(IServiceCollection services) { // required for cookies and session cookies (will throw CryptographicException without) services.AddDataProtection() .SetApplicationName("ClubSite") .SetDefaultKeyLifetime(TimeSpan.FromDays(360)) .PersistKeysToFileSystem( new DirectoryInfo(Path.Combine(WebHostEnvironment.ContentRootPath, "DataProtectionKeys"))) .UseCryptographicAlgorithms(new AuthenticatedEncryptorConfiguration() { EncryptionAlgorithm = EncryptionAlgorithm.AES_256_CBC, ValidationAlgorithm = ValidationAlgorithm.HMACSHA256 }); // Make sure we can connect to the database using (var connection = new Microsoft.Data.SqlClient.SqlConnection(Configuration.GetConnectionString("VolleyballClub"))) try { connection.Open(); } finally { connection.Close(); } services.AddMemoryCache(); // Adds a default in-memory cache implementation // Custom ClubSite db context services.AddDbContext <Data.ClubDbContext>((sp, options) => options.UseSqlServer(Configuration.GetConnectionString("VolleyballClub"))); // Piranha service setup services.AddPiranha(svcBuilder => { svcBuilder.AddRazorRuntimeCompilation = WebHostEnvironment.IsDevelopment(); svcBuilder.UseCms(); svcBuilder.UseFileStorage(naming: Piranha.Local.FileStorageNaming.UniqueFolderNames); svcBuilder.UseImageSharp(); svcBuilder.UseManager(); // https://localhost:44306/manager/ initial user: admin, pw: password svcBuilder.UseTinyMCE(); svcBuilder.UseMemoryCache(); svcBuilder.UseEF <SQLServerDb>(db => db.UseSqlServer(Configuration.GetConnectionString("VolleyballClub"))); svcBuilder.UseIdentityWithSeed <IdentitySQLServerDb>(db => db.UseSqlServer(Configuration.GetConnectionString("VolleyballClub"))); }); // MUST be before AddMvc! services.AddSession(options => { options.IdleTimeout = TimeSpan.FromMinutes(60); options.Cookie.HttpOnly = true; options.Cookie.Name = ".sid"; options.Cookie.IsEssential = true; }); services.AddRazorPages().AddPiranhaManagerOptions(); services.AddMvc() .AddSessionStateTempDataProvider() .AddMvcOptions(options => { options.ModelBindingMessageProvider.SetValueMustNotBeNullAccessor(x => string.Format(Resources.ModelBindingMessageResource.ValueMustNotBeNull)); options.ModelBindingMessageProvider.SetAttemptedValueIsInvalidAccessor((x, val) => string.Format(Resources.ModelBindingMessageResource.AttemptedValueIsInvalid, x, val)); options.ModelBindingMessageProvider.SetValueIsInvalidAccessor(x => string.Format(Resources.ModelBindingMessageResource.ValueIsInvalid, x)); options.ModelBindingMessageProvider.SetValueMustBeANumberAccessor(x => string.Format(Resources.ModelBindingMessageResource.ValueMustBeANumber, x)); options.ModelBindingMessageProvider.SetMissingKeyOrValueAccessor(() => Resources.ModelBindingMessageResource.MissingKeyOrValue); }) .AddControllersAsServices(); services.AddHttpsRedirection(options => { options.RedirectStatusCode = StatusCodes.Status301MovedPermanently; }); services.Configure <ConfigurationPoco.MailSettings>( Configuration.GetSection(nameof(ConfigurationPoco.MailSettings)) ?? throw new ArgumentNullException( $"Configuration section '{nameof(ConfigurationPoco.MailSettings)}' not found.")); services.AddTransient <Services.IMailService, Services.MailService>(); }
public async Task <IActionResult> registerStudent(StdEnr input) { SqlConnection conn = new SqlConnection(_connectionString); SqlCommand com = new SqlCommand(); Study study; { com.Connection = conn; com.CommandText = "select IdStudy, Name from Studies where Name = @name"; com.Parameters.AddWithValue("name", input.Studies); conn.Open(); try { SqlDataReader dataReader = await com.ExecuteReaderAsync(); await dataReader.ReadAsync(); study = new Study { IdStudy = int.Parse(dataReader["IdStudy"].ToString()), Name = dataReader["Name"].ToString() }; if (study == null) { return(BadRequest()); } } catch { return(null); } } conn = new SqlConnection(_connectionString); com = new SqlCommand(); conn.Open(); SqlTransaction transaction = conn.BeginTransaction(); com.Connection = conn; com.Transaction = transaction; com.CommandText = @"declare @enrollmentId int select @enrollmentId = e.IdEnrollment from Enrollment e left join Studies s on e.IdStudy = s.IdStudy where e.IdStudy = @studyId and e.Semester = 1 if @enrollmentId is null begin select @enrollmentId = max(IdEnrollment) + 1 from Enrollment insert into Enrollment values (@enrollmentId, 1, @studyId, getdate()); end insert into Student values (@index, @firstName, @lastName, @birthDate, @enrollmentId)"; com.Parameters.AddWithValue("studyId", study.IdStudy); com.Parameters.AddWithValue("index", input.IndexNumber); com.Parameters.AddWithValue("firstName", input.FirstName); com.Parameters.AddWithValue("lastName", input.LastName); com.Parameters.AddWithValue("birthDate", input.BirthDate); try { await com.ExecuteNonQueryAsync(); await transaction.CommitAsync(); } catch (Exception e) { await transaction.RollbackAsync(); } return(StatusCode(200)); }
public ImpotacaoLancamentosDto GerarLancamentos(IEnumerable <ImpotacaoLancamentos> lancamentos) { var builderErros = new System.Text.StringBuilder(); var builderStatus = new System.Text.StringBuilder(); var comandos = new List <Tuple <string, decimal, decimal, long?> >(); int numeroLinha = 0; foreach (var item in lancamentos) { numeroLinha++; var erros = new System.Text.StringBuilder(); var projeto = databaseContext.Projeto.FirstOrDefault(it => it.Id == item.IdProjeto); if (projeto is null) { erros.Append(" O ID de projeto não existe. "); } var dataAtual = DateTime.UtcNow.Date; if (!projeto.Ativo || projeto.DataInicio.Date > dataAtual || projeto.DataTermino < dataAtual) { erros.Append(" O projeto deve estar ativo e dentro da vigência atual. "); } var indicador = databaseContext.Indicador.FirstOrDefault(it => it.Identificador == item.Identificador); if (indicador is null) { erros.Append(" O identificador não existe. "); } if (erros.Length > 1) { builderErros.AppendLine($"A linha {numeroLinha} está com problema(s): '{erros.ToString()}'."); } else if (builderErros.Length == 0) { var lancamento = databaseContext.IndicadorLancamentos.FirstOrDefault(it => it.IdProjeto == item.IdProjeto && it.IdIndicador == indicador.Id && it.Ano == item.Ano && it.Mes == item.Mes); decimal valorMeta = 0.00m; decimal valorRealizado = 0.00m; string status = "Valor da meta e valor do realizado foram zerados porque o tipo de cálculo do indicador está definido como 'Meta e realizado calculados'."; if (indicador.TipoCalculo == TipoCalculo.NaoCalculado) { valorMeta = item.ValorMeta; valorRealizado = item.ValorRealizado; status = string.Empty; } else if (indicador.TipoCalculo == TipoCalculo.SomenteMeta) { valorRealizado = item.ValorRealizado; status = "Valor da meta foi zerado porque o tipo de cálculo do indicador está definido como 'Meta calculada'."; } else if (indicador.TipoCalculo == TipoCalculo.SomenteRealizado) { valorMeta = item.ValorMeta; status = "Valor do realizado foi zerado porque o tipo de cálculo do indicador está definido como 'Realizado calculado'."; } if (lancamento is null) { comandos.Add(new Tuple <string, decimal, decimal, long?>($"insert into IndicadorLancamento (IdProjeto, IdIndicador, Ano, Mes, ValorMeta, ValorRealizado) values ({item.IdProjeto}, {indicador.Id}, {item.Ano}, {item.Mes}, @p1, @p2);", valorMeta, valorRealizado, null)); builderStatus.Append($"<p style='color:green'>A linha {numeroLinha} foi <b>inserida</b> com sucesso. (<i style='color:orange'>{status}</i>)</p><br/>").Replace("(<i style='color:orange'></i>)", string.Empty); } else { comandos.Add(new Tuple <string, decimal, decimal, long?>("update IndicadorLancamento set ValorMeta = @p1, ValorRealizado = @p2 where Id = @p3;", valorMeta, valorRealizado, lancamento.Id)); builderStatus.Append($"<p style='color:blue'>A linha {numeroLinha} foi <b>atualizada</b> com sucesso. (<i style='color:orange'>{status}</i>)</p><br/>").Replace("(<i style='color:orange'></i>)", string.Empty); } } } if (builderErros.Length > 1) { return(new ImpotacaoLancamentosDto { Sucesso = false, Mensagem = builderErros.ToString().Replace(Environment.NewLine, "<br/>") }); } string erro = null; using var context = new Microsoft.Data.SqlClient.SqlConnection(databaseContext.Database.GetDbConnection().ConnectionString); context.Open(); using var transaction = context.BeginTransaction(); foreach (var item in comandos) { using var cmd = new Microsoft.Data.SqlClient.SqlCommand(item.Item1, context, transaction); try { cmd.Parameters.AddWithValue("p1", item.Item2); cmd.Parameters.AddWithValue("p2", item.Item3); if (item.Item4.HasValue) { cmd.Parameters.AddWithValue("p3", item.Item4.Value); } cmd.ExecuteNonQuery(); } catch (Exception exc) { erro = exc.Message; break; } } try { if (erro is null) { transaction.Commit(); } else { transaction.Rollback(); } context.Close(); } catch { } if (erro != null) { return(new ImpotacaoLancamentosDto { Sucesso = false, Mensagem = erro }); } return(new ImpotacaoLancamentosDto { Sucesso = true, Mensagem = builderStatus.ToString() }); }