tag:blogger.com,1999:blog-42565382205878576092024-02-18T23:07:45.701-08:00Explore C# and C++ Programming Tips and TricksWith this blog I try to share the solutions of real time problems I have faced in different software solutions.
Most of these are related to C#.NET and interation of C#.NEt with C++.NETAnugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.comBlogger36125tag:blogger.com,1999:blog-4256538220587857609.post-39405177713607016052016-08-01T04:04:00.000-07:002016-08-01T04:04:24.961-07:00<div dir="ltr" style="text-align: left;" trbidi="on">
<h1 style="margin-bottom: 11.25pt; margin-left: 0in; margin-right: 0in; margin-top: 0in;">
<span style="color: #222222; font-family: Helvetica, sans-serif;"><span style="font-size: 20px;">Did I Really Install SQL Server 2016 in Just 2 Minutes?</span></span></h1>
<div class="MsoNormal">
<span style="color: #222222; font-family: "Helvetica",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Earlier this week, I
published a video, which you can watch <strong><a href="http://t.dripemail2.com/c/eyJhY2NvdW50X2lkIjoiMjc1MDc0OSIsImRlbGl2ZXJ5X2lkIjoiMjIyMTY3NzkwIiwidXJsIjoiaHR0cHM6Ly93d3cueW91dHViZS5jb20vd2F0Y2g_dj1WSmNvNVdqSFZhUVx1MDAyNl9fcz04enVtN3g1czR2cGRqc3ZqOTlkcSJ9">here</a></strong>: <a href="https://www.youtube.com/watch?v=VJco5WjHVaQ">https://www.youtube.com/watch?v=VJco5WjHVaQ</a> I
demonstrated how one can install SQL Server 2016 with all the possible features
turned on. I tried to explain everything you need to know to do a default
installation in SQL Server 2016. The total length of the video is 121
seconds.<br />
<br />
After the video was published, I was asked by a couple of users that did it
really took only 2 minutes for me to install SQL Server 2016. Well, here is the
answer, in reality it took around 6-7 minutes, but during installation, there
were many moments where the screen was just stuck in the same status for a
while. I edited them out and the video is now just of 2 minutes.<br />
<br />
Trust me, this is the best possible use of your 2 minutes of time. REMEMBER you
can download SQL Server 2016 Developer version for FREE. Additionally, you will
have to install SQL Server Management Studio separately in SQL Server 2016
version. Microsoft has introduced a new sample database over in SQL Server
2016. Do not miss out on all these important points. <o:p></o:p></span></div>
<ul type="disc">
<li class="MsoNormal" style="color: #222222; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><strong><span style="font-family: "Helvetica",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><a href="http://t.dripemail2.com/c/eyJhY2NvdW50X2lkIjoiMjc1MDc0OSIsImRlbGl2ZXJ5X2lkIjoiMjIyMTY3NzkwIiwidXJsIjoiaHR0cDovL2Jsb2cuc3FsYXV0aG9yaXR5LmNvbS8yMDE2LzA2LzAxL2Rvd25sb2FkLXNxbC1zZXJ2ZXItMjAxNi1kZXZlbG9wZXItZWRpdGlvbi1mcmVlLz9fX3M9OHp1bTd4NXM0dnBkanN2ajk5ZHEifQ" target="_blank">SQL Server 2016 Developer Edition FREE Download</a></span></strong><span style="font-family: "Helvetica",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></li>
<li class="MsoNormal" style="color: #222222; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><strong><span style="font-family: "Helvetica",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><a href="http://t.dripemail2.com/c/eyJhY2NvdW50X2lkIjoiMjc1MDc0OSIsImRlbGl2ZXJ5X2lkIjoiMjIyMTY3NzkwIiwidXJsIjoiaHR0cDovL2Jsb2cuc3FsYXV0aG9yaXR5LmNvbS8yMDE2LzAzLzE0L3NxbC1zZXJ2ZXItaW5zdGFsbGluZy1tYW5hZ2VtZW50LXN0dWRpby1zcWwtc2VydmVyLTIwMTYtc3RlcC1zdGVwLz9fX3M9OHp1bTd4NXM0dnBkanN2ajk5ZHEifQ" target="_blank">SQL Server 2016 Management Studio Download</a></span></strong><span style="font-family: "Helvetica",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></li>
<li class="MsoNormal" style="color: #222222; mso-list: l0 level1 lfo1; mso-margin-bottom-alt: auto; mso-margin-top-alt: auto; tab-stops: list .5in;"><strong><span style="font-family: "Helvetica",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><a href="http://t.dripemail2.com/c/eyJhY2NvdW50X2lkIjoiMjc1MDc0OSIsImRlbGl2ZXJ5X2lkIjoiMjIyMTY3NzkwIiwidXJsIjoiaHR0cDovL2Jsb2cuc3FsYXV0aG9yaXR5LmNvbS8yMDE2LzA2LzE3L3NxbC1zZXJ2ZXItMjAxNi1pbXBvcnQtbmV3LXNhbXBsZS1kYXRhYmFzZS13aWRld29ybGRpbXBvcnRlcnMvP19fcz04enVtN3g1czR2cGRqc3ZqOTlkcSJ9" target="_blank">SQL Server 2016 Sample Database Download and Install</a></span></strong><span style="font-family: "Helvetica",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></li>
</ul>
<span style="color: #222222; font-family: "Helvetica",sans-serif; font-size: 10.5pt; mso-ansi-language: EN-US; mso-bidi-font-family: "Times New Roman"; mso-bidi-language: AR-SA; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-US;">All
the associated resources are mentioned in <strong><a href="http://t.dripemail2.com/c/eyJhY2NvdW50X2lkIjoiMjc1MDc0OSIsImRlbGl2ZXJ5X2lkIjoiMjIyMTY3NzkwIiwidXJsIjoiaHR0cDovL2Jsb2cuc3FsYXV0aG9yaXR5LmNvbS8yMDE2LzA3LzI3L3NxbC1zZXJ2ZXItMjAxNi1sZWFybi1pbnN0YWxsYXRpb24tc3FsLXNlcnZlci0yMDE2LTItbWludXRlcy12aWRlby8_X19zPTh6dW03eDVzNHZwZGpzdmo5OWRxIn0">this
blog post</a></strong>.<br />
Link: <a href="http://blog.sqlauthority.com/2016/07/27/sql-server-2016-learn-installation-sql-server-2016-2-minutes-video/">http://blog.sqlauthority.com/2016/07/27/sql-server-2016-learn-installation-sql-server-2016-2-minutes-video/</a></span><br />
<br />
From:<br />
Pinal Dave<br />
SQLAuthority.com</div>
Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-69976963503483733002014-09-03T02:05:00.000-07:002014-09-03T02:06:04.815-07:00WCF REST vs. Web API<div class="WordSection1"> <h1><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";font-weight:normal">Very nice explanation of difference between Web Service, WCF, WCF REST and Web API.<o:p></o:p></span></h1> <h1><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";font-weight:normal"><a href="http://www.dotnet-tricks.com/Tutorial/webapi/JI2X050413-Difference-between-WCF-and-Web-API-and-WCF-REST-and-Web-Service.html">http://www.dotnet-tricks.com/Tutorial/webapi/JI2X050413-Difference-between-WCF-and-Web-API-and-WCF-REST-and-Web-Service.html</a> <o:p></o:p></span></h1> <p class="MsoNormal"><span lang="EN-IN" style="color:black">Namaste !</span><b><span lang="EN-IN" style="color:#1F497D"><o:p></o:p></span></b></p> <p class="MsoNormal"><b><span lang="EN-IN" style="color:#1F497D">Anugrah Atreya<o:p></o:p></span></b></p> </div> <br> <hr> <font face="Arial" color="Black" size="2"><br> </font> Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-29312749896886521442014-08-09T11:49:00.001-07:002014-08-09T12:05:15.277-07:00Unit Testing : [ExpectedException]<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="WordSection1">
<div class="MsoPlainText">
<span style="font-family: Arial,Helvetica,sans-serif;">Hi All,</span></div>
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
<span style="font-family: Arial,Helvetica,sans-serif;"><o:p></o:p></span></div>
<div class="MsoPlainText">
<span style="font-family: Arial,Helvetica,sans-serif;">In this post I’ll explain what you need to do if you have to ensure that a particular exception <b>must </b>be thrown by your C# code.</span></div>
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
<span style="font-family: Arial,Helvetica,sans-serif;"><o:p></o:p></span></div>
<div class="MsoPlainText">
<span style="font-family: Arial,Helvetica,sans-serif;">The reason why I am writing this post is that some people mistakenly use keyword <span style="color: #2b91af; font-size: 10.0pt;">ExpectedException</span> as given below:</span><o:p></o:p></div>
<div class="MsoPlainText">
<br /></div>
<blockquote class="tr_bq">
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;">[<span style="color: #2b91af;">TestMethod</span>]<o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"><span style="background: silver; mso-highlight: silver;">[<span style="color: #2b91af;">ExpectedException</span>(<span style="color: blue;">typeof</span>(<span style="color: #2b91af;">ApplicationException</span>), <span style="color: #a31515;">"Game has already Concluded, Start new Game !"</span>)]</span> <o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"><span style="color: blue;">public</span> <span style="color: blue;">void</span> FirstTwoRows()<o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;">{<o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <span style="color: #2b91af;">GameEngine</span> ttt = <span style="color: blue;">new</span> <span style="color: #2b91af;">GameEngine</span>();<o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <span style="color: #2b91af;">Assert</span>.IsTrue(ttt.RecordMove(<span style="color: #a31515;">'a'</span>, 3), <span style="color: #a31515;">"This is not a winning move"</span>);<o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <span style="color: green;">//Following line should throw Application Exception <o:p></o:p></span></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <span style="color: #2b91af;">Assert</span>.IsFalse(ttt.RecordMove(<span style="color: #a31515;">'b'</span>, 6), <span style="color: #a31515;">"This is not a winning move"</span>);<o:p></o:p></span></div>
<div class="MsoPlainText">
<span style="font-family: "Courier New"; font-size: 10.0pt;">}<o:p></o:p></span></div>
</blockquote>
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
<span style="font-family: Arial,Helvetica,sans-serif;">Q: What is wrong with this code?<o:p></o:p></span></div>
<div class="MsoPlainText">
<span style="font-family: Arial,Helvetica,sans-serif;">A: This test method will not fail if you get the specified Exception, but it will also pass when you don’t get it at all.<o:p></o:p></span></div>
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
<span style="font-family: Arial,Helvetica,sans-serif;">Q: So, whats the fix?<o:p></o:p></span></div>
<div class="MsoPlainText">
<span style="font-family: Arial,Helvetica,sans-serif;">A: You also need to add <span style="color: #2b91af; font-size: 10.0pt;"> Assert</span><span style="font-size: 10.0pt;">.Fail</span> after the line where you are expecting the exception.</span><o:p></o:p></div>
<div class="MsoPlainText">
<br /></div>
<blockquote class="tr_bq">
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;">[<span style="color: #2b91af;">TestMethod</span>]<o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;">[<span style="color: #2b91af;">ExpectedException</span>(<span style="color: blue;">typeof</span>(<span style="color: #2b91af;">ApplicationException</span>), <span style="color: #a31515;">"Game has already Concluded, Start new Game !"</span>)] <o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"><span style="color: blue;">public</span> <span style="color: blue;">void</span> FirstTwoRows()<o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;">{<o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <span style="color: #2b91af;">GameEngine</span> ttt = <span style="color: blue;">new</span> <span style="color: #2b91af;">GameEngine</span>();<o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <span style="color: #2b91af;">Assert</span>.IsTrue(ttt.RecordMove(<span style="color: #a31515;">'a'</span>, 3), <span style="color: #a31515;">"This is not a winning move"</span>);<o:p></o:p></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <span style="color: green;">//Following line should throw Application Exception<o:p></o:p></span></span></div>
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> <span style="color: #2b91af;">Assert</span>.IsFalse(ttt.RecordMove(<span style="color: #a31515;">'b'</span>, 6), <span style="color: #a31515;">"This is not a winning move"</span>);</span><span style="background: silver; color: #2b91af; font-family: "Courier New"; font-size: 10.0pt; mso-highlight: silver;"></span><br />
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;"> </span><span style="font-family: "Courier New"; font-size: 10.0pt;"><span style="color: #2b91af;">Assert</span>.Fail("<span style="color: #990000;">Above line should have thrown Exception of given type</span>");</span><br />
<div class="MsoNormal" style="text-autospace: none;">
<span style="font-family: "Courier New"; font-size: 10.0pt;">}</span></div>
</div>
</div>
</blockquote>
<span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;">I hope this small change will save lot of time for you and make your Test driven Development even better.<o:p></o:p></span></span><br />
<div class="MsoPlainText">
<br /></div>
<div class="MsoPlainText">
<span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;">Namaste (Greetings)<o:p></o:p></span></span></div>
<div class="MsoPlainText">
<span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;">Anugrah Atreya</span></span><o:p></o:p></div>
<div class="MsoPlainText">
<br /></div>
</div>
<br />
<hr />
<span style="color: black; font-family: Arial; font-size: x-small;"><br /> </span> </div>
Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-59436446711472537212014-08-09T11:20:00.000-07:002014-08-09T11:21:03.687-07:00Big O Notation : Algorithm Performance<div class="WordSection1"> <p class="MsoNormal"><b>Big-O-Notation</b> is used to measure and express the <strong> <span style="font-family:"Calibri","sans-serif"">relative representation of the complexity of an algorithm</span></strong>:<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">Did you ever tried to understand why :<o:p></o:p></p> <p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo1"><![if !supportLists]><span style="font-family:Symbol"><span style="mso-list:Ignore">·<span style="font:7.0pt "Times New Roman""> </span></span></span><![endif]>Bubble Sort is O( n square ) <o:p></o:p></p> <p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo1"><![if !supportLists]><span style="font-family:Symbol"><span style="mso-list:Ignore">·<span style="font:7.0pt "Times New Roman""> </span></span></span><![endif]>Quick Sort is O( n log n) <o:p></o:p></p> <p class="MsoNormal">or you just remember it by heart and don’t know what’s hiding behind these names/values <span style="font-family:Wingdings">J</span><o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">Below given are two best explanations I found in a quick search:<o:p></o:p></p> <p class="MsoNormal"><a href="http://stackoverflow.com/questions/2307283/what-does-olog-n-mean-exactly">http://stackoverflow.com/questions/2307283/what-does-olog-n-mean-exactly</a><o:p></o:p></p> <p class="MsoNormal"><a href="http://stackoverflow.com/questions/487258/plain-english-explanation-of-big-o/487278#487278">http://stackoverflow.com/questions/487258/plain-english-explanation-of-big-o/487278#487278</a><o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">They both use example of <b>Telephone Book</b> and make it so simple to understand the whole thing. <o:p></o:p></p> <p class="MsoNormal">If you understand the Telephone Book example then I guarantee you that you will be able to tell the performance of any algorithm in a quick moment without referring its documentation.<o:p></o:p></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal">Namaste (Greetings)<o:p></o:p></p> <p class="MsoNormal"><b><span lang="EN-IN" style="color:#1F497D">Anugrah Atreya<o:p></o:p></span></b></p> <p class="MsoNormal"><o:p> </o:p></p> </div> <br> <hr> <font face="Arial" color="Black" size="2"><br> </font> Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-58599967738229331102012-04-10T03:01:00.001-07:002012-04-10T03:01:54.344-07:00Debugging of UnitTest cases from network drive<div class=WordSection1><p class=MsoNormal>Hi,<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span style='color:#1F497D'>This is in continuation of my original post: <o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><a href="http://explorecsharp.blogspot.in/2009/09/not-able-to-run-unittest-cases-when-you_3846.html">http://explorecsharp.blogspot.in/2009/09/not-able-to-run-unittest-cases-when-you_3846.html</a><o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal>Please follow below given steps in order to enable debugging of unit test cases from network drive:<o:p></o:p></p><p class=MsoNormal style='margin-left:.25in'><o:p> </o:p></p><p class=MsoNormal>1.) Open window : Control Panel > AdministrativeTools > .NET Framework Configuration <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikeTlxN9zXoaAk5NboJ3DlqMjn_vzbOWjVzaZQKUUtQLaVQicfWbQyBeLQJrHdt9oGsBjOiMcClLjpAv5WJxqK4EkfaWgRX-3vezCLQt5q_ryF96G90QtbVmZa2VjbzgNzCZ3dCgLwIkU/s1600/image001-714345.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikeTlxN9zXoaAk5NboJ3DlqMjn_vzbOWjVzaZQKUUtQLaVQicfWbQyBeLQJrHdt9oGsBjOiMcClLjpAv5WJxqK4EkfaWgRX-3vezCLQt5q_ryF96G90QtbVmZa2VjbzgNzCZ3dCgLwIkU/s320/image001-714345.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5729710205468864546" /></a><o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>2.) Click on “Edit Code Group Properties” : Set permission Set to “Full Trust” as shown below<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyWUnru6QhqMB8k4YUP70Evr9ZCXJeepz74howi2TszoeOVqL9qhVr25x8Xi4FAInfVAqu0jKt2nGcqI2Yz8q9ZWcbeOhkG2iW2duEmNbv3k_4m_UxqiJ3wRH46XkbFBzj00cysyAW2Tc/s1600/image002-716717.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyWUnru6QhqMB8k4YUP70Evr9ZCXJeepz74howi2TszoeOVqL9qhVr25x8Xi4FAInfVAqu0jKt2nGcqI2Yz8q9ZWcbeOhkG2iW2duEmNbv3k_4m_UxqiJ3wRH46XkbFBzj00cysyAW2Tc/s320/image002-716717.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5729710210028595250" /></a><o:p></o:p></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span style='color:#1F497D'>Namaste!</span><o:p></o:p></p><p class=MsoNormal>Anugrah Atreya <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p></div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com1tag:blogger.com,1999:blog-4256538220587857609.post-27776134031833473992011-10-07T00:14:00.000-07:002011-10-07T00:15:45.479-07:00SQLServer Denali : OFFSET & NEXT Keywords<div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><div><div style='mso-element:para-border-div;border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt;background:white'><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'>Hi All,<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p> </o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'>I found these two new & very useful keywords in SQLServer Denali<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'>OFFSET & NEXT<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p> </o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'>SELECT *<br>FROM [Order Details]<br>ORDER BY [Order ID]<br>OFFSET 20 ROWS<br>FETCH NEXT 7 ROWS ONLY<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p> </o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><a href="http://3.bp.blogspot.com/-OXpKMdthfCc/To6nIet5IvI/AAAAAAAAAXo/uKG3rT1HE_s/s1600/image001-745480.jpg"><img src="http://3.bp.blogspot.com/-OXpKMdthfCc/To6nIet5IvI/AAAAAAAAAXo/uKG3rT1HE_s/s320/image001-745480.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5660645545777636082" /></a></span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p> </o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'>Above script returns records 21 to 27 from the tables [Order Details]. OFFSET clause will skip the first 20 rows and then returns 7 rows. Please note, this T-SQL will throw error if ORDER BY is not used in the query.<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p> </o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'>Reference: PinalDave's blog<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><a href="http://blog.sqlauthority.com/2011/10/07/sql-server-server-side-paging-in-sql-server-ce-compact-edition/">http://blog.sqlauthority.com/2011/10/07/sql-server-server-side-paging-in-sql-server-ce-compact-edition/</a><o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p> </o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'>Namaste!<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#385376'>Anugrah </span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p></o:p></span></p></div></div><p class=MsoNormal align=center style='text-align:center'><span style='font-size:10.5pt;font-family:"Helvetica","sans-serif";color:#666666'><img border=0 width=1 height=1 id="_x0000_i1025" src="http://stats.wordpress.com/b.gif?host=wordpress.com&blog=668536&post=14833&subd=sqlauthority&ref=&email=1&email_o=wpcom"><o:p></o:p></span></p></div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com1tag:blogger.com,1999:blog-4256538220587857609.post-1475658576559943362011-09-23T05:38:00.000-07:002011-09-23T06:25:47.617-07:00Create a .NET Publisher Policy<div style="margin-top: 5pt; margin-bottom: 5pt; ">Vendors of assemblies can state that previously compiled applications/clients should use a newer version of an assembly by including a publisher policy file with the upgraded assembly. The publisher policy file specifies assembly redirection and code base settings, and uses the same format as an application configuration file. The publisher policy file is compiled into an assembly and placed in the global assembly cache.</div> <div style="margin-top: 5pt; margin-bottom: 5pt; ">There are three steps involved in creating a publisher policy: </div> <ol style="margin-top: 0pt; margin-bottom: 0pt; margin-left: 36pt; "> <li style="margin-top: 5pt; margin-bottom: 5pt; ">Create a publisher policy file.</li><li style="margin-top: 5pt; margin-bottom: 5pt; ">Create a publisher policy assembly.</li><li style="margin-top: 5pt; margin-bottom: 5pt; ">Add the publisher policy assembly to the global assembly cache.</li></ol> <div style="margin-top: 5pt; margin-bottom: 5pt; "> </div> <div><font face="Calibri, sans-serif" size="2"><a href="javascript:void(0)"></a><a href="javascript:void(0)"><img src="cid:bd3f5be2-a759-4e60-bd0b-037920779063"><font size="4"><u> </u></font></a><font size="4"><span style="background-color:#C0C0C0"><u>1. C</u></span></font><font size="4"><span style="background-color:#C0C0C0"><u>reat</u></span></font><font size="4"><span style="background-color:#C0C0C0"><u>ing the Publisher Policy </u></span></font><font size="4"><span style="background-color:#C0C0C0"><u>XML</u></span></font><font size="4"><span style="background-color:#C0C0C0"><u> file</u></span></font></font></div> <div style="margin-top: 5pt; margin-bottom: 5pt; ">The schema for publisher policy is described in <a href="http://msdn.microsoft.com/en-us/library/7wd6ex19.aspx"><font color="#0000FF"><u>Redirecting Assembly Versions</u></font></a>. The following example shows a publisher policy file that redirects one version of myAssembly to another.</div> <div><font face="Consolas, monospace" size="2"><configuration></font></div> <div><font face="Consolas, monospace" size="2"> <runtime></font></div> <div><font face="Consolas, monospace" size="2"> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"></font></div> <div><font face="Consolas, monospace" size="2"> <dependentAssembly></font></div> <div><font face="Consolas, monospace" size="2"> <assemblyIdentity name="myAssembly"</font></div> <div><font face="Consolas, monospace" size="2"> publicKeyToken="32ab4ba45e0a69a1"</font></div> <div><font face="Consolas, monospace" size="2"> culture="en-us" /></font></div> <div><font face="Consolas, monospace" size="2"> <!-- Redirecting to version 2.0.0.0 of the assembly. --></font></div> <div><font face="Consolas, monospace" size="2"> <bindingRedirect oldVersion="1.0.0.0"</font></div> <div><font face="Consolas, monospace" size="2"> newVersion="2.0.0.0"/></font></div> <div><font face="Consolas, monospace" size="2"> </dependentAssembly></font></div> <div><font face="Consolas, monospace" size="2"> </assemblyBinding></font></div> <div><font face="Consolas, monospace" size="2"> </runtime></font></div> <div><font face="Consolas, monospace" size="2"></configuration></font></div> <div><font face="Calibri, sans-serif" size="2"> </font></div> <div><font face="Calibri, sans-serif" size="2"> </font></div> <div><font face="Calibri, sans-serif" size="2"><a href="javascript:void(0)"></a><a href="javascript:void(0)"><img src="cid:7d5e7bda-e7ce-4f88-a247-a3f52edd2ef3"><u> </u></a><font size="4"><span style="background-color:#C0C0C0"><u>2. C</u></span></font><font size="4"><span style="background-color:#C0C0C0"><u>reat</u></span></font><font size="4"><span style="background-color:#C0C0C0"><u>ing the Publisher Policy Assembly</u></span></font></font></div> <div><font face="Calibri, sans-serif" size="2"><img src="rtfimage://" width="83" height="41"></font></div> <div style="margin-top: 5pt; margin-bottom: 5pt; ">Use the <a href="http://msdn.microsoft.com/en-us/library/c405shex.aspx"><font color="#0000FF"><u>Assembly Linker (Al.exe)</u></font></a> to create the publisher policy assembly.</div> Type the following command at the command prompt: <b>al /link:</b> <i>publisherPolicyFile</i> <b>/out:</b><i>publisherPolicyAssemblyFile</i> <b>/keyfile:</b><i>keyPairFile</i> <b>/platform:</b><i>processorArchitecture</i>
<br />In this command: <ul style="margin-top: 0pt; margin-bottom: 0pt; margin-left: 5pt; "> <li style="margin-top: 5pt; margin-bottom: 5pt; ">The <i>publisherPolicyFile</i> argument is the name of the publisher policy file.</li><li style="margin-top: 5pt; margin-bottom: 5pt; ">The <i>publisherPolicyAssemblyFile</i> argument is the name of the publisher policy assembly that results from this command. The assembly file name must follow the format: </li></ul> <div style="margin-top: 5pt; margin-bottom: 5pt; padding-left: 72pt; "><b>policy.</b> <i>majorNumber</i> <b>.</b> <i>minorNumber</i> <b>.</b> <i>mainAssemblyName</i> <b>.dll</b> </div> <ul style="margin-top: 0pt; margin-bottom: 0pt; margin-left: 5pt; "> <li style="margin-top: 5pt; color:brown; margin-bottom: 5pt; ">The <i>keyPairFile</i>: You must sign the assembly and publisher policy assembly with the same .snk file</li><li style="margin-top: 5pt; margin-bottom: 5pt; ">The <i>processorArchitecture</i> argument identifies the platform targeted by a processor-specific assembly. It can be amd64, ia64, msil, or x86.</li></ul> <div style="margin-top: 5pt; margin-bottom: 5pt; padding-left: 36pt; ">The following command creates a publisher policy assembly called policy.1.0.myAssembly from a publisher policy file called pub.config, assigns a strong name to the assembly using the key pair in the sgKey.snk file, and specifies that the assembly targets the x86 processor architecture. </div> <div style="padding-left: 36pt; "><font face="Consolas, monospace" size="2">al /link:pub.config /out:policy.1.0.myAssembly.dll /keyfile:sgKey.snk /platform:x86</font></div> <div style="margin-top: 5pt; margin-bottom: 5pt; padding-left: 36pt; ">The publisher policy assembly must match the processor architecture of the assembly that it applies to. Thus, if your assembly has a <a href="http://msdn.microsoft.com/en-us/library/system.reflection.assemblyname.processorarchitecture.aspx"><font color="#0000FF"><u>ProcessorArchitecture</u></font></a> value of MSIL, the publisher policy assembly for that assembly must be created with /platform:anycpu. You must provide a separate publisher policy assembly for each processor-specific assembly.</div> <div style="margin-top: 5pt; margin-bottom: 5pt; padding-left: 36pt; ">A consequence of this rule is that in order to change the processor architecture for an assembly, you must change the major or minor component of the version number, so that you can supply a new publisher policy assembly with the correct processor architecture. The old publisher policy assembly cannot service your assembly once your assembly has a different processor architecture.</div> <div style="margin-top: 5pt; margin-bottom: 5pt; padding-left: 36pt; ">Another consequence is that the version 2.0 linker cannot be used to create a publisher policy assembly for an assembly compiled using earlier versions of the .NET Framework, because it always specifies processor architecture.</div> <div style="margin-top: 5pt; margin-bottom: 5pt; padding-left: 36pt; "> </div> <div><font face="Calibri, sans-serif" size="2"><a href="javascript:void(0)"></a><a href="javascript:void(0)"><img src="cid:693f2266-5496-4f61-b3b2-9c4561f0bd0d"> </a><font size="4"><span style="background-color:#C0C0C0"><u>3. A</u></span></font><font size="4"><span style="background-color:#C0C0C0"><u>dding the Publisher Policy Assembly to the Global Assemb</u></span></font><font size="4"><span style="background-color:#C0C0C0"><u>ly Cache</u></span></font></font></div> <div><font face="Calibri, sans-serif" size="2"><img src="rtfimage://" width="83" height="41"></font></div> <ul style="margin-top: 0pt; margin-bottom: 0pt; margin-left: 5pt; "> <li style="margin-top: 5pt; margin-bottom: 5pt; ">Drag the policy dll to GAC or do it using <b>gacutil</b> command from console</li><li style="margin-top: 5pt; color:brown; margin-bottom: 5pt; ">Make sure that the publisher policy assembly cannot be added to the global assembly cache unless the original publisher policy file is located in the same directory as the assembly. </li></ul> <div><font face="Calibri, sans-serif" size="2"> </font></div> <div><font face="Verdana, sans-serif" size="2">Namaste!</font></div> <div><font face="Verdana, sans-serif" size="2" color="#385376">Anugrah Atreya</font></div> <div><font face="Calibri, sans-serif" size="2"> </font></div> </font> Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-7226141932927913662011-09-22T23:50:00.000-07:002011-09-22T23:51:03.099-07:00ASP.NET : MVC3 : Introduction<font face="Calibri, sans-serif" size="2"> <div>Hi All,</div> <div> </div> <div>If you are an ASP.NET developer and not yet got a chance to catch up with MVC3 introduced with .Net 4.0</div> <div>Here is a must watch presentation on MVC3 (using new Razor View)</div> <div> </div> <div><a href="http://www.pluralsight-training.net/microsoft/players/PSODPlayer.aspx?author=scott-allen&name=mvc3-building-intro&mode=live&clip=0&course=aspdotnet-mvc3-intro"><font color="#0000FF"><u>http://www.pluralsight-training.net/microsoft/players/PSODPlayer.aspx?author=scott-allen&name=mvc3-building-intro&mode=live&clip=0&course=aspdotnet-mvc3-intro</u></font></a></div> <div> </div> <div><font face="Verdana, sans-serif" size="2">Namaste!</font></div> <div><font face="Verdana, sans-serif" size="2" color="#385376">Anugrah Atreya</font></div> <div> </div> </font> Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com1tag:blogger.com,1999:blog-4256538220587857609.post-13116589041610049482011-09-16T00:44:00.001-07:002011-09-16T00:44:50.660-07:00SQLServer : XML Shredding : Open XML : Part 1<div class=WordSection1><div style='mso-element:para-border-div;border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'><p class=MsoNormal style='border:none;padding:0in'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>In previous post we saw how we can get XML out of SQL Server.<o:p></o:p></span></p><p class=MsoNormal style='border:none;padding:0in'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>In this post we will see the reverse of same i.e. how we can put XML back to SQL server.<o:p></o:p></span></p><p class=MsoNormal style='border:none;padding:0in'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='border:none;padding:0in'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Over to Pinal Dave's original post below<o:p></o:p></span></p><p class=MsoNormal style='border:none;padding:0in'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'>Namaste !<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black;background:red'> </span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'> </span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#385376'>Anugrah Atreya<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#385376'><o:p> </o:p></span></p></div><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'><o:p> </o:p></span></b></p><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Journey to SQLAuthority [mailto:no-reply@wordpress.com] <br><b>Sent:</b> Saturday, September 03, 2011 12:21 PM<br><b>To:</b> Anugrah Atreya<br><b>Subject:</b> Daily digest for September 3, 2011<o:p></o:p></span></p></div></div><div><div style='border:solid #CCCCCC 1.0pt;padding:12.0pt 12.0pt 12.0pt 12.0pt;margin-left:12.0pt;margin-top:.25in;margin-right:12.0pt;margin-bottom:6.0pt;-webkit-border-radius: 5px;-moz-border-radius: 5px;border-radius: 5px'><table class=MsoNormalTable border=0 cellpadding=0 width="86%" style='width:86.94%;margin-left:51.0pt'><tr><td width=57 valign=top style='width:42.75pt;padding:.75pt .75pt .75pt .75pt'><p class=MsoNormal style='margin-right:5.25pt'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><a href="http://blog.sqlauthority.com/author/pinaldave/"><img border=0 width=48 height=48 id="_x0000_i1025" src="http://0.gravatar.com/avatar/48aa5a2264e8a27d802bb22ab6ccf688?s=48&d=identicon"></a><o:p></o:p></span></p></td><td valign=top style='padding:.75pt .75pt .75pt .75pt'><h2 style='margin:0in;margin-bottom:.0001pt'><span style='font-size:15.0pt;font-family:"Arial","sans-serif";color:#555555'><a href="http://blog.sqlauthority.com/2011/09/03/sql-server-tips-from-the-sql-joes-2-pros-development-series-preparing-xml-in-memory-day-34-of-35/">SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Preparing XML in Memory - Day 34 of 35</a> <o:p></o:p></span></h2><div style='margin-top:3.0pt'><p class=MsoNormal><strong><span style='font-size:8.5pt;font-family:"Arial","sans-serif";color:#999999'><a href="http://blog.sqlauthority.com/author/pinaldave/">pinaldave</a></span></strong><span style='font-size:8.5pt;font-family:"Arial","sans-serif";color:#999999'> | September 3, 2011 at 7:00 am | Categories: <a href="http://blog.sqlauthority.com/?cat=61617424">Joes 2 Pros</a>, <a href="http://blog.sqlauthority.com/?cat=1054439">Pinal Dave</a>, <a href="http://blog.sqlauthority.com/?cat=9625683">PostADay</a>, <a href="http://blog.sqlauthority.com/?cat=10346">SQL</a>, <a href="http://blog.sqlauthority.com/?cat=648577">SQL Authority</a>, <a href="http://blog.sqlauthority.com/?cat=311850">SQL Query</a>, <a href="http://blog.sqlauthority.com/?cat=1054425">SQL Scripts</a>, <a href="http://blog.sqlauthority.com/?cat=6407">SQL Server</a>, <a href="http://blog.sqlauthority.com/?cat=467219">SQL Tips and Tricks</a>, <a href="http://blog.sqlauthority.com/?cat=31191">T SQL</a>, <a href="http://blog.sqlauthority.com/?cat=6">Technology</a> | URL: <a href="http://wp.me/p2NUQ-3GE">http://wp.me/p2NUQ-3GE</a> <o:p></o:p></span></p></div></td></tr></table><h2 style='text-align:justify;mso-line-height-alt:16.8pt;background:white'><span style='font-family:"Arial","sans-serif"'>Consuming XML<o:p></o:p></span></h2><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Anytime you turn XML into another data format (e.g., into a SQL Server table) that process is called <strong><span style='font-family:"Arial","sans-serif"'>shredding XML</span></strong>. Before you can shred your XML, you must first prepare it. <strong><span style='font-family:"Arial","sans-serif"'>Preparing XML</span></strong> for SQL means storing the XML in a memory space where a query can retrieve and make use of the data. SQL Server 2008 provides native XML support to make streaming and shredding XML more robust.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=498 height=272 id="_x0000_i1038" src="cid:image001.png@01CC7472.8F18E950" alt="http://www.pinaldave.com/bimg/j2p/j2p_33_2.png"></span><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Think of XML as a big meal for SQL. While we essentially added just one line of code to our Select query to turn tabular data into an XML document, going the other direction is more complex. Behind the scenes, SQL Server uses additional memory when consuming XML. Our preparation will include a step to parse and store the XML document in memory. And the same way a big feast requires additional cleanup, we must include a step to remove the XML document from the memory cache once it is no longer needed.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Now that we understand the essential rules and components of well-formed XML documents, we will turn our attention toward consuming XML data. In the next posts we will see an advanced method (nodes) for shredding XML. Right now we want you to be familiar with the terms used when going back and forth from SQL to XML.<o:p></o:p></span></p><h2 style='text-align:justify;mso-line-height-alt:16.8pt;background:white'><span style='font-family:"Arial","sans-serif"'>Preparing XML in Memory<o:p></o:p></span></h2><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>If you want to take XML data and create a result set in SQL Server, you must first store the XML in memory. The process of preparing XML in SQL includes storing the XML in memory and processing the XML so that all the data and metadata is ready and available for you to query.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Recall that element levels in your XML document appear in the same order that tables appear in your SELECT list and are named according to any aliases you may have chosen (e.g., cust, Order, OrderDetail).<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 height=132 id="_x0000_i1026" src="http://www.pinaldave.com/bimg/j2p/j2p_34_1.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>A related query from the figure below helps to illustrate our root element (cust) having a top-level element (Order) and a child-level element (OrderDetail). There is a 1:Many (One-to-Many) relationship between the root node and the lower elements.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 height=276 id="_x0000_i1027" src="http://www.pinaldave.com/bimg/j2p/j2p_34_2.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Copy your XML result into a brand new query window and enclose it in single quotes (see figure below). Let's briefly digress for another comparison which may be helpful for our next step. Preparing to have your XML document shredded by SQL Server is a bit like the steps you take when having your tailor create a custom garment for you. One of my students recently underwent this process. Her favorite tailor, Kim, had the design for a poncho she liked. The tailor sent my student to the fabric store to select the particular fabric and trim she wanted for her poncho.When she brought the fabric and trim to Kim's shop, Kim took the fabric, wrote up an order slip, and then gave my student a claim ticket and said her poncho would be done in a week. You stored your garment at the tailor and when you want to use it you must show your claim ticket.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 height=281 id="_x0000_i1028" src="http://www.pinaldave.com/bimg/j2p/j2p_34_3.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>This tailor runs a small neighborhood shop but is always very busy due to the high quality of her work. While Kim could eventually have located the order without the benefit of the claim ticket, my conscientious student made very sure to bring her ticket when she returned the following week. She submitted her claim ticket and in exchange she was handed her lovely new hand-made garment.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 height=231 id="_x0000_i1029" src="http://www.pinaldave.com/bimg/j2p/j2p_34_4.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Much the same way, when you send an XML document to be tailored into memory, SQL Server gives you a claim number (called a handle) which you need later when referring to that document. We will send our XML document into memory and in exchange we will get back the handle in the form of an integer. To send our document into memory, we first need to declare an XML variable. In order for this variable to contain our XML document, we will set it equal to our XML.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 height=107 id="_x0000_i1030" src="http://www.pinaldave.com/bimg/j2p/j2p_34_5.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>The @Doc variable's data type is XML. Next we will declare the variable @hDoc, which we know will be an integer because it is the variable which will act as our document handle (i.e., our "claim ticket"). We will also use sp_XML_PrepareDocument, a system-supplied stored procedure which reads our XML document (@Doc), parses it, and makes it available for SQL's use.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 height=48 id="_x0000_i1031" src="http://www.pinaldave.com/bimg/j2p/j2p_34_6.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>When we send our XML to SQL Server's internal cache, we will receive a number which functions as our "claim ticket." Run all of the code together, including a SELECT statement to display the document handle (i.e., our "claim ticket" which SQL Server provides in exchange for the XML document). Run all of the code together and make note of the handle number, which is 1.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 height=298 id="_x0000_i1032" src="http://www.pinaldave.com/bimg/j2p/j2p_34_7.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Be sure to run this code only once, otherwise you will create multiple handles and instances of your XML document. Avoid accidentally creating unneeded instances of your document and handle.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 height=156 id="_x0000_i1033" src="http://www.pinaldave.com/bimg/j2p/j2p_34_8.png"><o:p></o:p></span></p><h2 style='text-align:justify;mso-line-height-alt:16.8pt;background:white'><span style='font-family:"Arial","sans-serif"'>Using the OpenXML Function<o:p></o:p></span></h2><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>We just sent our prepared XML into the SQL Server's internal cache so that we may pull out the data we want. The OpenXML function provides a rowset view of your XML data. It works with the in-memory copy of the XML document you've stored and provides a view of the data, no longer formatted as XML but with all its parts separated into a large grid. This allows you to query just the data that you need.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>We know the key to accessing the stored XML document is the document handle (like a claim ticket). The first argument needed by the OpenXML function is this value expressed as an integer. The second argument is the <em><span style='font-family:"Arial","sans-serif"'>rowpattern</span></em> hint for the data we wish to see.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>After declaring an integer variable and setting it equal to 1 (i.e., the value of our document handle, from 2 figures ago), we can use a SELECT statement to query the result set of the OpenXML function. The variable @iNum is the first parameter. The second parameter '/cust/Order/OrderDetail' specifies that we wish to see data for the OrderDetail element level.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 height=249 id="_x0000_i1034" src="http://www.pinaldave.com/bimg/j2p/j2p_34_9.png"><o:p></o:p></span></p><h2 style='text-align:justify;mso-line-height-alt:16.8pt;background:white'><span style='font-family:"Arial","sans-serif"'>Rowpattern<o:p></o:p></span></h2><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Since XML can have root tags, top level tags, and many levels of child tags, <em><span style='font-family:"Arial","sans-serif"'>rowpatterns </span></em>are needed to figure out which level represent your row data. A <em><span style='font-family:"Arial","sans-serif"'>rowpattern</span></em> is an XPath pattern telling your query where to look for the data that you want to see in your result.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>In our current example, the <em><span style='font-family:"Arial","sans-serif"'>rowpattern</span></em> hint ('/cust/Order/OrderDetail') narrows our query to the attributes found at the OrderDetail element level (see figure above). While the surrounding data isn't immediately interpretable, we can see the text for the ProductID attribute shows a 72, and the text for the Quantity attribute shows a 4 (see lower right corner of the figure above).<o:p></o:p></span></p><h2 style='text-align:justify;mso-line-height-alt:16.8pt;background:white'><span style='font-family:"Arial","sans-serif"'>Shredding One Level<o:p></o:p></span></h2><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Adding the WITH clause to our existing query allows us to pick just the values we wish to see. Our query specifies that we are still interested in data from the OrderDetail element level (see figure below). Our WITH clause lists the field names we want (ProductID, Quantity) from this element level and that these values should be expressed as integer data. In other words, ProductID and Quantity are both integers. The WITH clause allows us to shred data at a single element level.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 height=198 id="_x0000_i1035" src="http://www.pinaldave.com/bimg/j2p/j2p_34_10.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><em><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Note</span></em><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>: If you want to setup the sample JProCo database on your system you can watch this <a href="http://www.joes2pros.com/videos/T-SQL-2008-or-Beginning-SQL-J2P-Vol1/BookSetupLabSteps.wmv">video</a>. For this post you will want to run the SQLInteropChapter1.0Setup.sql script from Volume 5.<o:p></o:p></span></p><h2 style='text-align:justify;mso-line-height-alt:16.8pt;background:white'><span style='font-family:"Arial","sans-serif"'>Question 34<o:p></o:p></span></h2><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>The sp_XML_PrepareDocument stored procedure requires a parameter that is an XML data type. What is the output parameter for?<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:24.0pt;text-align:justify;text-indent:-.25in;mso-list:l0 level1 lfo1;background:white'><![if !supportLists]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><span style='mso-list:Ignore'>1.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>The handle as an INT<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:24.0pt;text-align:justify;text-indent:-.25in;mso-list:l0 level1 lfo1;background:white'><![if !supportLists]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><span style='mso-list:Ignore'>2.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>The handle as an XML<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:24.0pt;text-align:justify;text-indent:-.25in;mso-list:l0 level1 lfo1;background:white'><![if !supportLists]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><span style='mso-list:Ignore'>3.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>The handle as a Varchar<o:p></o:p></span></p><p style='margin-bottom:4.8pt;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><a href="http://blog.sqlauthority.com/2011/09/03/sql-server-tips-from-the-sql-joes-2-pros-development-series-preparing-xml-in-memory-day-34-of-35/#respond">Add a comment to this post</a> <o:p></o:p></span></p></div><p class=MsoNormal><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=1 height=1 id="_x0000_i1036" src="http://stats.wordpress.com/b.gif?host=wordpress.com&blog=668536&post=14176&subd=sqlauthority&ref=&email=1"><o:p></o:p></span></p></div><p class=MsoNormal><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><table class=MsoNormalTable border=0 cellpadding=0 style='max-width: 1024px;min-width: 600px'><tr><td width=80 style='width:60.0pt;padding:.75pt .75pt .75pt .75pt'><p class=MsoNormal style='line-height:19.2pt'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=64 height=64 id="_x0000_i1037" src="http://s.wordpress.org/about/images/logo-grey/grey-m.png" alt=WordPress><o:p></o:p></span></p></td><td style='padding:.75pt .75pt .75pt .75pt'><p style='line-height:19.2pt'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><a href="http://wordpress.com">WordPress.com</a> | Thanks for flying with WordPress!<br><a href="http://subscribe.wordpress.com/?key=5973c0a04e916937043db81e77066bfe&email=anugrah.atreya%40pb.com">Manage Subscriptions</a> | <a href="http://subscribe.wordpress.com/?key=5973c0a04e916937043db81e77066bfe&email=anugrah.atreya%40pb.com&b=0l0%5Bts-vd5fD%3Fsg%2Fgu%26DcbXJRbZFHuFIWzkdVcSOos_wo2g-6">Unsubscribe</a> | <a href="http://wordpress.com/signup/">Reach out to your own subscribers with WordPress.com.</a> <o:p></o:p></span></p></td></tr></table><p class=unsubscribe-url><strong><span style='font-size:7.5pt;font-family:"Arial","sans-serif"'>Trouble clicking? Copy and paste this URL into your browser:</span></strong><span style='font-size:7.5pt;font-family:"Arial","sans-serif"'> <a href="http://subscribe.wordpress.com">http://subscribe.wordpress.com</a> <o:p></o:p></span></p></div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-732351476089811882011-08-30T23:19:00.000-07:002011-08-30T23:20:37.594-07:00SQL Server 2008 : FOR XML RAW<div class=WordSection1><div><div style='mso-element:para-border-div;border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'><p class=MsoNormal style='border:none;padding:0in'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>This is amazing to see how we can get the output of SQL Queries in </span><b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:black'>XML Format</span></b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>.<o:p></o:p></span></p><p class=MsoNormal style='border:none;padding:0in'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>See complete article from Pinal Dave in subscription email below.<o:p></o:p></span></p><p class=MsoNormal style='border:none;padding:0in'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'>Namaste!<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black;background:red'> </span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'> </span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#385376'>Anugrah Atreya</span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p></o:p></span></p></div></div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Journey to SQLAuthority [mailto:no-reply@wordpress.com] <br><b>Sent:</b> Tuesday, August 30, 2011 12:16 PM<br><b>To:</b> Anugrah Atreya<br><b>Subject:</b> Daily digest for August 30, 2011<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div style='border:solid #CCCCCC 1.0pt;padding:12.0pt 12.0pt 12.0pt 12.0pt;margin-left:12.0pt;margin-top:.25in;margin-right:12.0pt;margin-bottom:6.0pt;-webkit-border-radius: 5px;-moz-border-radius: 5px;border-radius: 5px'><table class=MsoNormalTable border=0 cellpadding=0 width="100%" style='width:100.0%'><tr><td width=48 valign=top style='width:.5in;padding:.75pt .75pt .75pt .75pt'><p class=MsoNormal style='margin-right:5.25pt'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><a href="http://blog.sqlauthority.com/author/pinaldave/"><img border=0 width=48 height=48 id="_x0000_i1025" src="http://0.gravatar.com/avatar/48aa5a2264e8a27d802bb22ab6ccf688?s=48&d=identicon"></a><o:p></o:p></span></p></td><td valign=top style='padding:.75pt .75pt .75pt .75pt'><h2 style='margin:0in;margin-bottom:.0001pt'><span style='font-size:15.0pt;font-family:"Arial","sans-serif";color:#555555'><a href="http://blog.sqlauthority.com/2011/08/30/sql-server-tips-from-the-sql-joes-2-pros-development-series-what-is-xml-day-30-of-35/">SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35</a> <o:p></o:p></span></h2><div style='margin-top:3.0pt'><p class=MsoNormal><strong><span style='font-size:8.5pt;font-family:"Arial","sans-serif";color:#999999'><a href="http://blog.sqlauthority.com/author/pinaldave/">pinaldave</a></span></strong><span style='font-size:8.5pt;font-family:"Arial","sans-serif";color:#999999'> | August 30, 2011 at 7:00 am | Categories: <a href="http://blog.sqlauthority.com/?cat=61617424">Joes 2 Pros</a>, <a href="http://blog.sqlauthority.com/?cat=1054439">Pinal Dave</a>, <a href="http://blog.sqlauthority.com/?cat=9625683">PostADay</a>, <a href="http://blog.sqlauthority.com/?cat=10346">SQL</a>, <a href="http://blog.sqlauthority.com/?cat=648577">SQL Authority</a>, <a href="http://blog.sqlauthority.com/?cat=311850">SQL Query</a>, <a href="http://blog.sqlauthority.com/?cat=1054425">SQL Scripts</a>, <a href="http://blog.sqlauthority.com/?cat=6407">SQL Server</a>, <a href="http://blog.sqlauthority.com/?cat=467219">SQL Tips and Tricks</a>, <a href="http://blog.sqlauthority.com/?cat=31191">T SQL</a>, <a href="http://blog.sqlauthority.com/?cat=6">Technology</a> | URL: <a href="http://wp.me/p2NUQ-3FK">http://wp.me/p2NUQ-3FK</a> <o:p></o:p></span></p></div></td></tr></table><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Let's look at another example from the Employee table. If you ran the reset script for this chapter, you should see 14 JProCo employees showing in your Employee table.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 id="_x0000_i1026" src="http://www.pinaldave.com/bimg/j2p/j2p_30_1.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Next we will add FOR XML RAW to view the result from the Employee table as an XML output using the raw mode. We have changed our Employee table result to output as XML RAW. Notice that every row of our XML RAW output is labeled "row" by default.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 id="_x0000_i1027" src="http://www.pinaldave.com/bimg/j2p/j2p_30_2.png"><br><img border=0 width=500 id="_x0000_i1028" src="http://www.pinaldave.com/bimg/j2p/j2p_30_3.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>We next will add a root to our output. We will add the keyword ROOT to our existing code (see figure below) and then look at our revised output. We are adding the keyword ROOT in order to see a root node in our XML output. We now see the <strong><span style='font-family:"Arial","sans-serif"'>root node</span></strong> (a.k.a., the root element). Not only is our output more readable and organized, but this is considered "well-formed XML"<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 id="_x0000_i1029" src="http://www.pinaldave.com/bimg/j2p/j2p_30_4.png"><br><img border=0 width=500 id="_x0000_i1030" src="http://www.pinaldave.com/bimg/j2p/j2p_30_5.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Now let's put the data into elements. We can see each employee now has three sub-elements under the top element, which is "row"<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 id="_x0000_i1031" src="http://www.pinaldave.com/bimg/j2p/j2p_30_6.png"><br><img border=0 width=500 id="_x0000_i1032" src="http://www.pinaldave.com/bimg/j2p/j2p_30_7.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Each row has three child elements (FirstName, LastName, and LocationID). The exception is John Marshbank, who only has two elements. If we query the Employee table, we quickly see the reason for this is that John Marshbank is the only one with a NULL LocationID. John Marshbank has just two sub-elements beneath the top element, "row".<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 id="_x0000_i1033" src="http://www.pinaldave.com/bimg/j2p/j2p_30_8.png"><br><img border=0 width=500 id="_x0000_i1034" src="http://www.pinaldave.com/bimg/j2p/j2p_30_9.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Our mystery is solved – we understand John Marshbank's having just two data sub-elements is caused by his LocationID value having a NULL value. Suppose the program which needs to consume our result requires three data sub-elements. Or suppose company policy specifies that each employee record must contain three data sub-elements. John Marshbank's record doesn't meet the criteria and would thus be in violation of the policy.<o:p></o:p></span></p><h3 style='text-align:justify;line-height:16.8pt;background:white'><span style='font-family:"Arial","sans-serif"'>XSINIL<o:p></o:p></span></h3><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>For fields in SQL Server which include a null value for some records but are populated with regular values in the other records, you will seem to have missing tags for the null record. Often this is alright, as missing tags are presumed to be null. The <strong><span style='font-family:"Arial","sans-serif"'>XSINIL</span></strong> option allows you to force an XML tag to be present for every field in the query, even if the underlying data has NULL field values. Our next example will show us how to make a LocationID tag appear for John Marshbank.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>If you require all tags to be present (even if they have no data), then you can specify the XSINIL option for your XML stream. The XSINIL option will force tags to be present for all fields of all records, including those which contain null values. Let's rerun our prior code and add the XSINIL option.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 id="_x0000_i1035" src="http://www.pinaldave.com/bimg/j2p/j2p_30_10.png"><br><img border=0 width=500 id="_x0000_i1036" src="http://www.pinaldave.com/bimg/j2p/j2p_30_11.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>We now see a third sub-element for John Marshbank. The LocationID tag is no longer missing. It is present and shows the value xsi:nil="true" in place of a LocationID. Our objective has been met: John Marshbank's record now includes three data elements thanks to XSINIL.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><em><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Note</span></em><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>: If you want to setup the sample JProCo database on your system you can watch this <a href="http://www.joes2pros.com/videos/T-SQL-2008-or-Beginning-SQL-J2P-Vol1/BookSetupLabSteps.wmv">video</a>. For this post you will want to run the SQLInteropChapter1.0Setup.sql script from Volume 5.<o:p></o:p></span></p><p style='margin-bottom:4.8pt;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><a href="http://blog.sqlauthority.com/2011/08/30/sql-server-tips-from-the-sql-joes-2-pros-development-series-what-is-xml-day-30-of-35/#respond">Add a comment to this post</a> <o:p></o:p></span></p></div><p class=MsoNormal><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=1 height=1 id="_x0000_i1037" src="http://stats.wordpress.com/b.gif?host=wordpress.com&blog=668536&post=14120&subd=sqlauthority&ref=&email=1"></span><span style='font-size:7.5pt;font-family:"Arial","sans-serif"'> <o:p></o:p></span></p></div></div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com2tag:blogger.com,1999:blog-4256538220587857609.post-36046666786812784582011-08-29T00:01:00.000-07:002011-08-29T00:02:00.845-07:00SQLServer : TABLE Valued Functions<div class=WordSection1><div><div style='mso-element:para-border-div;border:solid windowtext 1.0pt;padding:1.0pt 0in 1.0pt 4.0pt;background:white'><p style='text-align:justify;line-height:16.8pt;background:white;border:none;padding:0in'><code><span style='font-size:9.0pt;color:blue'>SELECT </span></code><code><span style='font-size:9.0pt;color:gray'>* </span></code><code><span style='font-size:9.0pt;color:blue'>FROM </span></code><code><span style='font-size:9.0pt;color:black'>GetCategoryProducts</span></code><code><span style='font-size:9.0pt;color:gray'>(</span></code><code><span style='font-size:9.0pt;color:red'>'Medium-stay'</span></code><code><span style='font-size:9.0pt;color:gray'>)</span></code><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'>Where GetCategoryProducts is not a View or Table but a function which can take input arguments as well.<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p> </o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'>See complete post in subscription email from <a href="http://blog.sqlauthority.com/">http://blog.sqlauthority.com/</a> below.<o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D'>Namaste!</span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p></o:p></span></p><p class=MsoNormal style='background:white;border:none;padding:0in'><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black;background:red'> </span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'> </span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:#385376'>Anugrah Atreya</span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:black'><o:p></o:p></span></p></div></div><div><div style='border:solid #CCCCCC 1.0pt;padding:12.0pt 12.0pt 12.0pt 12.0pt;margin-left:12.0pt;margin-top:.25in;margin-right:12.0pt;margin-bottom:6.0pt;-webkit-border-radius: 5px;-moz-border-radius: 5px;border-radius: 5px'><table class=MsoNormalTable border=0 cellpadding=0 width="93%" style='width:93.24%;margin-left:25.5pt'><tr><td width=57 valign=top style='width:42.75pt;padding:.75pt .75pt .75pt .75pt'><p class=MsoNormal style='margin-right:5.25pt'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><a href="http://blog.sqlauthority.com/author/pinaldave/"><img border=0 width=48 height=48 id="_x0000_i1025" src="http://0.gravatar.com/avatar/48aa5a2264e8a27d802bb22ab6ccf688?s=48&d=identicon"></a><o:p></o:p></span></p></td><td valign=top style='padding:.75pt .75pt .75pt .75pt'><h2 style='margin:0in;margin-bottom:.0001pt'><span style='font-size:15.0pt;font-family:"Arial","sans-serif";color:#555555'><a href="http://blog.sqlauthority.com/2011/08/26/sql-server-tips-from-the-sql-joes-2-pros-development-series-table-valued-functions-day-26-of-35/">SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Table-Valued Functions - Day 26 of 35</a> <o:p></o:p></span></h2><div style='margin-top:3.0pt'><p class=MsoNormal><strong><span style='font-size:8.5pt;font-family:"Arial","sans-serif";color:#999999'><a href="http://blog.sqlauthority.com/author/pinaldave/">pinaldave</a></span></strong><span style='font-size:8.5pt;font-family:"Arial","sans-serif";color:#999999'> | August 26, 2011 at 7:00 am | Categories: <a href="http://blog.sqlauthority.com/?cat=61617424">Joes 2 Pros</a>, <a href="http://blog.sqlauthority.com/?cat=1054439">Pinal Dave</a>, <a href="http://blog.sqlauthority.com/?cat=9625683">PostADay</a>, <a href="http://blog.sqlauthority.com/?cat=10346">SQL</a>, <a href="http://blog.sqlauthority.com/?cat=648577">SQL Authority</a>, <a href="http://blog.sqlauthority.com/?cat=311850">SQL Query</a>, <a href="http://blog.sqlauthority.com/?cat=1054425">SQL Scripts</a>, <a href="http://blog.sqlauthority.com/?cat=6407">SQL Server</a>, <a href="http://blog.sqlauthority.com/?cat=467219">SQL Tips and Tricks</a>, <a href="http://blog.sqlauthority.com/?cat=31191">T SQL</a>, <a href="http://blog.sqlauthority.com/?cat=6">Technology</a> | URL: <a href="http://wp.me/p2NUQ-3F1">http://wp.me/p2NUQ-3F1</a> <o:p></o:p></span></p></div></td></tr></table><p style='line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif";color:#0088CC'><a href="http://amzn.to/pb49jq"><img border=0 width=182 height=247 id="_x0000_i1026" src="http://www.pinaldave.com/bimg/joes2pros4.jpg"></a></span><strong><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Answer simple quiz at the end of the blog post and -</span></strong><span class=MsoHyperlink><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><strong><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Every day one winner from India will get <a href="http://www.flipkart.com/books/1451579489?affid=pinaldavey" target="_blank">Joes 2 Pros Volume 4</a>.</span></strong><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><strong><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Every day one winner from United States will get <a href="http://amzn.to/pb49jq" target="_blank">Joes 2 Pros Volume 4</a>.</span></strong><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><em><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Note</span></em><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>: If you want to setup the sample JProCo database on your system you can watch this <a href="http://www.joes2pros.com/videos/T-SQL-2008-or-Beginning-SQL-J2P-Vol1/BookSetupLabSteps.wmv">video</a>. For this post you will want to run the SQLProgrammingChapter5.1Setup.sql script from Volume 4. <o:p></o:p></span></p><h2 style='text-align:justify;mso-line-height-alt:16.8pt;background:white'><span style='font-family:"Arial","sans-serif"'>Table-Valued Functions<o:p></o:p></span></h2><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Scalar-valued functions return a single value. <strong><span style='font-family:"Arial","sans-serif"'>Table-valued functions</span></strong> return tabular result sets ("tabular" meaning like a table). Table-valued functions look a lot like views because they both show us a tabular result set based on an underlying query. Table-valued functions can be based on one or more base tables.<o:p></o:p></span></p><h3 style='text-align:justify;line-height:16.8pt;background:white'><span style='font-family:"Arial","sans-serif"'>Creating and Implementing Table-Valued Functions<o:p></o:p></span></h3><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>The body of a table-valued function will essentially contain a query. Let's begin with a query containing four fields and all of the records from the CurrentProducts table.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=500 height=186 id="_x0000_i1027" src="http://www.pinaldave.com/bimg/j2p/j2p_26_1.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>This query will become the heart of a new table-valued function, GetAllProducts. By placing the query within the set of parentheses after the keyword RETURN, we have the body of the function. The RETURNS TABLE keyword specifies that the table-valued function GetAllProducts must return the result in the form of a table.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><code><span style='font-size:9.0pt;color:blue'>CREATE FUNCTION </span></code><code><span style='font-size:9.0pt;color:black'>GetAllProducts</span></code><code><span style='font-size:9.0pt;color:gray'>( )</span></code><span style='font-size:9.0pt;font-family:"Courier New";color:gray'><br></span><code><span style='font-size:9.0pt;color:blue'>RETURNS TABLE</span></code><span style='font-size:9.0pt;font-family:"Courier New";color:blue'><br></span><code><span style='font-size:10.0pt;color:blue'>AS</span></code><span style='font-size:9.0pt;font-family:"Courier New";color:blue'><br></span><code><span style='font-size:10.0pt;color:blue'>RETURN</span></code><span style='font-size:9.0pt;font-family:"Courier New";color:blue'><br></span><code><span style='font-size:9.0pt;color:gray'>(</span></code><code><span style='font-size:9.0pt;color:blue'>SELECT </span></code><code><span style='font-size:9.0pt;color:black'>ProductID</span></code><code><span style='font-size:9.0pt;color:gray'>, </span></code><code><span style='font-size:9.0pt;color:black'>ProductName</span></code><code><span style='font-size:9.0pt;color:gray'>, </span></code><code><span style='font-size:9.0pt;color:black'>RetailPrice</span></code><code><span style='font-size:9.0pt;color:gray'>, </span></code><code><span style='font-size:9.0pt;color:black'>Category</span></code><span style='font-size:9.0pt;font-family:"Courier New";color:black'><br></span><code><span style='font-size:9.0pt;color:blue'>FROM </span></code><code><span style='font-size:9.0pt;color:black'>CurrentProducts</span></code><code><span style='font-size:9.0pt;color:gray'>)</span></code><span style='font-size:9.0pt;font-family:"Courier New";color:gray'><br></span><code><span style='font-size:9.0pt;color:black'>GO</span></code><span style='font-size:9.0pt;font-family:"Courier New";color:black'><br></span><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Just how do you query a table-valued function? The syntax is somewhat similar to how you would run a SELECT statement against a table or a view. All functions need to be called by using a set of parentheses with all required parameters inside them. If the function has no parameters (which is currently the case with GetAllProducts), then you will simply include an empty set of parentheses.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=687 height=215 id="_x0000_i1028" src="http://www.pinaldave.com/bimg/j2p/j2p_26_2.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>To view all of the table-valued functions contained in the JProCo database from within the Object Explorer tree, traverse this path:<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>OE > Databases > JProCo > Programmability > Functions > Table-valued Functions<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=433 height=220 id="_x0000_i1029" src="http://www.pinaldave.com/bimg/j2p/j2p_26_3.png"><o:p></o:p></span></p><h3 style='text-align:justify;line-height:16.8pt;background:white'><span style='font-family:"Arial","sans-serif"'>Views versus Parameterized Table-Valued Functions<o:p></o:p></span></h3><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Views and table-valued functions are both useful ways to see the result set for a pre-defined query. There is no way to pass a variable into a view and change the way it runs. Views are hard-coded and their criterion does not change. A table-valued function can display different results by passing values into its parameter(s) at runtime. Let's begin by selecting all 'No-Stay' records from the CurrentProducts table. We want to turn this query into a function and allow that function to pick the category.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=641 height=317 id="_x0000_i1030" src="http://www.pinaldave.com/bimg/j2p/j2p_26_4.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>We're going to enclose our query in parentheses, indent it, and then add some code to create a function. We will create the GetCategoryProducts function which takes a @Category parameter. The query within the table-valued function will predicate on the value passed in when the function is called.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=505 height=201 id="_x0000_i1031" src="http://www.pinaldave.com/bimg/j2p/j2p_26_5.png"><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Change the parameter value to 'Medium-stay' and run this query. The GetCategoryProducts function now returns164 records.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><code><span style='font-size:9.0pt;color:blue'>SELECT </span></code><code><span style='font-size:9.0pt;color:gray'>* </span></code><code><span style='font-size:9.0pt;color:blue'>FROM </span></code><code><span style='font-size:9.0pt;color:black'>GetCategoryProducts</span></code><code><span style='font-size:9.0pt;color:gray'>(</span></code><code><span style='font-size:9.0pt;color:red'>'Medium-stay'</span></code><code><span style='font-size:9.0pt;color:gray'>)</span></code><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Whenever you call a function, you must remember to use a set of parentheses and include the parameter(s) which the function expects. Let's demonstrate the error message which results from forgetting to include the needed parameter within the parentheses. SQL Server's error message tells us that our code doesn't match the function's parameter signature. In other words, it reminds us that we need to specify a category.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><code><span style='font-size:9.0pt;color:blue'>SELECT </span></code><code><span style='font-size:9.0pt;color:gray'>* </span></code><code><span style='font-size:9.0pt;color:blue'>FROM </span></code><code><span style='font-size:9.0pt;color:black'>GetCategoryProducts</span></code><code><span style='font-size:9.0pt;color:gray'>()</span></code><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif";color:red'>Msg 313, Level 16, State 3, Line 1</span><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><br><span style='color:red'>An insufficient number of arguments were supplied for the procedure or function GetCategoryProducts.</span><o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><em><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Note</span></em><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>: If you want to setup the sample JProCo database on your system you can watch this <a href="http://www.joes2pros.com/videos/T-SQL-2008-or-Beginning-SQL-J2P-Vol1/BookSetupLabSteps.wmv">video</a>. For this post you will want to run the SQLProgrammingChapter8.1Setup.sql script from Volume 4.<o:p></o:p></span></p><h2 style='text-align:justify;mso-line-height-alt:16.8pt;background:white'><span style='font-family:"Arial","sans-serif"'>Question 26<o:p></o:p></span></h2><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>You need to create two functions that will each return a scalar result of the number of hours each user has logged for: 1) the current day, and 2) month to date. You will pass in the user ID as a parameter value. What two things must you do?<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:24.0pt;text-align:justify;text-indent:-.25in;mso-list:l0 level1 lfo2;background:white'><![if !supportLists]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><span style='mso-list:Ignore'>1.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Create a function that returns a list of values representing the login times for a given user.<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:24.0pt;text-align:justify;text-indent:-.25in;mso-list:l0 level1 lfo2;background:white'><![if !supportLists]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><span style='mso-list:Ignore'>2.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Create a function that returns a list of values representing the people who have logged more hours than the current user has logged.<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:24.0pt;text-align:justify;text-indent:-.25in;mso-list:l0 level1 lfo2;background:white'><![if !supportLists]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><span style='mso-list:Ignore'>3.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;margin-left:24.0pt;text-align:justify;text-indent:-.25in;mso-list:l0 level1 lfo2;background:white'><![if !supportLists]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><span style='mso-list:Ignore'>4.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Create a function that returns a number value representing the number of hours that a user has logged for the current month.<o:p></o:p></span></p><h3 style='text-align:justify;line-height:16.8pt;background:white'><strong><span style='font-family:"Arial","sans-serif"'>Rules:</span></strong><span style='font-family:"Arial","sans-serif"'><o:p></o:p></span></h3><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Please leave your answer in comment section below with correct option, explanation and your country of resident.<br>Every day one winner will be announced from United States.<br>Every day one winner will be announced from India.<br><strong><span style='font-family:"Arial","sans-serif"'>A valid answer must contain country of residence of answerer.</span></strong><br>Please check my <strong><span style='font-family:"Arial","sans-serif"'><a href="http://www.facebook.com/SQLAuth?sk=app_179191362131547" target="_blank">facebook page</a></span></strong> for winners name and correct answer.<br><strong><span style='font-family:"Arial","sans-serif"'>Every day one winner from India will get <a href="http://www.flipkart.com/books/1451579489?affid=pinaldavey" target="_blank">Joes 2 Pros Volume 4</a>.</span></strong><br><strong><span style='font-family:"Arial","sans-serif"'>Every day one winner from United States will get <a href="http://amzn.to/pb49jq" target="_blank">Joes 2 Pros Volume 4</a>.</span></strong><br>The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.<o:p></o:p></span></p><p style='text-align:justify;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'>Reference: <strong><span style='font-family:"Arial","sans-serif"'>Pinal Dave (<a href="http://blog.sqlauthority.com/" target="_blank">http://blog.SQLAuthority.com</a>)</span></strong><o:p></o:p></span></p><p style='margin-bottom:4.8pt;line-height:16.8pt;background:white'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><a href="http://blog.sqlauthority.com/2011/08/26/sql-server-tips-from-the-sql-joes-2-pros-development-series-table-valued-functions-day-26-of-35/#respond">Add a comment to this post</a> <o:p></o:p></span></p></div><p class=MsoNormal><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=1 height=1 id="_x0000_i1032" src="http://stats.wordpress.com/b.gif?host=wordpress.com&blog=668536&post=14075&subd=sqlauthority&ref=&email=1"><o:p></o:p></span></p></div><p class=MsoNormal><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><table class=MsoNormalTable border=0 cellpadding=0 style='max-width: 1024px;min-width: 600px'><tr><td width=80 style='width:60.0pt;padding:.75pt .75pt .75pt .75pt'><p class=MsoNormal style='line-height:19.2pt'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><img border=0 width=64 height=64 id="_x0000_i1033" src="http://s.wordpress.org/about/images/logo-grey/grey-m.png" alt=WordPress><o:p></o:p></span></p></td><td style='padding:.75pt .75pt .75pt .75pt'><p style='line-height:19.2pt'><span style='font-size:9.5pt;font-family:"Arial","sans-serif"'><a href="http://wordpress.com">WordPress.com</a> | Thanks for flying with WordPress!<br><a href="http://subscribe.wordpress.com/?key=5973c0a04e916937043db81e77066bfe&email=anugrah.atreya%40pb.com">Manage Subscriptions</a> | <a href="http://subscribe.wordpress.com/?key=5973c0a04e916937043db81e77066bfe&email=anugrah.atreya%40pb.com&b=YCuz%2BJsZ%7EXh9bUNAkh%2BQ%7CRKEyKSt5Ou%26gbLI-Y%2FnDE.as_Y75">Unsubscribe</a> | <a href="http://wordpress.com/signup/">Reach out to your own subscribers with WordPress.com.</a> <o:p></o:p></span></p></td></tr></table><p class=unsubscribe-url><strong><span style='font-size:7.5pt;font-family:"Arial","sans-serif"'>Trouble clicking? Copy and paste this URL into your browser:</span></strong><span style='font-size:7.5pt;font-family:"Arial","sans-serif"'> <a href="http://subscribe.wordpress.com">http://subscribe.wordpress.com</a> <o:p></o:p></span></p></div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-26697351992874578762011-08-05T00:02:00.000-07:002011-08-05T00:03:26.359-07:00FW: CQRS<div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>We think we know everything about Design Patterns, but it's a continuously evolving world……<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Found this interesting ppt on CQRS<o:p></o:p></span></p><p class=MsoNormal><a href="http://www.slideshare.net/dennisdoomen/cqrs-and-event-sourcing-an-alternative-architecture-for-ddd?from=ss_embed">http://www.slideshare.net/dennisdoomen/cqrs-and-event-sourcing-an-alternative-architecture-for-ddd?from=ss_embed</a><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>Feed:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Martin Fowler<br><b>Posted on:</b> Thursday, July 14, 2011 7:03 PM<br><b>Author:</b> Martin Fowler<br><b>Subject:</b> Bliki: CQRS<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><table class=MsoNormalTable border=0 cellpadding=0><tr><td style='padding:.75pt .75pt .75pt .75pt'><p><span style='font-family:"Calibri","sans-serif"'>CQRS stands for <b>Command Query Responsibility Segregation</b>. It's a pattern that I first heard described by <a href="http://codebetter.com/gregyoung/">Greg Young</a>. At its heart is a simple notion that you can use a different model to update information than the model you use to read information. This simple notion leads to some profound consequences for the design of information systems.<o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>The mainstream approach people use for interacting with an information system is to treat it as a CRUD datastore. By this I mean that we have mental model of some record structure where we can <b>c</b>reate new records, <b>r</b>ead records, <b>u</b>pdate existing records, and <b>d</b>elete records when we're done with them. In the simplest case, our interactions are all about storing and retrieving these records.<o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>As our needs become more sophisticated we steadily move away from that model. We may want to look at the information in a different way to the record store, perhaps collapsing multiple records into one, or forming virtual records by combining information for different places. On the update side we may find validation rules that only allow certain combinations of data to be stored, or may even infer data to be stored that's different from that we provide.<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri","sans-serif"'><img border=0 id="_x0000_i1025" src="http://martinfowler.com/bliki/images/cqrs/single-model.png"><o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>As this occurs we begin to see multiple representations of information. When users interact with the information they use various presentations of this information, each of which is a different representation. Developers typically build their own conceptual model which they use to manipulate the core elements of the model. If you're using a Domain Model, then this is usually the conceptual representation of the domain. You typically also make the persistent storage as close to the conceptual model as you can.<o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>This structure of multiple layers of representation can get quite complicated, but when people do this they still resolve it down to a single conceptual representation which acts as a conceptual integration point between all the presentations.<o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>The change that CQRS introduces is to split that conceptual model into separate models for update and display, which it refers to as Command and Query respectively following the vocabulary of <a href="http://martinfowler.com/bliki/CommandQuerySeparation.html">CommandQuerySeparation</a>. The rationale is that for many problems, particularly in more complicated domains, having the same conceptual model for commands and queries leads to a more complex model that does neither well.<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri","sans-serif"'><img border=0 id="_x0000_i1026" src="http://martinfowler.com/bliki/images/cqrs/cqrs.png"><o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>By separate models we most commonly mean different object models, probably running in different logical processes, perhaps on separate hardware. A web example would see a user looking at a web page that's rendered using the query model. If they initiate a change that change is routed to the separate command model for processing, the resulting change is communicated to the query model to render the updated state.<o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>There's room for considerable variation here. The in-memory models may share the same database, in which case the database acts as the communication between the two models. However they may also use separate databases, effectively making the query-side's database by a real-time <a href="http://martinfowler.com/bliki/ReportingDatabase.html">ReportingDatabase</a>. In this case there needs to be some communication mechanism between the two models or their databases.<o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>The two models might not be separate object models, it could be that the same objects have different interfaces for their command side and their query side, rather like views in relational databases. But usually when I hear of CQRS, they are clearly separate models. <o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>CQRS naturally fits with some other architectural patterns. <o:p></o:p></span></p><ul type=disc><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo3'><span style='font-family:"Calibri","sans-serif"'>As we move away from a single representation that we interact with via CRUD, we can easily move to a task-based UI.<o:p></o:p></span></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo3'><span style='font-family:"Calibri","sans-serif"'>Interacting with the command-model naturally falls into commands or events, which meshes well with <a href="http://martinfowler.com/eaaDev/EventSourcing.html">Event Sourcing</a>.<o:p></o:p></span></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo3'><span style='font-family:"Calibri","sans-serif"'>Having separate models raises questions about how hard to keep those models consistent, which raises the likelihood of using <a href="http://www.allthingsdistributed.com/2008/12/eventually_consistent.html">eventual consistency</a>.<o:p></o:p></span></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo3'><span style='font-family:"Calibri","sans-serif"'>For many domains, much of the logic is needed when you're updating, so it may make sense to use <a href="http://martinfowler.com/bliki/EagerReadDerivation.html">EagerReadDerivation</a> to simplify your query-side models.<o:p></o:p></span></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo3'><span style='font-family:"Calibri","sans-serif"'>CQRS is suited to complex domains, the kind that also benefit from <a href="http://www.amazon.com/gp/product/0321125215?ie=UTF8&tag=martinfowlerc-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0321125215">Domain-Driven Design</a><img border=0 width=1 height=1 id="_x0000_i1027" src="http://www.assoc-amazon.com/e/ir?t=martinfowlerc-20&l=as2&o=1&a=0321601912">.<o:p></o:p></span></li></ul><div class=MsoNormal align=center style='text-align:center'><span style='font-family:"Calibri","sans-serif"'><hr size=2 width="100%" align=center></span></div><h2><a name=WhenToUseIt><span style='font-family:"Calibri","sans-serif"'>When to use it</span></a><span style='font-family:"Calibri","sans-serif"'><o:p></o:p></span></h2><p><span style='font-family:"Calibri","sans-serif"'>Like any pattern, CQRS is useful in some places, but not in others. Many systems do fit a CRUD mental model, and so should be done in that style. CQRS is a significant mental leap for all concerned, so shouldn't be tackled unless the benefit is worth the jump.<o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>In particular CQRS <span style='background:lime;mso-highlight:lime'>should only be used on specific portions of a system</span> (a <a href="http://domaindrivendesign.org/node/91">Bounded Context</a> in DDD lingo) and not the the system as a whole. In this way of thinking, each Bounded Context needs its own decisions on how it should be modeled.<o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>So far I see benefits in two directions. Firstly is handling complexity - a complex domain may be easier to tackle by using CQRS. I do have to hedge this, usually there's enough overlap between the command and query sides that sharing a model is easier. Each domain has different characteristics.<o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>The other main benefit is in handling high performance applications. CQRS allows you to separate the load from reads and writes allowing you to scale each independently. If your application sees a big disparity between reads and writes this is very handy. Even without that, you can apply different optimization strategies to the two sides. An example of this is using different database access techniques for read and update.<o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>If your domain isn't suited to CQRS, but you have demanding queries that add complexity or performance problems, remember that <span style='background:lime;mso-highlight:lime'>you can still use</span> <span style='background:lime;mso-highlight:lime'>a <a href="http://martinfowler.com/bliki/ReportingDatabase.html">ReportingDatabase</a>. CQRS uses a separate model for all queries.</span> With a reporting database you still use your main system for most queries, but offload the more demanding ones to the reporting database.<o:p></o:p></span></p><p><span style='font-family:"Calibri","sans-serif"'>It's also true that we haven't seen enough uses of CQRS in the field yet to be confident that we understand its pros and cons. So while CQRS is a pattern I'd certainly want in my toolbox, I wouldn't keep it at the top.<o:p></o:p></span></p><div class=MsoNormal align=center style='text-align:center'><span style='font-family:"Calibri","sans-serif"'><hr size=2 width="100%" align=center></span></div><h2><a name=FurtherReading><span style='font-family:"Calibri","sans-serif"'>Further Reading</span></a><span style='font-family:"Calibri","sans-serif"'><o:p></o:p></span></h2><ul type=disc><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l2 level1 lfo6'><span style='font-family:"Calibri","sans-serif"'><a href="http://codebetter.com/gregyoung/">Greg Young</a> was the first person I heard talking about this approach - this is <a href="http://codebetter.com/gregyoung/2010/02/16/cqrs-task-based-uis-event-sourcing-agh/">the summary from him</a> that I like best.<o:p></o:p></span></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l2 level1 lfo6'><span style='font-family:"Calibri","sans-serif"'>Udi Dahan is another advocate of CQRS, he has a <a href="http://www.udidahan.com/2009/12/09/clarified-cqrs/">detailed description </a>of the technique.<o:p></o:p></span></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l2 level1 lfo6'><span style='font-family:"Calibri","sans-serif"'>There is a <a href="http://cqrsinfo.com">CQRS info</a> site that Greg set up. Its <a href="http://cqrsinfo.com/documents/">documents section</a> contains some interesting material. <o:p></o:p></span></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l2 level1 lfo6'><span style='font-family:"Calibri","sans-serif"'>There is an <a href="http://groups.google.com/group/dddcqrs">active mailing list</a> to discuss the approach.<o:p></o:p></span></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l2 level1 lfo6'><span style='font-family:"Calibri","sans-serif"'>Greg Young is working on a book on CQRS.<o:p></o:p></span></li></ul></td></tr></table><p><span style='font-family:"Calibri","sans-serif"'><br><a href="http://martinfowler.com/bliki/CQRS.html">View article...</a><o:p></o:p></span></p></div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-17283777827069946082011-08-03T03:07:00.000-07:002011-08-03T03:08:02.295-07:00Installing a Generic Text Printer<div class=Section1> <p class=MsoNormal><span style='font-size:8.5pt;font-family:"Verdana","sans-serif"; color:black'>You can use the instructions below to install the Windows Generic / Text Only printer driver.<o:p></o:p></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'><span style='mso-list:Ignore'>1.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif"; color:black'>Click <b>Start</b> | <b>Control Panel</b> | <b>Printers and Faxes</b>. If you do not see <b>Control Panel</b> after you click <b>Start</b>, click <b>Start</b> | <b>Settings</b> | <b>Control Panel</b> | <b>Printers and Faxes</b>.<o:p></o:p></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'><span style='mso-list:Ignore'>2.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif"; color:black'>On the <b>Printers and Faxes</b> window, double-click <b>Add a Printer</b>.<o:p></o:p></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'><span style='mso-list:Ignore'>3.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif"; color:black'>On the <b>Add Printer Wizard</b> window, click <b>Next</b>.<o:p></o:p></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'><span style='mso-list:Ignore'>4.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif"; color:black'>On the <b>Local or Network</b> window, check <b>Local printer attached to this computer</b> and uncheck <b>Automatically detect and install my Plug and Play printer</b>. Click <b>Next</b> to continue.<o:p></o:p></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'><span style='mso-list:Ignore'>5.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif"; color:black'>On the <b>Select a Printer Port</b> window, select <b>FILE: (Print to File)</b> from the <b>Use the following port:</b> drop-down list. Click <b>Next</b> to continue.<o:p></o:p></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'><span style='mso-list:Ignore'>6.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif"; color:black'>On the <b>Install Printer Software</b> window, click <b>Generic</b> from the <b>Manufacturer</b> pane. Click <b>Generic / Text Only</b> from the <b>Printers</b> pane. Click <b>Next</b> to continue.<o:p></o:p></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'><span style='mso-list:Ignore'>7.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif"; color:black'>On the <b>Name Your Printer</b> window, use the <b>Generic / Text Only</b> printer name in the <b>Printer name:</b> field. In the <b>Do you want to use this printer as the default printer</b> field, click <b>No</b>. Click<b>Next</b> to continue.<o:p></o:p></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'><span style='mso-list:Ignore'>8.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif"; color:black'>On the <b>Printer Sharing</b> window, click <b>Do not share this printer</b>. Click <b>Next</b> to continue.<o:p></o:p></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'><span style='mso-list:Ignore'>9.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif"; color:black'>On the <b>Print Test Page</b> window, click <b>No</b>. You do not want to print a text page. Click <b>Next</b> to continue.<o:p></o:p></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'><span style='mso-list:Ignore'>10.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'>Click <b>Finish</b> to finish adding your printer.<o:p></o:p></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; margin-left:.5in;text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'><span style='mso-list:Ignore'>11.<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'>If Windows XP displays the <b>Print to File</b> window, click <b>Cancel</b>.<o:p></o:p></span></p> <p class=MsoNormal>Namaste!<o:p></o:p></p> <p class=MsoNormal>Anugrah A<span style='font-size:8.0pt;font-family:Vrinda; color:black'><o:p></o:p></span></p> <p class=MsoNormal><o:p> </o:p></p> </div> Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-35982628007716730582010-01-22T02:58:00.001-08:002010-01-22T02:59:58.762-08:00C#4.0 : MethodBag & DynamicObject<div class=Section1> <p class=MsoNormal>Read this great article about C#4.0 <strong><span style='font-size:8.5pt;font-family:"Verdana","sans-serif";color:black'>MethodBag</span></strong> and <strong><span style='font-size:8.5pt;font-family:"Verdana","sans-serif"; color:black'>System.Dynamic.DynamicObject</span></strong><o:p></o:p></p> <p class=MsoNormal><a href="http://msdn.microsoft.com/en-us/library/ee658247.aspx">http://msdn.microsoft.com/en-us/library/ee658247.aspx</a><o:p></o:p></p> <p class=MsoNormal>Namaste!<BR>Anugrah Atreya<span style='font-size:8.0pt; font-family:Vrinda;color:black'><o:p></o:p></span></p> <p class=MsoNormal><o:p> </o:p></p> </div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-60369436093152413862009-12-17T23:52:00.001-08:002010-07-27T10:56:44.049-07:00LINQ to SQL : Understanding Query Execution<div class=Section1> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span style='font-size:13.5pt;font-family:"Times New Roman","serif"'>Query Execution</span></b><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'><p/>Whether you write a query as a high-level <i>query expression</i> or build one out of the individual operators, the query that you write is not an imperative statement executed immediately. It is a description. For example, in the declaration below the local variable <b>q</b> refers to the description of the query not the result of executing it.<o:p></o:p></span></p> <p class=MsoNormal style='background:#DDDDDD'><span style='font-size:10.0pt; font-family:"Courier New"'>var q =<BR /> from c in db.Customers<BR /> where c.City == "London"<BR /> select c;<BR /> foreach (Customer c in q)<BR /> Console.WriteLine(c.CompanyName);<BR /></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'>The actual type of <b>q</b> in this instance is <b>IQueryable<Customer></b>. It's not until the application attempts to enumerate the contents of the query that it actually executes. In this example the <b>foreach</b> statement causes the execution to occur. <p/>An <b>IQueryable</b> object is similar to an ADO.NET command object. Having one in hand does not imply that a query was executed. A command object holds onto a string that describes a query. Likewise, an <b>IQueryable</b> object holds onto a description of a query encoded as a data structure known as an <b>Expression</b>. A command object has an <b>ExecuteReader()</b> method that causes execution, returning results as a <b>DataReader</b>. An <b>IQueryable</b> object has a <b>GetEnumerator()</b> method that causes the execution, returning results as an <b>IEnumerator<Customer></b>.<p/><span style='color:red'>Therefore, it follows that if a query is enumerated twice it will be executed twice.<o:p></o:p></span></span></p> <p class=MsoNormal style='background:#DDDDDD'><span style='font-size:10.0pt; font-family:"Courier New"'>var q =<BR /> from c in db.Customers<BR /> where c.City == "London"<BR /> select c;<BR /> // Execute first time<BR /> foreach (Customer c in q)<BR /> Console.WriteLine(c.CompanyName);<BR /> // Execute second time<BR /> foreach (Customer c in q)<BR /> Console.WriteLine(c.CompanyName);<BR /></span></p> <p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'>This behavior is known as <i>deferred execution</i>. Just like with an ADO.NET command object it is possible to hold onto a query and re-execute it. <p/>Of course, application writers often need to be very explicit about where and when a query is executed. It would be unexpected if an application were to execute a query multiple times simply because it needed to examine the results more than once. For example, you may want to bind the results of a query to something like a DataGrid. The control may enumerate the results each time it paints on the screen.<p/>To avoid executing multiple times convert the results into any number of standard collection classes. It is easy to convert the results into a list or array using the standard query operators <b>ToList()</b> or <b>ToArray()</b>.<o:p></o:p></span></p> <p class=MsoNormal style='background:#DDDDDD'><span style='font-size:10.0pt; font-family:"Courier New"'>var q =<BR /> from c in db.Customers<BR /> where c.City == "London"<BR /> select c;<BR /> // Execute once using ToList() or ToArray()<BR /> var list = q.ToList();<BR /> foreach (Customer c in list)<BR /> Console.WriteLine(c.CompanyName);<BR /> foreach (Customer c in list)<BR /> Console.WriteLine(c.CompanyName);</span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'>This post is modified version of article "Introduction to LINQ" given at <a href="http://msdn.microsoft.com/en-us/library/bb425822.aspx">MSDN</a><br /><BR />Namaste !<BR/> <br />Anugrah Atreya </span></p><br /></div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com1tag:blogger.com,1999:blog-4256538220587857609.post-74392591479172869462009-12-15T01:38:00.001-08:002009-12-15T02:54:44.276-08:00LINQ : CopyToDataTable Where the Generic Type T Is Not a DataRow<div class=Section1> <p class=MsoNormal>Hi All,<br/><br/>Last week I was working on a LINQ2DataSet and LINQ2XML based application. There I happened to use .CopyToDataTable() method.<o:p></o:p></p> <p class=MsoNormal><o:p></o:p></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:#2B91AF'>DataSet</span> ds = <span style='color:blue'>new</span> <span style='color:#2B91AF'>DataSet</span>();<o:p></o:p></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'> ds.ReadXml(<span style='color:#C00000'>"C:\TestXML"</span>);</span><o:p></o:p></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:blue'>var</span> query = <span style='color:blue'>from</span> row <span style='color:blue'>in</span> ds.Tables[0].AsEnumerable()<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:blue'>where</span> row.Field<<span style='color:blue'>string</span>>(<span style='color:#A31515'>"IsActive"</span>) == <span style='color:#A31515'>"1"<o:p></o:p></span></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'> <span style='color:blue'>select</span> <span style='color:blue'>new</span> { ID = row.Field<<span style='color:blue'>string</span>>(<span style='color:#A31515'>"ID"</span>), UserName = row.Field<<span style='color:blue'>string</span>>(<span style='color:#A31515'>"UserName"</span>) };<o:p></o:p></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"; color:#2B91AF'> DataTable</span><span style='font-size:10.0pt;font-family:"Courier New"'> dtResults = query.CopyToDataTable();</span><o:p></o:p></p> <p class=MsoNormal><o:p></o:p></p> <p class=MsoNormal>I got an error something like this:<o:p></o:p></p> <p class=MsoNormal><span style='color:red'>The type 'AnonymousType#1' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable<T>(System.Collections.Generic.IEnumerable<T>)'. There is no implicit reference conversion from 'AnonymousType#1' to 'System.Data.DataRow'. <o:p></o:p></span></p> <p class=MsoNormal><o:p></o:p></p> <p class=MsoNormal>This error will go if you select complete DataRow like this:<o:p></o:p></p> <p class=MsoNormal><o:p></o:p></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:#2B91AF'>DataSet</span> ds = <span style='color:blue'>new</span> <span style='color:#2B91AF'>DataSet</span>();<o:p></o:p></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'> ds.ReadXml(<span style='color:#C00000'>"C:\TestXML"</span>);</span><o:p></o:p></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:blue'>var</span> query = <span style='color:blue'>from</span> row <span style='color:blue'>in</span> ds.Tables[0].AsEnumerable()<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:blue'>where</span> row.Field<<span style='color:blue'>string</span>>(<span style='color:#A31515'>"IsActive"</span>) == <span style='color:#A31515'>"1"<o:p></o:p></span></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'> <span style='color:blue'>select</span> row;<o:p></o:p></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"; color:#2B91AF'> DataTable</span><span style='font-size:10.0pt;font-family:"Courier New"'> dtResults = query.CopyToDataTable();</span><o:p></o:p></p> <p class=MsoNormal><o:p></o:p></p> <p class=MsoNormal>The root cause of this issue is the defination of <span style='font-size:10.0pt;font-family:"Courier New"'>CopyToDataTable (). <o:p></o:p></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'>Simply right click on function name and select "GoTo Defination".<o:p></o:p></span></p> <p class=MsoNormal><o:p></o:p></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"; color:blue'> public</span><span style='font-size: 10.0pt;font-family:"Courier New"'> <span style='color:blue'>static</span> <span style='color:#2B91AF'>DataTable</span> CopyToDataTable<T>(<span style='color:blue'>this</span> <span style='color:#2B91AF'>IEnumerable</span><T> source) <span style='color:blue'>where</span> T : <span style='color:#2B91AF'>DataRow</span>;<o:p></o:p></span></p> <p class=MsoNormal><o:p></o:p></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'>You can see that CopyToDataTable<T> is restricted for DataRow by "<span style='color:blue'>where</span> T : <span style='color:#2B91AF'>DataRow" </span>clause.<o:p></o:p></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'><o:p></o:p></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'>In case you have to have this working on the purely generic type (i.e.runtime DataRow object) you need to use custom method </span><strong><span style='font-family: "Calibri","sans-serif"'>CopyToDataTable2()</span></strong><span style='font-size:10.0pt;font-family:"Courier New"'> for which you need to manually add these extra files to your project as mentioned on these links:<o:p></o:p></span></p> <p class=MsoNormal><a href="http://msdn.microsoft.com/en-us/library/bb669096.aspx">http://msdn.microsoft.com/en-us/library/bb669096.aspx</a><o:p></o:p></p> <p class=MsoNormal><o:p></o:p></p> <p class=MsoNormal><o:p></o:p></p> <p class=MsoNormal>Namaste !<o:p></o:p></p> <p class=MsoNormal>Anugrah Atreya <o:p></o:p></p> </div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-31393261329781067522009-11-08T22:59:00.001-08:002009-11-19T22:23:47.494-08:00Detect and avoid memory leaks in .NET<div class=Section1> <p class=MsoNormal>Sharing this great memory leak article on MSDN (Though I myself have to read it) <BR /><a href="http://msdn.microsoft.com/en-us/library/ee658248.aspx">http://msdn.microsoft.com/en-us/library/ee658248.aspx</a><o:p></o:p></p> <p class=MsoNormal>Namaste (Greetings)<BR/><b><span style='color:#C00000'>|</span></b> Anugrah Atreya </p></div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com2tag:blogger.com,1999:blog-4256538220587857609.post-54550035925176437572009-10-19T04:32:00.001-07:002009-11-19T22:20:07.020-08:00BULK COPY/INSERT Example !<div class=Section1> <p class=MsoNormal>Hi,<o:p></o:p></p> <p class=MsoNormal>Recently I faced a scenario where I have to copy multiline data in varchar(2000) field from one data base to another database.<BR/>I tried copy and paste in SQL Server Management Studio and always eneded up with only top row of multi row data in target table.<o:p></o:p></p> <p class=MsoNormal>Writing an ETL (previously know as DTS) was an expensive approach.<BR/>I was testing something so I just copied the data to query editor and wrote an insert query and managed with duplicate multi-line data in all rows.<o:p></o:p></p> <p class=MsoNormal>Somehow I missed to used BULK COPY and INSERT approach and thus I though to share it below:<o:p></o:p></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New";color:blue'>exec</span><span style='font-size:10.0pt; font-family:"Courier New"'> master<span style='color:gray'>..</span><span style='color:maroon'>xp_cmdshell</span> <span style='color:red'>'bcp "Select * from myDB..myTable" queryout "C:\Data.txt" -c -T -t "^,~!@"'<o:p></o:p></span></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"; color:blue'>BULK</span><span style='font-size:10.0pt;font-family:"Courier New"'> <span style='color:blue'>INSERT</span> myDB<span style='color:gray'>..</span>myTable <span style='color:blue'>FROM</span> <span style='color:red'>'c:\Data.txt'</span> <span style='color:blue'>WITH</span> <span style='color:gray'>(</span><span style='color:blue'>FIELDTERMINATOR</span> <span style='color:gray'>=</span> <span style='color:red'>'^,~!@'</span><span style='color:gray'>)<o:p></o:p></span></span></p> <p class=MsoNormal>1 important note here is that you need to enable xp_cmdshell before doing this using your SQL Server Management Studio.<o:p></o:p></p><br />usage: bcp {dbtable | query} {in | out | queryout | format} datafile<br /> [-m maxerrors] [-f formatfile] [-e errfile]<br /> [-F firstrow] [-L lastrow] [-b batchsize]<br /> [-n native type] [-c character type] [-w wide character type]<br /> [-N keep non-text native] [-V file format version] [-q quoted identifier]<br /> [-C code page specifier] [-t field terminator] [-r row terminator]<br /> [-i inputfile] [-o outfile] [-a packetsize]<br /> [-S server name] [-U username] [-P password]<br /> [-T trusted connection] [-v version] [-R regional enable]<br /> [-k keep null values] [-E keep identity values]<br /> [-h "load hints"] [-x generate xml format file]<br /><p class=MsoNormal>Namaste (Greetings)<o:p></o:p></p> </div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com1tag:blogger.com,1999:blog-4256538220587857609.post-27988309072783097142009-09-30T04:09:00.001-07:002009-11-19T22:18:59.418-08:00Most useful GPG Commands : you wont find this anywhere else on internet !<div class=Section1> <p class=MsoNormal><span style='color:#1F497D'>I spent more than 2 days to explore these commands on my own. No documentation, no forum, no post will explain below given points:<o:p></o:p></span></p> <p class=MsoNormal><u><span style='color:#1F497D'>1. How to use passphrase in batch command ?<o:p></o:p></span></u></p> <p class=MsoNormal>gpg -o "C:\Data_Readable.txt" --batch --passphrase-fd 0 < "C:\My_Passphrase.txt" -d "C:\Data_Encrypted.txt"<span style='color:#1F497D'><o:p></o:p></span></p> <p class=MsoNormal><span style='color:#C00000'>i.e. Passphrase is stored in a predefined file and its read from there.</span><o:p></o:p></p> <p class=MsoNormal><o:p> </o:p></p> <p class=MsoNormal><u><span style='color:#1F497D'>2. How to use Fingerprint in delete batch command?</span><br /><br /><span style='color:#244061'><o:p></o:p></span></u></p> <br />You banged your head against the wall and still getting below given error<br><br>gpg: key "61ED 3960 36C8 F91A 406B BEA0 4D3C 8843 3D2D 1636" not found: Unknown system error<br />gpg: 61ED 3960 36C8 F91A 406B BEA0 4D3C 8843 3D2D 1636: delete key failed: Unknown system error<br><br />Here is the right BATCH DELETE command:<br /><br> <br /><p class=MsoNormal>gpg --batch --yes --delete-secret-and-public-key 61ED396036C8F91A406BBEA04D3C88433D2D1636<o:p></o:p></p> <p class=MsoNormal><span style='color:#C00000'>i.e. Fingerprint should not contain any spaces in between.<o:p></o:p></span></p> <p class=MsoNormal><b><span style='color:#C00000'>|</span></b> namaste from Anugrah<span style='color:#1F497D'><o:p></o:p></span></p> </div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-40550051121216476072009-09-16T05:20:00.001-07:002009-09-16T05:20:35.169-07:00Not able to run UnitTest cases when you open your project from shared location ?<div class=Section1> <p class=MsoNormal>Hi All,<o:p></o:p></p> <p class=MsoNormal>I am using Integrated Unit Testing framework of Visual Studio 2008 (i.e. Microsoft.VisualStudio.QualityTools.UnitTestFramework)<span style='color:#1F497D'>. </span>Recently I faced this issue(in heading/subject) when I opened my project from a dynamic view of clearacse(basically a shared drive)<o:p></o:p></p> <p class=MsoNormal>The actual error which I got <span style='color:#1F497D'>was</span>:<span style='color:#1F497D'> </span><span style='color:#C00000'>UTA057: The test 'XXX' cannot be executed because the assembly 'XXX.dll' is not fully trusted by .NET Framework Security Policy. Please use a local directory as the deployment directory for the test run, or use the .NET Framework Configuration tool to grant this assembly the Full Trust permission, and then try again. <o:p></o:p></span></p> <p class=MsoNormal>The project I was testing is a class library and <b><u>quick solution for this problem which I got is to uncheck the<span style='color:#C00000'> ENABLE DEPLOYMENT </span>checkbox in<span style='color:#C00000'> LocalTestRun.testrunconfig</span></u></b><span style='color:#C00000'>.<o:p></o:p></span></p> <p class=MsoNormal>If you don’t want to open this file in VS2008 then you can directly open the file in notepad add following tag <span style='color:#C00000'><Deployment enabled="false" /> </span><span style='color:#1F497D'>. </span>By default deployment is enabled and the line mentioned above does not exist in this file. <o:p></o:p></p> <p class=MsoNormal>I still need to check that what is the usage of enabling Deployment in this context.<span style='color:#1F497D'> </span>If you have used it then do send me comments.<o:p></o:p></p> <p class=MsoNormal>But for now Happy Unit Testing and better development :)<o:p></o:p></p> <p class=MsoNormal><b><span style='color:#C00000'>|</span></b> Anugrah Atreya <o:p></o:p></p> </div> Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com1tag:blogger.com,1999:blog-4256538220587857609.post-68631273607363701182009-07-24T12:27:00.000-07:002011-08-28T23:47:16.070-07:00Quick BytesI hope you would like to keep some basics handy with this document.
<br /><a href="http://docs.google.com/View?id=ddjcvgv_15dphh9vvr">http://docs.google.com/View?id=ddjcvgv_15dphh9vvr</a>
<br />
<br />Download or Print, have fun.
<br />As the document has lot to read, I'll keep this post short only.
<br />
<br />Namaste!
<br />AnugrahAnugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-57484093771829841732009-06-15T00:25:00.001-07:002009-06-15T00:30:18.064-07:00Using .NET component in C++ code<div>Referring a C++ COM component is easy in .NET; all we have to do is to </div> <ul> <li>register (regsvr32 <dll_name_with_full_path>) </li> <li>Add a reference to the COM dll in the Visual Studio Solution</li> <li>An Interop file will be created in the bin directory of .NET project and we are done </li></ul> <div>But the reverse is not as simple as above.</div> <div>So this time I have come up with <u>Steps we need to follow in order to refer a COM visible .NET dll in C++ code.</u> </div> <div> </div> <div class="gmail_quote"> <ul> <li><font face="arial,helvetica,sans-serif">Register the COM Visible .NET assambly with regasm like this:</font></li></ul></div> <blockquote dir="ltr" style="MARGIN-RIGHT: 0px"> <div><font face="arial,helvetica,sans-serif">regasm <dll_name_with_full_path> /tlb</font></div> <div><font face="arial,helvetica,sans-serif">e.g. regasm "C:\SampleProject\bin\Release\Atreya.Anugrah.ComVisibleComponent.dll" /tlb</font></div></blockquote> <ul> <li><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif">Refer .NET component in .h file like this:</font></span></li></ul> <blockquote dir="ltr" style="MARGIN-RIGHT: 0px"> <div><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"></span><font face="arial,helvetica,sans-serif"><span style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'">#import</span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"> <span style="COLOR: #a31515">"<font color="#000000"><generated_tlb_name_with_full_path></font>"</span> named_guids raw_interfaces_only</span></font></div> <div><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif">e.g. <span style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: 'Courier New'">#import</span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"> <span style="COLOR: #a31515">"<font color="#000000">C:\SampleProject\bin\Release\Atreya.Anugrah.ComVisibleComponent.tlb</font>"</span> named_guids raw_interfaces_only</span></font></span> </div> </blockquote> <ul> <li><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif">Create a class in .h file, and declare member variable like this: </font></span></li></ul> <blockquote dir="ltr" style="MARGIN-RIGHT: 0px"> <div><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif">ComVisibleComponent<span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">::<Name_of_Interface>* memberVariablePointer; </span></font></span></div> <div><font face="arial,helvetica,sans-serif"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">e.g. </span></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">Atreya_Anugrah_ComVisibleComponent::IMyInterface* m_pIMyInterface;</span></font><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"> </font></span></div> </blockquote> <ul> <li> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif">Now, Initialize the declared member variable pointer in .cpp file like this:</font></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"> </font></span></div> </li></ul> <blockquote dir="ltr" style="MARGIN-RIGHT: 0px"> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif">hr = CoCreateInstance(</font></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"> <span style="COLOR: blue">__uuidof</span>(<span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">Atreya_Anugrah_ComVisibleComponent</span>::<Name_of_CoClass_Implementing_Interface>), <span style="COLOR: green">// COM class id</span></font></span></div> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"><span style="COLOR: green"> </span></font></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif">NULL, <span style="COLOR: green">// Outer unknown</span></font></span></div> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"><span style="COLOR: green"> </span></font></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif">CLSCTX_ALL, <span style="COLOR: green">// Server INFO</span></font></span></div> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"><span style="COLOR: green"> </span></font></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">Atreya_Anugrah_ComVisibleComponent</span>::IID_<Name_of_Interface>, <span style="COLOR: green">// interface id</span></font></span></div> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"><span style="COLOR: green"> </span></font></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif">(<span style="COLOR: blue">void</span>**) &m_pIMyInterface</font></span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"> ); <span style="COLOR: green">// Returned Interface</span></font></span><span style="FONT-SIZE: 10pt; COLOR: green; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"> </font></span></div> </blockquote> <blockquote dir="ltr" style="MARGIN-RIGHT: 0px"> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"><span style="COLOR: blue">if</span> (FAILED(hr))</font></span></div> <div lang="EN-US" link="blue" vlink="purple"><font face="Arial"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">{</span></font></div> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"> _com_error e(hr);</font></span></div> <div lang="EN-US" link="blue" vlink="purple"><font face="Arial"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"> </span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">strErrorMsg.Format(<span style="COLOR: #a31515">"Error: Unable to create COM Visible .NET component. hr = %d. Message = %s"</span>,</span><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">hr, e.ErrorMessage());</span></font></div> <div lang="EN-US" link="blue" vlink="purple"><font face="Arial"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"> </span></font><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"><span style="COLOR: blue">return</span> hr;</font></span></div> <div lang="EN-US" link="blue" vlink="purple"><font face="Arial"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'">}</span></font></div></blockquote> <div lang="EN-US" link="blue" vlink="purple"> <span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="arial,helvetica,sans-serif"> <font color="#009900">//you are good to use your initialized pointer here :)</font></font> </span></div> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"> <font face="Arial">m_pIMyInterface->foo()</font></span></div> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="Arial"></font></span> </div> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="courier new,monospace">P.S. This post does not explain that how can we make our .NET component as COM visible. Please refer other post/article for that as it is quite simple activity.</font></span></span></div> <div lang="EN-US" link="blue" vlink="purple"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"><font face="Arial"><span style="FONT-SIZE: 10pt; FONT-FAMILY: 'Courier New'"> </span></font> </span></div> <div lang="EN-US" link="blue" vlink="purple">Namaste (Thanks and Regards)</div> <div lang="EN-US" link="blue" vlink="purple"><span style="COLOR: #c00000"><strong>| </strong></span>Anugrah Atreya</div> <div lang="EN-US" link="blue" vlink="purple"> </div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com2tag:blogger.com,1999:blog-4256538220587857609.post-41782667947417246052009-04-27T04:30:00.001-07:002009-04-27T04:30:24.510-07:00Steps to debug .NET dll (registered in GAC) consumed by unmanaged vb6 code<div class=Section1> <p class=MsoNormal>To debug Shared dll in GAC called by VB6 code:<o:p></o:p></p> <p class=MsoNormal><o:p> </o:p></p> <p class=MsoNormal>0.) Unregister shfusion.dll in <span style='color:#C00000'>c:\windows\microsoft.net\framework\v<version>\shfusion.dll</span> so that actual GAC folders are visible in Explorer.<o:p></o:p></p> <p class=MsoNormal>1.) Open .NET project and make sure that "Just my code debugging is unchecked" in Tools > Options > Debugging.<o:p></o:p></p> <p class=MsoNormal>2.) Now rebuild the .NET dll in debug mode.<o:p></o:p></p> <p class=MsoNormal>3.) Add dll built in debug mode along with the PDB to GAC<o:p></o:p></p> <p class=MsoNormal>4.) Run the vb6 component.<o:p></o:p></p> <p class=MsoNormal>5.) Attach the .NET project to running vb6.exe process.<o:p></o:p></p> <p class=MsoNormal>6.) Breakpoints will be loaded dynamically at runtime.<o:p></o:p></p> <p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p> <p class=MsoNormal><span style='color:#1F497D'>namaste!<o:p></o:p></span></p> <p class=MsoNormal><span style='color:#1F497D'>Anugrah Atreya<o:p></o:p></span></p> <p class=MsoNormal><o:p> </o:p></p> </div> Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-85966034765045033962009-03-06T04:30:00.001-08:002009-11-19T22:21:07.590-08:00Implemented IObjectSafety in .NET but object still unsafe for scripting : RESOLVED<div class=Section1> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>Hello Friends,<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>The intended audience of this article are not those developers who are looking how to implement IObjectSafety in .NET but those who have already done that and still getting “Object unsafe for scripting” popup in Internet Explorer.<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>(they can find the implementation on msdn blog here: http://blogs.msdn.com/infopath/archive/2005/04/15/408728.aspx).<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>I saw a lot of confusion related to implementation of IObjectSafety in .NET and thought to share my experience about the same.<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>So the simple cause to this big problem is our knowledge about COM & Guids </span><span style='font-size:10.0pt; font-family:Wingdings'>J</span><span style='font-size:10.0pt;font-family:"Verdana","sans-serif"'><o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>Basically when we get the interface from any knowledgebase we include it to out project and like a good programer we generate a new Guid and replace the one which is given in orignal documenttaion.<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>That’s it; we have messed our code and we are not even aware of it.<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>So the crux is that for any IObjectSafety implementation the guid of out interface should always be <o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'>{<span style='color:#A31515'>"CB5BDC81-93C1-11cf-8F20-00805F2CD064"}. </span>Period.<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>By the time you end up reading the above line your problem is hopefully solved and your component is now safe for scripting.<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New";color:blue'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>So this is how your interface will eventually look like:<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New";color:blue'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New";color:blue'>using</span><span style='font-size:10.0pt; font-family:"Courier New"'> System;<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New";color:blue'>using</span><span style='font-size:10.0pt; font-family:"Courier New"'> System.Runtime.InteropServices;<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New";color:blue'>namespace</span><span style='font-size: 10.0pt;font-family:"Courier New"'> Atreya.SampleCode<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'>{<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:gray'>///</span><span style='color:green'> </span><span style='color:gray'><summary><o:p></o:p></span></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:gray'>///</span><span style='color:green'> Methods in this interface needs to be implemented to mark any object safe for scripting in IE<o:p></o:p></span></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:gray'>///</span><span style='color:green'> </span><span style='color:gray'></summary><o:p></o:p></span></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> [<span style='color:#2B91AF'>ComImport</span>()]<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> [<span style='color:#2B91AF'>Guid</span>(<span style='color:#A31515'>"CB5BDC81-93C1-11cf-8F20-00805F2CD064"</span>)]<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> [<span style='color:#2B91AF'>InterfaceType</span>(<span style='color:#2B91AF'>ComInterfaceType</span>.InterfaceIsIUnknown)]<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:blue'>internal</span> <span style='color:blue'>interface</span> <span style='color:#2B91AF'>IObjectSafety<o:p></o:p></span></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> {<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> [<span style='color:#2B91AF'>PreserveSig</span>]<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:blue'>int</span> GetInterfaceSafetyOptions(<span style='color: blue'>ref</span> <span style='color:#2B91AF'>Guid</span> riid, <span style='color:blue'>out</span> <span style='color:blue'>int</span> pdwSupportedOptions, <span style='color:blue'>out</span> <span style='color:blue'>int</span> pdwEnabledOptions);<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> [<span style='color:#2B91AF'>PreserveSig</span>]<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <span style='color:blue'>int</span> SetInterfaceSafetyOptions(<span style='color: blue'>ref</span> <span style='color:#2B91AF'>Guid</span> riid, <span style='color:blue'>int</span> dwOptionSetMask, <span style='color:blue'>int</span> dwEnabledOptions);<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> }<o:p></o:p></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New"'>}</span><o:p></o:p></p> <p class=MsoNormal><o:p> </o:p></p> <p class=MsoNormal>Namaste!<o:p></o:p></p> <p class=MsoNormal><span style='background:red;mso-highlight:red'> </span> Anugrah Atreya <o:p></o:p></p> <p class=MsoNormal><o:p> </o:p></p> </div>Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0tag:blogger.com,1999:blog-4256538220587857609.post-6012061132146052922009-01-02T03:48:00.001-08:002009-01-02T03:48:37.603-08:00Transparent Button on Windows Forms<div class=Section1> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>Hi,<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Courier New"'> <o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>I was working on some VB6 migration to C#.NET, in VB6 application developers have used some third party controls (e.g. SSRibbon provided by Sheridan 3_0) to create transparent Buttons (required when button is drawn on the background image of form and you want user to click over that part of background image).<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>When I tried to search the internet for a solution in .NET, I didn’t found anything usefull. May be because this is quite simple thing :)<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>But still we need to have the solution on internet, so that someone in need can find it.<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'>So, all you need to do is to add these lines of code for your System.Windows.Forms.<span style='color:#2B91AF'>Button </span>object. In my case I have named it as btnLink.<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'><o:p> </o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'> btnLink.FlatStyle = <span style='color:#2B91AF'>FlatStyle</span>.Flat; <o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'> btnLink.BackColor = <span style='color:#2B91AF'>Color</span>.Transparent;<o:p></o:p></span></p> <p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt; font-family:"Verdana","sans-serif"'> btnLink.FlatAppearance.MouseDownBackColor = <span style='color:#2B91AF'>Color</span>.Transparent;<o:p></o:p></span></p> <p class=MsoNormal><span style='font-size:10.0pt;font-family:"Verdana","sans-serif"'> btnLink.FlatAppearance.MouseOverBackColor = <span style='color:#2B91AF'>Color</span>.Transparent;</span><span style='font-family: "Verdana","sans-serif"'><o:p></o:p></span></p> <p class=MsoNormal><span style='font-family:"Verdana","sans-serif"'><o:p> </o:p></span></p> <p class=MsoNormal><span style='font-family:"Verdana","sans-serif"'>namste !</span><span style='font-family:"Verdana","sans-serif"'><o:p></o:p></span></p> <p class=MsoNormal><span style='font-family:"Verdana","sans-serif";background: red;mso-highlight:red'> </span><span style='font-family:"Verdana","sans-serif"'> Anugrah Atreya <o:p></o:p></span></p> </div> Anugrah Ahttp://www.blogger.com/profile/03242166038964647046noreply@blogger.com0