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

Posted on 2009-04-30
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:

I left off at create custom sharepoint list.
Question by:jayngin
    LVL 1

    Expert Comment

    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

    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?
    LVL 1

    Expert Comment

    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.

    Author Comment

    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.

    LVL 1

    Expert Comment

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

    Author Comment

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

    Accepted Solution

    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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
    A quick step-by-step overview of installing and configuring Carbonite Server Backup.
    Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now