[dba-VB] What to do, what to do?

Hi John

Shamil knows the SqlClient stuff! As he knows, I prefer the datatable route - a matter of taste I think, but I admit working with SqlCommand may allow for some fine tuning not possible with the datatableadapters.

Here's an example from an app that updates (syncs) one table from another. Not exactly what you wish to do but it should contain the building blocks.
The tricky part here is that the unique id (autonumber) should not be compared. However, the id of a source row is stored in the target row as SyncId for later reference:

        private void ConvertOrder()
        {
            // DataTableAdapter to copy from.
            VrsCloneTableAdapters.OrderTableAdapter sourceDataTableAdapter =
                new VrsCloneTableAdapters.OrderTableAdapter();
            VrsClone.OrderDataTable sourceDataTable;

            // DataTableAdapter to copy to.
            KarneliaTableAdapters.SyncOrderTableAdapter targetDataTableAdapter =
                new KarneliaTableAdapters.SyncOrderTableAdapter();
            Karnelia.SyncOrderDataTable targetDataTable;

            sourceDataTableAdapter.Connection = ConnectionApplyPassword(sourceDataTableAdapter.Connection.ConnectionString);
            targetDataTableAdapter.Connection = ConnectionApplyPassword(targetDataTableAdapter.Connection.ConnectionString);

            // DataTable for source.
            sourceDataTable = sourceDataTableAdapter.GetDataSync();
            // DataTable for target.
            targetDataTable = targetDataTableAdapter.GetData();

            // Read each row from the source rows.
            // Update those found in the target table.
            // Append those not found in the target table.

            // Variable to hold primary key to locate.
            int key;
            // Row of target DataTable to be updated.
            Karnelia.SyncOrderRow targetRow = null;
            // Variables to hold hash codes of row contents.
            int sourceHash;
            int targetHash;

            int colCount = sourceDataTable.Columns.Count;
            int colItem;
            int colOrdinal;
            string colName;
            for (colItem = 0; colItem < colCount; colItem++)
            {
                colName = sourceDataTable.Columns[colItem].ColumnName;
                colOrdinal = sourceDataTable.Columns[colItem].Ordinal;
                targetDataTable.Columns[colName].SetOrdinal(colOrdinal + 1);
            }

            foreach (VrsClone.OrderRow sourceRow in sourceDataTable)
            {
                // Value of key to locate.
                key = sourceRow.SyncId;
                // Find a matching row in the target.
                targetRow = targetDataTable.FindBySyncId(key);

                if (targetRow == null)
                {
                    // Row is not found. 
                    targetRow = targetDataTable.NewSyncOrderRow();
                    targetRow.BeginEdit();
                    targetRow.VrsOrderId = sourceRow.VrsOrderId;
                    targetRow.ClientId = sourceRow.ClientId;
                    targetRow.StylistId = sourceRow.StylistId;
                    targetRow.DateCreated = sourceRow.DateCreated;
                    targetRow.SyncId = sourceRow.SyncId;
                    targetRow.EndEdit();
                    targetDataTable.AddSyncOrderRow(targetRow);
                    Console.WriteLine("Insert " + targetDataTable.ToString() + ": " + sourceRow.VrsOrderId.ToString() + " - " + targetRow.RowState.ToString());
                }
                else
                {
                    // Row is found.
                    // Calculate hash code to see if source row and target row match.
                    // Id of targetrow (first column) is not present in sourcerow, thus 
                    // concatenate first column of targetrow with the columns of sourcerow.
                    sourceHash = String.Concat(targetRow.ItemArray[0], String.Concat(sourceRow.ItemArray)).GetHashCode();
                    targetHash = String.Concat(targetRow.ItemArray).GetHashCode();
                    if (sourceHash != targetHash)
                    {
                        // The source row is updated.
                        // Copy full content from source row to target row.
                        // This sets RowState of the target row to Modified.
                        targetRow.BeginEdit();
                        targetRow.VrsOrderId = sourceRow.VrsOrderId;
                        targetRow.ClientId = sourceRow.ClientId;
                        targetRow.StylistId = sourceRow.StylistId;
                        targetRow.DateCreated = sourceRow.DateCreated;
                        targetRow.EndEdit();

                        // Do NOT call AcceptChanges here as this would clear property RowState.
                        Console.WriteLine("Update " + targetDataTable.ToString() + ": " + sourceRow.VrsOrderId.ToString() + " - " + targetRow.RowState.ToString());
                    }
                }
            }
            // Write back to the database table the updated target DataTable.
            targetDataTableAdapter.Update(targetDataTable);
            // Call AcceptChanges if needed for further processing.
            //targetDataTable.AcceptChanges();
        }


This database contains records by the thousands. How code like this will perform for millions of records has to be tested.

/gustav


>>> jwcolby at colbyconsulting.com 12-11-2009 18:22 >>>
I am writing an application in C# to merge records (previously discussed).  It looks pretty simple, 
all things considered.  Get data into a dataset object which will contain about 4 tables.  Then use 
the records and fields collections of the recordset to iterate through the fields building up a 
record (or modifying the first record of a small set of records - the dupes).  Copy a record from 
one table into another table inside of the dataset.  Delete records in a table in the dataset.  Etc. 
Etc all without going back out to the actual data tables on SQL Server until the very end when I 
will do an update back to the live data.

Am I correct in assuming here that I can programmatically append records from one table in the 
dataset to another record in the dataset?  Delete records in the tables in the dataset?  Update 
records in tables in the dataset?  All "in memory" without connecting back to the live data in the 
database?

Is there anything I need to know before I launch into this?

-- 
John W. Colby
www.ColbyConsulting.com