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     
----------------------------
开发者_如何学Go

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!

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

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