DotNetNuke – The Data Layer – Installing CodeSmith

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

Most Commented Post

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

  • Brian:

    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;
    }

Leave a Reply

Comment Policy:

  • You must verify your comment by responding to the automated email that is sent to your email address. Unverified comments will never show.Leave a good comment that adds to the conversation and I'll leave your link in.
  • Leave me pure spam and I'll delete it.
  • Leave a general comment and I'll remove the link but keep the comment.

Notify me of followup comments via e-mail

Bear