If you would separate you concerns, and let every method do only one thing, it would be easy to create tests to see why your code is not working.
Consider to separate your database handling from displaying fetched data. This way you can easily change where you fetch your data or change how you use the fetched data.
Apart from that this will help you to find the source of your problems, it will also make your code better to understand, easy to reuse and change.
You should especially take care that you separate your data (= model) from how you display it (= view) and how you store it.
class Patient
{
public int Id {get; set;}
public string Name {get; set;}
public Gender Gender {get; set;} // Gender is an enum
public DateTime BirthDate {get; set;}
...
}
Of course you don't save the patient's age in the database. If you did, you'd have to update your database daily.
public int Age => // some formula to get the Age from the current Date
Use DateTime.ToDay, Patient.BirthDate, and Today.DayOfYear >= BirthDate.DayOfYear
Fetching data from the database is done in a repository class. All that users of the class (= code, not operators) want to know is, that they can Store Patients in it, and fetch them back, even if the program is restarted. They don't care whether you store it in a database, or in an XML file, or maybe even store it somewhere in the internet.
This will also give you the freedom to change this, without users noticing.
class PatientRepository
{
public string DbConnectionString {get; set;}
public Patient FetchPatient(int patientId)
{
const string sqlTextFetchPatient = "select top 1"
+ " id,name,gen,age,date,cont,addr,disease,status,r_type,building,r_no,price"
+ " from patient"
+ " where id = @Id;";
using (var connectedDb = new SqlConnection(this.DbConnectionString)
{
using (var dbCommand = connectedDb.CreateCommand())
{
dbCommand.CommandText = sqlTextFetchPatient;
dbCommand.Parameters.AddWithValue("@Id", patientId);
connectedDb.Open();
using (var dbReader = dbCommand.ExecuteReader())
{
// expect only one Patient or null
if (dbReader.Read())
{
// there is a Patient:
return new Patient
{
Id = dbReader.GetInt32(0),
Name = dbReader.GetString(1),
Gender = (Gender)dbReader.GetInt32(2),
BirthDate = dbReader.GetDateTime(3),
...
}
}
}
}
}
}
}
Of course your PatientRepository will also have methods to Add / Update / Remove Patients.
By the way, did you see that I used using for every IDisposable object? This way you can be certain that the objects are Disposed as soon as you don't need them anymore, even after exceptions.
Furthermore, I made patientId a parameter and I only selected Top 1.
It seems that you want to show several Patients in a DataGridView. Add the following to your PatientRepository:
IEnumerable<Patient> FetchAllPatients()
{
... same as above
while (dbreader.Read())
{
yield return new Patient
{
Id = dbReader.GetInt32(0),
...
}
}
}
Or maybe You want to fetch Patients "per page"
IEnumerable<Patient> FetchPatientPage(int pageNr, int pageSize)
Do OrderBy and Skip PageNr * PageSize items and Select PageSize items. How to do this depends on your Sql dialect.
Now that we have a method to Fetch a Patient, you can easily unit test it. If you did, you will probably have found your error.
Display fetched patients
Somewhere in your form you have a method to fetch the Patients that you want to show:
private readonly string dbConnectionString = ...
protected Repository PatientRepository {get;} = new PatientRepository(this.dbConnectionString);
protected IEnumerable<Patient> FetchPatients()
{
this.PatientRepository.FetchPatients();
// or fetch per page
}
You want to show them in a DataGridView. Using visual studio designer, you have code like this:
DataGridView dgv1 = new DataGridView();
DataGridViewColumn columnId = new DataGridViewColumn();
DataGridViewColumn columnName = new DataGridViewColumn();
DataGridViewColumn columnGender = new DataGridViewColumn();
...
columnId.DataPropertyName = nameof(Patient.Id);
columnName.DataPropertyName = nameof(Patient.Name);
columnGender.DataPropertyName = nameof(Patient.Gender);
If you only want to Display the Patients:
this.dgv1.DataSource = this.FetchPatients().ToList();
This is display only. If you want to enable editing you should put the Patients in an object that implements IBindingList, like a BindingList
this.dgv1.DataSource = new BindingList(this.FetchPatients().ToList());
Now all changes that the operator makes: edit cells / add new row / delete rows, etc are automatically updated in the BindingList.
A proper design would add the following methods to your form:
BindingList<Patient> DisplayedPatients
{
get => (BindingList<Patient>)this.dgv1.DataSource;
set => this.dgv1.DataSource = value;
}
And if you want to handle items in selected item
Patient CurrentPatient => this.dgv1.CurrentRow?.DataBoundItem as Patient;
IEnumerable<Patient> SelectedPatients => this.dgv1.SelectedRows
.Select(row => row.DataBoundItem)
.Cast<Patient>();
After this, handling removed / added / changed Patients will be one-liners.
To display a Patient in your text boxes:
void DisplayPatient(Patient patient)
{
this.TextId = patient.Id;
this.TextBoxName = patient.Name;
...
}
To Display the current Patient in your text boxes:
public void DataGridViewCurrentCellChanged(object sender, ...)
{
this.DisplayPatient(this.CurrentPatient);
}
