GeekZilla

Return new identity from Strongly Typed Dataset DataTable.Insert method

Datasets are pretty good at auto generating stored procedures and wrapping c# code around them for you.

However with the insert method you often want to do something with the object that you have just inserted.

Fortunately there is an easy way to get the Sproc and the c# method to return a reference to the object.

Firstly edit your insert stored procedure adding a @return parameter and a line after the insert to set this to a suitable value.

The exmaple below assumes that you are using bigint identity fields.

ALTER PROCEDURE dbo.insMyRow
(
    @Description varchar(500),
    @Return bigint output
)
AS
    SET NOCOUNT OFF;
INSERT INTO [myTable] ([Description]) VALUES (@Description);
    
SET @Return = SCOPE_IDENTITY()

Note that SCOPE_IDENTITY() is similar to @@IDENTITY except its scope is limited to the current command and so improves scalability.

Now when you save the Dataset you see that the insert method takes two parameters, the second being a nullable long.

My first thought was that Datasets should be the end of editing stored procedures but I am still impressed that the c# method declaration changes accordingly.

You can call the updated method in the following way.

long? objId=null;

myTableAdapter d =new myTableAdapter();

d.Insert("Descriptive text", ref objId);

the long? just means nullable long.

After filling the table you can now use the FindByxxxID functions of the DataTable to return the DataRow object.

Author Dave Howard

I have been involved in IT development for the last 10 years - a lot of it around desktop applications and telecoms.

Comments

Add Comment

Latest Articles