Tag: BDC

Posted on

BCS/BDC Report Card

First and foremost, I LOVE SHAREPOINT.  It's awesome, it pays my bills and IT ROCKS.  I give credit where credit is due, but I'm also fair in that I will point out flaws too.  As some of you surely remember, I tweeted a little rant about BCS and how annoyed I was that customer after customer keeps asking me to make BCS do something it just can't do (point proven by the tweet I got from Paul Andrew "BCS is not a silver bullet", of which I replied, "It's a marketing problem", driven by conference presenters saying its the save all solution of the century).  A lot of people feel that because it can now both READ and WRITE data that you can DO ANYTHING with it.  WRONG.  So very wrong.  In an effort to prove that point, very simply here's my BCS Report Card (2007 scores in parenthesis):

Read Data     A+  (B)
Search Data  B-  (B-)
Write Data     D (I)

Why you say?  Why not A's across the board?  Let me explain it too you.  Let's start with a very simple table:

CREATE TABLE [dbo].[Contact](
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [ModifyDate] [datetime] NOT NULL,
    [CreateDate] [datetime] NOT NULL
)

Using BCS, I can point to this data, read it, NOT search it and write it.  Why can't I search it?  I didn't give it a identifier/primary key.  That means I can't point BCS at repeated non-unique data for searching and indexing, and hence why it gets a B-.  Adding the following will make it searchable (and now usable in SharePoint Designer 2010):

ContactId int primary key 

NOTE* it doesn't have to be a primary key, just need a column to be an identifier

Perfect.  A few more clicks in Designer and I have an external content type with a list using it.  Another couple of clicks in the Ribbon and I can even have the data syncing with my outlook (of which Fabian Williams first blogged an example of this). A few more clicks in my Search Service Application and its indexed. AWESOME!  Let me repeat that…AWESOME!  The problem comes into play when I add another table with a relationship:

–NEW TABLE–
StatusId int primary key
ShortName varchar(50)
LongName varchar(50)
ModifyDate datetime
CreateDate datetime

–ADD REF COLUMN TO CONTACT TABLE–
StatusId int

Regenerating my BCS/BDC application (which is not very friendly in Designer, but that's for another time) generates the same basic view of the contact data. However, notice that THE USER IS RESPONSIBLE for determining what the value of the StatusId column is…REALLY?  REALLY?  Of course if they type in something bad they get this:

 

Wait, what was that checkbox on the List generation?  Generate InfoPath form? 

 

That sounds promising, but:

 

The point is that BCS/BDC really only works with a single non-relational entity when it comes to WRITING of data.  Even given the fact that with SharePoint Designer (or manually if you are apt too because you get finally get so frustrated that you want to throw your laptop when doing it with Designer) you can create "Associations" to build relationships between similar or disparate data sources (another awesome feature), it really only helps with the BDC web parts for sending of filtering values.  What does this mean?

BDC ONLY WORKS WITH DE-NORMALIZED MEANINGFUL TABLES WHEN YOU WANT FULL* FUNCTIONALITY

The asterisk on "Full" simply means, the functionality offered by BCS (ref scorecard above), not what you would REALLY want. 

What do I think would be ideal?  The best options is to allow us to edit the InfoPath Form!!!  Then we can map the control to a drop down populated from a data source!  One would think that we should be able to edit the list column and point to corresponding column in the target association table to generate a selectable drop down (or even select the column in the BCS wizard or schema), but those are locked:

 

OR even better, when *generating* the BCS/BDC application definition, make it possible to create Metadata "Pointers" in the metadata service to these "distinct" items and have the column use the metadata service. 

 

But even if they (SP Product Team) go to the extreme to implement all this later (which seems inevitable as soon as they see this), "distinct" items brings in a whole different set of problems like caching and large list like issues (being they have already addressed this pattern of problem in large lists, seems trivial for them).

Why did I asterisk out "generating". BCS is a code gen tool.  It generates the BDC application definition file (and some other medium difficulty plumbing) for you with a fancy wizard.  Let me add, its a very BASIC codegen tool.  In the post that will follow this one, I will demonstrate for the first time my CodeGen tool (with its SharePoint extensions).  Watch for that post!

Some of the comments I received when I tweeted my original rant where:

  • Fabian & Chakkaradeep – use a .NET type : this is why I give an A+ to READ, but still doesn't resolve the WRITE issues
  • Some said use BDCMetaman, sorry it doesn't solve these issues either, its just another codegen tool like BCS

In summary, to quote a really great movie "Choose, but choose wisely" your integration points with SharePoint.  In the end you may fall over dead and old before you get it working (or realize it just won't work) with BCS (at least in this version #2).

Chris

Posted on

CodeGen With SharePoint BDC – Helpful Hints

I have been working on a lot of codegeneration techniques with SharePoint and approched the BDC today.  It seems that it doesn't like things like Guids that much when working with Search Stored Procedures.  It will create a default Guid instance and pass that value EVERY TIME!  And it will not allow "Wildcard" on Guid Types.

In order to get around this little fact, you have to build a dynamic sql stored procedure (that passes through the query in the proper way to avoid sql injection) that takes all string parameters!  Even then there are some types that cannot be compared with the "like" sql operator. 

I have everything working like a charm now (keeping to simple types), but man…that was a day's worth of work!

Posted on

Create a BDC Application Definition for a Web Service

In working with a large Canadian Law firm this week, I built this lab to help them learn to build Web Services that cater to BDC Web Service Application Definition files.  Pretty neat stuff…keep in mind that you have  to setup the BDC Editor before performing these steps.

Exercise 1 – Create a BDC App Def File (Web Service)

Purpose:
        Create
a BDC Application Definition File with the Microsoft BDC tool.  Note that this tool is a basic editor, it
doesn’t implement any advanced functionality. 
To do advanced things, you will need to reference the schema file and
build your own xml.

Result:            An Application Definition File

Task 1 –Create a Web
Service

  1. In Visual Studio, create a
    new web service project
    • Click
      “File->New->Web Site”
    • Select “ASP.NET Web
      Service”
    • For location, type
      “D:lab workBDCWebService”
    • Click “Ok”

Task 2 –Create a
return class

  1. Create a new class to act as
    our return type
    • Right click the
      project, select “Add New Item”
    • Select “Class”
    • For name, type
      “Product.cs”
    • Click “Add”
  2. Click “Yes” to the “App_Code”
    directory
  3. Add the following variable to
    the class (copy under the “public class Product” line):



protected int m_ProductID;

    protected
string m_ProductName;

    protected
int m_SupplierID;

    protected
int m_CategoryID;

    protected
string m_QuantityPerUnit;

    protected
double m_UnitPrice;

    protected
short m_UnitsInStock;

    protected
short m_UnitsOnOrder;

    protected
short m_ReorderLevel;

    protected
bool m_Discontinued;

 
  1. Add the following properties
    to the class:


public int ProductID

    {

        get {
return (m_ProductID); }

        set {
m_ProductID = value; }

    }

    public
string ProductName

    {

        get {
return (m_ProductName); }

        set {
m_ProductName = value; }

    }

    public int
SupplierID

    {

        get {
return (m_SupplierID); }

        set {
m_SupplierID = value; }

    }

    public int
CategoryID

    {

        get {
return (m_CategoryID); }

        set {
m_CategoryID = value; }

    }

    public
string QuantityPerUnit

    {

        get {
return (m_QuantityPerUnit); }

        set {
m_QuantityPerUnit = value; }

    }

    public
double UnitPrice

    {

        get {
return (m_UnitPrice); }

        set {
m_UnitPrice = value; }

    }

    public
short UnitsInStock

    {

        get {
return (m_UnitsInStock); }

        set { m_UnitsInStock
= value; }

    }

    public
short UnitsOnOrder

    {

        get {
return (m_UnitsOnOrder); }

        set {
m_UnitsOnOrder = value; }

    }

    public
short ReorderLevel

    {

        get {
return (m_ReorderLevel); }

        set {
m_ReorderLevel = value; }

    }

    public bool
Discontinued

    {

        get {
return (m_Discontinued); }

        set {
m_Discontinued = value; }

    }

 

 
  1. Add the following methods to
    the class:



public Product(IDataRecord record)

    {

       
this.Fill(record);

    }

 

    internal
void Fill(IDataRecord record)

    {

       
m_ProductID = (int)record["ProductID"];

       
m_ProductName = (string)record["ProductName"];

 

        if
(record["SupplierID"] != DBNull.Value)

        {

            m_SupplierID =
(int)record["SupplierID"];

        }

        if
(record["CategoryID"] != DBNull.Value)

        {

           
m_CategoryID = (int)record["CategoryID"];

        }

        if
(record["QuantityPerUnit"] != DBNull.Value)

        {

            m_QuantityPerUnit
= (string)record["QuantityPerUnit"];

        }

        if
(record["UnitPrice"] != DBNull.Value)

        {

           
m_UnitPrice = Convert.ToDouble(record["UnitPrice"]);

        }

        if
(record["UnitsInStock"] != DBNull.Value)

        {

           
m_UnitsInStock = Convert.ToInt16(record["UnitsInStock"]);

        }

        if
(record["UnitsOnOrder"] != DBNull.Value)

        {

           
m_UnitsOnOrder = Convert.ToInt16(record["UnitsOnOrder"]);

        }

        if
(record["ReorderLevel"] != DBNull.Value)

        {

           
m_ReorderLevel = Convert.ToInt16(record["ReorderLevel"]);

        }

       
m_Discontinued = (bool)record["Discontinued"];

 

    }

 
  1. Add the following attribute
    to the class:



[Serializable]

 
  1. Compile the project, fix any
    errors

Task 3 –Implement the
web methods

  1. Open the Service.cs file
  2. Add the following using
    statements to the file:


using System.Data;

using System.Data.SqlClient;

using System.Collections.Generic;

using System.ComponentModel;

using Microsoft.ApplicationBlocks.Data;

using System.Configuration;

 
  1. Add a property to get a
    connection string from the configuration file:


private string ConnectionString

    {

        get

        {

           
AppSettingsReader reader = new AppSettingsReader();

           
return (string)reader.GetValue("ConnectionString",
typeof(string));

        }

    }

 
  1. Open the web.config file
  2. Add the following line to the
    <Configuraton><appSettings> element:


    <add
key="ConnectionString"
value="server=.;database=northwind;uid=sa;pwd=Pa$$w0rd"/>

 
  1. Save the file
  2. Add a method to get a list of
    ids:



[WebMethod(Description = "Returns a List of
Products IDs")]

    public
List<int> GetProductEnumeratorIDs()

    {

       
List<int> retVal = new List<int>();

 

        string
sql = "SELECT [ProductID] FROM [Products]";

 

       
SqlDataReader reader = null;

 

        try

        {

 

           
reader = SqlHelper.ExecuteReader(this.ConnectionString,
CommandType.Text, sql);

 

            if
(reader.HasRows)

            {

 

               
while (reader.Read())

               
{

                   
retVal.Add(reader.GetInt32(0));

                }

 

            }

 

        }

        catch

        {

           
throw;

        }

        finally

        {

            if
(reader != null && !reader.IsClosed)

            {

               
reader.Close();

            }

        }

 

        return
retVal;

    }

 
  1. Add a method to get a single
    Product from the database by ProductId:


[WebMethod(Description = "Returns a single Product Entity by ID")]

    public
Product GetProduct(int productID)

    {

        string
sql = "SELECT [ProductID], [ProductName], [SupplierID], [CategoryID],
[QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder],
[ReorderLevel], [Discontinued] FROM [Products] WHERE [ProductID] =
@ProductID";

 

       
SqlDataReader reader = null;

 

        try

        {

 

           
reader = SqlHelper.ExecuteReader(this.ConnectionString,

                            CommandType.Text,
sql,

                            new
SqlParameter[] {

                                                    new SqlParameter("@ProductID",
productID) });

 

            if
(reader.HasRows)

            {

               
reader.Read();

               
return new Product(reader);

 

            }

 

        }

        catch

        {

           
throw;

        }

        finally

        {

            if
(reader != null && !reader.IsClosed)

            {

               
reader.Close();

            }

        }

 

        return
null;

    }

 
  1. Add a method to get a single
    Product from the database by ProductName:



[WebMethod(Description = "Returns a List of
Products Filtered By Name")]

    public
List<Product> GetProductsByName(string productName)

    {

       
List<Product> retVal = new List<Product>();

 

        string
sql = "SELECT [ProductID], [ProductName], [SupplierID], [CategoryID],
[QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel],
[Discontinued] FROM [Products] WHERE [ProductName] Like @ProductName";

 

       
SqlDataReader reader = null;

 

        try

        {

 

           
reader = SqlHelper.ExecuteReader(this.ConnectionString,

                            CommandType.Text,
sql,

                            new
SqlParameter[] {

                                                    new
SqlParameter("@ProductName", string.Format("%{0}%",
productName)) });

 

            if
(reader.HasRows)

            {

 

               
while (reader.Read())

               
{

                   
retVal.Add(new Product(reader));

               
}

 

            }

 

        }

        catch

        {

           
throw;

        }

        finally

        {

            if
(reader != null && !reader.IsClosed)

            {

               
reader.Close();

            }

        }

 

        return
retVal;

    }
  1. Add a method to get all the
    Products from the database:


[WebMethod(Description = "Returns a List of Products")]

    public
List<Product> GetProducts()

    {

       
List<Product> retVal = new List<Product>();

 

        string
sql = "SELECT [ProductID], [ProductName], [SupplierID], [CategoryID],
[QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder],
[ReorderLevel], [Discontinued] FROM [Products]";

 

       
SqlDataReader reader = null;

 

        try

        {

 

           
reader = SqlHelper.ExecuteReader(this.ConnectionString,

                            CommandType.Text,
sql);

 

            if
(reader.HasRows)

            {

 

               
while (reader.Read())

               
{

                   
retVal.Add(new Product(reader));

               
}

 

            }

 

        }

        catch

        {

           
throw;

        }

        finally

        {

            if
(reader != null && !reader.IsClosed)

            {

               
reader.Close();

            }

        }

 

        return
retVal;

    }

 
  1. Add a helper class called
    SqlHelper:
    • Right click the
      project, select “Add New Item”
    • Select “Class”
    • For name, type
      “SqlHelper”
    • Click “Add”
  2. Paste the code in the 07_Lab07.extra.txt
    code snippet file into the file
  3. Compile the project, fix any
    errors
  4. Set the web service port to
    200
    • Click the project in
      the solution explorer
    • In the “Properties”
      window, set Dynamic Ports to “false”
    • Set the Port number to
      “2000”
  5. Run the web service, press F5
  6. Click “Ok” to create the
    web.config file
  7. You should see your web
    service running on port 2000


Task 4 –Create a BDC
Application Definition file

  1. Click “Start->All
    Programs->Microsoft Business Data Catalog Definition Editor”
  2. The BDC editor will start:


  1. Click “Add LOB System”
  2. Click “Connect to Web Service”
  3. Type the URL of the web
    service “http://localhost:2000/BDCWebService/Service.asmx”
  4. Click “Connect”
  5. Click “Add Web Method” (on
    the right side)
  6. Drag all the methods to the design surface (make sure they are all
    added to the same Entity)!
    • GetProductEnumeratorIDs
    • GetProduct
    • GetProductsByName
    • GetProducts


  1. Click “Ok”
  2. For the name, type “Products”
  3. Click “Ok”
  4. Note how our Instances and
    Entities are populated


  1. Also note how the
    Identifiers, Methods and Actions are populated
  2. Right Click on any node,
    notice how you get the ability to add a new item that is appropriate for
    whatever level you are on in the tree view


  1. Select the “Enitiy1” node
  2. In the Property editor,
    change the name to “Products”


  1. Expand the
    Products->Methods->GetProductByName->Parameters->Return->Return->Item
    nodes


  1. You will see the properties
    of the Product class exposed as fields that will be returned in your BDC
    Application!
  2. Right click “Identifier”,
    select “Add Identifier”
    • For Name, type
      “ProductId”
    • For the Type, select
      “System.Int32”
  3. Setup an Enumerator method
    • Expand GetProductEnumeratorIDs
    • Right click
      “Instances”, select “Add Method Instance”
    • Click the “Id
      Enumerator” method type
    • For the name, type
      “EnumId”
    • Click “Ok”
  4. Setup a SpecificFinder method
    • Expand GetProduct
    • Right click “Filters”,
      select “Add Filter”
    • For FilterType, select
      “Equals”
    • For Name, type
      “ProductId”
    • Expand
      “Parameters->Return->Return”
    • Select “ProductID”
    • Set the Identifier to
      “ProductId[Product]”
    • Right click
      “Instances”, select “Add Method Instance”
    • Click the “SpecificFinder”
      method type
    • For the name, type
      “ProductSpecificFinder”
    • Click “Ok”
  5. Setup a Finder method
    • Expand GetProducts
    • Expand
      “Parameters->Return->Return->Item”
    • Select “ProductID”
    • Set the Identifier to
      “ProductId[Product]”
    • Right click
      “Instances”, select “Add Method Instance”
    • Click the “Finder”
      method type
    • For the name, type
      “ProductFinder”
    • Click “Ok”
  6. Right click the “Products”
    LobSystem and select “Export”


  1. Save to your desktop as Lists.xml
  2. Open the file, review its
    contents

Task 5 –Upload your
new BDC Application

  1. Open the Central
    Administration site
  2. Click “SharedServices1”
  3. Click “Import Application
    Definition”
  4. Select your “products.xml”
    file
  5. Click “Import”

Task 7 –Create BDC
Web Part

  1. Open your team site
  2. Click “Site Actions->Edit
    Page”
  3. Select the “Business Data
    List” web part
  4. Click “Add”
  5. Click “Open the tool pane”
    link
  6. For Type, click the browse
    button
  7. Select the “Products”
    business Data Type
  8. Click “Ok”
  9. Click “Ok”
  10. You should see a listing of
    all the products from the web service!