Lightning Tools Blog

Social Squared version 6.1 release

Lightning Tools — Thu, 09 Feb 2012 13:26:59 GMT

Social Squared 6.1 has been released

A new version of Social Squared has been released with a couple of bug fixes and a few nice new features to kick start February

Click here to download Social Squared.

What’s new?

Email Digest

In the latest release of Social Squared users have the ability to sign up for digest emails to notify them of changes inside of the forums. It was always possible to have an email alert to come through when someone replies or a new topic is created but now it is possible to daily or weekly notifications instead!

The emails come through with a nice listing of what has changed

Email Timer Job Log

It is now easier to view information from the time job log via Site Settings. Any issues with sending or receiving emails to the Social Squared forums can be easily identified. If you want a reminder of what you can do with Social Squared and email check out the short video from Nick Swan here: http://lightningtools.com/blog/archive/2011/01/27/social-squaredndashreply-via-email.aspx

Search Forum Group

It is possible to now restrict searches using the Social Squared search to just the current forum group making it easier to find the content you want.

Next Version

We’re working really hard behind the scenes here at Lightning Tools to bring some great new improvements to Social Squared version 7! As soon as we can we will make details of these exciting additions available on our blog

If you have any feedback send it through to contact@lightningtools.com and I will see what you have to say!

Click here to download Social Squared.

Using Twilio with SharePoint to send SMS messages

Lightning Tools — Fri, 03 Feb 2012 10:04:45 GMT

Twilio is a Software as a Service platform that you can use when you want to integrate voice or SMS text messages into your applications. There are a few instances where this could be interesting for SharePoint such as sending SMS messages when certain things happen in SharePoint either through event handlers or workflows. Let’s see how we can easily register for an account, setup a number and start getting SharePoint sending SMS messages using an Event Handler!

1, Visit www.twilio.com and click Try Twilio button – the good news is at the time of writing you get $30 of credit to try Twilio out!

2, Once logged in click on the ‘Upgrade Now’ link to add a credit card to your Twilio account and add a small amount, say $20. You can’t purchase a phone number to send messages from with the trial account (you still get $30 free though once you upgrade)

3, Once upgraded, click Numbers from the main menu and then the ‘Buy a new Twilio Number’ link.

4, You can put in an area code if you like for your number to be based off, or if like me you are in the UK just press search and it will give you numbers to chose from with a London area code

[click the images for a larger view]

5, Find the number you like the look of, and click the ‘Buy’ button to grab it!

6, We need to get our AccountId and Password to use later on in our code. Click on the Dashboard link in Twilio. At the top you’ll see your AccountId, and if you click the padlock to the right your account password will also be displayed. Copy these down for later…

7, The Twilio rest API all runs under https which is a good thing, but means a little extra configuration if you want to use it with SharePoint 2010. We’ll need to do an extra step of uploading the client certificate to SharePoint 2010 for SharePoint to trust the url. Open the following url in Firefox:

https://api.twilio.com/2010-04-01

8, Click on the Twilio icon in the address bar that indicates the certificate for this url is trusted

9, In the panel that opens up click on the ‘More Information’ button:

10, In the Page Information window that opens up click on ‘View Certificate’

11, In the certificate viewer window move to the details tab, and then click the top line in the ‘Certificate Hierarchy’ window and then click the ‘Export’ button. This should allow you to save the certificate.

12, Now we can add the certificate to SharePoint 2010. Open up Central Administration, Click on the Security Link and then ‘Manage Trust’.

13, Click on the New button in the ribbon and a dialog form will pop up. Give the trust relationship a name of Twilio and browse to the certificate file you downloaded in step 10. Finally click on OK.

14, Hurrah – now we can start writing some code. Open up Visual Studio 2010 and create a new SharePoint 2010 Event Receiver project.

15, In the proceeding windows that show as part of creating your Visual Studio project chose to deploy it as a Farm Solution.

16, When setting up the event receiver, pick List Item Events, the Announcement list type and finally the ‘An item was added’ event type – then click Finish

17, Open EventReceiver1.cs and paste in the following code:

private string CreateFormattedPostRequest(Dictionary<string, string> values)
{
    var paramterBuilder = new StringBuilder();
    var counter = 0;
    foreach (var value in values)
    {
        paramterBuilder.AppendFormat("{0}={1}", value.Key, HttpUtility.UrlEncode(value.Value));

        if (counter != values.Count - 1)
        {
            paramterBuilder.Append("&");
        }

        counter++;
    }

    return paramterBuilder.ToString();
}

private HttpWebRequest CreateWebRequest(string endPoint, Int32 contentLength)
{
    var request = (HttpWebRequest)WebRequest.Create(endPoint);

    request.Method = "POST";
    request.ContentLength = contentLength;
    request.ContentType = "application/x-www-form-urlencoded";

    return request;
}

These two small helper methods will help us format out rest requests to the Twilio rest web service. Thanks to Derik for these two sections of code…

18, Add the following code to your ItemAdded method

string accountId = "";
string password = "";
string from = "";
string to = "";

string title = properties.ListItem["Title"].ToString();

string endPoint = string.Format("https://api.twilio.com/2010-04-01/Accounts/{0}/SMS/Messages.xml", accountId);


Dictionary<string, string> parameters = new Dictionary<string, string>();
parameters.Add("From", from);
parameters.Add("To", to);
parameters.Add("Body", title);

var populatedEndPoint = CreateFormattedPostRequest(parameters);
byte[] bytes = Encoding.UTF8.GetBytes(populatedEndPoint);

HttpWebRequest request = CreateWebRequest(endPoint, bytes.Length);

request.Credentials = new NetworkCredential(accountId, password);

using (var requestStream = request.GetRequestStream())
{
    requestStream.Write(bytes, 0, bytes.Length);
}

using (var response = (HttpWebResponse)request.GetResponse())
{
    if (response.StatusCode != HttpStatusCode.Created)
    {
        string message = String.Format("POST failed. Received HTTP {0}", response.StatusCode);
        throw new ApplicationException(message);
    }
}

base.ItemAdded(properties);

Set the values of accountId and password to the values we got in step 6. The value for ‘from’ should be the number you bought at Twilio, and the ‘to’ value is the number you want to send an SMS to.

19, Tada – we are good to go. Press F5 and Visual Studio will compile and deploy your event receiver to SharePoint. If you do not have an Announcements list at the site Visual Studio opens up simply create one. Then go ahead and add an announcement and wait for the SMS text message to come through! Woot!

Hopefully this has given you a taster to see how easy it is to use Twilio to send SMS text messages from SharePoint. If you have any other ideas how this service would be useful with SharePoint leave them in the comments!

Mobile solutions for on premise data with Azure Service Bus

Lightning Tools — Mon, 23 Jan 2012 08:10:11 GMT

Note: I’m learning about Azure and Mobile Web App development as I go along. If you see any mistakes or better ways of doing things please drop me an email : nick@lightningtools.com

With more people becoming mobile workers, or working from home - business data that is stored away in on premise databases needs to be set free for people to use wherever they are, on whatever device they like. Accomplishing this can be a challenge for any size organization, opening firewall ports, active directory federated services, WCF service endpoints etc...

Setting the data free

If you have an on premise data source that you want to make available to mobile workers you do have a number of options:

1, SharePoint 2010, BCS + SharePoint Workspace
This is good for users who may not always have Internet connectivity as they can work offline and resync

2, Present the data to the outside world through a WCF service hosted on premise.
This does mean exposing parts of the network to the outside world and needing to manage authentication to the WCF service. Certainly not impossible, but it does present challenges and risks.

3, Using bits and pieces now available in Windows Azure
Using the Service Bus, we can expose the data through the firewall without needing to open up any extra ports. The authentication and security is managed through Azure Service Bus and ACS.

I'm presuming we are working with a business that does not want to configure and setup ADFSv2. With many companies these days allowing employees to buy and use their own mobile or tablet devices managing these internally becomes a challenge.

The actual data presentation solution will be an ASP.NET MVC web application running on Windows Azure. We'll use the jQueryMobile javascript library to present the data in a nice mobile looking way. This saves us having to build separate mobile applications for iPhone, Windows Phone, Android etc... Also having it as a web application allows us to easily rollout changes rather than expecting users to manually upgrade a native application.

We'll use a simple example. In our company we have a product stock control system, and our people on the road need to be able to check the stock levels before placing an order. Working with a simple Products table:

ProductId - int, primary key
Name - varchar(256)
Price – money
StockLevel - int

We will use the Azure Service Bus to allow on premise data to be made available to the outside world. The Service Bus has 2 endpoints. The Listener endpoints will sit inside the organization, constantly running and listening for calls to execute methods that return data. The second component is a caller application, that calls the Listener methods over the service bus. The service bus makes sure the connection between the caller and listener are secure.

Assumption : this walk through assumes you already have an Azure account up and running, with the necessary Azure SDK's and tools installed. To get an account setup and download the tools you need visit www.azure.com

Configure the Service Bus

1, Log in to www.azure.com and click the Manage button to open up the management portal.

2, Once in the management portal, click on the 'Service Bus, Access Control & Caching' tab

3, Click on the 'New' button within the 'Service Namespace' section

4, Give the new service bus you want to create a name, such as MobileProducts and select a location for the ServiceBus to run that makes sense geographically

5, Once your Service Bus is successfully created, click on the MobileProducts name in the list of buses available. This will display a list of properties down the right side.

6, Click on the 'View' button in the 'Default Key' section. Listed in the pop up you shall see the Default Issuer and Default Key that you will need shortly.

Create a Model project

1, Open Visual Studio 2010 and create a new blank solution called MobileProducts. Add a new Class Library project to the solution called MobileProducts.Model. The target Framework should be .NET 4.0

The Listener and Calling projects need to understand what object is being passed over the service bus, so it makes sense to create a project where you will define all these objects in classes in a central place where they can be referenced.

2, Add a reference to System.Runtime.Serialization

3, Rename the existing Class1.cs file to Product.cs, and amend/add the following attributes and properties to the class:

[DataContract]
public class Product
{
    [DataMember]
    public int ProductId { get; set; }
       
    [DataMember]
    public string Name { get; set; }
       
    [DataMember]
    public decimal Price { get; set; }
       
    [DataMember]
    public int StockLevel { get; set; }

}

I’m not going to list the using references you should add at the top of your code. If you get red squiggly lines when compiling, put your cursor on the code in question and do ctrl + . and Visual Studio will assist you in adding the necessary using references.

Creating the Listener project

1, Add a new Console application to the Visual Studio Solution called MobileProducts.Listener.

2, Ensure the project type is .NET Framework 4.0 (rather than .NET 4.0 client profile)

3, Add a reference to our local project MobileProducts.Model.

4, Add references to the necessary Azure libraries we will be using. There are:

System.ServiceModel.dll - this can be found from the standard .NET reference list

Microsoft.ServiceBus.dll - you'll need to browse to C:\Program Files\Windows Azure SDK\v1.6\ServiceBus\ref\Microsoft.ServiceBus.dll to find it.

5, Add a Linq 2 SQL class to the project and call it Database.dbml, add the Products table from the simple database you setup and created earlier.

6, We want to create a method that can be called over the Service Bus that returns a list of products. We'll use a contract/interface so add an interface class to your project called IGetData.cs and ensure it has the following code added:

[ServiceContract(Name = "MobileProductsContract", Namespace = "http://MobileProductsContract")]
public interface IGetData
{
    [OperationContract]
    IEnumerable<MobileProducts.Model.Product> GetProducts();

    [OperationContract]
    MobileProducts.Model.Product GetProductById(int Id);
}

7, Next we can add a class that implements this contract, add a class to the project called GetData.cs and implement the following code...

[ServiceBehavior(Name = "MobileProductsContract", Namespace = "http://MobileProductsContract")]
public class GetData : IGetData
{
    public IEnumerable<MobileProducts.Model.Product> GetProducts()
    {
        DatabaseDataContext db = new DatabaseDataContext();
        IEnumerable<MobileProducts.Model.Product> products = from p in db.Products
                                                             select new MobileProducts.Model.Product
                                                             {
                                                                 Name = p.Name,
                                                                 Price = p.Price,
                                                                 ProductId = p.ProductId,
                                                                 StockLevel = p.StockLevel
                                                             };

        return products;
    }

    public MobileProducts.Model.Product GetProductById(int Id)
    {
        DatabaseDataContext db = new DatabaseDataContext();
        var product = db.Products.SingleOrDefault(p => p.ProductId == Id);

        return new MobileProducts.Model.Product
                            { Name = product.Name,
                              Price = product.Price,
                              ProductId = product.ProductId,
                              StockLevel = product.StockLevel
                            };
    }
}

8, In App.Config we need to describe the bindings we want to use, add the following just beneath the connection strings elements:

<system.serviceModel>
    <services>
      <service name="MobileProducts.Listener.GetData">
        <endpoint contract="MobileProducts.Listener.IGetData" binding="netTcpRelayBinding" />
      </service>
    </services>
    <extensions>
      <bindingExtensions>
        <add name="netTcpRelayBinding" type="Microsoft.ServiceBus.Configuration.NetTcpRelayBindingCollectionElement, Microsoft.ServiceBus, Version=1.6.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
      </bindingExtensions>
    </extensions>
  </system.serviceModel>

9, We can now write the code in Program.cs to connect to our service bus and start listening for calls to execute methods...add this code within the Main method:

Console.Title = "Mobile Products Listener Service";

string nsDmn = "";
string issrName = "";
string issrSecret = "";

Uri address = ServiceBusEnvironment.CreateServiceUri("sb", nsDmn, "MobileProducts");

TransportClientEndpointBehavior sharedCreds = new TransportClientEndpointBehavior();
sharedCreds.TokenProvider = TokenProvider.CreateSharedSecretTokenProvider(issrName, issrSecret);

try
{
    ServiceHost serviceHost = new ServiceHost(typeof(GetData), address);
    IEndpointBehavior serviceRegSettings = new ServiceRegistrySettings(DiscoveryType.Public);
    foreach (ServiceEndpoint serviceEndpoint in serviceHost.Description.Endpoints)
    {
        serviceEndpoint.Behaviors.Add(sharedCreds);
    }

    serviceHost.Open();

    Console.WriteLine("Service Information: " + address);
    Console.WriteLine("Hit [Enter] to exit application...");
    Console.ReadLine();

}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
    Console.ReadLine();
}

10, Set the following variables:

nsDmn = the name you gave the Service Bus instance you created

issrName = the owner

issrSecret = the default key you got once your Service Bus was created

Create a test Caller project

It's always good to take mini steps and test things a we go along. Eventually our caller project will be the ASP.NET MVC web application, but initially lets create another console application that will act as a caller. If we get any issues it will be much easier to debug each time, make sure we have the Service Bus component working, and then move onto the next stage of building a mobile web app.

1, Add a new console application to your solution called MobileProducts.Caller.

2, Change the project from .Net 4.0 Client Profile to standard .Net Framework 4.0 again.

3, Add a reference to our local project dll, MobileProduct.Model and also Microsoft.ServiceBus and System.ServiceModel

4, We want to define the contract again, so our program understands the method it will be calling over the Service Bus and the data it will be getting back. Add an interface class to the project called IGetData and add the same code as when we created the contract for our Listener class

[ServiceContract(Name = "MobileProductsContract", Namespace = "http://MobileProductsContract")]
public interface IGetData
{
    [OperationContract]
    IEnumerable<Product> GetProducts();

    [OperationContract]
    Product GetProductById(int Id);
}

5, Add an application configuration (app.config) file to the project and add the following service model definition:

  <system.serviceModel>
    <client>
      <endpoint name="RelayEndpoint" contract="MobileProducts.Caller.IGetData" binding="netTcpRelayBinding" />
    </client>
    <extensions>
      <bindingExtensions>
        <add name="netTcpRelayBinding" type="Microsoft.ServiceBus.Configuration.NetTcpRelayBindingCollectionElement, Microsoft.ServiceBus, Version=1.6.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
      </bindingExtensions>
    </extensions>
  </system.serviceModel>

6, Add the following code to the Main method of Program.cs

Console.Title = "Mobile Products Test Caller";

string nsDmn = "";
string issrName = "";
string issrSecret = "";

Uri serviceUri = ServiceBusEnvironment.CreateServiceUri("sb", nsDmn, "MobileProducts");

TransportClientEndpointBehavior sharedCreds = new TransportClientEndpointBehavior();
sharedCreds.TokenProvider = TokenProvider.CreateSharedSecretTokenProvider(issrName, issrSecret);

ChannelFactory<IGetData> channelFactory = new ChannelFactory<IGetData>("RelayEndpoint", new EndpointAddress(serviceUri));

channelFactory.Endpoint.Behaviors.Add(sharedCreds);

IGetData channel = channelFactory.CreateChannel();

((ICommunicationObject)channel).Open();

try
{
    IEnumerable<MobileProducts.Model.Product> products = channel.GetProducts();
    foreach (var product in products)
    {
        Console.WriteLine(product.Name);
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
}

((ICommunicationObject)channel).Close();
channelFactory.Close();

Console.WriteLine("Press [Enter] to continue...");
Console.ReadLine();

7, As with the Listener project, set the same values for nsDmn, issrName and issrSecret.

8, Assuming you have some products created in your database table, you can now try the Service Bus out!

First right click on the MobileProducts.Listener project and choose Debug -> Start New Instance

Wait for the Service Information to be displayed in the console window

9, Now right click on the MobileProducts.Caller project and also choose Debug -> Start New Instance

In the Caller console window you should see a list of product names from your database table! Woot Woot! This means the Caller program is calling the GetData method over the Service Bus, and the Listener is executing this method and returning the data back across the Bus. Pretty awesome!

The MobileProducts.Caller application has served its purpose. We can now move forward knowing the service bus is working and look at building a mobile application to surface our data.

Create a mobile web app

Now that we know our Service Bus is setup and working through using the Listener and Caller projects we built, we can move forward and build a Mobile Web Application. We might as well host this in Windows Azure as well.

1, Right click on the Solution in Visual Studio and choose to Add -> New Project

2, From the Cloud section pick the Windows Azure Project and call it MobileProducts.Azure

3, When presented with the list of Azure Projects to create, move the ASP.NET MVC 3 Web Role over.

4, Right click on the project when it is in the right hand pane and choose 'Rename'. Give it a new name of MobileProducts.WebApp

5, In the ASP.NET MVC 3 new project dialog use the Empty project template, ensure Razor is selected as the view engine, and tick the box for using HTML 5 semantic markup

6, Lets do the backend work first and get our web application able to talk to the Service Bus. Add a reference to our local project MobileProducts.Model. Also add a reference to Microsoft.ServiceBus and System.ServiceModel. You should be able to easily pick these from the Recent tab in the 'Add Reference...' dialogue.

7, In solution explorer, right click on the Models folder in your MVC web app and add a new interface class called IGetData. Add the same code that we previously have done for this interface class:

[ServiceContract(Name = "MobileProductsContract", Namespace = "http://MobileProductsContract")]
public interface IGetData
{
    [OperationContract]
    IEnumerable<Product> GetProducts();

    [OperationContract]
    Product GetProductById(int Id);
}

8, Right click on the Models folder and choose to add a new class. Give the class a name of ProductWorker.cs

9, In the class add the following static methods:

public static IEnumerable<Product> GetProducts()
{
    string nsDmn = "";
    string issrName = "";
    string issrSecret = "";

    Uri serviceUri = ServiceBusEnvironment.CreateServiceUri("sb", nsDmn, "MobileProducts");

    TransportClientEndpointBehavior sharedCreds = new TransportClientEndpointBehavior();
    sharedCreds.TokenProvider = TokenProvider.CreateSharedSecretTokenProvider(issrName, issrSecret);

    ChannelFactory<IGetData> channelFactory = new ChannelFactory<IGetData>("RelayEndpoint", new EndpointAddress(serviceUri));

    channelFactory.Endpoint.Behaviors.Add(sharedCreds);

    IGetData channel = channelFactory.CreateChannel();

    ((ICommunicationObject)channel).Open();

    return channel.GetProducts();
}

public static Product GetProductById(int Id)
{
    string nsDmn = "";
    string issrName = "";
    string issrSecret = "";

    Uri serviceUri = ServiceBusEnvironment.CreateServiceUri("sb", nsDmn, "MobileProducts");

    TransportClientEndpointBehavior sharedCreds = new TransportClientEndpointBehavior();
    sharedCreds.TokenProvider = TokenProvider.CreateSharedSecretTokenProvider(issrName, issrSecret);

    ChannelFactory<IGetData> channelFactory = new ChannelFactory<IGetData>("RelayEndpoint", new EndpointAddress(serviceUri));

    channelFactory.Endpoint.Behaviors.Add(sharedCreds);

    IGetData channel = channelFactory.CreateChannel();

    ((ICommunicationObject)channel).Open();

    return channel.GetProductById(Id);
}

10, In the Views folder, right click and add a new folder called Home. Right click the Home folder and add a View called Index, and then add a View called Product

11, Right click the Controllers folder and add a new Controller called 'HomeController' created using the 'Empty controller' template

12, In HomeController.cs amend the code to look like this:

public ActionResult Index()
{
    return View(ProductWorker.GetProducts());
}

public ActionResult Product(int Id)
{
    return View(ProductWorker.GetProductById(Id));
}

13, Open up web.config and paste in the following XML fragment just after the opening <Configuration>

<system.serviceModel>
    <client>
      <endpoint name="RelayEndpoint" contract="MobileProducts.WebApp.IGetData" binding="netTcpRelayBinding" />
    </client>
    <extensions>
      <bindingExtensions>
        <add name="netTcpRelayBinding" type="Microsoft.ServiceBus.Configuration.NetTcpRelayBindingCollectionElement, Microsoft.ServiceBus, Version=1.6.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
      </bindingExtensions>
    </extensions>
  </system.serviceModel>

14, Open up Index.cshtml and paste in the following:

@model IEnumerable<MobileProducts.Model.Product>
@{
    ViewBag.Title = "Available Products";
}

<h2>Available Products</h2>

@foreach (var product in Model)
{
    <p>@product.Name</p>
}

15, Hurrah - it's time to test again. Right click your Listener project and Debug - Start new instance, and once that has fired up and is connected to the Service Bus right click your MVC web app and do the same. You should get your list of products displayed.

Pretty simple, but it shows the data is coming through the Service Bus ok.

16, We want to make our application really nice to use on mobiles, so lets add in jQueryMobile. In Views -> Shared open _Layout.cshtml. Before the closing </head> tag add in:

<link rel="stylesheet" href="http://code.jquery.com/mobile/1.0/jquery.mobile-1.0.min.css" />
<script type="text/javascript" src="http://code.jquery.com/mobile/1.0/jquery.mobile-1.0.min.js"></script>

Also change jQuery so it is using the latest version:

<script type="text/javascript" src="http://code.jquery.com/jquery-1.6.4.min.js"></script>

17, Final thing to add in the head section is:

<meta name="viewport" content="width=device-width, initial-scale=1">

18, Modify the html in your Index.cshtml file to look like this:

@model IEnumerable<MobileProducts.Model.Product>
@{
    ViewBag.Title = "Available Products";
}

<div data-role="page">

    <div data-role="header">
        <h2>Available Products</h2>
    </div>

    <div data-role="content">

        <ul data-role="listview" data-inset="true" data-filter="true">
            @foreach (var product in Model)
            {
                <li><a href="/home/product/@product.ProductId" data-transition="pop">@product.Name</a></li>
            }
        </ul>

    </div>

</div>

19, Open up Product.cshtml add get your code looking like:

@model MobileProducts.Model.Product

@{
    ViewBag.Title = "Product";
}

<h2>@Model.Name</h2>
<p>Price : @Model.Price</p>
<p>Stock Level : @Model.StockLevel</p>

<a href="#" data-rel="back">Back</a>

20, Go through the process of starting up your Listener project, and then your Web App. Hopefully the UI will look a lot nicer and more jQueryMobile like:

Now we are going to publish our web app to Azure so we can test it on our own mobiles.

Publish to Azure

Running locally is all good fun, but for our app to be viewable on mobile devices we want to publish it to Azure. Lets go do that...

1, Right click on MobileProducts.Azure in the solution explorer and choose 'Package...'

2, Open up www.azure.com and log in. Click on 'New Hosted Service in the ribbon

3, Give your hosted service a name, url, pick a region, deployment name - and then browse to the MobileProducts.Azure.cspkg and ServiceConfiguration.Cloud.cscfg files that Visual Studio created for you as part of the Packaging process.

4, The finally click OK. You can accept the warning that is displayed upon clicking OK as this is just a test app, but if it was a real production level app you'd want to take a bit more notice of it.

Once Azure has gone through the process of provisioning your new hosted service and uploading your application get out your smartphone and browse to the url you gave your application in Azure. Hopefully you will see something similar to below:

[Click for a larger image]

Check out how cool the search and page transitions are. jQueryMobile really is nice!

Things to think about

Authentication…We’ll be looking into this in future blog posts. At the moment our web app is open to the public so anybody can hit up our url. In most situation’s with company sensitive data we wouldn’t want this to happen.

Thanks for reading this far, hopefully this post has been useful and has got you excited about Azure and Mobile web apps. Any thoughts or feedback, please leave a comment or email nick@lightningtools.com

Lightning conductor 2010 web part version 2.0.0.2

Lightning Tools — Wed, 23 Nov 2011 14:44:52 GMT

An interim release of the Lightning Conductor 2010 Web Part is available in the following versions:

Lightning Conductor 2010 Express – Free web part which aggregates from the current site collection only but provides an easier way to display your content through the SPGridView wizard.

http://www.lightningtools.com/lightning-conductor-2010-express-web-part.aspx

Lightning Conductor 2010 Web Part Trial – The 30 day trial of the Cross Site Collection and Cross Web Application rollup web part providing you with the ability to aggregate content and display meta data via a wizard.

http://www.lightningtools.com/lightning-conductor-2010-web-part.aspx

Lightning Conductor 2010 Web Part Full– The 30 day trial of the Cross Site Collection and Cross Web Application rollup web part providing you with the ability to aggregate content and display meta data via a wizard.

To download the full version, please download from the customer downloads centre on our site: www.lightningtools.com.

You can view the release notes of version 2.0.0.2 by navigating to: http://www.lightningtools.com/lightning-conductor-2010/LatestReleaseNotes.aspx

Regards

Firebird and the Business Connectivity Services in SharePoint 2010

Lightning Tools — Tue, 22 Nov 2011 21:16:27 GMT

In this walkthrough we will demonstrate how to integrate Firebird database with Microsoft SharePoint 2010 through ODBC and display Firebird data on SharePoint 2010 page. In order to do that we will describe how to create a new external content type by means of BCS Meta Man.

Prerequisites:
- Microsoft SharePoint Server 2010
- Microsoft Visual Studio 2010 Professional or higher
- BCS Meta Man
- Download and install Firebird ODBC driver (Windows 32-bit Full Install) for Microsoft Windows 32-bit http://www.firebirdsql.org/en/odbc-driver/ on a machine where BCS Meta Man runs
- Download and install Firebird ODBC driver (Windows 64-bit Full Install) for Microsoft Windows 64-bit http://www.firebirdsql.org/en/odbc-driver/ on a machine where SharePoint 2010 runs
- Firebird client component (it’s a part of Firebird server installer) for Microsoft Windows 32-bit http://www.firebirdsql.org/en/downloads/ on a machine where BCS Meta Man runs
- Firebird client component (it’s a part of Firebird server installer) for Microsoft Windows 64-bit http://www.firebirdsql.org/en/downloads/ on a machine where SharePoint 2010 runs
Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e FirebirdBCSMetaManDemo and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way
Click the “Add Connection” button to show the “Connection Dialog”
Select “ODBC Server” as Data Source type, enter Firebird ODBC connection string (for example it can be “DbName=cygnus:C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB; Driver=Firebird/InterBase(r) driver;Uid=SYSDBA;Pwd=masterkey;Trusted_Connection=true;”), enter double quotes as left and right delimiters and “?” as parameter symbol and click “Connect” button
The Data Source Explorer will now be populated with your Firebird data
We will be working with the ”DEPARTMENT” table, to add it to the Model , just drag it from the “Data Source Explorer” onto the “Diagram”
When you drop the table you will be shown the following dialog, we should choose “Database , click “OK”
Accept the default entity name by clicking “Next”
Accept the default “Identifiers” by clicking “Next”
Accept the default 3 methods to be created, click “Generate” (The default 3 methods are: “Finder” – Returns all items, “Specific Finder” – Returns a single item by identifier, “IdEnumerator” – Returns just the Identifiers for all of the items )
Press F5 to deploy, this will load up your SharePoint Page once deployed
Add a new External list by selection on SharePoint page “Site Action”->“More Options”->“External List”
Click on the icon to show the available External Content Types
Select our FirebirdBCSMetaManDemo.DEPARTMENT External Content Type, click “OK”
Click “Create”
Your External System data from Firebird database is now displayed in the SharePoint External List

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

SharePoint technical support engineer

Lightning Tools — Mon, 21 Nov 2011 12:53:46 GMT

Lightning Tools is seeking the help of a support engineer with good SharePoint experience. We are a fast growing company who thrive on providing great SharePoint tools and web parts to companies of all sizes worldwide. We provide a great level of customer service, and in order to maintain that we need to expand our support team.

The role will involve answering technical questions from our customers either by email or telephone in a professional manner, providing remote assistance to the customer and feed back bug reports to our product managers.

The successful candidate will be professional, able to work within the hours of 8:30am US Eastern to 5pm US Eastern and will have good knowledge of SharePoint. Experience of SharePoint Business Connectivity Services, SharePoint administration and web part configuration would be very advantageous.

Full training will be given on all of our products as well as SharePoint Administration/Development.

This is an opportunity to work with a great team while expanding your SharePoint knowledge and have the ability to work from the comfort of your own home.

Salary will be negotiable depending on experience.

If you are interested in the role, please submit your resume to brett@lightningtools.com

Article by brett lonsdale on content aggregation techniques

Lightning Tools — Fri, 28 Oct 2011 11:11:33 GMT

In last months Dutch Information Workers Group e magazine Brett Lonsdale (co-founder of Lightning Tools) wrote an article on all of the possible ways to aggregate content in SharePoint 2010 and discussed the pro’s and cons of each method. You can subscribe and download the magazine for free from: http://www.diwug.nl/Pages/downloads.aspx

sharepoint saturday uk–12th november 2011

Lightning Tools — Tue, 25 Oct 2011 08:45:34 GMT

Brett Lonsdale from Lightning Tools is co-hosting SharePoint Saturday UK with Mark Macrae and Tony Pounder from ID Live. If you are interested in attending a totally free one day SharePoint event on Saturday 12th November, please sign up using the following url: http://www.sharepointsaturday.org/uk/default.aspx

The event is being held in Nottingham with over 30 speakers presenting from all over the world. Lightning Tools is one of the many sponsors of the event who make it possible for you as an attendee to receive expert SharePoint education.

If you are attending the event, we would love to meet you and show you our latest offerings.

compare sharepoint site permissions and clone permissions

Lightning Tools — Tue, 27 Sep 2011 14:03:42 GMT

Have you ever spent hours trying to figure out SharePoint site permissions and perhaps wonder why you can access some sites but not others? If you rely on SharePoint’s out of the box permission reporting you will have probably become frustrated when trying to determine what permissions your users have to the sites, lists, and list items.

In our next release which is due in November, as well as all of the existing permission reports that DeliverPoint provides such as Permission Inheritance reports, and Unique Permission reports, DeliverPoint will offer comparison reports. You can literally spend hours trying to figure out permissions for one site without having to compare them to others. With DeliverPoint, you can now select as many sites as you like and compare the permissions across all of those sites within a matter of seconds.

Compare Permission Report (Click the image to enlarge)

Once you have compared the sites, you may decide that the permissions should be the same across all of the selected sites. Doing this manually using SharePoint would be cumbersome even for a small SharePoint environment. DeliverPoint allows you to select a source and a target site and then synchronise the permissions:

Clone Site Permissions (Click the image to enlarge)

Managing Permission inheritance can also be a frustrating task and also risky as changing your site inheritance options can often mean somebody is accidentally granted permissions. DeliverPoint allows you to easily see the Sites, Lists and List items that have unique permissions and also provides the option to re-inherit the permissions or break permission inheritance.

Manage Site Permission Inheritance (Click the image to enlarge)

Our treeview shows clearly which sites inherit permissions and which sites have unique permissions:

Legend for the DeliverPoint Treeview (Click the image to enlarge)

As well as showing the permission inheritance for the sites, we also allow you to report on Lists with unique permissions as well as List Items within a list:

Unique Inheritance Reports (Click the image to enlarge)

DeliverPoint also shows you exactly why users have the permissions that they have even if they are granted permissions through a Domain Group

Enumeration of AD Groups (Click the image to enlarge)

DeliverPoint is not just another Farm Administrators permission management tool, its a permission management tool that is fully embedded into SharePoint and is accessible for Site Collection Administrators and Site Administrators.

If you are interested in beta testing this version, please contact brett@lightningtools.com or visit our booth for a demo at SharePoint Conference Anaheim.

why visit lightningtools at spc?

Lightning Tools — Thu, 22 Sep 2011 10:03:15 GMT

Lightning Tools is proud to be sponsoring the SharePoint Conference again and this time will be there in force. Julia Ito, Chris Ryan, Nick Swan (SharePoint MVP), Brett Lonsdale, Phill Duffy, Richard Young and Phillip McMillan will be at the Lightning Tools booth to greet you!

At the Lightning Tools booth we’ll not only provide you with a warm welcome, but we’ll take the time to show you our up and coming product releases which include a new improved Social Squared, Permissions Management for the cloud, and content aggregation for the cloud. If you haven’t taken the chance to read about or try our Data Masher tool, come and ask us for a demo! The Data Masher now allows syncing data to Office 365!!

Not only do we have some fantastic new products to show you, but we also have the one and only Steve Fox giving away FREE signed copies of his Developing SharePoint Applications with Windows Azure book, Dux Raymond Sy will be providing FREE signed copies of his SharePoint for Project Management book and will be performing a rap at the Lightning Tools booth and finally Bill English (SharePoint MVP) and Ben Curry (SharePoint MVP) will also be giving away FREE signed copies of the Administrators Companion book. “I did actually contribute a chapter to this so i’ll be more than happy to sign it too “ (Print the flyer below as a reminder of the times or come and get one from us at booth 651)

If you are a fan of the SharePoint Pod Show, you’ll find Rob Foster (SharePoint MVP), Nick Swan (SharePoint MVP), and myself hanging around the booth most of the time. We’ll be really happy to meet some of our listeners and perhaps share a beer or discuss some of Robs BBQ techniques with you.

Finally, this year we have a bit of a racing theme and you could be the lucky winner of a Nitro powered radio controlled car. Come and visit us for details.

If you don’t want to come and see us for any of the above, just pop by and say hi!

MySQL Stored Procedures and the Business Connectivity Services in SharePoint 2010

Lightning Tools — Fri, 16 Sep 2011 15:54:09 GMT

Introduction

In this walkthrough we will demonstrate how to integrate MySQL stored procedures with Microsoft SharePoint 2010 and display the data on SharePoint 2010 page. In order to do that we will describe how to create a new external content type by means of BCS Meta Man.

Prerequisites

Microsoft SharePoint Server 2010
Microsoft Visual Studio 2010 Professional or higher
BCS Meta Man
Download and install “Windows (x86, 32-bit), MSI Installer Connector-ODBC” http://www.mysql.com/downloads/connector/odbc/ on a machine where BCS Meta Man runs
Download and install “Windows (x86, 64-bit), MSI Installer Connector-ODBC” http://www.mysql.com/downloads/connector/odbc/ on a machine where SharePoint 2010 runs
MySQL stored procedures for CRUD operations such as:
1. CREATE DEFINER = 'root'@'localhost'
  PROCEDURE classicmodels.get_all_offices()
  BEGIN
  SELECT * FROM offices;
  END
2. CREATE DEFINER = 'root'@'localhost'
  PROCEDURE classicmodels.get_office_by_id(IN p_id INT)
  BEGIN
  SELECT * FROM offices WHERE officeCode=p_id;
  END
3. CREATE DEFINER = 'root'@'localhost'
  PROCEDURE classicmodels.get_office_ids()
  BEGIN
  SELECT officeCode FROM offices;
  END
4. CREATE DEFINER = 'root'@'localhost'
  PROCEDURE classicmodels.create_office(IN p_id INT, IN p_city VARCHAR(50),
  IN p_phone VARCHAR(50), IN p_address_line1 VARCHAR(50),
  IN p_address_line2 VARCHAR(50), IN p_state VARCHAR(50),
  IN p_country VARCHAR(50), IN p_postal_code VARCHAR(15),
  IN p_territory VARCHAR(10))
  BEGIN
  INSERT INTO offices values(p_id, p_city, p_phone, p_address_line1, p_address_line2,
  p_state, p_country, p_postal_code, p_territory);
  END
5. CREATE DEFINER = 'root'@'localhost'
  PROCEDURE classicmodels.update_office(IN p_id INT, IN p_city VARCHAR(50),
  IN p_phone VARCHAR(50), IN p_address_line1 VARCHAR(50),
  IN p_address_line2 VARCHAR(50), IN p_state VARCHAR(50),
  IN p_country VARCHAR(50), IN p_postal_code VARCHAR(15),
  IN p_territory VARCHAR(10))
  BEGIN
  UPDATE offices SET city=p_city, phone=p_phone, addressLine1=p_address_line1,
  addressLine2=p_address_line2, state=p_state, country=p_country, postalCode=p_postal_code,
  territory=p_territory WHERE officecode=p_id;
  END
6. CREATE DEFINER = 'root'@'localhost'
  PROCEDURE classicmodels.delete_office(IN p_id INT)
  BEGIN
  DELETE FROM offices WHERE officecode = p_id;
  END

Walkthrough

Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e MySQLStoredProceduresBCSMetaManDemo and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way
Click the “Add Connection” button to show the “Connection Dialog”
Select “ODBC Server” as Data Source type, enter MySQL ODBC connection string (for example it can be “Driver={MySQL ODBC 5.1 Driver};Server=taurus;Port=3306;Database=classicmodels; User=root;Password=12345;Trusted_Connection=yes;”), enter “`” as left and right delimiters and “?” as parameter symbol and click “Connect” button
The Data Source Explorer will now be populated with your MySQL data
Drag and drop your Finder Stored Procedure onto the design surface and enter External Content type name, i.e “Office”, click “OK”
Choose the Finder as the method type and click “OK”
The External Content Type will be created on the Diagram
In order to set an identifier right-click on the External Content Type, select “Manage External Content type” menu item
Click “Edit Identifiers” button on the “Entity Management” dialog, select an identifier and click “OK” and “Update” button then
The External Content Type will update with the new identifier
From the “BCS Meta Man Data Source Explorer” drag and drop your Specific Finder stored procedure onto the existing External Content Type
The “Stored Procedure Configuration” dialog will be displayed. Select “SpecificFinder” as the Method Type. Select the first Parameter – p_id and set the Filter Type as Comparison and the Default Value as ‘0’. Click “OK”.
The External Content Type will update with the new Specific Finder Method
Next drag and drop the Id Enumerator stored procedure onto the existing External Content Type
Select “IdEnumerator” as the Method Type, Click “OK”
The External Content Type will update with the new Id Enumerator Method
From the “BCS Meta Man Data Source Explorer” drag and drop your Creator stored procedure onto the existing External Content Type
The “Stored Procedure Configuration” dialog will be displayed. Select “Creator” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.
The External Content Type will update with the new Creator Method
Next drag and drop your Updater stored procedure onto the existing External Content Type
Again the “Stored Procedure Configuration” dialog will be displayed. Select “Updater” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.
The External Content Type will update with the new Updater Method
Finally drag and drop your Deleter stored procedure onto the existing External Content Type
Again the “Stored Procedure Configuration” dialog will be displayed. Select “Deleter” as the Method Type. Click “OK”.
The complete External Content Type will look like the following
Press F5 to deploy The External Content Type, this will load up your SharePoint Page once deployed
Add a new External list by selection on SharePoint page “Site Action”->“More Options…”->“External List”
Give your External List a name and Click on the icon to show the available External Content Types
Select created MySQLStoredProceduresBCSMetaManDemo.Office External Content Type, click “OK”. Click “Create” button then.
The external data provided by MySQL stored procedure is now displayed in the SharePoint External List
You can hover over the first column of your list and you’ll see a drop down appear. If you click the drop down arrow you’ll get a number of options for that particular row of data – one of which will be View Item.
You will be presented with the View Item dialog
Now click “Edit item” on the ribbon of the dialog. This option is only available if you create an Updater method for your External Content Type. Clicking on this link will open up a dialog form that allows you to edit that row of data and save it.
While viewing the external list, if you click the Items tab on the ribbon you’ll see a New Item button
This will bring up the “New Item” dialog where you can add a new Office. We will enter information about new Office and click “Save”
This will insert a new row directly to your MySQL database

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

BCS filters and DB2 Stored Procedures with Parameters in SharePoint 2010

Lightning Tools — Wed, 14 Sep 2011 20:11:45 GMT

Introduction

In this tutorial we will show you how to connect to a DB2 Database using BCS Meta Man and a Stored Procedure to return filtered data to a SharePoint Business Data List. In this walkthrough we will be creating a Finder Method with filters so our DB2 Stored Procedure needs to have parameters defined.

Prerequisites

Microsoft SharePoint Server 2010
Microsoft Visual Studio 2010 Professional or higher
BCS Meta Man
Download and install “IBM Data Server Runtime Client (Windows 32-bit AMD and Intel x86)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where BCS Meta Man runs
Download and install “IBM Data Server Runtime Client (Windows AMD64 and Intel EM64T)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where SharePoint 2010 runs
DB2 stored procedure such as:
1. CREATE PROCEDURE GET_EMPLOYEES_FILTERED(IN P_FIRST_NAME VARCHAR(12), IN P_BONUS DECIMAL(9,2))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_employees CURSOR WITH RETURN for
  select * from EMPLOYEE where FIRSTNME like '%' || P_FIRST_NAME || '%' and BONUS > P_BONUS;
  OPEN v_employees;
  END P1

Walkthrough

Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e BCSMetaManFilteredDB2StoredProcedure and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way
Click the “Add Connection” button to show the “Connection Dialog”
Select “ODBC Server” as Data Source type, enter DB2 ODBC connection string (for example it can be “Driver={IBM DB2 ODBC DRIVER};Database=SAMPLE; Hostname=cygnus;port=50000;Protocol=TCPIP;Uid=Administrator;Pwd=12345;Trusted_Connection=false;”), enter double quotes as left and right delimiters and “?” as parameter symbol and click “Connect” button.
The Data Source Explorer will now be populated with your DB2 data
Drag and drop your Finder Stored Procedure with parameters onto the design surface and enter External Content type name, i.e “FilteredEmployee”, click “OK”
Select the Method Type as “Finder’'
Select the first Parameter (p_first_name) and set it’s Filter Type to Comparison and the Default Value to ‘A’
Next select the second Parameter (p_bonus) and also set it’s Filter Type to Comparison and the Default Value to ‘100’
Click ‘OK’
The External Content Type will be created on the Diagram
In order to set an identifier right-click on the External Content Type, select “Manage External Content type” menu item
Click “Edit Identifiers” button on the “Entity Management” dialog, select an identifier and click “OK” and “Update” button then
The External Content Type will update with the new identifier
Press F5 to deploy, this will load up your SharePoint Page once deployed
Add a new Business Data List Web Part to your SharePoint page
Click on the ‘Open the tool pane’ link
Click on the icon to show the available External Content Types
Select our ‘BCSMetaManFilteredDB2StoredProcedure.FilteredEmployee’ External Content Type, click ‘OK’
Click ‘OK’ on the tool pane
Click the ‘Add’ link on the Business Data List Web Part to add the second Filter option
Change the second Filter to be the P_BONUS and enter values for the filters and click ‘Retrieve Data’
Your DB2 data provided by Stored Procedure is now displayed in the SharePoint Web Part

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

Business Connectivity Service Associations using DB2 Stored Procedures in SharePoint 2010

Lightning Tools — Tue, 13 Sep 2011 13:15:09 GMT

Introduction

In this walkthrough we will demonstrate you how to connect to DB2 Database and use stored procedures to create an association between two External Content Types using BCS Meta Man.

We are going to follow on from the ‘DB2 Stored Procedures and the Business Connectivity Services in SharePoint 2010’

Prerequisites

Microsoft SharePoint Server 2010
Microsoft Visual Studio 2010 Professional or higher
BCS Meta Man
Download and install “IBM Data Server Runtime Client (Windows 32-bit AMD and Intel x86)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where BCS Meta Man runs
Download and install “IBM Data Server Runtime Client (Windows AMD64 and Intel EM64T)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where SharePoint 2010 runs
DB2 stored procedures such as:
1. CREATE PROCEDURE GET_ALL_DEPARTMENTS()
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_departments CURSOR WITH RETURN for
  SELECT * FROM DEPARTMENT;
  OPEN v_departments;
  END P1
2. CREATE PROCEDURE GET_DEPARTMENT_BY_ID(IN P_ID CHAR(3))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_departments CURSOR WITH RETURN for
  select * from DEPARTMENT where DEPTNO=P_ID;
  OPEN v_departments;
  END P1
3. CREATE PROCEDURE GET_ALL_EMPLOYEES()
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_employees CURSOR WITH RETURN for
  SELECT * FROM EMPLOYEE;
  OPEN v_employees;
  END P1
4. CREATE PROCEDURE GET_EMPLOYEE_BY_ID(IN P_ID CHAR(6))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_employees CURSOR WITH RETURN for
  select * from EMPLOYEE where EMPNO=P_ID;
  OPEN v_employees;
  END P1
5. CREATE PROCEDURE GET_EMPLOYEE_BY_DEPARTMENT_ID(IN P_ID CHAR(3))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_employees CURSOR WITH RETURN for
  select * from EMPLOYEE where WORKDEPT=P_ID;
  OPEN v_employees;
  END P1

Walkthrough

Using the ‘DB2 Stored Procedures and the Business Connectivity Services in SharePoint 2010’ Tutorial create two External Content Types “Department” and “Employee” with Finder (Read List operation) and SpecificFinder (Read Item operation) methods only.
You should have the following External Content Types on your Diagram
Switch to the ‘Toolbox’ pane in Visual Studio (Ctrl-Alt-X)
Select the ‘Association’ toolbox item from the list
With the Association selected, Click and Drag from ‘Department’ to ‘Employee’
When you release the ‘Association Configuration’ dialog will show
Switch back to the ‘BCS Meta Man Data Source Explorer’ tool window pane
Drag the Association Stored Procedure onto the Arrow in the ‘Association Configuration’ dialog, it will turn green when OK to release
The dialog will update with the selected Stored Procedure
Select ‘P_ID’ in the Parameter and ‘DEPTNO’ in the Identifier drop-down list boxes, Click ‘Save’
The diagram will update to show an arrow indicating we have an association between the two External Content Types
Press F5 to deploy, this will load up your SharePoint Page once deployed
Add a new Business Data List Web Part to your SharePoint page
Click on the ‘Open the tool pane’ link
Click on the icon to show the available External Content Types
Select our ‘BCSMetaMan1.Department’ External Content Type, click OK
Click ‘OK’ on the tool pane
The external data provided by DB2 stored procedure is now displayed in the SharePoint Web Part
Next, Add a new ‘Business Data Related List’ Web Part to the page
Configure the Web Part to use the ‘BCSMetaMan1.Employee’ External Content Type
The Web Part will display a message saying the following
To do this we can set up the connection using the small arrow from the top right of our Web Part
Once this is done we can click on the arrows next to an item in our ‘Department’ web part and the employees related to this department will display on our ‘Employee’ web part

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

DB2 Stored Procedures and the Business Connectivity Services in SharePoint 2010

Lightning Tools — Fri, 09 Sep 2011 13:16:51 GMT

Introduction

In this walkthrough we will demonstrate how to integrate DB2 stored procedures with Microsoft SharePoint 2010 and display the data on SharePoint 2010 page. In order to do that we will describe how to create a new external content type by means of BCS Meta Man.

Prerequisites

Microsoft SharePoint Server 2010
Microsoft Visual Studio 2010 Professional or higher
BCS Meta Man
Download and install “IBM Data Server Runtime Client (Windows 32-bit AMD and Intel x86)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where BCS Meta Man runs
Download and install “IBM Data Server Runtime Client (Windows AMD64 and Intel EM64T)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where SharePoint 2010 runs
DB2 stored procedures for CRUD operations such as:
1. CREATE PROCEDURE GET_ALL_DEPARTMENTS()
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_departments CURSOR WITH RETURN for
  SELECT * FROM DEPARTMENT;
  OPEN v_departments;
  END P1
2. CREATE PROCEDURE GET_DEPARTMENT_BY_ID(IN P_ID CHAR(3))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_departments CURSOR WITH RETURN for
  select * from DEPARTMENT where DEPTNO=P_ID;
  OPEN v_departments;
  END P1
3. CREATE PROCEDURE GET_DEPARTMENT_IDS()
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  DECLARE v_department_ids CURSOR WITH RETURN for
  select DEPTNO from DEPARTMENT;
  OPEN v_department_ids;
  END P1
4. CREATE PROCEDURE CREATE_DEPARTMENT(IN P_ID CHAR(3), IN P_NAME VARCHAR(36), IN P_MGRNO CHAR(6), IN P_ADMID CHAR(3), IN P_LOCATION CHAR(16))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  insert into DEPARTMENT values(P_ID, P_NAME, P_MGRNO, P_ADMID, P_LOCATION);
  END P1
5. CREATE PROCEDURE UPDATE_DEPARTMENT(IN P_ID CHAR(3), IN P_NAME VARCHAR(36), IN P_MGRNO CHAR(6), IN P_ADMID CHAR(3), IN P_LOCATION CHAR(16))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  update DEPARTMENT set DEPTNAME=P_NAME, MGRNO=P_MGRNO, ADMRDEPT=P_ADMID, LOCATION=P_LOCATION where DEPTNO=P_ID;
  END P1
6. CREATE PROCEDURE DELETE_DEPARTMENT(IN P_ID CHAR(3))
  DYNAMIC RESULT SETS 1
  P1: BEGIN
  delete from DEPARTMENT where DEPTNO=P_ID;
  END P1

Walkthrough

Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e DB2StoredProceduresBCSMetaManDemo and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way
Click the “Add Connection” button to show the “Connection Dialog”
Select “ODBC Server” as Data Source type, enter DB2 ODBC connection string (for example it can be “Driver={IBM DB2 ODBC DRIVER};Database=SAMPLE;Hostname=cygnus;port=50000;Protocol=TCPIP;Uid=Administrator;Pwd=12345;Trusted_Connection=false;”), enter double quotes as left and right delimiters and “?” as parameter symbol and click “Connect” button.
The Data Source Explorer will now be populated with your DB2 data
Drag and drop your Finder Stored Procedure onto the design surface and enter External Content type name, i.e “Department”, click “OK”
Choose the Finder as the method type and click “OK”
The External Content Type will be created on the Diagram
In order to set an identifier right-click on the External Content Type, select “Manage External Content type” menu item
Click “Edit Identifiers” button on the “Entity Management” dialog, select an identifier and click “OK” and “Update” button then
The External Content Type will update with the new identifier
From the “BCS Meta Man Data Source Explorer” drag and drop your Specific Finder stored procedure onto the existing External Content Type
The “Stored Procedure Configuration” dialog will be displayed. Select “SpecificFinder” as the Method Type. Select the first Parameter – P_ID and set the Filter Type as Comparison and the Default Value as ‘0’. Click “OK”.
The External Content Type will update with the new Specific Finder Method
Next drag and drop the Id Enumerator stored procedure onto the existing External Content Type
Select “IdEnumerator” as the Method Type, Click “OK”
The External Content Type will update with the new Id Enumerator Method
From the “BCS Meta Man Data Source Explorer” drag and drop your Creator stored procedure onto the existing External Content Type
The “Stored Procedure Configuration” dialog will be displayed. Select “Creator” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.
The External Content Type will update with the new Creator Method
Next drag and drop your Updater stored procedure onto the existing External Content Type
Again the “Stored Procedure Configuration” dialog will be displayed. Select “Updater” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.
The External Content Type will update with the new Updater Method
Finally drag and drop your Deleter stored procedure onto the existing External Content Type
Again the “Stored Procedure Configuration” dialog will be displayed. Select “Deleter” as the Method Type. Click “OK”.
The complete External Content Type will look like the following
Press F5 to deploy The External Content Type, this will load up your SharePoint Page once deployed
Add a new External list by selection on SharePoint page “Site Action”->“More Options…”->“External List”
Give your External List a name and Click on the icon to show the available External Content Types
Select created DB2StoredProceduresBCSMetaManDemo.Department External Content Type, click “OK”. Click “Create” button then.
The external data provided by DB2 stored procedure is now displayed in the SharePoint External List
You can hover over the first column of your list and you’ll see a drop down appear. If you click the drop down arrow you’ll get a number of options for that particular row of data – one of which will be View Item.
You will be presented with the View Item dialog
Now click “Edit item” on the ribbon of the dialog. This option is only available if you create an Updater method for your External Content Type. Clicking on this link will open up a dialog form that allows you to edit that row of data and save it.
While viewing the external list, if you click the Items tab on the ribbon you’ll see a New Item button
This will bring up the “New Item” dialog where you can add a new Department. We will enter information about new Department and click “Save”
This will insert a new row directly to your DB2 database

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

BCS filters and Oracle Stored Procedures with Parameters in SharePoint 2010

Lightning Tools — Wed, 07 Sep 2011 13:35:51 GMT

Introduction

In this tutorial we will show you how to connect to a Oracle Database Server using BCS Meta Man and a Stored Procedure to return filtered data to a SharePoint Business Data List. In this walkthrough we will be creating a Finder Method with filters so our Oracle Stored Procedure needs to have parameters defined.

Prerequisites

Microsoft SharePoint Server 2010
Microsoft Visual Studio 2010 Professional or higher
BCS Meta Man
Oracle clients (This FAQ describes what should be installed)
Oracle stored procedure for such as:
1. create or replace function get_employees_filtered(p_last_name in varchar2, p_salary in number)
  return sys_refcursor
  is
  v_employees sys_refcursor;
  begin
  open v_employees for select * from employees where last_name like '%' || p_last_name || '%' and salary < p_salary;
  return v_employees;
  end get_employees_filtered;

Walkthrough

Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e BCSMetaManFilteredOracleStoredProcedure and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way
Click the “Add Connection” button to show the “Connection Dialog”
Select “ODBC Server” as Data Source type, enter Oracle ODBC connection string (for example it can be “Driver={Oracle in OraClient11g_home1};Dbq=XE_AURIGA;Uid=hr;Pwd=hr;Trusted_Connection=yes;”), enter double quotes as left and right delimiters and “?” as parameter symbol and click “Connect” button.
The Data Source Explorer will now be populated with your Oracle data
Drag and drop your Finder Stored Procedure with parameters onto the design surface and enter External Content type name, i.e “FilteredEmployee”, click “OK”
Select the Method Type as “Finder’'
Select the first Parameter (p_last_name) and set it’s Filter Type to Comparison and the Default Value to ‘a’
Next select the second Parameter (p_salary) and also set it’s Filter Type to Comparison and the Default Value to ‘4000’
Click ‘OK’
The External Content Type will be created on the Diagram
In order to set an identifier right-click on the External Content Type, select “Manage External Content type” menu item
Click “Edit Identifiers” button on the “Entity Management” dialog, select an identifier and click “OK” and “Update” button then
The External Content Type will update with the new identifier
Press F5 to deploy, this will load up your SharePoint Page once deployed
Add a new Business Data List Web Part to your SharePoint page
Click on the ‘Open the tool pane’ link
Click on the icon to show the available External Content Types
Select our ‘ BCSMetaManFilteredOracleStoredProcedure.FilteredEmployee’ External Content Type, click ‘OK’
Click ‘OK’ on the tool pane
Click the ‘Add’ link on the Business Data List Web Part to add the second Filter option
Change the second Filter to be the P_SALARY and enter values for the filters and click ‘Retrieve Data’
Your Oracle data provided by Stored Procedure is now displayed in the SharePoint Web Part

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

Business Connectivity Service Associations using Oracle Stored Procedures in SharePoint 2010

Lightning Tools — Tue, 06 Sep 2011 13:21:05 GMT

Introduction

In this walkthrough we will demonstrate you how to connect to Oracle Database Server and use stored procedures to create an association between two External Content Types using BCS Meta Man.

We are going to follow on from the ‘Oracle Stored Procedures and the Business Connectivity Services in SharePoint 2010’

Prerequisites

Microsoft SharePoint Server 2010
Microsoft Visual Studio 2010 Professional or higher
BCS Meta Man
Oracle clients (This FAQ describes what should be installed)
Oracle stored procedures for such as:
1. create or replace function get_all_countries
  return sys_refcursor
  is
  v_countries sys_refcursor;
  begin
  open v_countries for select * from countries;
  return v_countries;
  end get_all_countries;
2. create or replace function get_country_by_id(p_id in char)
  return sys_refcursor
  is
  v_countries sys_refcursor;
  begin
  open v_countries for select * from countries where country_id=p_id;
  return v_countries;
  end get_country_by_id;
3. create or replace function get_all_regions
  return sys_refcursor
  is
  v_regions sys_refcursor;
  begin
  open v_regions for select * from regions;
  return v_regions;
  end get_all_regions;
4. create or replace function get_region_by_id(p_id in number)
  return sys_refcursor
  is
  v_regions sys_refcursor;
  begin
  open v_regions for select * from regions where region_id=p_id;
  return v_regions;
  end get_region_by_id;
5. create or replace function get_countries_by_region_id(p_id in number)
  return sys_refcursor
  is
  v_countries sys_refcursor;
  begin
  open v_countries for select * from countries where region_id=p_id;
  return v_countries;
  end get_countries_by_region_id;

Walkthrough

Using the ‘Oracle Stored Procedures and the Business Connectivity Services in SharePoint 2010’ Tutorial create two External Content Types “Country” and “Region” with Finder (Read List operation) and SpecificFinder (Read Item operation) methods only.
You should have the following External Content Types on your Diagram
Switch to the ‘Toolbox’ pane in Visual Studio (Ctrl-Alt-X)
Select the ‘Association’ toolbox item from the list
With the Association selected, Click and Drag from ‘Region’ to ‘Country’
When you release the ‘Association Configuration’ dialog will show
Switch back to the ‘BCS Meta Man Data Source Explorer’ tool window pane
Drag the Association Stored Procedure onto the Arrow in the ‘Association Configuration’ dialog, it will turn green when OK to release
The dialog will update with the selected Stored Procedure
Select ‘P_ID’ in the Parameter and ‘REGION_ID’ in the Identifier drop-down list boxes, Click ‘Save’
The diagram will update to show an arrow indicating we have an association between the two External Content Types
Press F5 to deploy, this will load up your SharePoint Page once deployed
Add a new Business Data List Web Part to your SharePoint page
Click on the ‘Open the tool pane’ link
Click on the icon to show the available External Content Types
Select our ‘BCSMetaMan1.Region’ External Content Type, click OK
Click ‘OK’ on the tool pane
The external data provided by Oracle stored procedure is now displayed in the SharePoint Web Part
Next, Add a new ‘Business Data Related List’ Web Part to the page
Configure the Web Part to use the ‘BCSMetaMan1.Country’ External Content Type
The Web Part will display a message saying the following
To do this we can set up the connection using the small arrow from the top right of our Web Part
Once this is done we can click on the arrows next to an item in our ‘Region’ web part and the countries related to this region will display on our ‘Country’ web part

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

Oracle Stored Procedures and the Business Connectivity Services in SharePoint 2010

Lightning Tools — Mon, 05 Sep 2011 19:35:03 GMT

Introduction

In this walkthrough we will demonstrate how to integrate Oracle stored procedures with Microsoft SharePoint 2010 and display the data on SharePoint 2010 page. In order to do that we will describe how to create a new external content type by means of BCS Meta Man.

Prerequisites

Microsoft SharePoint Server 2010
Microsoft Visual Studio 2010 Professional or higher
BCS Meta Man
Oracle clients (This FAQ describes what should be installed)
Oracle stored procedures for CRUD operations such as:
1. create or replace function get_all_countries
  return sys_refcursor
  is
  v_countries sys_refcursor;
  begin
  open v_countries for select * from countries;
  return v_countries;
  end get_all_countries;
2. create or replace function get_country_by_id(p_id in char)
  return sys_refcursor
  is
  v_countries sys_refcursor;
  begin
  open v_countries for select * from countries where country_id=p_id;
  return v_countries;
  end get_country_by_id;
3. create or replace function get_country_ids
  return sys_refcursor
  is
  v_country_ids sys_refcursor;
  begin
  open v_country_ids for select country_id from countries;
  return v_country_ids;
  end get_country_ids;
4. create or replace procedure create_country(p_id in char, p_name in varchar2, p_region in number)
  is
  begin
  insert into countries values(p_id, p_name, p_region);
  end create_country;
5. create or replace procedure update_country(p_id in char, p_name in varchar2, p_region in number)
  is
  begin
  update countries set country_name=p_name, region_id=p_region where country_id=p_id;
  end update_country;
6. create or replace procedure delete_country(p_id in char)
  is
  begin
  delete from countries where country_id=p_id;
  end delete_country;

Walkthrough

Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e OracleStoredProceduresBCSMetaManDemo and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way
Click the “Add Connection” button to show the “Connection Dialog”
Select “ODBC Server” as Data Source type, enter Oracle ODBC connection string (for example it can be “Driver={Oracle in OraClient11g_home1};Dbq=XE_TAURUS;Uid=hr;Pwd=hr;Trusted_Connection=yes;”), enter double quotes as left and right delimiters and “?” as parameter symbol and click “Connect” button.
The Data Source Explorer will now be populated with your Oracle data
Drag and drop your Finder Stored Procedure onto the design surface and enter External Content type name, i.e “Country”, click “OK”
Choose the Finder as the method type and click “OK”
The External Content Type will be created on the Diagram
In order to set an identifier right-click on the External Content Type, select “Manage External Content type” menu item
Click “Edit Identifiers” button on the “Entity Management” dialog, select an identifier and click “OK” and “Update” button then
The External Content Type will update with the new identifier
From the “BCS Meta Man Data Source Explorer” drag and drop your Specific Finder stored procedure onto the existing External Content Type
The “Stored Procedure Configuration” dialog will be displayed. Select “SpecificFinder” as the Method Type. Select the first Parameter – P_ID and set the Filter Type as Comparison and the Default Value as ‘0’. Click “OK”.
The External Content Type will update with the new Specific Finder Method
Next drag and drop the Id Enumerator stored procedure onto the existing External Content Type
Select “IdEnumerator” as the Method Type, Click “OK”
The External Content Type will update with the new Id Enumerator Method
From the “BCS Meta Man Data Source Explorer” drag and drop your Creator stored procedure onto the existing External Content Type
The “Stored Procedure Configuration” dialog will be displayed. Select “Creator” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.
The External Content Type will update with the new Creator Method
Next drag and drop your Updater stored procedure onto the existing External Content Type
Again the “Stored Procedure Configuration” dialog will be displayed. Select “Updater” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.
The External Content Type will update with the new Updater Method
Finally drag and drop your Deleter stored procedure onto the existing External Content Type
Again the “Stored Procedure Configuration” dialog will be displayed. Select “Deleter” as the Method Type. Click “OK”.
The complete External Content Type will look like the following
Press F5 to deploy The External Content Type, this will load up your SharePoint Page once deployed
Add a new External list by selection on SharePoint page “Site Action”->“More Options…”->“External List”
Give your External List a name and Click on the icon to show the available External Content Types
Select created OracleStoredProceduresBCSMetaManDemo.Country External Content Type, click “OK”. Click “Create” button then.
The external data provided by Oracle stored procedure is now displayed in the SharePoint External List
You can hover over the first column of your list and you’ll see a drop down appear. If you click the drop down arrow you’ll get a number of options for that particular row of data – one of which will be View Item.
You will be presented with the View Item dialog
Now click “Edit item” on the ribbon of the dialog. This option is only available if you create an Updater method for your External Content Type. Clicking on this link will open up a dialog form that allows you to edit that row of data and save it.
While viewing the external list, if you click the Items tab on the ribbon you’ll see a New Item button
This will bring up the “New Item” dialog where you can add a new Country. We will enter information about new Country and click “Save”
This will insert a new row directly to your Oracle database

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

partner company Combined knowledge offer end user adoption tool

Lightning Tools — Thu, 01 Sep 2011 10:09:22 GMT

Combined Knowledge Launches SharePoint 2010 End User Adoption Product

Combined Knowledge, one of the World’s leading SharePoint Training Providers has launched a new SharePoint 2010 End User Adoption Product, which provides organizations with the ability to support their end users following the deployment of SharePoint 2010.

Our SharePoint End User Adoption Product provides companies with a global training and central support solution enabling users to have access to Over 1000 SharePoint Support actions for 2007 or 2010 , Just in Time Training , Task Based Support, Task Based Training, User Support Training, User Support System, SharePoint Helpdesk, End User Helpdesk, Online Support Modules, Online Training Materials, Virtual Training, ‘Show me’ Topics, Over 360 ‘How to’ Videos, and Quick Reference ‘Step by Step’ Guides

The solution can also be extended to support users of Office 2007 or 2010 and Windows 7, along with additional applications such as Lync and OneNote providing users with a central support on demand solution.

This web based product is easily deployed and also provides organizations with the ability to customize and enhance the content to suit their own environments and corporate branding.

Combined Knowledge, Headquartered in the UK, has been specialising in the development and delivery of SharePoint Training, Education and Adoption since 2001.

Combined Knowledge’s key aim in developing the business was to not only offer training classes but to provide organisations with a solution for their SharePoint deployment, from the product installation through to development, customisation and then ensuring successful user adoption.
Combined Knowledge extended their deliverables beyond the extensive training portfolio of classes offered to also incorporate The End User Adoption Solution Product as we understood that for a SharePoint implementation to be successful, the organisations user community had to understand and adopt the product.

Full product demo’s and trials are available on request visit the Combined Knowledge website for further information: www.combined-knowledge.com or email: sales@combined-knowledge.com

http://www.youtube.com/watch?v=OIlO2RBm8g0&feature=related

BCS Meta Man - Using a Connection String stored in the Secure Store

Lightning Tools — Wed, 24 Aug 2011 15:32:00 GMT

In this blog post I will show you how you can update your code to use a connection string in the Secure Store rather than having the value hard coded in your BCS Meta Man generated c# file. The reason why you may wish to put the connection details within the Secure Store is that the names of the database and server are likely to be different in my development environment than in my staging and production environments. In order to do this you will need to have the use of the secure store which is only available in SharePoint Server 2010 and not SharePoint Foundations.

To be able to use the details stored within the secure store you will be required to do some manual changes to the code generated by BCS Meta Man.

First of all create a new Secure Store application with the Connection String Field

Central Administration > Service Applications > Secure Store > New

Using the ‘Add Field’ button add a generic field called ‘Connection String’ and remove the other two fields

Click next and then add the Secure Store Administrator on the next screen. Now the Secure Store ID has been created you can click on it and choose to set the credentials, this is where you supply all of the credentials required.

Enter the connection string and the Credential Owner, Click OK

In BCS Meta Man, create a new BCS Meta Man Project and when connecting to your Data Source choose ‘Use Secure Store’ as your Authentication Mode.

You will be prompted for your credentials again, this is to allow BCS Meta Man to connect to the External System. Next create the External Content Type and choose .Net Assembly connector as the Model Type, generate the External Content Type in the usual way.

At this point the Model and assembly will be configured to the use Secure Store credentials for the User Name and Password but not for the Server and Database, Deploy the model to ensure the Secure Store details are correct. If it is working and you can see your External Data then it’s time to configure the code to retrieve the Server and Database, if not then check back to the Secure Store and make sure the credentials you entered have the permissions to the External System you are using.

Locate the BCSMetaMan.cs class and find the following method which retrieves the credentials from the Secure Store

internal Dictionary<string, string> ReadCredentialsFromSecureStore()
{
    // error checking removed for brevity
    string targetId = LobSystemInstance.GetProperties()["SsoApplicationId"] as string;
    ISecureStoreProvider provider = GetSecureStoreProvider();

    var userCredentials = new Dictionary<string, string>(2);

    // get the credentials for the user on whose behalf the code 
    // is executing 
    using (SecureStoreCredentialCollection credentials = provider.GetRestrictedCredentials(targetId))
    {

        // look for username and password in credentials 
        foreach (ISecureStoreCredential credential in credentials)
        {
            switch (credential.CredentialType)
            {
                case SecureStoreCredentialType.UserName:
                case SecureStoreCredentialType.WindowsUserName:
                    userCredentials.Add("UserID", GetStringFromSecureString(credential.Credential));
                    break;
                case SecureStoreCredentialType.Password:
                case SecureStoreCredentialType.WindowsPassword:
                    userCredentials.Add("Password", GetStringFromSecureString(credential.Credential));
                    break;
                default:
                    break;
            }
        }
    }

    return userCredentials;
}

You should be able to see that by default only the Username and Password are retrieved, replace the method with the following to pick up the Generic Field that we are using for the Connection String

internal Dictionary<string, string> ReadCredentialsFromSecureStore()
{
    // error checking removed for brevity
    string targetId = LobSystemInstance.GetProperties()["SsoApplicationId"] as string;
    ISecureStoreProvider provider = GetSecureStoreProvider();

    var userCredentials = new Dictionary<string, string>(2);

    // get the credentials for the user on whose behalf the code 
    // is executing 
    using (SecureStoreCredentialCollection credentials = provider.GetRestrictedCredentials(targetId))
    {

        // look for username and password in credentials 
        foreach (ISecureStoreCredential credential in credentials)
        {
            switch (credential.CredentialType)
            {
                case SecureStoreCredentialType.UserName:
                case SecureStoreCredentialType.WindowsUserName:
                    userCredentials.Add("UserID", GetStringFromSecureString(credential.Credential));
                    break;
                case SecureStoreCredentialType.Password:
                case SecureStoreCredentialType.WindowsPassword:
                    userCredentials.Add("Password", GetStringFromSecureString(credential.Credential));
                    break;
                case SecureStoreCredentialType.Generic:
                    userCredentials.Add("ConnectionString", GetStringFromSecureString(credential.Credential));
                    break;
            }
        }
    }

    return userCredentials;
}

Next switch to the *EntityService.cs class for your External Content Type i.e if your ECT was called Product the filename will be ProductEntityServiceClass.cs

Update the following method

public string GetConnectionStringWithCredentials()
{
    Dictionary<string, string> credentialsFromSecureStore = ReadCredentialsFromSecureStore();
    var connectionStringBuilder = new SqlConnectionStringBuilder(ConnectionString) { UserID = credentialsFromSecureStore["UserID"], Password = credentialsFromSecureStore["Password"] };
    return connectionStringBuilder.ToString();
}

with the following, this method now just returns the connection string

public string GetConnectionStringWithCredentials()
{
    return ReadCredentialsFromSecureStore()["ConnectionString"];
}

Now deploy the solution and it will be using the connection string from the Central Administration secure store, one thing which is worth noting is that when you go to change the credentials again you will not be shown what they were previously. You also need to make sure that the same secure store setup is used in each of your environments.

So there you have it not only can you use BCS Meta man to generate your c# code and configure the model with a few simple changes you can make the connection string configurable via the secure store.

Going to SharePoint Saturday “The Conference”?

Lightning Tools — Mon, 08 Aug 2011 22:37:50 GMT

Lightning Tools is exhibiting at SharePoint Saturday “The Conference” in Washington DC – Thursday 11th – Saturday 12th August. If you happen to be going to the conference, make sure you stop by Lightning Tools to say hello to the team and to see our latest developments of our products. Its been a busy year, and we have a lot to show you!

Brett Lonsdale from Lightning Tools will be demonstrating how Business Connectivity Services can help turn SharePoint into your central information hub for your organization allowing all stakeholders in the business to access external data. Brett’s session is on Friday 12th August at 1:30pm.

Also at the Lightning Tools booth you will have you chance to meet Steve Fox and Jennifer Mason and receive a signed copy of their latest books.

Finally, if you struggle with SharePoint permission management in SharePoint. Brett will be demonstrating in our sponsorship session how DeliverPoint resolves many issues for permission management. Come and see how we can help you take away all of your permission management problems and put you back in control.