Sunday, December 04, 2005

@@Identity Equivalent in MySql - Getting Last Inserted ID in MySQL

As a .NET Developer i really get used to Sql Server until I got a small project that require me to developed with MySql database backend. The first thing that I face with is to figure out the equivalent of @@Identity in MySql, got it , LAST_INSERT_ID() in MySql.

The second problem is how to return that function value to the application from the database. Since i am using a straight SQL. Then it turns out that below is my solution. I use execute scalar to retrieve the value of the last inserted id and use the usual executeNonQuery for update. Done.

Example:


comm = New MySqlCommand

param = New MySqlParameter("?MemberID", MySqlDbType.Int32)



If _MemberID = 0 Then

comm = New MySqlCommand("Insert Into Member(FirstName,MiddleName,LastName) VALUES (?FirstName,?MiddleName,?LastName);SELECT LAST_INSERT_ID();")

Else

comm = New MySqlCommand("Update Member( SET FirstName = ?FirstName,MiddleName = ?MiddleName,LastName = ?LastName) WHERE MemberID = ?MemberID; ")

End If

param.Direction = ParameterDirection.Input

comm.Parameters.Add(param)

comm.Connection = conn

With comm.Parameters

.Add(param)

param = New MySqlParameter("?FirstName", MySqlDbType.String)

param.Value = _FirstName

.Add(param)

param = New MySqlParameter("?MiddleName", MySqlDbType.String)

param.Value = _MiddleName

.Add(param)

param = New MySqlParameter("?LastName", MySqlDbType.String)

param.Value = _LastName

.Add(param)
conn.Open()

If _MemberID = 0 Then

_MemberID = comm.ExecuteScalar()

Else

comm.ExecuteNonQuery()

End If

conn.Close()