public long POST([FromBody] KravRow kravB)
{
/*
* Inserts new Krav based on posted data
*/
//Check if entered data are OK
KravRow kravT = ProtectDatabaseKrav(kravB);
//If everything is OK proceed
if (kravT.Check && Login.CheckLogging(kravB.User))
{
string CS = ConfigurationManager.ConnectionStrings["Fordonskontroll"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
string insertQuarry = "INSERT into KravTabell (Krav,Status,Efterkontroll,ExkluderadeBilar) select @Krav, @Status, @Efterkontroll, @Exkluderadebilar";
SqlCommand cmd = new SqlCommand(insertQuarry, con);
SqlParameter paramOriginalKrav = new SqlParameter("@Krav", kravT.Krav);
cmd.Parameters.Add(paramOriginalKrav);
SqlParameter paramOriginalStatus = new SqlParameter("@Status", kravT.Status);
cmd.Parameters.Add(paramOriginalStatus);
SqlParameter paramOriginalEfterkontroll = new SqlParameter("@Efterkontroll", kravT.Efterkontroll);
cmd.Parameters.Add(paramOriginalEfterkontroll);
SqlParameter paramOriginalExkluderadeBilar = new SqlParameter("@ExkluderadeBilar", kravT.ExkluderadeBilar);
cmd.Parameters.Add(paramOriginalExkluderadeBilar);
con.Open();
cmd.ExecuteNonQuery();
return(GetMaximumKravID());
}
}
else
{
return(0);
}
}
public long UKD([FromBody] KravRow kravB)
{
/*
* Updates krav with PUT route
*/
KravRow kravT = ProtectDatabaseKrav(kravB);
//If everything is OK proceed
if (kravT.Check && Login.CheckLogging(kravB.User))
{
string CS = ConfigurationManager.ConnectionStrings["Fordonskontroll"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
string updateQuarry = "update KravTabell set Status = @Status where KravID = @KravID";
SqlCommand cmd = new SqlCommand(updateQuarry, con);
SqlParameter paramOriginalKravId = new SqlParameter("@KravID", kravT.id);
cmd.Parameters.Add(paramOriginalKravId);
SqlParameter paramOriginalStatus = new SqlParameter("@Status", kravT.Status);
cmd.Parameters.Add(paramOriginalStatus);
con.Open();
cmd.ExecuteNonQuery();
return(kravT.id);
}
}
else
{
return(0);
}
}
public List <KravRow> GKL([FromBody] UserRowLog ur)
{
/*
* Returns list of all requirement from Krav table
*/
List <KravRow> krList = new List <KravRow>();
//Connect and retrieve data
if (Login.CheckLogging(ur.User))
{
string CS = ConfigurationManager.ConnectionStrings["Fordonskontroll"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("select * from KravTabell", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
//Get row
KravRow kt = new KravRow();
kt.id = Convert.ToInt64(rdr["KravID"]);
kt.Krav = rdr["Krav"].ToString();
kt.Status = Convert.ToInt32(rdr["Status"]);
kt.Efterkontroll = Convert.ToInt32(rdr["Efterkontroll"]);
kt.ExkluderadeBilar = Convert.ToInt32(rdr["ExkluderadeBilar"]);
//Translate reserved words in column "Krav"
kt.Krav = kt.Krav.Replace("@s@", "select");
kt.Krav = kt.Krav.Replace("@d@", "drop");
kt.Krav = kt.Krav.Replace("@i@", "insert");
kt.Krav = kt.Krav.Replace("@n@", "'");
//Add row to the list
krList.Add(kt);
}
}
}
return(krList);
}
public long PUT([FromBody] KravRow kravB)
{
/*
* Update one krav based on posted data
*/
//Check if entered data are OK
KravRow kravT = ProtectDatabaseKrav(kravB);
//If everything is OK proceed
if (kravT.Check && Login.CheckLogging(kravB.User))
{
string CS = ConfigurationManager.ConnectionStrings["Fordonskontroll"].ConnectionString;
using (SqlConnection con = new SqlConnection(CS))
{
string updateQuarry = "update KravTabell set Krav = @Krav, Status = @Status, Efterkontroll = @Efterkontroll,ExkluderadeBilar = @ExkluderadeBilar where KravID = @KravID";
SqlCommand cmd = new SqlCommand(updateQuarry, con);
SqlParameter paramOriginalKravId = new SqlParameter("@KravID", kravT.id);
cmd.Parameters.Add(paramOriginalKravId);
SqlParameter paramOriginalKrav = new SqlParameter("@Krav", kravT.Krav);
cmd.Parameters.Add(paramOriginalKrav);
SqlParameter paramOriginalStatus = new SqlParameter("@Status", kravT.Status);
cmd.Parameters.Add(paramOriginalStatus);
SqlParameter paramOriginalEfterkontroll = new SqlParameter("@Efterkontroll", kravT.Efterkontroll);
cmd.Parameters.Add(paramOriginalEfterkontroll);
SqlParameter paramOriginalExkluderadeBilar = new SqlParameter("@ExkluderadeBilar", kravT.ExkluderadeBilar);
cmd.Parameters.Add(paramOriginalExkluderadeBilar);
con.Open();
cmd.ExecuteNonQuery();
return(kravT.id);
}
}
else
{
return(0);
}
}
/*
* Methods
*/
private KravRow ProtectDatabaseKrav(KravRow kt)
{
/*
* Translates reserverd words in order to prevent sql injections. It should not happend because it is dropdown list but you never know...
* select => @s@
* drop => @d@
* insert => @i@
* ' =>@'@
*/
//Column KRAV
kt.Krav = kt.Krav.Replace("select", "@s@");
kt.Krav = kt.Krav.Replace("drop", "@d@");
kt.Krav = kt.Krav.Replace("insert", "@i@");
kt.Krav = kt.Krav.Replace("'", "@n@");
if (kt.Status != 1 && kt.Status != 0)
{
kt.Check = false;
}
return(kt);
}
