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 Bloghttp://www.karpach.com/twitter-application-only-authentication.htmhttp://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">&quot;https://api.twitter.com/oauth2/token &quot;</span>); <span class="keyword">var</span> customerInfo = <span class="className">Convert</span>.ToBase64String(<span class="keyword">new</span> UTF8Encoding() .GetBytes(OAuthConsumerKey + <span class="string">&quot;:&quot;</span> + OAuthConsumerSecret)); request.Headers.Add(<span class="string">&quot;Authorization&quot;</span>, <span class="string">&quot;Basic &quot;</span> + customerInfo); request.Content = <span class="keyword">new</span> StringContent(<span class="string">&quot;grant_type=client_credentials&quot;</span>, Encoding.UTF8, <span class="string">&quot;application/x-www-form-urlencoded&quot;</span>); 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">&quot;access_token&quot;</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">&quot;https://api.twitter.com/1.1/statuses/user_timeline.json?count={0}&amp;screen_name={1}&amp;trim_user=1&amp;exclude_replies=1&quot;</span>, count, userName)); requestUserTimeline.Headers.Add(<span class="string">&quot;Authorization&quot;</span>, <span class="string">&quot;Bearer &quot;</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">&quot;text&quot;</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 -07002014-08-18T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/subversion-to-git-migration.htmhttp://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 -08002014-01-27T00:00:00-08:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/synctoy-task-scheduler-no-console-window.htmhttp://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 -08002014-01-16T00:00:00-08:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/first-data-wcf-e4-integration.htmhttp://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 -08002014-01-07T00:00:00-08:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/save-for-android-photoshop-javascript.htmhttp://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 -07002013-03-17T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/t-sql-print-with-immediate-output.htmhttp://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 -08002013-01-26T00:00:00-08:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/inserted-and-source-id-using-merge.htmhttp://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 -08002013-01-05T00:00:00-08:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/firebug-vs-google-chrome-developer-tools.htmhttp://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 -07002012-08-06T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/linq-deferred-loading-yield-return.htmhttp://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 -07002012-03-31T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/pivot-tsql.htmhttp://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 -07002012-03-27T00:00:00-07:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/list-sql-server-table-dependencies.htmhttp://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 -08002012-02-19T00:00:00-08:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/how-to-measure-performance-stored-procedure.htmhttp://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 -08002012-02-15T00:00:00-08:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/javascript-properties-get-set.htmhttp://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 -08002012-02-04T00:00:00-08:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/yslow-and-asp-net-three-years-later.htmhttp://www.karpach.com/yslow-and-asp-net-three-years-later.htmASP.NETYSlow and ASP.NET: 100 points "A" grade year 2012<p>Almost three years ago I wrote an original <a href="http://www.karpach.com/yslow-and-asp-net-100-points-a-grade.htm">YSlow and ASP.NET: 100 points "A" grade is possible</a> article. Since then I changed couple hosting providers, made numerous updates and converted my blog project to .NET 4.0. Today I decided to revise my blog to see if it still has 100 yslow points with final "A" grade.</p> <p>First surprise, as of January 29, 2012, YSlow addon doesn't work in FireFox 9. However YSlow is now available for <a href="https://chrome.google.com/webstore/detail/ninejjcohidippngpapiilnmkgllmakh" target="_blank">Google Chrome</a>, <a href="https://addons.opera.com/addons/extensions/details/yslow/" target="_blank">Opera</a>, <a href="http://d.yimg.com/jc/safari/yslow.safariextz" target="_blank">Safari</a>. It has <a href="http://developer.yahoo.com/yslow/mobile/" target="_blank">bookmarket version</a> and even <a href="http://developer.yahoo.com/yslow/commandline" target="_blank">command line version</a>. I installed Google Chrome Yslow extension and began investigation. Right out of the box most of sections still had A grade. About a week ago I switched from Google App Engine "CDN" to true CDN <a href="http://aws.amazon.com/cloudfront/" target="_blank">Amazon CloudFront</a>. Some files were not in CDN, so I had to take care of those. In general I highly recommend Amazon CloudFront. It is easy to setup and cost just a few bucks a month based on your traffic usage. Amazon CloudFront mimics your local file settings, so you need to make sure that you have compression, expire header and Etags taken care of. I use IIS7 compression instead of a compression module recommended by me in my previous article. Out of the box IIS7 compression doesn't work with AWS CloudFront, so you need to modify C:\Windows\System32\inetsrv\config\applicationHost.config file on your server to include following: </p> <style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: #a31515; } .cb3 { color: red; } </style> <div class="cf"> <pre class="cl"><span class="cb1">&lt;</span><span class="cb2">serverRuntime</span><span class="cb1"> </span><span class="cb3">enabled</span><span class="cb1">=</span>&quot;<span class="cb1">true</span>&quot;<span class="cb1"> </span><span class="cb3">frequentHitThreshold</span><span class="cb1">=</span>&quot;<span class="cb1">1</span>&quot;<span class="cb1"> </span><span class="cb3">frequentHitTimePeriod</span><span class="cb1">=</span>&quot;<span class="cb1">00:00:20</span>&quot;<span class="cb1">/&gt;</span></pre> <pre class="cl"><span class="cb1">&lt;</span><span class="cb2">httpCompression</span><span class="cb1"> </span><span class="cb3">directory</span><span class="cb1">=</span>&quot;<span class="cb1">%SystemDrive%\inetpub\temp\IIS Temporary Compressed Files</span>&quot;<span class="cb1"> </span><span class="cb3">noCompressionForHttp10</span><span class="cb1">=</span>&quot;<span class="cb1">False</span>&quot;<span class="cb1"> </span><span class="cb3">noCompressionForProxies</span><span class="cb1">=</span>&quot;<span class="cb1">False</span>&quot;<span class="cb1">&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &lt;</span><span class="cb2">scheme</span><span class="cb1"> </span><span class="cb3">name</span><span class="cb1">=</span>&quot;<span class="cb1">gzip</span>&quot;<span class="cb1"> </span><span class="cb3">dll</span><span class="cb1">=</span>&quot;<span class="cb1">%Windir%\system32\inetsrv\gzip.dll</span>&quot;<span class="cb1"> /&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &lt;</span><span class="cb2">dynamicTypes</span><span class="cb1">&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &nbsp; &lt;</span><span class="cb2">add</span><span class="cb1"> </span><span class="cb3">mimeType</span><span class="cb1">=</span>&quot;<span class="cb1">text/*</span>&quot;<span class="cb1"> </span><span class="cb3">enabled</span><span class="cb1">=</span>&quot;<span class="cb1">true</span>&quot;<span class="cb1"> /&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &nbsp; &lt;</span><span class="cb2">add</span><span class="cb1"> </span><span class="cb3">mimeType</span><span class="cb1">=</span>&quot;<span class="cb1">message/*</span>&quot;<span class="cb1"> </span><span class="cb3">enabled</span><span class="cb1">=</span>&quot;<span class="cb1">true</span>&quot;<span class="cb1"> /&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &nbsp; &lt;</span><span class="cb2">add</span><span class="cb1"> </span><span class="cb3">mimeType</span><span class="cb1">=</span>&quot;<span class="cb1">application/x-javascript</span>&quot;<span class="cb1"> </span><span class="cb3">enabled</span><span class="cb1">=</span>&quot;<span class="cb1">true</span>&quot;<span class="cb1"> /&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &nbsp; &lt;</span><span class="cb2">add</span><span class="cb1"> </span><span class="cb3">mimeType</span><span class="cb1">=</span>&quot;<span class="cb1">*/*</span>&quot;<span class="cb1"> </span><span class="cb3">enabled</span><span class="cb1">=</span>&quot;<span class="cb1">false</span>&quot;<span class="cb1"> /&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &lt;/</span><span class="cb2">dynamicTypes</span><span class="cb1">&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &lt;</span><span class="cb2">staticTypes</span><span class="cb1">&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &nbsp; &lt;</span><span class="cb2">add</span><span class="cb1"> </span><span class="cb3">mimeType</span><span class="cb1">=</span>&quot;<span class="cb1">text/*</span>&quot;<span class="cb1"> </span><span class="cb3">enabled</span><span class="cb1">=</span>&quot;<span class="cb1">true</span>&quot;<span class="cb1"> /&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &nbsp; &lt;</span><span class="cb2">add</span><span class="cb1"> </span><span class="cb3">mimeType</span><span class="cb1">=</span>&quot;<span class="cb1">message/*</span>&quot;<span class="cb1"> </span><span class="cb3">enabled</span><span class="cb1">=</span>&quot;<span class="cb1">true</span>&quot;<span class="cb1"> /&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &nbsp; &lt;</span><span class="cb2">add</span><span class="cb1"> </span><span class="cb3">mimeType</span><span class="cb1">=</span>&quot;<span class="cb1">application/javascript</span>&quot;<span class="cb1"> </span><span class="cb3">enabled</span><span class="cb1">=</span>&quot;<span class="cb1">true</span>&quot;<span class="cb1"> /&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &nbsp; &lt;</span><span class="cb2">add</span><span class="cb1"> </span><span class="cb3">mimeType</span><span class="cb1">=</span>&quot;<span class="cb1">*/*</span>&quot;<span class="cb1"> </span><span class="cb3">enabled</span><span class="cb1">=</span>&quot;<span class="cb1">false</span>&quot;<span class="cb1"> /&gt;</span></pre> <pre class="cl"><span class="cb1">&nbsp; &lt;/</span><span class="cb2">staticTypes</span><span class="cb1">&gt;</span></pre> <pre class="cl"><span class="cb1">&lt;/</span><span class="cb2">httpCompression</span><span class="cb1">&gt;</span></pre> </div> <p>&nbsp;</p> <p>Also I modified my cache module to have ETags clean up:</p> <style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: #2b91af; } </style> <div class="cf"> <pre class="cl"><span class="cb1">public</span> <span class="cb1">void</span> Init(<span class="cb2">HttpApplication</span> context)</pre> <pre class="cl">{&nbsp;&nbsp; </pre> <pre class="cl">&nbsp;&nbsp;&nbsp; ...&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; </pre> <pre class="cl">&nbsp;&nbsp;&nbsp; context.PostReleaseRequestState += <span class="cb1">new</span> <span class="cb2">EventHandler</span>(application_PostReleaseRequestState);</pre> <pre class="cl">}</pre> <pre class="cl"> </pre> <pre class="cl"><span class="cb1">void</span> application_PostReleaseRequestState(<span class="cb1">object</span> sender, <span class="cb2">EventArgs</span> e)</pre> <pre class="cl">{</pre> <pre class="cl">&nbsp;&nbsp;&nbsp; <span class="cb2">HttpContext</span>.Current.Response.Headers.Remove(<span class="cb3">&quot;Server&quot;</span>);</pre> <pre class="cl">&nbsp;&nbsp;&nbsp; <span class="cb2">HttpContext</span>.Current.Response.Headers.Remove(<span class="cb3">&quot;X-AspNet-Version&quot;</span>);</pre> <pre class="cl">&nbsp;&nbsp;&nbsp; <span class="cb2">HttpContext</span>.Current.Response.Headers.Remove(<span class="cb3">&quot;ETag&quot;</span>);</pre> <pre class="cl">}</pre> </div> <p>&nbsp;</p> <p>YSlow added new section "Use Cookie-free Domains". My blog completely failed this one, since google analytics left cookies in top level karpach.com domain. I modified google analytics init script to have: _gaq.push(['_setDomainName', 'www.karpach.com']), so google cookies set for www.karpach.com instead of top level karpach.com. Then subdomain cdn.karpach.com, which points to Amazon CloudFront, became cookieless. Finally I am back to 100 YSlow points. Run Yslow on <a href="http://www.karpach.com/default.aspx?PageType=NoExternal">no externals version of karpach.com</a> to see it yourself.</p>Sun, 29 Jan 2012 00:00:00 -08002012-01-29T00:00:00-08:00Copyright (c) 2007 Viktar Karpachhttp://www.karpach.com/undefined-javascript-variable.htmhttp://www.karpach.com/undefined-javascript-variable.htmJavascriptHow to check if a javascript variable is undefined?<p> How to check if a javascript variable is undefined? This is actually a tricky question. There are three cases: </p> <ol> <li>A variable is declared using var keyword, but were never assigned any value.</li> <li>A variable is declared as function parameter, but when function were invoked parameter was not supplied.</li> <li>A variable is never declared or assigned a value and you are trying to access it.</li> </ol> <p> First two cases lead to <b>undefined value</b> of variable. Here is an example of first case: </p> <style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: #006400; } .cb3 { color: maroon; } </style> <div class="cf"> <pre class="cl"><span class="cb1">var</span> v; <span class="cb2">// Value not defined</span></pre> <pre class="cl"><span class="cb1">if</span> (v) {</pre> <pre class="cl">&nbsp;&nbsp;&nbsp; console.log(<span class="cb3">&quot;v=&quot;</span> + v);</pre> <pre class="cl">}</pre> <pre class="cl"><span class="cb1">else</span> {</pre> <pre class="cl">&nbsp;&nbsp;&nbsp; console.log(<span class="cb3">&quot;v has undefined value&quot;</span>);</pre> <pre class="cl">}</pre> <pre class="cl">v = <span class="cb3">&quot;some value&quot;</span>;</pre> <pre class="cl"><span class="cb1">if</span> (v) {</pre> <pre class="cl">&nbsp;&nbsp;&nbsp; console.log(<span class="cb3">&quot;v = &quot;</span> + v);</pre> <pre class="cl">}</pre> </div> <br/> <p> <br/> Output: <br/> v has undefined value<br/> v = some value<br/> </p> <p> The second case is similar and you can use if statement as in above code or: </p> <style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: #006400; } </style> <div class="cf"> <pre class="cl"><span class="cb1">function</span> Add(p1, p2) {</pre> <pre class="cl">&nbsp;&nbsp;&nbsp; p1 = p1 || 0; <span class="cb2">// set value to 0 if value is undefined</span></pre> <pre class="cl">&nbsp;&nbsp;&nbsp; p2 = p2 || 0; <span class="cb2">// set value to 0 if value is undefined</span></pre> <pre class="cl">&nbsp;&nbsp;&nbsp; <span class="cb1">return</span> p1 + p2; <span class="cb2">// would always work</span></pre> <pre class="cl">}</pre> <pre class="cl">&nbsp;</pre> <pre class="cl">console.log(Add(1, 2));</pre> <pre class="cl">console.log(Add(1));</pre> <pre class="cl">console.log(Add());</pre> </div> <p> <br/> Output: <br/> 3<br/> 1<br/> 0<br/> </p> <p> The third case when a variable is never declared. If you try to use such variable you get exception. If statement or default value assignment won't help. </p> <style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: maroon; } </style> <div class="cf"> <pre class="cl"><span class="cb1">if</span>(<span class="cb1">typeof</span> neverDeclared == <span class="cb2">&quot;undefined&quot;</span>) {</pre> <pre class="cl">&nbsp;&nbsp;&nbsp; console.log(<span class="cb2">&quot;There is something really wrong!&quot;</span>);</pre> <pre class="cl">}</pre> </div> <p> <br/> You would ask, what kind of sick people try to use a variable that was never declared or assigned a value? However I bet this is one of the most frequent javascript exceptions. Yes, simple variables are usually declared, but exception usually happens when you try to pass complex object with undeclared property. Javascript objects can be easily extended with additional properties and this comes at price of frequent undefined variable exceptions. Let me give you an example: </p> <style type="text/css"> .cf { color: black; } .cl { margin: 0px; } .cb1 { color: blue; } .cb2 { color: maroon; } .cb3 { color: #006400; } </style> <div class="cf"> <pre class="cl"><span class="cb1">var</span> obj = { FirstName: <span class="cb2">&quot;Viktar&quot;</span>, LastName: <span class="cb2">&quot;Karpach&quot;</span> };</pre> <pre class="cl">console.log(obj.FirstName);</pre> <pre class="cl">console.log(obj.LastName);</pre> <pre class="cl">console.log(obj.City); <span class="cb3">// Will give exception</span></pre> <pre class="cl">&nbsp;</pre> <pre class="cl"><span class="cb1">var</span> obj = { FirstName: <span class="cb2">&quot;Viktar&quot;</span>, LastName: <span class="cb2">&quot;Karpach&quot;</span> };</pre> <pre class="cl">obj.City = <span class="cb2">&quot;Chicago&quot;</span>;</pre> <pre class="cl">console.log(obj.City); <span class="cb3">// Works!</span></pre> <pre class="cl">&nbsp;</pre> <pre class="cl"><span class="cb1">if</span> (<span class="cb1">typeof</span> obj.State == <span class="cb2">&quot;undefined&quot;</span>) {</pre> <pre class="cl">&nbsp;&nbsp;&nbsp; console.log(<span class="cb2">&quot;State is undefined.&quot;</span>);</pre> <pre class="cl">}</pre> </div> <br/> <p>Undefined variable exception very often happens when you try to consume objects received from third party API or from AJAX requests, always be careful what you consume from partially unreliable resource.</p>Thu, 12 Jan 2012 00:00:00 -08002012-01-12T00:00:00-08:00Copyright (c) 2007 Viktar Karpach