CODEWITHSUNDEEP
c#asp.netsql
WithFlyingColors
WithFlyingColors

Reputation: 2760

A question about SQL insert statement!

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?

Upvotes: 6

Views: 333

Answers (4)

Muhammad Akhtar
Muhammad Akhtar

Reputation: 52241

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
}

Upvotes: 1

Andriy M
Andriy M

Reputation: 77657

You code samples do not correlate well with the rest of the information provided. Without the code your post seems consistent enough, though, so I'm inclined to consider those snippets as simply erroneous.

Anyway, your idea seems clear. In SQL Server 2005+ you could solve your problem with an INSERT statement like this one:

string insertCommand = 
    "INSERT INTO Topics (Theme, Topics, Date) " +
      "OUTPUT 'CONVERT(uniqueidentifier, '" + giveMeGuidID() +
      "'), INSERTED.TopicID, @dateTime, @questionTitle, @subTopic " +
      "INTO Threads (UserID, TopicID, Date, ThreadTitle, ThreadParagraph) " +
    "VALUES (@Theme, @Topics, @Date)";

Although this is a single statement, it performs two inserts into different tables. The 'main' insert is being made into the Topics table. The 'secondary' one, into Threads, is defined by the OUTPUT...INTO clause. Basically, the OUTPUT clause allows you to reference the data being inserted and either return them as a rowset to the client, or (when combined with INTO) direct them into an existing table, like you can see it done here.

Upvotes: 0

Akram Shahda
Akram Shahda

Reputation: 14771

If you are looking for something reliable you need to use Transactions.

Refer to Managing Transactions in SQL Server Stored Procedures to get an idea.

Also, take a look at Controlling Transactions (Database Engine) and SQL Server Transaction Isolation Models.

You also need to use @@Identity the last-inserted identity value.

Upvotes: 0

vityanya
vityanya

Reputation: 1176

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

Upvotes: 2

Related Questions