A question about SQL insert statement!

Question

I have two tables:

Threads
*******
ThreadID
UserID
TopicsID
Date
ThreadTitle
ThreadParagraph
ThreadClosed

Topics
******
TopicID
Theme
Topics
Date

I need to insert two statements and connect between them! This the first statment:

string insertCommand = 
    "INSERT INTO Users (UserID,TopicID,Date,ThreadTitle,ThreadParagraph) " +
     "VALUES ('CONVERT(uniqueidentifier, '" + giveMeGuidID() + 
     "),TopicID,dateTime,questionTitle,subTopic)";

and i need to have another statement for the Topics Table:

string insertCommand = 
    "INSERT INTO Topics (UserID,TopicID,Date,ThreadTitle,ThreadParagraph) " +
    "VALUES ('Theme, Topics, Date')";

The problem is that i have a connection between TopicsID (Threads table) and TopicsID (Topics table). Both are incremental ints, so how do i insert the same TopicID to both of them so they get the same value?

Answer 1

If you use MS SQL server, you can get the autoincrement value with @@Identity.

string insertCommand = 
    "INSERT INTO Users (UserID,TopicID,Date,ThreadTitle,ThreadParagraph) " +
    "VALUES ('CONVERT(uniqueidentifier, '" + giveMeGuidID() +
    "),TopicID,dateTime,questionTitle,subTopic); SELECT @@Identity";

Then, run this command as ExecuteScalar and get your value

Answer 2

You can maintain the Transaction by using TransactionScope and using SCOPE_IDENTITY() to get the inserted Id from the First Query.

 // Create the TransactionScope
using (TransactionScope oTranScope = new TransactionScope())
{
   Int32 TopicID;
    // Open a connection 
    using (SqlConnection oCn1 = new SqlConnection(this.sCn1))
    {
        SqlCommand oCmd1 = new SqlCommand("INSERT INTO Users (UserID,TopicID,Date,ThreadTitle,ThreadParagraph) " +
"VALUES ('CONVERT(uniqueidentifier, '" + giveMeGuidID() +
"),TopicID,dateTime,questionTitle,subTopic); SELECT SCOPE_IDENTITY()";, oCn1);

        oCmd1.Parameters.Add ... Better to use parameter to save SQL Injection Attack

        oCn1.Open();
        // At this point, the connection is in the transaction scope, 
        // which is a lightweight transaction.
        TopicID = Convert.ToInt32 oCmd1.ExecuteScaler()); // as you want to get Id
        oCn1.Close();
    }
    // Open a connection 
    using (SqlConnection oCn2 = new SqlConnection(this.sCn2))
    {
        SqlCommand oCmd2 = new SqlCommand("SQLQuery", oCn2);
        //use return TopicID from last inserted query
        oCn2.Open();
        // The connection is enlisted in the transaction scope, 
        // which is now promoted to a distributed transaction
        // controlled by MSDTC
        oCmd2.ExecuteNonQuery();
        oCn2.Close();
    }
    // Tell the transaction scope to commit when ready 
    oTranScope.Consistent = true;
    // The following bracket completes and disposes the transaction
}