public async Task <IActionResult> Post([FromBody] ExerItemViewModel vm)
{
if (vm == null)
{
return(BadRequest());
}
// Update the database
SqlConnection conn = new SqlConnection(Startup.DBConnectionString);
String queryString = "";
Boolean bError = false;
String strErrMsg = "";
try
{
await conn.OpenAsync();
queryString = @"INSERT INTO [dbo].[t_exitems]
([Question]
,[Answer]
,[CreatedBy]
,[CreatedAt]
,[Types])
VALUES
(@Question
,@Answer
,@CreatedBy
,@CreatedAt
,@Types );
SELECT @Identity = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(queryString, conn);
cmd.Parameters.AddWithValue("@Question", vm.Question);
cmd.Parameters.AddWithValue("@Answer", vm.Answer);
cmd.Parameters.AddWithValue("@CreatedBy", vm.CreatedBy);
cmd.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
if (String.IsNullOrEmpty(vm.Types))
{
cmd.Parameters.AddWithValue("@Types", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@Types", vm.Types);
}
SqlParameter idparam = cmd.Parameters.AddWithValue("@Identity", SqlDbType.Int);
idparam.Direction = ParameterDirection.Output;
Int32 nRst = await cmd.ExecuteNonQueryAsync();
vm.ID = (Int32)idparam.Value;
cmd.Dispose();
cmd = null;
}
catch (Exception exp)
{
System.Diagnostics.Debug.WriteLine(exp.Message);
bError = true;
strErrMsg = exp.Message;
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
conn = null;
}
}
if (bError)
{
return(StatusCode(500, strErrMsg));
}
return(new JsonResult(vm));
}
public async Task <IActionResult> Get()
{
List <ExerItemViewModel> listRst = new List <ExerItemViewModel>();
Boolean bError = false;
String strErrMsg = "";
try
{
SqlConnection conn = new SqlConnection(Startup.DBConnectionString);
try
{
await conn.OpenAsync();
String queryString = @"SELECT [ID],[Question],[Answer],[CreatedBy],[CreatedAt],[Types] FROM [dbo].[t_exitems] ";
SqlCommand cmd = new SqlCommand(queryString, conn);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ExerItemViewModel vm = new ExerItemViewModel()
{
ID = reader.GetInt32(0),
Question = reader.GetString(1),
Answer = reader.GetString(2),
CreatedBy = reader.GetString(3),
CreatedAt = reader.GetDateTime(4)
};
if (!reader.IsDBNull(5))
{
vm.Types = reader.GetString(5);
}
listRst.Add(vm);
}
}
}
catch (Exception exp)
{
System.Diagnostics.Debug.WriteLine(exp.Message);
bError = true;
strErrMsg = exp.Message;
}
finally
{
conn.Close();
conn.Dispose();
}
}
catch (Exception exp2)
{
System.Diagnostics.Debug.WriteLine(exp2.Message);
bError = true;
strErrMsg = exp2.Message;
}
if (bError)
{
return(StatusCode(500, strErrMsg));
}
return(new JsonResult(listRst));
}
