Comparing two collection lists

I've been at this for few hours now and can't seem to find a solution. I have 2 inventory lists, one a spreadsheet and the other a data table. I need to match the spreadsheet against the data table to find out if I have missing inventory. The spreadsheet should match with what I have in the db, ie the spreadsheet is like a master so when I have missing inventory in DB I need to add it an list and build a report.

I thought by looping throught the spreadsheet and for each inventory in the spreadsheet loop through the data table I can achieve my goal but that proved to be wrong. Any ideas how I would do this?

Thanks, Eric

Here is the method:

public void Reconcile()
{
    ObjectDataSource ods = new ObjectDataSource();
    ods.ID = "ods";
    ods.TypeName = "";
    ods.SelectMethod = "GetAssets";
    ods.TypeName = "dsAssetsTableAdapters.AssetsTableAdapter"; 
    ods.SelectParameters.Clear();

    ReportDataSource rds = new ReportDataSource("dsAssets_Assets", ods开发者_StackOverflow);

    reportViewer1.LocalReport.DataSources.Clear();
    reportViewer1.LocalReport.DataSources.Add(rds);

    string _list = "";
    string _list_missing_SN = "";

        string filename = Server.MapPath("XLS/reconcile.xls");
        string sheetname = GetExcelSheetNames(filename)[0].ToString();

        String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + filename + ";" +
            "Extended Properties=Excel 8.0;";
        OleDbConnection objConn = new OleDbConnection(sConnectionString);
        objConn.Open();
        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + sheetname + "]", objConn);
        OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
        objAdapter1.SelectCommand = objCmdSelect;
        DataSet objDataset1 = new DataSet();
        objAdapter1.Fill(objDataset1, "XLData");

        string m_AssetManagement = System.Configuration.ConfigurationManager.ConnectionStrings["Asset_Management"].ToString();

        List<string> SN_list = new List<string>();

        SqlDataReader Assets_rd;
        SqlCommand cmdMyAssets = new SqlCommand();
        cmdMyAssets.Connection = new SqlConnection(m_AssetManagement);
        cmdMyAssets.CommandType = CommandType.StoredProcedure;
        cmdMyAssets.CommandText = "sp_Assets_Hardware_Select_by_Serial_Number";

        try
        {
            cmdMyAssets.Connection.Open();
            Assets_rd = cmdMyAssets.ExecuteReader();
            string strString;
            while (Assets_rd.Read())
            {
                strString = Assets_rd.GetSqlString(0).ToString().Trim() + "^" + Assets_rd.GetInt32(1).ToString().Trim() + "^" + Assets_rd.GetInt32(2).ToString().Trim();                    
                SN_list.Add(strString);
            }
        }
        catch (SqlException dbError)
        {
            Trace.Write("Database unavailable with Message: ", dbError.Message);
            Trace.Write("Stack Trace: ", dbError.StackTrace);
            throw;
        }

        bool record_match = false;
        foreach (DataRow drXCL in objDataset1.Tables[0].Rows)
        {
            if (drXCL.ItemArray[1].ToString() != string.Empty)
            {                    
                try
                {
                    string[] assetInfo = null;
                    assetInfo = SN_list[0].Split('^');
                    if (assetInfo[0].Contains(drXCL.ItemArray[1].ToString()))
                    {
                        _list += "|" + drXCL.ItemArray[1].ToString();
                    }
                    else
                    {
                        _list_missing_SN += drXCL.ItemArray[1].ToString().Trim() + "<br>";
                    }                     
                }
                catch (Exception SqlEx)
                {
                    // Throw Sqw Exception
                    clAppExceptions.buildEmailNotification(SqlEx.Message.ToString());
                }
            }
            else
            {
                //_list += "|*** NO SERIAL NUMBER ***";                    
            }           
        }
    if (_list_missing_SN != "")
    {
        Page.ClientScript.RegisterClientScriptBlock(this.Page.GetType(), "myAlert", "<script language='javascript'>alert('Following Serial Numbers were not on the spreasheet: " + _list_missing_SN  + "');</script>");
    }
    _list += "|";

    ods.SelectMethod = "GetAssetsBySerialNumbers";
    ods.SelectParameters.Add("list", _list);

    reportViewer1.LocalReport.ReportPath = Server.MapPath("~/Reports/Asset_List.rdlc");

    ReportParameter rpCategory = new ReportParameter("ReportParameter", "These assets are gone.");
    ReportParameter[] _rpCategory = { rpCategory };
    reportViewer1.LocalReport.SetParameters(_rpCategory);

    reportViewer1.LocalReport.Refresh();
}

---

I would load the master list into an array and create a second array of bools that correspond to the positions of the first array. Then looping through the datatable, when you find the element, flip the bool to true. If you cannot find it, store that element in a not found array. Once the datatable loop is finished, you can produce 2 lists. The first list is the items in the datatable but not in the master list... the not found array. The second list is created by looping through the bool array, any value of false means that the master list element was not found in the datatable.

This can then be expanded to include counts or other pieces of information that should match but do not.

---

I'd suggest a different approach. You could copy the data from the database and put it in the spreadsheet on a different worksheet and use the match function. You could also take the data from the spreadsheet and put it in a new table. Then use a query to find discrepencies. I don't think a programming solution is required unless this isn't a one time thing. If this is required for an application of some sort, ignore my answer:)

---

Don't know if this is of any use but if you have the two lists in IEnumerable sequeneces you could do something simple with LINQ.

I have an extension method I wrote for IEnumerable that I use for this purpose:

    public static IEnumerable<T> NotIn<T>(this IEnumerable<T> inputSequence, IEnumerable<T> secondSequence)
    {
        return secondSequence == null ? new List<T>(inputSequence) : inputSequence.Where(element => !secondSequence.Contains(element));
    }

If I recall correctly I ended up finding a native LINQ function that accomplished the same thing but I, of course, forgot what it was

---

If your just looking for a quick solution, I would just do everything in Excel. It's easy to link Excel to a DB and to link lists.

1. Link your DB to your Excel file (this way it's always linked to the DB)

2. Insert a formula to check if the (part, key, etc...) in your master list exists in your list from the DB.

   Use this link to see how to link lists in Excel.

---

Ultimately you have many options. To make a sound decision you need to answer a few questions.

1. How often will this task need to be performed?
2. What level of resources do you have available to utilize?
3. How quickly does this task need to run?
4. How much data needs to be compared?

Once you have answered these questions, we can suggest a solid solution to you more accurately.

---

Keep it simple... ADO.Net will probably the simplest approach for this problem. If you fill a DataTable with the values from the spreadsheet (hopefully using OleDb) you will be able to also pull information from the Database (using either OleDb or the correct ADO.Net client.) You can then update the values back into the database for fields such as location or last seen time. These Fill and Update commands can be queries or stored procs.

If you provide more detail such as table schema I could expand my answer further.

Edit...

If you already have one of the sources in a DataTable in .Net you could put both of them in the same DataSet and write a DataView query that would do an outer join. The Outer Join would allow you to see the matched and unmatched values.

Updated...

Sorry it took so long to get back to this. (Started a new job so I have been rather busy.) I am using two spreedsheets, but there is not reason that you couldn't use thie same concept between different databases and even different ADO.Net providers. The basic idea behind this example is to create a LastSeen timestamp in your database. Then instead of looking for what isn't there, you post the latest inventroy back to the database and then query for what hasn't been updated.

var inventoryFile = "Inventory.xlsx"; //ID,Item
var databaseFile = "Database.xlsx"; //ID,Item,Type,SN,LastSeen

var connectionFormatter = "Provider=Microsoft.ACE.OLEDB.12.0;" +
    "Data Source=\"{0}\";Mode=ReadWrite;" +
    "Extended Properties=\"Excel 12.0 Xml;HDR=Yes;\";";

var inventoryConnectionString = string.Format(connectionFormatter,
                                              inventoryFile);
var databaseConnectionString = string.Format(connectionFormatter,
                                             databaseFile);

using (var inventoryConnection =
                 new OleDbConnection(inventoryConnectionString))
using (var databaseConnection =
                 new OleDbConnection(databaseConnectionString))
{
    if (inventoryConnection.State != ConnectionState.Open)
        inventoryConnection.Open();

    if (databaseConnection.State != ConnectionState.Open)
        databaseConnection.Open();

    var lastSeenCmdString = "SELECT MAX(LastSeen) FROM [Sheet1$]";
    var lastSeenCommand = new OleDbCommand(lastSeenCmdString,
                                           databaseConnection);
    var lastSeen = lastSeenCommand.ExecuteScalar();

    var inventorySelectCmdString = "SELECT ID, Item FROM [Sheet1$]";
    var inventoryCmd = new OleDbCommand(inventorySelectCmdString,
                                        inventoryConnection);
    var table = new DataTable();
    var idCol = table.Columns.Add("ID", typeof(int));
    var itemCol = table.Columns.Add("Item", typeof(int));

    var inventoryDataAdapter = new OleDbDataAdapter(inventoryCmd);
    var databaseDataAdapter = new OleDbDataAdapter();

    var updateLastSeenCmdString =
                    "UPDATE [Sheet1$] SET LastSeen=NOW() WHERE Item=?";
    var updateCmd = new OleDbCommand(updateLastSeenCmdString,
                                     databaseConnection);
    var parameter = updateCmd.Parameters.Add("Item",
                                             OleDbType.Integer,
                                             0,
                                             "Item");

    databaseDataAdapter.UpdateCommand = updateCmd;

    inventoryDataAdapter.Fill(table);

    table.AcceptChanges();
    foreach (var row in table.Rows.OfType<DataRow>())
        row.SetModified();

    databaseDataAdapter.Update(table);

    var notSeenCmdString = "SELECT ID,Item,Type,SN,LastSeen " +
                           "FROM [Sheet1$]" +
                           "WHERE LastSeen <= ?";
    var notSeenCmd = new OleDbCommand(notSeenCmdString,
                                      databaseConnection);
    notSeenCmd.Parameters.Add("LastSeen", OleDbType.Date).Value = lastSeen; 

    databaseDataAdapter.SelectCommand = notSeenCmd;

    var missingInventory = new DataTable();
    databaseDataAdapter.Fill(missingInventory);

    foreach (var row in missingInventory.Rows.OfType<DataRow>())
        Console.WriteLine("ID: {0} Item:{1} Type:{2} SN:{3} LastSeen:{4}", 
                          row.ItemArray);
}