public IActionResult Getlist(string listName) { var connectionString = _configuration.GetConnectionString("LaboratoryBookConnectionString"); var laboratoryBook = HttpContext.User.Claims.First(claim => claim.Type == "LaboratoryBook").Value; var columnToGet = new ColumnToGet(); switch (listName) { case "regimes": columnToGet = ColumnToGet.Regime; break; case "operators": columnToGet = ColumnToGet.Operator; break; case "materials": columnToGet = ColumnToGet.Material; break; case "substrates": columnToGet = ColumnToGet.Substrate; break; case "permissionIDs": columnToGet = ColumnToGet.PermissionID; break; default: return(null); } var columnList = LaboratoryBookHelper.GetColumnList(connectionString, laboratoryBook, columnToGet); return(Content(columnList)); }
public static string GetColumnList(string connectionString, string laboratoryBookName, ColumnToGet columnToGet) { var connection = new MySqlConnection(connectionString); string commandString = null; switch (columnToGet) { case ColumnToGet.Material: commandString = $"SELECT `material` FROM materials_{laboratoryBookName};"; break; case ColumnToGet.Operator: commandString = "SELECT `user_name` FROM `db_users` " + "JOIN `users` ON `users`.`user_id` = `db_users`.`user_id` " + "JOIN `db_list` ON `db_list`.`db_id` = `db_users`.`db_id` " + $"WHERE `db_name`= '{laboratoryBookName}';"; break; case ColumnToGet.PermissionID: commandString = "SELECT `permission_id` FROM `permission`;"; break; case ColumnToGet.Regime: commandString = $"SELECT `regime` FROM regimes_{laboratoryBookName};"; break; case ColumnToGet.Substrate: commandString = $"SELECT `substrate` FROM substrates_{laboratoryBookName};"; break; } var sqlCommand = new MySqlCommand(commandString, connection); var requestedDataTable = new DataTable(); try { connection.Open(); var reader = sqlCommand.ExecuteReader(); requestedDataTable.Load(reader); } finally { connection.Close(); sqlCommand?.Dispose(); } var resultBuilder = new StringBuilder(); foreach (DataRow dr in requestedDataTable.Rows) { resultBuilder.Append(dr[0].ToString() + ";"); } var result = resultBuilder.ToString(); if (string.IsNullOrEmpty(result)) { return(string.Empty); } return(result.Remove(result.Length - 1)); }
public static ObservableCollection <object> SetColumnList(string laboratoryBookName, ColumnToGet columnToGet) { var connectionString = ConfigurationManager.ConnectionStrings["cs_login"].ConnectionString; var connection = new MySqlConnection(connectionString); string commandString = null; switch (columnToGet) { case ColumnToGet.Material: commandString = $"SELECT `material` FROM materials_{laboratoryBookName};"; break; case ColumnToGet.Operator: commandString = "SELECT `user_name` FROM `db_users` " + "JOIN `users` ON `users`.`user_id` = `db_users`.`user_id` " + "JOIN `db_list` ON `db_list`.`db_id` = `db_users`.`db_id` " + $"WHERE `db_name`= '{laboratoryBookName}';"; break; case ColumnToGet.PermissionID: commandString = "SELECT `permission_id` FROM `permission`;"; break; case ColumnToGet.Regime: commandString = $"SELECT `regime` FROM regimes_{laboratoryBookName};"; break; case ColumnToGet.Substrate: commandString = $"SELECT `substrate` FROM substrates_{laboratoryBookName};"; break; } var sqlCommand = new MySqlCommand(commandString, connection); var requestedDataTable = new DataTable(); try { connection.Open(); var reader = sqlCommand.ExecuteReader(); requestedDataTable.Load(reader); } catch (Exception exception) { MessageBox.Show ( exception.Message, "Load error", MessageBoxButton.OK, MessageBoxImage.Error ); } finally { connection.Close(); sqlCommand?.Dispose(); } var result = new ObservableCollection <object>(); foreach (DataRow dr in requestedDataTable.Rows) { result.Add(dr[0]); } return(result); }