how to get the id of the inserted datarow?

[=?iso-8859-1?q?Norbert_P=FCrringer?=]

Hello there,

after inserting a row, I want to return the id of the inserted row.
This id could be a number or a string (e.g. GUID values). The database
could be SQL Server, Oracle, MySQL or Access. Is there a database
independent way to return the ID of the inserted data row?

My way does only work, if the ID is a sequence value of an Oracle
database. In each other case the ID, which I want to return, has the
value DBNull:

public int InsertDataRow(string tableName, IDictionary columnValues,
string idName, string idGenerator, out object idValue)
{
idValue = 0;

AddProperIdValue(tableName, columnValues, idName,
idGenerator, true);

IDbConnection conn = GetConnection();
try
{
string fields = null;
foreach (DictionaryEntry item in columnValues)
if (!_factory.IsGeometryValue(item.Value))
fields += item.Key.ToString() + ",";
if (!columnValues.Contains(idName))
fields += idName;
if (fields.EndsWith(","))
fields = fields.Substring(0, fields.Length - 1);

string sql = "SELECT " + fields + " FROM " + tableName
+ " WHERE 0=1";
IDbCommand selectcmd = _factory.CreateCommand(sql,
conn);
selectcmd.Transaction = _transaction;

IDbCommand insertcmd = CreateInsertCommand
(tableName, columnValues, conn, true);

try
{
IDbDataAdapter dataAdapter =
_factory.CreateAdapter(selectcmd);
dataAdapter.InsertCommand = insertcmd;

DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
DataRow dataRow = dataTable.NewRow();
foreach (DictionaryEntry item in columnValues)
dataRow[item.Key.ToString()] = item.Value;
dataTable.Rows.Add(dataRow);
int affectedrows = dataAdapter.Update(dataSet);

idValue = dataRow[idName];

return affectedrows;
}
catch (Exception ex)
{
Rollback();
throw CreateDataException
(ex, "Error inserting data row!\n" +
insertcmd.CommandText,
tableName, columnValues, true);
}
}
finally
{
CloseConnection(conn);
}
}

The important line in the upper code is idValue = dataRow[idName];
This line returns in the most cases DBNull instead of giving me the
new ID value of the inserted datarow.

What can be done to solve my problem?

Regards,
Norbert

 

[Cor Ligthert[MVP]]

Hi Norbert,

Why do you want to get a Guid back, you can just set it in advance. It is
Globally Unique you know.

Cor

 

[Guest]

In SQL Server, if your table contains identity column, the identity value is
stored in @@IDENTITY and it is probably the unique id that you want.

 

[William Vaughn]

Very true, but it's an unreliable global variable. We recommend use of
SCOPE_IDENTITY() instead.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

In SQL Server, if your table contains identity column, the identity value
is
stored in @@IDENTITY and it is probably the unique id that you want.

Hello there,

after inserting a row, I want to return the id of the inserted row.
This id could be a number or a string (e.g. GUID values). The database
could be SQL Server, Oracle, MySQL or Access. Is there a database
independent way to return the ID of the inserted data row?

My way does only work, if the ID is a sequence value of an Oracle
database. In each other case the ID, which I want to return, has the
value DBNull:

public int InsertDataRow(string tableName, IDictionary columnValues,
string idName, string idGenerator, out object idValue)
{
idValue = 0;

AddProperIdValue(tableName, columnValues, idName,
idGenerator, true);

IDbConnection conn = GetConnection();
try
{
string fields = null;
foreach (DictionaryEntry item in columnValues)
if (!_factory.IsGeometryValue(item.Value))
fields += item.Key.ToString() + ",";
if (!columnValues.Contains(idName))
fields += idName;
if (fields.EndsWith(","))
fields = fields.Substring(0, fields.Length - 1);

string sql = "SELECT " + fields + " FROM " + tableName
+ " WHERE 0=1";
IDbCommand selectcmd = _factory.CreateCommand(sql,
conn);
selectcmd.Transaction = _transaction;

IDbCommand insertcmd = CreateInsertCommand
(tableName, columnValues, conn, true);

try
{
IDbDataAdapter dataAdapter =
_factory.CreateAdapter(selectcmd);
dataAdapter.InsertCommand = insertcmd;

DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
DataRow dataRow = dataTable.NewRow();
foreach (DictionaryEntry item in columnValues)
dataRow[item.Key.ToString()] = item.Value;
dataTable.Rows.Add(dataRow);
int affectedrows = dataAdapter.Update(dataSet);

idValue = dataRow[idName];

return affectedrows;
}
catch (Exception ex)
{
Rollback();
throw CreateDataException
(ex, "Error inserting data row!\n" +
insertcmd.CommandText,
tableName, columnValues, true);
}
}
finally
{
CloseConnection(conn);
}
}

The important line in the upper code is idValue = dataRow[idName];
This line returns in the most cases DBNull instead of giving me the
new ID value of the inserted datarow.

What can be done to solve my problem?

Regards,
Norbert