Synchronize Kindle metadata between 2 Macs for 3rd party books

I try hard to stick to e-books for my developer resources for the obvious benefits of search and electronic notes/bookmarks.  I have several Macs and I use Dropbox to sync content and 3rd party e-books but I couldn't figure out how to sync my Kindle metadata for those 3rd party books.  Today I finally figured out a solution that works for Macs.

1. Created a Kindle_3rd_Party_Content folder within my Dropbox folder.
2. Moved all 3rd party .mobi and .mbp files to that folder.
3. Created symbolic links to the 3rd party .mobi and .mbp files within my Kindle application's Content Folder on one of my Macs
4. Duplicated step 3 on my other Macs

Now when I open my Kindle application on any of my Macs, Kindle recognizes the symbolic links as being within my Downloaded Items, and when I add notes/bookmarks or change my location, my changes are saved via the symbolic link to the original .mbp file stored in Dropbox.

The key to this solution is to use symbolic links instead of aliases, because the Kindle application does not recognize aliases.

The Dropbox Wiki provided some great suggestions on how to sync folders outside of the Dropbox folder.  Tom Nelson gave a nice explanation of the differences between aliases and symbolic links.

How to reuse a SQL statement

I needed to create a stored procedure that would call a somewhat complex SELECT statement using T-SQL full text search (CONTAINS/CONTAINSTABLE). The result set would be filtered differently depending on parameter values passed into the stored procedure.  I didn't want to copy and paste the full SELECT statement into multiple IF/THEN/ELSE blocks.

I considered two options:

1. Create a shared stored procedure to return a "base" recordset, and then call that stored procedure from other stored procedures that would further filter the "base" recordset
2. Generate dynamic SQL within an IF/THEN/ELSE structure and then execute the dynamic SQL at the end.

I found this link which provided great information on how to pass a result set from one stored procedure to another:
http://www.sommarskog.se/share_data.html

I decided against Option #1 because the best option involves the creation of a temporary table.  I was worried about performance and also maintaining the declaration of the temporary table in the calling stored procedures in the event of a design change to the source table.

This link provided enough information for me to generate and execute dynamic SQL:
http://stackoverflow.com/questions/7992837/sub-query-in-dynamic-query-sql-server-2008

Here is the final design of my stored procedure:

   1: USE [MyDatabase]

   2: GO

   3: /****** Object:  StoredProcedure [dbo].[SearchMessages]    Script Date: 12/05/2013 14:37:41 ******/

   4: SET ANSI_NULLS ON

   5: GO

   6: SET QUOTED_IDENTIFIER ON

   7: GO

   8:  

   9: CREATE PROCEDURE [dbo].[SearchMessages]

  10:     @limit INT = -1

  11:     ,@and_search_terms NVARCHAR(4000) = ''

  12:     ,@or_search_terms NVARCHAR(4000) = ''

  13:     ,@xml_include_message_types XML = ''

  14:     ,@xml_exclude_message_types XML = ''

  15: AS

  16: BEGIN

  17:     SET NOCOUNT ON;

  18:     

  19:     DECLARE @idoc int;

  20:     DECLARE @select_msg_type_prefix NVARCHAR(40) = 'SELECT A.* FROM ( ';

  21:     DECLARE @select_msg_type_suffix NVARCHAR(4) = ') A ';

  22:     DECLARE @select_msg_type_where NVARCHAR(150);

  23:     DECLARE @select_msg_type_orderby NVARCHAR(100) = ' ORDER BY A.select_rank, A.rank DESC, A.title';

  24:     DECLARE @select_final NVARCHAR(MAX);

  25:  

  26:     DECLARE @select_rnk_msg NVARCHAR(1000) =

  27:         '

  28:         SELECT RNK_MSG.* 

  29:         FROM (

  30:             SELECT m.*, 1 as select_rank, km.rank

  31:             FROM dbo.MY_TABLE m

  32:             INNER JOIN

  33:                 CONTAINSTABLE(dbo.MY_TABLE, (msg_text, location, title), @and_search_terms) as km

  34:                 ON m.msg_id = km.[KEY]

  35:             UNION

  36:             SELECT m.*, 2 as select_rank, km.rank

  37:             FROM dbo.MY_TABLE m

  38:             INNER JOIN

  39:                 CONTAINSTABLE(dbo.MY_TABLE, (msg_text, location, title), @or_search_terms) as km

  40:                 ON m.msg_id = km.[KEY]

  41:             WHERE m.msg_id NOT IN (SELECT msg_id FROM dbo.MY_TABLE WHERE CONTAINS((msg_text, location, title), @and_search_terms))

  42:         ) RNK_MSG

  43:         WHERE RNK_MSG.status != ''N'' AND RNK_MSG.delete_ind != 1

  44:         '

  45:         ;

  46:         

  47:     IF @limit >= 0

  48:     BEGIN

  49:         set @select_msg_type_prefix = 'SELECT TOP (@limit) A.* FROM ( ';

  50:     END

  51:         

  52:     IF @xml_include_message_types IS NOT NULL AND @xml_include_message_types.exist('/MessageTypes') = 1

  53:     BEGIN

  54:         EXEC sp_xml_preparedocument @idoc OUTPUT, @xml_include_message_types

  55:  

  56:         SET @select_msg_type_where =

  57:         'WHERE A.message_type_id IN (SELECT * FROM OPENXML (@idoc, ''/MessageTypes/Type'', 1) WITH (ID char(1)))';

  58:     END

  59:     ELSE IF @xml_exclude_message_types IS NOT NULL AND @xml_exclude_message_types.exist('/MessageTypes') = 1

  60:     BEGIN

  61:         EXEC sp_xml_preparedocument @idoc OUTPUT, @xml_exclude_message_types

  62:  

  63:         SET @select_msg_type_where =

  64:         'WHERE A.message_type_id NOT IN (SELECT * FROM OPENXML (@idoc, ''/MessageTypes/Type'', 1) WITH (ID char(1)))';

  65:     END

  66:     ELSE

  67:     BEGIN

  68:         SET @select_msg_type_where = '';

  69:     END

  70:  

  71:     SET @select_final = @select_msg_type_prefix + @select_rnk_msg + @select_msg_type_suffix + @select_msg_type_where + @select_msg_type_orderby;

  72:     EXEC sp_executesql @select_final

  73:         ,N'@idoc INT,@limit INT,@and_search_terms NVARCHAR(4000),@or_search_terms NVARCHAR(4000)'

  74:         ,@idoc=@idoc,@limit=@limit,@and_search_terms=@and_search_terms,@or_search_terms=@or_search_terms;

  75:  

  76: END

NUnit TestCase vs. TestCaseSource attributes

NUnit is my favorite framework for unit testing C#.NET code.  I have long been a fan of the TestCase attribute for testing multiple scenarios where the test logic is identical.  For example, TestCase is fantastic for checking edge cases.

Here is a simplistic class for calculating the difference between 2 dates.  If the dates represent the same day, the result should be a TimeSpan of 0, otherwise calculate the actual difference.

1. public class Calculator
2. {
3.     public static TimeSpan SubtractDates(DateTime firstDate, DateTime secondDate)
4.     {
5.         return firstDate.Date == secondDate.Date
6.             ? new TimeSpan(0)
7.             : secondDate - firstDate;
8.     }
9. }

I can use the TestCase attribute to confirm that dates from previous or subsequent days return a TimeSpan with an Hours value that is not 0, but dates from the same day always return a TimeSpan with an Hours value of 0.

1. [TestCase(1, 0, -9)]
2. [TestCase(2, 8, 0)]
3. [TestCase(2, 9, 0)]
4. [TestCase(2, 10, 0)]
5. [TestCase(3, 0, 15)]
6. public static void Test_Calculator_SubtractDates_Using_TestCase(int day, int hour, int expectedHours)
7. {
8.     var firstDate = new DateTime(2000, 6, 2, 9, 0, 0);
9.     var secondDate = new DateTime(2000, 6, day, hour, 0, 0);
10.  
11.     TimeSpan actual = Calculator.SubtractDates(firstDate, secondDate);
12.     Assert.That(actual.Hours, Is.EqualTo(expectedHours));
13. }

TestCase is limited in that the constructor will accept only constant, typeof, or array creation expressions.  This can be a pain if you want to test various scenarios with a range of objects.  For example, I am not able to do this with TestCase:

1. [TestCase(new DateTime(2000, 6, 1, 0, 0, 0), -9)]
2. [TestCase(new DateTime(2000, 6, 2, 8, 0, 0), 0)]
3. [TestCase(new DateTime(2000, 6, 2, 9, 0, 0), 0)]
4. [TestCase(new DateTime(2000, 6, 2, 10, 0, 0), 0)]
5. [TestCase(new DateTime(2000, 6, 3, 0, 0, 0), 15)]
6. public static void Test_Calculator_SubtractDates_Using_TestCase(DateTime secondDate, int expectedHours)
7. {
8.     var firstDate = new DateTime(2000, 6, 2, 9, 0, 0);
9.  
10.     TimeSpan actual = Calculator.SubtractDates(firstDate, secondDate);
11.     Assert.That(actual.Hours, Is.EqualTo(expectedHours));
12. }

If I attempt to run these tests, I get an error “An attribute argument must be a constant expression, typeof expression or array creation expression of an attribute parameter type.”

I just recently discovered the TestCaseSource attribute which can be used to work around the limitations of TestCase while providing the same functionality.  Here is the same set of unit tests using TestCaseSource:

1. [Test, TestCaseSource("SecondDateCases")]
2. public static void Test_Calculator_SubtractDates_Using_TestCaseSource(DateTime secondDate, int expectedHours)
3. {
4.     var temp = SecondDateCases.ToString();
5.     var firstDate = new DateTime(2000, 6, 2, 9, 0, 0);
6.  
7.     TimeSpan actual = Calculator.SubtractDates(firstDate, secondDate);
8.     Assert.That(actual.Hours, Is.EqualTo(expectedHours));
9. }
10. private static object[] SecondDateCases =
11. {
12.     new object[] { new DateTime(2000, 6, 1, 0, 0, 0), -9 },
13.     new object[] { new DateTime(2000, 6, 2, 8, 0, 0), 0 },
14.     new object[] { new DateTime(2000, 6, 2, 9, 0, 0), 0 },
15.     new object[] { new DateTime(2000, 6, 2, 10, 0, 0), 0 },
16.     new object[] { new DateTime(2000, 6, 3, 0, 0, 0), 15 }
17. };

For more information, consult the NUnit 2.6.3 Documentation for TestCase and TestCaseSource.

Resolve Entity Framework “An error occurred while saving entities that do not expose foreign key properties for their relationships” exception due to an empty string

A more complete version of the error message is this:

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details. System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_xxx". The conflict occurred in database "xxx", table "dbo.xxx", column ‘xxx’. The statement has been terminated.

My POCO originally had this design, exposing the Race string property as the foreign key for the associated RaceEntity lookup table entity:

1. public string Race { get; set; }
2. public virtual RaceLookup RaceEntity { get; set; }

My configuration file looks like this:

1. HasOptional(m => m.RaceEntity).WithMany().HasForeignKey(m => m.Race);

The underlying SQL Server table has a Race column defined as nullable nvarchar(50) with a foreign key constraint to a related Races lookup table.

In the UI, I have a dropdown list containing all of the possible Race values from the Races lookup table, with an additional lookup value of “None” because the underlying table allows a null value.  I was getting the exception when a user selected the “None” value because that set my POCO.Race string property to equal an empty string, which in turn caused the foreign key constraint conflict exception.

The solution was to change my code so that all empty strings are converted to null prior to save.  In my case, I accomplished this by changing the design of the Race property on my POCO:

1. private string _race;
2. public string Race
3. {
4.     get { return _race; }
5.     set { _race = string.IsNullOrEmpty(value) ? null : value; }
6. }
7. public virtual RaceLookup RaceEntity { get; set; }

Resolve debug issue in Eclipse for Android application

I have an Android application that I was not able to debug in Eclipse.  I would "Debug As" > "Android Application" but none of my breakpoints would be hit.  If I went to the Debug perspective and launched debug for my application, the application thread would appear briefly and then vanish.

I resolved this problem by using the DDMS perspective.  Once in DDMS, I would start my application on my device.  I then selected my application process and clicked the "Debug selected process" button.

Custom .NET Validator for CheckBoxList control

Here is a User Control that serves as a custom validator for a .NET CheckBoxList control.

ASCX file:

1. <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="CheckBoxListCustomValidator.ascx.cs" Inherits="MARE.Directory.UserControls.CheckBoxListCustomValidator" ClientIDMode="AutoID" %>
2. <asp:CustomValidator ID="valCheckBoxList" class="error" EnableClientScript="true" Text="Required" ErrorMessage="Required" runat="server" />
3.  
4. <script type="text/javascript">
5.     (function ($) {
6.  
7.         <%= ClientValidationMethodName %> = function (sender, e) {
8.             var $checkBoxList = $('#<%= CheckBoxListToValidate.ClientID %>');
9.             e.IsValid = $checkBoxList.find(':checkbox:checked').length > 0;
10.         };
11.  
12.         $(document).ready(function () {
13.             var $checkBoxList = $('#<%= CheckBoxListToValidate.ClientID %>');
14.             // setup validation
15.             $checkBoxList.find(':checkbox').click(function () {
16.                 // .NET validate the checkbox list
17.                 var validator = document.getElementById('<%= valCheckBoxList.ClientID %>');
18.                 ValidatorValidate(validator);
19.             });
20.         });
21.  
22.     })(jQuery);
23. </script>

Code-behind:

1. using System;
2. using System.Web.UI;
3. using System.Web.UI.WebControls;
4.  
5. namespace MARE.Directory.UserControls
6. {
7.     public partial class CheckBoxListCustomValidator : System.Web.UI.UserControl
8.     {
9.         private CheckBoxList _checkBoxListToValidate;
10.  
11.         protected void Page_Load(object sender, EventArgs e)
12.         {
13.             if (!string.IsNullOrEmpty(ControlToValidate))
14.             {
15.                 _checkBoxListToValidate = FindCheckBoxListRecursive(this.Page, ControlToValidate);
16.             }
17.             valCheckBoxList.ClientValidationFunction = ClientValidationMethodName;
18.             valCheckBoxList.ServerValidate += ValidateCheckBoxList;
19.         }
20.  
21.         private string _clientValidationMethodName;
22.         public string ClientValidationMethodName
23.         {
24.             get
25.             {
26.                 if (_clientValidationMethodName == null)
27.                 {
28.                     _clientValidationMethodName = string.Format("window.val{0}", Guid.NewGuid().ToString("N"));
29.                 }
30.                 return _clientValidationMethodName;
31.             }
32.         }
33.  
34.         public string ControlToValidate { get; set; }
35.  
36.         public CheckBoxList CheckBoxListToValidate { get { return _checkBoxListToValidate; } }
37.  
38.         public CustomValidator CheckBoxListValidator { get { return valCheckBoxList; } }
39.  
40.         public string ValidatorText
41.         {
42.             get { return CheckBoxListValidator.Text; }
43.             set { CheckBoxListValidator.Text = value; }
44.         }
45.  
46.         public string ValidatorErrorMessage
47.         {
48.             get { return CheckBoxListValidator.ErrorMessage; }
49.             set { CheckBoxListValidator.ErrorMessage = value; }
50.         }
51.  
52.         public void SetCheckBoxListToValidate(CheckBoxList checkBoxListToValidate)
53.         {
54.             _checkBoxListToValidate = checkBoxListToValidate;
55.         }
56.  
57.         internal void ValidateCheckBoxList(object sender, ServerValidateEventArgs e)
58.         {
59.             bool isValid = false;
60.             int i = 0;
61.  
62.             while (i < _checkBoxListToValidate.Items.Count && !isValid)
63.             {
64.                 ListItem li = _checkBoxListToValidate.Items[i];
65.                 isValid = li.Selected;
66.                 i++;
67.             }
68.  
69.             e.IsValid = isValid;
70.         }
71.  
72.         private static CheckBoxList FindCheckBoxListRecursive(Control control, string id)
73.         {
74.             if (control == null) return null;
75.             //try to find the control at the current level
76.             CheckBoxList ctrl = control.FindControl(id) as CheckBoxList;
77.  
78.             if (ctrl == null)
79.             {
80.                 //search the children
81.                 foreach (Control child in control.Controls)
82.                 {
83.                     ctrl = FindCheckBoxListRecursive(child, id);
84.  
85.                     if (ctrl != null) break;
86.                 }
87.             }
88.             return ctrl;
89.         }
90.     }
91. }

Implementation:

1. <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="MyUserControl.ascx.cs" Inherits="MARE.Directory.UserControls.MyUserControl" %>
2. <%@ Register Src="../UserControls/CheckBoxListCustomValidator.ascx" TagPrefix="uc1" TagName="CheckBoxListCustomValidator" %>
3.  
4. <asp:CheckBoxList ID="cblMyCheckboxList1" RepeatDirection="Horizontal" RepeatColumns="4" runat='server' />        
5. <uc1:CheckBoxListCustomValidator runat="server" id="valcblMyCheckboxList1" ControlToValidate="cblMyCheckboxList1" ValidatorErrorMessage="Required" />
6.  
7. <asp:CheckBoxList ID="cblMyCheckboxList2" RepeatDirection="Horizontal" RepeatColumns="4" runat='server' />        
8. <uc1:CheckBoxListCustomValidator runat="server" id="valcblMyCheckboxList2" ControlToValidate="cblMyCheckboxList2" ValidatorErrorMessage="Required" />

Customize DotNetNuke 7 display for file upload controls

DNN7 enhances the file upload control (input type=”file”) with a button display that shows the name of the selected file.  Unfortunately, this control displays only a single file name even if multiple files are selected.  Internet Explorer 9 and earlier versions do not support multiple files but most other browsers do.

Rendered without the DNN7 enhancement:

<input type="file" multiple="multiple" name="dnn$ctr618$Dispatch$myControl$myUpload" id="myUpload">

Rendered with the DNN7 enhancement:

<span class="dnnInputFileWrapper">
    <span class="dnnSecondaryAction">Choose File</span>
    <input type="file" multiple="multiple" name="dnn$ctr618$Dispatch$myControl$myUpload" id="myUpload">
</span>

The method that enhances file upload controls is declared in dnn.jquery.js and is called $.fn.dnnFileInput.  This method can be replaced with a custom method to display multiple file names.  My customization is in the declaration of the $ctrl.change event handler.

1. (function ($) {
2.     "use strict";
3.  
4.     function dnnFileInput() {
5.         return this.each(function () {
6.             var $ctrl = $(this),
7.                 text = '',
8.                 btn = {};
9.  
10.             if (this.wrapper)
11.                 return;
12.  
13.             //if this.wrapper is undefined, then we check if parent node is a wrapper
14.             if (this.parentNode && this.parentNode.tagName.toLowerCase() === 'span' && this.parentNode.className === 'dnnInputFileWrapper') {
15.                 return;
16.             }
17.  
18.             this.wrapper = $("<span class='dnnInputFileWrapper'></span>");
19.             $ctrl.wrap(this.wrapper);
20.             text = $ctrl.data('text');
21.             text = text || 'Choose File';
22.             btn = $("<span class='dnnSecondaryAction'>" + text + "</span>");
23.             btn.insertBefore($ctrl);
24.  
25.             // display the full list of uploaded files
26.             $ctrl.change(function () {
27.                 var val = $(this).val(),
28.                     files = [],
29.                     i = 0,
30.                     max = 0,
31.                     fName = '',
32.                     lastIdx = 0;
33.  
34.                 if (val !== '') {
35.                     if (!this.files) {
36.                         files = [{ name: /([^\\]+)$/.exec(this.value)[1] }];
37.                     }
38.                     else {
39.                         files = this.files;
40.                     }
41.                     max = files.length;
42.                     for (i = 0; i < max; i += 1) {
43.                         fName += files[i].name + ', ';
44.                     }
45.                     lastIdx = fName.lastIndexOf(',');
46.                     val = fName.substring(0, lastIdx);
47.                 } else {
48.                     val = text;
49.                 }
50.                 $(this).prev().html(val);
51.             });
52.         });
53.     };
54.  
55.     $(document).ready(function () {
56.         $.fn.dnnFileInput = dnnFileInput;
57.     });
58.  
59. }(jQuery));

I call the above function from an external script file that is loaded in the page head.

Thanks to the following references which helped me display the uploaded file name in IE9 and earlier:

• Use the file input’s value property instead of files
• Extract just the filename from a full path in JavaScript