I have two tables in my database, these tables are
Patient having 3 column Id(PK), Cnic, Name and
PatientVaccines having column Id(FK), Cnic, VaccinationName, VaccinationDate, CenterAddress.
I am implementing CRUD operation on them but i am stuck with fetching data from two tables into one view, here is my code
Patient class
public class Patient
{
public int Id { get; set; }
public string Cnic { get; set; }
public string Name { get; set; }
public string VaccinationName { get; set; }
public DateTime VaccinationDate { get; set; }
public string CenterAddress { get; set; }
}
PatientDBContext class
public class PatientDBContext
{
string cs = ConfigurationManager.ConnectionStrings["CRUDwithSPs"].ConnectionString;
public List<Patient> getPatient()
{
List<Patient> patientList = new List<Patient>();
SqlConnection con = new SqlConnection(cs);
SqlCommand cmd = new SqlCommand("showPatient", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Patient p = new Patient();
p.Id = Convert.ToInt32(dr["Id"].ToString());
p.Cnic = dr["Cnic"].ToString();
p.Name = dr["Name"].ToString();
p.VaccinationName = dr["VaccinationName"].ToString();
p.CenterAddress = dr["CenterAddress"].ToString();
patientList.Add(p);
}
con.Close();
return patientList;
}
}
This is my
HomeController
public class HomeController : Controller
{
public ActionResult Index()
{
PatientDBContext db = new PatientDBContext();
List<Patient> obj = db.getPatient();
return View(obj);
}
}
What I have tried:
I tried few things like
p.Id= (Convert.ToInt32(dr["Id"])).ToString();
and
p.Id = Convert.ToInt32(dr.GetValue(0).ToString());
but still getting IndexOutOfRange exception
Please also tell me what im doing wrong here
p.VaccinationDate = dr["VaccinationDate"].ToString();