.NET Answers

DotNetNuke – The Data Layer – Installing CodeSmith

July 22, 2008 By: Dave

There are other controls, and I’m sure I could go on talking about them for weeks yet, but we need to move on. Since most modules you build are going to need at least one table behind them, and most of the presentation layer can’t be developed until we have the data access coded, we are going to focus on the “right” way of accessing the tables in your database today.

The first thing you want to do is grab a copy of CodeSmith 2.6.

Now, before you tune out thinking you’ve seen the “How to use CodeSmith to create your Data Access Layer in DotNetNuke” presentation before, trust me. What I’m about to show you is only about 80% of what you’ve seen before. I have a small tweak that will by pass the info classes and use datasets instead. Stay tuned in.

There are more recent versions and you may ultimately want to grab a copy of a newer version, but version 2.6 is free while the more recent ones require that you pay for them. You can get CodeSmith 2.6 at: http://www.codesmithtools.com/freeware.aspx

If you have trouble running CodeSmith, you may need to remove the CodeSmith.exe.config file. This file specifies DLLs that may not be on your computer. I think it was written against .NET 1.0. Everything we need to have work, works if you remove this file. You should probably just rename it so it can’t be found by the application.

The reason you want this program is because we are going to have it write most of our data access layer. To do that, you will need to download and install this zip file into your CodeSmith application directory.

Once you’ve done that, you’ll need to tell CodeSmith to use that directory. You can do that by clicking the “Create Template Folder Shortcut” button at the top of the CodeSmith application (the open folder button on the left).

Next, we will discuss the ultimate naming convention for our tables and create the SQL scripts we will use to install the final version.

Other places talking about DotNetNuke Development

• Customizing the FCK Editor in DotNetNuke – Adding smileys and …- Objective: I wanted to customize the look of the FCK Editor on PokerDIY and allow users to select poker card icons as custom smileys in all modules that use the Rich HTML Editor, for example, Private Messages, the Poker Forums (in …
• Test Driven Development for DNN Modules Template – How to use it- … to allow you to develop outside the DesktopModules folder, see this post for details http://www.dotnetnuke.com/Community/Blogs/tabid/825/EntryID/1873/Default.aspx. The following steps require you to follow this development practice. …
• DotNetNuke Development- As an open source web application framework, dotnetnuke development has proven its creditability in web application development projects. It is being used for creating, deploying and managing intranet, extranet, commercial websites, …
• Migrating DotNetNuke From Development to Remote Production- Recently I have been answering a large number of questions regarding the processes to deploy an existing development site from localhost to a remote hosting provider. Typically this has been something that I was not willing to publish a …
• DotNetNuke Development by Alex Bromberg- DotNetNuke development services for DNN include: * Product Design and Analysis * Prototyping * Incremental Development Approach * Full Product Testing Cycle * Customization Services * Localization Services * Integration Services …

 

Other post in DotNetNuke - Module Development

• Creating DotNetNuke Modules - May 20th, 2008
• Creating DNN Modules - The Tools - May 22nd, 2008
• DotNetNuke Modules - Foundational Concepts - May 26th, 2008
• DotNetNuke Modules - Install DNN into VS 2008 - May 27th, 2008
• DotNetNuke Modules - Creating Base Modules - May 28th, 2008
• DotNetNuke Modules - Registering Your Module - May 29th, 2008
• DotNetNuke Modules - Where Stuff Shows Up - June 3rd, 2008
• DotNetNuke Modules - Benefits of Architecture - June 4th, 2008
• DotNetNuke Modules - Anatomy of the View - June 9th, 2008
• DotNetNuke Modules - Adding Actions - June 11th, 2008
• DotNetNuke Modules - DNN Controls - Label - June 18th, 2008
• DotNetNuke - Internationalization - June 25th, 2008
• DotNetNuke Modules - Internationalization (part 2) - June 30th, 2008
• DotNetNuke Modules - Labels w/ no Help - July 9th, 2008
• DotNetNuke Modules - LinkButtons - July 14th, 2008
• DotNetNuke Modules - Collapsible Panels - July 16th, 2008
• DotNetNuke - The Data Layer - Installing CodeSmith - July 22nd, 2008
• DotNetNuke - Modules - Creating The Tables - July 24th, 2008
• DotNetNuke - Modules - Creating Stored Procs - July 29th, 2008
• DotNetNuke - Modules - Portal Specific Modules - July 31st, 2008
• DotNetNuke Modules - Data Access Layer - August 5th, 2008
• DotNetNuke Modules - Data Access Layer - August 7th, 2008
• DotNetNuke - Data Access Layer Alternative - August 12th, 2008
• DotNetNuke - Modules - Linking within the module - August 14th, 2008
• DotNetNuke - Make Your Module Searchable - August 19th, 2008
• DotNetNuke Modules - Making Content Portable - August 25th, 2008
• DotNetNuke Modules - Exceptions the DNN Way - September 2nd, 2008
• DotNetNuke Modules - PortalModuleBase - September 4th, 2008
• DotNetNuke Modules - Inter Module Communication - September 9th, 2008
• DotNetNuke Modules - Finding The Page a Module is On - September 15th, 2008
• DotNetNuke Modules - Caching - September 17th, 2008
• DotNetNuke Modules - Module Settings - September 22nd, 2008
• DotNetNuke Modules - Retrieving Settings - September 24th, 2008
• DotNetNuke Modules - Advanced Architecture - October 20th, 2008
• DotNetNuke Modules - Creating the PA - October 30th, 2008
• DotNetNuke Modules - Automating the PA - November 5th, 2008
• DotNetNuke - FileUploadControl Danger! - February 26th, 2009

If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

Most Commented Post

• Object Oriented Programming has Failed Us
• Microsoft’s Biggest Mistake with ASP.NET
• WordPress w/ Forms Authentication on IIS6

Bookmark to:

Hide Sites

One Response to “ DotNetNuke – The Data Layer – Installing CodeSmith ”

1. # 1 Brian Says:
   January 8th, 2009 at 10:39 am

   Thanks for the great tutorial. I’m not as patient as you as far as going back in to “fix” generated code. Tried it twice before I had to update the CodeSmith template. Below is the modified template for the “Controller” class if its of any use to anybody:
   ———————————————————————————–

   using System;
   using System.Collections;
   using System.Data;
   using DotNetNuke.Framework;
   using ;

   namespace
   {
   public class Controller : DotNetNuke.Entities.Modules.ISearchable, DotNetNuke.Entities.Modules.IPortable : DotNetNuke.Entities.Modules.IPortable
   {
   #region “Public Methods”

   public (,int moduleId )
   {
   table = new ();
   table.Load(DataProvider.Instance().Get(, moduleId ));
   if (table.Count > 0) return table[0];
   return null;
   }

   public ()
   {
   table = new ();
   table.Load(DataProvider.Instance().());
   return table;
   }

   <% for (int K = 0; K

   public ArrayList By()
   {
   return DotNetNuke.Common.Utilities.CBO.FillCollection(DataProvider.Instance().By(), typeof());
   }

   public int ( obj)
   {
   return (int)DataProvider.Instance().();
   }

   public void ( obj)
   {
   DataProvider.Instance().(, );
   }

   public void ()
   {
   DataProvider.Instance().();
   }

   #endregion

   #region “Optional Interfaces”

   public DotNetNuke.Services.Search.SearchItemInfoCollection GetSearchItems(DotNetNuke.Entities.Modules.ModuleInfo ModInfo)
   {
   return null;
   }

   public string ExportModule(int ModuleID)
   {
   return “”;
   }

   public void ImportModule(int ModuleID, string Content, string Version, int UserId)
   {
   }

   #endregion

   }
   }

   public string GetMemberVariableDeclarationStatement(ColumnSchema column)
   {
   return GetMemberVariableDeclarationStatement(”protected”, column);
   }

   public string GetTableName(TableSchema table)
   {
   if( ObjectQualifier.Length > 0 )
   {
   return table.Name.Replace(ObjectQualifier, “”);
   }
   else
   {
   return table.Name;
   }
   }

   public string GetMemberVariableDeclarationStatement(string protectionLevel, ColumnSchema column)
   {
   //string statement = “Dim ” + GetMemberVariableName(column) + ” as ” + GetVBVariableType(column);
   string statement = GetCSharpVariableType(column) + ” ” + GetMemberVariableName(column) + “;”;// as ” + GetVBVariableType(column);
   return statement;
   }

   public string GetReaderAssignmentStatement(ColumnSchema column, int index)
   {
   string statement = “if (!reader.IsDBNull(” + index.ToString() + “)) “;
   statement += GetMemberVariableName(column) + ” = “;

   if (column.Name.EndsWith(”TypeCode”)) statement += “(” + column.Name + “)”;

   statement += “reader.” + GetReaderMethod(column) + “(” + index.ToString() + “);”;

   return statement;
   }

   public string GetCamelCaseName(string value)
   {
   return value.Substring(0, 1).ToLower() + value.Substring(1);
   }

   public string GetMemberVariableName(ColumnSchema column)
   {
   string propertyName = GetPropertyName(column);
   string memberVariableName = “_” + GetCamelCaseName(propertyName);

   return memberVariableName;
   }

   public string GetPropertyName(ColumnSchema column)
   {
   string propertyName = column.Name;

   if (propertyName == column.Table.Name + “Name”) return “Name”;
   if (propertyName == column.Table.Name + “Description”) return “Description”;

   if (propertyName.EndsWith(”TypeCode”)) propertyName = propertyName.Substring(0, propertyName.Length – 4);

   return propertyName;
   }

   public string GetMemberVariableDefaultValue(ColumnSchema column)
   {
   switch (column.DataType)
   {
   case DbType.Guid:
   {
   return “Guid.Empty”;
   }
   case DbType.AnsiString:
   case DbType.AnsiStringFixedLength:
   case DbType.String:
   case DbType.StringFixedLength:
   {
   return “string.Empty”;
   }
   default:
   {
   return “”;
   }
   }
   }

   public string GetCSharpVariableType(ColumnSchema column)
   {
   if (column.Name.EndsWith(”TypeCode”)) return column.Name;

   switch (column.DataType)
   {
   case DbType.AnsiString: return “string”;
   case DbType.AnsiStringFixedLength: return “string”;
   case DbType.Binary: return “byte()”;
   case DbType.Boolean: return “bool”;
   case DbType.Byte: return “byte”;
   case DbType.Currency: return “decimal”;
   case DbType.Date: return “DateTime”;
   case DbType.DateTime: return “DateTime”;
   case DbType.Decimal: return “decimal”;
   case DbType.Double: return “double”;
   case DbType.Guid: return “Guid”;
   case DbType.Int16: return “short”;
   case DbType.Int32: return “int”;
   case DbType.Int64: return “long”;
   case DbType.Object: return “object”;
   case DbType.SByte: return “sbyte”;
   case DbType.Single: return “float”;
   case DbType.String: return “string”;
   case DbType.StringFixedLength: return “string”;
   case DbType.Time: return “TimeSpan”;
   case DbType.UInt16: return “ushort”;
   case DbType.UInt32: return “uint”;
   case DbType.UInt64: return “ulong”;
   case DbType.VarNumeric: return “decimal”;
   default:
   {
   return “__UNKNOWN__” + column.NativeType;
   }
   }
   }

   public string GetVBVariableType(ColumnSchema column)
   {
   if (column.Name.EndsWith(”TypeCode”)) return column.Name;

   switch (column.DataType)
   {
   case DbType.AnsiString: return “string”;
   case DbType.AnsiStringFixedLength: return “string”;
   case DbType.Binary: return “byte()”;
   case DbType.Boolean: return “Boolean”;
   case DbType.Byte: return “byte”;
   case DbType.Currency: return “decimal”;
   case DbType.Date: return “DateTime”;
   case DbType.DateTime: return “DateTime”;
   case DbType.Decimal: return “decimal”;
   case DbType.Double: return “double”;
   case DbType.Guid: return “Guid”;
   case DbType.Int16: return “short”;
   case DbType.Int32: return “Integer”;
   case DbType.Int64: return “long”;
   case DbType.Object: return “object”;
   case DbType.SByte: return “sbyte”;
   case DbType.Single: return “float”;
   case DbType.String: return “String”;
   case DbType.StringFixedLength: return “String”;
   case DbType.Time: return “TimeSpan”;
   case DbType.UInt16: return “ushort”;
   case DbType.UInt32: return “uint”;
   case DbType.UInt64: return “ulong”;
   case DbType.VarNumeric: return “decimal”;
   default:
   {
   return “__UNKNOWN__” + column.NativeType;
   }
   }
   }

   public string GetReaderMethod(ColumnSchema column)
   {
   switch (column.DataType)
   {
   case DbType.Byte:
   {
   return “GetByte”;
   }
   case DbType.Int16:
   {
   return “GetInt16″;
   }
   case DbType.Int32:
   {
   return “GetInt32″;
   }
   case DbType.Int64:
   {
   return “GetInt64″;
   }
   case DbType.AnsiStringFixedLength:
   case DbType.AnsiString:
   case DbType.String:
   case DbType.StringFixedLength:
   {
   return “GetString”;
   }
   case DbType.Boolean:
   {
   return “GetBoolean”;
   }
   case DbType.Guid:
   {
   return “GetGuid”;
   }
   case DbType.Currency:
   case DbType.Decimal:
   {
   return “GetDecimal”;
   }
   case DbType.DateTime:
   case DbType.Date:
   {
   return “GetDateTime”;
   }
   default:
   {
   return “__SQL__” + column.DataType;
   }
   }
   }

   public string GetClassName(TableSchema table)
   {
   if( ObjectQualifier.Length > 0 )
   {
   return (”DataSet” + table.Name + “.”).Replace(ObjectQualifier, “”) + table.Name + “DataTable”;
   }
   else
   {
   return “DataSet” + table.Name + “.” + table.Name + “DataTable”;
   }
   }

   public string GetClassNameInfo(TableSchema table)
   {
   if( ObjectQualifier.Length > 0 )
   {
   return (”DataSet” + table.Name + “.”).Replace(ObjectQualifier, “”) + table.Name + “Row”;
   }
   else
   {
   return “DataSet” + table.Name + “.” + table.Name + “Row”;
   }
   }

   public string GetSqlDbType(ColumnSchema column)
   {
   switch (column.NativeType)
   {
   case “bigint”: return “BigInt”;
   case “binary”: return “Binary”;
   case “bit”: return “Bit”;
   case “char”: return “Char”;
   case “datetime”: return “DateTime”;
   case “decimal”: return “Decimal”;
   case “float”: return “Float”;
   case “image”: return “Image”;
   case “int”: return “Int”;
   case “money”: return “Money”;
   case “nchar”: return “NChar”;
   case “ntext”: return “NText”;
   case “numeric”: return “Decimal”;
   case “nvarchar”: return “NVarChar”;
   case “real”: return “Real”;
   case “smalldatetime”: return “SmallDateTime”;
   case “smallint”: return “SmallInt”;
   case “smallmoney”: return “SmallMoney”;
   case “sql_variant”: return “Variant”;
   case “sysname”: return “NChar”;
   case “text”: return “Text”;
   case “timestamp”: return “Timestamp”;
   case “tinyint”: return “TinyInt”;
   case “uniqueidentifier”: return “UniqueIdentifier”;
   case “varbinary”: return “VarBinary”;
   case “varchar”: return “VarChar”;
   default: return “__UNKNOWN__” + column.NativeType;
   }
   }

   public string GetPrimaryKeyType(TableSchema table)
   {
   if (table.PrimaryKey != null)
   {
   if (table.PrimaryKey.MemberColumns.Count == 1)
   {
   return GetCSharpVariableType(table.PrimaryKey.MemberColumns[0]);
   }
   else
   {
   throw new ApplicationException(”This template will not work on primary keys with more than one member column.”);
   }
   }
   else
   {
   throw new ApplicationException(”This template will only work on tables with a primary key.”);
   }
   }

   public string GetPrimaryKeyName(TableSchema table)
   {
   if (table.PrimaryKey != null)
   {
   if (table.PrimaryKey.MemberColumns.Count == 1)
   {
   return table.PrimaryKey.MemberColumns[0].Name;
   }
   else
   {
   throw new ApplicationException(”This template will not work on primary keys with more than one member column.”);
   }
   }
   else
   {
   throw new ApplicationException(”This template will only work on tables with a primary key.”);
   }
   }

   public string GetPrimaryKeyParameters(TableSchema table, bool includeTypes)
   {
   string parameters = “”;
   if (table.PrimaryKey != null)
   {
   for( int i = 0; i < table.PrimaryKey.MemberColumns.Count; i++ )
   {
   if(parameters.Length == 0)
   {
   if( includeTypes )
   parameters = parameters;// + “ByVal “;

   parameters = parameters + GetCamelCaseName(table.PrimaryKey.MemberColumns[i].Name);

   if( includeTypes )
   {
   parameters = GetCSharpVariableType(table.PrimaryKey.MemberColumns[i]) + ” ” + parameters + ” “;
   }
   }
   else
   {
   parameters = parameters + “, “;
   if( includeTypes )
   parameters = parameters;// + “ByVal “;

   parameters = parameters + GetCamelCaseName(table.PrimaryKey.MemberColumns[i].Name);

   if( includeTypes )
   {
   parameters = GetCSharpVariableType(table.PrimaryKey.MemberColumns[i]) + ” ” + parameters + ” “;
   }
   }
   }
   }
   else
   {
   throw new ApplicationException(”This template will only work on tables with a primary key.”);
   }
   return parameters;
   }

   public string GetNonPrimaryKeyParameters(TableSchema table, bool includeTypes)
   {
   string parameters = “”;
   if (table.PrimaryKey != null)
   {
   for( int i = 0; i < table.NonPrimaryKeyColumns.Count; i++ )
   {
   if(parameters.Length == 0)
   {
   if( includeTypes )
   parameters = parameters;// + “ByVal “;

   if( includeTypes )
   {
   parameters = GetCSharpVariableType(table.NonPrimaryKeyColumns[i]) + ” ” + GetCamelCaseName(table.NonPrimaryKeyColumns[i].Name) + ” “;
   }
   else
   {
   parameters = parameters + GetCamelCaseName(table.NonPrimaryKeyColumns[i].Name);
   }
   }
   else
   {
   parameters = parameters + “, “;
   if( includeTypes )
   parameters = parameters;// + “ByVal “;

   if( includeTypes )
   {
   parameters = parameters + GetCSharpVariableType(table.NonPrimaryKeyColumns[i]) + ” ” + GetCamelCaseName(table.NonPrimaryKeyColumns[i].Name) + ” “;
   }
   else
   {
   parameters = parameters + GetCamelCaseName(table.NonPrimaryKeyColumns[i].Name);
   }
   }
   }
   }
   else
   {
   throw new ApplicationException(”This template will only work on tables with a primary key.”);
   }
   return parameters;
   }

   public string GetPrimaryKeyParametersForObject(TableSchema table, string obj)
   {
   string parameters = “”;
   if (table.PrimaryKey != null)
   {
   for( int i = 0; i < table.PrimaryKey.MemberColumns.Count; i++ )
   {
   if(parameters.Length == 0)
   {
   parameters = parameters + obj + “.” + table.PrimaryKey.MemberColumns[i].Name;
   }
   else
   {
   parameters = parameters + “, “;
   parameters = parameters + obj + “.” + table.PrimaryKey.MemberColumns[i].Name;
   }
   }
   }
   else
   {
   throw new ApplicationException(”This template will only work on tables with a primary key.”);
   }
   return parameters;
   }

   public string GetNonPrimaryKeyParametersForObject(TableSchema table, string obj)
   {
   string parameters = “”;

   for( int i = 0; i < table.NonPrimaryKeyColumns.Count; i++ )
   {
   if(parameters.Length == 0)
   {
   parameters = parameters + obj + “.” + table.NonPrimaryKeyColumns[i].Name;
   }
   else
   {
   parameters = parameters + “, “;
   parameters = parameters + obj + “.” + table.NonPrimaryKeyColumns[i].Name;
   }
   }

   return parameters;
   }