[Firebird-net-provider] Problem with FbDataAdapter

mn Thu, 02 Nov 2006 04:33:31 -0800

FbDataAdapter does not handle correctly Update method because of

protected override int Update(DataRow[] dataRows, DataTableMapping tableMapping) implementation

Here is the case:

we have a table named zones created as follows

CREATE TABLE zones (
zone_id INTEGER NOT NULL, /* pk */
name VARCHAR(80) NOT NULL
);

ALTER TABLE zones
ADD CONSTRAINT zones_pk PRIMARY KEY (zone_id);

CREATE GENERATOR gen_zone_id;
COMMIT;

and a stored procedure created as follows

SET TERM !! ;
CREATE PROCEDURE zone_ins(
p_zone_id INTEGER,
p_name   VARCHAR(80)
)
RETURNS (
p_new_zone_id INTEGER
)
AS
DECLARE VARIABLE v_zone_id INTEGER;
BEGIN
IF ((p_zone_id IS NULL) OR (p_zone_id <= 0)) THEN
  v_zone_id = GEN_ID(gen_zone_id, 1);
ELSE
  v_zone_id = p_zone_id;

INSERT INTO zones( zone_id, name )
VALUES( :v_zone_id, :p_name);

p_new_zone_id = v_zone_id;
SUSPEND;
END !!
SET TERM ; !

and following class methods

        public void RunTestMerge()
        {
   string connString = @"User=SYSDBA;Password=masterkey;Host=localhost;Database=/test.fdb";

            FbConnection con = new FbConnection(connString);
            FbDataAdapter da = new FbDataAdapter("SELECT * FROM zones", con);

            FbCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "zone_ins";

            FbParameter param = new FbParameter("@p_zone_id", DBNull.Value);
            param.Direction = ParameterDirection.Input;
            param.SourceColumn = "zone_id";
            cmd.Parameters.Add(param);

            param = new FbParameter("@p_name", "zone");
            param.Direction = ParameterDirection.Input;
            param.SourceColumn = "name";
            cmd.Parameters.Add(param);

            param = new FbParameter("@p_new_zone_id", 0);
            param.Direction = ParameterDirection.Output;
            param.SourceColumn = "zone_id";
            cmd.Parameters.Add(param);

da.InsertCommand = cmd;

DataSet ds = new DataSet();
da.Fill(ds, "zones");

            DataTable zt = ds.Tables[0];
            DataColumn pk = zt.Columns["zone_id"];
            pk.AutoIncrement = true;
            pk.AutoIncrementSeed = -1;
            pk.AutoIncrementStep = -1;
            zt.PrimaryKey = new DataColumn[] {pk};

            DataRow r = zt.NewRow();
            r["name"] = "new zone";
            zt.Rows.Add(r);

DataSet dsChanges = ds.GetChanges();

           // does not work for FbDataAdapter !!!!!!!!!!!!!!!!!
            // da.AcceptChangesDuringUpdate = false;

            da.RowUpdated += new FbRowUpdatedEventHandler(da_RowUpdated);
            da.Update(dsChanges, "zones");

ds.Merge(dsChanges);

ds.AcceptChanges();
}

        void da_RowUpdated(object sender, FbRowUpdatedEventArgs e)
        {
            if (e.StatementType == StatementType.Insert)
            {
                e.Status = UpdateStatus.SkipCurrentRow;
            }
        }

Merge does not work correctly because after da.Update(dsChanges, "zones");

the added row has RowState == RowState.Added and not RowState.Modified as it should be

so there are no DataRowVersion.Orginal values so merge cannot update row using primary key.

As a result we have two rows instead of one.

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642

_______________________________________________
Firebird-net-provider mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

[Firebird-net-provider] Problem with FbDataAdapter

Reply via email to