C# adding data to a SQL table with asp.net It throws an error System.InvalidCastException: “Specified cast is not valid.”
This is for a foreign key column with a data type of bigint. I set my type in C# to be Int64, after int definition threw an error but still get the error.
Is this because I can't add to a column that has foreign keys? Do I need to cascade?
This is my datacontroller:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using SchoolDb.Models;
using MySql.Data.MySqlClient;
using System.Diagnostics;
namespace SchoolDb.Controllers
{
public class CoursesDataController : ApiController
{
private SchoolDbContext School = new SchoolDbContext();
/// <summary>
/// returns list of courses in the system
/// </summary>
/// <example>GET api/CoursesData/ListCourses</example>
/// <returns>a list of courses</returns>
[HttpGet]
[Route("api/CourseData/ListCourses/{SearchKey?}")]
public IEnumerable<Course> ListCourses(string SearchKey = null)
{
//create an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//open the connection between server and database
Conn.Open();
string query = "Select * from classes where lower(classname) like lower(@key) or classid like (@key)";
Debug.WriteLine("the search key is " query);
//establish a new command queery for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL Quesry
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@key", "%" SearchKey "%");
cmd.Prepare();
//Gather Result set of query into variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//create an empty list of Courses
List<Course> Courses = new List<Course> { };
while (ResultSet.Read())
{
//access column information by the db column name as an index
int ClassId = (int)ResultSet["classid"];
string ClassCode = (string)ResultSet["classcode"];
Int64 TeacherId = (Int64)ResultSet["teacherid"];
DateTime StartDate = (DateTime)ResultSet["startdate"];
DateTime FinishDate = (DateTime)ResultSet["finishdate"];
string ClassName = (string)ResultSet["classname"];
Course NewCourse = new Course();
NewCourse.ClassId = ClassId;
NewCourse.ClassCode = ClassCode;
NewCourse.TeacherId = TeacherId;
NewCourse.StartDate = StartDate;
NewCourse.FinishDate = FinishDate;
NewCourse.ClassName = ClassName;
//add the course info to the list
Courses.Add(NewCourse);
}
Conn.Close();
//return the final list of courses
return Courses;
}
/// <summary>
/// returns a single instance of a course
/// </summary>
/// <param name="id">class id</param>
/// <returns>info on a particular course based on classid input</returns>
[HttpGet]
public Course FindCourse(int id)
{
Course NewCourse = new Course();
//create an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//open the connection between server and database
Conn.Open();
//establish a new command query for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL Query
cmd.CommandText = "Select * from classes where classid = " id;
//Gather Result set of query into variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
while (ResultSet.Read())
{
//access column information by the db column name as an index
int ClassId = (int)ResultSet["classid"];
string ClassCode = (string)ResultSet["classcode"];
Int64 TeacherId = (Int64)ResultSet["teacherid"];
DateTime StartDate = (DateTime)ResultSet["startdate"];
DateTime FinishDate = (DateTime)ResultSet["finishdate"];
string ClassName = (string)ResultSet["classname"];
NewCourse.ClassId = ClassId;
NewCourse.ClassCode = ClassCode;
NewCourse.TeacherId = TeacherId;
NewCourse.StartDate = StartDate;
NewCourse.FinishDate = FinishDate;
NewCourse.ClassName = ClassName;
}
return NewCourse;
}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <example>POST: /api/CoursesData/DeleteCourse/3</example>
[HttpPost]
public void DeleteCourse(int id)
{
//create an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//open the connection between server and database
Conn.Open();
//establish a new command queery for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL Query
cmd.CommandText = "Delete from classes where classid=@id";
cmd.Parameters.AddWithValue("@id", id);
cmd.Prepare();
cmd.ExecuteNonQuery();
Conn.Close();
}
[HttpPost]
public void AddCourse(Course NewCourse)
{
//create an instance of a connection
MySqlConnection Conn = School.AccessDatabase();
//open the connection between server and database
Conn.Open();
//establish a new command query for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL Query
cmd.CommandText = "insert into classes (classcode, teacherid, startdate, finishdate, classname) value (@ClassCode, @TeacherId, @StartDate,@FinishDate,@ClassName)";
cmd.Parameters.AddWithValue("@ClassCode", NewCourse.ClassCode);
cmd.Parameters.AddWithValue("@TeacherId", NewCourse.TeacherId);
cmd.Parameters.AddWithValue("@StartDate", NewCourse.StartDate);
cmd.Parameters.AddWithValue("@FinishDate",NewCourse.FinishDate );
cmd.Parameters.AddWithValue("@ClassName", NewCourse.ClassName);
cmd.Prepare();
cmd.ExecuteNonQuery();
Conn.Close();
}
}
}
CodePudding user response：
You didn't specify which line the InvalidCastException occurs on, so I'm going to assume it's one of the following lines with explicit casts:
//access column information by the db column name as an index
int ClassId = (int)ResultSet["classid"];
string ClassCode = (string)ResultSet["classcode"];
Int64 TeacherId = (Int64)ResultSet["teacherid"];
DateTime StartDate = (DateTime)ResultSet["startdate"];
DateTime FinishDate = (DateTime)ResultSet["finishdate"];
string ClassName = (string)ResultSet["classname"];
One possibility is trying to retrieve an int from a long column, or vice versa. This can be avoided by using the GetInt32 or GetInt64 method. These will convert the value to a smaller size if possible, otherwise throw an OverflowException.
Another possibility is that some of the columns contain NULL. In that case, ResultSet["Name"] will return DBNull.Value, which can't be cast to a string (or int or DateTime).
Depending on what columns can contain NULL values, you likely need code similar to the following:
//access column information by the db column name as an index
int ClassId = ResultSet.GetInt32("classid");
string ClassCode = ResultSet.IsDBNull("classcode") ? null : reader.GetString("classcode");
Int64 TeacherId = ResultSet.GetInt64("teacherid");
DateTime StartDate = (DateTime)ResultSet["startdate"];
DateTime FinishDate = (DateTime)ResultSet["finishdate"];
string ClassName = ResultSet.IsDBNull("classname") ? null : ResultSet.GetString("classname");
But I would recommend using an ORM like Dapper to simplify all of this code and map a DB row easily to a C# object.