Inserting into MySQL Database with many-to-many relationship using C#

I have created the following tables to fill my data using C# and MySQL.

SCIENTIST
-----------------------
scientistID
scientistName
branch
address
phone
fax
url
email

SCIENTISTAREA
------------------------
scientistAreaID
scientistID
areaofscienceID

AREAOFSCIENCE
--------------------
areaofscienceID
areaName

I have a scientist class that contains all the information within the science table, including a 开发者_开发技巧List of strings containing each area of science the scientist covers. To make things easier when adding the queries I have created a List of strings containing every distinct area of science. I have written these base queries for when I write my own. As you'll see, I've prefixed the areas I'm having trouble with with a @ (mainly as I'll be using MySqlParameterCollection.AddWithValue() to add them):

insert into areaOfscience (
    areaName
) values (
    'Agricultural science'
);

insert into scientistArea (
    scientistID, areaOfscienceID
) values (
    @scientistID, @areaOfscienceID
);

insert into scientists (
    scientistName, branch, address, phone, fax, url, email
) values (
    'A scientists', 'Head Office', '123 Fake St', '0117 38473847', '0117 3746382', 'http://www.google.com', 'me@myhouse.com'
), (
    'B scientists', 'Head Office', '123 Fake St', '0121 38473847', '0121 3746382', 'http://www.google.com', 'me@myhouse.com'
);

At the moment, I am filling the area of science table with every available area of science, and am now looking to add scientists to my table. Each scientist covers a number of different areas of science, and I would like to insert all of these into my database.

Any help would be much appreciated.

You just need to look for a scientists and areas ids and insert them into scientistArea table. Maybe a code like this can help you, assuming there is no scientist with duplicated names, nor areas:

INSERT INTO scientistArea (scientistID, areaOfscienceID)
SELECT scientistID, areaOfscienceID
FROM   scientists,
       areaOfscience
WHERE  scientists.scientistName = 'A scientists' AND
       areaOfscience.areaName = 'Agricultural science'