How do I get information from a Business Data Catalog to be autopopulated into a list in SharePoint 2007?

Posted on 2009-04-30
Medium Priority
Last Modified: 2012-06-27
Help Anyone!!

I have sucessfully created a Bussiness Data Catalog Application to connect to a SQL 2005 database and the information can be searched or put into a Bussiness Data List Web Part (Data View) I would like to actually use the BDC information in a Custom list so I can run query's, workflows, drop down boxes to use in other lists in the site. Does anyone have any idea how I would go about doing this? I have tried one sugestion but I could not figure out where i needed to go to do the code for the lists see here: http://www.codeproject.com/KB/sharepoint/WSSDatabaseSync.aspx?fid=1531864&select=3023307&fr=1

I left off at create custom sharepoint list.
Question by:jayngin
  • 4
  • 3

Expert Comment

ID: 24273739
I created a piece of code that reads the bdc and populates a list, but I don't have a solution to update it. I use Visual Studio 2008, and the article you state probably uses the same.  The book SP Unleashed Development may help you out in understanding the basics.  The article you stated is more advanced with direct links to the database instead of using the bdc.  a colleague of mine had some difficulty with the connections in the past, but if you can pull it off, that link on code plex is the way to go.  

Author Comment

ID: 24273901
Does the code you wrote only populate this list once? Or does it populate it with any new rows of data that come into the BDC from the SQL DB?

Expert Comment

ID: 24276326
the console application runs once everytime you click it.  however it will add the new rows with the old, and unless you delete the rows added from the previous run, it will add to those to.
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.


Author Comment

ID: 24278905
Thanks for the explantion. Can I get a copy of the code possiably? I am sure I can figure out how to delete it and recreate it every time it imports that way it is always the most current data.


Expert Comment

ID: 24279350
I will put up a link tonight, that way we can add to it in a blog as we improve upon it.  

Author Comment

ID: 24282686
Thanks Kitesurf :).. I will look for it to night and let you know how it works

Accepted Solution

kitesurf earned 2000 total points
ID: 24284894
it may not be exactly what you are looking for, for it reads the user profiles and retrieves the profiles, then you would just update a list with the variables in the catch.  more code than i thought.  you can find the same code in spunleashed for both the bdc and list update.   good luck.   i can't show all the code, as it would open up a bag of worms.  this is the hardest part, and the part that is missing is just setting the variables to add to the list.  after thinking about it, my code only updates on one row.  in order to do a whole bdc, a loop of the wildcard search for an autonumber would have to be used to loop through all the rows in a database.  

/* Name: testBDCUpload
 * Description: Move BDC to a list

using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.SharePoint;
using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;
using Microsoft.Office.Server.ApplicationRegistry.Runtime;
using Microsoft.Office.Server.ApplicationRegistry.SystemSpecific;
using Microsoft.Office.Server.ApplicationRegistry.Infrastructure;
using Microsoft.SharePoint.Administration;
using Microsoft.Office.Server.Administration;
using Microsoft.Office.Server.UserProfiles;
using Microsoft.Office.Server;
using System.Web;

using System.Linq;
using System.Collections;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;

using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.Utilities;

namespace testBDCUpload
    class Program
        static void Main(string[] args)
            using (SPSite site = new SPSite("http://..."))
                using (SPWeb web = site.AllWebs["site"])
                //using (SPWeb web = site.AllWebs[0])
                    int x = 0;
                    int y = 0;
                    int v = 0;
                    string ntAcct = string.Empty;
                    string deptNbr = string.Empty;

                    SPList lrList = web.Lists["myList"];

                    ServerContext context = ServerContext.GetContext(site);
                    UserProfileManager profileManager = new UserProfileManager(context);
                    foreach (UserProfile p in profileManager)
                        string sAccount = (string)p.MultiloginAccounts[0];
                        string fullName = sAccount.Substring(sAccount.LastIndexOf('\\') + 1).Replace('.', ' ');
/*1ST BDC*/
                            x += 1;
                            NamedLobSystemInstanceDictionary sysInstances =
                            LobSystemInstance leaveRequestInstance =
                            Entity empEntity = leaveRequestInstance.GetEntities()["Emp"];

                            FilterCollection fc = empEntity.GetFinderFilters();
                            FilterBase nameFilter = fc[1];
                            FilterCollection newfc = new FilterCollection();
                            ((WildcardFilter)newfc[0]).Value = fullName;

                            IEntityInstanceEnumerator empEntityInstanceEnumerator =
                                empEntity.FindFiltered(newfc, requestInstance);

                            while (empEntityInstanceEnumerator.MoveNext())
                                IEntityInstance entityInstance = empEntityInstanceEnumerator.Current;
                                foreach (Field f in empEntity.GetFinderView().Fields)
                                    switch (f.Name)
                                        case "DeptNbr":
                                            deptNbr = Convert.ToString(entityInstance[f]);

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question