1

I need to create a procedure that will store data into 3 tables, while using data from each table to reference the rest.

E.g.: I am creating a team challenge database. This will allow a person to challenge another, setup who will be in each others teams, then store in into a database.

I have a primary table which stores the matchId, initiatorName, teamOneListId and teamTwoListId.

  • TeamOneList: ID, memberID, TeamName
  • TeamTwoList: ID, memberID, TeamName

As you can see, I need to add data into all of them at once (While having the team size and limits vary for 1v1, 3v3 etc).

I am using C# Asp.Net, and am well-versed in Transactions. I am just not too clued up in databasing.

EDIT: Wrote this at the end of a long hard day.
The question is, what would be the simplest way or method of going about this?

I've tried to mess around with the stored procedure, I can forward identities to the different tables, but it seems I still have to create a new stored procedure for both teams, and for every type of event.

The only way I could get the aforementioned working was to start a match, get the last row's information, and forward that to a second page binding the rest of the 2 teams information into the database.

So to reiterate, I need to know how to do all this at once, or have a way of not simply getting the last row (that might be wrong?).

TheGeekZn
  • 3,696
  • 10
  • 55
  • 91
  • 2
    It's more of a "here are the specifications", please post the code. Joel Spolsky and Jeff Atwood (creators of this site) have clearly stated in their podcast that the appropriate response to "do my work for me" questions is no votes up or down, and no replies. They will eventually be deleted by the robot as inactive questions. – Eric Leschinski Oct 15 '12 at 13:53
  • What have you tried? This does seem to be a "do my work for me" type question. All you need to do is start a transaction, then insert into your tables and then commit/rollback the transaction. If part of that is difficult, please clarify so that someone can provide a good targeted answer that doesn't do all of your work – Nathan Koop Oct 15 '12 at 13:55
  • You need to ask an actual question. You know, something with a "?" at the end. – RBarryYoung Oct 15 '12 at 14:59
  • are you looking for @@Identity? http://msdn.microsoft.com/en-us/library/aa933167(v=sql.80).aspx – Nathan Koop Oct 16 '12 at 13:22
  • @@identity helped me store the ID in the different tables, but not in the way I need it. I have re-defined my question, as well as my database (to something more managable) here: http://stackoverflow.com/questions/3907681/return-value-from-stored-procedure – TheGeekZn Oct 16 '12 at 13:25
  • you mean here right? http://stackoverflow.com/questions/12915684/retrieving-return-value-from-stored-procedure – Nathan Koop Oct 16 '12 at 18:00
  • Agh! yes... that other link was a different post i was looking at :/ – TheGeekZn Oct 16 '12 at 18:51

1 Answers1

0

You can try with SqlDataAdapter class

Link : http://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqldataadapter(v=vs.80).aspx

var connection = new SqlConnection("...");//You can adjust your string connection
var adapter = new SqlDataAdapter();

// Create the SelectCommand.
var command = new SqlCommand("YourSelectStoredProcedure", connection); //You can adjust your name of stored procedure
command.CommandType = CommandType.StoredProcedure;
adapter.SelectCommand = command;

// Create the InsertCommand.
command = new SqlCommand("YourInsertStoredProcedure", connection); //You can adjust your name of stored procedure
command.CommandType = CommandType.StoredProcedure;
adapter.InsertCommand = command;
Aghilas Yakoub
  • 28,516
  • 5
  • 46
  • 51