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()
