locked
Help about updating key values after insert through DAAB RRS feed

  • Question

  • User1771751609 posted

    Hello all,

    I need to know any suggestions for updating parent/child column values after insert through Microsoft Application Block.

    I am posting a scenario that suppose i have two tables PrtTable and ChildTable with PrtTable column named Id reffered in ChildTable as PrtTableId

    Now i m using following way to update tables in db

    Dim db As Database = DatabaseFactory.CreateDatabase

    db.UpdateDataSet(tempDs, "prtTable", cmdToInsert, Nothing, Nothing, UpdateBehavior.Standard,100)

    db.UpdateDataSet(tempDs,"ChildTable", cmdToInsert, Nothing, Nothing, UpdateBehavior.Standard, 100)

    But problem is i need to fetch updated Id column from prtTable after insert in database and update respective values in PrtTableId in ChildTable

    ----------------------------------------------------

    I know one method might be to execute db.executeNonQuery and get back an output parameter value as SCOPE_IDENTITY() from database. But how is it possible when executing ds.UpdateDataSet() in DAAB which uses dataadapter.update() method in background??

    Please inform about updating parent value from database in ds and further updating it in child through enforceconstraints or any other technique.

    Thanks.

     

     

     

     

    Monday, May 3, 2010 1:44 AM

All replies

  • User-37275327 posted

    try this code, But in C#.


            Database db = DatabaseFactory.CreateDatabase(Database_Connection_Name);
                    connection = db.CreateConnection();
                    connection.Open();
                    transaction = connection.BeginTransaction();

                    DbCommand command = db.GetStoredProcCommand(InsertPrtTable);

                    db.AddInParameter(command, "@vcUserFirstName", DbType.String, user.UserFirstName);            
                    db.AddOutParameter(command, "@nUserID", DbType.Int32, 4);


                    db.ExecuteNonQuery(command, transaction);
            int newuserid;
                    newuserid= Convert.ToInt32(db.GetParameterValue(command, "@nUserID").ToString());

                    if (newuserid > 0)
                    {
                        DbCommand dbInsertCommand = db.GetStoredProcCommand(Constants.SP_InsertChildTable);
                        
                        db.AddInParameter(dbInsertCommand, "@nUserID", DbType.Int32, newuserid);
                        db.AddInParameter(dbInsertCommand, "@nTest", DbType.Int32, "Test", DataRowVersion.Current);                  

                        db.UpdateDataSet(dsYourDataSet, dsYourDataSet.Tables[0].TableName, dbInsertCommand, null, null , transaction);
                    }

                    transaction.Commit();




    Friday, May 7, 2010 5:27 AM
  • User1771751609 posted

    Thx for ur reply rana,

    This is nice idea to use executeNonquery() for inserting parents and then update childs by updateDataset() method. I have to insert around 600k parent records with their childs at a time. If inserting them through UpdateDataset() inserts them in 15 mins than executenonquery() is taking approx 4 hrs! :)

    looking to shorten this time.

     

     

     

    Friday, May 7, 2010 5:57 AM
  • User-37275327 posted

     You better think of SQLbulkcopy technic. You can scale down the time then.

    try this

    http://www.codeproject.com/KB/database/SqlBulkCopy.aspx


    Sunday, May 9, 2010 3:38 AM