Jasper Schiks Jasper Schiks - 6 days ago 7
C# Question

Insert data in 1 table with data from 2 others

So I have this:

INSERT INTO Track (Trackname, Trackinfo) VALUES (Value1, Value2)
INSERT INTO Artiest (Artist) VALUES (Value1)


Both of these tables have an ID that is auto_increment
how can I create a table that will have both the IDs??

I am trying to do this in C#

EDIT: Maybe there is a way in MySQL where I can get the last IDs of the 2 tables?

Answer

You either write a stored procedure using T-SQL and SCOPE_IDENTITY(). Or you use LINQ to Entities and retrieve the ID of the newly created Entity.

T-SQL:

DECLARE @TrackID AS int
INSERT INTO Track (Trackname, Trackinfo) VALUES (Value1, Value2)
SET @TrackID = SCOPE_IDENTITY()

DECLARE @ArtiestID AS int
INSERT INTO Artiest (Artist) VALUES (Value1)
SET @ArtiestID = SCOPE_IDENTITY()

Now use those ID's to Insert into your table.

INSERT INTO TrackArtiest (TrackID , ArtiestID ) VALUES (@TrackID , @ArtiestID)

When using Linq to Entities:

var myTrackObject = new Track(Value1, Value2);
var myArtiestObject= new Artiest(Value1);

// db is your datacontext
db.Tracks.Insert(myTrackObject);
db.Artiests.Insert(myArtiestObject);
db.SubmitChanges();

// You can retrieve the id from the object
int trackID = myTrackObject.ID;
int artiestID = myArtiestObject.ID;

var myTrackArtiest = new TrackArtiest(trackID, artiestID);
db.TrackArtiest.Insert(myTrackArtiest );
db.SubmitChanges();

Is this what you meant ?