How to move records from one table to another in linq

Question

I have a ProductLevel table in an SQL database. It contains the products for a store. I want to copy these records into a ProductLevelDaily table at the time the user logs onto the hand held device in the morning.

As they scan items the bool goes from false to true so at anytime they can see what items are left to scan/check.

From the mobile device I pass the siteID and date to the server:

int userID = int.Parse(oWebRequest.requestData[5]); and a few other things

IEnumerable<dProductLevelDaily> plditems 
         = DSOLDAL.CheckProductDailyLevelbySiteCount(siteID, currentDate);

This checks if there are any records already moved into this table for this store. Being the first time this table should be empty or contain no records for this store on this date.

if (plditems.Count() == 0) // is 0
{
    IEnumerable<dProductLevel> ppitems = DSOLDAL.GetProductsbySite(siteID);

    // this gets the products for this store 
    if (ppitems.Count() > 0)
    {
        dProduct pi = new dProduct();

        foreach (dProductLevel pl in ppitems)
        {
            // get the product
            pi = DSOLDAL.getProductByID(pl.productID, companyID);

            dProductLevelDaily pld = new dProductLevelDaily();

            pld.guid = Guid.NewGuid();
            pld.siteID = siteID;
            pld.statusID = 1;
            pld.companyID = companyID;
            pld.counted = false;
            pld.createDate = DateTime.Now;
            pld.createUser = userID;
            pld.productID = pl.productID;
            pld.name = "1000"; //  pi.name;
            pld.description = "desc"; // pi.description;

            DSOLDAL.insertProductLevelDailyBySite(pld);
        }
    }
}

On the PDA the weberequest response returns NULL

I can't see what the problem is and why it wont work.

The insert is in DSOLDAL:

public static void insertProductLevelDailyBySite(dProductLevelDaily pld)
{
    dSOLDataContext dc = new dSOLDataContext();

    try
    {
        dc.dProductLevelDailies.InsertOnSubmit(pld);
        // dProductLevelDailies.Attach(pld, true);
        dc.SubmitChanges();
    }
    catch (Exception exc)
    {
        throw new Exception(getExceptionMessage(exc.Message));
    }
    finally
    {
        dc = null;
    }
}

This code works until I put the foreach loop inside with the insert

IEnumerable<dProductLevelDaily> plditems
           = DSOLDAL.CheckProductDailyLevelbySiteCount(siteID, s);

if (plditems.Count() == 0) // plditems.Count() < 0) 
{
    IEnumerable<dProductLevel> ppitems = DSOLDAL.GetProductsbySite(siteID);
    if (ppitems.Count() > 0)
    {

        oWebResponse.count = ppitems.Count().ToString();
        oWebResponse.status = "OK";
    }
    else
    {
        oWebResponse.count = ppitems.Count().ToString();
        oWebResponse.status = "OK";

    }
}
else
{
    oWebResponse.count = "2"; //  plditems.Count().ToString();
    oWebResponse.status = "OK";
}

Answer 1

These kind of bulk operations aren't very well matched to what Linq-to-SQL does.

In my opinion, I'd do this using a stored procedure, which you could include in your Linq-to-SQL DataContext and call from there.

That would also leave the data on the server and just copy it from one table to the other, instead of pulling down all data to your client and re-uploading it to the server.

Linq-to-SQL is a great tool - for manipulating single objects or small sets. It's less well suited for bulk operations.