C# database

ryze ryze

Hello everyone,

I have sql db with many tables but i want to be able to edit 2 tables and only 1 column in each together.

to make my self clear this is an example:

first table: location
xyz

second table: location of device
xyz, dev1

so im trying to create a simple winform that when i open it:
1. a column that shows the data in location table as it is in the db (cannot be edited)
2. a column called new location that i can change each location name.

and when i change the location name in the winform it has to effect also the location of device which means it has to effect the 2 columns.

im a newbie in C# so I would really appreciate any help

Thanks in advanced

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Wim Sturkenboom12y ago
Accepted answer
I suggest that you don't do it in C#. I suggest that you create a relationship in SQL between the two tables (based on the location column) and use cascading update; this will automatically handle your problem.

Below the description for MS SQL server (not sure what you are using). I''m assuming that both tables have a field called location. In below description the table with the locations is called tblLocation and the table with device locations is called tblDevicelocation.
- The easiest way is to use diagram. In SSMS (sql server management studio), right click database diagrams for your specific database and choose 'new database diagram'. A popup opens; select your two tables from it. Close the popup. You will now see the two tables.
- Drag the location field from tblLocation to the location field in tblDevicelocation (not the other way around). A popup 'table and columns' shows with the primary key table and the foreign key table; for both the field must be location. Click OK and OK. A line will appear between the two tables; one side has a key symbol and must be at the side of tblLocation.
- Next right click the line and select properties; usually the properties window will show at the right hand side. Double click 'Insert and Update specification'; modify the 'update rule' from 'No action' to 'Cascade'.
Close the diagram; no need to save unless you want to keep it.

If you now change the content of the location field in tblLocation, it will automatically reflect in tblDevicelocation.

There is also a delete rule; default is 'no action' which will prevent accidental deleting of a record from tblLocation if there are still associated records in tblDevicelocation; if you set that to 'cascade', a record in tblLocation will be deleted as well as all associated records in tblDevicelocation.

If you want to do it with SQL commands (MS SQL)

ALTER TABLE [dbo].[tblDevicelocation] WITH CHECK ADD CONSTRAINT [FK_tblDevicelocation_tblLocation] FOREIGN KEY([location])
REFERENCES [dbo].[tblLocation] ([location])
ON UPDATE CASCADE

Note1: the location field in tbLocation must be the primary key. If you have another primary key, your database design is wrong.
+1
Wim Sturkenboom12y ago
This might break the existing application. I strongly suggest that you test. The existing application might delete a record from tblLocation and next delete all associated records in tblDevicelocation. That approach might break when using relationships.

If the database was designed correctly, the relationship will already be there. You can, again, use SSMS and diagrams; create a diagram with the two tables and check if there is a connecting line between them that indicates a relationship; if so, check the settings and modify if needed.
0
ryze ryze12y ago
Many Thanks for this professional and great help and explanation Wim Sturkenboom it helped me a lot.

I have just 1 clarification and sorry for not mentioning this point earlier which is there is already software for this db and its written in C#, so what i wanted to do is to make it easier to the user instead of changing all the devices names with locations i wanted to create a simple windows form that shows all the existing location names and beside it a table for the user to just change the name of the location and it will effect all devices automatically. your answer will change them without need to create another application without any problems right?

Many Thanks and regards again
0