How to insert data row and reading out the ID of the new dataset

[thalion77]

Hello there,

what is the best way to add an dataset and, then, to read out the ID
value of the created dataset?

ExecuteNonQuery() does not retrieve informations about the new ID of
the dataset. Of course I can execute a statement like this: SELECT
MAX(ID) from TableName, but this would not work, if the ID is of type
string (this could be if someone likes to create a GUID string as ID
value).

Is there a possibility to work with a Dataset by setting the
SelectCommand and UpdateCommand properties? How to I define the
SelectCommand to fetch the ID of the new dataset?

Regards,
Norbert

 

[Sheng Jiang[MVP]]

If your dataset supports the UpdateCommand property, There could be another
property named InsertCommand.

 

[thalion77]

Hello Sheng,

If your dataset supports the UpdateCommand property, There could be another
property named InsertCommand.

but how do I get the ID of the inserted dataset?

Norbert

 

[Sheng Jiang[MVP]]

If you use tableadapter, the insert method will return the id

 

[Gregg Walker]

Norbert -- I use a method like this to retrieve Identity values from a Sql
Server database. I retrieve the value of scope_identity() immediately after
the insert is performed by the ExecuteNonQuery.

public object ExecuteIdentity(SqlCommand insertCommand)
{
insertCommand.ExecuteNonQuery();

using(SqlCommand identityCommand = new SqlCommand("select
scope_identity() as IdentityValue"))
{
return identityCommand.ExecuteScalar();
}
}

By returning object the type of the identity column does not matter.

I haven't used the TableAdapter as Sheng suggested but I'm sure that would
work equally as well if you want to go that route.

 

[Gregg Walker]

Norbert -- Sorry I cobbled together that method from my data access layer.
It would not work without setting the identityCommand Connection and
Transaction properties. Here's a better example method.

public object ExecuteIdentity(SqlCommand insertCommand)
{
// Assumes that insertCommand Connection and Transaction have already
been set...
insertCommand.ExecuteNonQuery();

using(SqlCommand identityCommand = new SqlCommand("select
scope_identity() as IdentityValue"))
{
identityCommand.Connection = insertCommand.Connection;
identityCommand.Transaction = insertCommand.Transaction;

return identityCommand.ExecuteScalar();
}
}

 

[thalion77]

Hello there,

I wrote following code to insert a data row and returning the ID of
the new row:

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

IDbConnection conn = GetConnection();
try
{
string fields = null;
foreach (DictionaryEntry item in columnValues)
if (!_factory.IsGeometryValue(item.Value))
fields += item.Key.ToString() + ",";
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);
}
}


That works very fine, but I've got a problem inserting a value, which
is of userdefined type. In Oracle this could be the Oracle Spatial
type MDSYS.SDO_GEOMETRY.

Columns containing userdefined types cannot be selected.

e.g. "SELECT ID, SDOGEOMETRY FROM Table" would raise an error.

As you see in the upper code, I cannot easily insert an userdefined
type because I have to select the column with the userdefined type.

My trick is not to select the column of userdefined type, but to
specify it in the INSERT statement like this

SelectCommand = "SELECT ID FROM Table"
InsertCommand = "INSERT INTO Table (ID, SDOGEOMETRY) VALUES p0,
MDSYS.SDO_GEOMETRY(...))"

I thought that it would work to specify the geometry (userdefined
value) hard-coded without any parameter. But I've got no chance: It is
not possible to specify a column in the INSERT statement, which does
not exist in the SELECT command.

Has anyone an idea how to insert a value of userdefined type into a
table by using the SelectCommand and InsertCommand properties of a
data adapter?

Regards,
Norbert