CODEWITHSUNDEEP
c#asp.netsqlsql-server
Ali
Ali

Reputation: 2592

Insert data within multiple tables in SQL Server 2008

I have 3 different tables in my database as below;

Table 1:
ResourceID |  ResourceTitle|  ResourceCategory

Table 2:
DocumentID|  DocName  |  DocSize

Table 3:
ResourceID  |  DocumentID

Now I want to add some values into the above tables (using a C# .ASP.NET) respectively.

All of the processes need to be done in one single transaction, so there would be a loop for the second table data in my asp.net c# class.

the problem that I have is finding the right way to handle the transaction to do this job, also I need to make sure while these processes are running it doesn't let the other users to modify these data.

Your help would be appreciated.

Upvotes: 2

Views: 6681

Answers (3)

Devraj Gadhavi
Devraj Gadhavi

Reputation: 3611

You can use table valued parameters in stored procedures.

This is a good LINK how to create & use TVP in stored procedures.

Then you need to pass TVP from your page/form.

You can have a look HERE to get an idea for that.

Example for creating table type :

CREATE TYPE yourDocumentTableType AS TABLE 
(
    docID INT NOT NULL IDENTITY(1, 1),
    docName VARCHAR(50),
    docSize INT 
)
GO

Your stored procedure should be like this

CREATE PROCEDURE yourInsertOperation 
    -- Add the parameters for the stored procedure here
    @Param1 INT, 
    @Param2 VARCHAR(20),
    --......your parameter list goes here
    -- here you would pass the records for your document table
    @YourTableValuedParam dbo.yourDocumentTableType READONLY 
AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- declare temp table type & assign the TVP to it
        DECLARE @TempDocuments dbo.yourDocumentTableType

        INSERT INTO @TempDocuments (docName, docSize)
        SELECT docName, docSize FROM @YourTableValuedParam

        DECLARE @ResourceID BIGINT            

        --Marking the start of a transaction
        BEGIN TRANSACTION

        --Inserting records into resource table
        INSERT INTO resourceTable(column1, column2) VALUES (@param1, @param2)

        --selecting resourceID to be inserted into document table & resource-document table
        SELECT @ResourceID = SCOPE_IDENTITY()

        WHILE EXISTS(SELECT * FROM @TempDocuments)
            BEGIN                        
                DECLARE @DocumentID BIGINT

                --Inserting records into document table from the table valued parameter
                INSERT INTO documentTable(docName, docSize, resourceID)
                SELECT TOP 1 docName, docSize, @ResourceID FROM @TempDocuments

                SELECT @DocumentID = SCOPE_IDENTITY()
                INSERT INTO resouceDocumentTable(resourceID, docID) VALUES (@ResourceID, @DocumentID)

                DELETE TOP (1) FROM @TempDocuments
            END

    --Checking for any error in the whole transaction (all 3 table insert operation)
    IF @@ERROR > 0
        --rollback if there was any error
        ROLLBACK TRANSACTION
    ELSE
        --commit whole transaction (all 3 table insert operation)
        COMMIT TRANSACTION

END
GO

Upvotes: 1

Deepak Saralaya
Deepak Saralaya

Reputation: 457

try this

DECLARE @ResourceID int
 DECLARE @DocumentID int
 Insert into Table 1(ResourceTitle,  ResourceCategory)value("values","values")          

 SELECT @ResourceID=SCOPE_IDENTITY()
 Insert into Table 2( DocName , DocSize) value("values","values")
 SELECT @DocumentID =SCOPE_IDENTITY()
 Insert into Table 3( ResourceID  ,  DocumentID) values(@ResourceID ,@DocumentID)

Upvotes: 2

Tushar Maru
Tushar Maru

Reputation: 3431

You can use transaction scope as you can see this link

http://www.dotnetjalps.com/2010/05/using-transactions-with-linq-to-sql.html

Upvotes: 1

Related Questions