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