SharePoint Development - Timer Job Creation

Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
32,829
Reaction score
3,033
Location
On the toilet
Hi Guys,

Is anyone profficient in SP? Need some assistance with copying and then updating items on site but differant lists. Workflows aren't possible because of one of the lists is a External List :\ .

Any help would be appreciated.
 
Code: 
    public override void Execute(Guid targetInstanceId) // you code to work with the lists
    {

        // get a reference to the current site collection's content database
        SPWebApplication webApplication = this.Parent as SPWebApplication;
        SPContentDatabase contentDb = webApplication.ContentDatabases[contentDbId];
        SPList TestCustomList = contentDb.Sites[0].RootWeb.Lists["Test_Database_Custom List"];
        SPList TestFilterList = contentDb.Sites[0].RootWeb.Lists["Eish"];
        //Adding New Items
        SPQuery query = new SPQuery();
        query.ViewXml = "<View Scope='RecursiveAll'></View>"; // View All ?
        query.Query = query.Query = string.Format(
                   "<OrderBy><FieldRef Name='SalesID' Ascending='False' /></OrderBy>"); //Selecting the List items and grouping them according to SalesID, in descending order
               SPListItemCollection TestFilterListitems = TestFilterList.GetItems(query); // The Query 
         //Comparison
				foreach (SPListItem Li in TestFilterListitems) // Goes through each list item and checks according to the below criteria
            {
                string Sales_ID = Li["SalesID"].ToString();
                SPListItemCollection CustomTestListItems = TestCustomList.GetItems(query);
                foreach (SPListItem CustomLi in CustomTestListItems)
                {
                    if (CustomLi["SalesID"].ToString == Sales_ID) ;

Okay guys. This is as far as I've gotten. Now any idea's on how to add a new item from the FilterList if the item doesn't exist in the CustomList building on what I already have here?
 
Code: 
        public override void Execute(Guid ContentDbId) // you code to work with the lists
        {
            SPWebApplication webApplication = this.Parent as SPWebApplication;
            SPContentDatabase contentDb = webApplication.ContentDatabases[ContentDbId];
            SPList MarvalCustomList = contentDb.Sites[0].RootWeb.Lists["Test_Database_Custom List"];
            SPList MarvalFilterList = contentDb.Sites[0].RootWeb.Lists["Eish"];
            foreach (SPListItem sourceitem in MarvalFilterList.Items)
            {
                SPListItem destItem = MarvalCustomList.Items.Add();

                foreach (SPField field in MarvalFilterList.Fields)
                {
                    if (!field.ReadOnlyField && field.InternalName != "Attachments")
                    {
                        destItem[field.InternalName] = sourceitem[field.InternalName];
                    }
                }

                foreach (string fileName in sourceitem.Attachments)
                {
                    SPFile file = sourceitem.ParentList.ParentWeb.GetFile(sourceitem.Attachments.UrlPrefix + fileName);
                    byte[] imageData = file.OpenBinary();
                    destItem.Attachments.Add(fileName, imageData);
                }
                destItem.Update();

This works for copying over the data from one list to another. Now just working on only adding the items which don't already exist on the destination list. Any help would be appreciated :)
 
ok, i have a couple of questions for you. How frequently are you updating the list? how are you updating the list, ie SSIS or ?
How many items are being updated, ie a couple or a couple of thousand?

basically you would have to retrieve a list of id's from both sides and do a comparison, but based on the answers above you can go about it in different ways.
 
ok, i have a couple of questions for you. How frequently are you updating the list? how are you updating the list, ie SSIS or ?
How many items are being updated, ie a couple or a couple of thousand?

basically you would have to retrieve a list of id's from both sides and do a comparison, but based on the answers above you can go about it in different ways.

Frequency- Every night (SP List) ; Table in Database - Every hour
How am I updating- Database- manually ; SPList - SP TimerJob
How many Items- Over 70k

Would have used SSIS but I cant run a scheduled task on the SQL server as the two servers (SP and SQL) are on two differant servers in two differant locations.

Also - It seems that you can't copy from the external list because the list doesnt have an Internal name like Custom SP lists. Anyone have a possible solution??
 
pity about the SSIS though, we use http://sqlsrvintegrationsrv.codeplex.com/ and works very nicely. However you could have an issue with authentication as it only accepts domain credentials (dont know if that has changed in the latest version though)

I am not sure what you mean by table? I thought you are copying from one sharepoint list to another sharepoint list. Where I had to write cross collections I've used the Client Model (Microsoft.Sharepoint.Client) to update a list, and it should work for your setup (cant see why not) unless you dont have the right permissions. Below is a snippet for adding the row.

Code: 
//get the user currently logged in 
SPUser _currentUser = SPContext.Current.Web.CurrentUser;

//get the user that has privileges to save to the list
NetworkCredential credentials = new NetworkCredential(_userName, _password, _domain);
//get the context
ClientContext context = new ClientContext(_siteName);
context.Credentials = credentials;
using (context)
{
    //get list info
    var list = context.Web.Lists.GetByTitle(_listNameAudit);
    //create new item object to save to
    ListItemCreationInformation info = new ListItemCreationInformation();
    ListItem item = list.AddItem(info);
    //specify properties for columns
    item["Title"] = WebpartName;
    item["Site_x0020_URL"] = HttpContext.Current.Request.Url.ToString();
    item["Further_x0020_Info"] = ExtraInfo;
    item["Employee_x0020_Number"] = _currentUser.LoginName.ToString();
    //save the item
    item.Update();
    context.ExecuteQuery();
}

Another thing I would like to point out is that sharepoint by default limits the amount of records it returns (think its 5000 not sure right now) so I suggest making use of views to give you the info you need.
 
Last edited:
Nefertiti said:
pity about the SSIS though, we use http://sqlsrvintegrationsrv.codeplex.com/ and works very nicely. However you could have an issue with authentication as it only accepts domain credentials (dont know if that has changed in the latest version though)

I am not sure what you mean by table? I thought you are copying from one sharepoint list to another sharepoint list. Where I had to write cross collections I've used the Client Model (Microsoft.Sharepoint.Client) to update a list, and it should work for your setup (cant see why not) unless you dont have the right permissions. Below is a snippet for adding the row.

Another thing I would like to point out is that sharepoint by default limits the amount of records it returns (think its 5000 not sure right now) so I suggest making use of views to give you the info you need.
Click to expand...

Haven't completely given up on SSIS so will give that a look a little later on tonight.
It is a SharePoint List (External list though) getting data off a SQL table using BCS. I will try the Client Model to try update the list. Good catch I'll be using a Group By Month view to sort the data. Will report back on my results.
 
Now I understand the table thing... we dont use use BCS, so wont be much help there. Please let me know the outcome, it would be interesting to know if the client model works against this type of list.
 
This is were I am so far, the Client Model seems to do a great job reading the external list but I'm having a bit of trouble copying those entries to the custom list. Thoughts guys?

Code: 
SP.ClientContext context = new SP.ClientContext("http://sp2010");
            SP.Web site = context.Web;
            var AuditFilterList = site.Lists.GetByTitle("Eish");
            var CustomMList = site.Lists.GetByTitle("ListTimerJob");

            SP.CamlQuery camlQuery = new SP.CamlQuery();
            //Queries the Filter List
            IQueryable<SP.ListItem> AuditListItems = AuditFilterList.GetItems(camlQuery);
            IEnumerable<SP.ListItem> externalList = context.LoadQuery(AuditListItems);
            context.ExecuteQuery();
            //Queries The Custom List
            IQueryable<SP.ListItem> CustomlListItems = AuditFilterList.GetItems(camlQuery);
            IEnumerable<SP.ListItem> CustomList = context.LoadQuery(CustomlListItems);
            //Executes against the Server
            context.ExecuteQuery();


            var ExternalAuditList = from Audit in externalList
                                  select new
                                  {
                                      Title = Audit.FieldValues.ElementAt(1).Value.ToString(),
                                      IncReport_Number = Audit.FieldValues.ElementAt(2).Value.ToString(),
                                      Name = Audit.FieldValues.ElementAt(3).Value.ToString(),
                                      Surname = Audit.FieldValues.ElementAt(4).Value.ToString(),
                                      Time_Spent = Audit.FieldValues.ElementAt(5).Value.ToString(),
                                      Date_Worked = Audit.FieldValues.ElementAt(6).Value.ToString(),
                                      Test_Field = Audit.FieldValues.ElementAt(7).Value.ToString()
                                  };
            foreach (var product in ExternalAuditList)
            {
                SP.ListItemCreationInformation destlist = new SP.ListItemCreationInformation();
                SP.ListItem newlistitem = CustomMList.AddItem(destlist);
 
Last edited:
List migration and update

Pho3nix
We created an application that runs as a batch job to insert/update SP list data from different SP servers as well as MS SQL Server data.

If you would be interested in learning more about the application, contact me at info at cartersolutions dot com.

Best regards,

Tony Carter
 
Is the code you posted complete? I think there is a section missing.

Using your code I would have done something like this in the last two lines you posted (im typing of the top of head, so apologies if its not compiling properly). Even though you are saying add item, it doesnt mean that it takes on the product that you specified. You still need to assign and then specify update.

foreach (var product in ExternalAuditList)
{
SP.ListItemCreationInformation destlist = new SP.ListItemCreationInformation();
SP.ListItem newlistitem = CustomMList.AddItem(destlist);
//specify properties for columns
newlistitem["Title"] = product.Title;
--mention other column names you need to update here
//save the item
newlistitem.Update();
}

ps. I assume that Time_Spent etc is the column names of the list your are copying too, and that they are spelled exactly the way you have them here, casing and all. Your column names didnt have spaces in, do they? And they are all string values? If Date_Worked is a proper date field in sharepoint, check that the date is in sharepoint format or in this format : yyyy-mm-dd hh:mm:ss. And if any of these fields are lookup tables, choice or multichoice there is a special way to update them.

Anyhow, let me know how it goes.
 
Code: 
        public override void Execute(Guid ContentDbId)
        {   
            //Setting Content Database
            //SPWebApplication webApp = this.Parent as SPWebApplication;
            //SPContentDatabase contentDb = webApp.ContentDatabases[ContentDbId];
            //SPList AuditCustomList = contentDb.Sites[0].RootWeb.Lists["ListTimerJob"];
            // Define the Client Context (as defined in your textbox)
            SP.ClientContext context = new SP.ClientContext("http://sp2010");
            SP.Web site = context.Web;
            var AuditFilterList = site.Lists.GetByTitle("Eish");
            var CustomMList = site.Lists.GetByTitle("ListTimerJob");

            SP.CamlQuery camlQuery = new SP.CamlQuery();
            //Queries the Filter List
            IQueryable<SP.ListItem> AuditListItems = AuditFilterList.GetItems(camlQuery);
            IEnumerable<SP.ListItem> externalList = context.LoadQuery(AuditListItems);
            //Executes against the Server
            context.ExecuteQuery();

            var ExternalAuditList = from Audit in externalList
                                  select new
                                  {
                                      Title = Audit.FieldValues.ElementAt(1).Value.ToString(),
                                      IncReport_Number = Audit.FieldValues.ElementAt(2).Value.ToString(),
                                      Name = Audit.FieldValues.ElementAt(3).Value.ToString(),
                                      Surname = Audit.FieldValues.ElementAt(4).Value.ToString(),
                                      Time_Spent = Audit.FieldValues.ElementAt(5).Value.ToString(),
                                      Date_Worked = Audit.FieldValues.ElementAt(6).Value.ToString(),
                                      Test_Field = Audit.FieldValues.ElementAt(7).Value.ToString()
                                  };
            foreach (var product in ExternalAuditList)
            {
                SP.ListItemCreationInformation destlist = new SP.ListItemCreationInformation();
                SP.ListItem newlistitem = CustomMList.AddItem(destlist);
             {
                //Specify properties for columns
                newlistitem["Title"] = product.Title;
                newlistitem["IncReport_Number"] = product.IncReport_Number;
                newlistitem["Name"] = product.Name;
                newlistitem["Surname"] = product.Surname;
                newlistitem["Time_Spent"] = product.Time_Spent;
                newlistitem["Date_Worked"] = product.Date_Worked;
                newlistitem["Test_Field"] = product.Test_Field;
                //save the item
            }
                newlistitem.Update();
            }
        }
    }
}

Complete code, compiles properly and even runs as a Timer Job successfully but it doesn't add any of the new List items. It will even show activity for the Timer Job was 16-50seconds but nothing shows up on the custom List..which leaves me stumped once again...

Nefertiti, thank you so much for helping me along :) I really do appreciate it :) Honestly, I do :)
 
no problem :)

i'd move the newlistitem.Update(); inside the bracket liste above it.

add a try catch and see if it generates any errors.
 
Hi guys, after mulling over my code I've decided to add exceptions to the code so I can track and possibly find out where the problem with the code is..Problem is I haven't ever done this. Tips and tricks would be appreciated while I tackle google to try assist me.
Thanks in advance :)
 
Working :) :) Thank you Pr⊕phet.

Adding List.Update() and context.ExecuteQuery() fixed the issue.

Code: 
     newlistitem["Date_Worked"] = product.Date_Worked;
                newlistitem["Test_Field"] = product.Test_Field;
                //save the item
                newlistitem.Update();
                context.ExecuteQuery();
            }
            CustomMList.Update();

Now to add only new entries when it runs a second time..
 
cool, glad you are coming right...

if there was anything unique in your data you could use that to do a match before you update but I dont know if that is possible with your data? I've used SPQuery before to search for specific items so maybe look into that to get the last updated unique value?
 
Edit :
The Execute method of job definition List.ListsManipulations (ID c2549eba-f683-49b6-937e-0821cfb9b53c) threw an exception. More information is included below.

The given key was not present in the dictionary.
Click to expand...

Anyone seen this error before? Have given the user read access to the authenticated users group and all the columns exist. Thoughts?
 
Last edited:
Pho3nix said:
Edit :

Anyone seen this error before? Have given the user read access to the authenticated users group and all the columns exist. Thoughts?
Click to expand...

It could also be that you are referencing a column that is not being returned. Had that the other day when I referenced a column that wasn't in my view, so check that.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X