private async Task <AdoPatient> FindBySsnAsync(string plainTextSsn)
{
var patient = new AdoPatient();
try
{
using (SqlConnection connection = new SqlConnection(GetConnectionString()))
using (SqlCommand cmd = connection.CreateCommand())
{
await connection.OpenAsync();
cmd.CommandText = @"
SELECT [Id]
, [FirstName]
, [LastName]
, [Ssn]
, [BirthDate]
, [Created]
, [LastModified]
FROM [dbo].[AdoPatients]
WHERE [Ssn]=@Ssn
";
var sqlParameterSsn = cmd.CreateParameter();
sqlParameterSsn.ParameterName = @"@Ssn";
sqlParameterSsn.DbType = DbType.AnsiStringFixedLength;
sqlParameterSsn.Direction = ParameterDirection.Input;
sqlParameterSsn.Value = plainTextSsn;
sqlParameterSsn.Size = 11;
cmd.Parameters.Add(sqlParameterSsn);
using (SqlDataReader sqlDataReader = cmd.ExecuteReader())
{
if (!sqlDataReader.HasRows)
{
return(null);
}
while (sqlDataReader.Read())
{
patient = new AdoPatient
{
Id = Convert.ToInt32(sqlDataReader["Id"]),
Ssn = sqlDataReader["Ssn"].ToString(),
FirstName = sqlDataReader["FirstName"].ToString(),
LastName = sqlDataReader["LastName"].ToString(),
BirthDate = (DateTime)sqlDataReader["BirthDate"],
Created = (DateTime)sqlDataReader["Created"],
LastModified = (DateTime)sqlDataReader["LastModified"],
};
}
}
}
}
catch (Exception ex)
{
Logger.LogError($"Read/Find operation error: {ex}");
}
return(patient);
}
private async Task <AdoPatient> UpdateAsync(AdoPatient entity)
{
try
{
var query = $@"
UPDATE [dbo].[AdoPatients]
SET [FirstName]=@FirstName
, [LastName]=@LastName
, [Ssn]=@Ssn
, [BirthDate]=@BirthDate
, [LastModified]=@LastModified
WHERE [Id]=@Id
";
using (var sqlConnection = new SqlConnection(GetConnectionString()))
using (var sqlCommand = new SqlCommand(query, sqlConnection))
{
sqlCommand.Parameters.AddWithValue("@FirstName", entity.FirstName);
sqlCommand.Parameters.AddWithValue("@LastName", entity.LastName);
var sqlParameterSsn = sqlCommand.CreateParameter();
sqlParameterSsn.ParameterName = @"@Ssn";
sqlParameterSsn.DbType = DbType.AnsiStringFixedLength;
sqlParameterSsn.Direction = ParameterDirection.Input;
sqlParameterSsn.Value = entity.Ssn;
sqlParameterSsn.Size = 11;
sqlCommand.Parameters.Add(sqlParameterSsn);
var sqlParameterBirthdate = sqlCommand.CreateParameter();
sqlParameterBirthdate.ParameterName = @"@BirthDate";
sqlParameterBirthdate.SqlDbType = SqlDbType.Date;
sqlParameterBirthdate.Direction = ParameterDirection.Input;
sqlParameterBirthdate.Value = entity.BirthDate;
sqlCommand.Parameters.Add(sqlParameterBirthdate);
sqlCommand.Parameters.AddWithValue("@LastModified", DateTime.UtcNow);
sqlCommand.Parameters.AddWithValue("@Id", entity.Id);
await sqlConnection.OpenAsync();
await sqlCommand.ExecuteNonQueryAsync();
}
}
catch (Exception ex)
{
Logger.LogError($"Update operation error: {ex}");
}
var updatedPatient = await FindBySsnAsync(entity.Ssn);
return(updatedPatient);
}
private async Task CreateAsync(AdoPatient entity)
{
try
{
using (var sqlConnection = new SqlConnection(GetConnectionString()))
using (var sqlCommand = sqlConnection.CreateCommand())
{
await sqlConnection.OpenAsync();
sqlCommand.CommandText = @"
INSERT INTO [dbo].[AdoPatients]
(
[Ssn]
, [FirstName]
, [LastName]
, [BirthDate]
, [Created]
, [LastModified]
) VALUES (
@SSN
, @FirstName
, @LastName
, @BirthDate
, @CreatedDate
, @LastModifiedDate
);
";
var sqlParameterSsn = sqlCommand.CreateParameter();
sqlParameterSsn.ParameterName = @"@SSN";
sqlParameterSsn.DbType = DbType.AnsiStringFixedLength;
sqlParameterSsn.Direction = ParameterDirection.Input;
sqlParameterSsn.Value = entity.Ssn;
sqlParameterSsn.Size = 11;
sqlCommand.Parameters.Add(sqlParameterSsn);
var sqlParameterFirstName = sqlCommand.CreateParameter();
sqlParameterFirstName.ParameterName = @"@FirstName";
sqlParameterFirstName.DbType = DbType.String;
sqlParameterFirstName.Direction = ParameterDirection.Input;
sqlParameterFirstName.Value = entity.FirstName;
sqlParameterFirstName.Size = 50;
sqlCommand.Parameters.Add(sqlParameterFirstName);
var sqlParameterLastName = sqlCommand.CreateParameter();
sqlParameterLastName.ParameterName = @"@LastName";
sqlParameterLastName.DbType = DbType.String;
sqlParameterLastName.Direction = ParameterDirection.Input;
sqlParameterLastName.Value = entity.LastName;
sqlParameterLastName.Size = 50;
sqlCommand.Parameters.Add(sqlParameterLastName);
var sqlParameterBirthdate = sqlCommand.CreateParameter();
sqlParameterBirthdate.ParameterName = @"@BirthDate";
sqlParameterBirthdate.SqlDbType = SqlDbType.Date;
sqlParameterBirthdate.Direction = ParameterDirection.Input;
sqlParameterBirthdate.Value = entity.BirthDate;
sqlCommand.Parameters.Add(sqlParameterBirthdate);
var sqlParameterCreated = sqlCommand.CreateParameter();
sqlParameterCreated.ParameterName = @"@CreatedDate";
sqlParameterCreated.SqlDbType = SqlDbType.DateTime;
sqlParameterCreated.Direction = ParameterDirection.Input;
sqlParameterCreated.Value = entity.BirthDate;
sqlCommand.Parameters.Add(sqlParameterCreated);
var sqlParameterLastModified = sqlCommand.CreateParameter();
sqlParameterLastModified.ParameterName = @"@LastModifiedDate";
sqlParameterLastModified.SqlDbType = SqlDbType.DateTime;
sqlParameterLastModified.Direction = ParameterDirection.Input;
sqlParameterLastModified.Value = entity.LastModified;
sqlCommand.Parameters.Add(sqlParameterLastModified);
await sqlCommand.ExecuteNonQueryAsync();
}
}
catch (Exception ex)
{
Logger.LogError($"Create operation error: {ex}");
}
}
