Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
|
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 10th, 2007, 11:15 AM
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to ajaidass
Default Relation update

How to update,delete,insert both parent and child datarow using dataset,dataadapter,datatable and datarow
 
Old January 11th, 2007, 01:25 PM
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to ajaidass
Default

public DataSet GetData()
{
            SqlConnection conn = new SqlConnection ("server=vcdb02;uid=sa;pwd=ricka;database=northwin d");
            //Pull back the recent orders for the parent rows.
            SqlDataAdapter daOrder = new SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'",conn);
            //Get only the appropriate child rows for the parent rows.
            SqlDataAdapter daDetails = new SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')",conn);

            DataSet ds = new DataSet();
            try
            {

                //Fill DataSet, and then set DataRelation to move through the DataGrid.
                conn.Open();

                daOrder.FillSchema(ds,SchemaType.Mapped,"Orders");
                daOrder.Fill(ds,"Orders");

                daDetails.FillSchema(ds,SchemaType.Mapped,"Details ");
                daDetails.Fill(ds,"Details");

                ds.Relations.Add("OrdDetail", ds.Tables["Orders"].Columns["OrderID"], ds.Tables["Details"].Columns["OrderID"]);

                DataColumn dc = ds.Tables["Orders"].Columns["OrderID"];
                dc.AutoIncrement = true;
                dc.AutoIncrementSeed = -1;
                dc.AutoIncrementStep = -1;
            }
            catch(SqlException ex)
            {
                Console.Write (ex.Message.ToString ());
                Console.Write(ex.InnerException.ToString ());

            }

            return ds;
        }

        [WebMethod]
        public DataSet UpdateData(DataSet ds)
        {
            SqlConnection conn = new SqlConnection ("server=vcdb02;uid=sa;pwd=ricka;database=northwin d");
            //Pull back the recent orders for the parent rows.
            SqlDataAdapter daOrders = new SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'",conn);
            //Get only the appropriate child rows for the parent rows.
            SqlDataAdapter daDetails = new SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')",conn);


            try
            {
                conn.Open();
                // Get commands for the Orders table.
                // Reselect record after insert to get new Identity value.
                // You must get the schema, which you did in GetData(), before you get commands;
                // otherwise, the Command builder tries to insert new rows, based
                // on the Identity column.
                SqlCommandBuilder cb = new SqlCommandBuilder(daOrders);
                daOrders.DeleteCommand = cb.GetDeleteCommand();
                daOrders.UpdateCommand = cb.GetUpdateCommand();
                daOrders.InsertCommand = cb.GetInsertCommand();
                daOrders.InsertCommand.CommandText = String.Concat(daOrders.InsertCommand.CommandText, "; Select * From Orders Where OrderID = @@IDENTITY");

                //UpdateRowSource tells the DataAdapter that there will be a re-selected record.

                daOrders.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
                //cb = null;


                // Get commands for the Order Details table.
                // Must set the QuotePrefix and QuoteSuffix;
                // otherwise, the CommandBuilder does not put brackets ([])
                // around the table name.

                SqlCommandBuilder cb1 = new SqlCommandBuilder(daDetails);
                cb1.QuotePrefix = "[";
                cb1.QuoteSuffix = "]";
                daDetails.DeleteCommand = cb1.GetDeleteCommand();
                daDetails.InsertCommand = cb1.GetInsertCommand();
                daDetails.UpdateCommand = cb1.GetUpdateCommand();


                // Create a new DataAdapter based on the original one to prevent the
                // CommandBuilder from modifying the SQL statements,
                // specifically the custom InsertCommand.
                // You do not need this if you roll your own commands and parameters
                // or if you use the Visual Tools to do it.

                SqlDataAdapter daOrd2 = new SqlDataAdapter();
                daOrd2.DeleteCommand = daOrders.DeleteCommand;
                daOrd2.InsertCommand = daOrders.InsertCommand;
                daOrd2.UpdateCommand = daOrders.UpdateCommand;

                // Use a delegate to prevent AcceptChanges from occurring on Deletes and Inserts.
                // This is for a limitation of the DataAdapter; see Q313540.

                daOrd2.RowUpdated += new SqlRowUpdatedEventHandler(OnOrd1RowUpdated);
                daDetails.RowUpdated += new SqlRowUpdatedEventHandler(OnDetailsRowUpdated);


                daDetails.Update(GetDeletedRows(ds.Tables["Details"]));
                daOrd2.Update(GetDeletedRows(ds.Tables["Orders"]));
                DataRow [] dsArray = ds.Tables["Orders"].Select("", "", DataViewRowState.ModifiedCurrent);
                daOrd2.Update(ds.Tables["Orders"].Select("", "", DataViewRowState.ModifiedCurrent));
                daDetails.Update(ds.Tables["Details"].Select("", "", DataViewRowState.ModifiedCurrent));

                daOrd2.Update(ds.Tables["Orders"].Select("", "", DataViewRowState.Added));

                ds.EnforceConstraints = false;
                daDetails.Update(ds.Tables["Details"].Select("","", DataViewRowState.Added));
                ds.EnforceConstraints = true;

                conn.Close();



            }
            catch(SqlException ex)
            {
                Console.Write (ex.Message.ToString ());
                Console.Write(ex.InnerException.ToString ());
            }
            return ds;
        }


        protected static void OnOrd1RowUpdated(object sender, SqlRowUpdatedEventArgs args)
        {
            if(args.StatementType == StatementType.Insert || args.StatementType == StatementType.Delete )
                args.Status = UpdateStatus.SkipCurrentRow;

        }
        protected static void OnDetailsRowUpdated(object sender, SqlRowUpdatedEventArgs args)
        {
            if(args.StatementType == StatementType.Insert )
            {
                // Do not allow the AcceptChanges to occur on this row.
                args.Status = UpdateStatus.SkipCurrentRow;

                // Get the current, actual primary key value so that you can plug it back
                // in after you get the correct original value that was generated for the child row.
                int currentkey = (int)args.Row["OrderID"];
                // This is where you get a correct original value key that is stored to the child row.
                // You pull the original, pseudo key value from the parent, plug it in as the child row's primary key
                // field, and then accept changes on it. Specifically, this is why you turned off EnforceConstraints.
                args.Row["OrderID"] = args.Row.GetParentRow("OrdDetail")["OrderID",DataRowVersion.Original];
                args.Row.AcceptChanges();
                // Store the actual primary key value in the foreign key column of the child row.
                args.Row["OrderID"] = currentkey;
            }

            if(args.StatementType == StatementType.Delete )
                args.Status = UpdateStatus.SkipCurrentRow;

        }
        private DataRow [] GetDeletedRows(DataTable dt)
        {
            DataRow [] dr ;
            if(dt == null)
                return null;
            dr = dt.Select("","",DataViewRowState.Deleted );
            if(dr.Length ==0 || dr[0] != null)
                return dr;
            // Workaround:
            // With a remoted DataSet, Select returns the array elements
            // that are filled with Nothing/null instead of DataRow objects.

            for(int i=0; i < (int)dt.Rows.Count; i++)
            {
                if(dt.Rows[i].RowState ==DataRowState.Deleted )
                    dr[i]=dt.Rows[i];
            }
            return dr;
}





Similar Threads
Thread Thread Starter Forum Replies Last Post
constraint for 1-to-n relation j_yan MySQL 0 December 10th, 2004 11:03 PM
find relation in dataset with c# kobystud C# 2 April 21st, 2004 01:04 AM
SQL Relation Lucian Ion SQL Server 2000 3 October 25th, 2003 03:31 PM
how can i best implement a many to many relation? Haroldd Access 1 July 2nd, 2003 03:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.