How to display records from a database without using DataGridView

[Lin100]

In Ms Access database, a form can display records from a table or query
in a single record as oppose to multiple records like a DataGridView

I want to display records from an Access database on a C# form without using DataGridView
because there are too many fields. When using DataGridView, you have to scroll left and right if
there are too many fields.

The attachment depict a form that has many fields but with just one record. To see the second
record, or third or fourth, the user just click on the arrow below. I want a C# form to do the same.
How would I do this in C# ?

 

[Lin100]

OK, so some first things first

View attachment 2585

Assuming a property is a building, like a high rise, and a unit is one or more residential units within the building, I probably would have made it so a Unit has a PropertyId (because realistically a unit can only be inside one building) and a UnitId, and I'd have UnitId be unique across all units everywhere - I wouldn't use the door number, such that two different units in two different buildings could have ID 100 because the door number is 100 on both units.

I would not have used property name as a key in other tables - names repeat; there is sure to be more than one building in the world called Beach Villas so you're engineering a collision in there that the poor user will ahve to work around by calling the second property they take on something else, such as repeating the city name into the property name; if I was making something that was relating to the property, such as the street address of the building, then an address record would have a property Id, or perhaps a property would have an address ID - we flip relationships around depending on how it suits us - can a property have multiple addresses? Probably not. Can an address have multiple properties? Maybe - so maybe the Property should have an address ID. The main thing is it's usually quite helpful to have a unique number for a record - it's one column that we can then establish relationships with

On relationships; the tenant and the unit clearly have a relationship when a tenant is leasing a unit, and we might want to keep ahold of that history too, even when the lease is over. One unit has many tenants according to your diagram, and yes, that might be true in the real world, for a house share.. But can one person ever rent two units? That also happens in the real world. Do you make another Tenant record for the same person, so you now have two John Does to keep up to date, that actually refer to the same person? What if one ends up with a good credit risk and the otehr a bad?
If you're having a record for John Doe, a particular human then he should probably have only one record. This would then mean he has e.g. TenantId 123. If youre having a record for apartment 104 (unit id 232323) Beach Villas (property Id 45) then on the question of how John can have multiple units and how a unit can have multiple tenants, you can actually have a third table, TenantUnit that tracks the two IDs as a pair, and then maybe also things like the length of the lease.. So you can record that tenant 123 (john) in unit 232323 from date to date - you only have one tenant record, one unit record, and this other table TenantUnit has multiple records in it that track every lease John has for all the units, and also tracks every tenant apartment 104 has/did have. The TenantId,UnitId in the TenantUnit table is unique, unless one person can lease one unit multiple times (move out and then back in later?) so you then start thinking whether to make the date part of the key, or maybe there is a new tenancy agreement record that is part of the key

Maybe also TenantUnit is the thing that has the reservation - everyone who takes a unit pays a reservation, we track that historically etc..

Think about that relationship thing with single numeric IDs though; you're making property name and unit number a key of Unit, which is understandable, but hard work, because in future whenever you want to relate something to a Unit, you now have to put 2 bits of info in the related table.

To cjard:

You said "Assuming a property is a building, like a high rise, and a unit is one or more residential units within the building, ...."

From my point of view which is based on the real world of apartment complexes,
a property has many buildings and each building has many units.
Building 100 has unit from 100 to 199. Building 200 has unit from 200 to 299
Building 300 has unit from 300 to 399. Building 400 has unit from 400 to 499.

In a single property, there will never be multiple unit of 100 in different building.
I do not see that in the real world. In all of the apartment in the United States,
a single property say "Green Oaks" will never have 2 or three unit with number 100.
Each unit number in a single property is unique. A single property will only have
one room whose number is 100 and it is the same for unit 101, unit 102 or unit 200
or unit 300 or unit 400.

In the real world in the USA, a single property will more than likely not have multiple unit with the same unit number as in unit 100 in building 1; unit 100 in building 2; unit 100 in building 3. You could have that scenario, but that would be confusing. When I go into an apartment office, if I say apartment 100, they will know that there is only
only unit with number 100.

Furthermore, when you design a software for a business, you have to ask them
about their business processes and business rules. Their business processes and business rules will dictate how relationship table, primary key and other things are
being done. If an apartment is set up like I stated above then it will done as such.
If an apartment set up like you said then it would be setup as you stated.

 

[Skydiver]

Think about that relationship thing with single numeric IDs though; you're making property name and unit number a key of Unit, which is understandable, but hard work, because in future whenever you want to relate something to a Unit, you now have to put 2 bits of info in the related table.

+1. Row ID numbers should be used to retain the relationships. Don't use the "name" of the thing for keeping the relationship.

For example, in my local area there was an apartment complex that had some serious infrastructure problems due to having gotten scammed by contractors with "chinese drywall" that caused health issues for the tenants. All the tenants had to be moved out so quickly that they were forced to leave most of their belongings while the issues were remediated. During the remediation, belongings were stolen. The tenants sued the apartment complex. The complex declared bankruptcy on the old apartment name, and then just renamed the same apartment complex to something new. Same management, same buildings, different name. With the scheme of using the property name, now all the old property name values in various tables and rows would have to be updated, instead of just updating a single row in a single table.

 

[Lin100]

Hi Skydiver and cjard.
Once you have made all the changes to the C# code please upload it. Before you upload it, run and test it first.
1) Form Unit_2 open up
2) Double click on the status field.
If Status = "Reserved" then
Reservation2 and Reservation 3 form should open up.
If Status = "Vacant" then
Reservation1 form should open up.
If Status = "Occupied" then
Tenant form should open up.

 

[Skydiver]

We are not a code writing service.

 

[Lin100]

I know that you are not a code writing service but cjard wanted me to upload the project so he can look at it and make changes and that is why I uploaded the project for you to see.

 

[Skydiver]

My impression from the other thread was that he wanted to demonstrate how much cleaner the code with look using a modern approach rather than the old way of doing things with direct calls to SQL, data sets, and data tables. It would have been for just one specific case from that other thread. I don't know where you got the impression that he was going to do a full rewrite of all your code that accesses the database. If that's his plan, then more power to him!

 

[Skydiver]

Following the instructions from post #4, I easily got this:

Basically, I deleted the existing "Reseservation3.cs" and it's associated files. Then I added a new "Reservation3" Form.
Next I change the type in the Data Sources from grid to details:

Then just drag and drop that Reservation to the new blank form.

 

[cjard]

I did a recording of post #4 plus some extras but the program I used don't capture the mouse cursor which made it slightly confusing to watch. Looking to redo it soon, but have a training course for the next 3 days..

If you want to review post 4 and also do the following:

Before you drag the details node onto the form, expand it and change the State to be a combo box

Drag the node to the form

Drag the state node (that represents the states datatable, not the state sub node you just changed to be a combo) to the form but delete the DataGridView - you don't want it but you do want to use the bindingsource and tabkeadapter that it makes

Change the state combo box to dropdown style of DropDownList

Delete the binding for Text (top of props grid, expand Data Bindings)

Create a binding for selected value that is bonded to reservation binding source's state column

Set the DataSource of the combo to be the statesBindingSource

Set the display member of the combo to be State_Name

Set the ValueMember of the combo to the state_initial

--

You now have. A combo that will show a list of all states and when the user changes it it will poke the State_Initial chosen, into the State column of the current visible row of the Reservation

 

[cjard]

Your dataset is quite large, many many tables and the source code for it is 3mb. You may find it easier (faster) to work with smaller datasets dedicated to each form. The dataset need only contain just the small number of tables you want to work with on a particular form

 

[Skydiver]

If you open there attached database, he starts off with a smaller set of it just 4 tables and a view.

 

[cjard]

Yea I figured it had been cut down; the main DataSet (xsd) is huge; it's often a nicer design experience to load fewer tables in, and sometimes we might even do stuff like configure relationships the other way round depending on the context, which separated DS are helpful for

Some tutorials probably teach something like "connect VS to the access db then drag the db into the dataset designer" and it'll go an create a representation of all thousand tables, then chug a fair bit!