Taking a crash course in LINQ - Part 3 (LINQ to SharePoint)

Hi all, 

LINQ to SharePoint is in fact making a debut with release of SharePoint 2010. Other LINQ providers such as 

• LINQ to SQL
• LINQ to XML 
• LINQ to Entities are very mature.

Any developer working on .NET platform should understand the concept of LINQ which the strategic way to access data for different providers. 

Advantages: 

•  Developers need not work the CAML directly. LINQ provides a seamless way to access the SharePoint list data. Apparently, when a developer write the LINQ statements, LINQ translates into CAML query and fetch data from the list. 
•  A tool is provided by SharePoint 2010 team which points to a site and as we do that, that tool will give is a strongly typed queries. The tool name is SPMetal.exe and its a command line tool accepts site collection and .NET language(cs or vb). 
• This tool also does compile-time checking as well. 
• Visual Studio 2010 also provides a Intelligence that helps developer to construct query. An assembly has to used Microsoft.SharePoint.Linq.dll

Disadvantage: 

•   LINQ translates the LINQ queries into Collaborative Application Markup Language (CAML) queries thus adding an extra step for retrieving the items.

How LINQ to SharePoint works? 

 

Firstly, we need to create entity classes using SPMetal tool. The SPMetal is a command line tool with the following syntax: 

SPMetal /web:<site Url> /code:<.cs or .vb>

It has to noted that after semi-colon, there MUST be not have any kind of space otherwise it will throw an exception.Once the entity classes have been generated successfully we have to add this auto-generated file in our project.  

Secondly, we have to create a "DataContext" object. DataContext class allows to the list data. Therefore, we construct any query using DataContext object.

Thirdly, because we have in number of lists within a site collection, LINQ understands any kind of list relationship as well.   

Fourthly, its possible to modify the list data via SubmitChanges() method.

So, lets create a simple demo and we will: 

>> use SPMetal that will generate the entity classes

>> Add this class to a Visual Studio 2010 project. 

>> Write LINQ queries. 

Step1: We create a team site named: http://sp2010:4848/Lab05/ via SharePoint 2010 UI.

Step2: Navigate to Site Actions >> More Options ... >> Create a "Contacts" list as illustrated below:

Step3: Add a couple of entries in "Contacts" list.

Step4: Create a new Visual Studio 2010 project, File >> New >> Project >> Select "SharePoint 2010" and name the project: "LINQtoSharePoint"

Step5 :Right click on "LINQtoSharePoint" project Add >> New Item >> Visual Web Part and name it SPLinqWebPart

 

 

Step6: In this LINQtoSharePoint, we create a batch file for SPMetal and it be empty initially. 

Step7: Edit the SetUp file and supply the web and code as discussed above: 

SET SPMETAL="C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN\SPMETAL.EXE"

%SPMETAL% /web:http://sp2010:4848/Lab05 /namespace:SPNorthwind /code:SPNorthWind.cs

pause

A SPNorthWind.cs file be generated.  

Step8: Right click on "LINQtoSharePoint" project Add >> Existing Item >> select SPNorthWind.cs file so that its embedded in the project. 

Step9:  Edit the "SPLinqWebPartUserControl.ascx" and add the SPGridView: 

-------------------------------

 <SharePoint:SPGridView runat="server" AutoGenerateColumns="false" ID="spGridView">

<HeaderStyle HorizontalAlign="Left" ForeColor="Navy" Font-Bold="true" />


<Columns>
<SharePoint:SPBoundField DataField="FirstName" HeaderText="First Name">
</SharePoint:SPBoundField>

<SharePoint:SPBoundField DataField="Title" HeaderText="Last Name">
</SharePoint:SPBoundField>



</Columns>

</SharePoint:SPGridView>

------------------------------- 

Step10: Edit the "SPLinqWebPartUserControl.ascx.cs" and reference to Microsoft.SharePoint.Linq.dll 

(C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\Microsoft.SharePoint.Linq.dl)

Step11: Add using statements in SPLinqWebPartUserControl.ascx.cs

 //LINQ

using System.Linq;
using SPNorthwind;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Linq;

Step11:  Now, we query company name to be "Google" using LINQ syntax. It has be noted that we are not using CAML here. Then we bind the query to the SPGridView

protected void Page_Load(object sender, EventArgs e)
        {
            //Create an instance of SPNorthWindContext
            //SPNorthWindDataContext dataContext = new SPNorthWindDataContext(SPContext.Current.Web.Url);

            SPNorthWindDataContext dataContext = new SPNorthWindDataContext(SPContext.Current.Web.Url);

            //Query from Contact list via LINQ
            var query = from c in dataContext.Contacts
                        where c.Company == "Google"
                        select c;

            spGridView.DataSource = query;
            spGridView.DataBind();

        }

Step12: Build, Deploy. Navigate to the http://sp2010:4848/Lab05/SitePages/Home.aspx >> Edit >> Insert >> Web Part >> choose "Custom" >> SPLinqWebPart >> Add 

Note that this web part filters people who belong to "Google". 

Cheers, 

--aaroh 

Download the source here. 

References: 

Accessing SharePoint List Data

Using LINQ to SharePoint from within a Visual web part

 

Taking a crash course in LINQ - Part 2 (LINQ to DataSet)

Hi all,

We know that LINQ dramatically simplifies the data access layer with an array of other benefits which I discussed in the previous post. ADO.NET is a bridge between our objects in ASP.NET and our database. ADO.NET provides an object oriented view to the database, encapsulating many of the database properties and relational within ADO.NET objects.

ADO.NET architecture diagram comes with connected (SqlConnection, SqlCommand, SqlDataReader, SqlBulkCopy, SqlDataAdpater etc.) and disconnected architecture (DataSet, DataTable etc).In this blog post, we will talk about LINQ to DataSet which is a disconnected architecture. These ADO.NET dataset and datatable are quite sophisticated objects and data tables is an iterative process through collections of rows and columns. 

Advantages: 

These ADO.NET object are extremely powerful and in development context, there are quite easy to develop.

Disadvantages: 

 1. DataSet objects do not expose rich query capabilities. Therefore developers have had to live with DataSet's limited query mechanism such as simple string operations for sorting and filtering which lead to a lot of extra work and custom applications.    

2. Neither DataSet nor DataSet implements either IEnumerable<T> or IQueryable<T>.

LINQ to DataSet: (For Developers)

LINQ to DataSet lets us use the standard query operators and some operators that are specific to datasets and data tables. It treats data tables as enumerations of DataRow objects.It doesn’t care how data gets into a dataset.

a) Provides a consistent model for querying data irrespective of  different data sources.
b) Write .NET Framework code rather than of SQL when working with data.
c) Compile-time syntax against our data.   
d) Making programming data easier and more consistent
e) Intellisense

f) Unfortunately, the DataSet’s DataTable and the DataTable’s DataRowCollection do not implement IEnumerable<DataRow> so these types cannot be used as sources for LINQ query expressions. This means, we cannot query a DataSet in the same way we would query a database.  

AsEnumerable: 

LINQ leverages IEnumerable<T> sources.  .But there is a workaround and LINQ provides an extension method called "AsEnumerable"  and its added to DataTable type.

Field<T>: 

We can use the DataRow column values via the Filed<T> method when developing LINQ to Data Set query expressions. This facilitates the query expression less error prone and thereby provides much cleaner code.  

So, lets take a sample of LINQ to DataSet.Firstly, we will discuss typed DataSet and in second sample we will discuss Untyped DataSets. 

-------------------------------------------------------------------------------------

LINQ to DataSet (Typed) 

Step1: Create a new Visual Studio 2008/2010 project, File >> New >> Project >> Select "ASP.NET Web Application" and name the project: "LINQtoDataSet"

Step2: Right click on the "LINQtoDataSet" project >> Add >> New Item >> Select "Data" from installed templates >> Select the "DataSet" item and name it "Customers". We now have a Typed DataSet.

Step3: In the toolbar, go to "Tools" >> "Connect to Database" >> Add Connection >> Establish connection to "Northwind"

Step4: Go to "Server Explorer" >> Open the "Northwind" database >> Tables >> Drag the "Customers" table to DataSet Designer as illustrated below:  

Step5:  In the "TypedDataSet.aspx, go to ToolBox and drag/drop a GridView control on the designer interface.

Step6: In the "TypedDataSet.aspx", go to code view and in Page_Load method we bind the GridView to the Customer table via LINQ. We query the country (France) and in the GridView display customerId and company. 

Typed DataSet is immensely helpful as we can leverage Visual Studio 2010 IntelliSense and therefore no mistakes in respect of columns names.     

Sample code:

        protected void Page_Load(object sender, EventArgs e)
        {

            //Create an instance of the dataset
            Customers ds = new Customers();

            // connection string
            string connStr = @"Data Source=SP2010\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

            // create connection
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //Command text
                string commStr = "select * from customers";

                // create data adapter
                SqlDataAdapter da = new SqlDataAdapter(commStr,conn);

                // fill data table
                da.Fill(ds, "Customers");

                //query the dataset via LINQ
                var query = from c in ds._Customers
                            where c.Country == "France"
                            select new
                            {
                                c.CustomerID,
                                c.CompanyName
                            };

                //Bindthe data to GridView
                GridView1.DataSource = query;
                GridView1.DataBind();
            }

        }

Step7: The output: 

-------------------------------------------------------------------------------

LINQ to DataSet (UnTyped)

Step1: Create a new Visual Studio 2008/2010 project, File >> New >> Project >> Select "ASP.NET Web Application" and name the project: "LINQtoDataSet2"

Step2:  In the "DataSet.aspx, go to ToolBox and drag/drop a GridView control on the designer interface.

Step3: In the "DataSet.aspx", go to code view and in Page_Load method we bind the GridView to the Customer table via LINQ. We query the customer id, company name  and in the GridView display customerId and company. 

UnTyped DataSet can be error prone as if by mistake we misspelled a column, then we will get an error. Furthermore, we have to us DataTable.AsEnumerable method and this no intellisense as well. 

Sample code:

       protected void Page_Load(object sender, EventArgs e)
        {

            // connection string
            string connStr = @"Data Source=SP2010\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

            // create connection
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //Create dataset
                DataSet ds = new DataSet();

                //Command text
                string commStr = "select * from customers";

                //Create data adapter
                SqlDataAdapter da = new SqlDataAdapter(commStr, conn);

                // fill data table
                da.Fill(ds, "Customers");

                DataTable customer = ds.Tables["Customers"];

                //Query the DataTable via LINQ
                var query = from c in customer.AsEnumerable()
                            select new
                            {
                                CustomerId = c.Field<string>("customerid"),
                                Company = c.Field<string>("companyname")
                            } ;

                
                //Bindthe data to GridView          
                GridView1.DataSource = query;
                GridView1.DataBind();
            }
        }

Step7: The output: 

Cheers, 

--aaroh 

Download the source here

References : 

Introducing LINQ—Using LINQ to DataSet

Rich Query for DataSet- An Introduction to LINQ to DataSet

Query Expression Syntax Examples: Projection (LINQ to DataSet)

LINQ to Datasets 

Connecting to SharePoint 2007 Web Services

Hi All, 

SharePoint gives the flexibility to write custom web services for developers as well as an ensemble of out-of-the-box web services. Developing SharePoint custom application could involve programming aspects such as object model, web services, XML, etc. If we consider SharePoint's object model, we can do most of the programmatic access in SharePoint. Its far more robust and has an array of features that developer could leverage on. But object model lacks support for remote operations and applications. Whereas, Web Services part supports interoperability and developers could use this technique to communicate with non-SharePoint applications. As we know that Web Services is platform independent and could be developed in Java, ColdFusion, JSP besides .NET. 

Where Web Services resides in SharePoint 2007 and SharePoint 2010?

In SharePoint 2007 context, SharePoint web services are implemented as ASP.NET web services and could be found in 12 hive i.e. "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI". When a new SharePoint site is provisioned, a virtual directory named "_vti_bin". Therfore, if want to have all the list web services (lists.asmx) methods for a specific site then just type in:

http://sp2007:4848/_layouts/_vti_bin/lists.asmx

In SharePoint 2010 context, follows exactly same concepts. In 14 hive, we will find a new folder named "WebServices" that contains additional web services and WFC services.

   
Advantage of Web Services over Object Model in SharePoint 2007? 

There are specialized query that can be used ONLY using web services. Let's take a simple example.  

a) Querying list with multiple folders:
 
Lets create a document library called "IT System Checklists" that contains lot of folders within. This is full structure:

Shared Documents (Main document library)
---------Cars (sub-folder)
----------------------BMW (sub-sub-folders)
----------------------Mercedes Benz
----------------------Ferrari
----------------------Lamborghini
---------Scooters (sub-folder)
----------------------(Same structure )

What I have to search for each folder and do some kind of manipulations. If we use U2U builder,

>>Choose the Site URL: http://testserver/apac/Tools
>>Choose "Connect via Object Model"









>> Right click on the "Shared Documents". We have just option "Query". If we query this list "ID" and order by, there will be just 2 rows i.e. "Cars" and "Scooters". There is no folders like BMW, Mercedes Benz, Ferrari folders etc.











But, we can choose second option: "Connect via SharePoint Web Services"








>> Right click on "Shared Documents", select "GetListItems" and choose "Query Options" tab.
Here we can select any sub folder.













Besides, folders there are other queries which object model could not handle. 
b) Query with DataTime values
c) Query with Calendar lists

A very detailed and extremely useful article by Karine Bosch (from U2U).

SharePoint web services can be leveraged in two ways:
a) Out-of-the-box web services: Utilize and manipulate our code.
b) Custom Web Services: Write our own implementation.    

Now, lets get our hands dirty and write a Window Application that display all the lists in a specific site and when a specific list with its contents. If a list does not have any list items then we display a message to the user.

Step1:  Create a new Visual Studio 2008/2010 project, File >> New >> Project >> Select "Windows Forms Application" and name the project: "WINWebServices"

Step2: Drag a listbox control named "lstSPLists", two group boxes and a GridView as show illustrated below:

Step3: Right click on the "WINWebServices" project and under "References" folder select the "Add Service Reference"

Step4:In the "Add Service Reference" window, click on "Advanced..." button

Step5: Click on "Add Web Reference" button.

Step6: Type in the location of list web service i.e.

http://sp2010:4848/_layouts/_vti_bin/lists.asmx

. Configure the "Web reference name" as "ListService"and click on "Add Reference". When we click on this button a windows authentication will be required and pass the login credentials.   

Step7: Now, we are connected to list web service. We have to populate the "lstSPLists" and use list web service "GetListCollection" method which displays all the lists for a specific site. "GetListCollection" accepts a string and listBox accepts an object. We define a class named: "GetListCollectionItem":

public class GetListCollectionItem
        {
            private String title;

            public String Title
            {
                get { return title; }
                set { title = value; }
            }

            private Guid theGuid;

            public Guid TheGuid
            {
                get { return theGuid; }
                set { theGuid = value; }
            }

            public GetListCollectionItem(string disp, Guid id)
            {
                title = disp;
                theGuid = id;
            }

            public override string ToString()
            {
                return Title;
            }

  }

Step8: Go to designer, select the windows form and double click to get a "Form1_Load"and when a form is loaded then all the lists will be populated automatically in "lstSPLists" control.

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {

                // Create an instance of the Web service proxy class.
                ListService.Lists listWS = new ListService.Lists();
                listWS.Credentials = CredentialCache.DefaultCredentials;

                // 1) Call the List service "GetListCollection" method
                XmlNode node = listWS.GetListCollection();

                //Loop all the lists for this site and populate in the "lstSPList" control
                foreach (XmlNode n in node.ChildNodes)
                {
                    lstSPLists.Items.Add(new GetListCollectionItem(n.Attributes["Title"].Value, new Guid(n.Attributes["ID"].Value)));
                }
            }
            catch (WebException ex)
            {
                MessageBox.Show("Message: " + ex.Message + "InnerException: " + ex.InnerException);
            }
            catch (System.Web.Services.Protocols.SoapException ex)
            {
                MessageBox.Show("Message: " + ex.Message + "InnerException: " + ex.InnerException);
            }
        }

Build the project and press F5 to view the application. 

Step9: Next step is to double click on the "lstSPLists" event handler using GetListItems methid. We also bind the list items to the grid view. We also check if there is no entry in a list then we display a message to user. 

Its also possible to add new list item, update or delete items as well. I have shown only "Add new item" in this example but other methods are similar.

Cheers,
--Aroh

Download the source here

References:

1) Column with Empty Values not listed in Dataset from GetListItems

http://social.msdn.microsoft.com/Forums/en/sharepointdevelopment/thread/1f11fb91-ffac-4d53-8952-e43caf76f1b9

2) http://nickgrattan.wordpress.com/2007/07/05/starting-out-programming-sharepoint-services/

3) http://msdn.microsoft.com/en-us/library/bb847941%28office.12%29.aspx

4) http://www.sharepointmonitor.com/2007/01/sharepoint-web-service/

5) 3 options

http://www.theserverside.net/tt/blogs/showblog.tss?id=WSRetrieveData 

6) SharePoint 2010 ListData.svc WPF application

http://blogs.msdn.com/b/vsdata/archive/2009/11/26/integrate-sharepoint-list-data-in-your-wpf-application.aspx

7)Creating a SharePoint Visual Web Part using Visual Studio 2010

http://blogs.msdn.com/b/bethmassi/archive/2010/01/28/creating-a-sharepoint-visual-web-part-using-visual-studio-2010.aspx

8) http://www.infoq.com/articles/swanson-moss-web-services