Using LINQ in WSS with VS 2008 Beta 2

In this article, I do a test in WSS about using linq to read database.

What you need to do my test:
1. VS 2008 Beta2
2. Windows Server 2003 with WSS 3.0 installed
3. Sql Server 2005 or Sql Server 2005 express with Northwind database intalled.

Now, let's begin. 

1. Configure the Web.config

First, you have to install .Net Framework 3.5. Then, you have to configure the web.config of your WSS web site. Just reference the file auto-generated by VS2008 Beta 2.

 Pay attention to that: the trust level must be changed to Full.

<trust level="Full"/> 

In the compilation elements, add following assemblies.

 

 

<compilation debug="false">
<assemblies>
        <add assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
        <add assembly="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Data.DataSetExtensions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
        <add assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        <add assembly="System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
</assemblies>
</compilation>

 Out of the <system.web> elements, add these:

 

<system.codedom>
      <compilers>
          <compiler language="c#;cs;csharp" extension=".cs" compilerOptions="/warnaserror-" warningLevel="4" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
              <providerOption name="CompilerVersion" value="v3.5"/>
             </compiler>
          <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" compilerOptions="/optioninfer+" type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
              <providerOption name="CompilerVersion" value="v3.5"/>
          </compiler>
      </compilers>
  </system.codedom>

 2. Creating a C# class library project. In the project setting page, signing this project.

 

3. Create a LINQ Northwind Context, reference the Scott Gu' blog about how to to it.

http://weblogs.asp.net/scottgu/archive/2007/05/29/linq-to-sql-part-2-defining-our-data-model-classes.aspx

4.Create a class which inherit from System.Web.UI.WebControls.WebParts.WebPart

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.WebControls;
using System.Xml.Serialization;

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;

namespace GlenCode
{
    public class HelloPart35:System.Web.UI.WebControls.WebParts.WebPart
    {
        Member Variables#region Member Variables
        protected GridView _gvProducts = null;
        protected Literal _litMsg = null;
        #endregion


        Overrides#region Overrides
        protected override void Render(HtmlTextWriter writer)
        {
            this._litMsg.RenderControl(writer);
            this._gvProducts.RenderControl(writer);           
        }

        protected override void CreateChildControls()
        {
            //Message 
            _litMsg = new Literal();
            _litMsg.Text = ""; //No Message yet

            // GridView
            this._gvProducts = new GridView();
            this._gvProducts.AutoGenerateColumns = false;
            this._gvProducts.BorderWidth = 0;
            this._gvProducts.ShowHeader = true;
            this._gvProducts.ShowFooter = false;
            this._gvProducts.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
            this._gvProducts.HorizontalAlign = HorizontalAlign.Left;
            this._gvProducts.DataKeyNames = new string[] { "ProductID" };
            this._gvProducts.Load += new EventHandler(gvProducts_Load);
            
            //this._gvProducts.PageIndexChanging += new GridViewPageEventHandler(gvProducts_PageIndexChanging);
            //this._gvProducts.Sorting += new GridViewSortEventHandler(gvProducts_Sorting);
            this._gvProducts.AlternatingRowStyle.BackColor = System.Drawing.Color.LemonChiffon;
            this._gvProducts.HeaderStyle.BackColor = System.Drawing.Color.Silver;
            this._gvProducts.AllowSorting = true;
            this._gvProducts.AllowPaging = true;
            this._gvProducts.PageSize = 10;

            BoundField bfProd = new BoundField();
            bfProd.DataField = "ProductName";
            bfProd.HeaderText = "Product";
            bfProd.SortExpression = "ProductName";
            this._gvProducts.Columns.Add(bfProd);

            BoundField bfCat = new BoundField();
            bfCat.DataField = "CategoryName";
            bfCat.HeaderText = "Category";
            bfCat.ReadOnly = true;
            bfCat.SortExpression = "CategoryName";
            this._gvProducts.Columns.Add(bfCat);

            BoundField bfSupplier = new BoundField();
            bfSupplier.DataField = "SupplierName";
            bfSupplier.HeaderText = "Supplier";
            bfSupplier.SortExpression = "SupplierName";
            bfSupplier.ReadOnly = true;
            this._gvProducts.Columns.Add(bfSupplier);

            BoundField bfUnitPrice = new BoundField();
            bfUnitPrice.DataField = "UnitPrice";
            bfUnitPrice.HeaderText = "Price";
            bfUnitPrice.SortExpression = "UnitPrice";
            bfUnitPrice.HtmlEncode = false;
            bfUnitPrice.DataFormatString = "{0:c}";
            bfUnitPrice.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
            this._gvProducts.Columns.Add(bfUnitPrice);

            CheckBoxField cbfDiscon = new CheckBoxField();
            cbfDiscon.DataField = "Discontinued";
            cbfDiscon.HeaderText = "Discontinued";
            cbfDiscon.SortExpression = "Discontinued";
            cbfDiscon.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            this._gvProducts.Columns.Add(cbfDiscon);

            this.Controls.Clear();
            this.Controls.Add(this._gvProducts);            
            this.Controls.Add(this._litMsg);


        }
        #endregion

        Delegates#region Delegates
        protected void gvProducts_Load(object sender, EventArgs e)
        {
            //Detect PostBack
            if (this._gvProducts.Rows.Count == 0)
            {
                BindGvProducts();
            }

        }

        #endregion


        Helper Methods#region Helper Methods

        /**//*
        void FillDataGrid()
        {
            this._gvProducts.DataSource = this._objDataSrcProducts;
            this._gvProducts.DataBind();
        }*/
        
        private void BindGvProducts()
        {
            NorthwindContextDataContext ndc = new NorthwindContextDataContext();

            var data = from p in ndc.Products
                       select new
                       {
                           ProductID = p.ProductID,
                           ProductName = p.ProductName,
                           CategoryName = p.Category.CategoryName,
                           SupplierName = p.Supplier.CompanyName,
                           UnitPrice = p.UnitPrice,
                           Discontinued = p.Discontinued
                       };
            this._gvProducts.DataSource = data;
            this._gvProducts.DataBind();
        }
        #endregion
    }
}

5. Build the Project. Put the dll into the bin of the WSS extended Web.

6.In the webpart gallary of the site collection, populate the webpart.

7.Add the webpart to one of your web page.