GetByIndex() implimentation

Feb 25, 2009 at 3:25 AM

I like the ideas that you have suggested for v4 of the framework.  I had originally commented on the IsValid() property about a year ago was implimented quite well. Thank you! 
I have the first pass at a GetByIndex() impimetation that is working within the Entity.generated CodeSmith template. If it saves some work, here it is...

NOTE: This method only works for inline SQL and will need to be updated to support SP's!

<% foreach (IndexSchema index in SourceTable.Indexes){%>
  <% 
   //ensure the index is a unique constraint
   if (index.IsPrimaryKey || !index.IsUnique)
    continue;
  %>
  /// <summary>
  /// Retrieve information for a <%= GetClassName(SourceTable) %> by the <%= GetClassName(SourceTable) + "." + GetClassNameFromIndex(index) %> unique index.
  /// </summary>
  <% foreach (ColumnSchema column in GetValidColumns(index.MemberColumns)) { %>
  /// <param name="<%= GetCamelCaseName(GetPropertyName(column)) %>"><%= GetCamelCaseName(GetPropertyName(column)) %></param>
  <% } %>
  /// <returns><%= GetClassName(SourceTable) %></returns>
  public static <%= GetClassName(SourceTable) %> Get<%= GetClassName(SourceTable) %>By<%= GetClassNameFromIndex(index) %>(<%
  isFirstTimeInLoop = true;
  foreach (ColumnSchema column in GetValidColumns(index.MemberColumns)) { 
   if(!isFirstTimeInLoop) {
    %>, <% 
   }
   isFirstTimeInLoop = false;
   %><%= GetCSharpVariableType(column) %> <%= GetCamelCaseName(GetPropertyName(column)) %><%
  } %>)
  {
   <% if(SQLType == SqlType.StoredProcedures) { %>
   string commandText = "<%= GetClassName(SourceTable) %>Get";
   <% } else { %>
   string commandText = @"<%= "\r\n" %><%= GetGetByIndexSql(SourceTable, index, SQLType, true) %> " + <%= GetClassName(SourceTable) %>.DefaultSortOrder;
   <% } %>
   
   System.Collections.Generic.List<SqlParameter> parameters = new System.Collections.Generic.List<SqlParameter>();
            <%
   foreach (ColumnSchema column in GetValidColumns(index.MemberColumns)) { %>
   parameters.Add(new SqlParameter("@<%= column.Name %>", <%= GetCamelCaseName(GetPropertyName(column)) %>));
   <% } %>
   
   return GetOne<<%= GetClassName(SourceTable) %>>(commandText, parameters);
  }
  <% } %>
  

 
public string GetClassNameFromIndex(IndexSchema index)
{
 string className = "";
 
 if(index.MemberColumns.Count == 1)
 {
  className = GetColumnNameWithoutIdSuffix(index.MemberColumns[0].Column);
 }
 else
 {
  //ORIG - className = GetClassName(index.Table);
  //MLS added
  foreach(ColumnSchema column in index.MemberColumns)
  {
   className += GetColumnNameWithoutIdSuffix(column);
  }
 }
 
 return className;
}


And then in the file DbUtilities.cs:

public string GetGetByIndexSql(TableSchema SourceTable, IndexSchema index, SqlType sqlType, bool includeSelect)
{
        string statement = String.Empty;

        if (includeSelect)
        {
          statement = "SELECT \r\n";

         if(sqlType == SqlType.Dynamic)
         {
         statement += "\" + " + GetClassName(SourceTable) + ".SelectFieldList + @\"\r\n";
         }
          else
         {
          statement += GetTableColumnList(SourceTable.Columns) + " \r\n";
         }
        }

 statement += "FROM \r\n";
 statement += "\t[" + SourceTable.Owner + "].[" + SourceTable.Name + "] \r\n";
 statement += "WHERE \r\n";
 statement += GetWhereClauseForColumnsEqualsSqlParameterList(index.MemberColumns);
 return statement;

}

Enjoy and great work!