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

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

-- 
Sheng Jiang
Microsoft MVP in VC++
 wrote in message
news:1184587035.203992.129260@n2g2000hse.googlegroups.com...

> 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
>

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

If you use tableadapter, the insert method will return the id
-- 
Sheng Jiang
Microsoft MVP in VC++
 wrote in message
news:1184665106.199287.181990@z28g2000prd.googlegroups.com...

> 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
>

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();
   }
  }

--
Gregg Walker

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