michael pardo

Let’s say I have a table called “Contacts”. I’d like to search that table for the string “michael pardo”, however, I could pass in the string “pardo michael” instead. I’d like it to work either way, so here’s what I think the query should look like:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT * 
FROM Contacts
WHERE 
(
    FirstName LIKE '%michael%' 
    OR LastName LIKE '%michael%'
)
AND 
(
    FirstName LIKE '%pardo%' 
    OR LastName LIKE '%pardo%'
)

Okay, well how do I enclose those condition expressions with SubSonic? We can take advantage of the Expression syntax to make this work. Here’s the SubSonic code, broken up by query parts.

1
2
3
4
5
6
7
8
9
10
11
12

// SELECT * FROM Contacts
var q = new Select().From<Contact>();
 
// WHERE (FirstName LIKE '%michael%' OR LastName LIKE '%michael%')
q.WhereExpression(Contact.Columns.FirstName).Like("%michael%");
q.Or(Contact.Columns.LastName).Like("%michael%");
 
// AND (FirstName LIKE '%pardo%' OR LastName LIKE '%pardo%')
q.AndExpression(Contact.Columns.FirstName).Like("%pardo%");
q.Or(Contact.Columns.LastName).Like("%pardo%");
 
q.ExecuteTypedList<Contact>();

One problem I encountered with this method was with chaining. If I chain the “or” commands instead of calling them separately, SubSonic will short-change you on the constraints it passes in. Some sort of bug (or feature) maybe.

SubSonic requires you to put some stuff in the web.config. You may be wondering how you’re going to distribute your module without requiring the buyer to alter his web.config. Here’s the method that I use:

1
2
3
4
5
6
7
8
9
10
11
12

private static void InitializeProvider()
{
    DataService.Provider = new SqlDataProvider();
    DataService.Providers = new DataProviderCollection();
    DataProvider provider = DataService.Provider;
    var config = new NameValueCollection();
    config.Add("SubSonicSqlString", ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString);
    provider.Initialize("SubSonicSqlString", config);
    DataService.Provider.DefaultConnectionString = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString;
    DataService.Provider.GeneratedNamespace = "Example.DataModel";
    DataService.Providers.Add(provider);
}

This method goes into my Page_Init event. Luckily I setup my module pages to inherit a base class that does all my setup. Here’s the base class:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

using System;
using System.Collections.Specialized;
using System.Configuration;
using System.Web.UI.HtmlControls;
using DotNetNuke.Entities.Modules;
using SubSonic;
 
namespace Example.Common
{
    public class ModuleBase : PortalModuleBase
    {
        #region Events
 
        protected void Page_Init(Object sender, EventArgs e)
        {
            InitializeProvider();
        }
 
        #endregion
 
        #region Methods
 
        private static void InitializeProvider()
        {
            DataService.Provider = new SqlDataProvider();
            DataService.Providers = new DataProviderCollection();
            DataProvider provider = DataService.Provider;
            var config = new NameValueCollection();
            config.Add("SubSonicSqlString", ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString);
            provider.Initialize("SubSonicSqlString", config);
            DataService.Provider.DefaultConnectionString = ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString;
            DataService.Provider.GeneratedNamespace = "Example.DataModel";
            DataService.Providers.Add(provider);
        }
 
        protected void RegisterJavascript(string fullPath)
        {
            var script = new HtmlGenericControl("script");
            script.Attributes.Add("type", "text/javascript");
            script.Attributes.Add("src", fullPath);
 
            Page.Header.Controls.Add(script);
        }
 
        #endregion
    }
}

In my last post about including jQuery properly I introduced the RegisterJavascript method. We can put the method in this class so we can use it in all pages inheriting the class.

One thing worth mentioning is that the app.config for SubSonic must be setup before you can expect this to work. You should also have a SubSonic data model built and referenced. You can learn more about that here: http://michaelpardo.com/2009/06/setting-up-subsonic-in-you-dnn-module-solution/

In my modules I generally have three different projects. One for SubSonic, one for common stuff, and one for the module itself. The first thing I do when setting up a module is add a project for SubSonic. Let’s add a “Class Library” and call it “DataModel”.

Add New Project

I’ll get to the setup of the other projects in another post. Let’s just worry about SubSonic for right now as it is probably the easiest project to setup.

Now that we have the project added let’s add our app config. From the “Project” menu click “Add New Item…”. Select “Application Configuration File” and add it to your project.

Add New Item

Here is the blank app.config file that I use to get started. You will need to alter some of the settings to fit your project.

You can find more information on setting up the app.config here: http://subsonicproject.com/setup/gettingstarted/

After you have done the initial setup you will be able to generate your classes. If you haven’t already setup SubSonic in Visual Studio you can learn how here: http://michaelpardo.com/2009/06/visuals-studio-and-subsonic/

Click “Tools” and then “SubSonic” to generate the classes. You will not see the files in the Solution Explorer until you click “Show All Files” and “Refresh”. If you have done all these steps properly you will see a “Generated” folder in your project. Add the folder and build.

You’re done! Now you can reference this project in your module.

If you aren’t using SubSonic yet, you should be. You can find out more at subsonicproject.com. Here’s the easiest way to get SubSonic working smoothly from within Visual Studio.

Download the SubSonic source from http://subsonicproject.googlecode.com/files/SubSonic_2.1_Final_Source.zip and extract it. I chose to create a folder for SubSonic in “C:\Program Files\”.

SubSonic Folder

Next, in Visual Studio click the “External Tools…” menu item from the “Tools” menu. A dialog box will pop up allowing you to manage the external tools. Click “Add” and enter the following:

External Tools Dialog

Title: SubSonic (or whatever you want really)
Command: C:\YOUR_SUBSONIC_PATH\SubSonic2.2\SubCommander\sonic.exe
Arguments: generate /out $(ProjectDir)/Generated
Initial directory: $(ProjectDir)

I check “User Output window”. If you don’t a console window will popup every time you run the command.

Now you will have a “SubSonic” menu item in your “Tools” menu which will generate all your subsonic classes.

Tools Menu

Of course, you need to have your app.config/web.config setup with SubSonic for the classes to generate. You can find more information about doing that here: http://subsonicproject.com/setup/gettingstarted/.