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); }