I do not know of one command/function (although see below) that will work for Access, MySql and SqlServer.
My Sql uses
LAST_INSERT_ID()
or
@@IDENTITY
(although there are reports that this is broken), Sql Server uses
SCOPE_IDENTITY()
or
IDENT_CURRENT('table')
(depending on the circumstances) or
@@IDENTITY
although once again this is deprecated (and possibly broken) and according to
this article[
^] (scroll down to the 'Access' heading) JET/OLEDB now also supports
@@IDENTITY
This, from MSDN[
^] (See the Remarks Section) should help you decide which to use for Sql Server.
So, provided you can confirm for yourself that @@IDENTITY works for all the systems you want to use, you might be able to use that.
Otherwise something like:
public enum DBSystem
{
Access,
MySql,
SqlServer
}
public string GetNextIDString(DBSystem system)
{
string nextIDString = "SELECT ";
switch (system)
{
case DBSystem.Access:
nextIDString += "@@IDENTITY .............";
break;
case DBSystem.MySql
nextIDString += "LAST_INSERT_ID(Contracts) .............";
break;
case DBSystem.SqlServer:
nextIDString += "@@IDENTITY .............";
nextIDString += "SCOPE_IDENTITY .............";
nextIDString += "IDENT_CURRENT(Contracts) .............";
break;
}
return nextIDString;
}
I hope that some of this helps.