tag:blogger.com,1999:blog-4826028108234038302024-03-13T07:33:46.461-07:00JSD Ideas.Net, MVC, MVP, SQL, JQuery, JSON, JavascriptJayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.comBlogger64125tag:blogger.com,1999:blog-482602810823403830.post-59365861293510845992015-12-29T02:59:00.000-08:002015-12-29T03:02:46.802-08:00SignalR Implementation in XAML Page<div dir="ltr" style="text-align: left;" trbidi="on">
<h2>XAML Page:</h2>
<!-- code formatted by http://manoli.net/csharpformat/ -->
<pre class="csharpcode">
<span class="kwrd">using</span> System.Collections.Generic;
<span class="kwrd">using</span> System.Windows;
<span class="kwrd">using</span> System.Windows.Controls;
<span class="kwrd">using</span> System.Windows.Input;
<span class="kwrd">using</span> System.Windows.Media;
<span class="kwrd">using</span> Microsoft.AspNet.SignalR.Client;
<span class="kwrd">namespace</span> SignalRDemo
{
<span class="rem">/// <summary></span>
<span class="rem">/// Interaction logic for MainWindow.xaml</span>
<span class="rem">/// </summary></span>
<span class="kwrd">public</span> <span class="kwrd">partial</span> <span class="kwrd">class</span> MainWindow : Window
{
<span class="kwrd">public</span> MainWindow()
{
InitializeComponent();
}
HubConnection _connection;
IHubProxy _hub;
<span class="rem">// Keep track of when fake "drag and drop" mode is enabled.</span>
<span class="kwrd">private</span> <span class="kwrd">bool</span> isDragging = <span class="kwrd">false</span>;
<span class="rem">// Store the location where the user clicked the control.</span>
<span class="kwrd">private</span> <span class="kwrd">double</span> clickOffsetX, clickOffsetY;
<span class="kwrd">private</span> <span class="kwrd">void</span> window_Loaded(<span class="kwrd">object</span> sender, RoutedEventArgs e)
{
Start();
}
<span class="kwrd">void</span> label_MouseDown(<span class="kwrd">object</span> sender, MouseButtonEventArgs e)
{
isDragging = <span class="kwrd">true</span>;
<span class="kwrd">var</span> position = e.GetPosition(window);
<span class="kwrd">var</span> label = sender <span class="kwrd">as</span> Label;
<span class="kwrd">var</span> l = (<span class="kwrd">double</span>)label.GetValue(Canvas.LeftProperty);
<span class="kwrd">var</span> t = (<span class="kwrd">double</span>)label.GetValue(Canvas.TopProperty);
clickOffsetX = l - position.X;
clickOffsetY = t - position.Y;
}
<span class="kwrd">void</span> label_MouseMove(<span class="kwrd">object</span> sender, MouseEventArgs e)
{
<span class="kwrd">if</span> (isDragging == <span class="kwrd">true</span>)
{
<span class="rem">// The control coordinates are converted into form coordinates</span>
<span class="rem">// by adding the label position offset.</span>
<span class="rem">// The offset where the user clicked in the control is also</span>
<span class="rem">// accounted for. Otherwise, it looks like the top-left corner</span>
<span class="rem">// of the label is attached to the mouse.</span>
<span class="kwrd">var</span> label = sender <span class="kwrd">as</span> Label;
<span class="kwrd">var</span> position = e.GetPosition(window);
<span class="kwrd">var</span> x = position.X + clickOffsetX;
<span class="kwrd">var</span> y = position.Y + clickOffsetY;
SetShapePosition(label, x, y);
_hub.Invoke(<span class="str">"MoveShape"</span>, <span class="kwrd">int</span>.Parse(label.Name.Replace(<span class="str">"shape"</span>, <span class="str">""</span>)), x, y);
}
}
<span class="kwrd">void</span> label_MouseUp(<span class="kwrd">object</span> sender, MouseButtonEventArgs e)
{
isDragging = <span class="kwrd">false</span>;
}
<span class="kwrd">private</span> <span class="kwrd">void</span> Start()
{
_connection = <span class="kwrd">new</span> HubConnection(<span class="str">"http://localhost/SignalRChat/myhubs/hubs"</span>);
_hub = _connection.CreateHubProxy(<span class="str">"SignalRHub"</span>);
_connection.Start().Wait();
_hub.On<<span class="kwrd">int</span>>(<span class="str">"usersConnected"</span>, (count) =>
{
<span class="kwrd">this</span>.Dispatcher.Invoke(() =>
UpdateUserCount(count)
);
});
_hub.On<List<Common.Shape>>(<span class="str">"shapeList"</span>, (list) =>
{
<span class="kwrd">this</span>.Dispatcher.Invoke(() =>
{
canvas.Children.Clear();
list.ForEach(q =>
{
<span class="kwrd">var</span> label = <span class="kwrd">new</span> Label();
label.Name = <span class="kwrd">string</span>.Format(<span class="str">"shape{0}"</span>, q.Id);
label.Content = q.Name;
label.Height = 100;
label.Width = 100;
label.Padding = <span class="kwrd">new</span> Thickness(10, 10, 0, 0);
label.Background = <span class="kwrd">new</span> SolidColorBrush((Color)ColorConverter.ConvertFromString(q.Color));
SetShapePosition(label, q.X, q.Y);
label.FontSize = 20;
label.MouseDown += label_MouseDown;
label.MouseMove += label_MouseMove;
label.MouseUp += label_MouseUp;
canvas.Children.Add(label);
});
});
});
_hub.On<Common.Shape>(<span class="str">"shapeMoved"</span>, (shape) =>
{
<span class="kwrd">this</span>.Dispatcher.Invoke(() =>
UpdatePosition(shape)
);
});
_hub.Invoke(<span class="str">"GetShapeList"</span>);
}
<span class="kwrd">private</span> <span class="kwrd">void</span> SetShapePosition(Label label, <span class="kwrd">double</span> x, <span class="kwrd">double</span> y)
{
<span class="kwrd">if</span> (label != <span class="kwrd">null</span>)
{
label.SetValue(Canvas.LeftProperty, x);
label.SetValue(Canvas.TopProperty, y);
}
}
<span class="kwrd">private</span> <span class="kwrd">void</span> UpdateUserCount(<span class="kwrd">int</span> count)
{
lblCount.Content = count;
}
<span class="kwrd">private</span> <span class="kwrd">void</span> UpdatePosition(Common.Shape shape)
{
<span class="kwrd">var</span> label = UIHelper.FindChild<Label>(Application.Current.MainWindow, <span class="kwrd">string</span>.Format(<span class="str">"shape{0}"</span>, shape.Id));
SetShapePosition(label, shape.X, shape.Y);
}
}
<span class="kwrd">public</span> <span class="kwrd">class</span> UIHelper
{
<span class="rem">/// <summary></span>
<span class="rem">/// Finds a Child of a given item in the visual tree. </span>
<span class="rem">/// </summary></span>
<span class="rem">/// <param name="parent">A direct parent of the queried item.</param></span>
<span class="rem">/// <typeparam name="T">The type of the queried item.</typeparam></span>
<span class="rem">/// <param name="childName">x:Name or Name of child. </param></span>
<span class="rem">/// <returns>The first parent item that matches the submitted type parameter. </span>
<span class="rem">/// If not matching item can be found, </span>
<span class="rem">/// a null parent is being returned.</returns></span>
<span class="kwrd">public</span> <span class="kwrd">static</span> T FindChild<T>(DependencyObject parent, <span class="kwrd">string</span> childName)
where T : DependencyObject
{
<span class="rem">// Confirm parent and childName are valid. </span>
<span class="kwrd">if</span> (parent == <span class="kwrd">null</span>) <span class="kwrd">return</span> <span class="kwrd">null</span>;
T foundChild = <span class="kwrd">null</span>;
<span class="kwrd">int</span> childrenCount = VisualTreeHelper.GetChildrenCount(parent);
<span class="kwrd">for</span> (<span class="kwrd">int</span> i = 0; i < childrenCount; i++)
{
<span class="kwrd">var</span> child = VisualTreeHelper.GetChild(parent, i);
<span class="rem">// If the child is not of the request child type child</span>
T childType = child <span class="kwrd">as</span> T;
<span class="kwrd">if</span> (childType == <span class="kwrd">null</span>)
{
<span class="rem">// recursively drill down the tree</span>
foundChild = FindChild<T>(child, childName);
<span class="rem">// If the child is found, break so we do not overwrite the found child. </span>
<span class="kwrd">if</span> (foundChild != <span class="kwrd">null</span>) <span class="kwrd">break</span>;
}
<span class="kwrd">else</span> <span class="kwrd">if</span> (!<span class="kwrd">string</span>.IsNullOrEmpty(childName))
{
<span class="kwrd">var</span> frameworkElement = child <span class="kwrd">as</span> FrameworkElement;
<span class="rem">// If the child's name is set for search</span>
<span class="kwrd">if</span> (frameworkElement != <span class="kwrd">null</span> && frameworkElement.Name == childName)
{
<span class="rem">// if the child's name is of the request name</span>
foundChild = (T)child;
<span class="kwrd">break</span>;
}
}
<span class="kwrd">else</span>
{
<span class="rem">// child element found.</span>
foundChild = (T)child;
<span class="kwrd">break</span>;
}
}
<span class="kwrd">return</span> foundChild;
}
}
}
</pre>
<h2>SignalR Hub Page :</h2>
<!-- code formatted by http://manoli.net/csharpformat/ -->
<pre class="csharpcode">
<span class="kwrd">using</span> Microsoft.AspNet.SignalR;
<span class="kwrd">using</span> System.Collections.Generic;
<span class="kwrd">using</span> System.Linq;
<span class="kwrd">using</span> System.Threading.Tasks;
<span class="kwrd">using</span> Common;
<span class="kwrd">namespace</span> SignalRChat
{
<span class="kwrd">public</span> <span class="kwrd">class</span> SignalRHub : Hub
{
<span class="kwrd">public</span> SignalRHub()
{
}
<span class="preproc">#region</span> Moveable Shape
<span class="kwrd">public</span> async Task GetShapeList() <span class="rem">// this method will be called from the client, when the user drag/move the shape</span>
{
await Clients.Caller.shapeList(ShapeHandler.Instance.ShapeList); <span class="rem">// this method will send the coord x, y to the other users but the user draging the shape</span>
}
<span class="kwrd">public</span> async Task MoveShape(<span class="kwrd">int</span> id, <span class="kwrd">double</span> x, <span class="kwrd">double</span> y) <span class="rem">// this method will be called from the client, when the user drag/move the shape</span>
{
<span class="kwrd">var</span> shape = ShapeHandler.Instance.GetShape(id, x, y);
<span class="kwrd">if</span> (shape.X != x || shape.Y != y) await Clients.Caller.shapeMoved(shape);
await Clients.Others.shapeMoved(shape); <span class="rem">// this method will send the coord x, y to the other users but the user draging the shape</span>
}
<span class="kwrd">public</span> <span class="kwrd">override</span> Task OnConnected() <span class="rem">//override OnConnect, OnReconnected and OnDisconnected to know if a user is connected or disconnected</span>
{
ShapeHandler.Instance.ConnectedIds.Add(Context.ConnectionId); <span class="rem">//add a connection id to the list</span>
Clients.All.usersConnected(ShapeHandler.Instance.ConnectedIds.Count()); <span class="rem">//this will send to ALL the clients the number of users connected</span>
<span class="kwrd">return</span> <span class="kwrd">base</span>.OnConnected();
}
<span class="kwrd">public</span> <span class="kwrd">override</span> Task OnReconnected()
{
ShapeHandler.Instance.ConnectedIds.Add(Context.ConnectionId);
Clients.All.usersConnected(ShapeHandler.Instance.ConnectedIds.Count());
<span class="kwrd">return</span> <span class="kwrd">base</span>.OnConnected();
}
<span class="kwrd">public</span> <span class="kwrd">override</span> Task OnDisconnected(<span class="kwrd">bool</span> stopCalled)
{
ShapeHandler.Instance.ConnectedIds.Remove(Context.ConnectionId);
Clients.All.usersConnected(ShapeHandler.Instance.ConnectedIds.Count());
<span class="kwrd">return</span> <span class="kwrd">base</span>.OnDisconnected(stopCalled);
}
<span class="preproc">#endregion</span>
}
}</pre>
</div>
Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com4tag:blogger.com,1999:blog-482602810823403830.post-52697828290631773342015-12-29T02:45:00.000-08:002015-12-29T02:45:01.977-08:00SignalR Implementation in HTML Page<div dir="ltr" style="text-align: left;" trbidi="on">
<h2>Html Page :</h2>
<!-- code formatted by http://manoli.net/csharpformat/ -->
<pre class="csharpcode">
<span class="kwrd"><!</span><span class="html">DOCTYPE</span> <span class="attr">html</span><span class="kwrd">></span>
<span class="kwrd"><</span><span class="html">html</span> <span class="attr">xmlns</span><span class="kwrd">="http://www.w3.org/1999/xhtml"</span><span class="kwrd">></span>
<span class="kwrd"><</span><span class="html">head</span><span class="kwrd">></span>
<span class="kwrd"><</span><span class="html">title</span><span class="kwrd">></</span><span class="html">title</span><span class="kwrd">></span>
<span class="kwrd"></</span><span class="html">head</span><span class="kwrd">></span>
<span class="kwrd"><</span><span class="html">body</span><span class="kwrd">></span>
<span class="kwrd"><</span><span class="html">label</span> <span class="attr">id</span><span class="kwrd">="serverDate"</span><span class="kwrd">></</span><span class="html">label</span><span class="kwrd">></span>
<span class="kwrd"><</span><span class="html">button</span> <span class="attr">id</span><span class="kwrd">="initTimer"</span><span class="kwrd">></span>Start Timer<span class="kwrd"></</span><span class="html">button</span><span class="kwrd">></span>
<span class="rem"><!--Script references. --></span>
<span class="kwrd"><</span><span class="html">script</span> <span class="attr">src</span><span class="kwrd">="Scripts/jquery-1.6.4.min.js"</span><span class="kwrd">></</span><span class="html">script</span><span class="kwrd">></span>
<script src=<span class="str">"Scripts/jquery.signalR-2.0.0.js"</span>></script>
<!--Reference the autogenerated SignalR hub script. -->
<script src=<span class="str">"signalR/hubs"</span>></script>
<script type=<span class="str">"text/javascript"</span>>
$(<span class="kwrd">function</span> () {
<span class="rem">// Declare a proxy to reference the hub.</span>
<span class="kwrd">var</span> hub = $.connection.demoHub;
hub.client.serverDateTimeInfo = <span class="kwrd">function</span> (time) {
$(<span class="str">"#serverDate"</span>).text(time);
};
$.connection.hub.start().done(<span class="kwrd">function</span> () {
alert(<span class="str">'Server connected'</span>);
hub.server.getServerDatetime();
$(<span class="str">"#initTimer"</span>).click(<span class="kwrd">function</span> () {
hub.server.initTimer();
});
});
});
<span class="kwrd"></</span><span class="html">script</span><span class="kwrd">></span>
<span class="kwrd"></</span><span class="html">body</span><span class="kwrd">></span>
<span class="kwrd"></</span><span class="html">html</span><span class="kwrd">></span>
</pre>
<h2>SignalR Hub Page :</h2>
<!-- code formatted by http://manoli.net/csharpformat/ -->
<pre class="csharpcode">
<span class="kwrd">using</span> Microsoft.AspNet.SignalR;
<span class="kwrd">using</span> System;
<span class="kwrd">using</span> System.Collections.Generic;
<span class="kwrd">using</span> System.Linq;
<span class="kwrd">using</span> System.Timers;
<span class="kwrd">using</span> System.Web;
<span class="kwrd">namespace</span> SignalRChat
{
<span class="kwrd">public</span> <span class="kwrd">class</span> DemoHub :Hub
{
<span class="kwrd">public</span> <span class="kwrd">void</span> GetServerDatetime()
{
SendNotification();
}
<span class="kwrd">private</span> <span class="kwrd">void</span> SendNotification()
{
<span class="kwrd">try</span>
{
Clients.Caller.serverDateTimeInfo(DateTime.Now.ToString());
}
<span class="kwrd">catch</span> { }
}
<span class="kwrd">public</span> <span class="kwrd">void</span> InitTimer()
{
Timer timer = <span class="kwrd">new</span> Timer(1000);
timer.Elapsed += <span class="kwrd">new</span> ElapsedEventHandler(timer_Elapsed);
timer.AutoReset = <span class="kwrd">true</span>;
timer.Enabled = <span class="kwrd">true</span>;
}
<span class="kwrd">public</span> <span class="kwrd">void</span> timer_Elapsed(<span class="kwrd">object</span> sender, ElapsedEventArgs e)
{
SendNotification();
}
}
}</pre>
</div>
Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-92007656845329998532012-03-15T22:34:00.003-07:002012-07-11T21:44:47.533-07:00Diffrence between CROSS APPLY and OUTER APPLY of SQL XML<a href="http://www.codeproject.com" rel="tag" style="display:none">CodeProject</a>
<pre class="csharpcode"><br /><b>Sample XML :</b><br />DECLARE @XML XML = <br /><span class="str">'<Root><br /> <UserInfo Id="1" Name="Name 1"><br /> <CityInfo Id="2" City="City 1" /><br /> <CityInfo Id="3" City="City 2" ><br /> <PrefInfo Id="33" Name="Show Whether" /><br /> </CityInfo><br /> </UserInfo><br /> <UserInfo Id="2" Name="Name 2"><br /> <CityInfo Id="4" City="City 3" /><br /> <CityInfo Id="5" City="City 4" ><br /> <PrefInfo Id="33" Name="Show Temprature" /><br /> </CityInfo><br /> </UserInfo><br /></Root>'</span><br /><br /><b>Cross Apply : </b> Cross apply is basically use inner join of two xml collection<br /><span class="kwrd">SELECT</span><br /> UserInfo.<span class="kwrd">value</span>(<span class="str">'@Id'</span>, <span class="str">'BIGINT'</span>) <span class="kwrd">as</span> UserId<br /> , UserInfo.<span class="kwrd">value</span>(<span class="str">'@Name'</span>, <span class="str">'VARCHAR(20)'</span>) <span class="kwrd">as</span> UserName<br /> , CityInfo.<span class="kwrd">value</span>(<span class="str">'@Id'</span>, <span class="str">'BIGINT'</span>) <span class="kwrd">as</span> CityId<br /> , CityInfo.<span class="kwrd">value</span>(<span class="str">'@City'</span>, <span class="str">'VARCHAR(20)'</span>) <span class="kwrd">as</span> CityName<br /> , PrefInfo.<span class="kwrd">value</span>(<span class="str">'@Id'</span>, <span class="str">'BIGINT'</span>) <span class="kwrd">as</span> PrefId<br /> , PrefInfo.<span class="kwrd">value</span>(<span class="str">'@Name'</span>, <span class="str">'VARCHAR(20)'</span>) <span class="kwrd">as</span> PrefName<br /><span class="kwrd">FROM</span> @xml.nodes(<span class="str">'/Root/UserInfo'</span>)e(UserInfo)<br /> <span class="kwrd">CROSS</span> APPLY UserInfo.nodes(<span class="str">'CityInfo'</span>)b(CityInfo)<br /> <span class="kwrd">CROSS</span> APPLY CityInfo.nodes(<span class="str">'PrefInfo'</span>)c(PrefInfo)<br /><br /><b>Output :</b><br /><br />UserId UserName CityId CityName PrefId PrefName<br />-------- --------- ------- --------- -------- ----------------<br />1 Name 1 3 City 2 33 Show Whether<br />2 Name 2 5 City 4 33 Show Temprature <br /><br /><b>Outer Apply : </b> Outer apply is basically use left outer join of two xml collection<br /><span class="kwrd">SELECT</span><br /> UserInfo.<span class="kwrd">value</span>(<span class="str">'@Id'</span>, <span class="str">'BIGINT'</span>) <span class="kwrd">as</span> UserId<br /> , UserInfo.<span class="kwrd">value</span>(<span class="str">'@Name'</span>, <span class="str">'VARCHAR(20)'</span>) <span class="kwrd">as</span> UserName<br /> , CityInfo.<span class="kwrd">value</span>(<span class="str">'@Id'</span>, <span class="str">'BIGINT'</span>) <span class="kwrd">as</span> CityId<br /> , CityInfo.<span class="kwrd">value</span>(<span class="str">'@City'</span>, <span class="str">'VARCHAR(20)'</span>) <span class="kwrd">as</span> CityName<br /> , PrefInfo.<span class="kwrd">value</span>(<span class="str">'@Id'</span>, <span class="str">'BIGINT'</span>) <span class="kwrd">as</span> PrefId<br /> , PrefInfo.<span class="kwrd">value</span>(<span class="str">'@Name'</span>, <span class="str">'VARCHAR(20)'</span>) <span class="kwrd">as</span> PrefName<br /><span class="kwrd">FROM</span> @xml.nodes(<span class="str">'/Root/UserInfo'</span>)e(UserInfo)<br /> <span class="kwrd">CROSS</span> APPLY UserInfo.nodes(<span class="str">'CityInfo'</span>)b(CityInfo)<br /> <span class="kwrd">OUTER</span> APPLY CityInfo.nodes(<span class="str">'PrefInfo'</span>)c(PrefInfo)<br /><br /><b>Output :</b><br />UserId UserName CityId CityName PrefId PrefName<br />------- --------- ------- --------- ------- ----------------<br />1 Name 1 2 City 1 NULL NULL<br />1 Name 1 3 City 2 33 Show Whether<br />2 Name 2 4 City 3 NULL NULL<br />2 Name 2 5 City 4 33 Show Temprature <br /></pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-75505143926458396162012-03-14T04:25:00.003-07:002012-03-14T04:35:40.429-07:00How to Handle button click event in jquery<b>Code :</b><br /><pre class="csharpcode"><br /> <span class="kwrd"><</span><span class="html">script</span> <span class="attr">type</span><span class="kwrd">="text/javascript"</span> <span class="attr">language</span><span class="kwrd">="javascript"</span><span class="kwrd">></span><br /> $(document).ready(<span class="kwrd">function</span> () {<br /> $(<span class="str">"[id*='btnSave']"</span>).click(<span class="kwrd">function</span> (<span class="kwrd">event</span>) {<br /> <span class="kwrd">event</span>.preventDefault();<br /> <span class="kwrd">if</span> (confirm(<span class="str">"Want to save data?"</span>)) {<br /> $(<span class="kwrd">this</span>).unbind(<span class="str">'click'</span>).click()<br /> }<br /> });<br /><br /> });<br /> <span class="kwrd"></</span><span class="html">script</span><span class="kwrd">></span></pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-19914060072879446942011-09-20T23:41:00.000-07:002011-09-26T04:41:43.548-07:00jQuery Selector<pre><br />#No Syntax Description<br />---------------------------------------------------------------------------------------<br />1. $(this) Current HTML element<br />2. $(".intro") All elements with class="intro"<br />3. $("#intro") The first element with id="intro"<br />4. $("p") All <p> elements<br />5. $("p.intro") All <p> elements with class="intro"<br />6. $("p#intro") All <p> elements with id="intro"<br />7. $("p#intro:first-child") The first <p> element with id="intro"<br />8. $("ul li:first-child") The first <li> element of each <ul><br />9. $("ul li:last-child") The last <li> element of each <ul><br />10. $("ul li:nth-child(4)") The fourth <li> element of each <ul><br />11. $("div#intro .head") All elements with class="head" and id="intro" of DIV<br />12. $("[href*='User']") All elements with href contains "User"<br />13. $("[href^='User']") All elements with href start with "User"<br />14. $("[href$='.html']") All elements with an href attribute<br /> that ends with ".html"<br />15. $("[href*='User'] div") AND condition for Getting all element <br /> which have href contains "User" and inner element div<br />16. $("[href*='User'],div") OR condition for Getting all element <br /> which have href contains "User" or div element<br />17. $("[href!='UserInfo.html']") NOT condition for Getting all element <br /> which have href not equle to "UserInfo.html"<br />18. $("div > input#User") Getting all element which have a parent element is<br /> DIV and next element is INPUT have a id User<br />19. $("div").find("input#User") Getting all element of parent element is <br /> DIV and child element is INPUT have a id User<br />20. $("div").not(".UserInfo, #UserId") Getting all div element which not have a <br /> class USERINFO or id is USERID<br /><br /></pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-74763550998511141782011-08-29T00:23:00.000-07:002011-09-21T03:44:37.155-07:00Rounding date in SQL query<pre class="csharpcode"><br /><b>Query : </b><br /><span class="kwrd">SELECT</span><br /> DATEADD( MI, DATEDIFF( MI, 0, DATEADD( SS, 30, Dates.SampleDate) ), 0) <span class="kwrd">AS</span> RoundedDate<br /><span class="kwrd">FROM</span><br />(<br /> <span class="kwrd">SELECT</span> SampleDate = <span class="kwrd">CONVERT</span>( DATETIME, <span class="str">'8/29/2011 12:59:29.998'</span>)<br /> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span><br /> <span class="kwrd">SELECT</span> SampleDate = <span class="kwrd">CONVERT</span>( DATETIME, <span class="str">'8/29/2011 12:59:30.000'</span>)<br />) Dates<br /><br /><b>Results : </b><br /><br />RoundedDate<br />-----------------------<br />2011-08-29 12:59:00.000<br />2011-08-29 13:00:00.000<br /><br /></pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-36393417171009247522011-07-01T00:16:00.000-07:002011-07-01T00:53:05.658-07:00Use of Rollup, Grouping, Grouping_ID and Cube in Group By<pre class="csharpcode"><br /><b>ROLLUP</b> = Generates the simple GROUP BY aggregate rows<br /> , plus subtotal or super-aggregate rows<br /> , and also a grand total row.<br /><b>GROUPING</b> = Indicates whether a specified column expression in a GROUP BY list is aggregated or not.<br /><b>GROUPING_ID</b> = Is a function that computes the level of grouping.<br /><b>CUBE = </b>Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.<br /><h3>Sample Data</h3><span class="kwrd">DECLARE</span> @Sales <span class="kwrd">TABLE</span> (<span class="kwrd">Year</span> <span class="kwrd">INT</span>, Quarter <span class="kwrd">VARCHAR</span>(50), SalesPerson <span class="kwrd">VARCHAR</span>(50), Amount <span class="kwrd">FLOAT</span>)<br /><br /><span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> @Sales <span class="kwrd">values</span><br /> (1998, <span class="str">'Q1'</span>, <span class="str">'SalePerson1'</span>, 123)<br /> , (1998, <span class="str">'Q2'</span>, <span class="str">'SalePerson2'</span>, 234)<br /> , (1998, <span class="str">'Q3'</span>, <span class="str">'SalePerson4'</span>, 345)<br /> , (1998, <span class="str">'Q4'</span>, <span class="str">'SalePerson3'</span>, 556)<br /> , (1999, <span class="str">'Q1'</span>, <span class="str">'SalePerson1'</span>, 623)<br /> , (1999, <span class="str">'Q2'</span>, <span class="str">'SalePerson2'</span>, 734)<br /> , (1999, <span class="str">'Q3'</span>, <span class="str">'SalePerson3'</span>, 845)<br /> , (1999, <span class="str">'Q4'</span>, <span class="str">'SalePerson4'</span>, 956)<br /><br /><b>Example 1 :</b> (Single field in grouping)<br /><br /><span class="kwrd">SELECT</span><br /> [<span class="kwrd">Year</span>]<br /> , <span class="kwrd">AVG</span>(Amount) <span class="kwrd">as</span> Average<br /> , <span class="kwrd">GROUPING</span>([<span class="kwrd">Year</span>]) <span class="kwrd">as</span> [YearRollUp?]<br /><span class="kwrd">FROM</span> <br /> @Sales<br /><span class="kwrd">GROUP</span> <span class="kwrd">BY</span><br /> [<span class="kwrd">Year</span>] <span class="kwrd">WITH</span> ROLLUP<br /><br /><b>Output :</b><br /><br />Year Average YearRollUp?<br />----------- ---------------------- -----------<br />1998 314.5 0<br />1999 789.5 0<br />NULL 552 1<br /><br /><b>Example 2 :</b> (Multipal field in grouping)<br /><br /><span class="kwrd">SELECT</span><br /> [<span class="kwrd">Year</span>]<br /> , Quarter<br /> , <span class="kwrd">AVG</span>(Amount) <span class="kwrd">as</span> Average<br /> , <span class="kwrd">GROUPING</span>( [<span class="kwrd">Year</span>] ) <span class="kwrd">as</span> [YearRollUp?]<br /><span class="kwrd">FROM</span> <br /> @Sales<br /><span class="kwrd">GROUP</span> <span class="kwrd">BY</span><br /> <span class="kwrd">GROUPING</span> <span class="kwrd">SETS</span> (( [<span class="kwrd">Year</span>], Quarter), ( [<span class="kwrd">Year</span>] ), ())<br /><br /><b>Output : </b><br /><br />Year Quarter Average YearRollUp?<br />----------- ------- ---------------------- -----------<br />1998 Q1 123 0<br />1998 Q2 234 0<br />1998 Q3 345 0<br />1998 Q4 556 0<br />1998 NULL 314.5 0<br />1999 Q1 623 0<br />1999 Q2 734 0<br />1999 Q3 845 0<br />1999 Q4 956 0<br />1999 NULL 789.5 0<br />NULL NULL 552 1<br /><br /><b>Example 3 :</b>( Example of Grouping_ID )<br /><br /><span class="kwrd">SELECT</span><br /> [<span class="kwrd">Year</span>]<br /> , Quarter<br /> , <span class="kwrd">AVG</span>(Amount) <span class="kwrd">as</span> Average<br /> , GROUPING_ID( [<span class="kwrd">Year</span>], Quarter) <span class="kwrd">as</span> [x]<br /><span class="kwrd">FROM</span> <br /> @Sales<br /><span class="kwrd">GROUP</span> <span class="kwrd">BY</span><br /> <span class="kwrd">GROUPING</span> <span class="kwrd">SETS</span> (( [<span class="kwrd">Year</span>], Quarter), ( [<span class="kwrd">Year</span>] ), ( [Quarter] ), ())<br /><br /><b>Output :</b><br /><br />Year Quarter Average x<br />----------- ------- ---------------------- -----------<br />1998 Q1 123 0<br />1999 Q1 623 0<br />NULL Q1 373 2<br />1998 Q2 234 0<br />1999 Q2 734 0<br />NULL Q2 484 2<br />1998 Q3 345 0<br />1999 Q3 845 0<br />NULL Q3 595 2<br />1998 Q4 556 0<br />1999 Q4 956 0<br />NULL Q4 756 2<br />NULL NULL 552 3<br />1998 NULL 314.5 1<br />1999 NULL 789.5 1<br /><br /><b>Example 4:</b>(Example of Cube)<br /><br /><span class="kwrd">SELECT</span><br /> [<span class="kwrd">Year</span>]<br /> , [Quarter]<br /> , <span class="kwrd">AVG</span>(Amount) <span class="kwrd">as</span> Average<br /> , GROUPING_ID( [<span class="kwrd">Year</span>], [Quarter]) <span class="kwrd">as</span> [x]<br /><span class="kwrd">FROM</span> <br /> @Sales<br /><span class="kwrd">GROUP</span> <span class="kwrd">BY</span><br /> <span class="kwrd">CUBE</span>( [<span class="kwrd">Year</span>], [Quarter])<br /><br /><b>Output :</b><br /><br />Year Quarter Average x<br />----------- ------- ---------------------- -----------<br />1998 Q1 123 0<br />1999 Q1 623 0<br />NULL Q1 373 2<br />1998 Q2 234 0<br />1999 Q2 734 0<br />NULL Q2 484 2<br />1998 Q3 345 0<br />1999 Q3 845 0<br />NULL Q3 595 2<br />1998 Q4 556 0<br />1999 Q4 956 0<br />NULL Q4 756 2<br />NULL NULL 552 3<br />1998 NULL 314.5 1<br />1999 NULL 789.5 1<br /></pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-89452742816048602232011-06-30T23:15:00.000-07:002011-06-30T23:20:36.312-07:00Query with static output<h2>Query :</h2><br /><pre class="csharpcode"><br /><span class="kwrd">select</span> * <span class="kwrd">from</span><br />(<br /> <span class="kwrd">values</span> (1, 2), (2, 3), (3, 4)<br />) <span class="kwrd">as</span> MyTable(Col1, Col2)</pre><br /><pre><h2>Output :</h2><br />Col1 Col2<br />----------- -----------<br />1 2<br />2 3<br />3 4<br /></pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-90893931101131303382011-04-28T03:35:00.000-07:002011-09-21T03:43:52.111-07:00Genrate table to ENUM<div class="code"><br /><span class="ReservedKeyword">DECLARE</span> @TableName <span class="DataType">VARCHAR</span>(20) = <span class="String">'tblFileType'</span> <span class="InlineComment">-------------> type table name</span><br /><br /><span class="ReservedKeyword">DECLARE</span> @ValueField <span class="DataType">VARCHAR</span>(20) = <span class="String">'Id'</span> <span class="InlineComment">-------------> type ValueField Name</span><br /><br /><span class="ReservedKeyword">DECLARE</span> @NameField <span class="DataType">VARCHAR</span>(20) = <span class="String">'Name'</span> <span class="InlineComment">-------------> type NAme Field Name</span><br /><br /><br /><br /><span class="ReservedKeyword">BEGIN</span><br /><br /> <span class="ReservedKeyword">DECLARE</span> @SqlQuery <span class="DataType">VARCHAR</span>(<span class="Function">MAX</span>) = <br /><br /> <span class="String">'SELECT ''public enum enum'</span> + <span class="Function">REPLACE</span>( @TableName, <span class="String">'tbl'</span>, <span class="String">''</span>) + <span class="String">''' AS enum <br /><br /> UNION ALL<br /><br /> SELECT ''{''<br /><br /> UNION ALL<br /><br /> SELECT ( REPLACE('</span> + @NameField + <span class="String">', '' '', '''') + '' = '' + CAST( '</span> + @ValueField + <span class="String">' AS VARCHAR(10) ) ) + '','' FROM '</span> + @TableName<br /><br /> + <span class="String">' UNION ALL SELECT ''}'' '</span> <br /><br /> <br /><br /> <span class="ReservedKeyword">PRINT</span> @SqlQuery<br /><br /> <span class="ReservedKeyword">EXECUTE</span>( @SqlQuery )<br /><br /> <span class="InlineComment">-- SELECT Name + ' = ' + CAST( Id AS VARCHAR(10) ) + ',' FROM tblUserRoll</span><br /><br /><span class="ReservedKeyword">END</span><br /></div>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-58030257374877544622010-11-18T00:27:00.000-08:002010-11-18T22:52:53.871-08:00MVC Application Configration in IISI have referred this site to get the MVC applicaiton working using Virtual directory. There is no direct way to get the MCV working on the IIS. It can run through port by default but making it work with IIS Virtual Directory follow this link. <br /><br />1) <a href='http://haacked.com/archive/2008/11/26/asp.net-mvc-on-iis-6-walkthrough.aspx'>http://haacked.com/archive/2008/11/26/asp.net-mvc-on-iis-6-walkthrough.aspx</a><br />2) <a href='http://www.asp.net/mvc/tutorials/using-asp-net-mvc-with-different-versions-of-iis-vb'>http://www.asp.net/mvc/tutorials/using-asp-net-mvc-with-different-versions-of-iis-vb</a>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-3343813044931251762010-07-20T01:21:00.001-07:002012-03-23T03:45:13.891-07:00Get All Relationship of Primary Key<pre class="csharpcode"><br /><span class="kwrd">SELECT</span><br /> PKTableInfo.TABLE_NAME <span class="kwrd">AS</span> PK_Table_Name<br /> , ConstarintReference.UNIQUE_CONSTRAINT_NAME <span class="kwrd">AS</span> PK_CONSTRAINT_NAME<br /> , STUFF( (<br /> <span class="kwrd">SELECT</span><br /> <span class="str">','</span> + kcu.COLUMN_NAME<br /> <span class="kwrd">FROM</span> INFORMATION_SCHEMA.KEY_COLUMN_USAGE <span class="kwrd">AS</span> kcu<br /> <span class="kwrd">WHERE</span> kcu.TABLE_CATALOG = PKTableInfo.TABLE_CATALOG<br /> <span class="kwrd">AND</span> kcu.TABLE_SCHEMA = PKTableInfo.TABLE_SCHEMA<br /> <span class="kwrd">AND</span> kcu.TABLE_NAME = PKTableInfo.TABLE_NAME<br /> <span class="kwrd">AND</span> kcu.CONSTRAINT_CATALOG = PKTableInfo.CONSTRAINT_CATALOG<br /> <span class="kwrd">AND</span> kcu.CONSTRAINT_SCHEMA = PKTableInfo.CONSTRAINT_SCHEMA<br /> <span class="kwrd">AND</span> kcu.CONSTRAINT_NAME = PKTableInfo.CONSTRAINT_NAME<br /> <span class="kwrd">ORDER</span> <span class="kwrd">BY</span> kcu.ORDINAL_POSITION<br /> <span class="kwrd">FOR</span> XML <span class="kwrd">PATH</span>(<span class="str">''</span>)<br /> ), 1, 1, <span class="str">''</span>) <span class="kwrd">AS</span> [PK_CONSTRAINT_COLUMNS]<br /> , FKTableInfo.TABLE_NAME <span class="kwrd">AS</span> FK_Table_Name<br /> , ConstarintReference.CONSTRAINT_NAME <span class="kwrd">AS</span> FK_CONSTRAINT_NAME<br /> , STUFF( (<br /> <span class="kwrd">SELECT</span><br /> <span class="str">','</span> + kcu.COLUMN_NAME<br /> <span class="kwrd">FROM</span> INFORMATION_SCHEMA.KEY_COLUMN_USAGE <span class="kwrd">AS</span> kcu<br /> <span class="kwrd">WHERE</span> kcu.TABLE_CATALOG = FKTableInfo.TABLE_CATALOG<br /> <span class="kwrd">AND</span> kcu.TABLE_SCHEMA = FKTableInfo.TABLE_SCHEMA<br /> <span class="kwrd">AND</span> kcu.TABLE_NAME = FKTableInfo.TABLE_NAME<br /> <span class="kwrd">AND</span> kcu.CONSTRAINT_CATALOG = FKTableInfo.CONSTRAINT_CATALOG<br /> <span class="kwrd">AND</span> kcu.CONSTRAINT_SCHEMA = FKTableInfo.CONSTRAINT_SCHEMA<br /> <span class="kwrd">AND</span> kcu.CONSTRAINT_NAME = FKTableInfo.CONSTRAINT_NAME<br /> <span class="kwrd">ORDER</span> <span class="kwrd">BY</span> kcu.ORDINAL_POSITION<br /> <span class="kwrd">FOR</span> XML <span class="kwrd">PATH</span>(<span class="str">''</span>)<br /> ), 1, 1, <span class="str">''</span>) <span class="kwrd">AS</span> [FK_CONSTRAINT_COLUMNS]<br /><span class="kwrd">FROM</span> INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS <span class="kwrd">AS</span> ConstarintReference<br /> <span class="kwrd">INNER</span> <span class="kwrd">JOIN</span> INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE <span class="kwrd">AS</span> PKTableInfo <span class="kwrd">ON</span> PKTableInfo.CONSTRAINT_CATALOG = ConstarintReference.UNIQUE_CONSTRAINT_CATALOG<br /> <span class="kwrd">AND</span> PKTableInfo.CONSTRAINT_SCHEMA = ConstarintReference.UNIQUE_CONSTRAINT_SCHEMA<br /> <span class="kwrd">AND</span> PKTableInfo.CONSTRAINT_NAME = ConstarintReference.UNIQUE_CONSTRAINT_NAME<br /> <span class="kwrd">INNER</span> <span class="kwrd">JOIN</span> INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE <span class="kwrd">AS</span> FKTableInfo <span class="kwrd">ON</span> ConstarintReference.CONSTRAINT_CATALOG = FKTableInfo.CONSTRAINT_CATALOG<br /> <span class="kwrd">AND</span> ConstarintReference.CONSTRAINT_SCHEMA = FKTableInfo.CONSTRAINT_SCHEMA<br /> <span class="kwrd">AND</span> ConstarintReference.CONSTRAINT_NAME = FKTableInfo.CONSTRAINT_NAME<br /><span class="kwrd">ORDER</span> <span class="kwrd">BY</span> PK_Table_Name, FK_Table_Name<br /><span class="kwrd">GO</span><br /></pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-80690011591900955522010-07-17T03:15:00.001-07:002012-03-23T03:44:47.402-07:00Get detailed error handling information in SQLRefer this link<br /><br /><a href='http://msdn.microsoft.com/en-us/library/ms175976.aspx'>http://msdn.microsoft.com/en-us/library/ms175976.aspx</a><br /><br /><br/>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-65310639035855176622010-07-14T23:06:00.001-07:002012-03-23T03:42:38.170-07:00Searching and Multipal Selection Criteria in JQuery<pre class="csharpcode"><br /><br /> <div><br /> <div style=<span class="str">"float: left; width: 150px"</span>><br /> Your Gender :<br /> </div><br /> <div style=<span class="str">"float: left; width: 250px"</span>><br /> <asp:RadioButtonList ID=<span class="str">"rdbGender"</span> runat=<span class="str">"server"</span>><br /> <asp:ListItem Text=<span class="str">"Male"</span> Value=<span class="str">"1"</span>></asp:ListItem><br /> <asp:ListItem Text=<span class="str">"Female"</span> Value=<span class="str">"0"</span>></asp:ListItem><br /> </asp:RadioButtonList><br /> </div><br /> </div><br /> <div style=<span class="str">"clear: both"</span>><br /> <div style=<span class="str">"float: left; width: 150px"</span>><br /> Searching :<br /> </div><br /> <div style=<span class="str">"float: left; width: 250px"</span>><br /> <asp:RadioButtonList ID=<span class="str">"rdbSearching"</span> runat=<span class="str">"server"</span>><br /> <asp:ListItem Text=<span class="str">"Groom"</span> Value=<span class="str">"1"</span>></asp:ListItem><br /> <asp:ListItem Text=<span class="str">"Bride"</span> Value=<span class="str">"0"</span>></asp:ListItem><br /> </asp:RadioButtonList><br /> </div><br /> </div><br /><br /> <script type=<span class="str">"text/javascript"</span> language=<span class="str">"javascript"</span>><br /> $(document).ready(function() {<br /> $(<span class="str">"input[id*='rdbGender'], input[id*='rdbSearching']"</span>).click(function() {<br /><br /> var foundIn = $(<span class="kwrd">this</span>).attr(<span class="str">'id'</span>).toString().search(<span class="kwrd">new</span> RegExp(/rdbGender/i));<br /><br /> var strOpositeRadioButton = <span class="str">"rdbGender"</span>;<br /><br /> <span class="kwrd">if</span> (foundIn > -1) {<br /> strOpositeRadioButton = <span class="str">"rdbSearching"</span>;<br /> }<br /><br /> <span class="kwrd">if</span> ($(<span class="kwrd">this</span>).val() == 1) {<br /> $(<span class="str">"input[id*="</span> + strOpositeRadioButton + <span class="str">"][value=0]"</span>).attr(<span class="str">"checked"</span>, <span class="str">"checked"</span>);<br /> }<br /> <span class="kwrd">else</span> {<br /> $(<span class="str">"input[id*="</span> + strOpositeRadioButton + <span class="str">"][value=1]"</span>).attr(<span class="str">"checked"</span>, <span class="str">"checked"</span>);<br /> }<br /> });<br /> });<br /> </script></pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-11864622387614567502010-07-12T01:21:00.000-07:002010-07-12T01:36:58.836-07:00JQuery<pre class="csharpcode"><br /><span style="font-weight:bold;">Ready</span><br />-----------------------------------<br />$(document).read(function(){<br /> alert(<span class="str">'hi'</span>);<br />})<br /><br />$(document).read(FormSetting)<br />Function FormSetting()<br />{<br /> alert(<span class="str">'hi'</span>);<br />}<br />-----------------------------------<br /><br /><span style="font-weight:bold;">Selection Criteria</span><br />-----------------------------------<br />=> ID<br /> $(<span class="str">"#txtUserName"</span>)<br /><br />=> Control<br /> $(<span class="str">"input"</span>)<br /> $(<span class="str">"div"</span>)<br /><br />=> Inner Control<br /> $(<span class="str">"div div input"</span>)<br /><br />=> Class<br /> $(<span class="str">".button"</span>)<br /><br />=> Selection on standard attributes<br /> $(<span class="str">"input[type='button']"</span>) <br /><br />=> Selection on custome attributes<br /> <input type=<span class="str">"text"</span> IsGridText=<span class="str">"True"</span> /><br /> $(<span class="str">"input[IsGridText ='True']"</span>)<br />-----------------------------------<br /><br /><span style="font-weight:bold;">Functions</span><br />-----------------------------------<br />=> VAL()<br /> Var strUserName = $(<span class="str">"#txtUserName).val();<br /> $("</span>#txtUserName).val(<span class="str">"jsd24"</span>);<br /><br />=> HTML()<br /> Var strInnerHtml = $(<span class="str">"#divUserInfo).html();<br /> $("</span>#divUserInfo).html(<span class="str">"<a href='#' OnClick='Alert(1)'>Click Me</a>"</span>);<br /><br />=> Attr ( attribute)<br /> var IsGridText = $(<span class="str">"#txtUserName"</span>).attr(<span class="str">"IsGridText"</span>);<br /> $(<span class="str">"#txtUserName"</span>).attr(<span class="str">"IsGridText"</span>, <span class="str">"False"</span>);<br /> $(<span class="str">"#txtUserName"</span>).attr({IsGridText:<span class="str">'True'</span>, IsSelected:<span class="str">'True'</span>});<br /><br />=> Css (style sheet)<br /> var color = $(<span class="str">"#txtUserName"</span>).css(<span class="str">"color"</span>);<br /> $(<span class="str">"#txtUserName"</span>).css(<span class="str">"color"</span>, <span class="str">"red"</span>);<br /> $(<span class="str">"#txtUserName"</span>).css({<span class="str">'width'</span>: <span class="str">'100px'</span>, <span class="str">'height'</span>:<span class="str">'25px'</span>});<br /> <br />=> Each<br /> $(<span class="str">"input"</span>).each(function() { $(<span class="kwrd">this</span>).val(<span class="str">"jd"</span>); })<br /><br />=> addClass()<br /> $(<span class="str">"input[type='button']"</span>).addClass(<span class="str">"button"</span>);<br /><br />=> removeClass()<br /> $(<span class="str">"input[type='button']"</span>).removeClass(<span class="str">"button"</span>);<br /><br />=> haseClass()<br /> $(<span class="str">"#txtUserName"</span>).haseClass(<span class="str">"UserName"</span>);<br /> $(<span class="str">"#txtUserName"</span>).haseClass(<span class="str">"EmailID"</span>);<br /><br />=> toggleClass()<br /> $(<span class="str">"#divUserInfo"</span>).toggleClass(<span class="str">"Content"</span>);<br /><br />=> height()<br /> $(<span class="str">"#divUserInfo"</span>).height();<br /><br />=> innerHeight()<br /> $(<span class="str">"#divUserInfo"</span>).innerHeight();<br /><br />=> outerHeight()<br /> $(<span class="str">"#divUserInfo"</span>).outerHeight();<br /><br />=> width()<br /> $(<span class="str">"#divUserInfo"</span>).width();<br /><br />=> innerWidth ()<br /> $(<span class="str">"#divUserInfo"</span>).innerWidth ();<br /><br />=> outerWidth ()<br /> $(<span class="str">"#divUserInfo"</span>).outerWidth ();<br /><br />=> fadeIn()<br /> $(<span class="str">"#divUserInfo"</span>).fadeIn();<br /><br />=> fadeOut()<br /> $(<span class="str">"#divUserInfo"</span>).fadeOut ();<br /><br />=> hide()<br /> $(<span class="str">"#divUserInfo"</span>).hide();<br /><br />=> show()<br /> $(<span class="str">"#divUserInfo"</span>).show();<br /><br />=> appendTo <br />$(<span class="str">"Hello"</span>).appendTo(<span class="str">"#divUserInfo"</span>);<br /><br />=> clone<br />$(<span class="str">"#divUserInfo"</span>).clone().appendTo(<span class="str">"#divUserInfo1"</span>);<br /><br />=> not<br />$(<span class="str">"#ddlProductList option"</span>).not(<span class="str">"[value*='Computer']"</span>).clone().appendTo(<span class="str">"#ddlFilterList"</span>);<br />-----------------------------------<br /><br /><span style="font-weight:bold;">Multiple Action in single line</span><br />-----------------------------------<br />$(<span class="str">"#txtUserName"</span>).val(<span class="str">"JSD24"</span>).css(<span class="str">"color"</span>, <span class="str">"green"</span>).attr(<span class="str">"IsSelected"</span>, <span class="str">"true"</span>)<br />-----------------------------------<br /><br /><span style="font-weight:bold;">Events</span><br />-----------------------------------<br />=> change()<br /> $(<span class="str">"#txtUserName"</span>).change(function(){});<br /> <br />=> click()<br /> $(<span class="str">"a"</span>).click(function(){});<br /><br />=> dblclick()<br /> $(<span class="str">"a"</span>).dblclick(function(){});<br /><br />=> focus()<br /> $(<span class="str">"#txtUserName"</span>).focus();<br /><br />=> focusIn()<br /> $(<span class="str">"#txtUserName"</span>).focusIn(function());<br /><br />=> focusOut()<br /> $(<span class="str">"#txtUserName"</span>).focusOut(function());<br /><br />=> hover()<br /> $(<span class="str">"#txtUserName"</span>).hover(function());<br /><br />=> keydown()<br /> $(<span class="str">"#txtUserName"</span>).keydown(function());<br /><br />=> keyup()<br /> $(<span class="str">"#txtUserName"</span>).keyup(function());<br /><br />=> keypress ()<br /> $(<span class="str">"#txtUserName"</span>).keypress(function());<br /><br />=> mousedown()<br /> $(<span class="str">"#txtUserName"</span>).mousedown(function());<br /><br />=> mouseup()<br /> $(<span class="str">"#txtUserName"</span>).mouseup(function());<br /><br />=> mousemove()<br /> $(<span class="str">"#txtUserName"</span>).mousemove(function());<br />-----------------------------------<br /><br /><span style="font-weight:bold;">AJAX / JSON</span><br />-----------------------------------<br />$.ajax({<br /> url : <span class="str">"test.html"</span>,<br /> type:<span class="str">"GET"</span> , <span class="rem">//Post</span><br /> data : { id : <span class="str">"2"</span>, pid : <span class="str">"1"</span> },<br /> context: document.body,<br /> datatype : <span class="str">"html"</span> <span class="rem">//JSON, XML</span><br /> success : handleResponse,<br /> error : handleError<br />});<br /><br />Function handleResponse(data)<br />{<br /> alert(data);<br />}<br /><br />Function handleError(msg)<br />{<br /> alert(msg);<br />}<br />-----------------------------------</pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-13735818735317402282010-07-11T21:32:00.000-07:002010-07-14T05:17:29.568-07:00Using Enum in Javascript<pre class="csharpcode"><br /><script type=<span class="str">"text/javascript"</span> language=<span class="str">"javascript"</span>><br /> var enumTabInfo = { <br /> BasicInformation: 1, <br /> SkillDetail: 2, <br /> EducationDetail: 3, <br /> ProjectDetail: 4, <br /> CompanyDetail: 5 <br /> }<br /> <br /> $(document).ready(function(){ <br /> $(<span class="str">"div.UserTab"</span>).Hide();<br /> <br /> var intTabNo = Number($(<span class="str">"#<%= hdnCurrentTab.ClientID %>"</span>).val());<br /><br /> <span class="kwrd">switch</span> (intTabNo) {<br /> <span class="kwrd">case</span> enumPageStep.BasicInformation:<br /> $(<span class="str">"#divBasicInformation"</span>).show();<br /> <span class="kwrd">break</span>;<br /><br /> <span class="kwrd">case</span> enumPageStep.SkillDetail:<br /> $(<span class="str">"#divSkillDetail"</span>).show();<br /> <span class="kwrd">break</span>;<br /><br /> <span class="kwrd">case</span> enumPageStep.EducationDetail:<br /> $(<span class="str">"#divEducationDetail"</span>).show();<br /> <span class="kwrd">break</span>;<br /><br /> <span class="kwrd">case</span> enumPageStep.ProjectDetail:<br /> $(<span class="str">"#divProjectDetail"</span>).show();<br /> <span class="kwrd">break</span>;<br /><br /> <span class="kwrd">case</span> enumPageStep.CompanyDetail:<br /> $(<span class="str">"#divCompanyDetail"</span>).show();<br /> <span class="kwrd">break</span>;<br /> }<br /> });<br /></script></pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-54586996675779947742010-07-10T03:39:00.000-07:002011-05-31T22:16:02.017-07:00Enum Extention<span style="font-weight:bold;">Enum Extention :</span><pre class="csharpcode"><br /> <span class="kwrd">public</span> <span class="kwrd">static</span> <span class="kwrd">class</span> EnumExtention<br /> {<br /> <span class="kwrd">public</span> <span class="kwrd">static</span> Dictionary<<span class="kwrd">int</span>, <span class="kwrd">string</span>> GetEnumData(<span class="kwrd">this</span> Type EnumType)<br /> {<br /> Dictionary<<span class="kwrd">int</span>, <span class="kwrd">string</span>> objEnumList = <span class="kwrd">new</span> Dictionary<<span class="kwrd">int</span>, <span class="kwrd">string</span>>();<br /><br /> <span class="kwrd">if</span> (EnumType != <span class="kwrd">null</span> && EnumType.IsEnum)<br /> {<br /> <span class="kwrd">foreach</span> (<span class="kwrd">int</span> intCount <span class="kwrd">in</span> Enum.GetValues(EnumType))<br /> {<br /> objEnumList.Add(intCount, Enum.GetName(EnumType, intCount).ToString().Replace(<span class="str">"_"</span>, <span class="str">" "</span>));<br /> }<br /> }<br /> <span class="kwrd">return</span> objEnumList;<br /> }<br /> }<br /></pre><span style="font-weight:bold;">Get Enum Data :</span><pre class="csharpcode"><br /> <span class="kwrd">private</span> <span class="kwrd">enum</span> enumUserType<br /> {<br /> Admin,<br /> Sales_Department,<br /> Purchase_Department,<br /> Guest<br /> }<br /><br /> <span class="kwrd">protected</span> <span class="kwrd">void</span> Page_Load(<span class="kwrd">object</span> sender, EventArgs e)<br /> {<br /> ddlUserType.DataSource = <span class="kwrd">typeof</span>(enumUserType).GetEnumData();<br /> <br /> ddlUserType.DataTextField = <span class="str">"Value"</span>;<br /> ddlUserType.DataValueField = <span class="str">"Key"</span>;<br /><br /> ddlUserType.DataBind();<br /> }<br /></pre><span style="font-weight:bold;">Output :</span><pre class="csharpcode"><br /><select name=<span class="str">"ddlUserType"</span> id=<span class="str">"ddlUserType"</span>><br /> <option <span class="kwrd">value</span>=<span class="str">"1"</span>>Admin</option><br /> <option <span class="kwrd">value</span>=<span class="str">"2"</span>>Sales Department</option><br /> <option <span class="kwrd">value</span>=<span class="str">"3"</span>>Purchase Department</option><br /> <option <span class="kwrd">value</span>=<span class="str">"4"</span>>Guest</option> <br /></select><br /></pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-49718112217762450322010-07-09T23:19:00.000-07:002010-07-09T23:23:05.612-07:00OUTPUT Clause<span style="font-weight:bold;">SQL QUERY :</span><pre class="csharpcode"><br /> <span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> #tblSource ( ID <span class="kwrd">INT</span>, GroupID <span class="kwrd">INT</span>, Name <span class="kwrd">VARCHAR</span>(10) )<br /> <span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> #tblTarget ( ID <span class="kwrd">INT</span>, GroupID <span class="kwrd">INT</span>, Name <span class="kwrd">VARCHAR</span>(10) )<br /> <span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> #tblOutPut ( OutputAction <span class="kwrd">VARCHAR</span>(20), ID <span class="kwrd">INT</span>, GroupID <span class="kwrd">INT</span>, NewValueName <span class="kwrd">VARCHAR</span>(10), OldValueName <span class="kwrd">VARCHAR</span>(10) )<br /> <br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblSource <span class="kwrd">VALUES</span> (1,1,<span class="str">'a11'</span>)<br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblSource <span class="kwrd">VALUES</span> (2,1,<span class="str">'a21'</span>)<br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblSource <span class="kwrd">VALUES</span> (3,1,<span class="str">'a31'</span>)<br /> <br /> -<span class="rem">-- Inserted</span><br /> <span class="kwrd">BEGIN</span><br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblTarget<br /> <span class="kwrd">OUTPUT</span> <br /> <span class="str">'Inserted'</span> <span class="kwrd">AS</span> OutputAction<br /> , INSERTED.ID <br /> , INSERTED.GroupID<br /> , INSERTED.Name<br /> , <span class="kwrd">NULL</span><br /> <span class="kwrd">INTO</span> #tblOutPut<br /> <span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> #tblSource<br /> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span><br /> <span class="kwrd">SELECT</span> ID + 10, GroupID, Name <span class="kwrd">FROM</span> #tblSource;<br /> <span class="kwrd">END</span><br /> <br /> -<span class="rem">-- Updated</span><br /> <span class="kwrd">BEGIN</span><br /> <span class="kwrd">UPDATE</span> #tblTarget<br /> <span class="kwrd">SET</span> Name = <span class="kwrd">CAST</span>(ID <span class="kwrd">AS</span> <span class="kwrd">VARCHAR</span>) + <span class="str">' : '</span> + Name<br /> <span class="kwrd">OUTPUT</span> <br /> <span class="str">'Updated'</span> <span class="kwrd">AS</span> OutputAction<br /> , INSERTED.ID <br /> , INSERTED.GroupID<br /> , INSERTED.Name<br /> , deleted.Name<br /> <span class="kwrd">INTO</span> #tblOutPut;<br /> <span class="kwrd">END</span><br /> <br /> -<span class="rem">-- Deleted</span><br /> <span class="kwrd">BEGIN</span><br /> <span class="kwrd">DELETE</span> <span class="kwrd">FROM</span> #tblTarget<br /> <span class="kwrd">OUTPUT</span> <br /> <span class="str">'Deleted'</span> <span class="kwrd">AS</span> OutputAction<br /> , deleted.ID <br /> , deleted.GroupID<br /> , <span class="kwrd">null</span><br /> , deleted.Name<br /> <span class="kwrd">INTO</span> #tblOutPut<br /> <span class="kwrd">WHERE</span> ID >= 10;<br /> <span class="kwrd">END</span><br /> <br /> <span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> #tblOutPut<br /><br /> <span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> #tblSource<br /> <span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> #tblTarget<br /> <span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> #tblOutPut<br /></pre><span style="font-weight:bold;">Output :</span><pre>OutputAction ID GroupID NewValueName OldValueName<br />-------------------- ----------- ----------- ------------ ------------<br />Inserted 1 1 a11 NULL<br />Inserted 2 1 a21 NULL<br />Inserted 3 1 a31 NULL<br />Inserted 11 1 a11 NULL<br />Inserted 12 1 a21 NULL<br />Inserted 13 1 a31 NULL<br />Updated 1 1 1 : a11 a11<br />Updated 2 1 2 : a21 a21<br />Updated 3 1 3 : a31 a31<br />Updated 11 1 11 : a11 a11<br />Updated 12 1 12 : a21 a21<br />Updated 13 1 13 : a31 a31<br />Deleted 11 1 NULL 11 : a11<br />Deleted 12 1 NULL 12 : a21<br />Deleted 13 1 NULL 13 : a31</pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-35889207251397627862010-07-09T22:04:00.001-07:002010-07-09T22:08:03.532-07:00Merge Statement with insert into output table<pre class="csharpcode"><br /> <span class="kwrd">DECLARE</span> @tblMergeOutput <span class="kwrd">TABLE</span><br /> (<br /> ID BIGINT <span class="kwrd">IDENTITY</span>(1,1)<br /> , MergeAction NVARCHAR(100)<br /> , InsertedId <span class="kwrd">INT</span><br /> , DeletedId <span class="kwrd">INT</span><br /> , GroupID <span class="kwrd">INT</span><br /> )<br /><br /> <span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> #tblSource ( ID <span class="kwrd">INT</span>, GroupID <span class="kwrd">INT</span>, Name <span class="kwrd">VARCHAR</span>(10) )<br /> <span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> #tblTarget ( ID <span class="kwrd">INT</span>, GroupID <span class="kwrd">INT</span>, Name <span class="kwrd">VARCHAR</span>(10) )<br /><br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblSource <span class="kwrd">VALUES</span> (1,1,<span class="str">'a11'</span>)<br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblSource <span class="kwrd">VALUES</span> (2,1,<span class="str">'a21'</span>)<br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblSource <span class="kwrd">VALUES</span> (3,1,<span class="str">'a31'</span>)<br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblSource <span class="kwrd">VALUES</span> (4,1,<span class="str">'a41'</span>)<br /><br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblTarget <span class="kwrd">VALUES</span> (1,1,<span class="str">'a1'</span>)<br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblTarget <span class="kwrd">VALUES</span> (3,1,<span class="str">'a3'</span>)<br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblTarget <span class="kwrd">VALUES</span> (5,1,<span class="str">'a5'</span>)<br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblTarget <span class="kwrd">VALUES</span> (6,2,<span class="str">'b2'</span>)<br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> #tblTarget <span class="kwrd">VALUES</span> (7,2,<span class="str">'b1'</span>)<br /><br /> <br /> --<span class="rem">-- Method 2 : Using Output insert into table</span><br /> <span class="kwrd">BEGIN</span><br /> <span class="kwrd">INSERT</span> <span class="kwrd">INTO</span> @tblMergeOutput<br /> <span class="kwrd">SELECT</span> MergeAction, InsertedId, DeletedID, GroupID <br /> <span class="kwrd">FROM</span><br /> (<br /> MERGE #tblTarget<br /> <span class="kwrd">USING</span> (<br /> <span class="kwrd">SELECT</span> *<br /> <span class="kwrd">FROM</span> #tblSource<br /> ) <span class="kwrd">AS</span> tblSource<br /> <span class="kwrd">ON</span> tblSource.GroupID = #tblTarget.GroupID<br /> <span class="kwrd">AND</span> tblSource.ID = #tblTarget.ID<br /> <span class="kwrd">WHEN</span> MATCHED <span class="kwrd">THEN</span> <br /> <span class="kwrd">UPDATE</span><br /> <span class="kwrd">SET</span> Name = tblSource.Name<br /> <span class="kwrd">WHEN</span> <span class="kwrd">NOT</span> MATCHED <span class="kwrd">THEN</span><br /> <span class="kwrd">INSERT</span> (<br /> ID<br /> , GroupID<br /> , Name<br /> )<br /> <span class="kwrd">VALUES</span> ( <br /> tblSource.ID<br /> , tblSource.GroupID<br /> , tblSource.Name<br /> )<br /> <span class="kwrd">WHEN</span> <span class="kwrd">NOT</span> MATCHED <span class="kwrd">BY</span> SOURCE <span class="kwrd">AND</span> #tblTarget.GroupID = 1<br /> <span class="kwrd">THEN</span> <span class="kwrd">DELETE</span><br /> <span class="kwrd">OUTPUT</span> <br /> $<span class="kwrd">action</span> <span class="kwrd">AS</span> MergeAction<br /> , inserted.Id <span class="kwrd">AS</span> InsertedId<br /> , deleted.Id <span class="kwrd">AS</span> DeletedID<br /> , tblSource.GroupID <span class="kwrd">AS</span> GroupID<br /> ) <span class="kwrd">AS</span> MergeOutput (MergeAction, InsertedId, DeletedID, GroupID);<br /> <span class="kwrd">END</span><br /><br /> <span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> #tblTarget <span class="kwrd">order</span> <span class="kwrd">by</span> GroupID, ID<br /> <span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> @tblMergeOutput<br /><br /> <span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> #tblSource<br /> <span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> #tblTarget<br /></pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-84042676536280519382010-07-08T23:14:00.001-07:002012-03-23T03:40:49.150-07:00Enable AD Hoc Distributed Queries OPENROWSETBy default Ad Hoc Distributed Queries are disabled on SQL server<br /><br />If you try to run OPENROWSET you will get this error message<br /><br />Server: Msg 15281, Level 16, State 1, Line 1<br /><span style='color:red'><br />SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.<br /></span><br /><br />To enable it do this<br /><pre class="csharpcode"><br /><span class="kwrd">EXECUTE</span> sp_configure <span class="str">'show advanced options'</span>, 1<br /><span class="kwrd">RECONFIGURE</span> <span class="kwrd">WITH</span> OVERRIDE<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">EXECUTE</span> sp_configure <span class="str">'Ad Hoc Distributed Queries'</span>, <span class="str">'1'</span><br /><span class="kwrd">RECONFIGURE</span> <span class="kwrd">WITH</span> OVERRIDE<br /><span class="kwrd">GO</span><br /><br /><span class="kwrd">EXECUTE</span> sp_configure <span class="str">'show advanced options'</span>, 0<br /><span class="kwrd">RECONFIGURE</span> <span class="kwrd">WITH</span> OVERRIDE<br />GO</pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-30884261452953076372010-07-08T22:54:00.000-07:002010-07-08T23:14:12.127-07:00Openrowset Command in SQL<span style="font-weight:bold;">Syntax :</span><pre class="csharpcode"><br /><span class="kwrd">OPENROWSET</span> <br />( { <span class="str">'provider_name'</span>, { <span class="str">'datasource'</span>;<span class="str">'user_id'</span>;<span class="str">'password'</span> <br /> | <span class="str">'provider_string'</span> } <br /> , { [ <span class="kwrd">catalog</span>. ] [ <span class="kwrd">schema</span>. ] <span class="kwrd">object</span> <br /> | <span class="str">'query'</span> <br /> } <br /> | <span class="kwrd">BULK</span> <span class="str">'data_file'</span>, <br /> { FORMATFILE =<span class="str">'format_file_path'</span> [ <bulk_options> ]<br /> | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }<br />} )<bulk_options> ::=<br /> [ , CODEPAGE = { <span class="str">'ACP'</span> | <span class="str">'OEM'</span> | <span class="str">'RAW'</span> | <span class="str">'code_page'</span> } ] <br /> [ , ERRORFILE =<span class="str">'file_name'</span> ]<br /> [ , FIRSTROW = first_row ] <br /> [ , LASTROW = last_row ] <br /> [ , MAXERRORS = maximum_errors ] <br /> [ , ROWS_PER_BATCH =rows_per_batch ] <br /></pre><span style="font-weight:bold;">Import CSV File : </span><pre class="csharpcode"><br /><span class="kwrd">SELECT</span> <br /> <span class="str">'EmployeeInfo.csv'</span> <span class="kwrd">AS</span> FileName<br /> , <span class="str">'.csv'</span> <span class="kwrd">AS</span> FileType<br /> , BulkColumn<br /><span class="kwrd">FROM</span> <span class="kwrd">OPENROWSET</span>(<span class="kwrd">BULK</span> N<span class="str">'C:\EmployeeInfo.csv'</span>, SINGLE_CLOB) <span class="kwrd">AS</span> Document;<br /></pre><span style="font-weight:bold;">Output :</span><pre>FileName FileType BulkColumn<br />---------------- -------- ----------------------------------------------<br />EmployeeInfo.csv .csv Eugene Malarky,Grove Street Smithtown,New York</pre><span style="font-weight:bold;">Example Of SQL Database :</span><pre class="csharpcode"><br /><span class="kwrd">SELECT</span> JSDTest.*<br /><span class="kwrd">FROM</span> <span class="kwrd">OPENROWSET</span>(<span class="str">'SQLOLEDB'</span>,<span class="str">'PC\SQLSERVER2008'</span>;<span class="str">'sa'</span>;<span class="str">'softweb'</span>,<br /> <span class="str">'SELECT CategoryName, ProductName<br /> FROM JSDTest.dbo.Product AS Product<br /> INNER JOIN JSDTest.dbo.Category AS Category ON Category.CategoryID = Product.CategoryID<br /> ORDER BY CategoryName, ProductName'</span>) <span class="kwrd">AS</span> JSDTest</pre><span style="font-weight:bold;">Example of MDB :</span><pre class="csharpcode"><br /><span class="kwrd">SELECT</span> a.*<br /><span class="kwrd">FROM</span> <span class="kwrd">OPENROWSET</span>(<span class="str">'Microsoft.Jet.OLEDB.4.0'</span>, <br /> <span class="str">'c:\MSOffice\Access\Samples\northwind.mdb'</span>;<span class="str">'admin'</span>;<span class="str">'mypwd'</span>, Orders) <br /> <span class="kwrd">AS</span> a<br /></pre><span style="font-weight:bold;">Example of Excel :</span><pre class="csharpcode"><br /><span class="kwrd">SELECT</span> *<br /><span class="kwrd">INTO</span> db1.dbo.table1<br /><span class="kwrd">FROM</span> <span class="kwrd">OPENROWSET</span>(<span class="str">'MSDASQL'</span>,<br /> <span class="str">'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\book1.xls'</span>,<br /> <span class="str">'SELECT * FROM [sheet1$]'</span>)</pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-4299177989940132702010-07-08T22:38:00.001-07:002012-03-23T03:44:35.136-07:00Import CSV File using SQL Query<span style="font-weight:bold;">SQL Query :</span><br /><pre class="csharpcode"><br /><span class="kwrd">DECLARE</span> @FileName <span class="kwrd">VARCHAR</span>(100) = <span class="str">'C:\EmployeeInfo.csv'</span><br /><br /><span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> #UserInfo<br />(<br /> UserName <span class="kwrd">VARCHAR</span>(50)<br /> , Address <span class="kwrd">VARCHAR</span>(100)<br /> , CITY <span class="kwrd">VARCHAR</span>(10)<br />)<br /><br />/*** <span class="kwrd">Get</span> <span class="kwrd">Data</span> <span class="kwrd">From</span> Import <span class="kwrd">File</span> ***/<br /><span class="kwrd">DECLARE</span> @SqlQuery <span class="kwrd">VARCHAR</span>(2000)<br /><span class="kwrd">SELECT</span> @SqlQuery = <span class="str">'BULK INSERT'</span><br /> + <span class="str">' #UserInfo'</span> <br /> + <span class="str">' FROM'</span><br /> + <span class="str">' '</span><span class="str">''</span> + @FileName + <span class="str">''</span><span class="str">' '</span><br /> + <span class="str">' WITH (FIELDTERMINATOR='</span><span class="str">','</span><span class="str">''</span><br /> + <span class="str">',ROWTERMINATOR = '</span><span class="str">''</span> + <span class="kwrd">CHAR</span>(10) + <span class="str">''</span><span class="str">')'</span><br /><br /><span class="kwrd">EXECUTE</span> ( @SqlQuery )<br />/*** <span class="kwrd">Get</span> <span class="kwrd">Data</span> <span class="kwrd">From</span> Import <span class="kwrd">File</span> ***/<br /><br /><span class="kwrd">SELECT</span> * <span class="kwrd">FROM</span> #UserInfo<br /><br /><span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> #UserInfo<br /></pre><span style="font-weight:bold;">CSV File :</span><pre>Eugene Malarky,Grove Street Smithtown,New York<br />Sam Adams,Main Street,Ashville<br />Loren Sandler,Main Street,Hometown<br /></pre><span style="font-weight:bold;">Output :</span><pre>UserName Address CITY<br />------------------------------ ------------------------------ ----------<br />Eugene Malarky Grove Street Smithtown New York<br /><br />Sam Adams Main Street Ashville<br /><br />Loren Sandler Main Street Hometown</pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-10704723341774478232010-07-08T03:25:00.000-07:002010-07-08T03:35:38.772-07:00Excel Permission Configration for ASP.NET ApplicationRefer this link<br /><br /><span style="font-weight:bold;">http://blog.crowe.co.nz/archive/2006/03/02/589.aspx</span>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-40835341977936482212010-07-08T03:13:00.000-07:002010-07-08T03:37:18.358-07:00Create XLS File in ASP.NET application<span style="font-weight:bold;">Class CreateXLS :</span><br /><pre class="csharpcode"><br /><span class="kwrd">using</span> System;<br /><span class="kwrd">using</span> System.Collections.Generic;<br /><span class="kwrd">using</span> System.Linq;<br /><span class="kwrd">using</span> System.Web;<br /><span class="kwrd">using</span> Microsoft.Office.Interop;<br /><span class="kwrd">using</span> Microsoft.Office.Interop.Excel;<br /><span class="kwrd">using</span> System.IO;<br /><span class="kwrd">using</span> System.Drawing;<br /><span class="kwrd">using</span> System.Reflection;<br /><br /><span class="kwrd">public</span> <span class="kwrd">class</span> CreateXLS<br />{<br /> <span class="preproc">#region</span> Excel Variables<br /> <span class="kwrd">private</span> Microsoft.Office.Interop.Excel.Application oXL;<br /> <span class="kwrd">private</span> Microsoft.Office.Interop.Excel._Workbook oWB;<br /> <span class="kwrd">private</span> Microsoft.Office.Interop.Excel._Worksheet oSheet;<br /><br /> <span class="kwrd">private</span> <span class="kwrd">long</span> iExcelRow = 2;<br /> <span class="kwrd">private</span> <span class="kwrd">long</span> iExcelCol = 2;<br /> <span class="kwrd">private</span> <span class="kwrd">long</span> iStartRow = 0;<br /><br /> <span class="kwrd">private</span> <span class="kwrd">string</span> sFromCellNo = <span class="str">""</span>;<br /> <span class="kwrd">private</span> <span class="kwrd">string</span> sToCellNo = <span class="str">""</span>;<br /><br /> <span class="kwrd">private</span> <span class="kwrd">enum</span> enumXLSFields<br /> {<br /> EmployeeName,<br /> Address,<br /> AccountNo,<br /> Earnings,<br /> Deduction,<br /> NetSalary<br /> }<br /><br /> <span class="kwrd">private</span> <span class="kwrd">float</span> LogoLeft = 50;<br /> <span class="kwrd">private</span> <span class="kwrd">float</span> LogoTop = 15;<br /> <span class="kwrd">private</span> <span class="kwrd">float</span> LogoWidth = 52;<br /> <span class="kwrd">private</span> <span class="kwrd">float</span> LogoHeight = 52;<br /> <span class="preproc">#endregion</span><br /><br /> <span class="preproc">#region</span> Properties<br /> <span class="kwrd">public</span> <span class="kwrd">string</span> XLSPath { get; set; }<br /> <span class="kwrd">public</span> <span class="kwrd">string</span> XLSFileName { get; set; }<br /> <span class="kwrd">public</span> <span class="kwrd">string</span> XLSSheetName { get; set; }<br /><br /> <span class="kwrd">public</span> List<EmployeeInfo> EmployeeList { get; set; }<br /><br /> <span class="rem">/// <summary></span><br /> <span class="rem">/// Logo Properties</span><br /> <span class="rem">/// </summary></span><br /> <span class="kwrd">public</span> <span class="kwrd">string</span> LogoFile { get; set; }<br /> <span class="preproc">#endregion</span><br /><br /> <span class="rem">/// <summary></span><br /> <span class="rem">/// Cunstructor</span><br /> <span class="rem">/// </summary></span><br /> <span class="kwrd">public</span> CreateXLS()<br /> {<br /> oXL = <span class="kwrd">new</span> Microsoft.Office.Interop.Excel.Application();<br /> oXL.Visible = <span class="kwrd">false</span>;<br /> oXL.UserControl = <span class="kwrd">false</span>;<br /><br /> <span class="kwrd">this</span>.EmployeeList = <span class="kwrd">new</span> List<EmployeeInfo>();<br /> }<br /><br /> <span class="preproc">#region</span> Public Methods<br /> <span class="kwrd">public</span> <span class="kwrd">void</span> GenerateXLSFile()<br /> {<br /> <span class="kwrd">try</span><br /> {<br /> <span class="preproc">#region</span> GenrateXLSFile<br /> GC.Collect();<br /> oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));<br /> <span class="rem">//oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet));</span><br /> oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;<br /> oSheet.Name = <span class="kwrd">this</span>.XLSSheetName;<br /><br /> <span class="rem">/*----------------Main Coding----------------*/</span><br /> InsertLogoFileToSheet();<br /><br /> SetXLSTitle((<span class="kwrd">int</span>)enumXLSFields.EmployeeName, (<span class="kwrd">int</span>)enumXLSFields.NetSalary, <span class="str">"Employee Report"</span>, 10, <span class="kwrd">true</span>);<br /><br /> DisplayEmployeeInfo();<br /><br /> SheetAutoFit();<br /> <span class="rem">/*-------------------------------------------*/</span><br /><br /> <span class="preproc">#region</span> XLS File Save into Server Path<br /><br /> <span class="preproc">#region</span> Check XLS File <span class="kwrd">if</span> Exists<br /> <span class="kwrd">string</span> sFileName = <span class="kwrd">this</span>.XLSPath + <span class="kwrd">this</span>.XLSFileName + <span class="str">".XLS"</span>;<br /> <span class="kwrd">if</span> (File.Exists(sFileName))<br /> {<br /> File.Delete(sFileName);<br /> }<br /> <span class="preproc">#endregion</span><br /><br /> <span class="rem">//Create BillingXLS Directory</span><br /> <span class="kwrd">if</span> (!System.IO.Directory.Exists(XLSPath))<br /> {<br /> System.IO.Directory.CreateDirectory(XLSPath);<br /> }<br /><br /> <span class="kwrd">string</span> strXLSFile = <span class="kwrd">string</span>.Format(<span class="str">"{0}\\{1}.xls"</span>, <span class="kwrd">this</span>.XLSPath, <span class="kwrd">this</span>.XLSFileName);<br /> <span class="kwrd">if</span> (File.Exists(strXLSFile))<br /> {<br /> File.Delete(strXLSFile);<br /> }<br /> oWB.SaveAs(<span class="kwrd">string</span>.Format(<span class="str">"{0}\\{1}"</span>, <span class="kwrd">this</span>.XLSPath, <span class="kwrd">this</span>.XLSFileName), XlFileFormat.xlWorkbookNormal, <span class="kwrd">null</span>, <span class="kwrd">null</span>, <span class="kwrd">false</span>, <span class="kwrd">false</span>, XlSaveAsAccessMode.xlExclusive, <span class="kwrd">false</span>, <span class="kwrd">false</span>, <span class="kwrd">null</span>, <span class="kwrd">null</span>, <span class="kwrd">null</span>);<br /> oWB.Close(<span class="kwrd">null</span>, <span class="kwrd">null</span>, <span class="kwrd">null</span>);<br /> <span class="preproc">#endregion</span><br /><br /> <span class="preproc">#endregion</span><br /> }<br /> <span class="kwrd">catch</span><br /> {<br /> System.GC.Collect();<br /> }<br /> <span class="kwrd">finally</span><br /> {<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);<br /><br /> oSheet = <span class="kwrd">null</span>;<br /> oWB = <span class="kwrd">null</span>;<br /> }<br /> }<br /><br /> <span class="kwrd">private</span> <span class="kwrd">void</span> DisplayEmployeeInfo()<br /> {<br /> <span class="preproc">#region</span> Header<br /> iExcelRow++;<br /><br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.EmployeeName] = <span class="str">"Employee Name"</span>;<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.Address] = <span class="str">"Address"</span>;<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.AccountNo] = <span class="str">"Account No"</span>;<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.Earnings] = <span class="str">"Earnings"</span>;<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.Deduction] = <span class="str">"Deduction"</span>;<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.NetSalary] = <span class="str">"Net Salary"</span>;<br /><br /> SetRangeBackColor(iExcelRow, iExcelRow, (<span class="kwrd">int</span>)enumXLSFields.EmployeeName, (<span class="kwrd">int</span>)enumXLSFields.NetSalary);<br /> iExcelRow++;<br /> <span class="preproc">#endregion</span><br /><br /> <span class="preproc">#region</span> Detail<br /> iStartRow = iExcelRow;<br /> <span class="kwrd">foreach</span> (EmployeeInfo Employee <span class="kwrd">in</span> EmployeeList)<br /> {<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.EmployeeName] = Employee.EmployeeName;<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.Address] = Employee.Address;<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.AccountNo] = Employee.AccountNo;<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.Earnings] = Employee.Earnings;<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.Deduction] = Employee.Deduction;<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.NetSalary] = Employee.NetSalary;<br /> iExcelRow++;<br /> }<br /> <span class="preproc">#endregion</span><br /><br /> <span class="preproc">#region</span> Total<br /><br /> <span class="preproc">#region</span> Print Total<br /> oSheet.Cells[iExcelRow, iExcelCol + (<span class="kwrd">int</span>)enumXLSFields.EmployeeName] = <span class="str">"Total"</span>;<br /><br /> SetRangeTotal(iExcelRow, (<span class="kwrd">int</span>)enumXLSFields.Earnings, iStartRow, iExcelRow - 1, (<span class="kwrd">int</span>)enumXLSFields.Earnings);<br /> SetRangeTotal(iExcelRow, (<span class="kwrd">int</span>)enumXLSFields.Deduction, iStartRow, iExcelRow - 1, (<span class="kwrd">int</span>)enumXLSFields.Deduction);<br /> SetRangeTotal(iExcelRow, (<span class="kwrd">int</span>)enumXLSFields.NetSalary, iStartRow, iExcelRow - 1, (<span class="kwrd">int</span>)enumXLSFields.NetSalary);<br /> <span class="preproc">#endregion</span><br /><br /> <span class="preproc">#region</span> Range Format<br /> SetRangeNumberFormat(iStartRow, iExcelRow, (<span class="kwrd">int</span>)enumXLSFields.Earnings, (<span class="kwrd">int</span>)enumXLSFields.NetSalary);<br /> SetRangeBackColor(iExcelRow, iExcelRow, (<span class="kwrd">int</span>)enumXLSFields.EmployeeName, (<span class="kwrd">int</span>)enumXLSFields.NetSalary);<br /> SetRangeBorder(iStartRow, iExcelRow, (<span class="kwrd">int</span>)enumXLSFields.EmployeeName, (<span class="kwrd">int</span>)enumXLSFields.NetSalary);<br /> <span class="preproc">#endregion</span><br /><br /> <span class="preproc">#endregion</span><br /> }<br /> <span class="preproc">#endregion</span><br /><br /> <span class="preproc">#region</span> Private Methods<br /> <span class="kwrd">private</span> <span class="kwrd">void</span> InsertLogoFileToSheet()<br /> {<br /> <span class="kwrd">if</span> (File.Exists(<span class="kwrd">this</span>.LogoFile))<br /> {<br /> oSheet.Shapes.AddPicture(<span class="kwrd">this</span>.LogoFile, Microsoft.Office.Core.MsoTriState.msoTrue, Microsoft.Office.Core.MsoTriState.msoTrue, LogoLeft, LogoTop, LogoWidth, LogoHeight);<br /> }<br /> SetXLSTitle((<span class="kwrd">int</span>)enumXLSFields.Address, (<span class="kwrd">int</span>)enumXLSFields.NetSalary, <span class="str">"ABC Inc."</span>, 12, <span class="kwrd">true</span>);<br /><br /> iExcelRow = 6;<br /> }<br /><br /> <span class="kwrd">private</span> <span class="kwrd">void</span> SheetAutoFit()<br /> {<br /> Range oRng = oSheet.get_Range(<span class="str">"A1"</span>, <span class="str">"Z1"</span>);<br /> oRng.EntireColumn.AutoFit();<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);<br /> }<br /><br /> <span class="kwrd">private</span> <span class="kwrd">void</span> SetXLSTitle(<span class="kwrd">int</span> StartColumn, <span class="kwrd">int</span> EndColumn, <span class="kwrd">string</span> ColumnHeading, <span class="kwrd">int</span> FontSize, <span class="kwrd">bool</span> MergeColumn)<br /> {<br /> sFromCellNo = ((<span class="kwrd">char</span>)(iExcelCol + StartColumn + 64)) + iExcelRow.ToString();<br /> sToCellNo = ((<span class="kwrd">char</span>)(iExcelCol + EndColumn + 64)) + iExcelRow.ToString();<br /><br /> oSheet.Cells[iExcelRow, iExcelCol + StartColumn] = ColumnHeading;<br /><br /> Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);<br /> oRng.Font.Name = <span class="str">"Arial"</span>;<br /> oRng.Font.Size = FontSize;<br /> oRng.Font.Bold = <span class="kwrd">true</span>;<br /> <span class="kwrd">if</span> (MergeColumn)<br /> {<br /> oRng.Merge(1);<br /> oRng.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;<br /> oRng.Font.Color = Color.White.ToArgb();<br /> oRng.Interior.Color = Color.FromArgb(128, 128, 128).ToArgb();<br /> oRng.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;<br /> oRng.Borders.Value = 1;<br /> oRng.Borders.Color = Color.Black.ToArgb();<br /> }<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);<br /> }<br /><br /> <span class="kwrd">private</span> <span class="kwrd">void</span> SetRangeTotal(<span class="kwrd">long</span> DisplayRow, <span class="kwrd">long</span> DisplayColumn, <span class="kwrd">long</span> StartRow, <span class="kwrd">long</span> EndRow, <span class="kwrd">long</span> RangeColumn)<br /> {<br /> sFromCellNo = ((<span class="kwrd">char</span>)(iExcelCol + RangeColumn + 64)) + StartRow.ToString();<br /> sToCellNo = ((<span class="kwrd">char</span>)(iExcelCol + RangeColumn + 64)) + EndRow.ToString();<br /><br /> oSheet.Cells[DisplayRow, iExcelCol + DisplayColumn] = <span class="str">"=SUM("</span> + sFromCellNo + <span class="str">":"</span> + sToCellNo + <span class="str">")"</span>;<br /> }<br /><br /> <span class="kwrd">private</span> <span class="kwrd">void</span> SetRangeNumberFormat(<span class="kwrd">long</span> StartRow, <span class="kwrd">long</span> EndRow, <span class="kwrd">long</span> StartColumn, <span class="kwrd">long</span> EndColumn)<br /> {<br /> sFromCellNo = ((<span class="kwrd">char</span>)(iExcelCol + StartColumn + 64)) + StartRow.ToString();<br /> sToCellNo = ((<span class="kwrd">char</span>)(iExcelCol + EndColumn + 64)) + EndRow.ToString();<br /><br /> Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);<br /> oRng.NumberFormat = <span class="str">"###,###,##0.00"</span>;<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);<br /> }<br /><br /> <span class="kwrd">private</span> <span class="kwrd">void</span> SetRangeBorder(<span class="kwrd">long</span> StartRow, <span class="kwrd">long</span> EndRow, <span class="kwrd">long</span> StartColumn, <span class="kwrd">long</span> EndColumn)<br /> {<br /> sFromCellNo = ((<span class="kwrd">char</span>)(iExcelCol + StartColumn + 64)) + StartRow.ToString();<br /> sToCellNo = ((<span class="kwrd">char</span>)(iExcelCol + EndColumn + 64)) + EndRow.ToString();<br /><br /> Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);<br /> oRng.Borders.Value = 1;<br /> oRng.Borders.Color = Color.Black.ToArgb();<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);<br /> }<br /><br /> <span class="kwrd">private</span> <span class="kwrd">void</span> SetRangeBackColor(<span class="kwrd">long</span> StartRow, <span class="kwrd">long</span> EndRow, <span class="kwrd">int</span> StartColumn, <span class="kwrd">int</span> EndColumn)<br /> {<br /> sFromCellNo = ((<span class="kwrd">char</span>)(iExcelCol + StartColumn + 64)) + StartRow.ToString();<br /> sToCellNo = ((<span class="kwrd">char</span>)(iExcelCol + EndColumn + 64)) + EndRow.ToString();<br /><br /> Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);<br /> oRng.Font.Name = <span class="str">"Arial"</span>;<br /> oRng.Font.Bold = <span class="kwrd">true</span>;<br /> oRng.Font.Color = Color.Black.ToArgb();<br /> oRng.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192).ToArgb();<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);<br /> }<br /><br /> <span class="kwrd">private</span> <span class="kwrd">void</span> SetRangeBold(<span class="kwrd">long</span> StartRow, <span class="kwrd">long</span> EndRow, <span class="kwrd">long</span> StartColumn, <span class="kwrd">long</span> EndColumn)<br /> {<br /> sFromCellNo = ((<span class="kwrd">char</span>)(iExcelCol + StartColumn + 64)) + StartRow.ToString();<br /> sToCellNo = ((<span class="kwrd">char</span>)(iExcelCol + EndColumn + 64)) + EndRow.ToString();<br /><br /> Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);<br /> oRng.Font.Name = <span class="str">"Arial"</span>;<br /> oRng.Font.Bold = <span class="kwrd">true</span>;<br /> oRng.Font.Color = Color.Black.ToArgb();<br /> oRng.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192).ToArgb();<br /> System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);<br /> }<br /> <span class="preproc">#endregion</span><br />}<br /><br /><span class="kwrd">public</span> <span class="kwrd">class</span> EmployeeInfo<br />{<br /> <span class="kwrd">public</span> <span class="kwrd">string</span> EmployeeName { get; set; }<br /> <span class="kwrd">public</span> <span class="kwrd">string</span> Address { get; set; }<br /> <span class="kwrd">public</span> <span class="kwrd">string</span> AccountNo { get; set; }<br /> <span class="kwrd">public</span> <span class="kwrd">float</span> Earnings { get; set; }<br /> <span class="kwrd">public</span> <span class="kwrd">float</span> Deduction { get; set; }<br /> <span class="kwrd">public</span> <span class="kwrd">float</span> NetSalary { get; set; }<br />}<br /></pre><br /><span style="font-weight:bold;">ProcessXLS.ASPX :</span><br /><pre class="csharpcode"><br /><span class="kwrd">public</span> <span class="kwrd">partial</span> <span class="kwrd">class</span> ProcessXLS : System.Web.UI.Page<br />{<br /> <span class="kwrd">protected</span> <span class="kwrd">void</span> Page_Load(<span class="kwrd">object</span> sender, EventArgs e)<br /> {<br /> GenrateXLS();<br /> }<br /><br /> <span class="kwrd">private</span> <span class="kwrd">void</span> GenrateXLS()<br /> {<br /> CreateXLS objXLS = <span class="kwrd">new</span> CreateXLS();<br /><br /> objXLS.EmployeeList.Add(<span class="kwrd">new</span> EmployeeInfo() { EmployeeName = <span class="str">"Eugene Malarky"</span>, Address = <span class="str">"Grove Street, Smithtown"</span>, AccountNo = <span class="str">"A001"</span>, Earnings = 1500, Deduction = 450, NetSalary = 1050 });<br /> objXLS.EmployeeList.Add(<span class="kwrd">new</span> EmployeeInfo() { EmployeeName = <span class="str">"Sam Adams"</span>, Address = <span class="str">"Main Street, Suite 1000, Ashville"</span>, AccountNo = <span class="str">"A002"</span>, Earnings = 1250, Deduction = 320, NetSalary = 930 });<br /> objXLS.EmployeeList.Add(<span class="kwrd">new</span> EmployeeInfo() { EmployeeName = <span class="str">"Loren Sandler"</span>, Address = <span class="str">"Main Street, Hometown"</span>, AccountNo = <span class="str">"A003"</span>, Earnings = 1800, Deduction = 550, NetSalary = 1250 });<br /><br /> objXLS.XLSPath = Server.MapPath(<span class="str">"XLSFiles"</span>);<br /> objXLS.XLSFileName = <span class="str">"EmployeeSalary"</span>;<br /> objXLS.XLSSheetName = <span class="str">"EmployeeList"</span>;<br /><br /> objXLS.LogoFile = Server.MapPath(<span class="str">"XLSFiles/JDLogo.jpeg"</span>);<br /><br /> objXLS.GenerateXLSFile();<br /> }<br />}<br /></pre><br /><strong>Sample XLS File</strong><br /><a href="http://1.bp.blogspot.com/_h_Bkpc8049s/TDWnLJvR4sI/AAAAAAAAABk/zsWGEsrsp6Q/s1600/SampleXLS.PNG"><img style="cursor:pointer; cursor:hand;width: 320px; height: 182px;" src="http://1.bp.blogspot.com/_h_Bkpc8049s/TDWnLJvR4sI/AAAAAAAAABk/zsWGEsrsp6Q/s320/SampleXLS.PNG" border="0" alt=""id="BLOGGER_PHOTO_ID_5491479130682811074" /></a>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com1tag:blogger.com,1999:blog-482602810823403830.post-85009978087283381852010-07-07T21:55:00.002-07:002012-03-23T03:44:05.225-07:00MERGE Statement and compatibility levelIn SQL 2008 Merge Statement when execute and give the error like this<br /><br /><span style="color:red">Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.</span><br /><br /><span style="font-weight:bold; color:blue">Solution</span><br /><span style="font-weight:bold;">SQL Command</span><pre class="csharpcode"><span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> <DBName><br /><span class="kwrd">SET</span> COMPATIBILITY_LEVEL = 100 </pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0tag:blogger.com,1999:blog-482602810823403830.post-79370237657252450802010-07-05T02:30:00.000-07:002010-07-08T03:37:28.977-07:00Delegate using in ASPX Page and user control<span style="font-weight: bold;">In ASPX Page :</span><br /><pre class="csharpcode"><br /><span class="kwrd">public</span> <span class="kwrd">partial</span> <span class="kwrd">class</span> Activity_ContactActivity : Page<br />{<br /> <span class="kwrd">protected</span> <span class="kwrd">void</span> Page_Load(<span class="kwrd">object</span> sender, EventArgs e)<br /> {<br /> MonthActivity1.LoadWeekTabInformation += <span class="kwrd">new</span> UserControls_Activity_MonthActivity.LoadTabInformation(MonthActivity1_LoadWeekTabInformation);<br /> }<br /><br /> <span class="kwrd">void</span> MonthActivity1_LoadWeekTabInformation()<br /> {<br /> <span class="kwrd">this</span>.ActivityDate = MonthActivity1.ActivityDate;<br /> <span class="kwrd">this</span>.CurrentActivityTab = enumContactActivityTab.Week;<br /> LoadActivityInformation();<br /> }<br />}</pre><br /><br /><span style="font-weight: bold;">In User Control Page : </span><br /><pre class="csharpcode"><br /><span class="kwrd">public</span> <span class="kwrd">partial</span> <span class="kwrd">class</span> UserControls_Activity_MonthActivity : UserControl<br />{<br /> <span class="kwrd">public</span> <span class="kwrd">delegate</span> <span class="kwrd">void</span> LoadTabInformation();<br /> <span class="kwrd">public</span> <span class="kwrd">event</span> LoadTabInformation LoadWeekTabInformation;<br /><br /> <span class="kwrd">private</span> <span class="kwrd">void</span> DisplayWeekTabInformation()<br /> {<br /> LoadWeekTabInformation();<br /> }<br />}</pre>Jayesh Chauhanhttp://www.blogger.com/profile/07837823503910695576noreply@blogger.com0