Andrus Development

SQL CLR Aggregate: Median

2012-01-12T13:22:00.000-08:00

Since Sql Server doesn't have an aggregate for Median, I figured, that I'll create my first SQL CLR to handle this problem.

So, I created my intial project, using the .Net Framework 3.5 and C# SQL CLR aggregate template, and started coding:

Code Snippet

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections.Generic;

[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(

    Format.UserDefined,

    IsInvariantToDuplicates = false,

    IsInvariantToNulls = false,

    IsInvariantToOrder = false,

    MaxByteSize = 8000)]

public struct Median : IBinarySerialize 

{

    //Variables to hold the values;

    private List<double> ld;

    public void Init()

    {

        ld = new List<double>();

    }

    public void Accumulate(SqlDouble Value)

    {

        if (!Value.IsNull)

        {

            ld.Add(Value.Value);

        }

    }

    /// 

    /// Merge the partially computed aggregate with this aggregate.

    /// 

    /// 

The other partial results to be merged

    public void Merge(Median Group)

    {

        this.ld.AddRange(Group.ld.ToArray());

    }

    /// 

    /// Called at the end of aggregation, to return the results.

    /// 

    /// The median of all inputted values

    public SqlDouble Terminate()

    {

        //debug: return (SqlDouble)ld.Count;

        //special case 0 values

        if (ld.Count == 0)

            return SqlDouble.Null;

        ld.Sort();

        int index = (int)ld.Count / 2;

        if (ld.Count % 2 == 0)

        {

            return (SqlDouble)(((double)ld[index] + (double)ld[index - 1]) / 2);

        }

        else

        {

            return (SqlDouble)((double)ld[index]);

        }

    }

    #region IBinarySerialize Members

    public void Read(System.IO.BinaryReader r)

    {

        int cnt = r.ReadInt32();

        this.ld = new List<double>(cnt);

        for(int i = 0; i < cnt; i++)

        {

            this.ld.Add(r.ReadDouble());

        }

    }

    public void Write(System.IO.BinaryWriter w)

    {

        w.Write(this.ld.Count);

        foreach (double d in this.ld)

        {

            w.Write(d);

        }

    }

    #endregion

}

Some of the intial changes that I've made from the default:
1) The SqlUserDefinedAggregate default was Format.Native. The problem with using the default Serializer is that I needed a way to store my values, preferably in a List. By changing the format to Format.UserDefined -- I was able to create my own serializer. In this case I used the IBinarySerialize interface.

2) I also changed the default IsInvariant attributes.

IsInvariantToDuplicates - set to false since I want duplicates.
IsInvariantToNull - set to false since I will handle Null values in my code.
IsInvariantToOrder - set to false, I can do this in code.

Once the code is compiled, you will need to place the dll on the same machine as the SQL Server is located.

Using the following query to set the assembly and aggregate name:

Code Snippet

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Median')

   DROP AGGREGATE Median

go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'MyClrCode')

   DROP ASSEMBLY MyClrCode

go

CREATE ASSEMBLY MyClrCode

FROM 'C:\Median.dll'

WITH PERMISSION_SET = SAFE

GO 

CREATE AGGREGATE dbo.Median

(@input float)

RETURNS float

EXTERNAL NAME MyClrCode.Median

Once done, time to do some testing. An example:

Code Snippet

--Test 1 value

select 

dbo.Median(x.y)

from

(

    select 1 as y

)x

GO

--Test Accuracy

select 

dbo.Median(x.y)

from

(

    select 1 as y

    union all

    select 2 as y

)x

GO

SSRS: Saving User Settings from the Report

2012-01-11T09:11:00.000-08:00

Sometimes, different report's parameters default values are needed for each users. Here is a way, each user can save their default settings for the next time they run an SSRS report, from the report itself.

We will need to save the user's settings to a database table.
Table create query:

SETQUOTED_IDENTIFIER ON
GO

SETANSI_PADDING ON
GO

CREATETABLE [dbo].[UserSettings](
[UserSettingSQN] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar] (50) NOT NULL,
[ReportName] [varchar] (50) NOT NULL,
[ParameterName] [varchar](50) NULL,[ParameterValue] [varchar](max) NULL,
[LastUpdateDate] [datetime] NOT NULL,
CONSTRAINT [PK_UserSettingSQN] PRIMARY KEY CLUSTERED
( [UserSettingSQN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

CONSTRAINT [IX_UserSettings] UNIQUE NONCLUSTERED
([UserID] ASC,
[ReportName] ASC,
[ParameterName] 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
SETANSI_PADDING OFF
GO

The fields:

UserID: to store the user info that they used to access the report. I currently store both the computer and username.
ReportName: The name of the report
ParameterName: Parameter that is used in the report that is to be saved.
ParameterValue: Value the user set to save.

An example of the data:

The Report:

In the Reports, I've created 3 additional parameters, to be used for saving the user's default parameter settings:

UserID: I use the SSRS's built-in field, User!UserID, to get the ID of the user running the report. Set the visibility to be internal.
ReportName: Again, I use the SSRS's built-in field, Globals!ReportName, to get the name of the report as it is stored on the report server database. Set the visibility to be internal.
Save: A boolean field that is visible to the user. I have the default set to "False", so when the user wants to change his/her settings they can change it to true when they run the report.

So when they run the report, I have the stored procedure check to see if the boolean was set to 1 -- if so then save with update/insert.

IF@Save = 1
BEGIN
UPDATE [ScientificArray].dbo.UserSettings WITH (SERIALIZABLE) SET ParameterValue = @StringNames
WHERE UserID = @UserID AND ReportName = @ReportName AND ParameterName = 'InverterString'

IF @@ROWCOUNT = 0
BEGIN

INSERT INTO [ScientificArray].dbo.UserSettings (UserID, ReportName, ParameterName, ParameterValue, LastUpdateDate) VALUES (@UserID, @ReportName, 'InverterString', @StringNames, GETDATE())

END
END

Removing accents for string comparisons in tsql

2012-01-06T14:17:00.000-08:00

I've been reading The Daily WTF, and found this one interesting: http://thedailywtf.com/Articles/The-AntiSQL-Coalition-.aspx

By using the Latin1_General_CI_AI (AI stands for Accent Insensitive)
the user was able to by-pass special characters like: É,È,Ê, and Ë for E when doing a search.
Example:

SELECT*
FROMPersons
WHEREname collate Latin1_General_CI_AI LIKE @name
AND surname collate Latin1_General_CI_AI LIKE @surname
ORDERBY name, surname

Additional references:
http://msdn.microsoft.com/en-us/library/ms187582.aspx

P.S. Funny to see other companies have Database haters too.

TSQL Example: Cumulative

2011-12-15T14:38:00.000-08:00

A quick example on implementing cumaltive logic in a tsql query:

WITH CTE(Name, Observation, RowNum)
AS
(
SELECT
Name,
count(*) as Observation,
ROW_NUMBER() OVER (ORDER BY count(*)desc) as rownum
FROM
dbo.TABLE_LOGIC_STUFF
GROUP BY Name
)

select
c1.Name,
c1.Observation,
(select sum(c2.Observation) from cte as c2 where c2.RowNum <= c1.RowNum) as cumulative
from cte as c1

by adding row number in the cte table, I can then use a subquery to sum all the values below the current rownum.

SQL Server Query Performance Analysis

2011-11-28T10:49:00.001-08:00

Great post by Carl Nolan(http://blogs.msdn.com/b/mcsuksoldev/archive/2011/11/27/adventure-in-tsql-sql-server-query-performance-analysis-using-dmvs.aspx) on finding the worst offending queries that do the most CPU and Disk I/O loads.

The two queries are:

CPU Query

-- Which Queries are taking the most time/cpu to execute
SELECT TOP 20
    total_worker_time, total_elapsed_time,
    total_worker_time/execution_count AS avg_cpu_cost, execution_count,
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
        FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset) / 2
        )
        FROM sys.dm_exec_sql_text([sql_handle]) AS est) AS query_text,
    total_logical_reads/execution_count AS avg_logical_reads,
    total_logical_writes/execution_count AS avg_logical_writes,
    last_worker_time, min_worker_time, max_worker_time,
    last_elapsed_time, min_elapsed_time, max_elapsed_time,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND DB_NAME(dbid) IS NOT NULL
  AND (total_worker_time/execution_count) > 100
--ORDER BY avg_cpu_cost DESC;
--ORDER BY execution_count DESC;
ORDER BY total_worker_time DESC;

Disk IO Query

SELECT TOP 20
    total_logical_reads/execution_count AS avg_logical_reads,
    total_logical_writes/execution_count AS avg_logical_writes,
    total_worker_time/execution_count AS avg_cpu_cost, execution_count,
    total_worker_time, total_logical_reads, total_logical_writes,
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
        FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset
        ) / 2)
        FROM sys.dm_exec_sql_text(sql_handle) AS est) AS query_text,
    last_logical_reads, min_logical_reads, max_logical_reads,
    last_logical_writes, min_logical_writes, max_logical_writes,
    total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads,
    (total_logical_reads + (total_logical_writes * 5))/execution_count AS io_weighting,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND DB_NAME(dbid) IS NOT NULL
  and (total_worker_time/execution_count) > 100
ORDER BY io_weighting DESC;
--ORDER BY avg_logical_reads DESC;
--ORDER BY avg_logical_writes DESC;
--ORDER BY avg_cpu_cost DESC;

Dictionary Not Found

2011-10-24T14:57:00.000-07:00

Kept getting the error: "Dictionary Not Found..." once I uploaded new changes to the web server to allow spell checking. So, my first step was to open the developer tools in IE (F12) and do a quick capture of the network traffic.

Which gave:

URL: /fieldperformance/C1Spell_en-US.dct
Method: GET
Result: 404
Type: text/html
Received: 1.37 KB
Taken: 265 ms
Initiator:
Wait‎‎: 1482
Start: 62
Request: 203
Response‎: 0
Cache: 0
read‎‎ Gap‎‎: 5067

So, it's looking in the fieldperformance folder for the dictionary file. Doing a quick check, yes the file is located there. The problem then ends up being the MIME type is missing.

Going to the IIS Manager, I added the Extension dct with a MIME type of application/octet-stream.

WCF & Silverlight max buffer size issue

2011-10-14T07:49:00.000-07:00

Ran into these problems a few times with the WCF errors, in which the buffer size was the issue:

“The remote server returned an error: NotFound”
"Unable to read data from the transport connection: The connection was closed"
"The maximum message size quota for incoming messages (65536) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element."

This would be an easy solution if only dealing with the WCF. Since, we are using the WCF layer to pass data to/from the Silverlight layer the problem arises in two locations -- not one.

Over at Mehroz's Experiments (http://smehrozalam.wordpress.com/2009/01/29/retrieving-huge-amount-of-data-from-wcf-service-in-silverlight-application/) he goes into good detail on solving this problem.

So in the Silverlight's ServiceReferences.ClientConfig we increase the buffer size.

Code Snippet

 <binding name="BasicHttpBinding_IScientificDataService"   closeTimeout="00:01:00"openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" maxBufferSize="2147483647" maxReceivedMessageSize="2147483647" textEncoding="utf-8" transferMode="Buffered">

    <security mode="None"/>

binding>

In the WCF's Web.config, we needed to increase the maxBufferSize and maxReceivedMessageSize to a larger number



Code Snippet

  <system.serviceModel>
    

    <behaviors>
      

      <serviceBehaviors>
        

        <behavior>

          <serviceMetadata httpGetEnabled="true" />

          <serviceDebug includeExceptionDetailInFaults="true" />

          <dataContractSerializer maxItemsInObjectGraph="2147483647"/>

        behavior>

      serviceBehaviors>

    behaviors>

    <serviceHostingEnvironment multipleSiteBindingsEnabled="true" aspNetCompatibilityEnabled="false" />

  system.serviceModel>

SQL String Manipulations

2011-10-12T10:58:00.000-07:00

Interesting article in SQL Server Magazine about String Manipulations by Itzik Ben-Gan (http://www.sqlmag.com/article/tsql/string-manipulation-tips-techniques-part-1-136427)

So to give me a quick reference in the future, I'll summarize it here:

Counting Occurrences of a subString within a string:

DECLARE
@str    AS VARCHAR(1000) = 'abchellodehellofhello',
@substr AS VARCHAR(1000) = 'hello';

SELECT (LEN(@str) - LEN(REPLACE(@str, @substr, ''))) / LEN(@substr);

Exactly N Occurrences of a substring within a string:

DECLARE
@str    AS VARCHAR(1000) = 'abchellodehellofhello',
@substr AS VARCHAR(1000) = 'hello',
@N      AS INT           = 3;

SELECT
CASE
    WHEN (LEN(@str) - LEN(REPLACE(@str, @substr, ''))) / LEN(@substr) = @N
      THEN 'True'
    ELSE 'False or Unknown'
END;
-OR-

SELECT
CASE
    WHEN @str LIKE '%' + REPLICATE(@substr + '%', @N)
         AND @str NOT LIKE '%' + REPLICATE(@substr + '%', @N+1)
      THEN 'True'
    ELSE 'False or Unknown'
END;

Replacing Multiple Contiguous Spaces with a single space:

DECLARE @str AS VARCHAR(1000) = 'this   is     a   string    with     lots   of   spaces';

SELECT REPLACE(REPLACE(REPLACE(@str, ' ', '~ '), ' ~', ''), '~ ', ' ');

Replacing Overlapping Occurrences:

DECLARE @str AS VARCHAR(1000) = '.x.x.x.x.';

SELECT REPLACE(REPLACE(@str, '.x.', '.y.'), '.x.', '.y.');

-OR-

SELECT REPLACE(REPLACE(REPLACE(@str, '.', '..'), '.x.', '.y.'), '..', '.');
String Formatting Numbers with Leading Zeros:

DECLARE @num AS INT = -1759;

SELECT CASE SIGN(@num) WHEN -1 THEN '-' ELSE '' END + REPLACE(STR(ABS(@num), 10), ' ', '0');

-OR-

SELECT CASE SIGN(@num) WHEN -1 THEN '-' ELSE '' END + RIGHT('000000000' + CAST(ABS(@num) AS VARCHAR(10)), 10);

-OR (In Denali)-

SELECT FORMAT(@num, '0000000000');

Left Trimming Leading Occurrences of a Character:

DECLARE @str AS VARCHAR(100) = '0000001709';

SELECT REPLACE(LTRIM(REPLACE(@str, '0', ' ')), ' ', '0');

Checking That a String Is Made of Only Digits:

DECLARE @str AS VARCHAR(1000) = '1759';
SELECT
CASE
    WHEN @str NOT LIKE '%[^0-9]%' THEN 'True'
    ELSE 'False or Unknown'
END;

-OR-

CHECK (col1 NOT LIKE '%[^0-9]%')

Median in SQL

2011-09-22T09:10:00.000-07:00

Median is not a default aggregate in SQL-Server, but is sometime a perferable statistical function than Average. So here is quick tip on how I got the median:

Define: Median -

"The median of a finite list of numbers can be found by arranging all the observations from lowest value to highest value and picking the middle one. If there is an even number of observations, then there is no single middle value; the median is then usually defined to be the mean of the two middle values" - Wikipedia http://en.wikipedia.org/wiki/Median

By using ROW_NUMBER() function twice I can get my approximation of where the median is located.

For Example:
ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY Current ASC

-and-

ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY Current DESC

By using these two values I can pull the absolute value from their subtraction

ABS(ASCRow - DescRow)

To get an approximation of where it is associated with the median. Now if the values you are sorting are distinct, then you can find the median by looking for numbers that are less than or equal to 1 and then taking their average.

ABS(ASCRow - DescRow) <=1

In my case I can get more than a count of 2 numbers that are at the median, in this case I did another ROW_NUMBER function but this time order on the absolute difference and then I selected the top row in an outer query:

ROW_NUMBER() OVER (PARTITION BY Date, Array, Inverter ORDER BY MedianDistance) as ROW_NUM

This is where I stopped, even though there is still the chance of not getting the "true" Median. I would basically need to go further and pull all data points with the same value as the MedianDistance for those partitions and do an average.

One cause of Silverlight's White Screen of Death (WSOD)

2011-08-29T08:46:00.000-07:00

We have this silverlight (4.0) website that would work for most, but a select few were only seeing a white screen. I tried running the website in different IE formats and even in firefox. The results were the same no matter what browsers were used. So we narrowed it down to something with the machine.

A white screen usually means that there is a silverlight error, which can be tough to trace/track down. In our case it came down to the issue that nested user controls cannot have static resources. Don't know how this applies to the machine level is beyond my comprehension.

So instead of this:
<usercontrol.resources>
  <style targettype="sdk:DataGridColumnHeader" x:key="DataGridHeader">
      <setter Property="FontSize" Value="10"/>
      <setter Property="Padding" Value="3"/>
      <setter Property="Margin" Value="0"/>
</style>
</usercontrol.resources>

We needed to put the style more inline:
<sdk:datagrid.columnheaderstyle>
   <style targettype="sdk:DataGridColumnHeader">
      <setter Property="FontSize" Value="10"/>
      <setter Property="Padding" Value="3"/>
      <setter Property="Margin" Value="0"/>
</style>
</sdk:datagrid.columnheaderstyle>

Another SQL Trick/Tip

2011-05-18T13:50:00.000-07:00

I had a request to change a view to display information as a result of "Zones" instead of "Observation". So, a little background...

For each processed file, after inserting, I set an observation number based on the row in the file. This is easily done with the ROW_NUMBER() function in SQL-Server:

ROW_NUMBER() OVER (ORDER BY ObservationSQN) AS ROWNUM

,and some other unique tricks I use to separate it out per file.

The request then asked, instead of displaying Observation Numbers, they wanted an average; and if the number is less than or equal to 0 then don't include that observation in the average.

Zone 1 has the average of observations 1,2,3,4,6
Zone 2 has the average of observations 5,8,11
Zone 3 has the average of observations 10,12,13,14,15

Well, thankfully SQL-Server doesn't include a value in it's average function if it's null. Just have to set the value to NULL if <= 0

So at first I did this brute force technique (which ends up being ugly and slow):
SELECT
avg(zone1.value) as zone1Avg,
avg(zone2.value) as zone2Avg,
avg(zone3.value) as zone3Avg,
zone1.FileSQN
FROM
(
SELECT
FileSQN,
CASE WHEN value <= 0 THEN NULL ELSE value END AS value
FROM Table1
WHERE Observation in (1,2,3,4,6)
AND DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)zone1
INNER JOIN
(
SELECT
FileSQN,
CASE WHEN value <= 0 THEN NULL ELSE value END AS value
FROM Table1
WHEREObservation in (5,8,11)
AND DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)zone2 on zone2.FileSQN = zone1.FileSQN
INNER JOIN
(
SELECT
FileSQN,
CASE WHEN value <= 0 THEN NULL ELSE value END AS value
FROM Table1
WHERE Observation in (10,12,13,14,15)
AND DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)zone3 on zone3.FileSQN = zone2.FileSQN
GROUP BY FileSQN

The technique I ended up using, used the WITH common table expression:

WITH WithTable (FileSQN, value, ObservationNumber)
AS
(
SELECT
FileSQN,
CASE WHEN value <= 0 THEN NULL ELSE value END AS value,
ObservationNumber
FROMTable1
WHERE DateDiff(day, ProcessEndDate, GETDATE()) <= 1)
)

SELECT
zone1.zone1Avg,
zone2.zone2Avg,
zone3.zone3Avg,
FileSQN
FROM
(
SELECT
avg(value) as zone1Avg,
FileSQN
FROM
WithTable
WHERE ObservationNumber in (1,2,3,4,6)
GROUP BY FileSQN
) zone1
INNER JOIN
(
SELECT
avg(value) as zone2Avg,
FileSQN
FROM
WithTable
WHEREObservationNumber in (5,8,11)
GROUP BY FileSQN
)zone2 on zone2.FileSQN = zone1.FileSQN
INNER JOIN
(
SELECT
avg(value) as zone3Avg,
FileSQN
FROM
WithTable
WHEREObservationNumber in (10,12,13,14,15)
GROUP BY FileSQN
)

Summary: The first technique was slower, since I was basically pulling the same info 3 times, compared to the second technique's once.

Note: The names of the tables have been change to protect the innocent. I wrote this in line, so not everything might be syntactically correct

Reminder: How to use Window's Shell32 to do basic decompression (unzipping).

2011-04-20T08:48:00.000-07:00

Add the shell32.dll reference found in C:\Windows\System32

Excerpt from my code:

Shell32.Shell sc = new Shell32.Shell();
Shell32.Folder SrcFlder = sc.NameSpace(file.FullName);
Shell32.Folder DestFlder = sc.NameSpace(args[2]);
Shell32.FolderItems items = SrcFlder.Items();
DestFlder.CopyHere(items, 20);

where:
file.FullName is a string representing the full path to the *.zip file. (Actually a FileInfo type)
arg[2] is another string representing the destination folder.

Debugging Interop in SSIS

2011-01-24T12:30:00.000-08:00

Had a little trouble getting a COM (interop) dll/tbl from working in a SSIS's script task, while a similar program in a normal C# project would run sucessfully.

First thing I did was a quick registry hack to turn logging on for interop:
HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/Fusion

and created a new DWORD called EnableLog
and set the value to 1

This then gave me more info when I ran the package "without debugging".

In this case, it was having trouble finding my interop dll, because it was looking in the SQL-Server and .Net directories instead of the build directory.

So, as a quick solution, I copied the interop dlls from the build directory and drop them in the necessary folders:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\

-and-

C:\Windows\Microsoft.NET\Framework\v2.0.50727

Sending multivalue from SSRS to a stored procedure.

2010-11-04T09:16:00.000-07:00

One, of the many, features that I wish they would add to SSRS; is an easier way to send in multivalue selected parameters to a stored procedure.

The best solution to this problem, is to use a function that converts a string of delimited values into a table.

Here are the quick low down steps:

1) Go to your SSRS dataset properties -> Parameters -> (select the multivalue parameter function [fx]) and change the parameter to send in a sting of delimited values. For example,

=Join(Parameters!MeasurementID.Value,",")

2) Create the function that splits and creates a table:

CREATE FUNCTION dbo.fn_charlist_to_table
(
@list ntext,
@delimiter nchar(1) = N','
)
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

str varchar(4000),
nstr nvarchar(2000)) AS

BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)

SET @textpos = 1
SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2
BEGIN
    SET @chunklen = 4000 - datalength(@leftover) / 2
    SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
    SET @textpos = @textpos + @chunklen
    SET @pos = charindex(@delimiter, @tmpstr)

    WHILE @pos > 0
      BEGIN
        SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
        INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
        SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
        SET @pos = charindex(@delimiter, @tmpstr)
       END

    SET @leftover = @tmpstr
END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))

RETURN
END
GO

3) In your stored procedure, call the function. For example:

Select * from measurment where
measurement.MeasurementID in (select [str] from fn_charlist_to_table(@MeasurementID,Default))

Mimic Domain for SQL Server or other executables.

2010-10-05T11:49:00.000-07:00

One tiresome thing about being a consultant is the lack of ability to use Windows Authentication when on site. Your computer is on a separate domain and you run into the "I need permissions" issue.

One solution to this is to use the run as command in a shortcut's target.

First create a shortcut of the executable.

For this example, I used SQL Server.

C:\Windows\System32\runas.exe /netonly /user:consultantDomain\wandrus "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

The highlighted yellow is what was added, where I set the domain\user_name to what I would like to use. When this shortcut is then clicked, it will prompt for your password.

Only display # rows per page in a SSRS report.

2010-04-14T14:39:00.000-07:00

One problem I ran into and tried every solution on the net (that I could find) with no luck. I had to display only 22 rows per page, with blank rows -- if the data is not filled in for the other rows.

Step 1: SQL
So, first thing is to get your sql query to return, not row numbers but page numbers. So in this case, I used the row_number function found in SQL-Server subtracting 1 and then divided by the number of rows I needed per page:

((row_number() over(order by license_no)) -1) /22 as 'Page'

Step 2: Add Parent Group
In my report I needed to display a header, a footer, detail information, and empty rows if not exceeding 22. This is of course done with SSRS's Table.

First add a new parent group and in the Group Properties/General add a group on to the "Page" field. This group should span the whole page.

Don't bother with the page break sections, this will just mess things up.

Step 3: Add Child Group with blank rows
The child group is what I used to hold the blank rows. Within that group I added 22 rows with no information in the textboxes.

Step 4: Change visibility of blank rows
Right-clicking on the row, and select Row Visibility. Select the bullet: "Show or hide based on an expression". Within each row the expression will check if the row count is less than the ((page + 1) * rows_per_page) - (rows_per_page - position)
Example (Note:"section2" is my dataset):
[Row 22 Expression]: =IIF(CountRows("Section2") < ((Fields!Page.Value + 1) * 22),false,true)
[Row 21 Expression]: =IIF(CountRows("Section2") < ((Fields!Page.Value + 1) * 22) - 1,false,true)
...

This is all that is needed to acomplish this task.

If you need to hide a row until the end, I used the following visibility expression:
=IIF(Fields!Page.Value = Last(Fields!Page.Value,"Section2"),false,true)

And of course if you need to repeat the header on each page, like I do. This is found in the Tablix Properites/General and just put a check mark in the "Repeat header rows on each page".

Precision Timer

2010-02-19T12:24:00.000-08:00

Ran into a problem yesterday wth timing issues, this is the first time I ever needed to create a timer with extreme precision.

Note: The precision of multithreaded timers depends on the operating system, and is typically in the 10-20 milliseconds region. This class is used to generate greater precision using the P/Invoke interop and calls the Windows multimedia timer; which has a precision of 1 ms. But that increased responsiveness comes at a cost - since the system scheduler is running more often, the system spends more time scheduling tasks, context switching, etc. This can ultimately reduce overall system performance, since every clock cycle the system is processing "system stuff" is a clock cycle that isn't being spent running your application.

So here is some code I found and changed up a bit that uses the winmm.dll timesetevent:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Runtime.InteropServices;

using System.Diagnostics;

public class PrecisionTimer : IDisposable{

//Lib API declarations

[DllImport("Winmm.dll", CharSet = CharSet.Auto)]

static extern uint timeSetEvent(uint uDelay, uint uResolution, TimerCallback lpTimeProc, UIntPtr dwUser, uint fuEvent);

[DllImport("Winmm.dll", CharSet = CharSet.Auto)]

static extern uint timeKillEvent(uint uTimerID);

[DllImport("Winmm.dll", CharSet = CharSet.Auto)]

static extern uint timeGetTime();

[DllImport("Winmm.dll", CharSet = CharSet.Auto)]

static extern uint timeBeginPeriod(uint uPeriod);

[DllImport("Winmm.dll", CharSet = CharSet.Auto)]

static extern uint timeEndPeriod(uint uPeriod);

//Timer type definitions

[Flags]

public enum fuEvent : uint{

TIME_ONESHOT = 0, //Event occurs once, after uDelay milliseconds.

TIME_PERIODIC = 1,

TIME_CALLBACK_FUNCTION = 0x0000, /* callback is function */

//TIME_CALLBACK_EVENT_SET = 0x0010, /* callback is event - use SetEvent */

//TIME_CALLBACK_EVENT_PULSE = 0x0020 /* callback is event - use PulseEvent */

}

//Delegate definition for the API callback

delegate void TimerCallback(uint uTimerID, uint uMsg, UIntPtr dwUser, UIntPtr dw1, UIntPtr dw2);

private fuEvent f;

private uint ms;

//IDisposable code

private bool disposed = false;

public void Dispose(){

Dispose(true);

GC.SuppressFinalize(this);

}

private void Dispose(bool disposing){

if (!this.disposed){

if (disposing){

Stop();

}

}

disposed = true;

}

~PrecisionTimer(){

Dispose(false);

}

///

/// The current timer instance ID

///

uint id = 0;

///

/// The callback used by the the API

///

TimerCallback thisCB;

///

/// The timer elapsed event

///

public event EventHandler Timer;

protected virtual void OnTimer(EventArgs e){

if (Timer != null)

Timer(this, e);

}

///

/// Initialize

///

///

///

public PrecisionTimer(uint ms, bool repeat){

//Initialize the API callback

thisCB = CBFunc;

this.ms = ms;

//Set the timer type flags

f = fuEvent.TIME_CALLBACK_FUNCTION
(repeat ? fuEvent.TIME_PERIODIC : fuEvent.TIME_ONESHOT);

//Tell OS that we are about to need a precision timer.

PrecisionTimer.timeBeginPeriod(1);

}

///

/// Stop the current timer instance

/// VERY IMPORTANT TO CALL

///

public void Stop(){

lock (this)

{

if (id != 0){

timeKillEvent(id);

Trace.WriteLine("Timer " + id.ToString() + " stopped " + DateTime.Now.ToString("HH:mm:ss.ffff"));

id = 0;

}

}

//Tell OS that we are done using the precision timer and that it can continue back to normal.

PrecisionTimer.timeEndPeriod(1);

}

///

/// Start a timer instance

///

/// Timer interval in milliseconds

/// If true sets a repetitive event, otherwise sets a one-shot

public void Start(){

//Kill any existing timer

//Stop();

lock (this){

id = timeSetEvent(ms, 0, thisCB, UIntPtr.Zero, (uint)f);

if (id == 0)

throw new Exception("timeSetEvent error");

Trace.WriteLine("Timer " + id.ToString() + " started " + DateTime.Now.ToString("HH:mm:ss.ffff"));

}

}

void CBFunc(uint uTimerID, uint uMsg, UIntPtr dwUser, UIntPtr dw1, UIntPtr dw2){

//Callback from the PrecisionTimer API that fires the Timer event. Note we are in a different thread here

OnTimer(new EventArgs());

}

}

}

An example of calling the class:

PrecisionTimer timer = new PrecisionTimer(8500, false); //will time for 8.5 seconds before triggering an event
timer.Timer += new EventHandler(timer_Timer);

timer.Start();
//Do Stuff or something until event

My event handler

void timer_Timer(object sender, EventArgs e){

timer.Stop();

DoStuff();

}

List Creator

2010-01-11T09:01:00.000-08:00

This is one of my favorite pieces of code I've ~~stolen~~ borrowed from another site. I've had made some minor code changes and additions from the original, but basically I just need to send in a DataReader after running a sql command and it will return a list of objects of that type. (Very similar to Linq to Sql)

You just need to make sure that anything that is return through the query is defined in the object as a write property.

So here is the List Creator code, which does the creation via reflections:

public class ListCreator where T : new()

{

     public List FindAll(IDataReader iDataReader)

     {

         List returnList = new List();

         try

         {

             //need a Type and PropertyInfo object to set properties via reflection

             Type tType = new T().GetType();

             PropertyInfo pInfo;

             //x will hold the instance of  until it is added to the list

             T x;

             //use reader to populate list of objects

             while (iDataReader.Read())

             {

                 x = new T();

                 //set property values

                 //for this to work, command’s column names must match property names in object 

                 for (int i = 0; i < iDataReader.FieldCount; i++)

                 {

                     pInfo = tType.GetProperty(iDataReader.GetName(i));

                     pInfo.SetValue(x, (iDataReader[i] == DBNull.Value? null:iDataReader[i]), null);

                 }

                 //once instance of  is populated, add to list

                 returnList.Add(x);

             }

         }

         catch (Exception ex)

         {

             Logging.Logging.Error(ex.ToString());

         }

         return returnList;

     }

}

So in my DAL I might have something like:

public List<PartsOrdering> GetAllPartsOrdering()

{

   List<PartsOrdering> li = new List<PartsOrdering>();

   try

   {

      using (SqlConnection cn = new SqlConnection(connString))

      {

          ListCreator<PartsOrdering> PartsOrders = new ListCreator<PartsOrdering>();

          SqlCommand cmd = new SqlCommand("dbo.selPartsOrdering", cn);

          cmd.CommandType = CommandType.StoredProcedure;

          cn.Open();

          li = PartsOrders.FindAll(ExecuteReader(cmd));

      }

  }

  catch (Exception ex)

  {

     Logging.Error(ex.ToString());

  }

  return li;

}

[Serializable]

public class PartsOrdering : IFormattable

{

    #region Properties

    [DefaultValue(-1)]

    public int PartsOrderingID

    {

         get;

         set;

    }

    [DefaultValue("")]

    public string ToolID

    {

         get;

         set;

    }

    [DefaultValue("")]

    public string PartNumber

    {

         get;

         set;

    }

    [DefaultValue("")]

    public string Description

    {

         get;

         set;

    }

    [DefaultValue(0)]

    public int Quantity

    {

         get;

         set;

    }

    [DefaultValue(1)]

    public int PartsOrderingLocationID

    {

         get;

         set;

    }

    [DefaultValue(1)]

    public int PartsOrderingUrgencyID

    {

         get;

         set;

    }

    [DefaultValue("")]

    public string Comment

    {

         get;

         set;

    }

    public DateTime RequestedDateTime

    {

         get;

         set;

    }

    [DefaultValue("")]

    public string RequestedUser

    {

         get;

         set;

    }

    public string LocationName

    {

         get

         {

             List<PartsOrderingLocation> locations = new PartsOrderingLocation().List();

             return locations.Where(n => n.PartsOrderingLocationID.Equals(this.PartsOrderingLocationID)).Select(n => n.Name).First();

         }

         set

         {

             List<PartsOrderingLocation> locations = new PartsOrderingLocation().List();

             this.PartsOrderingLocationID = locations.Where(n => n.Name.Equals(value)).Select(n => n.PartsOrderingLocationID).First();

         }

     }

     public string UrgencyName

     {

         get

         {

             List<PartsOrderingUrgency> locations = new PartsOrderingUrgency().List();

             return locations.Where(n => n.PartsOrderingUrgencyID.Equals(this.PartsOrderingUrgencyID)).Select(n => n.Name).First();

         }

         set

         {

             List<PartsOrderingUrgency> locations = new PartsOrderingUrgency().List();

             this.PartsOrderingUrgencyID = locations.Where(n => n.Name.Equals(value)).Select(n => n.PartsOrderingUrgencyID).First();

         }

     }

     public int UrgencyRank

     {

         get

         {

             List<PartsOrderingUrgency> locations = new PartsOrderingUrgency().List();

             return locations.Where(n => n.PartsOrderingUrgencyID.Equals(this.PartsOrderingUrgencyID)).Select(n => n.Rank).First();

         }

     }

     [DefaultValue(-1)]

     public int PartsOrderingGroupID

     {

         get;

         set;

     }

     [DefaultValue(true)]

     public bool IsActive

     {

         get;

         set;

     }

How to update large count of rows without locking them

2010-01-08T08:27:00.000-08:00

Found this interesting, how to update large databases, so that you don't have to lock rows
http://blogs.msdn.com/sqlpfe/archive/2010/01/06/tsql-coding-patterns-i.aspx

So instead of the usual:

UPDATE dbo.Foo

SET Column = 'Value'

Try:

DECLARE @UpdatedRows(PK_Id int NOT NULL PRIMARY KEY)

DECLARE @var INT

SELECT @var=0 -- this resets @@ROWCOUNT=1

WHILE @@ROWCOUNT >0

BEGIN

UPDATE TOP(1500) BAR

SET Column='Value'

OUTPUT inserted.PK_ID

INTO @UpdatedRows

FROM dbo.BAR as BAR

WHERE NOT EXISTS (SELECT 1 FROM @UpdatedRows UPD WHERE UPD.PK_ID=BAR.PK_ID)

END

I prefer one of the ways the commentator offered: (easier to follow)

WHILE @@ROWCOUNT >0

BEGIN

UPDATE TOP(1500) BAR

SET Column='Value'

WHERE

Column <> 'Value'

END

Converting XPS to Bitmap

2009-12-15T16:07:00.000-08:00

One thing I had to do recently, for a SSRS report, was attach a 3 page pdf to a notice that is mailed out. Since, I can't just attach a pdf directly to the report, I figured I'll just take the images.

So, first thing I did was print the pdf to a XPS file. Now I just need to take that XPS file and convert it to some type of usable image that SSRS can recognize.

Found a solution on one of the MSDN message boards; however, I had to make some updates to it:

        static public void SaveXpsPageToBitmap(string xpsFileName)

        {

            DirectoryInfo di = new DirectoryInfo(xpsFileName);

            XpsDocument xpsDoc = new XpsDocument(xpsFileName, System.IO.FileAccess.Read);

            FixedDocumentSequence docSeq = xpsDoc.GetFixedDocumentSequence();

            //DocumentReferenceCollection drc = docSeq.References;

            for (int i = 0; i < docSeq.DocumentPaginator.PageCount; i++)

            {

                DocumentPage docPage = docSeq.DocumentPaginator.GetPage(i);

                BitmapImage bitmap = new BitmapImage();

                RenderTargetBitmap renderTarget = new RenderTargetBitmap((int)docPage.Size.Width, (int)docPage.Size.Height, 96, 96, PixelFormats.Default);

                renderTarget.Render(docPage.Visual);

                BitmapEncoder encoder = new BmpBitmapEncoder();

                encoder.Frames.Add(BitmapFrame.Create(renderTarget));

                FileStream pageOutStream = new FileStream(di.FullName.Substring(0,di.FullName.Length - 4) + "_Page_" + (i+1).ToString() + ".bmp", FileMode.Create, FileAccess.Write);

                encoder.Save(pageOutStream);

                pageOutStream.Close();

            }

        }

So this program works nicely, only problem I currently have is that I have to convert these large size bitmaps (~5MB per page) to something more reasonable. Especially, since the SSRS deployment gives me a SOAP error because of the size. I'll probably just end up converting them to Jpeg later on.

Creating an Psion Tecklogix Scan Gun App

2009-12-01T12:42:00.000-08:00

In 4 simple and easy to follow steps:

Step 1: Getting Psion Resources

You will have to register to get access to the page.
(https://teknet.psionteklogix.com/ptxCMS/Teknet.aspx?s=us&p=DevKits)

Download and install their "Mobile Devices SDK"

Step 2: Create Project

If you go to the "Smart Device" section, you will notices a "Psion Teklogix Device Project". This add the Psion TeklogixNet reference and also the PtxSdkCommon.dll in the root directory, which is a dependency when running your app on device.

Step 3: Add Items to the Toolbar, if needed

Right-click on the Toolbar, and select Choose Items.
Browse to C:\Program Files\Psion Teklogix\Mobile Devices SDK V3.1\DotNet2 and select the PsionTeklogixNet.dll

This should add:

If not already done so, drag each of those items onto the form.

The default app gives a good example, it uses the ScanCompleteEvent; which send in a string value representation of the barcode and places it text into a textbox for the user to see.

/* $Revision 1.1.1.1  */

/* Copyright Psion Teklogix Inc. 2007 */

/*

 * File: Form1.cs

 *

 * Description:

 * Implementation file for Form1 class in PtxApp1

 *

 */

using System;

using System.Collections;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Runtime.InteropServices;

using System.Text;

using System.Windows.Forms;

using PsionTeklogix.Barcode;

using PsionTeklogix.Barcode.ScannerServices;

/*! <summary> 

    Contains the class and functions related to PtxApp1 scanner application  

    </summary>

*/

namespace PtxApp1

{

    /* 

     * Form1

     *

     */

    /// <summary>

    /// The Form1 class generates the Graphical User Interface for PtxApp1.

    /// </summary>

    public partial class Form1 : Form

    {

        /*

         * InitializeScanner

         *

         */

        /// <summary>

        /// Initialize components for PtxApp1. 

        /// </summary>

        private void InitializeScanner()

        {

            scanner.Driver = scannerServicesDriver;

            scanner.ScanCompleteEvent +=

                new ScanCompleteEventHandler(scanner_ScanCompleteEvent);

        }

        /*

         * Form1

         *

         */

        /// <summary>

        /// Required for Windows Form designer support.

        /// </summary>

        public Form1()

        {

            try

            {

                InitializeComponent();

                InitializeScanner();

            }

            catch (Exception ex)

            {

                MessageBox.Show("Failed to initialize component: " + ex.ToString());

                this.Close();

            }

        }

        /*

         * Button1_Click

         *

         */

        /// <summary>

        /// This method is called when the scan button is clicked and will scan the 

        /// barcode. 

        /// </summary>b

        /// <param name="sender">

        /// The calling object that represents the user that sent the message. 

        /// </param>

        /// <param name="e">

        /// Contains event data of the Scan Button control

        /// </param>

        private void button1_Click(object sender, EventArgs e)

        {

            try

            {

                scanner.Scan();

            }

            catch (Exception ex)

            {

                MessageBox.Show("Scan error: " + ex.ToString());

                this.Close();

            }

        }

        /*

         * Scanner1_ScanCompleteEvent

         *

         */

        /// <summary>

        /// This method is called when the scan complete event occurs. The method is

        /// called by its respective handler and then displays a text representation

        /// of the barcode on the display. 

        /// </summary>

        /// <param name="sender">

        /// The calling object that represents the user that sent the message. 

        /// </param>

        /// <param name="e">

        /// Contains event data once the scan is complete

        /// </param>

        delegate void scanner_ScanCompleteDelegate(object sender, ScanCompleteEventArgs e);

        private void scanner_ScanCompleteEvent(object sender, ScanCompleteEventArgs e)

        {

            if (!InvokeRequired)

            {

                textBox1.Text = e.Text;

            }

            else

            {

                Invoke(new scanner_ScanCompleteDelegate(scanner_ScanCompleteEvent),

                    new object[] { sender, e });

            }

        }

    }

}

Step 4: Deploying

When deploying, remember to set the device to PtxPxa27c: ARMV4I_Release

P.S.
The size of the form shouldn't be bigger than 245 x 300

Project Euler Problem 1 (C# vs F#)

2009-07-31T23:28:00.000-07:00

The problem:

If we list all the natural numbers below 10 that are multiples of 3 or 5, we get 3, 5, 6 and 9. The sum of these multiples is 23.

Find the sum of all the multiples of 3 or 5 below 1000.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
 
namespace Euler
{
       class Program
       {
              //Add all the natural numbers below one thousand that are multiples of 3 or 5.
             //0.001 seconds
             static void Main(string[] args)
            {
                   Stopwatch sw = new Stopwatch();
                   sw.Start();
                   int total = 0;
                   for (int i = 1; i <>
                  {
                          if (i % 3 == 0 || i % 5 == 0)
                         {
                                  total += i;
                         }
                 }
 
                Console.WriteLine("Answer: " + total);
                sw.Stop();
                Console.WriteLine(sw.Elapsed);
                Console.WriteLine(sw.ElapsedTicks);
                Console.ReadLine();
           }
  }
}

The F# solution:

F#
#light
//0096404 milliseconds
open System.Diagnostics
let stopWatch = new Stopwatch()
stopWatch.Start()
 
printfn "%A" (List.sum(List.filter (fun n-> (n % 3) = 0 or (n % 5) = 0) [1 .. 999]))
 
stopWatch.Stop();
printfn "%A"stopWatch.Elapsed
printfn "%A"stopWatch.ElapsedTicks
 
open System
Console.ReadKey(true) 

C# does it in

0.001 seconds

3606 ticks

F# does it in

0.0096404 seconds

24380 ticks

There is probably some more efficient way of doing the F#, this was however my first attempt at learning the language. Will have to see how it can do the more tougher problems.

Link Server limited return row size from Informix

2009-05-21T12:53:00.000-07:00

So I ran into the following error while trying to run a query.

"Maximum output rowsize (32767) exceeded"

The problem with this, was that I was using a SQL SERVER link server, called ETS, to an Informix database. The problem was that I could not limit the size on the "Notes", so basically I ended up using an exec and open query just to accomplish this task.

declare @Query varchar(max)

set @Query = N'select

wrkhdr.region_no,

notif.atten,

notif.dba,

notif.owner,

notif.addr1,

notif.addr2,

notif.city,

notif.state,

notif.country,

notif.postal_code,

notif.notif_id,

notif.notif_date,

notif.start_date,

notif.end_date,

wrkhdr.create_user,

wrkhdr.rid,

wrkhdr.license_no,

notif.license_status,

rtrim(CAST(notes.note as nvarchar(250)))

FROM

notification notif

join workq_hdr wrkhdr ON wrkhdr.workq_id = notif.workq_id

left join document_header ON document_header.filing_id = wrkhdr.filing_id

left join document_header_notes ON document_header.recno = document_header_notes.document_header_notes_id

left join notes ON document_header_notes.notes = notes.note_id

where notif.notif_id = ' + convert(varchar(30),@Parameter1)

set @Query = N'select * from openquery(ets2, ''' + REPLACE(@Query, '''', '''''') + ''')'

exec (@Query)

Permutations. The nth permutation from an ordered set of permutations.

2009-01-08T13:11:00.001-08:00

So, I was doing Euler 24 from Project Euler. I thought of an interesting way of find the nth permutation from a set. Here is my solution:

        /*A permutation is an ordered arrangement of objects. For example, 3124 is one possible permutation of the
digits 1, 2, 3 and 4. 
        * If all of the permutations are listed numerically or alphabetically, we call it lexicographic order. The lexicographic permutations of 0, 1 and 2 are:
 
        012   021   102   120   201   210
 
        What is the millionth lexicographic permutation of the digits 0, 1, 2, 3, 4, 5, 6, 7, 8 and 9?
        */
        //.01 seconds
        static void Main(string[] args)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            int[] numbers = new int[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
            char[] letters = new char[] {'a','b','c','d','e','f','g','h','i','j'};
 
            Console.WriteLine(findPermutationAtElement<int>(numbers, 999999));
 
            Console.WriteLine(findPermutationAtElement<char>(letters, 999999));
 
 
            sw.Stop();
            Console.WriteLine(sw.Elapsed);
            Console.ReadLine();
 
        }
 
        private static string findPermutationAtElement<T>(T[] numbers, int p)
        {
            string answer = "";
            ulong totalPerms;
            int position;
            ulong section;
            T[] temp;
            int length = numbers.Length;
 
            //make sure array is ordered
            numbers = numbers.OrderBy(n => n).ToArray();
 
            for (int i = 0; i < length; i++)
            {
                //find number of permutations left
                totalPerms = factorial(numbers.Length);
 
                //find what section number will fall into
                section = totalPerms / (ulong)numbers.Length;
 
                //find whole number index
                position = p / (int)section;
 
                //change p to reflect sub array position
                p = p % (int)section;
 
                //put value at the end
                answer = answer.Insert(answer.Length, numbers.ElementAt(position).ToString());
 
                //remove element from array
                temp = new T[numbers.Length - 1];
 
                for (int j = 0; j < numbers.Length; j++)
                {
                    if (j == position)
                    {
 
                    }
                    else if (j > position)
                    {
                        temp[j - 1] = numbers[j];
                    }
                    else
                    {
                        temp[j] = numbers[j];
                    }
                }
 
                numbers = temp;
 
            }
 
            return answer;
        }
 
        private static ulong factorial(int p)
        {
            if (p == 0)
            {
                return 1;
            }
 
            return (ulong)p * factorial(p - 1);
        }