how to get the id of the inserted datarow?

  • Thread starter Thread starter =?iso-8859-1?q?Norbert_P=FCrringer?=
  • Start date Start date
?

=?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
 
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
 
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.
 
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)
-----------------------------------------------------------------------------------------------------------------------

Fatih Isikhan said:
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.

Norbert Pürringer said:
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
Click to expand...
Click to expand...
 
Back
Top