Using LINQ to SQL with DotNetNuke

This uses this post from Benjamin Hermann as an example.

First, Install ASP.NET 3.5 and Visual Studio 2008

Use Visual Studio 2008 to open DotNetNuke:

You will get a message like this:

Click Yes. This will add  the needed changes to the web.config to allow LINQ to SQL to run.

Create a module (see this tutorial for instructions on creating a simple DotNetNuke module).

Right-click on the module folder under App_Code and select Add New Item...

Select the LINQ to SQL Classes template, give the .dbml file a name and click Add.

When the .dbml file opens you can modify it's connection properties. Expand the Connection section and select SiteSqlServer from the dropdown. This instructors the module to use the database connection that the DotNetNuke site is running on.

Next, in the Server Explorer, right-click on Data Connections and click Add Connection.

Enter the connection information for the database and click OK. This is only for the designer. At run-time the module will use the connection string indicated in the earlier step.

You can now expand the Tables section of the Database connection you created and drag the Users table to the Object Relational Designer.

Under Properties, select UserDataContext from the drop-down.

Supply a Context Namespace and an Entity Namespace. Note, if  the .dbml file connection properties have changed when you created the Data Connection in the Server Explorer...

Change them back to SiteSqlServer (Web.config).

Now, in your module you can place a GridView on an .ascx control and use code like this to fill it with data:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

namespace DotNetNuke.Modules.Test
{
    public partial class View : DotNetNuke.Entities.Modules.PortalModuleBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            UsersDataContext mydata = new UsersDataContext();

            var results = from abc in mydata.Users select abc;
            GridView1.DataSource = results;
            GridView1.DataBind();
        }
    }
}

You can download the sample module here: Test_01.00.00_Install.zip

Note: In order to run this module on another DotNetNuke site, you need to modify the web.config:

Change: the <system.codedom> section to:

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

Change: the <assemblies> section to: