tag:blogger.com,1999:blog-80126140734299287752024-03-06T04:05:54.027+01:00Morzel about Computer ProgrammingAbout Computer Programming, C#, Asp.Net, SQL and othersMiskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.comBlogger32125tag:blogger.com,1999:blog-8012614073429928775.post-38149516020336907812013-09-22T14:27:00.001+02:002013-09-22T16:19:52.561+02:00SSMS 2012–SP_HelpText workaround<p> </p> <p>There is a well-known issue in SQL Server Management Studio 2012 with <strong>sp_helptext</strong>: It <strong>puts extra lines in grid result set</strong>. This problem is known by the Microsoft <a href="http://connect.microsoft.com/SQLServer/feedback/details/668022/ssms-puts-extra-lines-in-the-result-set-in-grid-view-for-sp-helptext">since 2011</a> and it seems that they really do not want to fix it until the next version of SQL Server.</p> <p>I will show how to reproduce the problem and how to make a workaround for it.</p> <p><strong>I: Reproduce the problem:</strong></p> <p>1: Open SQL Server Management Studio 2012</p> <p>2: Create a sample stored procedure: <div class="csharpcode"> <div id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:dd8c5a6b-8bf6-4f85-bfe1-e727dce9eb25" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background: #000080; color: #fff; font-family: Verdana, Tahoma, Arial, sans-serif; font-weight: bold; padding: 2px 5px">DoSomething</div> <div style="background: #ddd; max-height: 300px; overflow: auto"> <ol start="1" style="background: #ffffff; margin: 0 0 0 2.5em; padding: 0 0 0 5px; white-space: nowrap"> <li><span style="color:#0000ff">create</span> <span style="color:#0000ff">procedure</span> <span style="color:#008080">dbo</span><span style="color:#808080">.</span><span style="color:#008080">DoSomething</span><span style="color:#0000ff"> </span><span style="color:#808080">(</span></li> <li style="background: #f3f3f3"> <span style="color:#008080">@string</span> <span style="color:#0000ff">nvarchar</span><span style="color:#808080">(</span>100<span style="color:#808080">)</span></li> <li><span style="color:#808080">)</span></li> <li style="background: #f3f3f3"><span style="color:#0000ff">as</span> <span style="color:#0000ff">begin</span></li> <li> <span style="color:#0000ff">set</span> <span style="color:#0000ff">nocount</span> <span style="color:#0000ff">on</span></li> <li style="background: #f3f3f3"> </li> <li> <span style="color:#0000ff">if</span> <span style="color:#008080">@string</span> <span style="color:#808080">is</span> <span style="color:#808080">null</span> <span style="color:#808080">or</span> <span style="color:#008080">@string</span> <span style="color:#808080">=</span> <span style="color:#ff0000">''</span> </li> <li style="background: #f3f3f3"> <span style="color:#0000ff">return</span> 1</li> <li> </li> <li style="background: #f3f3f3"> <span style="color:#0000ff">select</span> <span style="color:#008080">@string</span></li> <li><span style="color:#0000ff">end</span></li> <li style="background: #f3f3f3"><span style="color:#0000ff">go</span></li> </ol> </div> </div> </div></div> <style type="text/css">.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }<br /></style> <p>3: Run SP_HelpText with Results to Grid mode:</p> <p>- Make sure that Results to Grid option is set:</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLPa19KptjYkmAq-VTQe7hHHzG6akn8fI2l6qEWokLvpif_lHYr6BsyiEVBujpayTSZelnBS1peWSr1QGbWWbBtRScqkeKb4frDai9W0RDBIfgjv_1FChZfESss66QJQf8eA-3xkG5-z4/s1600-h/ResultsToGrid3.png"><img title="ResultsToGrid" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="ResultsToGrid" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_XsMWg944iXSjEcY_-5dbLeHSf0nVTfNKRxcfyUn1okQaCBc3KknGsaf5oH9suhBZPlPXtU4qhlDtKowP17_0o1Xe098i8jVQPAcc8xG9V7EB8_agLz5oyl9fJebjxSecy_FHQMW6JRk/?imgmax=800" width="644" height="112"></a></p> <p>- Run sp_helptext:</p> <div id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:c6019041-ed86-45f2-a0d8-e0b53f046997" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; max-height: 300px; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#0000ff">exec</span> <span style="color:#008080">usp_helptext</span><span style="color:#0000ff"> </span><span style="color:#ff0000">'usp_helptext'</span></div> </div> </div> <style type="text/css">.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }<br /></style> <p>- Copy the resultset, and paste into the code window:</p> <p><a href="http://lh5.ggpht.com/-SJlveMLmGiU/Uj7iPbA5vCI/AAAAAAAAANQ/gOaq45Mpd_0/s1600-h/image7.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyz38mW_GWqPXoNLx5HQCrdvEFsNtViWyabaeSE6lLSl8xCEFBJH5axUO-GtJI1qBvJJVCltsD95y52_BbjOI4pJ8n0zdC8xhHl17Bx16KSIrMd48vzXTJOsrHqUmlAjmf6rcN5z2CAc4/?imgmax=800" width="644" height="376"></a></p> <p>As we can se, as soon as we pasted the result into our code window, each line got an extra row.</p> <p> </p> <p><strong>II. Possible solutions:</strong></p> <blockquote> <p>- Most famous on the internet: <u>use the Results to Text (CTRL-T) option and copy from there</u>.</p> <p>- <u>Create our own version from the sp_helptext</u> stored procedure.</p> <p>- <u>Switch to SSMS 2008R2</u></p> <p>- <u>Wait until the next version of SQL Server is released.</u></p> <p>* Extra solution: use <u>regexp replace</u> on the copied result (we just have to replace all <u>\n\n patterns to \n</u></p></blockquote> <p>I think there is no really good solution for the problem: Using Results to Text option means that we always have to switch between the text and grid mode during our work. As a database engineer I think that is only waste of time. We can create an own version of sp_helptext. The advantage is that we can use it really easy and we have a full control on the solution (that is why I have chosen this method. The disadvantage is that we have to compile our custom procedure in each database that we use. Of course we can switch to the older version of Management Studio too but I am not sure backward upgrading can be a proper solution for anything, and of course we can wait until the next release but if we work with it every day we must have a fix as soon as possible. And of course, we can use regexp replace but who has such time?</p> <p> </p> <p><strong>III. Let se usp_helptext</strong></p> <p>After I decided to create a custom version, I sat down and created the following script:</p> <div id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:463c77f0-d988-4705-9cb4-d114e90b067b" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background: #000080; color: #fff; font-family: Verdana, Tahoma, Arial, sans-serif; font-weight: bold; padding: 2px 5px">usp_helptext</div> <div style="background: #ddd; max-height: 300px; overflow: auto"> <ol start="1" style="background: #ffffff; margin: 0 0 0 2.5em; padding: 0 0 0 5px; white-space: nowrap"> <li><span style="color:#0000ff">drop</span> <span style="color:#0000ff">procedure</span> <span style="color:#008080">dbo</span><span style="color:#808080">.</span><span style="color:#008080">usp_helptext</span></li> <li style="background: #f3f3f3"><span style="color:#0000ff">go</span></li> <li> </li> <li style="background: #f3f3f3"><span style="color:#0000ff">create</span> <span style="color:#0000ff">procedure</span> <span style="color:#008080">dbo</span><span style="color:#808080">.</span><span style="color:#008080">usp_helptext</span><span style="color:#808080">(</span></li> <li> <span style="color:#008080">@SPName</span> <span style="color:#0000ff">sysname</span> </li> <li style="background: #f3f3f3"><span style="color:#808080">)</span></li> <li><span style="color:#0000ff">as</span> <span style="color:#0000ff">begin</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">set</span> <span style="color:#0000ff">nocount</span> <span style="color:#0000ff">on</span></li> <li> </li> <li style="background: #f3f3f3"> <span style="color:#0000ff">declare</span> <span style="color:#008080">@LocalSPName</span> <span style="color:#0000ff">varchar</span><span style="color:#808080">(</span>100<span style="color:#808080">)</span></li> <li> <span style="color:#0000ff">declare</span> <span style="color:#008080">@Definition</span> <span style="color:#0000ff">nvarchar</span><span style="color:#808080">(</span><span style="color:#ff00ff">max</span><span style="color:#808080">)</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">declare</span> <span style="color:#008080">@NewLine</span> <span style="color:#0000ff">nchar</span><span style="color:#808080">(</span>2<span style="color:#808080">)</span> <span style="color:#808080">=</span> <span style="color:#0000ff">CHAR</span><span style="color:#808080">(</span>13<span style="color:#808080">)</span> <span style="color:#808080">+</span> <span style="color:#0000ff">CHAR</span><span style="color:#808080">(</span>10<span style="color:#808080">)</span></li> <li> <span style="color:#0000ff">declare</span> <span style="color:#008080">@SPDefTable</span> <span style="color:#0000ff">table </span><span style="color:#808080">(</span><span style="color:#008080">RowNumber</span> <span style="color:#0000ff">int</span> <span style="color:#0000ff">identity</span><span style="color:#808080">(</span>1<span style="color:#808080">,</span>1<span style="color:#808080">)</span> <span style="color:#0000ff">primary</span> <span style="color:#0000ff">key</span><span style="color:#808080">,</span> <span style="color:#0000ff">Row</span> <span style="color:#0000ff">nvarchar</span><span style="color:#808080">(</span>500<span style="color:#808080">)</span> <span style="color:#808080">not</span> <span style="color:#808080">null)</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">declare</span> <span style="color:#008080">@NLPos</span> <span style="color:#0000ff">int</span></li> <li> <span style="color:#0000ff">declare</span> <span style="color:#008080">@Row</span> <span style="color:#0000ff">nvarchar</span><span style="color:#808080">(</span><span style="color:#ff00ff">max</span><span style="color:#808080">)</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">declare</span> <span style="color:#008080">@Length</span> <span style="color:#0000ff">int</span></li> <li> </li> <li style="background: #f3f3f3"> <span style="color:#0000ff">if</span> <span style="color:#008080">@SPName</span> <span style="color:#808080">is</span> <span style="color:#808080">null</span> <span style="color:#808080">or</span> <span style="color:#008080">@SPName</span> <span style="color:#808080">=</span> <span style="color:#ff0000">''</span></li> <li> <span style="color:#0000ff">return</span></li> <li style="background: #f3f3f3"> </li> <li> <span style="color:#0000ff">set</span> <span style="color:#008080">@LocalSPName</span> <span style="color:#808080">=</span> <span style="color:#008080">@SPName</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">select</span> <span style="color:#008080">@Definition</span> <span style="color:#808080">=</span> <span style="color:#0000ff">definition</span> <span style="color:#0000ff">from</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">all_sql_modules</span> <span style="color:#0000ff">with</span><span style="color:#808080">(</span><span style="color:#0000ff">nolock</span><span style="color:#808080">)</span> <span style="color:#0000ff">where</span> <span style="color:#ff00ff">object_id</span> <span style="color:#808080">=</span> <span style="color:#ff00ff">object_id</span><span style="color:#808080">(</span><span style="color:#008080">@LocalSPName</span><span style="color:#808080">)</span></li> <li> </li> <li style="background: #f3f3f3"> <span style="color:#0000ff">if</span> <span style="color:#ff00ff">@@ROWCOUNT</span> <span style="color:#808080">=</span> 0</li> <li> <span style="color:#0000ff">return</span></li> <li style="background: #f3f3f3"> </li> <li> <span style="color:#0000ff">set</span> <span style="color:#008080">@Length</span> <span style="color:#808080">=</span> <span style="color:#ff00ff">LEN</span><span style="color:#808080">(</span><span style="color:#008080">@Definition</span><span style="color:#808080">)</span></li> <li style="background: #f3f3f3"> </li> <li> <span style="color:#0000ff">while</span> <span style="color:#008080">@Definition</span> <span style="color:#808080">></span> <span style="color:#ff0000">''</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">begin</span></li> <li> <span style="color:#0000ff">set</span> <span style="color:#008080">@NLPos</span> <span style="color:#808080">=</span> <span style="color:#ff00ff">PATINDEX</span><span style="color:#808080">(</span><span style="color:#ff0000">'%'</span> <span style="color:#808080">+</span> <span style="color:#008080">@NewLine</span> <span style="color:#808080">+</span> <span style="color:#ff0000">'%'</span><span style="color:#808080">,</span> <span style="color:#008080">@Definition</span><span style="color:#808080">)</span></li> <li style="background: #f3f3f3"> </li> <li> <span style="color:#0000ff">if</span> <span style="color:#008080">@NLPos</span> <span style="color:#808080"><></span> 0</li> <li style="background: #f3f3f3"> <span style="color:#0000ff">begin</span></li> <li> <span style="color:#0000ff">set</span> <span style="color:#008080">@Row</span> <span style="color:#808080">=</span> <span style="color:#808080">LEFT(</span><span style="color:#008080">@Definition</span><span style="color:#808080">,</span> <span style="color:#008080">@NLPos</span> <span style="color:#808080">-</span> 1<span style="color:#808080">)</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">set</span> <span style="color:#008080">@Definition</span> <span style="color:#808080">=</span> <span style="color:#ff00ff">SUBSTRING</span><span style="color:#808080">(</span><span style="color:#008080">@Definition</span><span style="color:#808080">,</span> <span style="color:#008080">@NLPos</span> <span style="color:#808080">+</span> 2<span style="color:#808080">,</span> <span style="color:#008080">@Length</span><span style="color:#808080">)</span></li> <li> <span style="color:#0000ff">end</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">else</span> </li> <li> <span style="color:#0000ff">begin</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">break</span><span style="color:#808080">;</span></li> <li> <span style="color:#0000ff">end</span></li> <li style="background: #f3f3f3"> </li> <li> <span style="color:#0000ff">insert</span> <span style="color:#0000ff">into</span> <span style="color:#008080">@SPDefTable</span><span style="color:#0000ff"> </span><span style="color:#808080">(</span><span style="color:#0000ff">Row</span><span style="color:#808080">)</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">select</span> <span style="color:#008080">@Row</span></li> <li> <span style="color:#0000ff">end</span></li> <li style="background: #f3f3f3"> </li> <li> <span style="color:#0000ff">if</span> <span style="color:#008080">@Definition</span> <span style="color:#808080">></span> <span style="color:#ff0000">''</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">insert</span> <span style="color:#0000ff">into</span> <span style="color:#008080">@SPDefTable</span><span style="color:#0000ff"> </span><span style="color:#808080">(</span><span style="color:#0000ff">Row</span><span style="color:#808080">)</span></li> <li> <span style="color:#0000ff">select</span> <span style="color:#008080">@Definition</span></li> <li style="background: #f3f3f3"> </li> <li> <span style="color:#0000ff">select</span> <span style="color:#0000ff">Row</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">from</span> <span style="color:#008080">@SPDefTable</span></li> <li> <span style="color:#0000ff">order</span> <span style="color:#0000ff">by</span> <span style="color:#008080">RowNumber</span></li> <li style="background: #f3f3f3"><span style="color:#0000ff">end</span></li> <li><span style="color:#0000ff">go</span></li> </ol> </div> </div> </div> <style type="text/css">.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }<br /></style> <p>As we can see, I have to learn a lot about programming sql server. This solution is based on my <u>structured programming</u> studies: It uses a loop and conditional statements. I made so of course it works fine but it is not a good-looking solution if your solution is set-based. It must contain some selects or it not a solution <img class="wlEmoticon wlEmoticon-openmouthedsmile" style="border-top-style: none; border-left-style: none; border-bottom-style: none; border-right-style: none" alt="Arc nagy mosollyal" src="http://lh6.ggpht.com/-aUT0y9GCdwE/Uj7iQSQu0DI/AAAAAAAAANc/igxCo2ickaQ/wlEmoticon-openmouthedsmile%25255B2%25255D.png?imgmax=800">.<br>When I have finished with my usp_helptext procedure, I showed it to my boss. He likes challenges so immediately started to type something. When he finished, he send his solution to me and I started to test it. After I recognized all the boundary cases the transact-sql solution has born:</p> <div id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:2c85fa95-a08b-461d-8980-dbe6b765368f" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background: #000080; color: #fff; font-family: Verdana, Tahoma, Arial, sans-serif; font-weight: bold; padding: 2px 5px">usp_helptext</div> <div style="background: #ddd; max-height: 300px; overflow: auto"> <ol start="1" style="background: #ffffff; margin: 0 0 0 2.5em; padding: 0 0 0 5px; white-space: nowrap"> <li><span style="color:#0000ff">drop</span> <span style="color:#0000ff">procedure</span> <span style="color:#008080">dbo</span><span style="color:#808080">.</span><span style="color:#008080">usp_helptext</span></li> <li style="background: #f3f3f3"><span style="color:#0000ff">go</span></li> <li> </li> <li style="background: #f3f3f3"><span style="color:#0000ff">create</span> <span style="color:#0000ff">procedure</span> <span style="color:#008080">dbo</span><span style="color:#808080">.</span><span style="color:#008080">usp_helptext</span><span style="color:#808080">(</span></li> <li> <span style="color:#008080">@SPName</span> <span style="color:#0000ff">varchar</span><span style="color:#808080">(</span>100<span style="color:#808080">)</span> </li> <li style="background: #f3f3f3"><span style="color:#808080">)</span></li> <li><span style="color:#0000ff">as</span> <span style="color:#0000ff">begin</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">set</span> <span style="color:#0000ff">nocount</span> <span style="color:#0000ff">on</span></li> <li> </li> <li style="background: #f3f3f3"> <span style="color:#0000ff">declare</span> <span style="color:#008080">@LocalSPName</span> <span style="color:#0000ff">varchar</span><span style="color:#808080">(</span>100<span style="color:#808080">)</span></li> <li> <span style="color:#0000ff">declare</span> <span style="color:#008080">@Definition</span> <span style="color:#0000ff">nvarchar</span><span style="color:#808080">(</span><span style="color:#ff00ff">max</span><span style="color:#808080">)</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">declare</span> <span style="color:#008080">@NewLine</span> <span style="color:#0000ff">nchar</span><span style="color:#808080">(</span>2<span style="color:#808080">)</span> <span style="color:#808080">=</span> <span style="color:#0000ff">CHAR</span><span style="color:#808080">(</span>13<span style="color:#808080">)</span> <span style="color:#808080">+</span> <span style="color:#0000ff">CHAR</span><span style="color:#808080">(</span>10<span style="color:#808080">)</span></li> <li> <span style="color:#0000ff">declare</span> <span style="color:#008080">@Length</span> <span style="color:#0000ff">int</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">declare</span> <span style="color:#008080">@ErrMsg</span> <span style="color:#0000ff">nvarchar</span><span style="color:#808080">(</span>200<span style="color:#808080">)</span></li> <li> </li> <li style="background: #f3f3f3"> <span style="color:#0000ff">if</span> <span style="color:#008080">@SPName</span> <span style="color:#808080">is</span> <span style="color:#808080">null</span> <span style="color:#808080">or</span> <span style="color:#008080">@SPName</span> <span style="color:#808080">=</span> <span style="color:#ff0000">''</span></li> <li> <span style="color:#0000ff">return</span></li> <li style="background: #f3f3f3"> </li> <li> <span style="color:#0000ff">set</span> <span style="color:#008080">@LocalSPName</span> <span style="color:#808080">=</span> <span style="color:#008080">@SPName</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">select</span> <span style="color:#008080">@Definition</span> <span style="color:#808080">=</span> <span style="color:#0000ff">definition</span> <span style="color:#0000ff">from</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">all_sql_modules</span> <span style="color:#0000ff">with</span><span style="color:#808080">(</span><span style="color:#0000ff">nolock</span><span style="color:#808080">)</span> <span style="color:#0000ff">where</span> <span style="color:#ff00ff">object_id</span> <span style="color:#808080">=</span> <span style="color:#ff00ff">object_id</span><span style="color:#808080">(</span><span style="color:#008080">@LocalSPName</span><span style="color:#808080">)</span></li> <li> </li> <li style="background: #f3f3f3"> <span style="color:#0000ff">if</span> <span style="color:#ff00ff">@@ROWCOUNT</span> <span style="color:#808080">=</span> 0</li> <li> <span style="color:#0000ff">begin</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">set</span> <span style="color:#008080">@ErrMsg</span> <span style="color:#808080">=</span> <span style="color:#ff0000">N'Cannot find stored procedure: '</span> <span style="color:#808080">+</span> <span style="color:#008080">@LocalSPName</span></li> <li> </li> <li style="background: #f3f3f3"> <span style="color:#0000ff">raiserror </span><span style="color:#808080">(</span><span style="color:#008080">@ErrMsg</span><span style="color:#808080">,</span> 16<span style="color:#808080">,</span> 1<span style="color:#808080">)</span></li> <li> <span style="color:#0000ff">end</span></li> <li style="background: #f3f3f3"> </li> <li> <span style="color:#0000ff">set</span> <span style="color:#008080">@Length</span> <span style="color:#808080">=</span> <span style="color:#ff00ff">LEN</span><span style="color:#808080">(</span><span style="color:#008080">@Definition</span><span style="color:#808080">)</span> </li> <li style="background: #f3f3f3"> </li> <li> <span style="color:#808080">;</span><span style="color:#0000ff">with</span> <span style="color:#008080">a</span> <span style="color:#0000ff">as</span> </li> <li style="background: #f3f3f3"><span style="color:#0000ff"> </span><span style="color:#808080">(</span> </li> <li> <span style="color:#0000ff">select</span> <span style="color:#ff00ff">cast</span><span style="color:#808080">(</span>1 <span style="color:#0000ff">as</span> <span style="color:#0000ff">bigint</span><span style="color:#808080">)</span> <span style="color:#008080">start</span><span style="color:#808080">,</span> <span style="color:#ff00ff">PATINDEX</span><span style="color:#808080">(</span><span style="color:#ff0000">'%'</span> <span style="color:#808080">+</span> <span style="color:#008080">@NewLine</span> <span style="color:#808080">+</span> <span style="color:#ff0000">'%'</span><span style="color:#808080">,</span> <span style="color:#008080">@Definition</span><span style="color:#808080">)</span> <span style="color:#008080">curlength</span><span style="color:#808080">,</span> 0 <span style="color:#0000ff">as</span> <span style="color:#008080">stopthere</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">union</span> <span style="color:#808080">all</span> </li> <li> <span style="color:#0000ff">select</span> <span style="color:#008080">a</span><span style="color:#808080">.</span><span style="color:#008080">start</span><span style="color:#808080">+</span><span style="color:#008080">a</span><span style="color:#808080">.</span><span style="color:#008080">curlength</span><span style="color:#808080">,</span> <span style="color:#ff00ff">PATINDEX</span><span style="color:#808080">(</span><span style="color:#ff0000">'%'</span> <span style="color:#808080">+</span> <span style="color:#008080">@NewLine</span> <span style="color:#808080">+</span> <span style="color:#ff0000">'%'</span><span style="color:#808080">,</span> <span style="color:#ff00ff">SUBSTRING</span><span style="color:#808080">(</span><span style="color:#008080">@Definition</span><span style="color:#808080">,</span> <span style="color:#008080">a</span><span style="color:#808080">.</span><span style="color:#008080">start</span><span style="color:#808080">+</span><span style="color:#008080">a</span><span style="color:#808080">.</span><span style="color:#008080">curlength</span><span style="color:#808080">,</span> <span style="color:#008080">@Length</span><span style="color:#808080">))</span> <span style="color:#808080">,</span> <span style="color:#0000ff">case</span> <span style="color:#0000ff">when</span> <span style="color:#ff00ff">PATINDEX</span><span style="color:#808080">(</span><span style="color:#ff0000">'%'</span> <span style="color:#808080">+</span> <span style="color:#008080">@NewLine</span> <span style="color:#808080">+</span> <span style="color:#ff0000">'%'</span><span style="color:#808080">,</span> <span style="color:#ff00ff">SUBSTRING</span><span style="color:#808080">(</span><span style="color:#008080">@Definition</span><span style="color:#808080">,</span> <span style="color:#008080">a</span><span style="color:#808080">.</span><span style="color:#008080">start</span><span style="color:#808080">+</span><span style="color:#008080">a</span><span style="color:#808080">.</span><span style="color:#008080">curlength</span><span style="color:#808080">,</span> <span style="color:#008080">@Length</span><span style="color:#808080">))</span> <span style="color:#808080">=</span> 0 <span style="color:#0000ff">then</span> 1 <span style="color:#0000ff">else</span> 0 <span style="color:#0000ff">end</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">from</span> <span style="color:#008080">a</span> </li> <li> <span style="color:#0000ff">where</span> <span style="color:#008080">a</span><span style="color:#808080">.</span><span style="color:#008080">start</span><span style="color:#808080">+</span><span style="color:#008080">a</span><span style="color:#808080">.</span><span style="color:#008080">curlength</span> <span style="color:#808080"><</span> <span style="color:#008080">@Length</span> <span style="color:#808080">and</span> <span style="color:#008080">a</span><span style="color:#808080">.</span><span style="color:#008080">stopthere</span> <span style="color:#808080">=</span> 0</li> <li style="background: #f3f3f3"> <span style="color:#808080">)</span> </li> <li> <span style="color:#0000ff">select</span> <span style="color:#ff00ff">REPLACE</span><span style="color:#808080">(</span><span style="color:#ff00ff">REPLACE</span><span style="color:#808080">(</span><span style="color:#ff00ff">SUBSTRING</span><span style="color:#808080">(</span><span style="color:#008080">@Definition</span><span style="color:#808080">,</span> <span style="color:#008080">a</span><span style="color:#808080">.</span><span style="color:#008080">start</span><span style="color:#808080">,</span> <span style="color:#008080">a</span><span style="color:#808080">.</span><span style="color:#008080">curlength</span><span style="color:#808080">),</span> <span style="color:#0000ff">CHAR</span><span style="color:#808080">(</span>10<span style="color:#808080">),</span> <span style="color:#ff0000">''</span><span style="color:#808080">),</span> <span style="color:#0000ff">CHAR</span><span style="color:#808080">(</span>13<span style="color:#808080">),</span> <span style="color:#ff0000">''</span><span style="color:#808080">)</span> </li> <li style="background: #f3f3f3"> <span style="color:#0000ff">from</span> <span style="color:#008080">a</span> </li> <li> <span style="color:#0000ff">option </span><span style="color:#808080">(</span><span style="color:#008080">maxrecursion</span> 0<span style="color:#808080">)</span></li> <li style="background: #f3f3f3"> </li> <li><span style="color:#0000ff">end</span></li> <li style="background: #f3f3f3"><span style="color:#0000ff">go</span></li> </ol> </div> </div> </div> <p>I am proud to this solution. It is really a <u>set-based solution</u> and it shows what is the difference between an usual programmer and an sql developer. The golden rule is: If we want to use a loop we just have to sit down and search a proper select statement.</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com1tag:blogger.com,1999:blog-8012614073429928775.post-35098996926435080622013-03-29T18:51:00.001+01:002013-03-29T18:51:34.388+01:00The real multi-lingual site<p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsTTA4xThu9vlIh9ilF6uyyJWGggPLxhjBsoejRmVe4vTk4OwlbBpWN1s6pQnpq5KSwcjLiS3i4zmBMl54UzaPKJJ8mGZbyDHqxEbFWxmnknBsOcA8Lj2nGqILln9Udp_P6-9K1a-BDrM/s1600-h/Multi-Language%252520site%25255B3%25255D.png"><img title="Multi-Language site" style="border-left-width: 0px; border-right-width: 0px; border-bottom-width: 0px; display: inline; border-top-width: 0px" border="0" alt="Multi-Language site" src="http://lh4.ggpht.com/-cct3j7rBPJE/UVXUpd4ZYdI/AAAAAAAAAK0/qtjDlMiZmo8/Multi-Language%252520site_thumb%25255B1%25255D.png?imgmax=800" width="470" height="356"></a></p> <p>We can find hungarian, english and german languages in one form.</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-24134595109848365302012-04-27T12:17:00.001+02:002012-04-27T12:18:27.963+02:00Sql Server–Row Count for All Tables<p> </p> <p>Tested on Sql Server 2008 and 2008 R2.</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:c384905d-b5e9-41bd-852f-86e1402847ca" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background: #000080; color: #fff; font-family: Verdana, Tahoma, Arial, sans-serif; font-weight: bold; padding: 2px 5px">Code Snippet</div> <div style="background: #ddd; max-height: 300px; overflow: auto"> <ol start="1" style="background: #ffffff; margin: 0 0 0 2.5em; padding: 0 0 0 5px;"> <li><span style="color:#0000ff">select</span> <span style="color:#ff00ff">SCHEMA_NAME</span><span style="color:#808080">(</span><span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">tables</span><span style="color:#808080">.</span><span style="color:#ff00ff">schema_id</span><span style="color:#808080">)</span> <span style="color:#0000ff">as</span> [Schema]<span style="color:#808080">,</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">tables</span><span style="color:#808080">.</span>name <span style="color:#0000ff">as</span> [Table]<span style="color:#808080">,</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">sysindexes</span><span style="color:#808080">.</span>rowcnt <span style="color:#0000ff">as</span> [Cnt] </li> <li style="background: #f3f3f3"><span style="color:#0000ff">from</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">indexes</span></li> <li> <span style="color:#808080">join</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">sysindexes</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">on</span> <span style="color:#008000">sysindexes</span><span style="color:#808080">.</span>id <span style="color:#808080">=</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">indexes</span><span style="color:#808080">.</span><span style="color:#ff00ff">object_id</span></li> <li> <span style="color:#808080">and</span> <span style="color:#008000">sysindexes</span><span style="color:#808080">.</span>indid <span style="color:#808080">=</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">indexes</span><span style="color:#808080">.</span>index_id</li> <li style="background: #f3f3f3"> <span style="color:#808080">join</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">tables</span></li> <li> <span style="color:#0000ff">on</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">tables</span><span style="color:#808080">.</span><span style="color:#ff00ff">object_id</span> <span style="color:#808080">=</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">sysindexes</span><span style="color:#808080">.</span>id</li> <li style="background: #f3f3f3"><span style="color:#0000ff">where</span> is_primary_key <span style="color:#808080">=</span> 1</li> <li> <span style="color:#808080">and</span> <span style="color:#008000">sys</span><span style="color:#808080">.</span><span style="color:#008000">tables</span><span style="color:#808080">.</span>type_desc <span style="color:#808080">=</span> <span style="color:#ff0000">'USER_TABLE'</span></li> <li style="background: #f3f3f3"><span style="color:#0000ff">order</span> <span style="color:#0000ff">by</span> [Schema]<span style="color:#808080">,</span> [Table]</li> </ol> </div> </div> </div> <p>It is simple, isn’t it?</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-75568551180401161072012-04-05T13:04:00.001+02:002012-04-05T15:19:04.196+02:00Looking for unused indexes in Sql Server 2008<p> </p> <p>In a long running project the database is changing day by day. We create new tables, optimize our queries, and of course, make new indexes. Usually when I finish an optimization process I forget an important step: </p> <p>I always forget to check how many indexes I have that I don’t use anymore.</p> <p>Sql server stores <strong>index statisctics in</strong> a sys.dm_ view named <strong>SYS.DM_DB_INDEX_USAGE_STATS</strong>. As the other sys.dm_ views, <strong>sql server clears statistics on every restart</strong>, so we have to check after missing indexes only for our database instance had run for significant time and we had collected enough statistics to decide if we delete an index or not.</p> <p>Now after we have understood the basic, let’s see the query for showing our index statistics:<br> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:645d490e-8d67-44ef-99a4-ee52f5e47b59" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; max-height: 500px; overflow: auto; padding: 2px 5px;"><span style="color:#0000ff">DECLARE</span> @table_name <span style="color:#0000ff">varchar</span><span style="color:#808080">(</span>100<span style="color:#808080">)</span> <span style="color:#808080">=</span> <span style="color:#808080">null</span><br> <br> <span style="color:#008000">-- Uncomment the following line if you want to filter for a given table</span><br> <span style="color:#008000">--set @table_name = 'table_name'</span><br> <br> <span style="color:#0000ff">SELECT</span><br> <span style="color:#ff00ff">OBJECT_NAME</span><span style="color:#808080">(</span>I<span style="color:#808080">.</span>[OBJECT_ID]<span style="color:#808080">)</span> <span style="color:#0000ff">AS</span> [OBJECT NAME]<span style="color:#808080">,</span> <br> I<span style="color:#808080">.</span>[object_id]<span style="color:#808080">,</span><br> I<span style="color:#808080">.</span>[NAME] <span style="color:#0000ff">AS</span> [INDEX NAME]<span style="color:#808080">,</span> <br> <span style="color:#ff00ff">sum</span><span style="color:#808080">(</span>USER_SEEKS<span style="color:#808080">)</span> <span style="color:#0000ff">as</span> USER_SEEKS<span style="color:#808080">,</span> <br> <span style="color:#ff00ff">sum</span><span style="color:#808080">(</span>USER_SCANS<span style="color:#808080">)</span> <span style="color:#0000ff">as</span> USER_SCANS<span style="color:#808080">,</span> <br> <span style="color:#ff00ff">sum</span><span style="color:#808080">(</span>USER_LOOKUPS<span style="color:#808080">)</span> <span style="color:#0000ff">as</span> USER_LOOKUPS<span style="color:#808080">,</span> <br> <span style="color:#ff00ff">sum</span><span style="color:#808080">(</span>USER_UPDATES<span style="color:#808080">)</span> <span style="color:#0000ff">as</span> USER_UPDATES<br> <span style="color:#0000ff">FROM</span><span style="color:#008000">SYS</span><span style="color:#808080">.</span><span style="color:#008000">INDEXES</span> <span style="color:#0000ff">AS</span> I <br> <span style="color:#808080">LEFT</span> <span style="color:#808080">JOIN</span> <span style="color:#008000">SYS</span><span style="color:#808080">.</span><span style="color:#008000">DM_DB_INDEX_USAGE_STATS</span> <span style="color:#0000ff">AS</span> S <br> <span style="color:#0000ff">ON</span> I<span style="color:#808080">.</span>[OBJECT_ID] <span style="color:#808080">=</span> S<span style="color:#808080">.</span>[OBJECT_ID] <br> <span style="color:#808080">AND</span> I<span style="color:#808080">.</span>INDEX_ID <span style="color:#808080">=</span> S<span style="color:#808080">.</span>INDEX_ID <br> <span style="color:#0000ff">WHERE</span><span style="color:#ff00ff">OBJECTPROPERTY</span><span style="color:#808080">(</span>I<span style="color:#808080">.</span>[OBJECT_ID]<span style="color:#808080">,</span><span style="color:#ff0000">'IsUserTable'</span><span style="color:#808080">)</span> <span style="color:#808080">=</span> 1<br> <span style="color:#808080">and</span><span style="color:#0000ff"> </span><span style="color:#808080">(</span>@table_name <span style="color:#808080">is</span> <span style="color:#808080">null</span> <span style="color:#808080">or</span> <span style="color:#ff00ff">OBJECT_NAME</span><span style="color:#808080">(</span>I<span style="color:#808080">.</span>[OBJECT_ID]<span style="color:#808080">)</span> <span style="color:#808080">=</span> @table_name<span style="color:#808080">)</span> <br> <br> <span style="color:#0000ff">group</span> <span style="color:#0000ff">by</span> <span style="color:#ff00ff">OBJECT_NAME</span><span style="color:#808080">(</span>I<span style="color:#808080">.</span>[OBJECT_ID]<span style="color:#808080">),</span> <br> I<span style="color:#808080">.</span>[object_id]<span style="color:#808080">,</span><br> I<span style="color:#808080">.</span>[NAME]<br> <span style="color:#0000ff">order</span> <span style="color:#0000ff">by</span> [OBJECT NAME]<span style="color:#808080">,</span> [INDEX NAME]</div> </div> </div></p> <p>As we can see on our column names, we access seek, scan, lookup and index update statistics counted since the last start of our sql server instance. Our result can contain null statistics. In that case we don’t have any statistics about the given index since the last restart.</p> <p>This script contains one parameter (@table_name). If we uncomment the 4th line, we can filter for indexes only on a given table.</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-76007021646548074042012-03-19T23:53:00.000+01:002012-03-19T23:53:15.695+01:004000 visitors<div dir="ltr" style="text-align: left;" trbidi="on">
4000 visitors! :)<br /><br />I work on 3 projects during this month and don't have a time to write. <br />The life continues on April.<br /><br />Morzel</div>Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-50595061886153296082012-03-01T14:20:00.001+01:002012-03-01T14:20:57.446+01:00ProgrammerFail - Else or Continue?<p> </p> <p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:0ebac83f-0cef-4d34-becf-a69df3806e85" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px;"><span style="color:#0000ff">if</span> (value < 100)<br> {<br> value = 100 - value;<br> <span style="color:#0000ff">continue</span>; <span style="color:#008000">// Why??? </span><br> }<br> <span style="color:#0000ff">else</span><br> {<br> value = value + 100;<br> }</div> </div> </div>Double penetration – I really don’t want to run the else clause…</p> <p align="center"><a href="http://lh6.ggpht.com/-FGBcs2GfYck/T093tQZ5VkI/AAAAAAAAAJw/rJ_rDbyBTMU/s1600-h/ProgrammerFail%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="ProgrammerFail" border="0" alt="ProgrammerFail" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUvJbUMV_-KyJW8sLt34ucKRtIe8VY-tcc9f4sgXeBrqzbK2nNrOEaIOrrepIA4g2pVGPvmwcvHxfp-ylmWHZ_RHBo4t6c_VdaGVHZgWTxyJeSSG121zf1W__KrVYZZzWB64XWQPlzV7g/?imgmax=800" width="244" height="205"></a></p> <p align="center"><font color="#c0504d" size="6">!!!FAIL!!!</font></p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-21548582256750589052012-02-27T16:20:00.001+01:002012-02-27T16:20:18.011+01:00ProgrammerFail–Using System Functions<p> </p> <p>Nice example that shows what happens if we don’t have enough time to read a book about ‘Teach Yourself SQL Server Programming in 24 hours’ and start programming without knowing the tool that we use.</p> <p>After we check <a href="http://msdn.microsoft.com/en-us/library/ms189794.aspx">msdn</a>, we can easily check if two dates are in the same day:<br> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:5569b894-a4b7-489f-9cd6-98caa9d9652b" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px;"><br> <span style="color:#0000ff">create</span> <span style="color:#0000ff">FUNCTION</span> [dbo]<span style="color:#808080">.</span>[isSameDay]<span style="color:#808080">(</span>@DATE1 <span style="color:#0000ff">datetime</span><span style="color:#808080">,</span> @DATE2 <span style="color:#0000ff">datetime</span><span style="color:#808080">)</span><br> <span style="color:#0000ff">RETURNS</span> <span style="color:#0000ff">bit</span><br> <span style="color:#0000ff">AS</span><br> <span style="color:#0000ff">BEGIN</span><br> <span style="color:#0000ff">DECLARE</span> @result <span style="color:#0000ff">bit</span><span style="color:#808080">=</span>0<br> <span style="color:#0000ff">if </span><span style="color:#808080">(</span><span style="color:#ff00ff">year</span><span style="color:#808080">(</span>@DATE1<span style="color:#808080">)=</span><span style="color:#ff00ff">year</span><span style="color:#808080">(</span>@DATE2<span style="color:#808080">))</span> <br> <span style="color:#808080">and</span><span style="color:#0000ff"> </span><span style="color:#808080">(</span><span style="color:#ff00ff">month</span><span style="color:#808080">(</span>@DATE1<span style="color:#808080">)=</span><span style="color:#ff00ff">month</span><span style="color:#808080">(</span>@DATE2<span style="color:#808080">))</span><br> <span style="color:#808080">and</span><span style="color:#0000ff"> </span><span style="color:#808080">(</span><span style="color:#ff00ff">day</span><span style="color:#808080">(</span>@DATE1<span style="color:#808080">)=</span><span style="color:#ff00ff">day</span><span style="color:#808080">(</span>@DATE2<span style="color:#808080">))</span><br> <span style="color:#0000ff">set</span> @result<span style="color:#808080">=</span>1<br> <span style="color:#0000ff">return</span> @result<br> <span style="color:#0000ff">END</span></div> </div> </div></p> <p align="center"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikIDWB72aWZON7QYvyIniybW8cJQ69bge15K0ATNv6acaZnXmcHUfUrElbBLPx8BmJNdJRox3vQVcdry1kJuAIm4U9peMjJ4Wquw-fFgb865jyomX92OeA49q6R8RVp12lkwshdwLRpPY/s1600-h/ProgrammerFail%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="ProgrammerFail" border="0" alt="ProgrammerFail" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSxoFK4E6bSFzarOgXErUJwcDb4w9uroMpmJP00toKJ2oNat_S8lrKNfOmK9O4tbTlCVPiihOQF21dWH_HEfWSwQCOlQ0rXE4dqXMtrfZCi0rlCIR_MGU2Tmx_hCBkUkhesWzpNlOiPoY/?imgmax=800" width="244" height="205"></a></p> <p align="center"><font color="#c0504d" size="6">!!!FAIL!!!</font></p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-55625749963411457882012-02-24T10:11:00.001+01:002012-02-24T10:11:51.111+01:00Sql Server–Query random N elements from a table with identity PK<p> </p> <p>A simple solution how to query random N records from a database table without iteration on a full table:</p> <p> </p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:b7661d6b-0d76-44d8-9d2f-711ea500f3a2" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px;"><br> <span style="color:#008000">--- because cannot call rand() in scalar valued function</span><br> <span style="color:#0000ff">CREATE</span> <span style="color:#0000ff">VIEW</span> vRandNumber<br> <span style="color:#0000ff">AS</span><br> <span style="color:#0000ff">SELECT</span> <span style="color:#ff00ff">RAND</span><span style="color:#808080">()</span> <span style="color:#0000ff">as</span> RandNumber<br> <br> <span style="color:#0000ff">go</span><br> <br> <span style="color:#008000">--- returns a randon number between min and max</span><br> <span style="color:#0000ff">CREATE</span> <span style="color:#0000ff">FUNCTION</span> RandNumber<span style="color:#808080">(</span>@Min <span style="color:#0000ff">int</span><span style="color:#808080">,</span> @Max <span style="color:#0000ff">int</span><span style="color:#808080">)</span><br> <span style="color:#0000ff">RETURNS</span> <span style="color:#0000ff">int</span><br> <span style="color:#0000ff">AS</span><br> <span style="color:#0000ff">BEGIN</span><br> <span style="color:#0000ff">RETURN</span> <span style="color:#ff00ff">ceiling</span><span style="color:#808080">(</span>@Min <span style="color:#808080">+</span><span style="color:#0000ff"> </span><span style="color:#808080">(</span><span style="color:#0000ff">select</span> RandNumber <span style="color:#0000ff">from</span> vRandNumber<span style="color:#808080">)</span> <span style="color:#808080">*</span><span style="color:#0000ff"> </span><span style="color:#808080">(</span>@Max<span style="color:#808080">-</span>@Min<span style="color:#808080">))</span><br> <span style="color:#0000ff">END</span><br> <br> <span style="color:#0000ff">go</span><br> <br> <br> <span style="color:#008000">-- selecting 100 random rows from a table</span><br> <span style="color:#0000ff">declare</span> @cnt <span style="color:#0000ff">int</span><br> <br> <span style="color:#0000ff">select</span> @cnt <span style="color:#808080">=</span> <span style="color:#ff00ff">MAX</span><span style="color:#808080">(<</span>pk_column_name<span style="color:#808080">,</span> <span style="color:#0000ff">sysname</span><span style="color:#808080">,</span> ID<span style="color:#808080">>)</span> <br> <span style="color:#0000ff">from</span> <span style="color:#808080"><</span>table_name<span style="color:#808080">,</span> <span style="color:#0000ff">sysname</span><span style="color:#808080">,</span> sample_table<span style="color:#808080">></span><br> <br> <br> <br> <span style="color:#0000ff">select</span> <span style="color:#0000ff">top</span> <span style="color:#808080"><</span>number_of_rows<span style="color:#808080">,,</span> 100<span style="color:#808080">></span> <span style="color:#808080">*</span> <br> <span style="color:#0000ff">from</span> <span style="color:#808080"><</span>table_name<span style="color:#808080">,</span> <span style="color:#0000ff">sysname</span><span style="color:#808080">,</span> sample_table<span style="color:#808080">></span><br> <span style="color:#0000ff">where</span> <span style="color:#808080"><</span>pk_column_name<span style="color:#808080">,</span> <span style="color:#0000ff">sysname</span><span style="color:#808080">,</span> ID<span style="color:#808080">></span> <span style="color:#808080">in</span><span style="color:#0000ff"> </span><span style="color:#808080">(</span><br> <span style="color:#0000ff">SELECT</span> dbo<span style="color:#808080">.</span>RandNumber<span style="color:#808080">(</span>1<span style="color:#808080">,</span> @cnt<span style="color:#808080">)</span> <span style="color:#0000ff">as</span> rnd<br> <span style="color:#0000ff">from</span> <span style="color:#808080"><</span>table_name<span style="color:#808080">,</span> <span style="color:#0000ff">sysname</span><span style="color:#808080">,</span> sample_table<span style="color:#808080">></span><br> <span style="color:#808080">)</span> <br> <span style="color:#0000ff">option </span><span style="color:#808080">(</span><span style="color:#0000ff">recompile</span><span style="color:#808080">)</span> <span style="color:#008000">-- it selects only one row without recompile</span></div> </div> </div> <p> </p> <p>That’s all.</p> <p> </p> <p>Morzels</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-45927838109437883812012-02-20T09:26:00.001+01:002012-02-20T09:26:47.119+01:00ProgrammerFail–Stored Procedures<p> </p> <p>I have found this construction in a living project’s db layer:<br><br></p> <p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:b4ea627a-8ada-4163-9239-a9d8e8467264" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px;"><span style="color:#0000ff">ALTER</span> <span style="color:#0000ff">PROCEDURE</span> [dbo]<span style="color:#808080">.</span>[GetSomething] @param <span style="color:#0000ff">VARCHAR</span><span style="color:#808080">(</span>100<span style="color:#808080">)</span><br> <span style="color:#0000ff">AS</span><br> <span style="color:#0000ff">BEGIN</span><br> <span style="color:#0000ff">exec</span> [dbo]<span style="color:#808080">.</span>[GetSomething2]<span style="color:#0000ff"> </span>@param<br> <span style="color:#008000">/*</span><br> <span style="color:#008000"> -- Original Select</span><br> <span style="color:#008000"> select .....</span><br> <br> <span style="color:#008000">*/</span><br> <span style="color:#0000ff">END</span></div> </div> </div></p> <p><br>And what is in the stored procedure that we call:</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:c4890b74-5313-4715-9649-e04197c5de01" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px;"><span style="color:#0000ff">ALTER</span> <span style="color:#0000ff">PROCEDURE</span> [dbo]<span style="color:#808080">.</span>[GetSomething2]<span style="color:#0000ff"> </span><span style="color:#808080">(</span><br> @param <span style="color:#0000ff">varchar</span><span style="color:#808080">(</span>100<span style="color:#808080">)</span><br> <span style="color:#808080">)</span> <span style="color:#0000ff">AS</span><br> <span style="color:#0000ff">BEGIN</span><br> <span style="color:#008000">--- new select</span><br> <span style="color:#0000ff">select</span> <span style="color:#808080">....</span><br> <br> <span style="color:#0000ff">END</span></div> </div> </div> <p> </p> <p>Nice code if we collect stored procedures instead of stamps…</p> <p align="center"><a href="http://lh5.ggpht.com/-tMqiceYvgwM/T0IDxEbOEpI/AAAAAAAAAJQ/KJnjXVYKyZs/s1600-h/ProgrammerFail%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="ProgrammerFail" border="0" alt="ProgrammerFail" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvkPTxP1jzKCUVz4JTTU-a5Rzyl-65T55MQ2w8vwn1IgqHDzNry5LwRBwXowwL_R2O6a-6PQNank1IrbxGIIxfxQc8ghhpb5uATKWYVcOPqoLyeqYef7pQVBejkps9XwYl_8J9k43uZmI/?imgmax=800" width="244" height="205"></a></p> <p align="center"><font color="#c0504d" size="6">!!!FAIL!!!</font></p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-18280757606908757352012-02-09T10:54:00.001+01:002012-02-09T11:20:53.815+01:00ProgrammerFail–The Fail of Naming<p> </p> <p> </p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:fa2ac8aa-a853-405d-afcf-e484866cc917" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px;"><span style="color:#0000ff">if</span> (!need_to_update)<br> update_all_views();</div> </div> </div> <p>Let's update all the views, when there is no need to update.. As we can see high quality comments describe what this piece of shit does.</p> <p> </p> <p align="center"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigPQayvsJ1iBKmi7U9U_SezOKDZZ1DU9tvvl7LJ_qS-1eBR7isriGRdcMma6pEgAfwBDypG12EG8INjX1y9Q4ukJBlLeS51k8bRFDw_nf0bmNFViSRzEI-dGD7r-LRt-6x0Rm55PfwRII/s1600-h/ProgrammerFail%25255B3%25255D.jpg"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="ProgrammerFail" border="0" alt="ProgrammerFail" src="http://lh6.ggpht.com/-k9xR4TVBZZE/TzOXume0VUI/AAAAAAAAAJE/PJJT0zexSgg/ProgrammerFail_thumb.jpg?imgmax=800" width="244" height="205"></a></p> <p align="center"><font color="#c0504d" size="6">!!!FAIL!!!</font></p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com1tag:blogger.com,1999:blog-8012614073429928775.post-67295718515846788372012-02-07T14:22:00.001+01:002012-02-07T14:34:11.768+01:00ProgrammerFail–Advanced techniques using Sql Server in C#<p> </p> <p> <p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:3c124da2-b2b8-43a3-a201-0825f20a796c" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px;">[<span style="color:#2b91af">WebMethod</span>]<br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">static</span> <span style="color:#0000ff">string</span> CreateProfileID(<span style="color:#0000ff">string</span> firstName, <span style="color:#0000ff">string</span> lastName)<br> {<br> <span style="color:#0000ff">string</span> profileID = <span style="color:#a31515">""</span>;<br> <br> <span style="color:#0000ff">using</span> (<span style="color:#2b91af">SqlConnection</span> conn = <span style="color:#2b91af">Common</span>.getConnection())<br> {<br> <span style="color:#0000ff">try</span><br> {<br> conn.Open();<br> <span style="color:#2b91af">SqlCommand</span> cmd = <span style="color:#0000ff">new</span> <span style="color:#2b91af">SqlCommand</span>();<br> cmd.Connection = conn;<br> cmd.CommandType = System.Data.<span style="color:#2b91af">CommandType</span>.Text;<br> cmd.CommandText = <span style="color:#0000ff">string</span>.Format(<span style="color:#a31515">"select dbo.[create-something] ('{0}', '{1}') as somethingID"</span>, firstName, lastName);<br> <br> <span style="color:#2b91af">SqlDataReader</span> dr = cmd.ExecuteReader();<br> <span style="color:#0000ff">if</span> (dr.Read())<br> {<br> profileID = dr[<span style="color:#a31515">"somethingID"</span>] <span style="color:#0000ff">as</span> <span style="color:#0000ff">string</span>;<br> }<br> }<br> <span style="color:#0000ff">catch</span> (<span style="color:#2b91af">Exception</span> e)<br> {<br> <br> }<br> }<br> <br> <span style="color:#0000ff">return</span> profileID;<br> }</div> </div> </div><br>Everything in one method: How to handle exceptions.<br></p> <p> </p><a href="http://lh6.ggpht.com/-dojClSDR9xs/TzElkEG88pI/AAAAAAAAAIw/eOsFgQp6CXw/s1600-h/ProgrammerFail%25255B3%25255D.jpg"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto; padding-top: 0px" title="ProgrammerFail" border="0" alt="ProgrammerFail" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJkjy7lQuHUrQA6FHvFpmbDtQHeb1HNdMlLajNJwoPw1xhBWnq47LRoLz6R6dgFaGM-D04jsCZX22V_2lWv0HrjZN13JdEJWI7O_HK3u5C3-Xrl0-NecJeFWvU4B0HkHE8qZqDTIb-370/?imgmax=800" width="244" height="205"></a> <blockquote> <p align="center"><font color="#c0504d" size="6">!!!FAIL!!!</font></p></blockquote> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-19642037214229533712012-02-02T12:02:00.001+01:002012-02-02T12:02:09.210+01:00ProgrammerFail–Go To The Hell<p> </p> <p>Without any comment, let’s <a href="http://channel9.msdn.com/achievements/visualstudio/GotoAchievement">go to the hell</a>:</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:e294f237-1b2f-4906-8822-8e0e9d68fa40" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px;"><span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> WriteRaw writes out the given string "unescaped", in other words it better be well formed XML markup.</span><br> <span style="color:#808080">///</span><span style="color:#008000"> So for the XmlNodeWriter we parse this string and build the resulting tree, so it maps to setting the</span><br> <span style="color:#808080">///</span><span style="color:#008000"> InnerXml property. </span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><param name="data"></param></span><br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">override</span> <span style="color:#0000ff">void</span> WriteRaw(<span style="color:#0000ff">string</span> data)<br> {<br> <span style="color:#0000ff">if</span> (data.IndexOf(<span style="color:#a31515">"<"</span>) < 0)<br> {<br> WriteString(data);<br> <span style="color:#0000ff">return</span>;<br> }<br> <br> <span style="color:#0000ff">switch</span> (state)<br> {<br> <span style="color:#0000ff">case</span> <span style="color:#2b91af">WriteState</span>.Start:<br> <span style="color:#0000ff">goto</span> <span style="color:#0000ff">case</span> WriteState.Content;<br> <span style="color:#0000ff">case</span> <span style="color:#2b91af">WriteState</span>.Prolog:<br> <span style="color:#0000ff">goto</span> <span style="color:#0000ff">case</span> WriteState.Content;<br> <span style="color:#0000ff">case</span> <span style="color:#2b91af">WriteState</span>.Element:<br> state = <span style="color:#2b91af">WriteState</span>.Content;<br> <span style="color:#0000ff">goto</span> <span style="color:#0000ff">case</span> WriteState.Content;<br> <span style="color:#0000ff">case</span> <span style="color:#2b91af">WriteState</span>.Attribute:<br> {<br> <span style="color:#2b91af">ArrayList</span> saved = <span style="color:#0000ff">new</span> <span style="color:#2b91af">ArrayList</span>();<br> <span style="color:#0000ff">if</span> (ca.HasChildNodes)<br> {<br> <span style="color:#0000ff">while</span> (ca.FirstChild != <span style="color:#0000ff">null</span>)<br> {<br> saved.Add(ca.FirstChild);<br> ca.RemoveChild(ca.FirstChild);<br> }<br> }<br> ca.InnerXml = data;<br> <span style="color:#0000ff">for</span> (<span style="color:#0000ff">int</span> i = saved.Count - 1; i >= 0; i--)<br> {<br> ca.PrependChild((<span style="color:#2b91af">XmlNode</span>)saved[i]);<br> }<br> }<br> <span style="color:#0000ff">break</span>;<br> <span style="color:#0000ff">case</span> <span style="color:#2b91af">WriteState</span>.Content:<br> {<br> <span style="color:#2b91af">ArrayList</span> saved = <span style="color:#0000ff">new</span> <span style="color:#2b91af">ArrayList</span>();<br> <span style="color:#0000ff">if</span> (current.HasChildNodes)<br> {<br> <span style="color:#0000ff">while</span> (current.FirstChild != <span style="color:#0000ff">null</span>)<br> {<br> saved.Add(current.FirstChild);<br> current.RemoveChild(current.FirstChild);<br> }<br> }<br> current.InnerXml = data;<br> <span style="color:#0000ff">for</span> (<span style="color:#0000ff">int</span> i = saved.Count - 1; i >= 0; i--)<br> {<br> current.PrependChild((<span style="color:#2b91af">XmlNode</span>)saved[i]);<br> }<br> state = <span style="color:#2b91af">WriteState</span>.Content;<br> }<br> <span style="color:#0000ff">break</span>;<br> <span style="color:#0000ff">case</span> <span style="color:#2b91af">WriteState</span>.Closed:<br> <span style="color:#0000ff">throw</span> <span style="color:#0000ff">new</span> <span style="color:#2b91af">InvalidOperationException</span>(<span style="color:#a31515">"Writer is closed"</span>);<br> }<br> <br> }</div> </div> </div> <p>Nice combination of using <a href="http://msdn.microsoft.com/en-us/library/13940fs2(v=vs.100).aspx">goto</a> and <a href="http://msdn.microsoft.com/en-us/library/06tc147t.aspx">switch</a> statement together. I hope I will never work with the author.</p> <p> </p> <p><a href="http://lh3.ggpht.com/-IbcClbtiRLQ/TyptLW4zfMI/AAAAAAAAAIg/T78UZNelPCI/s1600-h/ProgrammerFail3.jpg"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; margin-left: auto; border-left-width: 0px; margin-right: auto; padding-top: 0px" title="ProgrammerFail" border="0" alt="ProgrammerFail" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFCPthhR6FztnalIzOtvojuwBcHc302weEuhiICK9CG5Uel_dQ5wPTTpz5BtfLCkgjQY-ZsPxZJ5XIQt5sZaLkVrZWk4lTItPv00NUDg_k7Ylmbe7S8mZKURPx-QV0vPiLUI_bCPEpn5M/?imgmax=800" width="244" height="205"></a></p> <p align="center"><font color="#c0504d" size="6">!!!FAIL!!!</font></p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com7tag:blogger.com,1999:blog-8012614073429928775.post-91515892756554203952012-01-26T15:08:00.001+01:002012-01-27T10:39:52.613+01:00ProgrammerFail–N Layered Architecture<p>What is wrong with the following code?</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:d10adf65-881f-4876-8422-f409b99ca561" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px;"><span style="color:#0000ff">using</span> (<span style="color:#2b91af">SqlDataReader</span> dr = ...)</div> </div> </div> <style type="text/css">.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }<br /></style> <p>At the first look, it seems perfect but:</p> <ul> <li>N layered architecture <li>Layers are: DAO –> Factory –> BL –>WebApp</li></ul> <p> </p> <p>And this code with the previous row:</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:df9efe37-4bcb-409f-9d11-e3ac315e0c6f" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px;"><span style="color:#0000ff">protected</span> <span style="color:#0000ff">void</span> ddlUnits_SelectedIndexChanged(<span style="color:#0000ff">object</span> sender, <span style="color:#2b91af">EventArgs</span> e)<br> {<br> <span style="color:#0000ff">using</span> (<span style="color:#2b91af">SqlDataReader</span> dr = ...)</div> </div> </div> <p align="center"><font color="#c0504d" size="6"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUTq3yWPr_ddrnsPUP2uDNVOY8B1yyXpa-YqxCbP85TpuHxKfNeZAkw-GN2mf2xmvCrqYqEWP2TxJHGhB8BL1wMukK0MZyAAHNXOk7LYXVLyCcnSIrRIWG-0mEggxxtC9aqFbQaB3DHc8/s1600-h/ProgrammerFail%25255B3%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="ProgrammerFail" border="0" alt="ProgrammerFail" src="http://lh3.ggpht.com/-NyZG8ciTTiw/TyJw56CtzuI/AAAAAAAAAIU/FIa2pfQsGsM/ProgrammerFail_thumb.jpg?imgmax=800" width="244" height="205"></a></font></p> <p align="center"><font color="#c0504d" size="6">!!!FAIL!!!</font></p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-89537878558417386682012-01-10T18:38:00.001+01:002012-01-10T18:41:10.161+01:00Why is it good to read CodeProject newsletter? Because it is good to read about query optimalization!<p> </p> <p>I have found <a href="http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-query-optimizer-ebook3" target="_blank">this</a> in the <a href="http://www.codeproject.com/script/Mailouts/View.aspx?mlid=9446" target="_blank">newsletter</a>.</p> <p><a href="http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-query-optimizer-ebook3" target="_blank">It is a free e-book about the query optimizer of Sql Server in 265 pages.</a></p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-29508646146747459492012-01-09T13:34:00.001+01:002012-01-09T22:32:42.128+01:00Sql Server Management Studio tuning: T-Sql Formatter and SSMS Tools Pack<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
I think Microsoft Sql Server Management Studio is a great tool for managing Sql Server, writing and optimizing Transact Sql code and for anything with Sql Server but I have always missed some functions:<br />
<ul>
<li>Formatting Sql code </li>
<li>Code Snippets (Like in Visual Studio) </li>
<li>CRUD Stored Procedure generation</li>
</ul>
I had to reinstall my working machine last week and started searching for some free Sql Server Management Studio addins for these functions and finally I found a solution:<br />
<a href="http://architectshack.com/PoorMansTSqlFormatter.ashx">Poor Man’s T-SQL Formatter for formatting Sql code.</a><br />
<a href="http://www.ssmstoolspack.com/">SSMS Tools Pack for the others.</a><br />
<br />
Poor Man’s T-SQL Formatter is a free add in that formats Sql scripts with a single hotkey. As Visual Studio, we can format the whole document or just the selected part of it.<br />After the installation it puts two entries into the SSMS Tools menu: Format T-SQL Code and T-SQL Formatting Options.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4pYNciMY8tT3BJWHxMMqW_ZE4ck7o1rCPmOouuSQyTV5wCLAQU8tauhXlEhDm4wmUgf9HSyMID8-P74kVxgRyQcPacyEi4ZD1EkfbJU8GN23Jok5vmCYeax6mstapr_htojQYNQ0C5dg/s1600-h/PoorMansTSqlFormatterOptions%25255B4%25255D.png"><img alt="PoorMansTSqlFormatterOptions" border="0" height="192" src="http://lh4.ggpht.com/-dzpZvgPwHzM/Twreu9oGrRI/AAAAAAAAAIM/fHsnMXc-kG8/PoorMansTSqlFormatterOptions_thumb%25255B2%25255D.png?imgmax=800" style="background-image: none; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="PoorMansTSqlFormatterOptions" width="244" /></a><br />These are my personal settings. I have changed the default hotkey to CTRL+K, CTRL+D and unchecked all the extras, because the default settings were uncomfortable for me (This hotkey is the same as the debugging hotkey in SSMS Tools Pack but I disabled this function because I think I wont use it).<br />It also has a free web service for testing at <a href="http://poorsql.com/">poorsql.com</a>.<br />
SSMS Tools Pack is also a free add-in created by <a href="http://weblogs.sqlteam.com/mladenp/">Mladen Prajdić</a> SQL Server MVP. It has a lot of features but for me the most importants are:<br />
<ul>
<li><strong>Execution Plan Analyzer!!!!!!! (Verry important)</strong><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvp0n8BBiNDq7EZrYB8qnXzQH8BMbcn1EeIJ7dcqlvGU1mL2uE12YXOMg6c8N_gxN1yUNO-ZrpVmu4o1zWsIBKRxD1wDA-tuKTh9Ut8tsqd2XnkNNjfketdpmfJYNvYyp65GufPBMon3U/s1600-h/SSMSToolsPack_QueryPlanAnalyzer%25255B3%25255D.png"><img alt="SSMSToolsPack_QueryPlanAnalyzer" border="0" height="239" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwwSNtDJl1bkEB3eP81frdnP0OwtVZYI5i1_D3eVygTTtXpsr8cbN7HU5C5VZ-yHzMvPsS2aFdz-XUhq6JPo67c-mdG1KLVsAkmH2Ofd0Ygxwxcbsxp6OJBxoFUqAeTl3gvieUP4Xn0II/?imgmax=800" style="background-image: none; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="SSMSToolsPack_QueryPlanAnalyzer" width="644" /></a> <br /> </li>
<li><strong>SQL Snippets</strong> (Fires on Enter – as default – or Tab key)<br /><a href="http://lh3.ggpht.com/-kjAazN_6yn4/TwrewvhKs_I/AAAAAAAAAHM/vTobde6qL9E/s1600-h/SSMSToolsPack_Snippets%25255B3%25255D.png"><img alt="SSMSToolsPack_Snippets" border="0" height="484" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5OqLNnsaZ_5SJaFWXVdppeg-CbcPSutSr8AsdIBtyIBnyFtzHxa3gRj0OTAh0U1jI65aHnGxFDoCSvsq38PIR-4F3BhsJRY4yPBqSjMsZ8Duj6CQXbVtpX1aFP-PhxfC_F5dr9oSLxP4/?imgmax=800" style="background-image: none; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="SSMSToolsPack_Snippets" width="563" /></a> <br /> </li>
<li><strong>Search Results in Grid Mode</strong><br /><a href="http://lh4.ggpht.com/--kQ0x15Wv54/Twrex0xGJOI/AAAAAAAAAHc/8Qem3rIjGHk/s1600-h/SSMSToolsPack_SearchGridResults%25255B3%25255D.png"><img alt="SSMSToolsPack_SearchGridResults" border="0" height="238" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgN4CO-7lIC5aOtiYDxc3Wo1PiyygaewdAF5jpe3jFVvgxlGbJ1Sn6TI2e_SMHZJOQQA9NBXFXtz46CYsicxy8lC7-NjtlRiKCF27DOo3HMFhghiec-ZQ5C2fZrv0SBK4qew50BzTOTKYs/?imgmax=800" style="background-image: none; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="SSMSToolsPack_SearchGridResults" width="644" /></a> <br /> </li>
<li><strong>CRUD</strong> (Create, Read, Update, Delete) <strong>stored procedure generation</strong><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiozv_JCLdKQZh_EH_Vi8_Xa3xvlzqk2IsePpgjkRfOpP8QKewZiuESS0bZx0EPJo-XuCKYQntAgtqAQWPfl_N16-uNR-6jfHNGKoZd3VnWm4SsIkI-tHI8qNQ1U3iKGjRYHoTTDQje53c/s1600-h/SSMSToolsPack_CRUDGeneration%25255B3%25255D.png"><img alt="SSMSToolsPack_CRUDGeneration" border="0" height="484" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAx9fYFuvGQRlF3qW8ek5m2bA6RtrcD1ls9V6SF2htGBkp2AAvGrMvEu4BqEuZRgZmhF_HrcuF0UGSPFfhEFapgb_6Uc5cGzNSgkSab62Dk3IsFec_90m1CVUK-H-zVqj678AG-V6G2rk/?imgmax=800" style="background-image: none; border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="SSMSToolsPack_CRUDGeneration" width="559" /></a><br /><br /> </li>
<li><strong><a href="http://www.ssmstoolspack.com/Features">And a lot of other good stuff</a> <img alt="Mosolygó arc" class="wlEmoticon wlEmoticon-smile" src="http://lh3.ggpht.com/-uBNuTpUp_OI/Twre0SFXWAI/AAAAAAAAAH8/XUWYCSV_jCI/wlEmoticon-smile%25255B2%25255D.png?imgmax=800" style="border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none;" />.</strong></li>
</ul>
I also have to mention the Format SQL feature that I think is a Fake Function. It only makes keywords uppercase or lowercase, doesn’t format anything in my code.<br />
<br />
Summary: I recommend installing these to everybody who uses SQL Server. SSMS Tools Pack works with every version of SQL Server from SQL Server 2005 (as the install shows), and unfortunately I don’t have any information about Poor Mans T-SQL Formatter but I tried it with SQL Server 2008 and it worked fine for me.<br />
<strong>Note: EVERYBODY has to try the<em> <u>Execution Plan Analyzer</u></em>, it is really-really worth it.</strong><br />
<br />
The download links are:<br />
<ul>
<li><a href="http://architectshack.com/PoorMansTSqlFormatter.ashx#Download_8">Poor Man’s T-SQL Formatter.</a> </li>
<li><a href="http://www.ssmstoolspack.com/Download">SSMS Tools Pack.</a></li>
</ul>
<br />
Happy Coding!<br />
Morzel</div>Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com1tag:blogger.com,1999:blog-8012614073429928775.post-63866444346588816262012-01-03T11:13:00.001+01:002012-01-03T11:13:12.062+01:00Server Side Dynamic TreeView in Asp.Net<p> </p> <p>In most cases loading large TreeView in one step can be a good performance killer. The generated html is really large and our page is loading so slow at the client and the average user don’t want to see the full tree together.</p> <p>Let see, what needs for loading dynamically an asp.net server control on the server side.</p> <ul> <li>We need some data (In that case that’s will the ProductCategory table in the <a href="http://msftdbprodsamples.codeplex.com/">AdventureWorks</a> sample database).</li> <li>We need a method to determine if the given node has childs or not.</li> <li>We need a <a href="http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.treeview(v=VS.90).aspx">TreeView</a> <img style="border-bottom-style: none; border-left-style: none; border-top-style: none; border-right-style: none" class="wlEmoticon wlEmoticon-smile" alt="Mosolygó arc" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDyddGozpavo_zfKyWAO6LvnMHPQlRkiYGw7VlTQ_7JdpJdViE2p7nwRXrfoVFAdboMnLC6cUtpZKJ3Xj3OuPAVw1FH30lgpNF5TCxG4TMSdTSuYIO8mSkS4GnPgU7prp-AUCeRS-IQEI/?imgmax=800">.</li> <li>First step:</li> <ul> <li>We have to load the first level nodes of the TreeView on the first page load. </li> <li>Handle the <a href="http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.treeview.treenodepopulate(v=VS.90).aspx">OnTreeNodePopulate</a> event if the childnode has more childs on the next level.</li> <li>Set the nodes expanded property to false. The TreeNodePopulate event will be fired when we click on a node. The default value is true and the tree is expanded.</li></ul> <li>Repeat the previous step on every TreeNodePopulate event.</li></ul> <p> </p> <p>The ProductCategory table contains the following records: <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiy4PkSwe6V0Mf3CEqYuctUyYShBM1_Q050vqXFCzET42mDn-if_eWEyr2khRLDGeaGBf7YtSHa_JuSDF2JaA3lhhfC4jwNWN4oT0RvB6t7QRMN2nDQjQClv206UjdFnQPMUWse3wrx62k/s1600-h/ProductCategoriesTable%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="ProductCategoriesTable" border="0" alt="ProductCategoriesTable" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRP073vP85dUyVzYuHrsOk988Z1SMhA7Iw6kZXx_FCnB9g60M6W7mFkSgkrkoqsH24y_1PHFyddBSp2DTo9Kfl0IcCYc9q6cybhieyKW-QdJrD8SSpCclBDs-axVJ7ZmpDkgOrdkEvLPo/?imgmax=800" width="438" height="484"></a><br>The value of ParentProductCategoryID is null at the root elements and the ProductCategoryID of the parent element at the others.</p> <p> </p> <p>We need a method that queries the list of the child nodes for a given node (DAO\CategoryDAO.cs): <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:5a09ccbd-4ede-446f-8b1b-d3cce6b18622" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; max-height: 500px; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#0000ff">public</span> <span style="color:#0000ff">static</span> <span style="color:#2b91af">List</span><<span style="color:#2b91af">ProductCategory</span>> GetCategoriesByParentID(<span style="color:#0000ff">int</span>? parentID)<br> {<br> <span style="color:#0000ff">using</span> (<span style="color:#2b91af">AdventureWorksLT2008Entities</span> ctx = <span style="color:#0000ff">new</span> <span style="color:#2b91af">AdventureWorksLT2008Entities</span>())<br> {<br> <span style="color:#0000ff">return</span> ctx.ProductCategory.Where(cat => cat.ParentProductCategoryID == parentID || (<span style="color:#0000ff">null</span> == parentID && cat.ParentProductCategoryID == <span style="color:#0000ff">null</span>)).ToList();<br> }<br> }</div> </div> </div></p> <p> </p> <p>Add a TreeView for the page (Default.aspx): <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:f84987f6-62a0-4179-9e6c-919e3a3a2b36" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">TreeView</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="tvCategories"</span><br> <span style="color:#ff0000">OnTreeNodePopulate</span><span style="color:#0000ff">="tvCategoris_TreeNodePopulate"></</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">TreeView</span><span style="color:#0000ff">></span></div> </div> </div></p> <p> </p> <p>Make an AddChildNodes method for populate the child nodes of the given node (Default.aspx.cs): <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:794d5d65-a817-4ddb-9c44-833d40c49fe3" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#0000ff">protected</span> <span style="color:#0000ff">void</span> AddChildNodes(<span style="color:#2b91af">TreeNodeCollection</span> nodes, <span style="color:#2b91af">TreeNode</span> node)<br> {<br> <span style="color:#2b91af">List</span><<span style="color:#2b91af">ProductCategory</span>> categories = <span style="color:#0000ff">null</span>;<br> <span style="color:#0000ff">int</span> parentID = -1;<br> <br> <span style="color:#0000ff">if</span> (<span style="color:#0000ff">null</span> != node && <span style="color:#0000ff">int</span>.TryParse(node.Value, <span style="color:#0000ff">out</span> parentID))<br> categories = <span style="color:#2b91af">CategoryDAO</span>.GetCategoriesByParentID(parentID);<br> <span style="color:#0000ff">else</span><br> categories = <span style="color:#2b91af">CategoryDAO</span>.GetCategoriesByParentID(<span style="color:#0000ff">null</span>);<br> <br> <span style="color:#0000ff">foreach</span> (<span style="color:#0000ff">var</span> cat <span style="color:#0000ff">in</span> categories)<br> {<br> <span style="color:#2b91af">TreeNode</span> newNode = <span style="color:#0000ff">new</span> <span style="color:#2b91af">TreeNode</span>();<br> newNode.Value = cat.ProductCategoryID.ToString();<br> newNode.Text = cat.Name;<br> newNode.PopulateOnDemand = <span style="color:#0000ff">null</span> == node;<br> newNode.Expanded = <span style="color:#0000ff">false</span>;<br> <br> nodes.Add(newNode);<br> }<br> }</div> </div> </div><br>If the new node is leaf node, we have to set the value of the <a href="http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.treenode.populateondemand(v=VS.90).aspx">PopulateOnDemand</a> property to false.</p> <p> </p> <p>Load the root elements on the first page load (Default.aspx.cs): <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:e1b514b8-7f12-4189-b934-712065d94819" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#0000ff">protected</span> <span style="color:#0000ff">void</span> Page_Load(<span style="color:#0000ff">object</span> sender, <span style="color:#2b91af">EventArgs</span> e)<br> {<br> <span style="color:#0000ff">if</span> (!IsPostBack)<br> AddChildNodes(tvCategories.Nodes, <span style="color:#0000ff">null</span>);<br> }</div> </div> </div></p> <p> </p> <p>Create an event handler for the TreeNodePopulate event (Default.aspx.cs): <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:a29527d6-087e-4c03-934c-6a21bb69f698" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#0000ff">protected</span> <span style="color:#0000ff">void</span> tvCategoris_TreeNodePopulate(<span style="color:#0000ff">object</span> sender, <span style="color:#2b91af">TreeNodeEventArgs</span> e)<br> {<br> <span style="color:#0000ff">if</span> (e.Node.ChildNodes.Count == 0)<br> {<br> AddChildNodes(e.Node.ChildNodes, e.Node);<br> }<br> }</div> </div> </div></p> <p> </p> <p>The full project is downloadable from <a href="https://skydrive.live.com/?cid=4bd24c1cd19d9f89&id=4BD24C1CD19D9F89%21217">my Skydrive public folder</a>.</p> <p> </p> <p>Happy and Bug free coding!</p> <p>Morzel</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com1tag:blogger.com,1999:blog-8012614073429928775.post-62717583143043806862011-11-14T15:41:00.001+01:002011-11-14T15:48:02.594+01:00Dimensions Of A Series Paper Sizes<p> </p> <p>There is a short table of the paper sizes of A series:</p> <table border="1" cellspacing="0" cellpadding="5" width="343"> <tbody> <tr> <td width="55"><strong>Size</strong></td> <td width="146"><strong>Height x Width (mm)</strong></td> <td width="140"><strong>Height x Width (in</strong>)</td></tr> <tr> <td width="62">4A0</td> <td width="143">2378 x 1682 mm</td> <td width="138">93.6 x 66.2 in</td></tr> <tr> <td width="67">2A0</td> <td width="141">1682 x 1189 mm</td> <td width="136">66.2 x 46.8 in</td></tr> <tr> <td width="70">A0</td> <td width="140">1189 x 841 mm</td> <td width="135">46.8 x 33.1 in</td></tr> <tr> <td width="72">A1</td> <td width="140">841 x 594 mm</td> <td width="135">33.1 x 23.4 in</td></tr> <tr> <td width="73">A2</td> <td width="140">594 x 420 mm</td> <td width="134">23.4 x 16.5 in</td></tr> <tr> <td width="74">A3</td> <td width="139">420 x 297 mm</td> <td width="134">16.5 x 11.7 in</td></tr> <tr> <td width="75">A4</td> <td width="139">297 x 210 mm</td> <td width="134">11.7 x 8.3 in</td></tr> <tr> <td width="75">A5</td> <td width="139">210 x 148 mm</td> <td width="134">8.3 x 5.8 in</td></tr> <tr> <td width="75">A6</td> <td width="139">148 x 105 mm</td> <td width="134">5.8 x 4.1 in</td></tr> <tr> <td width="75">A7</td> <td width="139">105 x 74 mm</td> <td width="134">4.1 x. 2.9 in</td></tr> <tr> <td width="75">A8</td> <td width="139">74 x 52 mm</td> <td width="134">2.9 x 2.0 in</td></tr> <tr> <td width="75">A9</td> <td width="139">52 x 37 mm</td> <td width="134">2.0 x 1.5 in</td></tr> <tr> <td width="75">A10</td> <td width="139">37 x 26 mm</td> <td width="137">1.5 x 1.0 in</td></tr></tbody></table> <p>I found it <a href="http://www.papersizes.org/a-paper-sizes.htm">there</a>.</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-81582525520330853252011-09-27T11:30:00.001+02:002011-09-27T11:30:40.102+02:00Where is the Ajax Control Toolkit Reference?<p> </p> <p>It is so easy to find. </p> <p>Just go to the <a href="http://www.asp.net/ajaxlibrary/AjaxControlToolkitSampleSite/">sample site</a> (every google search points there), and:</p> <p>Click on <a href="http://www.asp.net/ajaxlibrary/act.ashx">Getting Started</a> tutorial.</p> <p>Click on <a href="http://www.asp.net/ajaxlibrary/act_tutorials.ashx">Tutorials</a>.</p> <p> </p> <p>And you will find the reference of each controls. And you will see the full reference on server and client side properties, events and methods. </p> <p><a href="http://www.asp.net/ajaxlibrary/act_tutorials.ashx">It is easy to find, not?</a></p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-60526533612185121172011-09-27T11:02:00.001+02:002011-09-27T11:02:08.376+02:00Not every man should be a computer programmer<p> </p> <p>The question is only that: Why???</p> <p> </p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:ae3618f2-7a65-4c06-b557-2531592c6411" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background: #000080; color: #fff; font-family: Verdana, Tahoma, Arial, sans-serif; font-weight: bold; padding: 2px 5px">Code Snippet</div> <div style="background: #ddd; max-height: 300px; overflow: auto"> <ol style="background: #ffffff; margin: 0 0 0 2em; padding: 0 0 0 5px;"> <li><span style="color:#0000ff">catch</span> (<span style="color:#2b91af">Exception</span> ex)</li> <li style="background: #f3f3f3">{</li> <li> <span style="color:#0000ff">if</span> ((ex.GetType().ToString() == <span style="color:#a31515">"System.Data.SqlClient.SqlException"</span>) && ex.Message.LastIndexOf(<span style="color:#a31515">"Timeout expired"</span>) >= 0)</li> <li style="background: #f3f3f3"> {</li> <li> <span style="color:#808080">///</span><span style="color:#008000"> Secret code</span></li> <li style="background: #f3f3f3"> }</li> <li> <span style="color:#0000ff">else</span></li> <li style="background: #f3f3f3"> <span style="color:#0000ff">throw</span> ex;</li> <li>}</li> </ol> </div> </div> </div> <p>Why the hell he doesn’t go and play football instead of programming?</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0tag:blogger.com,1999:blog-8012614073429928775.post-51413409973460152452011-09-20T10:54:00.001+02:002011-09-20T10:54:48.345+02:00Fed up with windows<p> </p> <p>The point is arrived. Enough. I am fed up with windows.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisX_VdqEYs9JH5sLwehN74EDaC0MPtDCZpaD_-pGneDU5jQhZqxy1I3ulWCuWYhmdZTXPlGxYB3IIH9fna3CtYpPPYrJ0vfbI4MbIPL4-SGCxgTHTXlyVwyrCMk_uFvu5EeX3rrVDTsQs/s1600-h/20110920197%25255B6%25255D.jpg"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="20110920197" border="0" alt="20110920197" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDr3Wh9OlSw_wC8wUokst0LtdTrBxS05dx_RTe8FlPHKNztJZ3mnBeS0jL3jJJHEvciZu1MZqQSKcEQHyI1MFdQd6BY6Pvk6ficdaKoTKj3Uz0tyJJ5b3LNgs_o9_Ce0nJh0jSiCM67wk/?imgmax=800" width="644" height="363"></a></p> <p>But I hope, it will comes back.</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com1tag:blogger.com,1999:blog-8012614073429928775.post-10446800589582757792011-02-07T15:41:00.001+01:002011-02-07T15:41:32.006+01:00Generate RDLC dynamically with unique column headers<p>I use the great example from <a href="http://gotreportviewer.com/">Gotreportviewer</a> since about six month that uses Microsoft Reportviewer and my biggest problem is giving columns titles that different to field names.</p> <p>There is my solution that has an additional property: <font face="Courier New">List<string> Headers</font> that contains column titles with the following restriction:</p> <p>Number of headers must be equal to number of columns or <font face="Courier New">TableRdlGenerator</font> throws the following exception:<br><font face="Courier New">Error creating report: header and field columns must be set and have to be the same count of elements both of them!<br></font></p> <p>The entire solution can be downloaded from <a href="http://cid-4bd24c1cd19d9f89.office.live.com/self.aspx/Public/DynamicTable.zip">my public folder on skydrive</a>.</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com2tag:blogger.com,1999:blog-8012614073429928775.post-84106285469158137852011-02-06T14:48:00.001+01:002011-02-06T15:32:43.289+01:00jQuery based collapse extender web user control for asp.net<p>After I have been started to learn jquery I decided to replace my <a href="http://www.asp.net/ajax/ajaxcontroltoolkit/samples/">Ajax Control Toolkit</a> controls for a jQuery-based solution.</p> <p>My control for replace in that week is the <a href="http://www.asp.net/ajax/ajaxcontroltoolkit/Samples/CollapsiblePanel/CollapsiblePanel.aspx">CollapsiblePanelExtender</a>. I need a solution that works on partial page postbacks and full page postbacks too and stores collapsed state during postbacks, and, of course I have to be able to change the collapse state on server side so I had to mix server and client side code for the solution.</p> <p><strong>Note</strong>: at the end of the post, I share the zipped full Visual Studio 2010 solution.</p> <p>The extender needs the following informations:</p> <ul> <ul> <li>Collapsed: The state if the target control collapsed or not.</li> <li>TargetControlID: The ID of the panel that will be collapsed.</li> <li>ToggleControlID: The ID of the control that we will click on when toggle or collapse.</li> <li>ToggleTextID: The ID of the control that shows different texts on different states.</li> <li>ToggleImageID: The ID of the control that show different images on different states.</li> <li>ExpandedText: Text to show when the control is expanded.</li> <li>CollapsedText: Text to show when the control is collapsed.</li> <li>ExpandedImage: Image to show when the control is expanded.</li> <li>CollapsedImage: Image to show ehen the control is collapsed.</li></ul></ul> <p>The code behind of the web user control:</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:2b87f263-a1fe-4218-a562-d61dcaddc50f" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#0000ff">using</span> System;<br> <span style="color:#0000ff">using</span> System.Collections.Generic;<br> <span style="color:#0000ff">using</span> System.Linq;<br> <span style="color:#0000ff">using</span> System.Web;<br> <span style="color:#0000ff">using</span> System.Web.UI;<br> <span style="color:#0000ff">using</span> System.Web.UI.WebControls;<br> <br> <span style="color:#0000ff">namespace</span> JQueryCollapse<br> {<br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">partial</span> <span style="color:#0000ff">class</span> <span style="color:#2b91af">jQueryToggleExtender</span> : System.Web.UI.<span style="color:#2b91af">UserControl</span><br> {<br> <span style="color:#0000ff">#region</span> properties<br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> Defines if the panel to collapse is collapsed or not.</span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">bool</span> Collapsed<br> {<br> <span style="color:#0000ff">get</span><br> {<br> <span style="color:#0000ff">return</span> hfCollapsed.Value == <span style="color:#a31515">"1"</span> ? <span style="color:#0000ff">true</span> : <span style="color:#0000ff">false</span>;<br> }<br> <span style="color:#0000ff">set</span> { hfCollapsed.Value = (<span style="color:#0000ff">value</span> ? 1 : 0).ToString(); }<br> }<br> <br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">string</span> _TargetControlID;<br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> A conntrol to collapse and expand</span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">string</span> TargetControlID<br> {<br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">get</span><br> {<br> <span style="color:#0000ff">return</span> GetClientID(_TargetControlID);<br> }<br> <span style="color:#0000ff">set</span><br> {<br> _TargetControlID = <span style="color:#0000ff">value</span>;<br> }<br> }<br> <br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">string</span> _ToggleControlID;<br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> A control to toggle.</span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">string</span> ToggleControlID<br> {<br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">get</span><br> {<br> <span style="color:#0000ff">return</span> GetClientID(_ToggleControlID);<br> }<br> <span style="color:#0000ff">set</span><br> {<br> _ToggleControlID = <span style="color:#0000ff">value</span>;<br> }<br> }<br> <br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">string</span> _ToggleTextID;<br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> ID for toggle text control.</span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">string</span> ToggleTextID<br> {<br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">get</span><br> {<br> <span style="color:#0000ff">return</span> GetClientID(_ToggleTextID);<br> }<br> <span style="color:#0000ff">set</span><br> {<br> _ToggleTextID = <span style="color:#0000ff">value</span>;<br> }<br> }<br> <br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">string</span> _ToggleImageID;<br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> ID for toggle image control</span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">string</span> ToggleImageID<br> {<br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">get</span><br> {<br> <span style="color:#0000ff">return</span> GetClientID(_ToggleImageID);<br> }<br> <span style="color:#0000ff">set</span><br> {<br> _ToggleImageID = <span style="color:#0000ff">value</span>;<br> }<br> }<br> <br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> Text to show when expanded</span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">string</span> ExpandedText { <span style="color:#0000ff">get</span>; <span style="color:#0000ff">set</span>; }<br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> Text to show when collapsed</span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">string</span> CollapsedText { <span style="color:#0000ff">get</span>; <span style="color:#0000ff">set</span>; }<br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> Image to show when expanded</span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">string</span> ExpandedImage { <span style="color:#0000ff">get</span>; <span style="color:#0000ff">set</span>; }<br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> Image to show when collapsed</span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">string</span> CollapsedImage { <span style="color:#0000ff">get</span>; <span style="color:#0000ff">set</span>; }<br> <span style="color:#0000ff">#endregion</span><br> <br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> Get a ClientID for a control by ID</span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><param name="id"></span><span style="color:#008000">Id of the control</span><span style="color:#808080"></param></span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><returns></returns></span><br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">string</span> GetClientID(<span style="color:#0000ff">string</span> id)<br> {<br> <span style="color:#2b91af">Control</span> c = <span style="color:#2b91af">ControlUtils</span>.FindControlRecursive(<span style="color:#0000ff">this</span>.Page, id);<br> <br> <span style="color:#0000ff">return</span> <span style="color:#0000ff">null</span> != c ? c.ClientID : <span style="color:#0000ff">null</span>;<br> }<br> }<br> }</div> </div> </div> <p>In the markup we need to include our javascript files. For getting this work on partial page and full page postbacks and any other cases we rendering script block in InlineScripts as shown in my <a href="http://zsoltmiskolczi.blogspot.com/2011/01/aspnet-web-server-control-for-rendering.html">previous post</a> before. We store collapsed state in a simple asp.net hiddenfield because we can reach it’s value on server and client side too.<br>Based on these the markup for the web user control is:</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:246e190e-d066-4d33-bb15-9642d3e70301" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="background:#ffff00"><%</span><span style="color:#0000ff">@</span> <span style="color:#800000">Control</span> <span style="color:#ff0000">Language</span><span style="color:#0000ff">="C#"</span> <span style="color:#ff0000">AutoEventWireup</span><span style="color:#0000ff">="true"</span> <span style="color:#ff0000">CodeBehind</span><span style="color:#0000ff">="jQueryToggleExtender.ascx.cs"</span><span style="color:#ff0000">Inherits</span><span style="color:#0000ff">="JQueryCollapse.jQueryToggleExtender"</span> <span style="background:#ffff00">%></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">ScriptManagerProxy</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="smp"></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">Scripts</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">ScriptReference</span> <span style="color:#ff0000">Path</span><span style="color:#0000ff">="~/js/jquery-1.4.4.min.js"</span> <span style="color:#0000ff">/></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">ScriptReference</span> <span style="color:#ff0000">Path</span><span style="color:#0000ff">="~/js/jquery-ui-1.8.8.custom.min.js"</span> <span style="color:#0000ff">/></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">Scripts</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">ScriptManagerProxy</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">wc</span><span style="color:#0000ff">:</span><span style="color:#800000">InlineScript</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="inlineScripts"></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">script</span> <span style="color:#ff0000">type</span><span style="color:#0000ff">="text/javascript"></span><br> <span style="color:#0000ff">function</span> pageLoad() {<br> <span style="color:#0000ff">var</span> collapsed = $get(<span style="color:#800000">'</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= hfCollapsed.ClientID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>);<br> <br> $(<span style="color:#800000">'#</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ToggleControlID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).click(<br> <span style="color:#0000ff">function</span> () {<br> <span style="color:#0000ff">if</span> (collapsed.value == 1) {<br> $(<span style="color:#800000">'#</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ToggleImageID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).attr(<span style="color:#800000">'src'</span>, <span style="color:#800000">'</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ExpandedImage </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>);<br> $(<span style="color:#800000">'#</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ToggleTextID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).text(<span style="color:#800000">'</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ExpandedText </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>);<br> }<br> <span style="color:#0000ff">else</span> {<br> $(<span style="color:#800000">'#</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ToggleImageID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).attr(<span style="color:#800000">'src'</span>, <span style="color:#800000">'</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= CollapsedImage </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>);<br> $(<span style="color:#800000">'#</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ToggleTextID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).text(<span style="color:#800000">'</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= CollapsedText </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>);<br> }<br> <br> $(<span style="color:#800000">'#</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= TargetControlID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).toggle(<span style="color:#800000">'blind'</span>, {}, 500);<br> collapsed.value = 1 - collapsed.value;<br> });<br> <br> <span style="color:#0000ff">if</span> (collapsed.value == 1) {<br> $(<span style="color:#800000">'#</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= TargetControlID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).hide();<br> $(<span style="color:#800000">'#</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ToggleTextID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).text(<span style="color:#800000">'</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= CollapsedText </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).html();<br> $(<span style="color:#800000">'#</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ToggleImageID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).attr(<span style="color:#800000">'src'</span>, <span style="color:#800000">'</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= CollapsedImage </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>);<br> } <span style="color:#0000ff">else</span> {<br> $(<span style="color:#800000">'#</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ToggleTextID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).text(<span style="color:#800000">'</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ExpandedText </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).html();<br> $(<span style="color:#800000">'#</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ToggleImageID </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>).attr(<span style="color:#800000">'src'</span>, <span style="color:#800000">'</span><span style="background:#ffff00;color:#800000"><%</span><span style="color:#800000">= ExpandedImage </span><span style="background:#ffff00;color:#800000">%></span><span style="color:#800000">'</span>);<br> }<br> }<br> <span style="color:#0000ff"></</span><span style="color:#800000">script</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">wc</span><span style="color:#0000ff">:</span><span style="color:#800000">InlineScript</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">HiddenField</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="hfCollapsed"</span> <span style="color:#0000ff">/></span></div> </div> </div> <p>We have to register our control in the web.config before using:</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:a8180b3f-9805-4bda-81dd-e57e72fdde82" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#0000ff"><?</span><span style="color:#a31515">xml</span><span style="color:#0000ff"> </span><span style="color:#ff0000">version</span><span style="color:#0000ff">=</span>"<span style="color:#0000ff">1.0</span>"<span style="color:#0000ff">?></span><br> <br> <span style="color:#0000ff"><!--</span><br> <span style="color:#008000">For more information on how to configure your ASP.NET application, please visit</span><br> <span style="color:#008000">http://go.microsoft.com/fwlink/?LinkId=169433</span><br> <span style="color:#008000"></span><span style="color:#0000ff">--></span><br> <br> <span style="color:#0000ff"><</span><span style="color:#a31515">configuration</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#a31515">system.web</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#a31515">pages</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#a31515">controls</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><!--</span><span style="color:#008000">Registering inline script</span><span style="color:#0000ff">--></span><br> <span style="color:#0000ff"><</span><span style="color:#a31515">add</span><span style="color:#0000ff"> </span><span style="color:#ff0000">tagPrefix</span><span style="color:#0000ff">=</span>"<span style="color:#0000ff">wc</span>"<span style="color:#0000ff"> </span><span style="color:#ff0000">assembly</span><span style="color:#0000ff">=</span>"<span style="color:#0000ff">JQueryCollapse</span>"<span style="color:#0000ff"> </span><span style="color:#ff0000">namespace</span><span style="color:#0000ff">=</span>"<span style="color:#0000ff">JQueryCollapse.WebServerControls</span>"<span style="color:#0000ff"> /></span><br> <span style="color:#0000ff"><!--</span><span style="color:#008000">Registering toggle extender</span><span style="color:#0000ff">--></span><br> <span style="color:#0000ff"><</span><span style="color:#a31515">add</span><span style="color:#0000ff"> </span><span style="color:#ff0000">tagPrefix</span><span style="color:#0000ff">=</span>"<span style="color:#0000ff">wuc</span>"<span style="color:#0000ff"> </span><span style="color:#ff0000">tagName</span><span style="color:#0000ff">=</span>"<span style="color:#0000ff">Toggle</span>"<span style="color:#0000ff"> </span><span style="color:#ff0000">src</span><span style="color:#0000ff">=</span>"<span style="color:#0000ff">~/WebUserControls/jQueryToggleExtender/jQueryToggleExtender.ascx</span>"<span style="color:#0000ff"> /></span><br> <span style="color:#0000ff"></</span><span style="color:#a31515">controls</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#a31515">pages</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#a31515">compilation</span><span style="color:#0000ff"> </span><span style="color:#ff0000">debug</span><span style="color:#0000ff">=</span>"<span style="color:#0000ff">true</span>"<span style="color:#0000ff"> </span><span style="color:#ff0000">targetFramework</span><span style="color:#0000ff">=</span>"<span style="color:#0000ff">4.0</span>"<span style="color:#0000ff"> /></span><br> <span style="color:#0000ff"></</span><span style="color:#a31515">system.web</span><span style="color:#0000ff">></span><br> <br> <span style="color:#0000ff"></</span><span style="color:#a31515">configuration</span><span style="color:#0000ff">></span></div> </div> </div> <p>A simple example page for testing:</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:16da8821-677d-43c6-93e9-f3347d90ef4e" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="background:#ffff00"><%</span><span style="color:#0000ff">@</span> <span style="color:#800000">Page</span> <span style="color:#ff0000">Language</span><span style="color:#0000ff">="C#"</span> <span style="color:#ff0000">AutoEventWireup</span><span style="color:#0000ff">="true"</span> <span style="color:#ff0000">CodeBehind</span><span style="color:#0000ff">="Default.aspx.cs"</span> <span style="color:#ff0000">Inherits</span><span style="color:#0000ff">="JQueryCollapse.Default"</span> <span style="background:#ffff00">%></span><br> <br> <span style="color:#0000ff"><!</span><span style="color:#800000">DOCTYPE</span> <span style="color:#ff0000">html</span> <span style="color:#ff0000">PUBLIC</span> <span style="color:#0000ff">"-//W3C//DTD XHTML 1.0 Transitional//EN"</span> <span style="color:#0000ff">"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">html</span> <span style="color:#ff0000">xmlns</span><span style="color:#0000ff">="http://www.w3.org/1999/xhtml"></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">head</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">title</span><span style="color:#0000ff">></span>jquery toggle for asp.net<span style="color:#0000ff"></</span><span style="color:#800000">title</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">head</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">body</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">form</span> <span style="color:#ff0000">id</span><span style="color:#0000ff">="form1"</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">ScriptManager</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="ScriptManager1"</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">ScriptManager</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">UpdatePanel</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="upMain"></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">ContentTemplate</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">Button</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="btnClickMe"</span> <span style="color:#ff0000">Text</span><span style="color:#0000ff">="ClickMe"</span> <span style="color:#0000ff">/></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">a</span> <span style="color:#ff0000">id</span><span style="color:#0000ff">="hrefToggle"</span> <span style="color:#ff0000">href</span><span style="color:#0000ff">="#"</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">Panel</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="pnToggle"</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">Width</span><span style="color:#0000ff">="100%"</span> <span style="color:#ff0000">BackColor</span><span style="color:#0000ff">="Azure"></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">ImageButton</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="imgCollapse"</span> <span style="color:#ff0000">AlternateText</span><span style="color:#0000ff">="Click here to collapse or expand"</span> <span style="color:#0000ff">/></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">Label</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="lblCollapse"></</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">Label</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">Panel</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">a</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">Panel</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="pnContent"></span><br> Hello<br> <span style="color:#0000ff"></</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">Panel</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">ContentTemplate</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">UpdatePanel</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">Button</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="btnPb"</span> <span style="color:#ff0000">Text</span><span style="color:#0000ff">="Postback"</span> <span style="color:#0000ff">/></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">br</span> <span style="color:#0000ff">/></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">asp</span><span style="color:#0000ff">:</span><span style="color:#800000">Button</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="btnCollapse"</span> <span style="color:#ff0000">OnClick</span><span style="color:#0000ff">="btnCollapse_Click"</span> <span style="color:#ff0000">Text</span><span style="color:#0000ff">="Toggle"</span> <span style="color:#0000ff">/></span><br> <span style="background:#ffff00"><%</span><span style="color:#006400">--ToggleControlExtender--</span><span style="background:#ffff00">%></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">wuc</span><span style="color:#0000ff">:</span><span style="color:#800000">Toggle</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="tTest"</span> <span style="color:#ff0000">TargetControlID</span><span style="color:#0000ff">="pnContent"</span> <span style="color:#ff0000">ToggleControlID</span><span style="color:#0000ff">="hrefToggle"</span><br> <span style="color:#ff0000">ToggleImageID</span><span style="color:#0000ff">="imgCollapse"</span> <span style="color:#ff0000">ToggleTextID</span><span style="color:#0000ff">="lblCollapse"</span> <span style="color:#ff0000">ExpandedText</span><span style="color:#0000ff">="Click here to collapse"</span><br> <span style="color:#ff0000">ExpandedImage</span><span style="color:#0000ff">="Images/collapse.gif"</span> <span style="color:#ff0000">CollapsedText</span><span style="color:#0000ff">="Click here to expand"</span> <span style="color:#ff0000">CollapsedImage</span><span style="color:#0000ff">="Images/expand.gif"</span><br> <span style="color:#ff0000">Collapsed</span><span style="color:#0000ff">="true"></</span><span style="color:#800000">wuc</span><span style="color:#0000ff">:</span><span style="color:#800000">Toggle</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">form</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">body</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">html</span><span style="color:#0000ff">></span></div> </div> </div> <p>And the code behind of the page:</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:ee689e09-25c8-4504-b00a-db117ca6eddf" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#0000ff">using</span> System;<br> <span style="color:#0000ff">using</span> System.Collections.Generic;<br> <span style="color:#0000ff">using</span> System.Linq;<br> <span style="color:#0000ff">using</span> System.Web;<br> <span style="color:#0000ff">using</span> System.Web.UI;<br> <span style="color:#0000ff">using</span> System.Web.UI.WebControls;<br> <br> <span style="color:#0000ff">namespace</span> JQueryCollapse<br> {<br> <span style="color:#0000ff">public</span> <span style="color:#0000ff">partial</span> <span style="color:#0000ff">class</span> <span style="color:#2b91af">Default</span> : System.Web.UI.<span style="color:#2b91af">Page</span><br> {<br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">void</span> Page_Load(<span style="color:#0000ff">object</span> sender, <span style="color:#2b91af">EventArgs</span> e)<br> {<br> }<br> <br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> Toggle the extender</span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"></summary></span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><param name="sender"></param></span><br> <span style="color:#808080">///</span><span style="color:#008000"> </span><span style="color:#808080"><param name="e"></param></span><br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">void</span> btnCollapse_Click(<span style="color:#0000ff">object</span> sender, <span style="color:#2b91af">EventArgs</span> e)<br> {<br> tTest.Collapsed = !tTest.Collapsed;<br> }<br> }<br> }</div> </div> </div> <p> </p> <p>And at last the full source code of the solution can be downloaded <a href="http://cid-4bd24c1cd19d9f89.office.live.com/self.aspx/Public/JQueryCollapse.zip">my public folder on skydrive</a>.</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com3tag:blogger.com,1999:blog-8012614073429928775.post-25176060936323149822011-01-31T16:14:00.001+01:002011-01-31T16:14:28.071+01:00Asp.net web server control for rendering script blocks<p>There are many ways to add script block to our asp.net pages. This is a very simple problem until we had got a complex project with custom controls, partial page rendering, updatepanels and dynamically loaded controls.<br>We have to add script blocks different ways if we asynchrony post backs or full page post backs. <br>The other problem is when the script block don’t have to load at the first page load, it depends on some logic dynamically.</p> <p>After many trying I use this solution since about half year and it works at any case.</p> <p>The simple and easy to use source code is:</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:b5dd9917-fabe-411c-b5f4-db70e1fdcf37" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#0000ff">public</span> <span style="color:#0000ff">class</span> <span style="color:#2b91af">InlineScript</span> : <span style="color:#2b91af">Control</span><br> {<br> <span style="color:#0000ff">protected</span> <span style="color:#0000ff">override</span> <span style="color:#0000ff">void</span> Render(<span style="color:#2b91af">HtmlTextWriter</span> writer)<br> {<br> <span style="color:#2b91af">StringBuilder</span> sb = <span style="color:#0000ff">new</span> <span style="color:#2b91af">StringBuilder</span>();<br> <span style="color:#0000ff">base</span>.Render(<span style="color:#0000ff">new</span> <span style="color:#2b91af">HtmlTextWriter</span>(<span style="color:#0000ff">new</span> <span style="color:#2b91af">StringWriter</span>(sb)));<br> <br> <span style="color:#0000ff">string</span> script = sb.ToString();<br> <br> <span style="color:#2b91af">ScriptManager</span> sm = <span style="color:#2b91af">ScriptManager</span>.GetCurrent(Page);<br> <span style="color:#0000ff">if</span> (!(<span style="color:#0000ff">null</span> == sm))<br> {<br> <span style="color:#0000ff">if</span> (sm.IsInAsyncPostBack)<br> {<br> <span style="color:#2b91af">ScriptManager</span>.RegisterStartupScript(<span style="color:#0000ff">this</span>, <span style="color:#0000ff">typeof</span>(<span style="color:#2b91af">InlineScript</span>), UniqueID, script, <span style="color:#0000ff">false</span>);<br> }<br> <span style="color:#0000ff">else</span><br> {<br> <span style="color:#0000ff">base</span>.Render(writer);<br> }<br> }<br> <span style="color:#0000ff">else</span><br> {<br> <span style="color:#0000ff">this</span>.Page.ClientScript.RegisterStartupScript(<span style="color:#0000ff">this</span>.GetType(), <span style="color:#0000ff">this</span>.ID, sb.ToString());<br> }<br> }<br> }</div> </div> </div> <p>After registering into web.config, we can use it easily everywhere on our program:</p> <div style="padding-bottom: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: none; padding-top: 0px" id="scid:9ce6104f-a9aa-4a17-a79f-3a39532ebf7c:623d2781-596e-4a47-ae15-ad702300e763" class="wlWriterEditableSmartContent"> <div style="border: #000080 1px solid; color: #000; font-family: 'Courier New', Courier, Monospace; font-size: 10pt"> <div style="background-color: #ffffff; overflow: auto; padding: 2px 5px; white-space: nowrap"><span style="color:#0000ff"><</span><span style="color:#800000">wc</span><span style="color:#0000ff">:</span><span style="color:#800000">InlineScript</span> <span style="color:#ff0000">ID</span><span style="color:#0000ff">="isTest"</span> <span style="color:#ff0000">runat</span><span style="color:#0000ff">="server"></span><br> <span style="color:#0000ff"><</span><span style="color:#800000">script</span> <span style="color:#ff0000">type</span><span style="color:#0000ff">="text/javascript"></span><br> <span style="color:#0000ff">function</span> pageLoad() {<br> alert(<span style="color:#800000">'hi'</span>);<br> }<br> <span style="color:#0000ff"></</span><span style="color:#800000">script</span><span style="color:#0000ff">></span><br> <span style="color:#0000ff"></</span><span style="color:#800000">wc</span><span style="color:#0000ff">:</span><span style="color:#800000">InlineScript</span><span style="color:#0000ff">></span></div> </div> </div> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com1tag:blogger.com,1999:blog-8012614073429928775.post-63547793241457168442011-01-29T13:43:00.001+01:002011-01-29T13:44:07.787+01:00How to set up Windows 7 environment for PHP + MySQL development on IIS7<p>We need a following tools for an average PHP + MySql development procedure:</p> <blockquote> <p>- a good IDE</p> <p>- something for managing database</p> <p>- web server</p> <p>- ability to debugging applications</p> <p>- get all of them free.</p></blockquote> <p>IDE: We need intellisense support and tools for debugging. Smart people doesn’t start any development without these. If you have Windows operating system, you can use <a href="http://www.microsoft.com/web/downloads/platform.aspx" target="_blank">Web Platform Installer</a> and it offers a development environment named Webmatrix free of charge. <a href="http://www.asp.net/webmatrix" target="_blank">Webmatrix</a> doesn’t have debugger or intellisense support so it’s only a memory killer, good looking notepad. After some googling and based on my experiments I have been chosen <a href="http://netbeans.org/" target="_blank">Netbeans IDE.</a></p> <p>Database Manager: I don’t use MySql too often so I let <a href="http://www.mysql.com/" target="_blank">mysql.com</a> to choose my tool. I wanted a native sql IDE and found <a href="http://wb.mysql.com/" target="_blank">MySql Workbench</a>. It doesn’t have too long feature list but we can do the basic SQL tasks and it is not <a href="http://www.phpmyadmin.net/home_page/index.php" target="_blank">PHPMyAdmin</a> so we doesn’t need to run web browser for insert a simple record into our database.</p> <p>Web server: I use Windows 7 and of course it will IIS7. I have been bought my Windows before and I can install it free of charge.</p> <p> </p> <p>Installing <a href="http://www.iis.net/" target="_blank">IIS7</a>: I won’t describe how to install it. <a href="http://learn.iis.net/page.aspx/28/installing-iis-7-on-windows-vista-and-windows-7/" target="_blank">There is a good description how to install it on IIS website.</a></p> <p>Installing Netbeans IDE:</p> <blockquote> <p>- <a href="http://netbeans.org/downloads/index.html" target="_blank">Download</a> from netbeans.org the php development version (or we can choose version named ALL, that will install every development tools from C to Java that netbeans supports).</p> <p>- Install the executable. (Not too hard, next-next-next…..-finish).</p> <p>- Navigate to PHP tab on Tools->Options on Netbeans menu. If we got ALL version, we have to make a new php project. In that case Netbeans initializes himself for php development and we will see PHP tab on Options->Tools after creating New PHP Project. If we installed the PHP version, we don’t need to make new PHP project, Netbeans initializes php settings during the first run. </p> <p>- Check the php development settings. We need to set Debugger Port to 9000 and Session_ID to netbeans-xdebug for debugging php applications.</p></blockquote> <p>Installing Mysql</p> <blockquote> <p>- Go to <a href="http://www.mysql.com/downloads/" target="_blank">MySQL Download page.</a></p> <p>- Download the proper version for our operating system (In my case it means Windows (x86, 32-bit), MSI Installer).</p> <p>- Install the executable. (Not too hard, next-next-next…..-finish). We have to set security settings during the installation.</p> <p>- <a href="http://dev.mysql.com/downloads/workbench/5.2.html" target="_blank">Download MySql Workbench</a>. We have to chose a version again.</p> <p>- Install the executable. (Not too hard, next-next-next…..-finish).</p> <p>- Start MySql Workbench and test if we can connect to our database. <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVV4QrHbuYFZp2HfhqRm5BKpxSYdmvWXX92X5et2BLTYuigypwUMvAfYLlqcdiUnRbyTA3uJGirDzB8M4q6sA6nUxTghy5AnMkM-k_k_RgNrf4i4A6iZwKYRq8p9gO9pS4MQBOmHZs-9w/s1600-h/WorkbenchStartPage%5B4%5D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top: 0px; border-right: 0px; padding-top: 0px" title="WorkbenchStartPage" border="0" alt="WorkbenchStartPage" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzY57jK5c7YSIrKn4StCt-qPmcKYrFr8-IDz1_UDXo8V3dW2n4SEZmRkdOlsfaoZB4iKhRiv7cCKoQM475dTUFsM-OcYq-D-3pvqF_R8CPk5pAIAyItenwnys6FmaVqYcSCC_0FfH6iGk/?imgmax=800" width="244" height="184"></a></p></blockquote> <p>Installing PHP binaries:</p> <blockquote> <p>- Start Web Platform Installer. If doesn’t have, download and install it.</p> <p>- Go to products tab, Frameworks entry and check PHP 5.3 for WebMatrix and PHP Manager for IIS.<a href="http://lh5.ggpht.com/_pXaM22WUhZY/TUQLOF28ItI/AAAAAAAAADo/FiMSnevxoX0/s1600-h/WebPlatformInstallerSelectItems%5B2%5D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top: 0px; border-right: 0px; padding-top: 0px" title="WebPlatformInstallerSelectItems" border="0" alt="WebPlatformInstallerSelectItems" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpI_azZLMBUIZ52gVaNiTf_zcg-PRvVc-53v1Ga85Mhi1oG-4a3ErfKSLulIyyHOudTzfXgvZJmsnb61M4Llz86imOI1Gj7Nbolhg7fedncS8SA_M1Rhu3ud5NuC356XpFC5YQ0sf5cCU/?imgmax=800" width="244" height="170"></a></p> <p>- Click on Install button.</p> <p>- Installer shows us what will it install and what will it install more. We didn’t check WebMatrix but we have to install it. Click on Accept button. Ha must install it but don’t need to use this piece of shit.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSkoWpZvA_p0d-2Civ1cJxyr_Us4GBRorCkiDXvAcPpFr3qPsmWreRgWM8ol7SMOQaQih4hNEOhAam7Qj6xB5jgT5o8jrAfQ29M6qYfEaQurkfHUZHX4cHT0P2WoH8BWtOIgPt6M-AzfI/s1600-h/WebPlatformInstallerDependencies%5B2%5D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top: 0px; border-right: 0px; padding-top: 0px" title="WebPlatformInstallerDependencies" border="0" alt="WebPlatformInstallerDependencies" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDPEWgikeET2aVx4m32BAoBwwDFUQZNNcfkgiFi_5ixYPtEcy84MEppPHBRx9T2iJdtPrmqZwBo_lM7_Q35sMe6LbcoGkckyLNDjOeGswlMKzfpwW7mm6Zhxa4VOpelJV2azg0ZuOloUg/?imgmax=800" width="244" height="230"></a></p> <p>- At the end of the installation Web Platform Installer shows us a message window and offers to run WebMatrix. Simple close the window, don’t need to start it.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_n_L62xr8muBCODeNLmf20rD1b_oGIB4viFEAomhFWDtNGVofvg2VfppOnDAfUWe85YxqBwjsTTg4B2VV1j20iq7kuk_L8ZJPiQoACTOOPxGOwlXOa1lsrcrQDPmt_3NNyMUr1H9kvdA/s1600-h/WebPlatformInstallerInstalComplete%5B2%5D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top: 0px; border-right: 0px; padding-top: 0px" title="WebPlatformInstallerInstalComplete" border="0" alt="WebPlatformInstallerInstalComplete" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNDU268FRE-aABA8OxrOOeiEby_YiqNTwmyS419BbhH-aFIaLiT9hb8rdcITq4FQGnnQAyKUdsjI4QK-37ZOJcVShWBC0GMwdALTr7O0MJN3JOG9iRzwtxbBLiiDK8ahoKUxerMPqJYBo/?imgmax=800" width="244" height="169"></a></p> <p>- Install CGI Extension for IIS7:</p> <ul> <li>Start Control Panel and go to Programs entry.<br><a href="http://lh6.ggpht.com/_pXaM22WUhZY/TUQLRnOTmRI/AAAAAAAAAEA/37U9b2U1V1A/s1600-h/IISCGIModuleInstallation%5B5%5D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top: 0px; border-right: 0px; padding-top: 0px" title="IISCGIModuleInstallation" border="0" alt="IISCGIModuleInstallation" src="http://lh6.ggpht.com/_pXaM22WUhZY/TUQLSIOF_sI/AAAAAAAAAEE/LD7k-tsUUNI/IISCGIModuleInstallation_thumb%5B1%5D.png?imgmax=800" width="244" height="148"></a></li> <li>Click on Turn Windows features on or off entry.</li> <li>Got to Information Services-World Wide Web Services->Application Development Feature and check CGI</li> <li>Install it.</li></ul></blockquote> <p>Installing xDebug:</p> <blockquote> <p>- Start IIS7 manager.</p> <p>- We can find PHP Manager on IIS section on IIS Manager.<a href="http://lh6.ggpht.com/_pXaM22WUhZY/TUQLSUUQ3TI/AAAAAAAAAEI/2WBTRXNA7uM/s1600-h/IISManagerPHPManagerIcon%5B2%5D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top: 0px; border-right: 0px; padding-top: 0px" title="IISManagerPHPManagerIcon" border="0" alt="IISManagerPHPManagerIcon" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ8n2UwurXGNcGC3POzel_hnNYE4Xbb0TPM4dBBw79ZEyRC5c-vQx_S2KgZuMMtqPgHy7H7JiisaD5ANR6Pa1pDKHRZl8gGrExX9sKZlaeZqaQT-ued0rIuhkFi0ufqiL1sjxJq5ifF5A/?imgmax=800" width="244" height="144"></a></p> <p>- Click on Check phpinfo() link.</p> <p><a href="http://lh4.ggpht.com/_pXaM22WUhZY/TUQLTsCN12I/AAAAAAAAAEQ/cMTprXXQvO8/s1600-h/PHPManagerCheckPHPInfo%5B2%5D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top: 0px; border-right: 0px; padding-top: 0px" title="PHPManagerCheckPHPInfo" border="0" alt="PHPManagerCheckPHPInfo" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSBDz9knrsME2PcmdyWRdRiRhbRzOM9Voub2sZyG-uG-UuLa8RXHFTahIS3xlTUhhenXeeKthjWjqpEErDkQMBvE2rzJBK5s9EmeSZEoXuN9diiKGDa4mEys2QF4E6v_YGA-IBzeyeeMc/?imgmax=800" width="244" height="144"></a></p> <p>- Choose site and url.</p> <p>- Start our browser and go to the <a href="http://www.xdebug.org/" target="_blank">home page of xDebug</a>. There is a page named <a href="http://www.xdebug.org/find-binary.php" target="_blank">fnd binary</a> on that site and it helps us deciding which xDebug version we need. There is a big, empty textbox in that page. Paste our phpinfo output into this textbox and click on Analyse my phpinfo() button. After analysed, the page offers the proper xDebug version for us. That means MS VC9 – Architecture: x86 in my case. After analyising this page offers a link direct to download page under Instructions.</p> <p>- <a href="http://www.xdebug.org/download.php" target="_blank">Download</a> the proper dll and copy it to an ext subfolder to our php installation. At my case it means the folder c:\Program Files (x86)\PHP\v5.3\ext\ .</p> <p>- Go back to PHP Manager on IIS and click on Configuration file link and we can edit our php.ini settting file.</p> <p>- Add the following lines to the end of the file:</p> <p>- Restart IIS.</p> <p>- Check phpinfo() again. If we installed xDebug successfully we will find a ‘… with xDebug…’ part of the output at the end of the first block of the html result of phpinfo that signs that our installation is successful.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3zBRGcjQ28H8TiOPwuWhyphenhyphen8Jd-570mvBaq2hqoHdnd0T_7M_oWmTg-C4YSUXMGZXOkaMP4nNtL_fKy_C9pncXELzghv26vYaemQDfRNL1pIg-InHGvLoLRIRsxvs9Mn23CoDD7YK6PUms/s1600-h/PHPInfoWithXDebug%5B2%5D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top: 0px; border-right: 0px; padding-top: 0px" title="PHPInfoWithXDebug" border="0" alt="PHPInfoWithXDebug" src="http://lh3.ggpht.com/_pXaM22WUhZY/TUQLVSGh1RI/AAAAAAAAAEc/WvaoYtfXhLo/PHPInfoWithXDebug_thumb.png?imgmax=800" width="244" height="148"></a></p></blockquote> <p>At that point we don’t have any to do, only write our firs PHP application (of course a simple Hello World) and check if we can debug it and if we can connect to our MySql database.</p> <p> </p> <p>Note: We have to use the same port at xDebug port in php.ini and netbeans-xdebug port in Netbeans settings. If we start a php project on debug mode in Netbeans and we have wrong port settings, we can’t debug and Netbeans cannot connect to xDebug process only shows a messange: Waiting for conncection (netbeans-xdebug).<a href="http://lh3.ggpht.com/_pXaM22WUhZY/TUQLV58j1CI/AAAAAAAAAEg/djy0tAzSMaY/s1600-h/NetbeansWrongXDebugSettings%5B2%5D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top: 0px; border-right: 0px; padding-top: 0px" title="NetbeansWrongXDebugSettings" border="0" alt="NetbeansWrongXDebugSettings" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgS_4C_oGVNbjV5-eT1gqTC8m9k7zg8ADzu8BeB_4tVKHmh-9peW_NzoW6E0O6SlFBdCA5Idap0TloNHHnFAtffGKOdcNVhyphenhypheneoyskU0LfCc4HikDfT706zq60uGKayc-pDilvpHMEXXPu8/?imgmax=800" width="244" height="148"></a></p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com8tag:blogger.com,1999:blog-8012614073429928775.post-65891805066440649422011-01-08T13:07:00.001+01:002011-01-08T13:07:41.219+01:00C#: throwable exceptions and Visual Studio Productivity Power Tools<p> </p> <p>There is a really great add-on for Visual Studio named <a href="http://visualstudiogallery.msdn.microsoft.com/en-us/d0d33361-18e2-46c0-8ff2-4adea1e34fef" target="_blank">Productivity Power Tools</a>. It helps a lot during my work but hides some information that so important for me: the list of throwable exceptions.</p> <p>At the <a href="http://unideb.hu" target="_blank">university</a> I didn’t have a choice. I had to learn Java. Java is a great language and has a restriction that I like so: you must specify the list of the throwable exceptions at the specification of each method. I work with C# nowadays. It doesn’t has this rule but Visual Studio tries to help us on a simple way: it shows us this list when we hover on the name of the given method with the mouse like this:<br><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIeduwyrA5APXpsnjS-qZzN2MNdDwt2MARGo-iHeVPu_lo7iuOVutiyuxf-zrZibg3KJNeMj58SGA9fhgYH7OqyHqZV3nXiJEa4mHgBpK4AbxnzfJ4q0BQVK3mtLIRBQeiMxxUfz5ruv8/s1600-h/originalLook%5B4%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="originalLook" border="0" alt="originalLook" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiprTB7uTab2bGa6ixjfHFwAlzAVR1EaQZeSO47ZhVzfpijjpAaCzduYODAKJxtTlBXUcWg9pqbvBFj14y0dAgFzkLN5e-Gc7lGr7-32qC0vTa9vaTfhNdVeOXk2wDS52aOSromaQjyri4/?imgmax=800" width="644" height="388"></a></p> <p>As we can se, we now a bit closer to the Java restrictions. We can find out fast the given list but the language still don’t force us into handle all exceptions. It smart or not – I don’t know. But in fact I’m happy to see this list.</p> <p>There is a bit problem with the power tools: It gives us a new window for our Visual Studio, named Solution Navigator. It has a lot of functions among others coloring the hints that I shown before:</p> <p><a href="http://lh3.ggpht.com/_pXaM22WUhZY/TShTgzCvzAI/AAAAAAAAADQ/aOBnjorJyiw/s1600-h/withSolutionNavigator%5B3%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="withSolutionNavigator" border="0" alt="withSolutionNavigator" src="http://lh3.ggpht.com/_pXaM22WUhZY/TShThbr5eVI/AAAAAAAAADU/QFQQ5LyZ0MY/withSolutionNavigator_thumb%5B1%5D.png?imgmax=800" width="644" height="376"></a></p> <p>Ops! As we can see, the list of throwable exceptions has disappeared. We have an other function instead of this, every can try this out by clicking on the triangle at the right side of the popup.</p> <p>At this time we have two choice. We can use our Visual Studio with these settings or we can disable this function by setting of on the Visual Studio menu –> Tools –> Options –> Productivity Power Tools –> Solution Navigator. We got the original function after restarting our development environment.</p> <p><a href="http://lh6.ggpht.com/_pXaM22WUhZY/TShTh5IuvjI/AAAAAAAAADY/St65-djvxr8/s1600-h/powerToolsSettings%5B3%5D.png"><img style="background-image: none; border-right-width: 0px; margin: ; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="powerToolsSettings" border="0" alt="powerToolsSettings" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi232j4Mpii2a5gEf9ynUwV-G8NDA231rlqcKLLimMC1Pt52jj4cTtuXxEmmf7A2fojQIkx7PnUnnvPCQD7dMTLwLg6gEETy_bD-pAyi8j6QVpYKwqRZ4MVVstM-MIVgsLR7jxOXI0Hb8A/?imgmax=800" width="644" height="376"></a></p> <p> </p> <p>Remarks: Solution Navigator is so slow. Sometimes useful but the most part of my times it only slows down my work without any benefits.</p> Miskolczi Zsolthttp://www.blogger.com/profile/02786539701446355702noreply@blogger.com0