Dataset: How to capture the primary key value returned by SELECT SCOPE_IDENTITY()?

Question

I'm using a dataset. I have a table Adapter called PackageTableAdapter, which contains a method called InsertPackage.

INSERT INTO [dbo].[Packages] ([UserID], [Name]) VALUES (@UserID, @Name)

//Return the PackageID value for the newly created record...
SELECT SCOPE_IDENTITY()

This table adapter is being used by a middle tier class, with the below code:

private PackagesTableAdapter _packagesTableAdapter = null;
protected PackagesTableAdapter Adapter
{
    get
    {
        if (_packagesTableAdapter == null)
            _packagesTableAdapter = new PackagesTableAdapter();

        return _packagesTableAdapter;
    }
}    

public bool AddPackage(string PackageName)
{
    // Create a new PackageRow instance
    Album.PackagesDataTable packages = new AlbumCongo.PackagesDataTable();
    Album.PackagesRow package = packages.NewPackagesRow();

    // Add the new package
    package.Name = PackageName;
    packages.AddPackagesRow(package);
    int rowsAffected = Adapter.Update(packages);       

    // Return true if precisely one row was inserted,
    // otherwise false
    return rowsAffected == 1;        
}

How do I capture the primary key of the newly created package (the one that's supposed to returned by the SELECT SCOPE_IDENTITY() statement)?

EDIT

Instead of returning a bool value, I'd like to return a custom object that contains both the bool value and an int representing the ID of the newly created row.

Thanks for helping.

If you're using data adapters, you can check out: http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx. You'll probably have to wrangle your own custom object though. — womp, May 19 '11 at 23:23
In the code, there's a way of telling how many rows were affected. Isn't there an easy way for retrieving the newly created ID? — Richard77, May 19 '11 at 23:33

score 2 · Accepted Answer · edited Feb 17 '16 at 15:35

2

First of all you have to make the return type of the insert method to Scaler. You can also do so by right clicking the properties of your insert Method properties.

Secondly you can get the ID by calling the Adapter method like this:

Int32 ID = Convert.ToInt32(Adapter.Insert(parameters...);

edited Feb 17 '16 at 15:35

vard

4,057
2
26
46

answered May 20 '11 at 04:14

Muhammad Akhtar

51,913
37
138
191

score 1 · Answer 2 · answered May 20 '11 at 03:25

1

Check the PackageID on the DataRow object AFTER the Adapter.Update call.

answered May 20 '11 at 03:25

ethorn10

1,889
1
18
29

Dataset: How to capture the primary key value returned by SELECT SCOPE_IDENTITY()?

2 Answers2