Viktar Karpach Web Developer Blog about ASP.NET,C#,T-SQL,Javascript,CSS,SilverlightViktar Karpach Web Developer Blog about ASP.NET,C#,T-SQL,Javascript,CSS,Silverlight. New technics and tehnlogies reviews.en-usKarpach Web Developer Bloghttps://www.karpach.com/visual-studio-team-services-assembly-signing.htmhttps://www.karpach.com/visual-studio-team-services-assembly-signing.htmC# .NETHow to sign assembly with as strong name in Visual Studio Team Services?<p>I have self generated password protected key.pfx. That key.pfx file is used to sign some assemblies in my solution. I tried to build that solution in Visual Studio Team Services and it failed right away with the following error:</p> <pre style="color:red">Error MSB3325: Cannot import the following key file: key.pfx. The key file may be password protected. To correct this, try to import the certificate again or manually install the certificate to the Strong Name CSP with the following key container name: VS_KEY_9000008CC1777 </pre> <p>Quick googling shows that I need to run:</p> <pre> sn -i key.pfx VS_KEY_9000008CC1777 </pre> <p>However this command will prompt a password, so this would not work for on a build server. I tried to use automated solution for the above command using following powershell script:</p> <div class="csharp"><pre> Param( [Parameter(Mandatory=$True,Position=1)] [<span class="keyword">string</span>] $PfxFilePath, [<span class="keyword">string</span>] $PfxPassword ) # The path to the snk file we're creating [<span class="keyword">string</span>] $snkFilePath = [IO.Path]::GetFileNameWithoutExtension($PfxFilePath) + <span class="string">&quot;.snk&quot;</span>; # Read <span class="keyword">in</span> the bytes of the pfx file [<span class="keyword">byte</span>[]] $pfxBytes = Get-Content $PfxFilePath -Encoding <span class="className">Byte</span>; # Get a cert <span class="keyword">object</span> <span class="keyword">from</span> the pfx bytes with the <span class="keyword">private</span> key marked <span class="keyword">as</span> exportable $cert = New-<span class="className">Object</span> System.Security.Cryptography.X509Certificates.X509Certificate2( $pfxBytes, $PfxPassword, [Security.Cryptography.X509Certificates.X509KeyStorageFlags]::Exportable); # Export a CSP blob <span class="keyword">from</span> the cert (which <span class="keyword">is</span> the same format <span class="keyword">as</span> an SNK file) [<span class="keyword">byte</span>[]] $snkBytes = ([Security.Cryptography.RSACryptoServiceProvider]$cert.PrivateKey).ExportCspBlob($<span class="keyword">true</span>); # Write the CSP blob/SNK bytes to the snk file [IO.File]::WriteAllBytes([IO.Path]::Combine([IO.Path]::GetDirectoryName($PfxFilePath), $snkFilePath), $snkBytes); </pre></div> <p>However this didn't work for Visual Studio Teams Services hosted build agent. I suspect that hosted build server has some kind of environment protection and above certificate installation fails silently. Then I decided to extract key.snk file from key.pfx and use that file to sign assemblies. This approach works, but it is not secure, since I would need to store private unprotected key file in source control. So, I came up with the idea to dynamically extract key.snk using following powershell script:</p> <div class="csharp"><pre> Param( [Parameter(Mandatory=$True,Position=1)] [<span class="keyword">string</span>] $PfxFilePath, [<span class="keyword">string</span>] $PfxPassword ) # The path to the snk file we're creating [<span class="keyword">string</span>] $snkFilePath = [IO.Path]::GetFileNameWithoutExtension($PfxFilePath) + <span class="string">&quot;.snk&quot;</span>; # Read <span class="keyword">in</span> the bytes of the pfx file [<span class="keyword">byte</span>[]] $pfxBytes = Get-Content $PfxFilePath -Encoding <span class="className">Byte</span>; # Get a cert <span class="keyword">object</span> <span class="keyword">from</span> the pfx bytes with the <span class="keyword">private</span> key marked <span class="keyword">as</span> exportable $cert = New-<span class="className">Object</span> System.Security.Cryptography.X509Certificates.X509Certificate2( $pfxBytes, $PfxPassword, [Security.Cryptography.X509Certificates.X509KeyStorageFlags]::Exportable); # Export a CSP blob <span class="keyword">from</span> the cert (which <span class="keyword">is</span> the same format <span class="keyword">as</span> an SNK file) [<span class="keyword">byte</span>[]] $snkBytes = ([Security.Cryptography.RSACryptoServiceProvider]$cert.PrivateKey).ExportCspBlob($<span class="keyword">true</span>); # Write the CSP blob/SNK bytes to the snk file [IO.File]::WriteAllBytes([IO.Path]::Combine([IO.Path]::GetDirectoryName($PfxFilePath), $snkFilePath), $snkBytes); </pre></div> <p>Then I added two variables to build definition:</p> <pre>CertPath = $(Build.SourcesDirectory)\key.pfx CertPass = password (clicked on a lock to secure value of variable) </pre> <p>After that I added powershell task with following arguments:</p> <p>-PfxFilePath "$(CertPath)" -PfxPassword "$(CertPass)"</p> <p>This time the build passed and assemblies were signed successfully.</p> Mon, 24 Jul 2017 00:00:00 -06002017-07-24T00:00:00-06:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/how-to-integrate-ci-cd-with-visual-studio-team-services.htmhttps://www.karpach.com/how-to-integrate-ci-cd-with-visual-studio-team-services.htmASP.NETHow to integrate CI/CD with Visual Studio Team Services?<p>Microsoft provides free Visual Studio Team Services for small team. It has free unlimited Git repositories and 4 hours per month to run builds, which should be more than enough for my projects.</p> <p>I already integrated karpach.com front end and admin site. In this post I'll document how I integrated redboxnewreleases.com.</p> <h2>Step 1 Created build definition</h2> <p>My favorite browser is Google Chrome, but for below process I used Microsoft Edge, since some things didn't work in Google Chrome (e.g. reordering of build tasks).</p> <p>I didn't use any templates and started with an empty process. First of all lets tried to build my solution. I used following tasks as is:</p> <ol> <li>Get sources (point to git repository)</li> <li>NuGet Restore **/*.sln (default settings)</li> <li>Build solution **/*.sln (default setting)</li> </ol> <p>In my case the build failed with following error:</p> <pre>Error MSB4226: The imported project "C:\Program Files (x86)\Microsoft Visual Studio\2017\ Enterprise\MSBuild\ExtensionPack\4.0\MSBuild.ExtensionPack.tasks" was not found.</pre><br/> <p>In my previous CI system I used ExtensionPack for assembly version modification. This is old approach, so I just removed that task reference and used different approach to specify version later in the post.</p> <p>I pushed a change, but it didn't trigger any build. I added a trigger then:</p> <ol> <li>Opened a Triggers tab.</li> <li>Enabled continuos integration trigger.</li> <li>Set repository setting to build on any master branch change.</li> <li>Went to options tab and set &quot;Default agent queue&quot;: Hosted VS2017.</li> <li>Pressed Save &amp; queue</li> </ol> <p>This time build succeeded. Then I added assembly versioning back:</p> <ol> <li>Downloaded ApplyVersionToAssemblies.ps1 from https://github.com/tfsbuildextensions/CustomActivities.</li> <li>Deleted &quot;$Env:TF_BUILD -and -not&quot; from that powershell script.</li> <li>Replaced TF_BUILD with just BUILD in ApplyVersionToAssemblies.ps1.</li> <li>Added ApplyVersionToAssemblies.ps1 to Git repository.</li> <li>Went back to build definition web editor, switched to Variables tab. Added MajorVersion and MinorVersion variables there.</li> <li>Switched to the Options tab and filled &quot;Build number format&quot; field with $(BuildDefinitionName)_$(MajorVersion).$(MinorVersion).$(Year:yy)$(DayOfYear)$(Rev:.rr)).</li> <li>Made sure that AssemblyVersion and AssemblyFileVersion lines present in AssemblyInfo files (powershell script does replacement in those files).</li> <li>Added powershell task to run ApplyVersionToAssemblies.ps1.</li> <li>Moved up powershell task to run after Get sources task.</li> </ol> <p>After that I modified msbuild task to produce a package for web deployment:</p> <ol> <li>Change build to build web csproj file instead of solution file</li> <li>Specified configuration: Release</li> <li>Used following MSBuild Arguments: /T:Package /P:PackageLocation=..\Artifacts\package.zip</li> <li>Added Publish Build Artifacts task: Path to Publish = Artifacts\package.zip, Artifact Name = Redbox</li> </ol> <p><img src="http://www.karpach.com/images/uploaded/redboxnewreleases-build-definition.png" alt="Build definition" title="Build definition"></p> <h2>Step 2 Created release definition</h2> <ol> <li>Started from Empty template.</li> <li>Picked my build from previous step.</li> <li>Added a deployment group phase task.</li> <li>Added &quot;IIS Web App Deploy (Preview)&quot; task.</li> <li>Created deployment group.</li> <li>Ran generated powershell script on my VPS hosting server.</li> <li>Specified website name: redboxnewrleases.com.</li> <li>Specified package folder using browse button to point to package.zip: $(System.DefaultWorkingDirectory)/Redbox-CI/Redbox/package.zip).</li> <li>Set trigger for continuous deployment.</li> <li>Optionally modified release name format in General tab: Release-$(Build.BuildNumber))</li> </ol> <p><img src="http://www.karpach.com/images/uploaded/redboxnewreleases-release-definition.png" alt="Release definition" title="Release definition"></p> Sat, 08 Jul 2017 00:00:00 -06002017-07-08T00:00:00-06:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/twitter-application-only-authentication.htmhttps://www.karpach.com/twitter-application-only-authentication.htmC# .NETHow to get latest tweets from Twitter REST API?<p>My blog uses twitter REST API to pull up my latest tweets to the right side navigation. About a year ago twitter deprecated 1.0 API and now every request needs to be authenticated. According to twitter's documentation I can use application-only authentication, since I am only getting tweets and don't post anything.</p> <p>Application-only authentication approach consists of two steps:</p><br> <ol> <li>Get access token from https://api.twitter.com/oauth2/token</li> <li>Use access token for any read only operations (get posts, friends, followers, user information or search tweets)</li> </ol> <p>I didn't find any .NET bare-bones example that suits my needs. The code below don't need any third party libraries. It is .NET 4.5 (I used some dynamic and async / await).</p> <p>Here is how you get access token (step 1 from the above):</p> <div class="csharp"><pre><span class="keyword">public</span> async Task&lt;<span class="keyword">string</span>&gt; GetAccessToken() { <span class="keyword">var</span> httpClient = <span class="keyword">new</span> HttpClient(); <span class="keyword">var</span> request = <span class="keyword">new</span> HttpRequestMessage(HttpMethod.Post, <span class="string">"https://api.twitter.com/oauth2/token "</span>); <span class="keyword">var</span> customerInfo = <span class="className">Convert</span>.ToBase64String(<span class="keyword">new</span> UTF8Encoding() .GetBytes(OAuthConsumerKey + <span class="string">":"</span> + OAuthConsumerSecret)); request.Headers.Add(<span class="string">"Authorization"</span>, <span class="string">"Basic "</span> + customerInfo); request.Content = <span class="keyword">new</span> StringContent(<span class="string">"grant_type=client_credentials"</span>, Encoding.UTF8, <span class="string" style="font-family: tahoma, arial, verdana, sans-serif;">"application/x-www-form-urlencoded"</span><span style="font-family: tahoma, arial, verdana, sans-serif;">);</span></pre><pre> HttpResponseMessage response = await httpClient.SendAsync(request); <span class="keyword">string</span> json = await response.Content.ReadAsStringAsync(); <span class="keyword">var</span> serializer = <span class="keyword">new</span> JavaScriptSerializer(); <span class="keyword">dynamic</span> item = serializer.Deserialize&lt;<span class="keyword">object</span>&gt;(json); <span class="keyword">return</span> item[<span class="string">"access_token"</span>]; } </pre></div> <br> <p>Here is how you get tweets:</p> <div class="csharp"><pre><span class="keyword">public</span> async Task&lt;IEnumerable&lt;<span class="keyword">string</span>&gt;&gt; GetTweets(<span class="keyword">string</span> userName,<span class="keyword">int</span> count, <span class="keyword">string</span> accessToken = <span class="keyword">null</span>) { <span class="keyword">if</span> (accessToken == <span class="keyword">null</span>) { accessToken = await GetAccessToken(); } <span class="keyword">var</span> requestUserTimeline = <span class="keyword">new</span> HttpRequestMessage(HttpMethod.Get, <span class="keyword">string</span>.Format(<span class="string">"https://api.twitter.com/1.1/statuses/user_timeline.json? count={0}&amp;screen_name={1}&amp;trim_user=1&amp;exclude_replies=1"</span>, count, userName)); requestUserTimeline.Headers.Add(<span class="string">"Authorization"</span>, <span class="string">"Bearer "</span> + accessToken); <span class="keyword">var</span> httpClient = <span class="keyword">new</span> HttpClient(); HttpResponseMessage responseUserTimeLine = await httpClient.SendAsync(requestUserTimeline); <span class="keyword">var</span> serializer = <span class="keyword">new</span> JavaScriptSerializer(); <span class="keyword">dynamic</span> json = serializer.Deserialize&lt;<span class="keyword">object</span>&gt;(await responseUserTimeLine.Content.ReadAsStringAsync()); <span class="keyword">var</span> enumerableTweets = (json <span class="keyword">as</span> IEnumerable&lt;<span class="keyword">dynamic</span>&gt;); <span class="keyword">if</span> (enumerableTweets == <span class="keyword">null</span>) { <span class="keyword">return</span> <span class="keyword">null</span>; } <span class="keyword">return</span> enumerableTweets.Select(t =&gt; (<span class="keyword">string</span>)(t[<span class="string">"text"</span>].ToString())); } </pre></div> <br> <p>See complete console application at github repository <a href="https://github.com/karpach/twitter" target="_blank">Twitter .NET C# Sample Application</a></p>Mon, 18 Aug 2014 00:00:00 -06002014-08-18T00:00:00-06:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/subversion-to-git-migration.htmhttps://www.karpach.com/subversion-to-git-migration.htmBashHow to migrate from subversion to git with almost no down time?<img src="http://www.karpach.com/images/uploaded/svn-to-git.png" alt="Svn to git migration"/> <p>Last year I was in charge of SVN to Git migration at company that I work for. We wanted to migrate the history as well. In our case there were about 40,000 revisions made during last 8 years. In order to minimize developers downtime I did a lot of scripting preparation ahead of time. Actual switch from SVN to Git took less then 2 hours. Here are the steps that we took.</p> <h2>1. Retrieve a list of all committers</h2> <p>You'll need to create a list of users that have committed to the SVN repo and then convert those users over to the Git format as Subversion only supplies the username of the person committing and not the username and email. To retrieve the list of users from SVN, create a new folder, right click and select Git Bash Here to open a Git command window. Run the following command:</p> <div class="bash"><pre> svn log http://url/to/svn/repository -q | awk -F '|' '/^r/ {sub(&quot;^ &quot;, &quot;&quot;, $2); sub(&quot; $&quot;, &quot;&quot;, $2); print $2&quot; = &quot;$2&quot; &lt;&quot;$2&quot;&gt;&quot;}' | sort -u &gt; users.txt </pre></div> <br/> <p>Note: this will take a couple of minutes to complete based on the size of your repository, number of commits, and number of committers. </p> <p>The text file will have separate lines for each committer and will need to be transformed from <i>vkarpach = vkarpach &lt;vkarpach&gt;</i> to <i>vkarpach = Viktar Karpach &lt;vkarpach@company.com&gt;</i></p> <h2>2. Clone the repository using git-svn</h2> <p>Note - this step will take hours to complete, so it is suggested to run this step over night on dedicated box. Run the following command to convert the repository to a Git repository:</p> <div class="bash"><pre> git svn clone --stdlayout --no-metadata -A users.txt http://url/to/svn/repository dest_dir-tmp </pre></div> <h2>3. Make a copy of this folder.</h2> <p>git svn clone takes a lot of time. For our main project it took 48 hours for about 18000 commits. Make a copy of this folder, so you don't need to do it again. Create scripts for next steps, so when you are ready to switch you can do it quickly.</p> <h2>4. Fetch latest commits.</h2> <p>The team continued to use Subversion until a very last moment, so while working on migration scripts time to time I had to fetch latest commits.</p> <div class="bash"><pre> git svn fetch git reset --hard trunk </pre></div> <h2>5. Clean up script.</h2> <p>Delete tags</p> <div class="bash"><pre> for t in `git branch -r | grep 'tags/' | sed s_tags/__` ; do git tag $t tags/$t^ git branch -d -r tags/$t done </pre></div> <br/> <p>Delete trunk, since we will use master from now on.</p> <div class="bash"><pre> git branch -d -r trunk </pre></div> <br/> <p>Remove SVN references</p> <div class="bash"><pre> git config --remove-section svn-remote.svn rm -rf .git/svn .git/{logs/,}refs/remotes/svn/ </pre></div> <br/> <p>And finally convert the remaining remote branches to local branches</p> <div class="bash"><pre> git config remote.origin.url . git config --add remote.origin.fetch +refs/remotes/*:refs/heads/* git fetch </pre></div> <br/> <p>Remove remote branches:</p> <div class="bash"><pre> for t in `git branch -r` ; do git branch -d -r $t done </pre></div> <br/> <p>Git doesn't support space in branch names, so git svn fetch replaced spaces with %20. I think it is more aesthetic to use underscore instead of %20:</p> <div class="bash"><pre> for t in `git branch -a|grep '%20'` ; do newName=`echo $t | sed 's/%20/-/g'` git branch -m $t $newName done </pre></div> <br/> <p>You might want to delete some unused branches:</p> <div class="bash"><pre> for t in `cat ../list_of_branches_for_deletion.txt`; do git branch -D $t done </pre></div> <br/> <p>Where list_of_branches_for_deletion.txt contains branch names that will be deleted. Use following code to populate this files:</p> <div class="bash"><pre> git branch -a > ../list_of_branches_for_deletion.txt </pre></div> <br/> <p>Manually edit list_of_branches_for_deletion.txt file. Leave only those branches that you want to delete.</p> <h2>6. Replace any svn externals with git submodules</h2> <div class="bash"><pre> git submodule add ssh://git@git.company.com:7999/ProjectName/external_repo.git ExternalFolderName git commit -m "Added submodules" </pre></div> <br/> <p>Use sumbodules only for external projects that don't change very often. We had to combine our internal projects in one git repository, since it is hard to maintain submodules for rapidly changing projects. Each project gets its own directory in git repository:</p> <p>Before migration:</p> <div class="code"><pre> svn_main_project external_1 external_1_folder_1 external_1_folder_2 external_2 external_2_folder_1 external_2_folder_2 svn_main_project_folder_1 svn_main_project_folder_2 </pre></div> <br/> <p>Where svn_main_project has to externals external_1 and external_2.</p> <p>After migration</p> <div class="code"><pre> git svn_main_project svn_main_project_folder_1 svn_main_project_folder_2 external_1 external_1_folder_1 external_1_folder_2 external_2 external_2_folder_1 external_2_folder_2 </pre></div> <br/> <p>You can use following bash script to push everything in sub_folder, so later you can combine repositories. The script will modify commit history as well.</p> <div class="bash"><pre> git filter-branch --index-filter \ 'git ls-files -s | sed "s-\t\"*-&sub_folder/-" | GIT_INDEX_FILE=$GIT_INDEX_FILE.new \ git update-index --index-info && mv "$GIT_INDEX_FILE.new" "$GIT_INDEX_FILE" || true' HEAD </pre></div> <h2>7. Get your repository onto the server</h2> <p>Create a repository on your git server.</p> <p>Init local repository</p> <div class="bash"><pre> git init </pre></div> <br/> <p>Use following if you are combining repositories:</p> <div class="bash"><pre> git remote add external_1 ../external_1/ git pull external_1 master git remote rm external_1 </pre></div> <br/> <p>Add gitignore</p> <div class="bash"><pre> cp ../gitignore.txt .gitignore git add . git commit -m "Added .gitignore" </pre></div> <br/> <p>Push all branches in one shot:</p> <div class="bash"><pre> git remote add origin ssh://git@git.company.com:7999/repo.git git push --all origin </pre></div> Mon, 27 Jan 2014 00:00:00 -07002014-01-27T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/synctoy-task-scheduler-no-console-window.htmhttps://www.karpach.com/synctoy-task-scheduler-no-console-window.htmPowerShellHow to hide SyncToy console window in windows Task Scheduler?<p><a href="http://www.microsoft.com/en-us/download/details.aspx?id=15155" target="_blank">SyncToy</a> is a free app from Microsoft that synchronizes files and folders between different locations. It even has a command line version (SyncToyCmd.exe), which can be used in windows Task Scheduler. Unfortunately Task Scheduler build-in "Hidden" mode didn't hide console window, so I composed from different sources a little vbs script. This script can be used in Task Scheduler, so no console window will show up.</p> <p><b>SyncToy.vbs</b></p> <div class="powershell"><pre> Const HIDDEN_WINDOW = 12 strComputer = <span class="string">&quot;.&quot;</span> Set objWMIService = GetObject(<span class="string">&quot;winmgmts:&quot;</span> _ &amp; <span class="string">&quot;{impersonationLevel=impersonate}!\\&quot; &amp; strComputer &amp; &quot;</span>\root\cimv2&quot;) Set objStartup = objWMIService.Get(<span class="string">&quot;Win32_ProcessStartup&quot;</span>) Set objConfig = objStartup.SpawnInstance_ objConfig.ShowWindow = HIDDEN_WINDOW Set objProcess = GetObject(<span class="string">&quot;winmgmts:root\cimv2:Win32_Process&quot;</span>) errReturn = objProcess.Create(<span class="string">&quot;C:\Program Files\Synctoy 2.1\SynctoyCmd.exe -R&quot;</span> , <span class="keyword">null</span>, objConfig, intProcessID) </pre></div>Thu, 16 Jan 2014 00:00:00 -07002014-01-16T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/first-data-wcf-e4-integration.htmhttps://www.karpach.com/first-data-wcf-e4-integration.htmC# .NETHow to integrate First Data Gateway e4 with .NET WCF?<p>Recently I worked on a project that needed integration with First Data Getaway e4 payment processing. Starting from v12 First Data introduced HMAC hash security. I found <a href="https://firstdata.zendesk.com/entries/37396863-C-REST-Sample-Code-For-Use-with-v12-v13" target="_blank">.NET REST sample</a>, but there is nothing for WCF or Web Services. This new HMAC security feature makes usage of Web Service a little challenging. I hope this guide will help somebody.</p> <p>Step 1. Add service reference:</p> <br/> <img src="http://www.karpach.com/images/uploaded/Add-Service-Reference.png" alt="WCF: Add Service Reference"/> <br/><br/> <img src="http://www.karpach.com/images/uploaded/Add-Service-Reference-Step-2.png" alt="WCF: Add Service Reference Step 2"/> <br/> <p>Step 2. Create <a href="https://firstdata.zendesk.com/entries/21510561-first-data-global-gateway-e4sm-demo-accounts" target="_blank">demo account</a></p> <p>Step 3. Use following code wrapper to charge a card:</p> <div class="csharp"><pre> <span class="keyword">public</span> <span class="keyword">class</span> Merchant { <span class="keyword">private</span> <span class="keyword">readonly</span> <span class="keyword">string</span> _gatewayId; <span class="keyword">private</span> <span class="keyword">readonly</span> <span class="keyword">string</span> _password; <span class="keyword">private</span> <span class="keyword">readonly</span> <span class="keyword">string</span> _keyId; <span class="keyword">private</span> <span class="keyword">readonly</span> <span class="keyword">string</span> _hmac; <span class="keyword">private</span> <span class="keyword">readonly</span> <span class="keyword">bool</span> _isDemo; <span class="keyword">private</span> <span class="keyword">const</span> <span class="keyword">string</span> ProdUrl = <span class="string">&quot;https://api.globalgatewaye4.firstdata.com/transaction/v12&quot;</span>; <span class="keyword">private</span> <span class="keyword">const</span> <span class="keyword">string</span> TestUrl = <span class="string">&quot;https://api.demo.globalgatewaye4.firstdata.com/transaction/v12&quot;</span>; <span class="keyword">public</span> Merchant(<span class="keyword">string</span> gatewayId, <span class="keyword">string</span> password, <span class="keyword">string</span> hmac, <span class="keyword">string</span> keyId, <span class="keyword">bool</span> isDemo = <span class="keyword">true</span>) { _gatewayId = gatewayId; _password = password; _hmac = hmac; _keyId = keyId; _isDemo = isDemo; } <span class="keyword">public</span> MerchantResponse Charge(<span class="keyword">int</span> orderId, <span class="keyword">string</span> cardHoldersName, <span class="keyword">string</span> cardNumber, <span class="keyword">decimal</span> amount, <span class="keyword">int</span> expirationMonth, <span class="keyword">int</span> expirationYear, <span class="keyword">int</span> ccv, <span class="keyword">string</span> address, <span class="keyword">string</span> city, <span class="keyword">string</span> state, <span class="keyword">string</span> zip) { <span class="keyword">var</span> client = <span class="keyword">new</span> ServiceSoapClient(<span class="keyword">new</span> BasicHttpBinding(BasicHttpSecurityMode.Transport), <span class="keyword">new</span> EndpointAddress(_isDemo ? TestUrl : ProdUrl)); client.ChannelFactory.Endpoint.Behaviors.Add(<span class="keyword">new</span> HmacHeaderBehaivour(_hmac,_keyId)); TransactionResult result = client.SendAndCommit(<span class="keyword">new</span> Transaction { ExactID = _gatewayId, Password = _password, Transaction_Type = <span class="string">&quot;00&quot;</span>, Card_Number = cardNumber, CardHoldersName = cardHoldersName, DollarAmount = amount.ToString(<span class="string">&quot;F&quot;</span>), Expiry_Date = <span class="keyword">string</span>.Format(<span class="string">&quot;{0:D2}{1}&quot;</span>,expirationMonth,expirationYear), Customer_Ref = orderId.ToString(), VerificationStr1 = <span class="keyword">string</span>.Format(<span class="string">&quot;{0}|{1}|{2}|{3}|US&quot;</span>,address,zip,city,state), VerificationStr2 = ccv.ToString() }); <span class="keyword">var</span> response = <span class="keyword">new</span> MerchantResponse { IsTransactionApproved = result.Transaction_Approved, IsError = result.Transaction_Error }; <span class="keyword">if</span> (!result.Transaction_Approved &amp;&amp; !result.Transaction_Error) { response.Message = <span class="keyword">string</span>.Format(<span class="string">&quot;Error {0}: {1}&quot;</span>, result.Bank_Resp_Code, result.Bank_Message); } <span class="keyword">if</span> (!result.Transaction_Approved &amp;&amp; result.Transaction_Error) { response.Message = <span class="keyword">string</span>.Format(<span class="string">&quot;Error {0}: {1}&quot;</span>,result.EXact_Resp_Code,result.EXact_Message); } <span class="keyword">if</span> (result.Transaction_Approved) { response.Message = result.Authorization_Num; } <span class="keyword">return</span> response; } <span class="keyword">class</span> HmacHeaderBehaivour: IEndpointBehavior { <span class="keyword">private</span> <span class="keyword">readonly</span> <span class="keyword">string</span> _hmac; <span class="keyword">private</span> <span class="keyword">readonly</span> <span class="keyword">string</span> _keyId; <span class="keyword">public</span> HmacHeaderBehaivour(<span class="keyword">string</span> hmac, <span class="keyword">string</span> keyId) { _hmac = hmac; _keyId = keyId; } <span class="keyword">public</span> <span class="keyword">void</span> Validate(ServiceEndpoint endpoint) { } <span class="keyword">public</span> <span class="keyword">void</span> AddBindingParameters(ServiceEndpoint endpoint, BindingParameterCollection bindingParameters) { } <span class="keyword">public</span> <span class="keyword">void</span> ApplyDispatchBehavior(ServiceEndpoint endpoint, EndpointDispatcher endpointDispatcher) { } <span class="keyword">public</span> <span class="keyword">void</span> ApplyClientBehavior(ServiceEndpoint endpoint, ClientRuntime clientRuntime) { clientRuntime.MessageInspectors.Add(<span class="keyword">new</span> HmacHeaderInspector(_hmac,_keyId)); } } <span class="keyword">class</span> HmacHeaderInspector: IClientMessageInspector { <span class="keyword">private</span> <span class="keyword">readonly</span> <span class="keyword">string</span> _hmac; <span class="keyword">private</span> <span class="keyword">readonly</span> <span class="keyword">string</span> _keyId; <span class="keyword">public</span> HmacHeaderInspector(<span class="keyword">string</span> hmac,<span class="keyword">string</span> keyId) { _hmac = hmac; _keyId = keyId; } <span class="keyword">public</span> <span class="keyword">object</span> BeforeSendRequest(<span class="keyword">ref</span> Message request, IClientChannel channel) { MessageBuffer buffer = request.CreateBufferedCopy(<span class="className">Int32</span>.MaxValue); request = buffer.CreateMessage(); Message msg = buffer.CreateMessage(); ASCIIEncoding encoder = <span class="keyword">new</span> ASCIIEncoding(); <span class="keyword">var</span> sb = <span class="keyword">new</span> <span class="className">StringBuilder</span>(); <span class="keyword">var</span> xmlWriter = XmlWriter.Create(sb, <span class="keyword">new</span> XmlWriterSettings { OmitXmlDeclaration = <span class="keyword">true</span> }); <span class="keyword">var</span> writer = XmlDictionaryWriter.CreateDictionaryWriter(xmlWriter); msg.WriteStartEnvelope(writer); msg.WriteStartBody(writer); msg.WriteBodyContents(writer); xmlWriter.WriteEndElement(); xmlWriter.WriteEndElement(); writer.Flush(); <span class="keyword">string</span> body = sb.ToString().Replace(<span class="string">&quot; /&gt;&quot;</span>,<span class="string">&quot;/&gt;&quot;</span>); <span class="keyword">byte</span>[] xmlByte = encoder.GetBytes(body); SHA1CryptoServiceProvider sha1Crypto = <span class="keyword">new</span> SHA1CryptoServiceProvider(); <span class="keyword">string</span> hash = <span class="className">BitConverter</span>.ToString(sha1Crypto.ComputeHash(xmlByte)).Replace(<span class="string">&quot;-&quot;</span>, <span class="string">&quot;&quot;</span>); <span class="keyword">string</span> hashedContent = hash.ToLower(); <span class="comment">//assign values to hashing and header variables</span> <span class="keyword">string</span> time = <span class="className">DateTime</span>.UtcNow.ToString(<span class="string">&quot;yyyy-MM-ddTHH:mm:ssZ&quot;</span>); <span class="keyword">string</span> hashData = <span class="string">&quot;POST\ntext/xml; charset=utf-8\n&quot;</span> + hashedContent + <span class="string">&quot;\n&quot;</span> + time + <span class="string">&quot;\n/transaction/v12&quot;</span>; <span class="comment">//hmac sha1 hash with key + hash_data</span> HMAC hmacSha1 = <span class="keyword">new</span> HMACSHA1(Encoding.UTF8.GetBytes(_hmac)); <span class="comment">//key</span> <span class="keyword">byte</span>[] hmacData = hmacSha1.ComputeHash(Encoding.UTF8.GetBytes(hashData)); <span class="comment">//data</span> <span class="comment">//base64 encode on hmac_data</span> <span class="keyword">string</span> base64Hash = <span class="className">Convert</span>.ToBase64String(hmacData); HttpRequestMessageProperty httpRequestMessage; <span class="keyword">object</span> httpRequestMessageObject; <span class="keyword">if</span> (request.Properties.TryGetValue(HttpRequestMessageProperty.Name, <span class="keyword">out</span> httpRequestMessageObject)) { httpRequestMessage = httpRequestMessageObject <span class="keyword">as</span> HttpRequestMessageProperty; httpRequestMessage.Headers[<span class="string">&quot;X-GGe4-Content-SHA1&quot;</span>] = hashedContent; httpRequestMessage.Headers[<span class="string">&quot;X-GGe4-Date&quot;</span>] = time; httpRequestMessage.Headers[<span class="string">&quot;Authorization&quot;</span>] = <span class="string">&quot;GGE4_API &quot;</span> + _keyId + <span class="string">&quot;:&quot;</span> + base64Hash; } <span class="keyword">else</span> { httpRequestMessage = <span class="keyword">new</span> HttpRequestMessageProperty(); httpRequestMessage.Headers[<span class="string">&quot;X-GGe4-Content-SHA1&quot;</span>] = hashedContent; httpRequestMessage.Headers[<span class="string">&quot;X-GGe4-Date&quot;</span>] = time; httpRequestMessage.Headers[<span class="string">&quot;Authorization&quot;</span>] = <span class="string">&quot;GGE4_API &quot;</span> + _keyId + <span class="string">&quot;:&quot;</span> + base64Hash; request.Properties.Add(HttpRequestMessageProperty.Name, httpRequestMessage); } <span class="keyword">return</span> <span class="keyword">null</span>; } <span class="keyword">public</span> <span class="keyword">void</span> AfterReceiveReply(<span class="keyword">ref</span> Message reply, <span class="keyword">object</span> correlationState) { } } } <span class="keyword">public</span> <span class="keyword">class</span> MerchantResponse { <span class="keyword">public</span> <span class="keyword">bool</span> IsTransactionApproved { <span class="keyword">get</span>; <span class="keyword">set</span>; } <span class="keyword">public</span> <span class="keyword">bool</span> IsError { <span class="keyword">get</span>; <span class="keyword">set</span>; } <span class="keyword">public</span> <span class="keyword">string</span> Message { <span class="keyword">get</span>; <span class="keyword">set</span>; } } </pre></div> <br/> <p>As you can see "Charge" method is simple, but HMAC requirement makes it challenging. HMAC hash is calculated based on SOAP request body, which is not accessible through ServiceSoapClient. Also we need to modify HTTP request header. Both things can be done using IClientMessageInspector or IClientMessageFormatter. In my implementation I used IClientMessageInspector, since it is a little easier to integrate it with ServiceSoapClient. I didn't find any easy way to get serialized SOAP body of the request, so using Fiddler I came up with following magic code:</p> <br/> <div class="csharp"><pre> <span class="keyword">var</span> sb = <span class="keyword">new</span> <span class="className">StringBuilder</span>(); <span class="keyword">var</span> xmlWriter = XmlWriter.Create(sb, <span class="keyword">new</span> XmlWriterSettings { OmitXmlDeclaration = <span class="keyword">true</span> }); <span class="keyword">var</span> writer = XmlDictionaryWriter.CreateDictionaryWriter(xmlWriter); msg.WriteStartEnvelope(writer); msg.WriteStartBody(writer); msg.WriteBodyContents(writer); xmlWriter.WriteEndElement(); xmlWriter.WriteEndElement(); writer.Flush(); <span class="keyword">string</span> body = sb.ToString().Replace(<span class="string">&quot; /&gt;&quot;</span>,<span class="string">&quot;/&gt;&quot;</span>); </pre></div> <br/> <p>The code above gives me serialized SOAP envelope without SOAP headers string. This string is used for HMAC calculation and at the end X-GGe4-Content-SHA1, X-GGe4-Date, Authorization are added to HTTP header.</p> <p>Tips: You might get 401 errors. Use Fiddler to see actual error. Make sure that you saved generated HMAC key (generate button doesn't save it).</p>Tue, 07 Jan 2014 00:00:00 -07002014-01-07T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/save-for-android-photoshop-javascript.htmhttps://www.karpach.com/save-for-android-photoshop-javascript.htmAndroidSave for android photoshop javascript<p>Due to different pixel density of different android devices developer / designer need to create different versions of graphic artifacts.</p> <p>Recently I've been trying to create my first Android application and got tired of creating image artifacts for different pixel density. As a result I came up with a following photoshop javascript that automates with tedious task.</p> <div class="javascript"><pre> <span class="keyword">var</span> docRef = activeDocument; <span class="keyword">function</span> saveAs(path,filename,width,height,dpi) { savedState = docRef.activeHistoryState docRef.resizeImage (width, height, dpi); <span class="keyword">var</span> folder = <span class="keyword">new</span> Folder (path); <span class="keyword">if</span> (!folder.exists) { folder.create(); } pngFile = <span class="keyword">new</span> File( path + filename) pngSaveOptions = <span class="keyword">new</span> PNGSaveOptions() app.activeDocument.saveAs(pngFile, pngSaveOptions, <span class="keyword">true</span>, Extension.LOWERCASE) docRef.activeHistoryState = savedState } <span class="keyword">function</span> main(isIcon) { <span class="keyword">if</span> (documents.length == 0) { alert(<span class="string">&quot;There are no documents open.&quot;</span>); <span class="keyword">return</span>; } <span class="keyword">try</span> { Path = app.activeDocument.path; } <span class="keyword">catch</span> (exception) { docRef.saveAs(File.saveDialog (<span class="string">&quot;Save image&quot;</span>,[<span class="string">&quot;*.psd&quot;</span>,<span class="string">&quot;*.png&quot;</span>,<span class="string">&quot;*.jpg&quot;</span>])); } Path = app.activeDocument.path; <span class="keyword">var</span> Name = app.activeDocument.name.replace(/\.[^\.]+$/, <span class="string">''</span>); startRulerUnits = app.preferences.rulerUnits; app.preferences.rulerUnits = Units.PIXELS; globSavedState = docRef.activeHistoryState; <span class="keyword">try</span> { docRef.mergeVisibleLayers(); } <span class="keyword">catch</span> (exception) { alert(<span class="string">&quot;Please select visible layer.&quot;</span>); <span class="keyword">return</span>; } <span class="keyword">if</span> (isIcon) { saveAs(Path + <span class="string">&quot;/drawable-xxhdpi/&quot;</span> , Name + <span class="string">&quot;.png&quot;</span>,144,144,480); saveAs(Path + <span class="string">&quot;/drawable-xhdpi/&quot;</span> , Name + <span class="string">&quot;.png&quot;</span>,96,96,320); saveAs(Path + <span class="string">&quot;/drawable-hdpi/&quot;</span> ,Name + <span class="string">&quot;.png&quot;</span>,72,72,240); saveAs(Path + <span class="string">&quot;/drawable-mdpi/&quot;</span> ,Name + <span class="string">&quot;.png&quot;</span>,48,48,160); saveAs(Path + <span class="string">&quot;/drawable-ldpi/&quot;</span> ,Name + <span class="string">&quot;.png&quot;</span>,36,36,120); } <span class="keyword">else</span> { <span class="keyword">var</span> dpi = 480; saveAs(Path + <span class="string">&quot;/drawable-xxhdpi/&quot;</span> , Name + <span class="string">&quot;.png&quot;</span>, docRef.width * dpi / docRef.resolution, docRef.height * dpi / docRef.resolution, dpi); dpi = 320; saveAs(Path + <span class="string">&quot;/drawable-xhdpi/&quot;</span> , Name + <span class="string">&quot;.png&quot;</span>, docRef.width * dpi / docRef.resolution,docRef.height * dpi / docRef.resolution, dpi); dpi = 240; saveAs(Path + <span class="string">&quot;/drawable-hdpi/&quot;</span> , Name + <span class="string">&quot;.png&quot;</span>, docRef.width * dpi / docRef.resolution ,docRef.height * dpi / docRef.resolution, dpi); dpi = 160; saveAs(Path + <span class="string">&quot;/drawable-mdpi/&quot;</span> , Name + <span class="string">&quot;.png&quot;</span>, docRef.width * dpi / docRef.resolution,docRef.height * dpi / docRef.resolution, dpi); dpi = 120; saveAs(Path + <span class="string">&quot;/drawable-ldpi/&quot;</span> , Name + <span class="string">&quot;.png&quot;</span>, docRef.width * dpi / docRef.resolution, docRef.height * dpi / docRef.resolution, dpi); } docRef.activeHistoryState = globSavedState; app.preferences.rulerUnits = startRulerUnits; docRef.save(); } </pre></div> <br/> <p>main(true) generates following:<br/><br/> 480dpi 144x144 - drawable-xxhdpi<br/> 320dpi 96x96 - drawable-xhdpi<br/> 240dpi 72x72 - drawable-hdpi<br/> 160dpi 48x48 - drawable-mdpi<br/> 120dpi 36x36 - drawable-ldpi<br/> </p> <p>main(false) generates images for android in respective folders, where dimensions are proportional to selected resolution / dpi.</p> <p> For example:<br/><br/> Resolution: 480dpi<br/> Width:150<br/> Height:80<br/> </p> <p> Becomes:<br/><br/> 480dpi 150x80 - drawable-xxhdpi<br/> 320dpi 100x53 - drawable-xhdpi<br/> 240dpi 75x40 - drawable-hdpi<br/> 160dpi 50x27 - drawable-mdpi<br/> 120dpi 37x20- drawable-ldpi<br/> </p> <p>See <a href="https://github.com/karpach/Save4Android">Save4Android github repository</a> for more details.</p>Sun, 17 Mar 2013 00:00:00 -06002013-03-17T00:00:00-06:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/t-sql-print-with-immediate-output.htmhttps://www.karpach.com/t-sql-print-with-immediate-output.htmT-SQLHow to log a message from t-sql script?<p>Recently I've been working on a long running script that will be executed directly in PROD enviroment. I needed an easy way to see a progress of t-sql script execution. </p> <p>I tried to use PRINT, but it doesn’t output anything until script execution is done.</p> <p>You can use some log table and insert progress status messages there, but there is an easier way:</p> <div class="sql"><pre> <span class="keyword">RAISERROR</span> (<span class="string">'Message'</span>, 10, 1) <span class="keyword">WITH</span> NOWAIT </pre></div> <br/> <p>RAISERROR with severity of 10 or less doesn't trigger try catch block and can be used for log purposes. Here is a little longer example:</p> <br/> <div class="sql"><pre> <span class="keyword">DECLARE</span> @i <span class="keyword">INT</span> = 0 <span class="keyword">WHILE</span> 1=1 <span class="keyword">BEGIN</span> <span class="keyword">IF</span> @i &gt;= 10 <span class="keyword">BREAK</span>; <span class="keyword">WAITFOR</span> DELAY <span class="string">'00:00:01'</span>; <span class="keyword">SET</span> @i = @i + 1 <span class="keyword">RAISERROR</span> (<span class="string">'%d sec running'</span>, 10, 1, @i) <span class="keyword">WITH</span> NOWAIT <span class="keyword">END</span> </pre></div> <br/> <p>Results:</p> <img src="http://www.karpach.com/images/uploaded/raiserror.gif" alt="Raiseerror execution example"/>Sat, 26 Jan 2013 00:00:00 -07002013-01-26T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/inserted-and-source-id-using-merge.htmhttps://www.karpach.com/inserted-and-source-id-using-merge.htmT-SQLHow to get both inserted source table id and target table id?<p> Lets say we have two tables source and target. We want to copy records from the source to the target table and save mappings between old and new ids in a third table. </p> <div class="sql"><pre> <span class="keyword">DECLARE</span> @source <span class="keyword">TABLE</span> ( id <span class="keyword">INT</span> <span class="keyword">IDENTITY</span>(5,1) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">PRIMARY</span> <span class="keyword">KEY</span>, <span class="keyword">name</span> <span class="keyword">VARCHAR</span>(<span class="sqlSystemFunction">max</span>) ) <span class="keyword">DECLARE</span> @target <span class="keyword">TABLE</span> ( id <span class="keyword">INT</span> <span class="keyword">IDENTITY</span>(1,1) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">PRIMARY</span> <span class="keyword">KEY</span>, <span class="keyword">name</span> <span class="keyword">VARCHAR</span>(<span class="sqlSystemFunction">max</span>) ) <span class="keyword">DECLARE</span> @inserted <span class="keyword">TABLE</span> ( id <span class="keyword">INT</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>, clone_id <span class="keyword">INT</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span> ) </pre></div> <br/> <p> Lets populate the source table: </p> <div class="sql"><pre> <span class="keyword">INSERT</span> <span class="keyword">INTO</span> @subscription (name) <span class="keyword">VALUES</span> (<span class="string">'Test 1'</span>), (<span class="string">'Test 2'</span>), (<span class="string">'Test 3'</span>) </pre></div> <br/> <p> Now we can populate the target table with the following code: </p> <div class="sql"><pre> <span class="keyword">INSERT</span> <span class="keyword">INTO</span> @target (<span class="keyword">name</span> ) <span class="keyword">SELECT</span> <span class="keyword">name</span> + <span class="string">' - Cloned'</span> <span class="keyword">FROM</span> @source </pre></div> <br/> <p> You can try to populate inserted table using the following code: </p> <div class="sql"><pre> <span class="keyword">INSERT</span> <span class="keyword">INTO</span> @target (<span class="keyword">name</span> ) <span class="keyword">OUTPUT</span> s.id, INSERTED.id <span class="keyword">INTO</span> @inserted <span class="keyword">SELECT</span> <span class="keyword">name</span> + <span class="string">' - Cloned'</span> <span class="keyword">FROM</span> @source s </pre></div> <br/> <p>But you will get a syntax error:</p> <p style="color:red">The multi-part identifier "s.id" could not be bound.</p> <p>This is due to a nature of the INSERT statement. The SELECT statement considered to be apart from the INSERT statement, so you can't reference its tables in the OUTPUT clause. However you can achieve desired outcome using <b>MERGE</b> sql statement, which is a part of SQL standard from 2003, but rarely used by anybody.</p> <div class="sql"><pre> MERGE @target t USING @source s <span class="keyword">ON</span> 0=1 <span class="keyword">WHEN</span> <span class="keyword">NOT</span> MATCHED <span class="keyword">THEN</span> <span class="keyword">INSERT</span> (<span class="keyword">name</span>) <span class="keyword">VALUES</span> (s.name + <span class="string">' - Cloned'</span>) <span class="keyword">OUTPUT</span> s.id, INSERTED.id <span class="keyword">INTO</span> @inserted; <span class="keyword">SELECT</span> * <span class="keyword">FROM</span> @source <span class="keyword">SELECT</span> * <span class="keyword">FROM</span> @target <span class="keyword">SELECT</span> * <span class="keyword">FROM</span> @inserted </pre></div> <br/> <p>Results:</p> <table class="tableDefinition"> <tr><th>id</th><th>name</th></tr> <tr><td>5</td><td>Test 1</td></tr> <tr><td>6</td><td>Test 2</td></tr> <tr><td>7</td><td>Test 3</td></tr> </table> <br/> <table class="tableDefinition"> <tr><th>id</th><th>name</th></tr> <tr><td>1</td><td>Test 1 - Cloned</td></tr> <tr><td>2</td><td>Test 2 - Cloned</td></tr> <tr><td>3</td><td>Test 3 - Cloned</td></tr> </table> <br/> <table class="tableDefinition"> <tr><th>id</th><th>clone_id</th></tr> <tr><td>5</td><td>1</td></tr> <tr><td>6</td><td>2</td></tr> <tr><td>7</td><td>3</td></tr> </table> Sat, 05 Jan 2013 00:00:00 -07002013-01-05T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/firebug-vs-google-chrome-developer-tools.htmhttps://www.karpach.com/firebug-vs-google-chrome-developer-tools.htmASP.NETFirebug vs Google Chrome Developer Tools<p>Recently I&rsquo;ve been trying to use more and more Chrome Developer Tools instead of Firebug. Here are couple of things that I thought are show stoppers, but appeared to be a lack of knowledge on my side.</p> <p>There are no AJAX requests in console window. There is a setting for this:</p> <p><img src="http://www.karpach.com/images/uploaded/console-ajax-google-chrome-setting.jpg" alt="Console AJAX Google Chrome Setting" /> <img src="http://www.karpach.com/images/uploaded/console-ajax-google-chrome.jpg" alt="Console AJAX Google Chrome" /></p> <p>Secondly I can&rsquo;t search all javascript files:</p> <p><img src="http://www.karpach.com/images/uploaded/google-chrome-scripts-search.jpg" alt="Google Chrome scripts search" /></p> <p>Actually you can search all javascript files and more. Just press Ctrl+Shift+F:</p> <p><img src="http://www.karpach.com/images/uploaded/google-chrome-scripts-search2.jpg" alt="Google Chrome scripts search" /></p> <p>Do you miss any FireBug functionality in Chrome Developer Tools?</p>Mon, 06 Aug 2012 00:00:00 -06002012-08-06T00:00:00-06:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/linq-deferred-loading-yield-return.htmhttps://www.karpach.com/linq-deferred-loading-yield-return.htmC# .NETLINQ deferred loading using C# yield-return<p>Yield keyword exists in C# language since version 2 along with Visual Studio 2005, however it is rarely used by developers. Recently I read <a href="http://visualstudiomagazine.com/articles/2012/02/01/demystifying-the-c-yield-return-mechanism.aspx" target="_blank">Demystifying the C# Yield-Return Mechanism</a> article about yield-return keyword usage. This article gave three most common usage scenarios, but author (James McCaffrey) didn't mention LINQ. I think LINQ is where yield-return becomes most useful since it provides mechanism for deferred loading. Here is a quick example.</p> <p>Lets say we have bunch of unit tests to run. It takes some time to run a single test, lets say 1 sec. For simplicity lets assume that even tests pass and odd fail:</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: #2b91af; } .cb3 { color: #a31515; } .cb4 { color: #3cb371; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">public</span> <span class="cb1">class</span> <span class="cb2">Test</span></pre> <pre class="cl"> {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">public</span> <span class="cb1">int</span> Id { <span class="cb1">get</span>; <span class="cb1">set</span>; }&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; </pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">public</span> <span class="cb1">bool</span> Assert()</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; System.Threading.<span class="cb2">Thread</span>.Sleep(1000);</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb2">Console</span>.WriteLine(<span class="cb1">string</span>.Format(<span class="cb3">&quot;Test </span><span class="cb4">{0}</span><span class="cb3"> was processed.&quot;</span>, Id));&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; </pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">return</span> Id % 2 == 0;</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; }&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; </pre> <pre class="cl"> }</pre> </div> <p>&nbsp;</p> <p>Lets write couple LINQ like extension methods for Test collections. Process1 doesn't use yield return. Process2 uses yield return and PrintPass output &quot;Test # is passed&quot; message.</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: #2b91af; } .cb3 { color: #a31515; } .cb4 { color: #3cb371; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">public</span> <span class="cb1">static</span> <span class="cb1">class</span> <span class="cb2">Extensions</span></pre> <pre class="cl"> {&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; </pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">public</span> <span class="cb1">static</span> <span class="cb2">IEnumerable</span>&lt;<span class="cb2">Test</span>&gt; Process1(<span class="cb1">this</span> <span class="cb2">IEnumerable</span>&lt;<span class="cb2">Test</span>&gt; tests)</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">var</span> result = <span class="cb1">new</span> <span class="cb2">List</span>&lt;<span class="cb2">Test</span>&gt;();</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">foreach</span> (<span class="cb1">var</span> test <span class="cb1">in</span> tests)</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">if</span> (test.Assert())</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; result.Add(test);</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; }</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; }</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">return</span> result;</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; }</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">public</span> <span class="cb1">static</span> <span class="cb2">IEnumerable</span>&lt;<span class="cb2">Test</span>&gt; Process2(<span class="cb1">this</span> <span class="cb2">IEnumerable</span>&lt;<span class="cb2">Test</span>&gt; tests)</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">foreach</span> (<span class="cb1">var</span> test <span class="cb1">in</span> tests)</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">if</span> (test.Assert())</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">yield</span> <span class="cb1">return</span> test;</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; }</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; }</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; }</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">public</span> <span class="cb1">static</span> <span class="cb1">void</span> PrintPass(<span class="cb1">this</span> <span class="cb2">IEnumerable</span>&lt;<span class="cb2">Test</span>&gt; tests)</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">foreach</span> (<span class="cb1">var</span> t <span class="cb1">in</span> tests)</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb2">Console</span>.WriteLine(<span class="cb1">string</span>.Format(<span class="cb3">&quot;Test </span><span class="cb4">{0}</span><span class="cb3"> is passed.&quot;</span>, t.Id));&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; </pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; }</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; } </pre> <pre class="cl"> }</pre> </div> <p>&nbsp;</p> <p>Lets fill up test collection and compare output for Process1 and Process2 method:</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: #2b91af; } .cb3 { color: #a31515; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">class</span> <span class="cb2">Program</span></pre> <pre class="cl"> {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">static</span> <span class="cb1">void</span> Main()</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">var</span> tests = <span class="cb1">new</span> <span class="cb2">List</span>&lt;<span class="cb2">Test</span>&gt;(<span class="cb1">new</span> []</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">new</span> <span class="cb2">Test</span> {Id = 1},&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; </pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">new</span> <span class="cb2">Test</span> {Id = 2},</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">new</span> <span class="cb2">Test</span> {Id = 3},</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">new</span> <span class="cb2">Test</span> {Id = 4},</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb1">new</span> <span class="cb2">Test</span> {Id = 5},</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; });</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb2">Console</span>.WriteLine(<span class="cb3">&quot;**** No yield return *******&quot;</span>);</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; tests.Process1().PrintPass();&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; </pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb2">Console</span>.WriteLine(<span class="cb3">&quot;**** Using yield return *******&quot;</span>);</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; tests.Process2().PrintPass();&nbsp; </pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <span class="cb2">Console</span>.ReadKey();</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; }</pre> <pre class="cl"> }</pre> </div> <p>&nbsp;</p> <p>Output:</p> <p>**** No yield return *******<br /> Test 1 was processed.<br /> Test 2 was processed.<br /> Test 3 was processed.<br /> Test 4 was processed.<br /> Test 5 was processed.<br /> Test 2 is passed.<br /> Test 4 is passed.<br /> <br /> **** Using yield return *******<br /> Test 1 was processed.<br /> Test 2 was processed.<br /> Test 2 is passed.<br /> Test 3 was processed.<br /> Test 4 was processed.<br /> Test 4 is passed.<br /> Test 5 was processed.</p> <p>As you can see, in first case whole collection was built before PrintPass method output anything. In second case you can see PrintPass prints &quot;Test 2 is passed.&quot; right after Assert method for this test have done its processing.</p>Sat, 31 Mar 2012 00:00:00 -06002012-03-31T00:00:00-06:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/pivot-tsql.htmhttps://www.karpach.com/pivot-tsql.htmT-SQLHow to use PIVOT in T-SQL queries?<p>Web has a lot of PIVOT examples, however all of them are based on fact tables. Regular relational database rarely has facts tables. Lets see how you can use PIVOT statement with regular database. First of all lets create sample Grocery database:</p> <p><img alt="Grocery Database Diagram" src="http://www.karpach.com/images/uploaded/Pivot-Store-Diagram.png" /></p> <p>Here is a script that can create this database:</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">CREATE DATABASE </span>Grocery</pre> <pre class="cl"> GO</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"><span class="cb1">USE </span>Grocery</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"><span class="cb1">CREATE TABLE </span>Products</pre> <pre class="cl"> (</pre> <pre class="cl"> &nbsp; ProductId <span class="cb1">INT IDENTITY</span>(1,1) <span class="cb1">PRIMARY KEY</span>,</pre> <pre class="cl"> &nbsp; ProductName <span class="cb1">VARCHAR</span>(50),</pre> <pre class="cl"> &nbsp; Price <span class="cb1">MONEY</span></pre> <pre class="cl"> )</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"><span class="cb1">CREATE TABLE </span>Stores</pre> <pre class="cl"> (</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; StoreId <span class="cb1">INT IDENTITY</span>(1,1) <span class="cb1">PRIMARY KEY</span>,</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; StoreName <span class="cb1">VARCHAR</span>(50)</pre> <pre class="cl"> )</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"><span class="cb1">CREATE TABLE </span>Orders</pre> <pre class="cl"> (</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; OrderId <span class="cb1">INT IDENTITY</span>(1,1) <span class="cb1">PRIMARY KEY</span>,</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; StoreId <span class="cb1">INT</span>,</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; OrderDate <span class="cb1">DATE</span>,</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">CONSTRAINT </span>FK_Orders_Store <span class="cb1">FOREIGN KEY </span>(StoreId) <span class="cb1">REFERENCES </span>Stores(StoreId)</pre> <pre class="cl"> )</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"><span class="cb1">CREATE TABLE </span>OrderProducts</pre> <pre class="cl"> (</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; OrderId <span class="cb1">INT</span>,</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; ProductId <span class="cb1">INT</span>,</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; Qty <span class="cb1">INT</span>,</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">CONSTRAINT </span>PK_OrderId_ProductId <span class="cb1">PRIMARY KEY </span>(OrderId,ProductId),</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">CONSTRAINT </span>FK_OrderProducts_Products <span class="cb1">FOREIGN KEY </span>(ProductId) <span class="cb1">REFERENCES </span>Products(ProductId),</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">CONSTRAINT </span>FK_OrderProducts_Orders <span class="cb1">FOREIGN KEY </span>(OrderId) <span class="cb1">REFERENCES </span>Orders(OrderId)</pre> <pre class="cl"> )</pre> </div> <p>&nbsp;</p> <p>Lets populate those tables:</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: #a31515; } </style></p> <div class="cf"> <pre class="cl"> &nbsp;</pre> <pre class="cl"><span class="cb1">INSERT INTO </span>Products</pre> <pre class="cl"> (ProductName, Price)</pre> <pre class="cl"><span class="cb1">VALUES </span> </pre> <pre class="cl"> (<span class="cb2">'Milk'</span>, 2.99),</pre> <pre class="cl"> (<span class="cb2">'Bread'</span>,1.99),</pre> <pre class="cl"> (<span class="cb2">'Tomato'</span>,0.99),</pre> <pre class="cl"> (<span class="cb2">'Grape'</span>,1.99)</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"><span class="cb1">INSERT INTO </span>Stores</pre> <pre class="cl"> (StoreName )</pre> <pre class="cl"><span class="cb1">VALUES </span> </pre> <pre class="cl"> (<span class="cb2">'Jewel'</span>),</pre> <pre class="cl"> (<span class="cb2">'Dominicks'</span>),</pre> <pre class="cl"> (<span class="cb2">'Walmart'</span>)</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"><span class="cb1">INSERT INTO </span>Orders</pre> <pre class="cl"> (StoreId, OrderDate )</pre> <pre class="cl"><span class="cb1">VALUES </span> </pre> <pre class="cl"> (1,<span class="cb2">'1/11/2011'</span>),</pre> <pre class="cl"> (1,<span class="cb2">'1/14/2011'</span>),</pre> <pre class="cl"> (2,<span class="cb2">'2/05/2011'</span>),</pre> <pre class="cl"> (3,<span class="cb2">'3/17/2011'</span>),</pre> <pre class="cl"> (3,<span class="cb2">'3/29/2011'</span>),</pre> <pre class="cl"> (3,<span class="cb2">'4/02/2011'</span>)</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"><span class="cb1">INSERT INTO </span>OrderProducts</pre> <pre class="cl"> ( OrderId, ProductId, Qty )</pre> <pre class="cl"><span class="cb1">VALUES </span></pre> <pre class="cl"> (1,2,2),</pre> <pre class="cl"> (1,1,1),</pre> <pre class="cl"> (2,3,1),</pre> <pre class="cl"> (3,4,3),</pre> <pre class="cl"> (3,1,2),</pre> <pre class="cl"> (3,3,1),</pre> <pre class="cl"> (4,3,2),</pre> <pre class="cl"> (4,4,2),</pre> <pre class="cl"> (5,1,1),</pre> <pre class="cl"> (5,3,1),</pre> <pre class="cl"> (6,1,1),</pre> <pre class="cl"> (6,2,3),</pre> <pre class="cl"> (6,4,1)</pre> </div> <p>&nbsp;</p> <p>Products:</p> <table cellspacing="0" cellpadding="0" class="tableDefinition"> <tbody> <tr> <th>ProductId</th> <th>ProductName</th> <th>Price</th> </tr> <tr> <td>1</td> <td>Milk</td> <td>2.99</td> </tr> <tr> <td>2</td> <td>Bread</td> <td>1.99</td> </tr> <tr> <td>3</td> <td>Tomato</td> <td>0.99</td> </tr> <tr> <td>4</td> <td>Grape</td> <td>1.99</td> </tr> </tbody> </table> <p>&nbsp;</p> <p>Stores:</p> <table cellspacing="0" cellpadding="0" class="tableDefinition"> <tbody> <tr> <th>StoreId</th> <th>StoreName</th> </tr> <tr> <td>1</td> <td>Jewel</td> </tr> <tr> <td>2</td> <td>Dominicks</td> </tr> <tr> <td>3</td> <td>Walmart</td> </tr> </tbody> </table> <p>&nbsp;</p> <p>Orders:</p> <table cellspacing="0" cellpadding="0" class="tableDefinition"> <tbody> <tr> <th>OrderId</th> <th>StoreId</th> <th>OrderDate</th> </tr> <tr> <td>1</td> <td>1</td> <td>2011-01-11</td> </tr> <tr> <td>2</td> <td>1</td> <td>2011-01-14</td> </tr> <tr> <td>3</td> <td>2</td> <td>2011-02-05</td> </tr> <tr> <td>4</td> <td>3</td> <td>2011-03-17</td> </tr> <tr> <td>5</td> <td>3</td> <td>2011-03-29</td> </tr> <tr> <td>6</td> <td>3</td> <td>2011-04-02</td> </tr> </tbody> </table> <p>&nbsp;</p> <p>OrderProducts:</p> <table cellspacing="0" cellpadding="0" class="tableDefinition"> <tbody> <tr> <th>OrderId</th> <th>ProductId</th> <th>Qty</th> </tr> <tr> <td>1</td> <td>1</td> <td>1</td> </tr> <tr> <td>1</td> <td>2</td> <td>2</td> </tr> <tr> <td>2</td> <td>3</td> <td>1</td> </tr> <tr> <td>3</td> <td>1</td> <td>2</td> </tr> <tr> <td>3</td> <td>3</td> <td>1</td> </tr> <tr> <td>3</td> <td>4</td> <td>3</td> </tr> <tr> <td>4</td> <td>3</td> <td>2</td> </tr> <tr> <td>4</td> <td>4</td> <td>2</td> </tr> <tr> <td>5</td> <td>1</td> <td>1</td> </tr> <tr> <td>5</td> <td>3</td> <td>1</td> </tr> <tr> <td>6</td> <td>1</td> <td>1</td> </tr> <tr> <td>6</td> <td>2</td> <td>3</td> </tr> <tr> <td>6</td> <td>4</td> <td>1</td> </tr> </tbody> </table> <p>&nbsp;</p> <p>Now our sample relation database is ready for some pivoting. Lets say we want to know what products are sold by what store. Lets pull needed data using regular query:</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">SELECT </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; s.StoreName,p.ProductName, op.Qty * p.Price <span class="cb1">AS </span>Totals </pre> <pre class="cl"><span class="cb1">FROM </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; Products p <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; OrderProducts op <span class="cb1">ON </span>p.ProductId = op.ProductId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; Orders o <span class="cb1">ON </span>op.OrderId = o.OrderId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; Stores s <span class="cb1">ON </span>o.StoreId = s.StoreId</pre> </div> <p>&nbsp;</p> <p>Results:</p> <table cellspacing="0" cellpadding="0" class="tableDefinition"> <tbody> <tr> <th>StoreName</th> <th>ProductName</th> <th>Totals</th> </tr> <tr> <td>Jewel</td> <td>Milk</td> <td>2.99</td> </tr> <tr> <td>Jewel</td> <td>Bread</td> <td>3.98</td> </tr> <tr> <td>Jewel</td> <td>Tomato</td> <td>0.99</td> </tr> <tr> <td>Dominicks</td> <td>Milk</td> <td>5.98</td> </tr> <tr> <td>Dominicks</td> <td>Tomato</td> <td>0.99</td> </tr> <tr> <td>Dominicks</td> <td>Grape</td> <td>5.97</td> </tr> <tr> <td>Walmart</td> <td>Tomato</td> <td>1.98</td> </tr> <tr> <td>Walmart</td> <td>Grape</td> <td>3.98</td> </tr> <tr> <td>Walmart</td> <td>Milk</td> <td>2.99</td> </tr> <tr> <td>Walmart</td> <td>Tomato</td> <td>0.99</td> </tr> <tr> <td>Walmart</td> <td>Milk</td> <td>2.99</td> </tr> <tr> <td>Walmart</td> <td>Bread</td> <td>5.97</td> </tr> <tr> <td>Walmart</td> <td>Grape</td> <td>1.99</td> </tr> </tbody> </table> <p>&nbsp;</p> <p>Those result rows look like a fact table. Lets apply PIVOT to them:</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">SELECT </span>* <span class="cb1">FROM</span></pre> <pre class="cl"> (</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">SELECT </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; s.StoreName,p.ProductName, op.Qty * p.Price <span class="cb1">AS </span>Totals </pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">FROM </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Products p <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; OrderProducts op <span class="cb1">ON </span>p.ProductId = op.ProductId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Orders o <span class="cb1">ON </span>op.OrderId = o.OrderId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Stores s <span class="cb1">ON </span>o.StoreId = s.StoreId</pre> <pre class="cl"> ) <span class="cb1">AS </span>Source&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; </pre> <pre class="cl"><span class="cb1">PIVOT</span></pre> <pre class="cl"> (</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">SUM</span>(Totals)</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">FOR </span>ProductName <span class="cb1">IN </span>([Bread],[Milk],[Tomato],[Grape])&nbsp;&nbsp;&nbsp; </pre> <pre class="cl"> ) <span class="cb1">AS </span>p</pre> </div> <p>&nbsp;</p> <p>PIVOT results:</p> <table cellspacing="0" cellpadding="0" class="tableDefinition"> <tbody> <tr> <th>StoreName</th> <th>Bread</th> <th>Milk</th> <th>Tomato</th> <th>Grape</th> </tr> <tr> <td>Dominicks</td> <td>NULL</td> <td>5.98</td> <td>0.99</td> <td>5.97</td> </tr> <tr> <td>Jewel</td> <td>3.98</td> <td>2.99</td> <td>0.99</td> <td>NULL</td> </tr> <tr> <td>Walmart</td> <td>5.97</td> <td>5.98</td> <td>2.97</td> <td>5.97</td> </tr> </tbody> </table> <p>&nbsp;</p> <p>Lets do another one: products sales by months.</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">SELECT </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; p.ProductName, <span class="cb1">DATENAME</span>(m,o.OrderDate) <span class="cb1">AS </span>[Month], op.Qty * p.Price <span class="cb1">AS </span>Totals </pre> <pre class="cl"><span class="cb1">FROM </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; Products p <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; OrderProducts op <span class="cb1">ON </span>p.ProductId = op.ProductId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; Orders o <span class="cb1">ON </span>op.OrderId = o.OrderId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; Stores s <span class="cb1">ON </span>o.StoreId = s.StoreId</pre> </div> <p>&nbsp;</p> <p>Results:</p> <table cellspacing="0" cellpadding="0" class="tableDefinition"> <tbody> <tr> <th>ProductName</th> <th>Month</th> <th>Totals</th> </tr> <tr> <td>Milk</td> <td>January</td> <td>2.99</td> </tr> <tr> <td>Bread</td> <td>January</td> <td>3.98</td> </tr> <tr> <td>Tomato</td> <td>January</td> <td>0.99</td> </tr> <tr> <td>Milk</td> <td>February</td> <td>5.98</td> </tr> <tr> <td>Tomato</td> <td>February</td> <td>0.99</td> </tr> <tr> <td>Grape</td> <td>February</td> <td>5.97</td> </tr> <tr> <td>Tomato</td> <td>March</td> <td>1.98</td> </tr> <tr> <td>Grape</td> <td>March</td> <td>3.98</td> </tr> <tr> <td>Milk</td> <td>March</td> <td>2.99</td> </tr> <tr> <td>Tomato</td> <td>March</td> <td>0.99</td> </tr> <tr> <td>Milk</td> <td>April</td> <td>2.99</td> </tr> <tr> <td>Bread</td> <td>April</td> <td>5.97</td> </tr> <tr> <td>Grape</td> <td>April</td> <td>1.99</td> </tr> </tbody> </table> <p>&nbsp;</p> <p>PIVOT version:</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">SELECT </span>* <span class="cb1">FROM</span></pre> <pre class="cl"> (</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">SELECT </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; p.ProductName, <span class="cb1">DATENAME</span>(m,o.OrderDate) <span class="cb1">AS </span>[Month], op.Qty * p.Price <span class="cb1">AS </span>Totals </pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">FROM </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Products p <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; OrderProducts op <span class="cb1">ON </span>p.ProductId = op.ProductId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Orders o <span class="cb1">ON </span>op.OrderId = o.OrderId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Stores s <span class="cb1">ON </span>o.StoreId = s.StoreId</pre> <pre class="cl"> ) <span class="cb1">AS </span>Source&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; </pre> <pre class="cl"><span class="cb1">PIVOT</span></pre> <pre class="cl"> (</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">SUM</span>(Totals)</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">FOR </span>[Month] <span class="cb1">IN </span>([January],[February],[March],[April])&nbsp;&nbsp;&nbsp; </pre> <pre class="cl"> ) <span class="cb1">AS </span>p</pre> </div> <p>&nbsp;</p> <p>PIVOT results:</p> <table cellspacing="0" cellpadding="0" class="tableDefinition"> <tbody> <tr> <th>ProductName</th> <th>January</th> <th>February</th> <th>March</th> <th>April</th> </tr> <tr> <td>Bread</td> <td>3.98</td> <td>NULL</td> <td>NULL</td> <td>5.97</td> </tr> <tr> <td>Grape</td> <td>NULL</td> <td>5.97</td> <td>3.98</td> <td>1.99</td> </tr> <tr> <td>Milk</td> <td>2.99</td> <td>5.98</td> <td>2.99</td> <td>2.99</td> </tr> <tr> <td>Tomato</td> <td>0.99</td> <td>0.99</td> <td>2.97</td> <td>NULL</td> </tr> </tbody> </table> <p>&nbsp;</p> <p>And last one lets do stores sales by months:</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">SELECT </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; s.StoreName,<span class="cb1">DATENAME</span>(m,o.OrderDate) <span class="cb1">AS </span>[Month], op.Qty * p.Price <span class="cb1">AS </span>Totals </pre> <pre class="cl"><span class="cb1">FROM </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; Products p <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; OrderProducts op <span class="cb1">ON </span>p.ProductId = op.ProductId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; Orders o <span class="cb1">ON </span>op.OrderId = o.OrderId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; Stores s <span class="cb1">ON </span>o.StoreId = s.StoreId</pre> </div> <p>&nbsp;</p> <p>Results:</p> <table cellspacing="0" cellpadding="0" class="tableDefinition"> <tbody> <tr> <th>StoreName</th> <th>Month</th> <th>Totals</th> </tr> <tr> <td>Jewel</td> <td>January</td> <td>2.99</td> </tr> <tr> <td>Jewel</td> <td>January</td> <td>3.98</td> </tr> <tr> <td>Jewel</td> <td>January</td> <td>0.99</td> </tr> <tr> <td>Dominicks</td> <td>February</td> <td>5.98</td> </tr> <tr> <td>Dominicks</td> <td>February</td> <td>0.99</td> </tr> <tr> <td>Dominicks</td> <td>February</td> <td>5.97</td> </tr> <tr> <td>Walmart</td> <td>March</td> <td>1.98</td> </tr> <tr> <td>Walmart</td> <td>March</td> <td>3.98</td> </tr> <tr> <td>Walmart</td> <td>March</td> <td>2.99</td> </tr> <tr> <td>Walmart</td> <td>March</td> <td>0.99</td> </tr> <tr> <td>Walmart</td> <td>April</td> <td>2.99</td> </tr> <tr> <td>Walmart</td> <td>April</td> <td>5.97</td> </tr> <tr> <td>Walmart</td> <td>April</td> <td>1.99</td> </tr> </tbody> </table> <p>&nbsp;</p> <p>PIVOT version:</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">SELECT </span>* <span class="cb1">FROM</span></pre> <pre class="cl"> (</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">SELECT </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; s.StoreName,<span class="cb1">DATENAME</span>(m,o.OrderDate) <span class="cb1">AS </span>[Month], op.Qty * p.Price <span class="cb1">AS </span>Totals </pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">FROM </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Products p <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; OrderProducts op <span class="cb1">ON </span>p.ProductId = op.ProductId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Orders o <span class="cb1">ON </span>op.OrderId = o.OrderId <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Stores s <span class="cb1">ON </span>o.StoreId = s.StoreId</pre> <pre class="cl"> ) <span class="cb1">AS </span>Source&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; </pre> <pre class="cl"><span class="cb1">PIVOT</span></pre> <pre class="cl"> (</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">SUM</span>(Totals)</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp; <span class="cb1">FOR </span>[Month] <span class="cb1">IN </span>([January],[February],[March],[April])&nbsp;&nbsp;&nbsp; </pre> <pre class="cl"> ) <span class="cb1">AS </span>p</pre> </div> <p>&nbsp;</p> <p>PIVOT results:</p> <table cellspacing="0" cellpadding="0" class="tableDefinition"> <tbody> <tr> <th>StoreName</th> <th>January</th> <th>February</th> <th>March</th> <th>April</th> </tr> <tr> <td>Dominicks</td> <td>NULL</td> <td>12.94</td> <td>NULL</td> <td>NULL</td> </tr> <tr> <td>Jewel</td> <td>7.96</td> <td>NULL</td> <td>NULL</td> <td>NULL</td> </tr> <tr> <td>Walmart</td> <td>NULL</td> <td>NULL</td> <td>9.94</td> <td>10.95</td> </tr> </tbody> </table>Tue, 27 Mar 2012 00:00:00 -06002012-03-27T00:00:00-06:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/list-sql-server-table-dependencies.htmhttps://www.karpach.com/list-sql-server-table-dependencies.htmT-SQLHow to list sql server table dependencies using T-SQL?<p>You can always use SQL Server Management Studio, just right click on a table in object explorer and select View Dependencies.</p> <p><img alt="View Dependencies" src="http://www.karpach.com/images/uploaded/Object-Dependencies.png" /></p> <p>However this way doesn't give you ability to copy dependencies to clipboard or any other way to export dependencies list. Luckily you can find the same data using <strong>SysObjects</strong> and <strong>SysDepends</strong> tables. Here is a quick T-SQL snippet that you might want to use for this purpose.</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: #a31515; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">SELECT DISTINCT </span>dobj.name,dobj.type</pre> <pre class="cl"><span class="cb1">FROM </span>SysObjects obj <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> SysDepends d <span class="cb1">ON </span>obj.id = d.depid <span class="cb1">INNER JOIN </span></pre> <pre class="cl"> SysObjects dobj <span class="cb1">ON </span>d.id = dobj.id</pre> <pre class="cl"><span class="cb1">WHERE </span>obj.name = <span class="cb2">'WorkOrder' </span><span class="cb1">ORDER BY </span>dobj.type,dobj.name</pre> </div>Sun, 19 Feb 2012 00:00:00 -07002012-02-19T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/how-to-measure-performance-stored-procedure.htmhttps://www.karpach.com/how-to-measure-performance-stored-procedure.htmT-SQLHow to measure stored procedure execution time?<p>You can use <a href="http://msdn.microsoft.com/en-us/library/ms190287.aspx" target='_blank'>SET STATISTICS TIME ON</a>, but it would return you timing for all single queries inside of your stored procedure. If you have bunch of queries inside then it might be not really convenient. Here is a little code snippet alternative to STATISTICS TIME:</p> <style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: green; } </style> <div class="cf"> <pre class="cl"><span class="cb1">Declare </span>@d <span class="cb1">datetime</span></pre> <pre class="cl"><span class="cb1">Set </span>@d = <span class="cb1">CURRENT_TIMESTAMP</span></pre> <pre class="cl">&nbsp;</pre> <pre class="cl"><span class="cb2">-- Your stored procedure call goes here</span></pre> <pre class="cl">&nbsp;</pre> <pre class="cl"><span class="cb1">SELECT DATEDIFF</span>(ms,@d,<span class="cb1">CURRENT_TIMESTAMP</span>)</pre> <pre class="cl">&nbsp;</pre> </div> <p>Use <strong>DBCC DROPCLEANBUFFERS</strong> to clear sql server cache for cache independent time measurement.</p> Wed, 15 Feb 2012 00:00:00 -07002012-02-15T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttps://www.karpach.com/javascript-properties-get-set.htmhttps://www.karpach.com/javascript-properties-get-set.htmJavascriptGet and Set operators in Javascript<p>I am C# developer, hence I use C# properties a lot. However I just recently discovered that Javascript has properties as well. Get and Set operators supported by Firefox 2.0+, Safari 3.0+, Chrome, Opera 9.5+. However IE still doesn't support properties. May be Microsoft would introduce support in IE10, but so far no luck. Here is a little example of Get and Set declaration:</p> <p><style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: maroon; } </style></p> <div class="cf"> <pre class="cl"><span class="cb1">var</span> obj = {</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"> &nbsp;&nbsp; get CustomProperty() {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp;&nbsp; <span class="cb1">return</span> 0;</pre> <pre class="cl"> &nbsp;&nbsp; },</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"> &nbsp;&nbsp; set CustomProperty(s) {</pre> <pre class="cl"> &nbsp;&nbsp;&nbsp;&nbsp; alert (<span class="cb2">&quot;It is readonly.&quot;</span>);</pre> <pre class="cl"> &nbsp;&nbsp; }</pre> <pre class="cl"> };</pre> </div> <p>&nbsp;</p> <p>Properties can be useful and scary at the same time. A simple variable assignment is not predictable anymore. For example with a little bit of work you can convert your AJAX calls to something like this:</p> <div class="cf"> <pre class="cl"> ajax.url = <span class="cb1">&quot;script.php&quot;</span>;</pre> <pre class="cl"> &nbsp;</pre> <pre class="cl"> alert(ajax.result);</pre> </div> <p>&nbsp;</p>Sat, 04 Feb 2012 00:00:00 -07002012-02-04T00:00:00-07:00Copyright (c) 2007 Viktar Karpach