Problem with an autoincrement field

kermitfrosch posted on Thursday, September 22, 2011

Hallo users,

i am a new user with csla.net and i have the following problem:

---- console programm --

Dim user = UserEdit.NewUserEdit
user.FirstName = "Max"
user.Name = "Mustermann"
user.Save()
Out(user.UserId) < -- the user.UserId = -1

---- Data Access ----
Public Sub Insert(ByRef data As DataTransferObjects.UserDto) Implements Contracts.IUserDal.Insert
  Using ctx = ConnectionManager(Of SqlConnection).GetManager(DataBaseConstants.DataBaseConfigName)
    Dim cm = ctx.Connection.CreateCommand
    cm.CommandType = CommandType.Text
    cm.CommandText = "INSERT INTO portaluser VALUES(@FIRSTNAME, @NAME)"
    cm.Parameters.Add(New SqlParameter("@FIRSTNAME", data.firstname))
    cm.Parameters.Add(New SqlParameter("@NAME", data.name))
    cm.ExecuteNonQuery()
    cm.Parameters.Clear()
    cm.CommandText = "SELECT @@identity"
    Dim r = cm.ExecuteScalar
    Dim newId = Integer.Parse(r.ToString)
    data.id = newId < -- correct id
  End Using
End Sub


---- business object --
<Transactional(TransactionalTypes.TransactionScope)> _
Protected Overrides Sub DataPortal_Insert()
  Using dalManager = DataAccess.DalFactory.GetManager()
    Dim dal = dalManager.GetProvider(Of DataAccess.Contracts.IUserDal)()
      Using BypassPropertyChecks
        Dim data = New AgPortal.DataAccess.DataTransferObjects.UserDto(UserId, FirstName, Name)
        dal.Insert(data)
        Me.UserId = data.id < -- the UserId is correct
      End Using
  End Using
End Sub

Why does the id don't pass the buusiness object?

JonnyBee replied on Thursday, September 22, 2011


Try this code: 

Public Sub Insert(ByRef data As DataTransferObjects.UserDto) Implements Contracts.IUserDal.Insert
  Using ctx = ConnectionManager(Of SqlConnection).GetManager(DataBaseConstants.DataBaseConfigName)
    Dim cm = ctx.Connection.CreateCommand
    cm.CommandType = CommandType.Text
    cm.CommandText = "INSERT INTO portaluser VALUES(@FIRSTNAME, @NAME); SELECT SCOPE_IDENTITY()"
    cm.Parameters.Add(New SqlParameter("@FIRSTNAME", data.firstname))
    cm.Parameters.Add(New SqlParameter("@NAME", data.name))
    Dim r = cm.ExecuteScalar
    Dim newId = Integer.Parse(r.ToString)
    data.id = newId < -- correct id
  End Using
End Sub

NEVER USE @@IDENTITY FOR THIS PURPOSE. IT SHOULD BE CURSED....... 

SCOPE_IDENTITY() is the ONLY proper function to use.

kermitfrosch replied on Thursday, September 22, 2011

Hallo,

i think, the problem is not in my dataaccess code.

the dataaccess code gives me the correct value.

Public Sub Insert(ByRef data As DataTransferObjects.UserDto) Implements Contracts.IUserDal.Insert
 Using ctx = ConnectionManager(Of SqlConnection).GetManager(DataBaseConstants.DataBaseConfigName)
   Dim cm = ctx.Connection.CreateCommand
   cm.CommandType = CommandType.Text
   cm.CommandText = "INSERT INTO portaluser VALUES(@FIRSTNAME, @NAME); SELECT SCOPE_IDENTITY()"
   cm.Parameters.Add(New SqlParameter("@FIRSTNAME", data.firstname))
   cm.Parameters.Add(New SqlParameter("@NAME", data.name))
   cm.ExecuteNonQuery()
   Dim r = cm.ExecuteScalar
   ' ################## This works correct ####################
   Dim newId = Integer.Parse(r.ToString)
   ' ##########################################################
   data.id = newId 
 End Using
End Sub

<Transactional(TransactionalTypes.TransactionScope)> _
Protected Overrides Sub DataPortal_Insert()
  Using dalManager = DataAccess.DalFactory.GetManager()
    Dim dal = dalManager.GetProvider(Of DataAccess.Contracts.IUserDal)()
    Using BypassPropertyChecks
      Dim data = New AgPortal.DataAccess.DataTransferObjects.UserDto(UserId, FirstName, Name)
      dal.Insert(data)
      '################# This works correct #########
       Me.UserId = data.id
      '#############################################
     End Using
  End Using
End Sub

Sub Main()
  Dim user = UserEdit.NewUserEdit
  user.FirstName = "Max"
  user.Name = "Mustermann"
  user.Save()
  ' ################## here i get -1 ################
  Out(user.UserId)
  ' #################################################

End Sub