public ActionResult <TestDone> POST([FromBody] TestDone TestDone)
{
List <TestDone> list = new List <TestDone>();
try
{
DateTime myDate = DateTime.Now;
string myDateString = myDate.ToString("yyyy-MM-dd HH:mm:ss");
conn.Open(); //打开数据库连接
MySqlCommand cmd;
cmd = conn.CreateCommand();
cmd.CommandText = $"INSERT INTO interviewee ( name , answer , test_time , timestamp ) VALUES (@name , @answer , @test_time , @timestamp )";
cmd.Parameters.AddWithValue("@name", TestDone.NAME.ToString());
cmd.Parameters.AddWithValue("@answer", TestDone.ANSWER.ToString());
cmd.Parameters.AddWithValue("@test_time", TestDone.TESTTIME.ToString());
cmd.Parameters.AddWithValue("@timestamp", myDateString);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
//⑦关闭连接
conn.Close();
}
return(this.Ok(new ActionResult <TestDone>(TestDone)));
}
public ActionResult <TestDone> POST([FromQuery] long count, [FromBody] TestDone TestDone)
{
List <TestDone> list = new List <TestDone>();
try
{
conn.Open();
MySqlCommand cmd;
cmd = conn.CreateCommand();
cmd.CommandText = $"UPDATE interviewee SET status = '1', score = @score, note = @note WHERE id = @count";
cmd.Parameters.AddWithValue("@score", TestDone.SCORE.ToString());
cmd.Parameters.AddWithValue("@note", TestDone.NOTE.ToString());
cmd.Parameters.AddWithValue("@count", count.ToString());
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(this.Ok(new ActionResult <TestDone>(TestDone)));
}
public ActionResult <List <TestDone> > Get()
{
List <TestDone> list = new List <TestDone>();
MySqlDataReader reader = null;
try
{
conn.Open(); //打开数据库连接
cmd = new MySqlCommand("SELECT * FROM interview.interviewee", conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
TestDone answer = new TestDone();
answer.Id = reader["id"].ToString();
answer.NAME = reader["name"].ToString();
answer.ANSWER = reader["answer"].ToString();
answer.TESTTIME = reader["test_time"].ToString();
answer.TIMESTAMP = reader["timestamp"].ToString();
answer.STATUS = reader["status"].ToString();
answer.SCORE = reader["score"].ToString();
answer.NOTE = reader["note"].ToString();
list.Add(answer);
}
}
catch (Exception)
{
throw;
}
finally
{
reader.Close();
conn.Close();
}
return(this.Ok(new List <TestDone>(list)));
}
private void SetTestDone(SqlCommand cm, TestDone test)
{
DatabaseHelper.InsertInt32Param("@FacilityId", cm, test.FacilityId);
DatabaseHelper.InsertInt32Param("@InstrumentId", cm, test.InstrumentId);
DatabaseHelper.InsertInt32Param("@TestId", cm, test.TestId);
DatabaseHelper.InsertStringNVarCharParam("@TestType", cm, test.TestType);
DatabaseHelper.InsertInt32Param("@NoTestDone", cm, test.NoTestDone);
DatabaseHelper.InsertInt32Param("@Month", cm, test.Month);
DatabaseHelper.InsertInt32Param("@Year", cm, test.Year);
}
public void SaveTestDone(TestDone test, SqlTransaction tr)
{
string sql = "INSERT INTO TestDone([FacilityId], [InstrumentId], [TestId],[TestType],[NoTestDone],[Month],[Year])";
sql += "VALUES(@FacilityId, @InstrumentId, @TestId, @TestType, @NoTestDone, @Month, @Year) SELECT @@identity";
using (SqlCommand cm = new SqlCommand(sql, DefaultConnection, tr))
{
SetTestDone(cm, test);
test.Id = int.Parse(cm.ExecuteScalar().ToString());
}
}
private TestDone FetchTestDone(SqlDataReader dr)
{
TestDone t = new TestDone()
{
Id = DatabaseHelper.GetInt32("Id", dr),
TestId = DatabaseHelper.GetInt32("TestId", dr),
TestType = DatabaseHelper.GetString("TestType", dr),
FacilityId = DatabaseHelper.GetInt32("FacilityId", dr),
InstrumentId = DatabaseHelper.GetInt32("InstrumentId", dr),
NoTestDone = DatabaseHelper.GetInt32("NoTestDone", dr),
Year = DatabaseHelper.GetInt32("Year", dr),
Month = DatabaseHelper.GetInt32("Month", dr)
};
return(t);
}
