private IActionResult getTemporaryTable()
{
List <TemporaryTableModel> result = new List <TemporaryTableModel> ();
var connString = ConnectionSettings.getConnectionString();
using (var conn = new NpgsqlConnection(connString)) {
conn.Open();
string command = "SELECT * FROM temporary ORDER BY id;";
using (var cmd = new NpgsqlCommand(command, conn))
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
TemporaryTableModel query = new TemporaryTableModel(reader);
result.Add(query);
}
}
}
return(View("AdminPanel", result));
}
private TemporaryTableModel getRowFromTemporaryTable(int id)
{
List <TemporaryTableModel> result = new List <TemporaryTableModel> ();
var connString = ConnectionSettings.getConnectionString();
using (var conn = new NpgsqlConnection(connString)) {
conn.Open();
string command = "SELECT * FROM temporary WHERE id = " + id + "ORDER BY id;";
using (var cmd = new NpgsqlCommand(command, conn))
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
TemporaryTableModel query = new TemporaryTableModel(reader);
result.Add(query);
}
}
}
return(result[0]);
}
private void AcceptRow(string id)
{
var connString = ConnectionSettings.getConnectionString();
TemporaryTableModel sequenceData = getRowFromTemporaryTable(Convert.ToInt32(id));
//dodać do funkcji get niezwracanie uwagi na wielkość liter
int taxonID = sequenceData.getTaxonID(connString);
int speciesID = sequenceData.getSpeciesID(connString, taxonID);
int journalID = sequenceData.getJournalID(connString);
int articleID = sequenceData.getArticleID(connString, journalID);
string insert = "INSERT INTO sequence (article, species, name, genbank, seq, about) VALUES ";
string values = " (" + articleID + ", " + speciesID + ", '" + sequenceData.sequenceName + "', '" + sequenceData.genbank +
"', '" + sequenceData.sequence + "', '" + sequenceData.about + "');";
string command = insert + values;
using (var conn = new NpgsqlConnection(connString)) {
conn.Open();
var cmd = new NpgsqlCommand(command, conn);
cmd.ExecuteNonQuery();
}
DeleteFromTable(id);
}
[Route("SubmitData")] public IActionResult SubmitData([FromForm] TemporaryTableModel sequenceData)
{
var connString = ConnectionSettings.getConnectionString();
Dictionary <string, string> valuesDictionary = new Dictionary <string, string> ();
valuesDictionary.Add("p0", "sequenceData.name");
valuesDictionary.Add("p1", "sequenceData.contact");
valuesDictionary.Add("p2", "sequenceData.sequenceName");
valuesDictionary.Add("p3", "sequenceData.genbank");
valuesDictionary.Add("p4", "sequenceData.sequence");
valuesDictionary.Add("p5", "sequenceData.about");
valuesDictionary.Add("p6", "sequenceData.taxon");
valuesDictionary.Add("p7", "sequenceData.species");
valuesDictionary.Add("p8", "sequenceData.journal");
valuesDictionary.Add("p9", " sequenceData.article");
valuesDictionary.Add("p10", " sequenceData.articleLink");
string columns = "name, contact, seq_name, genbank, seq, about, taxon, species, journal, article_name, article_link";
string values = "";
for (int i = 0; i < valuesDictionary.Count; i++)
{
values += "@p" + i;
if (i != valuesDictionary.Count - 1)
{
values += ",";
}
}
string command = "INSERT INTO temporary (" + columns + ") VALUES (" + values + ");";
string selectCommand = "SELECT genbank FROM sequence WHERE genbank = @p3;";
var results = new List <string> ();
using (var conn = new NpgsqlConnection(connString)) {
conn.Open();
using (var cmd = new NpgsqlCommand(selectCommand, conn)) {
foreach (var pair in valuesDictionary)
{
cmd.Parameters.AddWithValue(pair.Key, pair.Value);
}
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
results.Add(reader.GetString(0));
}
}
}
if (results.Count == 0)
{
using (var cmd = new NpgsqlCommand(command, conn)) {
foreach (var pair in valuesDictionary)
{
cmd.Parameters.AddWithValue(pair.Key, pair.Value);
}
cmd.ExecuteNonQuery();
}
return(RedirectToAction("SubmitMessage", "Home", new { area = "Home" }));
}
else
{
return(RedirectToAction("SubmitFail", "Home", new { area = "Home" }));
}
}
}
