Comparing two collection lists

Question

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);

    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();
}

Answer 1

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.