/// <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);
}
/// <include file='../../../../doc/snippets/Microsoft.Data.SqlClient/SqlCommand.xml' path='docs/members[@name="SqlCommand"]/ctor[@name="cmdTextStringAndSqlConnectionAndSqlTransactionAndSqlCommandColumnEncryptionSetting"]/*'/>
public SqlCommand(string cmdText, Microsoft.Data.SqlClient.SqlConnection connection, Microsoft.Data.SqlClient.SqlTransaction transaction, Microsoft.Data.SqlClient.SqlCommandColumnEncryptionSetting columnEncryptionSetting)
{
}
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));
}
