My problem: I have .NET 2.0 application that is installed in many places. The
application uses SQL Server and of course Microsoft SQL Provider. I want to
support Firebird Database in my application. But, because you choose not to use
provided DbDataAdapter.Update implementation for FbDataAdapter, FbDataAdapter
does not behave the same way Microsoft does.
I understand the case when it is not possible to provide the same
functionality, but this is not the case.
What I do not understand is why you don't provide the same functionality if you
can, why don't you use Microsoft default implementation (I ask this but I get
an answer that did not convince me at all).
And in fact that is not my problem. This problem is everywhere. It's where you
have identity columns, where you have generators. It's described and supported
by Microsoft. FbProvider is a piece of code that is to be integrated in their
ADO.NET environment, isn't it ?
I have send Carlos two similar VS 2005 projects that contains the TestCase that
demonstrates the differences between SQLProvider and FBProvider for this
scenario.
I have send you a link that describe the problem and the scenario
http://msdn2.microsoft.com/en-us/library/ks9f57t0(VS.80).aspx
http://72.14.221.104/search?q=cache:Op4FFrqXbIkJ:msdn2.microsoft.com/en-us/ks9f57t0(VS.80).aspx+ADO.NET+performs+a+series+of+actions+to+set+the+current+values+of+the+DataRow&hl=en&ct=clnk&cd=1
and where Microsoft says: "First, the AcceptChanges
method of the DataRow is called to preserve the current values as original
values, and then the new values are assigned."
This is the code that is missing in present implementation of
FbDataAdapter.Update method:
if (statementType == StatementType.Insert)
{
row.AcceptChanges();
}
This code, as the Microsoft says, fills DataRowVersion.Original values that is
needed when performing a Merge operation between the DataSet as it was before
Updating and the DataSet that contains updated changes in the original one.
Because the link seems to be invalid i have retrieved it from google cache and
copy the whole problem here:
Merging New Identity Values
A common scenario is to call the GetChanges method of a DataTable to create a
copy that contains only changed rows, and to use the new copy when calling the
Update method of a DataAdapter. This is especially useful when you need to
marshal the changed rows to a separate component that performs the update.
Following the update, the copy can contain new identity values that must then
be merged back into the original DataTable. The new identity values are likely
to be different from the original values in the DataTable. To accomplish the
merge, the original values of the AutoIncrement columns in the copy must be
preserved, in order to be able to locate and update existing rows in the
original DataTable, rather than appending new rows containing the new identity
values. However, by default those original values are lost after a call to the
Update method of a DataAdapter, because AcceptChanges is implicitly called for
each updated DataRow.
There are two ways to preserve the original values of a DataColumn in a DataRow
during a DataAdapter update:
The first method of preserving the original values is to set the
AcceptChangesDuringUpdate property of the DataAdapter to false. This affects
every DataRow in the DataTable being updated. For more information and a code
example, see AcceptChangesDuringUpdate.
The second method is to write code in the RowUpdated event handler of the
DataAdapter to set the Status to SkipCurrentRow. The DataRow is updated but the
original value of each DataColumn is preserved. This method enables you to
preserve the original values for some rows and not for others. For example,
your code can preserve the original values for added rows and not for edited or
deleted rows by first checking the StatementType and then setting Status to
SkipCurrentRow only for rows with a StatementType of Insert.
When either of these methods is used to preserve original values in a DataRow
during a DataAdapter update, ADO.NET performs a series of actions to set the
current values of the DataRow to new values returned by output parameters or by
the first returned row of a result set, while still preserving the original
value in each DataColumn. First, the AcceptChanges method of the DataRow is
called to preserve the current values as original values, and then the new
values are assigned. Following these actions, DataRows that had their RowState
property set to Added will have their RowState property set to Modified, which
may be unexpected.
How the command results are applied to each DataRow being updated is determined
by the UpdatedRowSource property of each DbCommand. This property is set to a
value from the System.Data.UpdateRowSource enumeration.
The following table describes how the UpdateRowSource enumeration values affect
the RowState property of updated rows.
Member name Description
Both AcceptChanges is called and both output parameter values and/or the values
in the first row of any returned result set are placed in the DataRow being
updated. If there are no values to apply, the RowState will be Unchanged.
FirstReturnedRecord If a row was returned, AcceptChanges is called and the row
is mapped to the changed row in the DataTable, setting the RowState to
Modified. If no row is returned, then AcceptChanges is not called and the
RowState remains Added.
None Any returned parameters or rows are ignored. There is no call to
AcceptChanges and the RowState remains Added.
OutputParameters AcceptChanges is called and any output parameters are mapped
to the changed row in the DataTable, setting the RowState to Modified. If there
are no output parameters, the RowState will be Unchanged.
Example
This example demonstrates extracting changed rows from a DataTable and using a
SqlDataAdapter to update the data source and retrieve a new identity column
value. The InsertCommand executes two Transact-SQL statements; the first one is
the INSERT statement, and the second one is a SELECT statement that uses the
SCOPE_IDENTITY function to retrieve the identity value.
INSERT INTO dbo.Shippers (CompanyName)
VALUES (@CompanyName);
SELECT ShipperID, CompanyName FROM dbo.Shippers
WHERE ShipperID = SCOPE_IDENTITY();
The UpdatedRowSource property of the insert command is set to
UpdateRowSource.FirstReturnedRow and the MissingSchemaAction property of the
DataAdapter is set to MissingSchemaAction.AddWithKey. The DataTable is filled
and the code adds a new row to the DataTable. The changed rows are then
extracted into a new DataTable, which is passed to the DataAdapter, which then
updates the server.
Visual Basic
Private Sub MergeIdentityColumns(ByVal connectionString As String)
Using connection As SqlConnection = New SqlConnection( _
connectionString)
' Create the DataAdapter
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT ShipperID, CompanyName FROM dbo.Shippers", connection)
' Add the InsertCommand to retrieve new identity value.
adapter.InsertCommand = New SqlCommand( _
"INSERT INTO dbo.Shippers (CompanyName) " & _
"VALUES (@CompanyName); " & _
"SELECT ShipperID, CompanyName FROM dbo.Shippers " & _
"WHERE ShipperID = SCOPE_IDENTITY();", _
connection)
' Add the parameter for the inserted value.
adapter.InsertCommand.Parameters.Add( _
New SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, _
"CompanyName"))
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both
' MissingSchemaAction adds any missing schema to
' the DataTable, including identity columns
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
' Fill the DataTable.
Dim shipper As New DataTable
adapter.Fill(shipper)
' Add a new shipper.
Dim newRow As DataRow = shipper.NewRow()
newRow("CompanyName") = "New Shipper"
shipper.Rows.Add(newRow)
' Add changed rows to a new DataTable. This
' DataTable will be used by the DataAdapter.
Dim dataChanges As DataTable = shipper.GetChanges()
' Add the event handler.
AddHandler adapter.RowUpdated, New _
SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)
' Update the datasource with the modified records.
adapter.Update(dataChanges)
' Merge the two DataTables.
shipper.Merge(dataChanges)
' Commit the changes.
shipper.AcceptChanges()
Console.WriteLine("Rows after merge.")
Dim row As DataRow
For Each row In shipper.Rows
Console.WriteLine("{0}: {1}", row(0), row(1))
Next
End Using
End Sub
C#
private static void MergeIdentityColumns(string connectionString)
{
using (SqlConnection connection =
new SqlConnection(connectionString))
{
// Create the DataAdapter
SqlDataAdapter adapter =
new SqlDataAdapter(
"SELECT ShipperID, CompanyName FROM dbo.Shippers",
connection);
//Add the InsertCommand to retrieve new identity value.
adapter.InsertCommand = new SqlCommand(
"INSERT INTO dbo.Shippers (CompanyName) " +
"VALUES (@CompanyName); " +
"SELECT ShipperID, CompanyName FROM dbo.Shippers " +
"WHERE ShipperID = SCOPE_IDENTITY();", connection);
// Add the parameter for the inserted value.
adapter.InsertCommand.Parameters.Add(
new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40,
"CompanyName"));
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
// MissingSchemaAction adds any missing schema to
// the DataTable, including identity columns
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// Fill the DataTable.
DataTable shipper = new DataTable();
adapter.Fill(shipper);
// Add a new shipper.
DataRow newRow = shipper.NewRow();
newRow["CompanyName"] = "New Shipper";
shipper.Rows.Add(newRow);
// Add changed rows to a new DataTable. This
// DataTable will be used by the DataAdapter.
DataTable dataChanges = shipper.GetChanges();
// Add the event handler.
adapter.RowUpdated +=
new SqlRowUpdatedEventHandler(OnRowUpdated);
adapter.Update(dataChanges);
connection.Close();
// Merge the updates.
shipper.Merge(dataChanges);
// Commit the changes.
shipper.AcceptChanges();
Console.WriteLine("Rows after merge.");
foreach (DataRow row in shipper.Rows)
{
{
Console.WriteLine("{0}: {1}", row[0], row[1]);
}
}
}
}
The OnRowUpdated event handler checks the StatementType of the
SqlRowUpdatedEventArgs to determine if the row is an insert. If it is, then the
Status property is set to SkipCurrentRow. The row is updated, but the original
values in the row are preserved. In the main body of the procedure, the Merge
method is called to merge the new identity value into the original DataTable,
and finally AcceptChanges is called.
Visual Basic
Private Sub OnRowUpdated( _
ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)
' If this is an insert, then skip this row.
If e.StatementType = StatementType.Insert Then
e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub
C#
protected static void OnRowUpdated(
object sender, SqlRowUpdatedEventArgs e)
{
// If this is an insert, then skip this row.
if (e.StatementType == StatementType.Insert)
{
e.Status = UpdateStatus.SkipCurrentRow;
}
}
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Firebird-net-provider mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
