Jim's blog | .Net and SQL Server blog

I Love Idea Paint

by JBrooks 21. September 2016 16:23

We just received our Idea Paint that we are going to put on one of the walls in our new workout room. This reminded me of when we painted the main conference room in the office building I worked in.  It was a big conference room with 2 little white boards that always had “Do Not Erase” written on them and a silly little paper easel. So I did some research and here is what we did.

Bought some Idea paint (still the best as of this writing, but warning – the smell will make you want to work somewhere else for about 4 days.)

IdeaPaint

 

Click here to see current pricing.  My wife, daughter and I took a Friday after work and that Saturday to paint the conference room walls, which had a nice cured corner.

Start

 

Truth is, my wife (the artist in the family) did most of the work.

JenWork

 

Once done, it was a giant white board!  Couldn’t wait to put up my process flow diagrams on it.

Finisheed

 

I didn’t tell anyone I was doing this mind you, I just did it. A few complaints about the smell into the next Tuesday. Everyone loved it to the point where people from our company were coming over from another building to have meetings in the room just to use the white board. My (consulting) company did give me a big gift card as a thank you.

A few weeks later it was full.

Used

I guess we will use it in the new weight room to track our workout progress…. hmmm.  We will see how that works out, I still highly recommend it for the office. Click here to see Idea Paint on Amazon.

Tags:

SQL Server 2005 to 2012 Differences for Developers

by JBrooks 14. November 2015 12:24

We are upgrading from SQL Server 2005 to 2012. There are a lot of features for developers that have been added over those 7 years.  Here are some you might find useful.


1. You can assign a value to a variable when you declare it.

DECLARE @Status varchar(50) = 'Success', @Today Date = Getdate()


2. You can insert multiple rows without needing to repeat the column list

INSERT INTO MyTable ( ID, LastName ) VALUES ( 100, 'Smith' ), ( 200, 'Jones' ), ( 300, 'Watson' )

(Up to 1,000 sets.)


3. Among the new data type there are DATE and TIME types.

No more having to use a DateTime when all that you really want is a date.

DECLARE @Today Date = Getdate()


4. IIF() function

SELECT IIF(1 > 10, 'TRUE', 'FALSE' )

4

You can use the IIF() with an IN

DECLARE @LastName Varchar(50) = 'Smith' SELECT IIF(@LastName IN ('Smith','Jones'), 'Likes SQL', 'Likes ORACLE' )

4B



5. CHOOSE() function

CHOOSE ( index, val_1, val_2 [, val_n ] ) SELECT CHOOSE(3,'Spring','Summer','Autumn','Winter')
5

6. CONCAT() function

CONCAT( string1, string2 [,stringN])

- All arguments are converted to a string first

- All nulls are converted to an empty string

DECLARE @FirstName VARCHAR(20) = 'Bob', @LastName VARCHAR(20), @Age int = 30 SELECT CONCAT(@FirstName, ' ', @LastName, ' is ', @Age)
6

7. You have the “C” like short cuts +=, -=, *=, /= and %=

DEClARE @x INT = 2, @y INT = 2 SET @x += @y SELECT @x as x, @y as y

7



8. Paging is now much easier with OFFSET and FETCH NEXT

DECLARE @PageNumber int = 4, @PageSize int = 40 SELECT WNumber, AuditTime, AuditTypeCode, ActivityCode FROM tb_AuditLog ORDER BY AuditTime DESC OFFSET (@PageNumber1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;

9. FORMAT() function

SELECT FORMAT( GETDATE(), 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result', FORMAT(123456789,'###-##-####') AS 'Custom Number Result';
9


10. TRY_PARSE()and TRY_CONVERT() functions

IF TRY_PARSE('xxx' as DATETIME) IS NOT NULL SELECT 'TRY_PARSE: Conversion Successful' ELSE SELECT 'TRY_PARSE: Conversion Unsuccessful'
10 


11. You can now create your own types in the database (just like VARCHAR and INT are types.)  You can even define a table type.

CREATE TYPE IdsTable AS TABLE ( Id INT );

(Could have many more columns.)

You can then pass that type as a parameter to a stored procedure.

CREATE PROCEDURE GetSelectedAccounts @MyIds IdsTable READONLY AS SELECT * FROM Accounts WHERE AccountID IN (SELECT Id FROM @MyIds) GO

You can also pass the table in as a parameter from C#.

DataTable myIds = new DataTable(); myIds.Columns.Add("Id", typeof(Int32)); myIds.Rows.Add(100); myIds.Rows.Add(110); myIds.Rows.Add(2200); myIds.Rows.Add(40); dbCommand.Parameters.Add(new SqlParameter("@MyIds", myIds) { SqlDbType = SqlDbType.Structured }); DataSet ds = (DataSet) Db.ExecuteDataSet(dbCommand);

12. TRY CATCH THROW

BEGIN TRY DECLARE @Number int = 5 / 0; END TRY BEGIN CATCH -- Log the error info, then re-throw it INSERT INTO ErrorLog VALUES(SYSDATETIME(), ERROR_SEVERITY(), ERROR_MESSAGE()); THROW; END CATCH
When you re-throw it keeps the correct line number of the error.

This can also be used with transactions.

BEGIN TRY BEGIN TRANSACTION UPDATE #TestingTHROWCommand SET OvertimeAmount=95/OvertimeAmount COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION; THROW END CATCH

13. New virtual Windows FileTable commands in T-SQL lets you manage a folder like a table of documents, but still have external control over the contents

UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = ‘Bob’ AND Created <20100101';

14. Reporting Services no longer requires IIS, it makes direct calls to HTTP.SYS.




15. Now have Oracle like sequences

CREATE SEQUENCE MySequence START WITH 1 INCREMENT BY 1; INSERT @Person (ID, FullName) VALUES (NEXT VALUE FOR MySequence, 'Steve Luna'), (NEXT VALUE FOR MySequence, 'John Peter'), (NEXT VALUE FOR MySequence, 'Jim Brooks');

Tags:

Development | Silverlight

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.

Capture

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!

Tags:

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.

ZStick

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…

Schedule

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

Tags:

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.

image

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

Tags:

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.

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

Tags:

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

Tags:

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

Tags:

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.

image

 

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

Tags:

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;

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

Tags:

Development | Silverlight | XAML