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>

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

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.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Deletes](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [tableName] varchar(255) NOT NULL,
    [deletedId] [int] NOT NULL,
    [editBy] [varchar](50) NOT NULL,
    [edited] [datetime] NOT NULL,
 CONSTRAINT [PK_Deletes] PRIMARY KEY CLUSTERED 
(
    [id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Deletes] ADD  CONSTRAINT [DF_Deletes_editBy]  
    DEFAULT (user_name()) FOR [editBy]
GO

ALTER TABLE [dbo].[Deletes] ADD  CONSTRAINT [DF_Deletes_edited]  
    DEFAULT (getdate()) FOR [edited]
GO

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

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

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

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
    {
        set
        {
            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)
            Application.Current.RootVisual).Content;
    else
        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";
}

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;

    <UserControl.Resources>
        <vm:BuildingsModel x:Key="buildingsModel"  />
    </UserControl.Resources>

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

<ComboBox Name="cb" ItemsSource="{Binding Source={StaticResource buildingsModel}, Path=Buildings}" 
    SelectedValuePath="id" 
    SelectedValue="{Binding Path=buildingId, Mode=TwoWay}">
    <ComboBox.ItemTemplate>
        <DataTemplate>
            <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}" />
            </StackPanel>
        </DataTemplate>
    </ComboBox.ItemTemplate>
</ComboBox>

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.

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)

            cnt++;

        return cnt.ToString();

    }

    public object ConvertBack(object value,

                                Type targetType,

                                object parameter,

                                System.Globalization.CultureInfo culture)

    {

        throw new NotImplementedException();

    }

}

I have an application where the administrator can edit a user’s roles.

I wanted to reuse this same grid to show the user what roles they have, but I didn’t want them to be able to change the data. There is a IsReadOnly property on each of the columns, but this still allows the user to change the checkboxes.

If I set the IsEnabled property on the datagrid to false, then the user can’t change the checkboxes but the datagrid looks washed out and hard to read.

So this is too washed out to go with.  I realized that another way to disable CheckBoxes is from the style:

<sdk:DataGrid.Resources>

    <Style TargetType="CheckBox">

        <Setter Property="IsThreeState" Value="False" />

        <Setter Property="HorizontalAlignment" Value="Center" />

        <Setter Property="VerticalAlignment" Value="Center" />

    </Style>

</sdk:DataGrid.Resources>

I wanted to see if there was a way to programmatically add a setter to this and there was.

// find the CheckBox style resource.

var resource = dgAccounts.Resources.FirstOrDefault(x => x.Key == typeof(CheckBox));

if (resource.Key != null)

{

    Style s = resource.Value as Style;

    (s.Setters as SetterBaseCollection).Add(new

    Setter(CheckBox.IsEnabledProperty, value));

}

In the code above, “value” can be true or false.  This gives me a little better presentation:

But, hey, this is Silverlight – why am I using those dinky check boxes anyway.  The better solution is to create a user control that acts like a checkbox and you can style it as big as you want.

And then the code to set the disable style just needs to change the type it is looking for on the 1 line:

var resource = dgAccounts.Resources.FirstOrDefault(x => x.Key == typeof(ucCheckBox));

Now, the disabled version is a lot more readable.

Silverlight has an annoying bug that has been around since at least 2008. If you have a MessageBox popup in a DatePicker’s SelectedDateChanged event, then the calendar will stay open and the SelectedDateChanged will fire for each time the mouse pointer hovers over a new date.  See HERE for someone asking for help on this and their sample code.

It happens because the DatePicker still flags the left mouse button as being down – the up button event was eaten by the message box.

I have a page where the user works on data for a given day, and if they change the date the page will be refreshed with the new day’s data.

My work flow was that if a user changed data for a day and then changed the date, I would prompt them with “Save Changes?” – Yes, No, Cancel.  If they selected Cancel I would just set the DatePicker’s date back. 

No matter what they selected the DatePicker’s calendar would remain down and the SelectedDataChanged would fire for each date that was hovered over.

I didn’t like any of the answers to this problem that I found.  So I changed the workflow so that the prompt to save changes wasn’t necessary anymore.  So this is the new workflow.

1. New page, you can change the date, refresh the data, or start editing the data.

2. The data was changed. Now changing the date is disabled until you make a choice between Save and Cancel. I like how this visibly tells the user that the data is dirty.

This is done by capturing the KeyUp event for the parent control that holds all of the editing controls. In my example it is a ContentControl but it could be a grid, etc.  Note that the control’s in the images above are outside of my ContentControl for the data.

private void ccData_KeyUp(object sender, System.Windows.Input.KeyEventArgs e)
        {
            switch (e.Key)
            {
                case Key.Left:
                case Key.Right:
                case Key.Down:
                case Key.Up:
                case Key.Tab:
                    break;

                default:
                    if (Status != PageStatus.Changed)
                    {
                        Status = PageStatus.Changed;
                    }
                    break;
            }
        }

When the Status property gets set the showing, hiding and disabling of controls takes place depending on the new value of PageStatus.

If the user clicks “Save” then the data is saved, the page is refreshed and they are back at step 1 with the date selection enabled. If they click “Cancel”, then I go to step 3.

3. They clicked “Cancel”.  Now they are free to change the date which pulls up another day’s data and throws away their changes (step 1).   Or they can continue editing, which puts them back to step 2.  Or they can click Refresh which will pull the old data from the database and the page will be back to step 1.

I like doing it this way so I don’t have to mess with the SelectedDateChanged event and I like the visual cues.