Jim's Blog Ramblings about novels, comics, programming, and other geek topics

19Aug/09Off

SubSonic 3.0

Google AdSense

I've used SubSonic 2.x for a while and I've blogged about how useful it was as a tool to aid my development projects. A few weeks ago, SubSonic 3.0 was released and I hadn't spent much time reviewing the updates and changes to version 3.0 until this week.

At the present, I have several large projects using SubSonic 2.x and I have a new smaller project starting this week. So I decided to download and install SubSonic 3.0 and use the small project to get familiar with the updates prior to updating my other web sites from 2.x to 3.0.

3Feb/097

How to serialize SubSonic objects with nullable properties

Google AdSense

Recently, I ran into the following error when trying to serialize some SubSonic generated classes.

Cannot serialize member 'XXX' of type System.Nullable`1[XXX]. XmlAttribute/XmlText cannot be used to encode complex types.

The SubSonic autogenerated classes cannot serialize nullable types such as DateTime? and GUID?. This is really a .NET serialization problem and not directly related to SubSonic, since the SubSonic library just uses the native .NET code for serialization.

24Sep/085

How to use custom audit fields with SubSonic

SubSonic uses a few audit fields by default - you don't need to write any code. However, the field names that are used are coded into the source code. You can modify the source code and generate a new assembly, but if you're like me and would rather not edit the source code (so you don't need to update the code at every release), then you'll be looking for an alternative approach.

3Jul/0812

How to perform an aggregate query using SubSonic 2.1’s SqlQuery

Just recently, I upgraded from SubSonic 2.0 to SubSonic 2.1 RC1. One of the new features is the addition of the SqlQuery class and it's ability to perform aggregate queries (along with many other features).

If you're not familiar with SubSonic, then it is a tool that builds your DAL (Data Abstraction Layer). Each of your database, tables, and rows become classes that you can insert, update, delete, and select from.

By using an ORM instead of embedding SQL statements in your applications, it makes your code easier to test and catch mistakes (such as typos in field or table names). There's pros and cons to ORM versus SQL statements, but this entry won't touch on those issues.

This is an example of how to perform a query to get a count of unique records while grouping by a column.

19Nov/072

Debugging assembly loading within a unit test project

I reorganized one of my projects and discovered that my MbUnit tests no longer worked. The error stated that one of the assemblies could not be loaded, but it worked fine before I removed and added the project to another solution. The assembly in question was the SubSonic assembly.

This took me a little while to work through and I wasn't able to find any helpful blogs or documentation to help. So I figured that I'll contribute to the blogosphere and maybe someone else might find this useful.

I thought through the problem and finally figured it out. I had forgotten about how MbUnit executes. Even though, MbUnit's output is sent to the Visual Studio's output window, the tool runs in a separate process - remembering that I was able to debug and figure out how to solve my problem.

The root cause was that the assembly wasn't in the working directory for the test assembly so it couldn't be loaded by MbUnit. The SubSonic assembly was not in the GAC, the SubSonic directory wasn't in my %PATH%, and the assembly's "Copy Local"property was set to false.

By changing the "Copy Local" property, the test project automatically copied the SubSonic assembly to the bin directory and MbUnit was then able to load the assembly without any errors. Alternatively, you could add SubSonic to your GAC (Global Assembly Cache) or add the SubSonic directory (the directory with the .dll file) your environment path. Here's a little more information and the steps I used to resolve the problem.

Software

  • Visual Studio Professional 2005
  • SubSonic 2.0.3
  • MbUnit 1.0.2700 Add-in
  • TestDriven 2.8 Add-in

Symptoms

TestCase failed: An error occurred creating the configuration section handler for SubSonicService: Could not load file or assembly 'SubSonic' or one of its dependencies. The system cannot find the file specified.

System.Configuration.ConfigurationErrorsException

Message: An error occurred creating the configuration section handler for SubSonicService: Could not load file or assembly 'SubSonic' or one of its dependencies. The system cannot find the file specified.

Source: System.Configuration

StackTrace:
    at System.Configuration.BaseConfigurationRecord.FindAndEnsureFactoryRecord(String configKey, Boolean& isRootDeclaredHere)
    at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
    at System.Configuration.BaseConfigurationRecord.GetSection(String configKey, Boolean getLkg, Boolean checkPermission)
    at System.Configuration.BaseConfigurationRecord.GetSection(String configKey)
    at System.Configuration.ClientConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String sectionName)
    at System.Configuration.ConfigurationManager.GetSection(String sectionName)
    at SubSonic.DataService.LoadProviders()
    at SubSonic.DataService.GetInstance(String providerName)

Inner Exception:
    System.IO.FileNotFoundException
    Message: Could not load file or assembly 'SubSonic' or one of its dependencies. The system cannot find the file specified.

Source: System.Configuration

StackTrace:
    at System.Configuration.TypeUtil.GetTypeWithReflectionPermission(IInternalConfigHost host, String typeString, Boolean throwOnError)
    at System.Configuration.RuntimeConfigurationRecord.RuntimeConfigurationFactory.Init(RuntimeConfigurationRecord configRecord, FactoryRecord factoryRecord)
    at System.Configuration.RuntimeConfigurationRecord.RuntimeConfigurationFactory.InitWithRestrictedPermissions(RuntimeConfigurationRecord configRecord, FactoryRecord factoryRecord)
    at System.Configuration.RuntimeConfigurationRecord.RuntimeConfigurationFactory..ctor(RuntimeConfigurationRecord configRecord, FactoryRecord factoryRecord)
    at System.Configuration.RuntimeConfigurationRecord.CreateSectionFactory(FactoryRecord factoryRecord)
    at System.Configuration.BaseConfigurationRecord.FindAndEnsureFactoryRecord(String configKey, Boolean& isRootDeclaredHere)

Solution

  1. Expand "References" of the Test project
  2. Select "Subsonic" library
  3. Set "Copy Local" to true
  4. Rebuild the test project
6Nov/071

Saving and truncating values to fit with SubSonic

For one of my applications that I'm working on, I have two different data sources where one needs to push data to the other. However, since both data sources were not designed for this, there's a few destination fields that are shorter than the source fields.

To resolve this, I needed to truncate any values to fit within the maximum size of the destination fields. If there were only a handful, then it would be easy enough to code. However, there's several dozen fields and just to make sure I don't miss a field, I wrote the below code to loop through the destination fields and automatically truncate any values that exceed the maximum length.

After asking if there was a SubSonic way to do this, I learned that there wasn't any code already written. Montyguy recommended that I override the BeforeValidate method of my Data Access Layer (DAL) related class instead of calling a separate function in my code.

protected override void BeforeValidate()
{   
   //Truncates any string columns
   //to the maximum length of the field
   foreach (TableSchema.TableColumn col in 
            this.GetSchema().Columns)
   {
      if (col.DataType == DbType.String &&
          !string.IsNullOrEmpty(
            this.GetColumnValue<string>(col.ColumnName)) &&
          this.GetColumnValue<string>(col.ColumnName).Length > 
             col.MaxLength)
      {
         string newValue = 
            this.GetColumnValue<string>(col.ColumnName).
               Substring(0, col.MaxLength);
         this.SetColumnValue(col.ColumnName, newValue);
      }
   }
   base.BeforeValidate();
}

The above code loops through each column/field in the table/class and checks to see if the value is not null or empty and if the length exceeds the maximum length defined by the database. If so, then it takes a Substring of the value and sets the column to the truncated version of the original value.

My Related Posts: , , ,
14Sep/070

Simplify Your Data Access With ORM Automation

Every now and then, I stumble upon something worth saving. The SubSonic project is an open-source project that generates a Data Access Layer (or Data Abstraction Layer) for your .NET projects.

At first, I wasn't very familiar with ORM (Object Relational Mapping). I've been writing SQL statements for many, many years. Mostly, just because I've been surrounded by data-centric personnel such as DBAs or I've had to take on the role of a DBA.

Here is an example of how to query the "Change_Request" table to retrieve the top 10 records ordered by the created_on column.  For the most part, many of my ASP.NET web sites look like this. It is very easy to generate the SQL and this example doesn't even show a select command with several join statements and complex where clauses.

 <asp:SqlDataSource ID="sqlTop10Requests" runat="server" 
      ConnectionString="<%$ ConnectionStrings:SCR_Database %>"
      SelectCommand="select top 10 change_request.* 
              from change_request 
              where Project_id=@appId 
              order by created_on desc">
     <SelectParameters>
         <asp:QueryStringParameter Name="appId" 
             QueryStringField="appId" Type="string" />
         </SelectParameters>
</asp:SqlDataSource>

Now, this code was converted over to use objects generated by the SubSonic project.  Below is how my code looks now:

Query qry = new Query(ChangeRequest.Schema)
     .WHERE(ChangeRequest.Columns.ProjectId, projectId);
qry.Top(10);
qry.OrderBy = OrderBy.Desc(ChangeRequest.Columns.CreatedOn);

Although, you may be saying that it is still rather complicated. I see it that my biggest benefit is that everything is now strongly-typed. If the field named "Created_On" is renamed to "Created_On_Date", I can just refactor my code to make the change and if it compiles, then I know I found every instance and fixed it.

If a column was renamed when I am using the inline SQL (the first example), I'll have to rely on text searching and looking at each individual page and query statement to determine if the field is owned by the table that was modified.

Here's another way of querying using SubSonic:

ChangeRequestCollection crs2 = new ChangeRequestCollection()
     .Where(ChangeRequest.Columns.ProjectId, _projectId)
     .OrderByDesc(ChangeRequest.Columns.ClosedOn);

The only reason that I can't use the above code is that the TOP function isn't supported by the fluency chaining (see message board over at subsonic for details).

 

My Related Posts: , , ,

8Sep/071

Thoughts on Data Abstraction

On my path of becoming a better developer, I've began participating in the development of DotNetKicks. DotNetKicks is an open-source project that is a community driven link submission portal. In other words, it is very similar to major web sites of Digg and Reddit, but its focused into specific areas of technology.

DotNetKicks.com is a community based news site edited by our members. It specializes in Microsoft development techniques, technologies and tools including ASP.NET, C#, VB.NET, C++, Visual Studio, Team System, SQL Server and Microsoft Vista.

Like most open source projects, the project uses Subversion. I've used SVN a few times before for other open source projects. In the past, I've just used the SVN command line. But this time, I downloaded and installed TortoiseSVN and it has worked great so far.

I've been able to address a couple enhancement requests including adding a way to see which stories a user has submitted (DNK issue 24) and see all of the comments submitted by the user (DNK issue 62). These were two pretty simple fixes that were resolved by adding some stored procedures and a little bit of coding. Gavin Joyce, project lead, writes about the new features recently added to DotNetKicks on his blog.

One of the things I've notice from reviewing the code so far, is the use of a data abstraction and business layers. In most of my work so far, I've seen very little implementations of these layers. Most of the projects that I've written or reviewed had the presentation, data, and business layers combined into the web page (either ASP.NET or JSP).

There are advantages and disadvantages to using data abstraction layers and nearly everyone does it or reads about it for academic reasons. The largest benefit is transporting the database to another vendor or platform. You can just edit the DAL instead of going through each page and updating your SQL code for any RDBMS vendor specific code.

But that doesn't happen very often for most internal company web sites. For the most part, I've worked on Oracle databases for the government and SQL Server databases for commercial and in-house projects. I've never been apart of something that need abstraction for portability.

One of the advantages that I found is the use of strongly typing the data. When directly querying a database and just outputting the contents, its impossible to test for the existence of columns at design time. With an automated build process that generates the DAL, you can find errors when referencing database objects that may not exist or be of a different data type that you code in your presentation layer.

The DNK project uses SubSonic to automatically generate a data abstraction layer for its SQL Server 2005 database. SubSonic is an open-source project that can generate data abstraction layers for SQL Server 2000, SQL Server 2005, MySQL, and Oracle databases.

An alternative to SubSonic is to use Strongly Typed TableAdapters. Scott Gu provides a detailed tutorial on how to build a data abstraction layer using Visual Studio.

 

My Related Posts: , ,