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.