Using Scope Identity from one SP in another SP

[primagic]

Hi.

I am wondering how I would use the scope identity created in one SP and use it in another SP directly afterwards. I am trying to add a new registration to the database, get the last RegistrationID from that registration and then use it to add selected locations and registrationid in another table.

My SP for Registrations are below:

USE [KeyStrategy]
GO
/****** Object:  StoredProcedure [dbo].[AddNewRegistration]    Script Date: 09/22/2011 22:16:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddNewRegistration]
(
@Title char(10),
@Firstname varchar(50),
@Surname varchar(50),
@Phone varchar(50),
@OtherPhone varchar(50),
@EmailAddress nvarchar(max),
@TravelDistance numeric(4,0),
@MinDayRate decimal(18,2),
@MaxDayRate decimal(18,2)

)
AS
BEGIN

SET NOCOUNT ON;
DECLARE @RegistrationID Int

INSERT INTO dbo.Registrations(Title, Firstname, Surname, Phone, OtherPhone, EmailAddress, TravelDistance, MinimumDayRate, MaximumDayRate  )
VALUES (@Title, @Firstname, @Surname, @Phone, @OtherPhone, @EmailAddress, @TravelDistance, @MinDayRate, @MaxDayRate)
SELECT @RegistrationID = scope_identity()

SELECT @RegistrationID As RegistrationID
END

My stored procedure for Locations:

USE [KeyStrategy]
GO
/****** Object:  StoredProcedure [dbo].[AddLocations]    Script Date: 09/22/2011 22:17:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddLocations]
(
@RegistrationID int,
@Location varchar(50)


)
AS
BEGIN

SET NOCOUNT ON;

INSERT INTO dbo.Locations(RegistrationID, Location )
VALUES (@RegistrationID, @Location)


END

My codebehind to add the registration is:

Protected Sub btnRegister_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRegister.Click
        
        Dim ConnString As [String] = ConfigurationManager.ConnectionStrings("KeyStrategyConnectionString").ConnectionString
        Dim con As New SqlConnection(ConnString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "AddNewRegistration"
        cmd.Parameters.Add("@Title", SqlDbType.VarChar).Value = cboTitle.SelectedItem.Text.Trim()
        cmd.Parameters.Add("@Firstname", SqlDbType.VarChar).Value = txtFirstname.Text.Trim()
        cmd.Parameters.Add("@Surname", SqlDbType.VarChar).Value = txtSurname.Text.Trim()
        cmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = txtPhone.Text.Trim()
        cmd.Parameters.Add("@OtherPhone", SqlDbType.VarChar).Value = txtPhone2.Text.Trim()
        cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar).Value = txtEmail.Text.Trim()
        cmd.Parameters.Add("@TravelDistance", SqlDbType.VarChar).Value = txtMiles.Text.Trim()
        cmd.Parameters.Add("@MinDayRate", SqlDbType.Decimal).Value = txtMinDayRate.Text.Trim()
        cmd.Parameters.Add("@MaxDayRate", SqlDbType.Decimal).Value = txtMaxDayRate.Text.Trim()


        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteScalar()


        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
            con.Dispose()
        End Try
        AddLocation()
    End Sub

Then my code for AddLocation()

Private Sub AddLocation()
        Dim i As Integer
        Dim chkbx As CheckBoxList

        chkbx = CType(form1.FindControl("chkListLocations"), CheckBoxList)
        For i = 0 To chkbx.Items.Count - 1
            Dim ConnString As [String] = ConfigurationManager.ConnectionStrings("KeyStrategyConnectionString").ConnectionString
            Dim con As New SqlConnection(ConnString)
            Dim cmd As New SqlCommand()
            Dim ID As Integer
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "AddLocations"
            If chkbx.Items(i).Selected Then
                cmd.Parameters.AddWithValue("@Location", chkbx.Items(i).Value)
                cmd.Connection = con
                Try
                    con.Open()
                    cmd.ExecuteNonQuery()
                    cmd.Parameters.Clear()

                Catch ex As Exception
                    Throw ex
                Finally
                    con.Close()
                    con.Dispose()
                End Try


            End If
        Next
    End Sub

Just need to know how I get the last ID from the registration and where to put it in the AddLocation sub

Thanks

 

[jmeckley]

as it stands now you have two completely independent operations. unique connections, commands, and no transactional support. I would alter the operation to look something like this
[tt]
1. using a connection
2. begin transaction
3. create a command to
3.1. insert the new registration
3.2. return the id
4. create another command to insert the location using the registration id
5. commit transaction or
5.1. catch exception, rollback transaction and rethrow
6. close connection
[/tt]
the code would look something like this

var factory = DbConnectionProviderFactory.GetFactory(prodvidername);
using(var connection new = factory.CreateConnection())
{
   connection.ConnectionString = connectionstring;
   connection.Open();
   using(var tx = connection.BeginTransaction())
   {
      try
      {
         object registrationid;

         //insert registration
         using(var command = connection.CreateCommand())
         {
            ...setup sql statements and parameters...
            registrationid = command.ExecuteScalar();
         }

         //insert location
         using(var command = connection.CreateCommand())
         {
            ...setup sql statements and parameters...
            ...use registration id from the previous command...
            command.ExecuteNonQuery();
         }
         tx.Commit();
      }
      catch
      {
         tx.Rollback();
         throw;
      }
   }
}

and the new registration sql statement will look like this

INSERT INTO Registrations(Title, Firstname, Surname, Phone, OtherPhone, EmailAddress, TravelDistance, MinimumDayRate, MaximumDayRate)
VALUES (@Title, @Firstname, @Surname, @Phone, @OtherPhone, @EmailAddress, @TravelDistance, @MinDayRate, @MaxDayRate)
SELECT scope_identity()

I also posted a FAQ about managing database connections on this forum. the link is below in my signature.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[primagic]

What are your lines:

var factory = DbConnectionProviderFactory.GetFactory(prodvidername);
using(var connection new = factory.CreateConnection())

Is that the same as below:

 Dim ConnString As [String] = ConfigurationManager.ConnectionStrings("KeyStrategyConnectionString").ConnectionString
        Dim con As New SqlConnection(ConnString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "AddNewRegistration"

 

[jmeckley]

DbConnectionProviderFactory.GetFactory(prodvidername) doesn't require me to use a MS SQL database. DbConnectionProviderFactory will use the appropriate IDbConnection implementation based on the provider I set in the web.config.

I use this method, either directly or indirectly, with all my projects. this way I can run unit tests against an in memory SqLite database and MS SQL Server for staging & production without altering my code.

basically it's the practice of coding to the interface not the implementation and utilizes the principle of dependency inversion.

the using keyword will automatically dispose of my objects when they go out of scope. "using" is really just syntax sugar for

IDisposable disposable;
try
{
   disposable = new ObjectThatImplementsIDisposable();
   ... use disposable...
}
finally
{
   if(disposable != null)
   {
      disposable.Dispose();
   }
}

Jason Meckley
Programmer

faq855-7190
faq732-7259