public async Task <IActionResult> CreateLaboratoryBook([FromBody] CreateLaboratoryBookModel createLaboratoryBookModel)
{
try
{
var connectonString = _configuration
.GetConnectionString("LaboratoryBookConnectionString");
var laboratoryBookName = createLaboratoryBookModel.LaboratoryBookName;
var userStatus = HttpContext
.User
.Claims
.First(claim => claim.Type == "UserStatus")
.Value;
var userId = HttpContext
.User
.Claims
.First(claim => claim.Type == "UserId")
.Value;
var userName = HttpContext
.User
.Claims
.First(claim => claim.Type == "UserName")
.Value;
var createLaboratoryBookOptions = new CreateLaboratoryBookOptions
{
ConnectionString = connectonString,
LaboratoryBookName = laboratoryBookName,
UserId = userId,
UserName = userName
};
var user = (Administer)LaboratoryBookHelper.CurrentUser(userStatus);
var result = await user.CreateLaboratoryBook(createLaboratoryBookOptions);
if (result)
{
return(Ok(new { message = $"Laboratory book {laboratoryBookName} was created" }));
}
else
{
throw new Exception("Laboratory book was not created");
}
}
catch (Exception exception)
{
return(StatusCode(StatusCodes.Status500InternalServerError, new { message = exception.Message }));
}
}
public async Task <bool> CreateLaboratoryBook(CreateLaboratoryBookOptions options)
{
var assembly = Assembly.GetExecutingAssembly();
var createLaboratoryBookStream = assembly
.GetManifestResourceStream("LaboratoryBookWebApp.TextFiles.CreateLaboratoryBookTemplate.txt");
string createLaboratoryBookSQLCommands;
//get sql commands from the embedded resource
using (var resourceReader = new StreamReader(createLaboratoryBookStream))
{
createLaboratoryBookSQLCommands = await resourceReader.ReadToEndAsync();
}
var connection = new MySqlConnection(options.ConnectionString);
createLaboratoryBookSQLCommands = createLaboratoryBookSQLCommands
.Replace("test_db", options.LaboratoryBookName);
var sqlCommand = new MySqlCommand(String.Empty, connection);
try
{
await connection.OpenAsync();
//check if book exists
sqlCommand.CommandText = $"SELECT count(*) FROM `db_list` WHERE db_name = '{options.LaboratoryBookName}';";
var checkName = await sqlCommand.ExecuteScalarAsync();
if ((long)checkName > 0)// return false;
{
return(false);
}
//get user_id
sqlCommand.CommandText = $"SELECT `user_id` FROM `users` WHERE user_name = '{options.UserName}';";
var userID = await sqlCommand.ExecuteScalarAsync();
//add database to db_list
sqlCommand.CommandText = $"INSERT INTO `db_list` (`db_name`, `creator_id`) VALUES ('{options.LaboratoryBookName}', '{options.UserId}');";
var result = await sqlCommand.ExecuteNonQueryAsync();
//create all tables
sqlCommand.CommandText = createLaboratoryBookSQLCommands;
result = await sqlCommand.ExecuteNonQueryAsync();
//get db_id
sqlCommand.CommandText = $"SELECT `db_id` FROM `db_list` WHERE `db_name` = '{options.LaboratoryBookName}';";
var bookID = await sqlCommand.ExecuteScalarAsync();
//link creator of database with database
sqlCommand.CommandText = $"INSERT INTO `db_users` (`user_id`, `db_id`, `permission_id`) VALUES ('{options.UserId}', '{bookID}', '4');";
result = await sqlCommand.ExecuteNonQueryAsync();
//add statistics
sqlCommand.CommandText = $"INSERT INTO `statistics` (`db_name`, `user_name`, `time_changed`) " +
$"VALUES ('{options.LaboratoryBookName}', '{options.UserName}', '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}');";
result = await sqlCommand.ExecuteNonQueryAsync();
}
finally
{
await connection.CloseAsync();
sqlCommand?.Dispose();
}
return(true);
}