Binding an ASP.NET dropdown list to a SharePoint list

Quick code snippet to populate an ASP.NET dropdown list from a SharePoint list. Also note that there is no need to use a foreach loop to iterate through the items collection, adding the items to the list blablabla. I simply create an SPList instance, retrieve the data from the appropriate list and then tie those two bastards down. That's how I do stuff, yo.

using (SPWeb web = SPContext.Current.Site.OpenWeb())
            {
                SPList list = web.Lists["TestConnectionList"];
                ddlConnections.DataSource = list.Items;
                ddlConnections.DataValueField = "Title";
                ddlConnections.DataTextField = "Title";
                ddlConnections.DataBind();
            }

In this case, the name of the resource SharePoint list is called 'TestConnectionList' and the name of the dropdown list 'ddlConnections'. Make sure that you also specify the exact same column names in the list in the DataValueField and DataTextField or otherwise you'll get an exception thrown at you. As you can see, I am also using a scope so that the SPWeb object gets destroyed after usage. This is because I am explicitly calling the site.OpenWeb();

Serialization & deserialization in C#

Hi guys, Been pretty busy lately with developing a web part that allows our data specialists to deploy workflow data feeds from test environments to (pre)production environments. Not going into details, but I would like to show you a simple example on how to (de)serialize objects so these can be passed on to WCF services / ASP.NET web services. Consider the following class:

    [Serializable]
    public class MetalBand
    {
        private string _bandName;
        private int _intMembers;
        private string _genre;

        public MetalBand()
        {
        }

        public string BandName
        {
            get { return _bandName; }
            set { _bandName = value; }
        }

        public int NumberOfMembers
        {
            get { return _intMembers; }
            set { _intMembers = value; }
        }

        public string Genre
        {
            get { return _genre; }
            set { _genre = value; }
        }

    }

Suppose you’ve got an instance of this object like so:

            MetalBand myBandObject = new MetalBand();
            myBandObject.BandName = "Skeletonwitch";
            myBandObject.Genre = "Blackened Thrash";
            myBandObject.NumberOfMembers = 5;

And you would need to pass this data on to a web service, you would need to serialize it first. This means that you convert an object state into a format which can be transported and/or stored. Here is how to:

            XmlSerializer serializer = new XmlSerializer(typeof(MetalBand));
            XmlWriterSettings writerSettings = new XmlWriterSettings();
            writerSettings.OmitXmlDeclaration = true;
            StringWriter stringWriter = new StringWriter();
            using (XmlWriter xmlWriter = XmlWriter.Create(stringWriter, writerSettings))
            {
                serializer.Serialize(xmlWriter, myBandObject);
            }

            
            XmlDocument doc = new XmlDocument();

            // here is your serialized object
            doc.LoadXml(stringWriter.ToString());

The XmlSerializer class will do the actual magic of conversion, the MetalBand object type is passed on to the constructor of the class The instance of the XLMWriterSettings holds a supporting set of features on the XmlWriter object, which is created by the XmlWriter.Create. With the OmitXmlDeclaration property set to true, we state that we do not want to write an XML declaration in our document. We are then serializing the object instance into the XmlWriter stream with the chosen Xml declaration settings. Then we simply create a new instance of an XmlDocument and blast the xml stream right into it. As for deserialization, you can instantiate another object of XmlSerializer or use the same one created earlier. Then use the XmlNodeReader, which is also a stream reader, to stream that puppy into the Deserialize method.

            XmlSerializer xSerializer = new XmlSerializer(typeof(MetalBand));
            XmlNodeReader reader = new XmlNodeReader(doc);
            MetalBand mySecondBandObject = (MetalBand)xSerializer.Deserialize(reader);

That is all there is to it

Setting up Business Connectivity Services in SharePoint 2010

The Business Connectivity Services (BCS) are a set of services which are used to connect to external data such as a SQL Server database. In combination with SharePoint designer, it is fairly easy to create external content types and lists (creating it in Visual Studio is also a possibility but more difficult to do). Personally, I had a little bit of difficulty setting it up the whole thing. The first thing I tried to do was to create an external content type for an arbitrary table in SQL Server, but I got the following error in SharePoint Designer: "The Business Data Connectivity Metadata Store is currently unavailable."

Other SP peers seemed to have the same problem and many could solve it by installing a WCF hotfix (KB976462), however this did not work for me. I will show you three checks I did in order to have stuff working. 1. Check if the Business Data Connectivity Service is running. You can find it by going to "Central Administration" -> "Manage services on server". If it has stopped, start it again.

2. Make sure you've got a Business Data Connectivity Service Application set up in "Application Management" ->"Service Applications"->"Manage service applications"

3. The next one is what I overlooked and made me waste hours of my life. Go to "Application Management"->"Service Applications"->"Configure service applications associations". In here you need to check whether your BCS Application Proxy is checked, otherwise it won't work.

Appliance of Strategy Design Pattern

Here is a small example of how I used the Strategy Design Pattern to re-use specific methods

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

// Appliance of the Strategy Pattern

namespace ConsoleApplication5
{
        class Program
        {
            static void Main(string[] args)
            {
                Dude Earl = new Dude("Albert");
                Dude Gary = new Dude("Gary");
            
                Earl.SetMorningRitual(new CigaretteSmoker());
                Gary.SetMorningRitual(new CoffeeDrinker());

                Earl.WakeUp();
                Gary.WakeUp();
            
                // During the day Earl decides to give up smoking and start drinking coffee in the morning instead.

                Earl.SetMorningRitual(new CoffeeDrinker());
                Earl.WakeUp();
                Console.ReadKey();

            }
        }

        class Dude
        {
            IMorningRitual _behaviour;
            String _name;

            public Dude(String MyName)
            {
                _name = MyName;
            }

            public void SetMorningRitual(IMorningRitual Behaviour)
            {
                _behaviour = Behaviour;
            }

            public void WakeUp()
            {
                Console.WriteLine(this._name + " just woke up");
                _behaviour.DoMorningRitual();
            }
        }

        public interface IMorningRitual
        {
            void DoMorningRitual();
        }

        public class CigaretteSmoker : IMorningRitual
        {
            public void DoMorningRitual()
            {
                Console.WriteLine("Still sleepy but gonna smoke one anyway.. *lights cigarette\n");
            }
        }

        public class CoffeeDrinker : IMorningRitual
        {
            public void DoMorningRitual()
            {
                Console.WriteLine("A morning without coffee is like something without something. Aahhh delicious!\n");
            }
        }

}

Multiple recs merged into one with multiple columns

Hi, Long time since i posted something. I found a nice solution to pivot multiple rows to one single row with multiple columns. Suppose you have a dataset as follows:

and the desired end result should be like::

then use this script i created:

   1:  /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

   2:  *  User         : Gary

   3:  *  Date         : 20110616

   4:  *  Description  : Pivoting multiple rows into one row with multiple columns

   5:  *

   6:  * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

   7:   

   8:  --// Create example table with the usual All-American names

   9:   

  10:      CREATE TABLE #PivotRows (

  11:          FirstName            VARCHAR(10)

  12:      ,    LastName                VARCHAR(15)

  13:      ,    ModelOfInterest    VARCHAR(15)

  14:      )

  15:   

  16:   

  17:      INSERT INTO

  18:          #PivotRows

  19:      SELECT    'John','Cunningham','CORSA'        UNION ALL 

  20:      SELECT    'John','Cunningham','ASTRA'        UNION ALL

  21:      SELECT    'John','Cunningham','ANTARA'        UNION ALL

  22:      SELECT    'Mick','Clarkson'  ,'SIGNUM'        UNION ALL

  23:      SELECT    'Mick','Clarkson'  ,'AGILA'        UNION ALL

  24:      SELECT    'Robert','Flanigan','INSIGNIA'

  25:   

  26:   

  27:   

  28:      SELECT * FROM #PivotRows

  29:   

  30:   

  31:  --//  Shake and serve

  32:   

  33:      SELECT

  34:          FirstName

  35:      ,    LastName

  36:      ,    MAX(CASE        WHEN rowid=1    THEN    [ModelOfInterest]        ELSE    NULL    END) AS ModelOfInterest1

  37:      ,    MAX(CASE        WHEN rowid=2    THEN    [ModelOfInterest]        ELSE    NULL    END) AS ModelOfInterest2

  38:      ,    MAX(CASE        WHEN rowid=3    THEN    [ModelOfInterest]        ELSE    NULL    END) AS ModelOfInterest3

  39:      ,    MAX(CASE        WHEN rowid=4    THEN    [ModelOfInterest]        ELSE    NULL    END) AS ModelOfInterest4

  40:      FROM

  41:          (

  42:              SELECT    

  43:                  ROW_NUMBER()    OVER    (    PARTITION    BY        [FirstName],[LastName]    ORDER            BY        [FirstName],[LastName]    ) AS rowid

  44:              ,    *

  45:              FROM        #PivotRows

  46:          )    t1

  47:      GROUP BY 

  48:          FirstName

  49:      ,    LastName

VB function to invoke dialog box for file selectoin

Small piece of code but really handy for file selections. Important, make sure that the Microsoft Office xx.x Object Library is checked, otherwise the Dialog object will not be recognized:

Function FindTxtFile(strFNme As String, strSearchPath As String, strTitle As String) As String
 Dim myDialog As FileDialog
 Dim vrtSelectedItem As Variant
Set myDialog = Application.FileDialog(msoFileDialogOpen)
With myDialog
    .AllowMultiSelect = False
    .Filters.Add "Text", "*.txt", 1
    .Title = strTitle
'    Debug.Print Right(strSearchPath, Len(strSearchPath) - 10)
    .InitialFileName = strSearchPath & strFNme
    If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
            FindTxtFile = Trim(vrtSelectedItem)
        Next vrtSelectedItem
    Else
    End If
   Set myDialog = Nothing
End With
End Function

A perfect C# code formatting tool for your posts

Good Lord n butter... I've finally found a website providing a solution for formatting C# syntax in this damn blog. I almost gave up posting stuff because I got bored to death formatting all this code by hand, blegh. Here is the link to the site. Don't forget to tick the 'embed stylesheet' if you want to use it, for say, BlogSpot.

Exporting data from Access table / query to a delimited textfile

This is a real simple one (quick and dirty, no exception handling) making use of an InputBox to set the filename and path and then exporting it to a text file. I created the following function in VBA

Public Function ExportToTxt()
 Dim strPath As String
 Dim strDate As String
 Dim strFullPath As String

 ' User sets folder location    
 strPath = InputBox("Give path", "Export to", "J:\Outgoing\YYYYMMDD") & "\"


 ' User sets extract date    
 strDate = InputBox("Give date in YYYYMMDD", "Export to", "YYYYMMDD")

 ' Set filename including path in string, to be used with the Transfer function
 strFullPath = strPath & "\" & strDate
       
       
 ' Export results to a delimited textfile,
 '  - using specifications specifications named "BrochSpecs" and "TDSpecs"
 '  - from table/query "03 Export BR Extract" and "04 Export TD Extract"
 '  - to filename and location set in strFullpath, postfixed by "_BR_Extract.txt"   

 DoCmd.TransferText acExportDelim, "BrochSpecs", "03 Export BR Extract", strFullPath & "_BR_Extract.txt", True
 DoCmd.TransferText acExportDelim, "TDSpecs", "04 Export TD Extract", strFullPath & "_TD_Extract.txt", True

End Function