CODEWITHSUNDEEP
c#asp.netsqlado.net
Mazen Elkashef
Mazen Elkashef

Reputation: 3499

how to implement and manipulate a many to many relationship in ASP.NET

Assume that we've three tables

Course - table

------------------
 ID | CourseName
------------------
 1  |  C++
 2  |  Java
------------------

Teacher - table

-----------------------
 ID |   TeacherName
-----------------------
 1  |   Professor 1
 2  |   Professor 2  
-----------------------

CourseTeacher - table

----------------------------
 ID | CourseID | TeacherID 
----------------------------
 1  |   1      |     1   
 2  |   1      |     2    
 3  |   2      |     1   
 4  |   2      |     2     
----------------------------

Now that's what to do as AFAK, but how to select, insert, update, delete records in such a format!

I mean, I'd use a simple "INSERT INTO ... VALUES (..,..,..)" or a simple "SELECT * FROM ..."

but now to retrieve the same single information I've to use some queries that includes the 3 tables somehow (Hint: I already use joins, but still how!!)

A select and insert SQL statement for my case would be very helpful.

I don't use any models and this complicated stuff, I don't know what the use of such a thing! I'm using SQL Queries in my ADO.NET Objects and everything is working fine so far!

Upvotes: 1

Views: 664

Answers (2)

TalentTuner
TalentTuner

Reputation: 17556

it is as simple as for other table.

1 - I assume , you must have code for insert, update , select for course and teacher table. So continue using same methods.

2- but for CourseTeacher table (Junction table) , you must insert in this table when you actually have corrosponding TearcherId and CourseId avaliable in repective tables.

so when you want to add a new record in CourseTeacher table , first ensure respective values of courseid and teacherid presents.

when you wanna update in junction table , use the Id column to updae

Upvotes: 1

LaGrandMere
LaGrandMere

Reputation: 10359

I'm not sure to understand what your question is ...

Let's assume that your PK in each table is automatically incremented.

To add a course : INSERT INTO Course VALUES 'My New Course' To add a teacher : INSERT INTO Teacher VALUES 'My New Teacher'

Tu add an existing Course to an existing Teacher : In the worst case scenario, meaning that you only know the names of the Teacher and the Course

SELECT ID FROM Teacher WHERE NAME = 'My New Teacher' -> id1
SELECT ID FROM Course WHERE NAME = 'My New Course' -> id2
INSERT INTO CourseTeacher(IDCOURSE,IDTEACHER) VALUES (id2,id1)

In fact, in your application, when your user selects a Course to add to a Teacher, you should already know what the IDs are, because when you got the list containing them, you certainly have done this :

SELECT ID, NAME FROM Teacher
SELECT ID, NAME FROM Course.

A link which may be useful if what I wrote is not : http://www.singingeels.com/Articles/Understanding_SQL_Many_to_Many_Relationships.aspx

Upvotes: 2

Related Questions