How to Embed Code Snippets in Blogger

Ok so previously I used SyntaxHighlighter to do this but have noticed that has stopped working on some of my blog posts.

So I had a look for another solution and came across Gist from GitHub. You can save snippets of code on there site as a public file and then embed them into your blog and they look great.

Try it out. https://gist.github.com/

How To Permanently Link Arrow Keys to a ScrollBar using JQuery

Here is a little snippet of JQuery code to link your Up and Down arrow Keys to a specific scroll bar to make it work even if the element it is attached to is not in focus.


This uses the JQuery animate() function attached to the element that contains the scroll bar.
You then provide to the scrollTop porpery the current position of the scroll bar plus or minus a value you want to move it by.

Auto Generate C#.net Class from Database Table

So I was looking for a way to auto generate a basic C#.net object class based on a SQL Server database table and found this post on Stack Overflow.

http://stackoverflow.com/questions/5873170/generate-class-from-database-table

Thought the answer was very handy, below was the code that was provided by user Carnotaurus, copied here below for future reference.

DECLARE @TableName VARCHAR(MAX) = 'NewsItem'
DECLARE @TableSchema VARCHAR(MAX) = 'Markets'
DECLARE @result varchar(max) = ''

SET @result = @result + 'using System;' + CHAR(13) + CHAR(13) 

IF (@TableSchema IS NOT NULL) 
BEGIN
    SET @result = @result + 'namespace ' + @TableSchema  + CHAR(13) + '{' + CHAR(13) 
END

SET @result = @result + 'public class ' + @TableName + CHAR(13) + '{' + CHAR(13) 

SET @result = @result + '#region Instance Properties' + CHAR(13)  

SELECT @result = @result + CHAR(13) 
    + ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13) 
FROM
(
    SELECT  c.COLUMN_NAME   AS ColumnName 
        , CASE c.DATA_TYPE   
            WHEN 'bigint' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int64?' ELSE 'Int64' END
            WHEN 'binary' THEN 'Byte[]'
            WHEN 'bit' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Boolean?' ELSE 'Boolean' END            
            WHEN 'char' THEN 'String'
            WHEN 'date' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                        
            WHEN 'datetime' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                        
            WHEN 'datetime2' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                        
            WHEN 'datetimeoffset' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END                                    
            WHEN 'decimal' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                    
            WHEN 'float' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Single?' ELSE 'Single' END                                    
            WHEN 'image' THEN 'Byte[]'
            WHEN 'int' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int32?' ELSE 'Int32' END
            WHEN 'money' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                
            WHEN 'nchar' THEN 'String'
            WHEN 'ntext' THEN 'String'
            WHEN 'numeric' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                            
            WHEN 'nvarchar' THEN 'String'
            WHEN 'real' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Double?' ELSE 'Double' END                                                                        
            WHEN 'smalldatetime' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                    
            WHEN 'smallint' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int16?' ELSE 'Int16'END            
            WHEN 'smallmoney' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                                        
            WHEN 'text' THEN 'String'
            WHEN 'time' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END                                                                                    
            WHEN 'timestamp' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                    
            WHEN 'tinyint' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Byte?' ELSE 'Byte' END                                                
            WHEN 'uniqueidentifier' THEN 'Guid'
            WHEN 'varbinary' THEN 'Byte[]'
            WHEN 'varchar' THEN 'String'
            ELSE 'Object'
        END AS ColumnType
        , c.ORDINAL_POSITION 
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA  
) t
ORDER BY t.ORDINAL_POSITION

SET @result = @result + CHAR(13) + '#endregion Instance Properties' + CHAR(13)  

SET @result = @result  + '}' + CHAR(13)

IF (@TableSchema IS NOT NULL) 
BEGIN
    SET @result = @result + CHAR(13) + '}' 
END

PRINT @result

SSRS Web Service Insufficient Permissions

Error: System.Web.Services.Protocols.SoapException: The permissions granted to user are insufficient for performing this operation

I had this error recently when trying to connect to a SSRS Report Server vis Web Services using the code below.

rsExec = new ReportExecutionService();
//Get App setting valuesstring authenticationMode = "Windows";

string serverUrl = "http://server/ReportServer";
string userName = "username";
string password = "password";
string domain = "domain";

rsExec.Url = serverUrl.TrimEnd('/') +"/ReportExecution2005.asmx";

// Need to do different things depend on the authentication mode.
if (authenticationMode == SSRSAuthenticationModeEnum.Windows.ToString())
       rsExec.Credentials = CredentialCache.DefaultCredentials;
else
       rsExec.Credentials = new NetworkCredential(userName, password, domain);

ExecutionInfo ei = rsExec.LoadReport(reportPath, null);
rsExec.SetExecutionParameters(parameters, "en-us");
result = rsExec.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

The issue was that I was using "Windows" authentication and my SSRS Server was on a seperate machine to my web server. Once I changed my Authentication mode to "Forms" and provided some admin credentials it worked fine.

I beleive the issue only happens if you have multiple server environments. If the SSRS Server and Web Server are on the same machine I don't get this issue.

For reference I also found this blog post which although not helpful for me seems like it might be helpful for others.

http://techygypo.blogspot.co.uk/2011/06/systemwebservicesprotocolssoapexception.html

Connect to a SSRS Web Service from .Net

This is relatively straight forward. You have a SQL Server Reporting Services server set up somewhere with some Reports on it. Now you want to be able to connect to that Reporting Server from a .Net application to be able to dynamically interact with those reports. In my case I want to be able to run a Report and get back a PDF version of the Results.

First things first you need to connect to the Web Service and to do this you need to add a Reference to the Web Service that is exposed by Reporting Server. Select your Project in Visual Studio, Right Click and select Add Web Service.

In later versions of Visual Studio you will find you can only add Service References, but on the Add Service Reference screen if you select Advanced Options you get the option there to add a Web Reference. Note either way of doing this is fine but I just chose the Web Reference because that directly exposes access to the ReportExecutionService class and thats what we need.

Find the URL of the web service you want to use, this can be found by going to your ReportServer and adding the Web Service address onto the root URL. There are two you can use, the ReportService Web Service has a number of Report Mangement methods on it. The ReportExecution Web Service has a number of processing methods on it.You could add Web References to both into your project if you want to as you most likely would need to use both at some point.

http://server/ReportServer/ReportService2010.asmx?wsdl
http://server/ReportServer/ReportExecution2005.asmx?wsdl

Then in your code you can just create the following to be able to interact with the Web Service methods. Set a web service URL so that you can dynamically change which server you are pointing the code at and set some Credentials.

I will put up a seperate post soon with an example of using this web service to run a Report and retrieve a PDF version of the result within your .Net Application.  

SQL Server - Template Script for Looping Throught Table Without Using Cursor

Below is a single SQL Script I put together to use as a template whenever I need to loop through an SQL Table to then perform operations per row on certain data I can do it using this script.

The template is simple to understand it basically uses a temp table to copy into it the data you are interested in and then loop through it. The template then shows how to use the value you get from each row by just printing it to the screen. Of course you can replace that with any other code you want to.

-- Created 08/04/2010 by Matt Harrison
-- Template for Looping through table rows without using a cursor
------------------------------------------------------------------

DECLARE @RowCnt int
DECLARE @MaxRows int
SET @RowCnt = 1

DECLARE @TempTableName Table (rownum int IDENTITY (1, 1) PRIMARY KEY NOT NULL , Value_Name1 nvarchar(10))

-- Populate the TempTable
INSERT INTO @TempTableName (Value_Name1) (SELECT Value_Name1 FROM DatabaseTableName)
SET @MaxRows = (SELECT count(*) FROM @TempTableName)

-- Foreach Row in TempTableName
WHILE @RowCnt <= @MaxRows
BEGIN

 DECLARE @Value_Name nvarchar(10)
 SET @Value_Name = (SELECT Value_Name1 FROM @TempTableName WHERE rownum = @RowCnt)
 
 PRINT 'Row Value_Name ' + @Value_Name
 
 -- Next Row
 SET @RowCnt = @RowCnt + 1
END

Reset Service Broker Quene in SQL Server

I find from time to time when using a Service Broker Queue in SQL Server it just stops and messages just sit in the queue. I have found that the script below forces something to get reset that springs the queue back into life. It's ages since I found this script so can't even remember why it works, but it usually does.

ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'Password';

Where "Password" can be set to any suitable password.

Reseed an SQL Table Identity Column

If you get an unexpected error like this...

Violation of PRIMARY KEY constraint 'PK_Name'. Cannot insert duplicate key in object 'Table_Name'.

You look at your table and the primary key and realise that the primary key that was complianing was a indentity column then you think well isn't that a bit odd. The point of an identity column is that you insert new rows in a database and it generates a primarykey value for you, usually a unqiue integer value.

Sometimes though for some mystic unknown reason (one that I can't be bothered to find out the real cause for) the identity value that is created is invalid. So whats the solution?

DBCC CHECKIDENT

You can use this SQL command to check what the current status of an identity column on a table is. If you run the below command, it will tell you the current identify value, i.e. the next value that would be generated as a unquire identity value, and it will tell you the current column value, ie. the current highest unqiue identity value used in your database.

DBCC CHECKIDENT ("Table Name", NORESEED);

Now if it returns something like this then you may have a problem.


Checking identity information: current identity value '10', current column value '17'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If the current column value is higher than the current identity value then the problem may be that you are trying to insert a new row and the identity value being inserted automatically is '10' when the rows have already been created for identity values up to '17'.

To fix this just run this command to reset the identity value.

DBCC CHECKIDENT ("Table Name", RESEED);

Then run the previous command again and you should see this result.

Checking identity information: current identity value '17', current column value '17'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Sorted.

Make ‘Folders View’ the Windows Explorer Default in Windows XP

Here is a link to a very handy blog post on how to make folders view in the explorer bar, in windows explorer default to folders view, instead of that silly blue common tasks bar.

http://www.daleisphere.com/make-folders-view-the-windows-explorer-default-in-windows-xp/

Great!

How to create your own Code Signing Certificates

This has been a real pain for me recently. At work we had previously got code signing certificates from a certificate authority called Thawte. But when it came time to renew, they have been totally unhelpful, slow and inept. We have spent a month dealing with them trying to get a renewed certificated with no joy, so they can now go shove a banana because we are going to sort out our own certificates.

There is a great step by step guide in this forum discussion on channel9 that will help you set up your own code signing certificate.

http://channel9.msdn.com/forums/TechOff/13583-Generating-my-own-Software-Publishing-Certificate/

It's a fiddly process but worth it if all you need is a certificate to sign your code for use within your own network.

Using SyntaxHighlighter to display code snippets

Today I found a very handy javascript to display code snippets in web pages, and it can be adapted to work in Blogs as well.

The SyntaxHighlighter is very clever, and can display a lot of different code types, for example C#.

//Here is some C# Code
public void Method()
{
   string test = "display some code in my blog";
   test = test.tostring() + "and format it nicely";
}

There is a nice blog post by Arun K Viswanathan on how to easily adapt the code to work in Blogger. Check it out.

Drop Constraint in MS SQL that has an unknown name

Now when I say unknown name I catually mean I know constraint I want to drop, however the name of the constraint was automatically generated by SQL Server and that name contains a random number e.g.

"DF__user__15502E78"

Well I could just write a script like this to get rid of it:

alter table [user]
drop constraint [DF__user__15502E78]

Fine that works, but what if I have created multiple version of that DB on different servers, each time SQL Server creates that constraint for me automatically it will contain a different random number in the name. So now when I run the script I worte above on a different server it fails.

So I wrote this little script below, that will search out the constraint and drop it for me. Now you will have to tweak this to get it to work for you as you may have more constraints as I ad in my DB, so you will need to alter the WHERE clause a bit to just get back the constraint name you are looking for.

-- Because this constraint is dyncamically created by SQL Server
-- the name of the constraint contains a random number
-- so the name will be different from DB to DB.
-- So the only way to drop the constraint is to use the script below.
DECLARE @constraint varchar(100)
SET @constraint = (SELECT OBJECT_NAME(OBJECT_ID) --AS NameofConstraint,
            --SCHEMA_NAME(schema_id) AS SchemaName,
            --OBJECT_NAME(parent_object_id) AS TableName,
            --type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
    AND OBJECT_NAME(parent_object_id) = 'user'
    AND type_desc <> 'PRIMARY_KEY_CONSTRAINT')

print @constraint

declare @sqlscript varchar(200)

set @sqlscript = 'alter table [user] drop constraint ' + @constraint

print @sqlscript

exec(@sqlscript)

If you look at the select clause in the script above that is what i'm using to return the name of the constraint, you can uncomment the values in that select statement and just run and tweak that to get it just right for you and then insert it back into the rest of the script and your all sorted.

Script Debugging in IE

Refer to this nice Blog post here: http://blogs.msdn.com/ie/archive/2004/10/26/247912.aspx

How to display HTML code in your Blogger Post

Convert the code to display in a blog post using this tool:

http://www.accessify.com/tools-and-wizards/developer-tools/quick-escape/default.php

Sorting and Paging a GridView with Custom DataSource in ASP.Net

So it's pretty simple to use a GridView in ASP.Net you can bind it to all sorts of data, but what I like doing is binding it to collections of my own custom objects. But this causes issues, because then you can't use all the nice in built paging and sorting without extra cosing. If you use the GridView wizard are hook it up to a SQLdatasource directly or a data set then all the paging and sorting is handled for you. But what if like me you want to use your own custome objects.

Well there is a way, it does require a little extras coding but it's nothing too complicated. Firstly lets look at the code to set up a standard GridView:

Client Side Code:

<asp:gridview id="GridView1" runat="server" autogeneratecolumns="False" onrowdatabound="GridView1_RowDataBound" onselectedindexchanging="GridView1_SelectedIndexChanging">

   <columns>

       <asp:boundfield datafield="ProjectID" headertext="ProjectID"/>

       <asp:boundfield datafield="ProjectName" headertext="ProjectName"/>

       <asp:boundfield datafield="Sector" headertext="Sector"/>

       <asp:boundfield datafield="ProjectStatus" headertext="Project Status"/>

   </columns>

</asp:gridview>

Server Side Code:

IList<project> projectList = Project.GetAllProjects();
GridView1. DataSource = projectList;
GridView1.DataBind();

Project Class:

public class Project
{

public Project()
{
}

public int ProjectID { get;set;}

public string ProjectName {get;set;}

public string Sector {get;set;}

public string ProjectStatus {get;set;}

static public Project GetAllProjects()
}

So this will display my list of custom objects in a simple GridView but I can't do paging or sorting, bummer. So what do we have to change to get it to work?

Well first on the ASP.Net page we add an ObjectDataSource control and point that to our datasource. This will help make our custome data collection more compatible with the GridView. We can then point out GridView at this ObjectDataSource.

We can then set paging on the GridView and Sorting. But for the sorting to work we still need some extra code. We need to make our custom data class comparable. So in the GetAllProjects method we add a parameter that will handle the sortExpression, we add this into the code of our GridView as well.

Then we add a switch statement in here to handle this sort expression. This requires us also to create a comparer class, we will call it ProjectComparer and in there we place code to handle the comparisons for each project value.


Client Side Code:

<asp:ObjectDataSource ID="dsProjects" runat="server" DataObjectTypeName="Business.DomainLogic.Project" TypeName="Business.DomainLogic.Project" SelectMethod="GetAllProjects" SortParameterName="sortExpression"></asp:ObjectDataSource>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
       OnRowDataBound="GridView1_RowDataBound" DataSourceID="dsProjects"
       AllowPaging="True" PageSize="8" AllowSorting="True"
       OnSelectedIndexChanging="GridView1_SelectedIndexChanging"
       OnPageIndexChanging="GridView1_PageIndexChanging">
       <Columns>
           <asp:BoundField DataField="ProjectID" HeaderText="ProjectID"  />
           <asp:BoundField DataField="ProjectName" HeaderText="ProjectName" SortExpression="ProjectName" />
           <asp:BoundField DataField="Sector" HeaderText="Sector" SortExpression="Sector" />
           <asp:BoundField DataField="ProjectStatus" HeaderText="Project Status" SortExpression="ProjectStatus" />
       </Columns>
   </asp:GridView>

Server Side Code:

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
       {
           GridView1.PageIndex = e.NewPageIndex;
           GridView1.DataBind();
       }

Project Class:

public List<project> GetAllProjects(string sortExpression)
{
      List<project> projects = new List<project>();

      switch (sortExpression)
           {
               case "ProjectName":
               case "ProjectName ASC":
                   projects.Sort(ProjectComparer.CompareByName);
                   break;

               case "ProjectName DESC":
                   projects.Sort(ProjectComparer.CompareByNameDesc);
                   break;

               case "Sector":
               case "Sector ASC":
                   projects.Sort(ProjectComparer.CompareBySector);
                   break;

               case "Sector DESC":
                   projects.Sort(ProjectComparer.CompareBySectorDesc);
                   break;

               case "ProjectStatus":
               case "ProjectStatus ASC":
                   projects.Sort(ProjectComparer.CompareByProjectStatus);
                   break;

               case "ProjectStatus DESC":
                   projects.Sort(ProjectComparer.CompareByProjectStatusDesc);
                   break;
           }
}

ProjectComparer Class:

public class ProjectComparer
   {
       //COMPARE BY NAME

       private static IComparer<project> _compareByName = new _sortName(false);
       public static IComparer<project> CompareByName { get { return _compareByName; } }

       private static IComparer<project> _compareByNameDesc = new _sortName(true);
       public static IComparer<project> CompareByNameDesc { get { return _compareByNameDesc; } }

       private class _sortName : IComparer<project>
       {
           bool _reverse;
           public _sortName(bool reverse)
           {
               this._reverse = reverse;
           }

           public int Compare(Project x, Project y)
           {
               if (_reverse) return y.ProjectName.CompareTo(x.ProjectName);
               else return x.ProjectName.CompareTo(y.ProjectName);
           }
       }


//COMPARE BY SECTOR

       private static IComparer<project> _compareBySector = new _sortSector(false);
       public static IComparer<project> CompareBySector { get { return _compareBySector; } }

       private static IComparer<project> _compareBySectorDesc = new _sortSector(true);
       public static IComparer<project> CompareBySectorDesc { get { return _compareBySectorDesc; } }

       private class _sortSector : IComparer<project>
       {
           bool _reverse;
           public _sortSector(bool reverse)
           {
               this._reverse = reverse;
           }

           public int Compare(Project x, Project y)
           {
               if (_reverse) return y.Sector.CompareTo(x.Sector);
               else return x.Sector.CompareTo(y.Sector);
           }
       }


//COMPARE BY Project Status

       private static IComparer<project> _compareByProjectStatus = new _sortProjectStatus(false);
       public static IComparer<project> CompareByProjectStatus { get { return _compareByProjectStatus; } }

       private static IComparer<project> _compareByProjectStatusDesc = new _sortProjectStatus(true);
       public static IComparer<project> CompareByProjectStatusDesc { get { return _compareByProjectStatusDesc; } }

       private class _sortProjectStatus : IComparer<project>
       {
           bool _reverse;
           public _sortProjectStatus(bool reverse)
           {
               this._reverse = reverse;
           }

           public int Compare(Project x, Project y)
           {
               if (_reverse) return y.ProjectStatus.CompareTo(x.ProjectStatus);
               else return x.ProjectStatus.CompareTo(y.ProjectStatus);
           }
       }

}

And that will give you paging and sorting on your GridView bound to a collection of custom objects. If i have missed any thing or it doesn't make any sense, plese just drop me a comment.