Jim's blog | .Net and SQL Server blog

Auto Feeding the Cat with Video Confirmation

by JBrooks 13. March 2015 11:38

From a schedule program I wrote on my laptop I wanted to turn on an electric wall outlet which would then turn on the cat’s auto feeder. The wife said she didn’t trust this to work, so she needed a video showing the cat being feed. 

So I combined all the Z-Wave tools I posted about here with some other C# libraries to capture sound and video.  I then wrote the code to have the video automatically uploaded to my server and then send my wife an email with a link to it.


You can see a sample video here. So here are the steps that it now follows:

1. The scheduler is scheduled to feed the cat 3 times during the day.

2. When it is time, the program turns on the laptop’s video and microphone.

3. The program sends a Z-Wave signal to the wall outlet to turn it on.

4. The program waits 30 seconds, then combines the video and sound.

5. The program uploads the new video to my web server.

6. It then sends out an email with a link to a page that will play the video.

I”m going to look into seeing if I can count the number of times it hears the sound of the food dinging the bowl as it falls. Then I could have the count in the subject of the email so it would be clear there was a problem if the count was low.

Also, I’m going to use my ChangeAlerts! program to send me a text message if a cat video isn’t uploaded within 5 minutes of its scheduled time. That is a hungry cat that cannot miss a meal!


Programming Your Home

by JBrooks 28. February 2015 11:09

I wanted to do some home automation and I wanted to write the code myself (I’m a software developer.) I looked into it and decided to use Z-Wave technology.   I started by buying a USB Z-Stick for about $45 to allow my laptop to talk to the different devices. This can control up to 232 devices and each device rebroadcasts your command so it has a far range.


Now I have this hanging off my laptop – not ideal but eventually I’ll be using a hidden old laptop in a closet to run this.

Next items needed were the Z-Wave devices themselves, you can get motion, light, and temperature sensors, etc.  And you can also get light switches and outlets that replace your normal ones.  So I installed the switch below so I can turn the front porch light on when it gets dark from a program running on my laptop. The switch is always in the neutral position, so if someone switches it by hand it goes back to the middle and it doesn’t move when changed through Z-Wave.

Switch Outlet

I have 2 of the outlets, one used by a light and one in the kitchen that I”m going to use to control a cat’s auto feeder (done – see here). You can buy all these items on Amazon.com.

Then I downloaded a library that would allow me to send commands to the Z-Stick from my programming language (C#). I used a library called OpenZWaveDotNet.  I had built a schedule for a different project, so I reused that code to schedule commands in this project. Then I’ll just have the program run forever…


Everything is working so far, if you can think of another use for this leave me a comment.


Scrolling in KnockoutJS

by JBrooks 7. January 2015 05:51

I have a KnockoutJS site (SupplyChaser.com ) where the user can change the order of the products by clicking an up or down arrow image when the product’s row is selected. See below.


The problem was getting the scrolling right if the move made the row go above or below the viewing area. If in the example above the user clicked the down arrow, the row would be move so it would be under the menu. So need a little code to scroll it back into view. Since it took way longer than it should have I thought I would post the code I ended up with.

First, KnockoutJS does the binding to the click event for us:

<td class="ControlTD"> <div data-bind="visible: AmISelected()"> <button data-bind="click: $root.editProduct">Edit</button> &nbsp;&nbsp; <img src="/Images/Site/Up.png" data-bind="click: $root.moveProductUp" /> Move <img src="/Images/Site/Down.png" data-bind="click: $root.moveProductDown" /> &nbsp;&nbsp; <img src="/Images/Site/Remove.png" data-bind="click: $root.removeProduct" /> </div> </td>
In my KnockoutJS model I have the functions that we bind to. Notice the first parameter passed to us is the object that is bound to the table row. The second parameter contains a property called “event.target” that will have a reference to the image that was just clicked.


self.moveProductDown = function (Product, event) { /*.... code to reorder the array here .... */ scrollIntoView(event.target);

And now the function I had to play with to get right. Obviously this uses JQuery. The hard coded values are based on how big my top and bottom menu’s are. This works better than I expected because the animate shows the page scrolling to the new position.

function scrollIntoView(element) { var elemTop = $(element).offset().top if (elemTop == 0) return; var windowTop = $(window).scrollTop(); var windowBottom = windowTop + $(window).height(); if (elemTop < windowTop + 190) { elemTop -= 190; } else if (elemTop > windowBottom - 220) { elemTop = elemTop - $(window).height() + 220; } else return; $('html, body').animate({ scrollTop: elemTop }); }


Recording Deletes When Using Entity Framework

by jbrooks 22. September 2014 06:30

I have all the changes archived to archive table using triggers, but this doesn’t record anything when a row is deleted. I created a dedicated table to record the deletes.


I then added this to my data model and add a record to it right after I do a remove.

db.Areas.Remove(area); db.Deletes.Add(new Delete() { deletedId = id, tableName = "Areas", editBy = this.User.Identity.Name, edited = DateTime.Now });


Generating Archive Tables and Triggers

by jbrooks 18. September 2014 09:19

I create archive tables to log all of the changes made to a table. The archive tables look like the base table with a few columns added. Then the base tables need a triggers to insert the old row right before a change. I used the stored proc below to generate the archive tables and triggers. You need a schema called “archives” for this to work.

create proc CreateArchive(@tablename nvarchar(200), @schema nvarchar(200) = 'dbo') as begin declare @sql nvarchar(4000) set @sql = ' create table archives.'+@tablename+'Arc ([archiveId] [int] IDENTITY(1,1) NOT NULL, [archiveAction] [char](1) NOT NULL, [archiveBy] [varchar](128) NULL, [archived] [datetime] NULL, ' select @sql = @sql + '['+c.COLUMN_NAME+'] ' + + c.DATA_TYPE + CASE WHEN c.CHARACTER_MAXIMUM_LENGTH > 0 THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')' WHEN c.CHARACTER_MAXIMUM_LENGTH = -1 THEN '(MAX)' ELSE '' END + ' NULL, ' from INFORMATION_SCHEMA.COLUMNS c where TABLE_NAME = @tablename and TABLE_SCHEMA = @schema order by c.ORDINAL_POSITION set @sql = @sql + 'CONSTRAINT [PK_'+ @tablename + 'Arc] PRIMARY KEY CLUSTERED ( [archiveId] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]; ALTER TABLE [archives].[' + @tablename + 'Arc] ADD CONSTRAINT [DF_' + @tablename + 'Arc_archiveBy] DEFAULT (user_name()) FOR [archiveBy]; ALTER TABLE [archives].[' + @tablename + 'Arc] ADD CONSTRAINT [DF_' + @tablename + 'Arc_archived] DEFAULT (getdate()) FOR [archived]; grant all on [archives].[' + @tablename + 'Arc] to public;' print @sql exec sp_executesql @sql set @sql = ' Create trigger [' + @schema + '].[t' + @tablename + 'Arc] on [' + @schema + '].[' + @tablename + '] for update, delete as begin set nocount on declare @Action char(1) if exists (select 1 from inserted) set @Action = ''U'' else set @Action = ''D'' insert into archives.[' + @tablename + 'Arc] (archiveAction' select @sql = @sql + ', ['+c.COLUMN_NAME+']' from INFORMATION_SCHEMA.COLUMNS c where TABLE_NAME = @tablename order by c.ORDINAL_POSITION set @sql = @sql + ') select @Action' select @sql = @sql + ', ['+c.COLUMN_NAME+']' from INFORMATION_SCHEMA.COLUMNS c where TABLE_NAME = @tablename order by c.ORDINAL_POSITION set @sql = @sql + ' from deleted end;' print @sql exec sp_executesql @sql end go


Here are some of the scripts I used to help with this:

-- create the archives schema create schema archives -- Generate the commands that call the stored proc for each table: select 'exec CreateArchive '+TABLE_NAME + ';' from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' order by TABLE_NAME -- drop the tables if I got an error: select 'drop table archives.'+TABLE_NAME + ';' from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'archives' order by TABLE_NAME -- drop the triggers if I got an error: select 'drop trigger '+name + ';' from sys.triggers where create_date > getdate() - 1 order by name -- see what object were last changed in the database select * from sys.objects order by modify_date desc


Generating Indexes for Foreign Keys

by jbrooks 18. September 2014 09:03

Surprisingly SQL Server doesn’t automatically create indexes just because you have a foreign key. So at the end of a development project I just used the script below to generate the SQL needed.

select 'create index indx_'+t.name +'_' + c.name + ' on ' + t.name +'(' + c.name + ');' from sys.foreign_key_columns as fk inner join sys.tables as t on fk.parent_object_id = t.object_id inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id where not exists (select 1 from sys.index_columns ic where ic.object_id = t.object_id and ic.column_id = c.column_id) order by 1

This returns the commands that I can then run to create the indexes.

create index indx_ActivityLog_activityTypeId on ActivityLog(activityTypeId);
create index indx_ActivityLog_userId on ActivityLog(userId);
create index indx_Areas_managerId on Areas(managerId);
create index indx_Categories_areaId on Categories(areaId);
create index indx_CriteriaItems_criteriaColorId on CriteriaItems(criteriaColorId);


SQL | Development

Silverlight Page Titles

by jbrooks 9. September 2013 17:21

In the past I would use part of the page for the page title while the MainPage had a lot of empty space in the center.  I wanted to move the title from the page to the MainPage as shown below.



To do this I first made a property on MainPage like this:

public partial class MainPage : UserControl
    public string Title
        get { return tbTitle.Text; }
        set { tbTitle.Text = value; }

Then I added this property to the App class:

public partial class App : Application
    public static string Title
            MainPage mp = App.GetMainPage();
            mp.Title = value;

A supporting method in this same App class is GetMainPage() where it gets the MainPage. I use this for other things too.

public static MainPage GetMainPage()
    if (Application.Current.RootVisual.GetType().Name == "BusyIndicator")
        return (MainPage)((ChangeAlerts.Controls.BusyIndicator)
        return (MainPage)Application.Current.RootVisual;

Then on each individual page I have something like this:

protected override void OnNavigatedTo(NavigationEventArgs e)
    App.Title = "Manage Accounts";


Silverlight | Development

Binding a Silverlight DataGrid’s ComboBox To a Model

by JBrooks 31. July 2013 11:49

I have a DataGrid that is bound to my Employee entity and I needed to be able to select his building from a list of buildings in that same model. There is a path to the Employee’s Building through buildingId, but no path to the full list of buildings so it could the source for the ComboBox.

The simple solution was to have the Buildings in a ViewModel and then reference that as a resource in my XAML.  The ViewModel retrieves the data in it’s constructor and it has a property EntitySet<Building> Buildings;

        <vm:BuildingsModel x:Key="buildingsModel"  />

Then on the CheckBox inside the <DataTemplate> I have its ItemSource use the resource.

<ComboBox Name="cb" ItemsSource="{Binding Source={StaticResource buildingsModel}, Path=Buildings}" 
    SelectedValue="{Binding Path=buildingId, Mode=TwoWay}">
            <StackPanel Orientation="Horizontal">
                <TextBlock Text="{Binding name, Mode=OneWay}" MinWidth="140" />
                <TextBlock Text="  " />
                <TextBlock Text="{Binding address, Mode=OneWay}" />
                <TextBlock Text=", " />

                <TextBlock Text="{Binding city, Mode=OneWay}" />
                <TextBlock Text=", " />
                <TextBlock Text="{Binding state, Mode=OneWay}" />


Development | Silverlight | XAML

Silverlight and Window Authentication

by JBrooks 4. June 2013 08:38

I deployed my Silverlight application with Windows Authentication.  I then set IIS to only use Windows Authentication. When I navigated to the web site I would get the error:

“Load operation failed for query ‘GetUser’. The remote server returned an error: NotFound.”


The fix that worked for me was to edit the file MyProject.Web/Services/AuthenticationService.cs.  I just changed this one method.

public class AuthenticationService : AuthenticationBase<User> {

    protected override User GetAuthenticatedUser(System.Security.Principal.IPrincipal principal)
        User newUser = new User();
        newUser.Name = principal.Identity.Name;

        return newUser;

This makes sense if you think about it, but the base class AuthenticationBase should have detected the authentication used and changed based on that.


Development | Silverlight

Show Child Count in a DataGrid

by jbrooks 11. March 2013 17:49

I have an application with the following as part of the data model:


So an Account can have many Users and a user can belong to many Accounts. I wanted to show the Accounts in a DataGrid along with the number of Users that were linked to that account like show in the 3rd column here:


The way I did this is to use a converter to count the number of entities in each Account.xrefAccountsUser set.  The DataGrid is bound to Accounts and the the XAML for the 3rd column is:

<sdk:DataGridTextColumn  Header="Users" 
Binding="{Binding xrefAccountsUsers, Converter={StaticResource setToCountConverter}}"

The converter is where we count up the number of entities in the related entity set (xrefAccountsUsers in this case.)  I could not find a type that I could cast the value to that had a Count property, so I  ended up casting it to IEnumerable and then loop thru the set and I do a count manually.  There should never be very many accounts so performance isn’t an issue.

public class SetToCountConverter : IValueConverter
    public object Convert(object value,
                                Type targetType,
                                object parameter,
                                System.Globalization.CultureInfo culture)
        int cnt = 0;
        IEnumerable set = value as IEnumerable;
        if (set == null)
            return "0";
        foreach (var item in set)
        return cnt.ToString();
    public object ConvertBack(object value,
                                Type targetType,
                                object parameter,
                                System.Globalization.CultureInfo culture)
        throw new NotImplementedException();


Silverlight | Development | XAML