SQLCE DataAdapter Update with Identity field

[PeterB]

Hi!

I'm using SQLCE on a pocket pc and I have a database table with an identity field. I use a sqlcedataadapter to fill, insert, delete etc. a dataset datatable. I've read that I need to take some extra steps to retrieve the incremented identity value when performing an insert.

The main problem is that the identity field of the dataset table is also an identity field but "on it's own" as it seems. I.e. when I create a new record it puts it's own increment in the value of the field without notice of the database's increment value. So the question is how should this be resolved in SSCE?

1. Identity field in dataset table is readonly
Since I use the fillschema method to fill the dataset table with it's schema it automatically sets the identity field to an identity field, and hence readonly. Should I explicitly remove the identity property and readonly property after filling the schema? By doing that I could update the identity field with the correct value from the database. What are the consequences of this?

2. Updating the value of the dataset table identity field
2.1 My first try was to add code for this in the RowUpdated event that is fired when I execute the adapter.Update() method. The code I use for this is:

private static void sqlAdapt_RowUpdated(object sender, SqlCeRowUpdatedEventArgs e)
{
// Include a variable and a command to retrieve the identity value from the database.
    object obj = null;
    int newID = 0;
    SqlCeCommand sqlCmd = new SqlCeCommand("SELECT @@IDENTITY", sqlConn);
    if (e.StatementType == StatementType.Insert)
    {
        // Retrieve the identity value and store it in the LOPNR column.
        obj = sqlCmd.ExecuteScalar();
        //newID = (int)sqlCmd.ExecuteScalar();
        System.Data.SqlTypes.SqlDecimal dec;
        dec = (System.Data.SqlTypes.SqlDecimal)obj;
        newID = Convert.ToInt32(dec.ToString());
        e.Row["LOPNR"] = newID;
    }
}

 

This great if I unset the identity and readonly property of the LOPNR column in the dataset table.If I don't unset those properties I get an exception telling me LOPNR is readonly. My SqlCeAdapter Insert commands does not have a parameter for the identity field here.

 

2.2 My second choice was from a post by David Sceppa on 2003-02-26 10:38:24 PST. He suggests adding a parameter for the identity field and modify the InserCommand as shown below (userid is equal to lopnr in my code):

 

INSERT INTO [users] ([firstname]) VALUES (@firstname);
SET @userid = @@IDENTITY

insertPrm = insertCmd.Parameters.Add("@userID", SqlDbType.Int, 0, "userid")
insertPrm.Direction = ParameterDirection.Output
insertCmd.UpdatedRowSource = UpdateRowSource.OutputParameters
dataadapter.InsertCommand = insertCmd

Questions:

1. Do I need to unset the dataset table readonly/identity property in 2.2 as well?

2. Will 2.2 work in SSCE?

3. Which method is recommended?

4. Can the update row event cause the wrong identity value to be added into the dataset table?

5. Is there a solution where you don't need to unset the identity/readonly properties in the dataset?

6. Is it even correct that the column is set to identity using FillSchema method? That increment will restart each time a application is restarted while the SSCE identity increment is permanent (until db is deleted). So eventually they will be out of sync won't they?

 

br,

 

 Peter

 

[Rajesh Patel]

I think, you don't need to do anything special except append your selectcommand text after your insertcommand text in the dataadapter.

 

e.g.

 

you select command is :

 

select * from itemmaster         ( where itemid is identity column)

 

your insert command would be

 

insert into itemmaster(col2, col3...) values (col2, col3....)            ( whatever it is , it's not important for identitycolumn.)

select * from itemmaster where itemid = @@identity                  ( this would be the only important thing to check out in your code).

 

 

don't worry about dataset automatically set new values when you insert row in dataset. your select statement in insertcommand overwrite the value set by dataset with newvalue from database.

 

Rajesh Patel

 

 

 

 

"PeterB" <pe...@data.se> wrote in message news:%23%23g$xEYjDH...@TK2MSFTNGP11.phx.gbl...

[PeterB]

Ah.. so that is kinda like method my method 2 but without adding the identity column as a parameter in the insert command?

 

Thanks for that, I will try it out asap!

 

 / Peter

"Rajesh Patel" <rdp...@hotmail.com> wrote in message news:%23VpAM$bjDHA...@TK2MSFTNGP09.phx.gbl...

[PeterB]

I have now tried to modify my insert command as you said without success. When the adapter.Update() method is executed an exception without name or message is caught?!?! It's just an empty no-name exception... and the row is never even inserted into the database at all.

 

I tried both with and without a semi-colon between the insert and select part of the insert command.

 

Maybe this is a SQL CE limitation, I will go and see what they say over in that newsgroup.

 

Thanks for the help anyway! If anyone has any other suggestions I'd be greatful!

 

 / Peter

 

"PeterB" <pe...@data.se> wrote in message news:OZiP7$ijDHA...@tk2msftngp13.phx.gbl...

[Kevin Sun [MS]]

Hi Peter,

The following information is from an article. Please let me know if it
helps you on this issue:

====================================================

Retrieving Newly Generated Autoincrement Values
====================================================

SQL Server, Access, Sybase, and other databases use autoincrement columns
(also referred to as identity columns). You can insert a new row into a
table, and the database will generate a new value for the autoincrement
column for that row. Many tables in the Northwind database—such as
Employees, Orders, and Products—use autoincrement columns for their primary
keys.

Why does working with autoincrement columns constitute an advanced updating
scenario? You can submit the new row to your table, but the database will
generate the value for that row’s autoincrement column. This means you
won’t know the value of the autoincrement column for your new row once
you’ve submitted the new row to your table. Generally speaking, you want to
know the primary key value for your rows.

So how do you use the ADO.NET object model to retrieve the newly generated
autoincrement value for your row?

Working with SQL Server
-------------------------

For the moment, imagine that you’re not submitting changes to your database
using a DataAdapter. Let’s say you’re building your own queries to submit
changes.

If you’re working with order information from the Northwind database, you
might use the following query to retrieve data from your table:

SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders
To insert a new row into your table, you might issue a query like this:

INSERT INTO Orders (CustomerID, EmployeeID, OrderDate) VALUES (?, ?, ?)
To retrieve the autoincrement value that the database generated for your
new row, you might use the following query:

SELECT @@IDENTITY

This query is the key to retrieving the new autoincrement value. We can use
this query in the ADO.NET object model the same way we used the query to
retrieve the timestamp value in the previous example.

We can modify the CommandText of the DataAdapter object’s Insert­Command to
execute the SELECT @@IDENTITY query after each insert:

INSERT INTO Orders (CustomerID, EmployeeID, OrderDate) VALUES (?, ?, ?);
SELECT @@IDENTITY AS OrderID
Note that the SELECT @@IDENTITY query includes an alias so that the Command
knows the column in which it should store the results of the query.

As with fetching new timestamp values, we can also return the new
autoincrement value by using a stored procedure output parameter, as shown
here:

CREATE PROCEDURE spOrdersInsert
(@OrderID int OUTPUT, @CustomerID nchar(5),
@EmployeeID int, @OrderDate datetime)
AS
INSERT INTO Orders (CustomerID, EmployeeID, OrderDate)
VALUES (@CustomerID, @EmployeeID, @OrderDate)
SELECT @OrderID = @@IDENTITY
Finally, we can use the DataAdapter object’s RowUpdated event to execute a
query to fetch the new autoincrement value, as shown in the following code
samples:

Visual Basic .NET
Dim da As OleDbDataAdapter = CreateMyDataAdapter()
Dim cn As OleDbConnection = da.SelectCommand.Connection
Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", cn)
AddHandler da.RowUpdated, AddressOf HandleRowUpdated
Dim tbl As DataTable = CreateMyDataTable()
da.Fill(tbl)

da.Update(tbl)

Private Sub HandleRowUpdated(ByVal sender As Object, _
ByVal e As OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso _
e.StatementType = StatementType.Insert Then
e.Row("OrderID") = CType(cmdGetIdentity.ExecuteScalar, Integer)
e.Row.AcceptChanges()
End If
End Sub
Visual C# .NET
OleDbDataAdapter da = CreateMyDataAdapter();
OleDbConnection cn = da.SelectCommand.Connection;
OleDbCommand cmdGetIdentity = new OleDbCommand("SELECT @@IDENTITY", cn);
da.RowUpdated += new OleDbRowUpdatedEventHandler(HandleRowUpdated);
DataTable tbl = CreateMyDataTable();
da.Fill(tbl);

da.Update(tbl);

private void HandleRowUpdated(object sender, OleDbRowUpdatedEventArgs e)
{
if ((e.Status == UpdateStatus.Continue) &&
((e.StatementType == StatementType.Insert))
{
e.Row["OrderID"] = (int) cmdGetIdentity.ExecuteScalar();
e.Row.AcceptChanges();
}
}
This code snippet differs slightly from the code that fetched new
time­stamp values in the RowUpdated event in two ways. First, and most
obviously, the query we’re executing to fetch data is different.

The second difference is in performance. What’s the fastest way to fetch
your new autoincrement values? The performance numbers I generated in
simple tests mirrored those from the timestamp tests. Stored procedure
output parameters provided the best performance, with batched queries
second and using the RowUpdated event a distant third.

@@IDENTITY vs. SCOPE_IDENTITY()
The SELECT @@IDENTITY query returns the last identity value generated on
your connection. This means that work done by other users on other
connections will not affect the results of your query. However, that does
not mean you’ll receive the value you expected.

Database administrators often use their own audit tables to track changes
made to the database. To track those changes, they generally rely on
triggers or stored procedures. Figure 11-2 shows an example.

Why have I drifted into a discussion of audit logs and triggers in the
middle of a discussion of retrieving autoincrement values? Let’s assume
that the audit table that the trigger shown in Figure 11-2 references has
an autoincrement column. If you insert a new row into the Orders table and
then issue the SELECT @@IDENTITY query, you’ll receive the autoincrement
value that the trigger generated for the new row in the audit table.

Remember that SELECT @@IDENTITY returns the last autoincrement value
generated for your connection.

To address this type of scenario, SQL Server 2000 introduced a new way to
retrieve autoincrement values: SCOPE_IDENTITY(). If you issue a SELECT
SCOPE_IDENTITY() query in this situation, you’ll receive the autoincrement
value generated for the new row in the Orders table.

If you’re working with SQL Server 2000 or later or Microsoft Desktop Engine
(MSDE) 2000 or later, you should consider using SCOPE_IDENTITY instead of
@@IDENTITY. There’s one minor exception to this rule. If you insert the new
row using a stored procedure but you want to retrieve that value after
calling the stored procedure, SCOPE_IDENTITY() will return Null. As I said,
this is a minor exception. If you’re going to insert new rows using stored
procedures and you want to retrieve the newly generated autoincrement
value, you should return this information using an output parameter.

For more information on the differences between @@IDENTITY and
SCOPE_IDENTITY(), see SQL Server Books Online.

==========================

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| From: "PeterB" <pe...@data.se>
| References: <##g$xEYjDH...@TK2MSFTNGP11.phx.gbl>
<#VpAM$bjDHA...@TK2MSFTNGP09.phx.gbl>
<OZiP7$ijDHA...@tk2msftngp13.phx.gbl>
| Subject: Re: SQLCE DataAdapter Update with Identity field
| Date: Thu, 9 Oct 2003 09:40:09 +0200
| Lines: 395
| MIME-Version: 1.0
| Content-Type: multipart/alternative;
| boundary="----=_NextPart_000_003E_01C38E49.540C60C0"
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <e4A7bijj...@TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: h113n2fls34o264.telia.com 217.208.187.113
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:63278
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet

[PeterB]

Thanks for that great article! I see that SELECT @@IDENTITY outperforms
using the rowupdated event which I am using. This is good to know.

However, I still don't get it to work. I just read that SqlCe doesn't
support batch queries :-(

So to conclude this:
If you are using Sql Server CE (SSCE) you need to use RowUpdated-event to
update your identity fields when creating new rows.
If you have filled the schema from your database, the column will most
likely be readonly and then you need to set the column to writable.

Thanks for all the help anyways!

/ Peter

"Kevin Sun [MS]" <zhi...@onlinemicrosoft.com> wrote in message
news:R32$CsjjDH...@cpmsftngxa06.phx.gbl...

> Hi Peter,
>
> The following information is from an article. Please let me know if it
> helps you on this issue:
>
> ====================================================
>
> Retrieving Newly Generated Autoincrement Values
> ====================================================
>
> SQL Server, Access, Sybase, and other databases use autoincrement columns
> (also referred to as identity columns). You can insert a new row into a
> table, and the database will generate a new value for the autoincrement

> column for that row. Many tables in the Northwind database-such as
> Employees, Orders, and Products-use autoincrement columns for their

> We can modify the CommandText of the DataAdapter object's Insert耶ommand

> time貞tamp values in the RowUpdated event in two ways. First, and most