Comparing two collection lists

Question

I have been doing this for several hours and cannot find a solution. I have 2 inventory lists, one table and another data table. I need to map a table to a data table to find out if I have any inventory. The spreadsheet should match what I have in db, i.e. The table is like a master, so when I don’t have inventory in the database, I need to add it to the list and create a report.

I thought, by looping the table and for each inventory in the spreadsheet cycle through the data table, I can achieve my goal, but this turned out to be wrong. Any ideas how I will do this?

Thanks Eric

Here is a way:

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

bools, . , , , bool true. , . , 2 . - datatable, ... . bool, false , datatable.

, , , .

Answer 2

. . . , . , , . - , :)

Answer 3

, , IEnumerable sequeneces, - LINQ.

, IEnumerable, :

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

, , LINQ, , , , ,

Answer 4

, Excel. Excel .

• Excel ( , )

• , , (, ..) .

  , , Excel.

Answer 5

... ADO.Net, , . DataTable (, OleDb), ( OleDb ADO.Net). , . Fill Update .

, ​​ , .

...

DataTable .Net, DataSet DataView, . Outer Join .

...

, , . ( , .) spreedsheets, , ADO.Net. - LastSeen . , , , , .

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

Answer 6

Ultimately, you have many options. To make the right decision, you need to answer a few questions.

• How often should this task be performed?
• What level of resources can you use?
• How to quickly complete this task?
• How much data do you need to compare?

Once you have answered these questions, we can offer you a more accurate solution.