"Fatih Isikhan" <Fatih Isikhan@discussions.microsoft.com> wrote in message
news:6A86FA61-3351-414B-BBFD-EBEA4C8B65F5@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>