Wednesday 30 March 2016

Grant Create Function or Procedure in SQL DB for a user/role

1) Grant permission for a user/role to create function in DB
 
GRANT CREATE FUNCTION TO [user/role]]
    GO


2) Grant select and DRI(Declarative Referential Integritypermission on a user define function in sql.

GRANT  SELECT, REFERENCES  ON [dbo].[func name]  TO [user/role]
    GO

Note:-DRI permission is required when function written type is data table 

Tuesday 29 March 2016

Date Picker (Calendar) control in ASP.NET using AJAX

How To Use Date Picker (Calendar) Control in ASP.NET Website

Date Picker using AJAX

Step 1) Create New ASP.NET Empty Website and add new web form.
Step 2) Add reference of AjaxControltoolKit in your website as follow :-



Note:- for using AjaxControlToolkit in visual studio please first download it from internet (https://ajaxcontroltoolkit.codeplex.com) and save it to any location (for example download folder of your local drive) then browse it and look in your downloaded location. 
 
Step 3) Register AjaxControltoolkit on design page as follow : -

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1"  %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
    </form>
</body>
</html>
Screen Shot

Step 4) Add ToolKitScriptManager ,  asp textbox ,  asp image button and CalendarExtender as follow : -

<body>
    <form id="form1" runat="server">
    <div>
    
 <cc1:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </cc1:ToolkitScriptManager>
    <asp:TextBox ID="txtDate" runat="server" ReadOnly="true"></asp:TextBox>
    <asp:ImageButton ID="imgPopup"  ImageAlign="Bottom"
        runat="server" />
    <cc1:CalendarExtender ID="Calendar1" PopupButtonID="imgPopup" runat="server" TargetControlID="txtDate"
        Format="dd/MM/yyyy">
    </cc1:CalendarExtender>

    </div>
    </form>
</body>

screen shot
Note:- for using AjaxControlToolkit in visual studio please first downlo

How to create web services in .NET (Beginners) ?

Basics Of ASP.NET Web Services For New Developers

Introduction:
1.   A Web Service is an entity or a piece of code which is hosted on the Web, which serves a specific purpose
2.  A Web Service is a part of a software application which is hosted on the Web and which does a particular function in order to execute the business logic.
3.   A Web Service allows a website to interact with other websites irrespective of the programming language the web site's been written in and also irrespective of the hardware & OS concerns of the machines.
example 
Suppose, you are a developer and you have some 3 different mathematical websites under you, demanded by various clients. A common functionality needs to be implemented in all the 3 websites, lets say displaying a Fibonacci series in a text area.
The question which arises here is, are you going to write separate piece of codes to have the same functionality 3 times. Well it would be a redundant extra effort, if you would try to aim at this approach. Here, the need for a Web Service comes into picture. Code the functionality of displaying a Fibonacci series in a Web Service just once, and call this web service in the 3 different web sites. There you go, you are done. You need not write the same code thrice or else.

Lets start with the creation of the Web Service. (in .NET 3.5)

1) Open Visual Studio 2008 Client. Click on New>Web Site >ASP.NET Web Service. Give the path where you want to place the code. Refer to the below screenshots.


2) Visual Studio will open a default Service.cs file in which you can write your methods. Write the below lines of code. 

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
[WebService(Namespace= "http://tempuri.org/")]
[WebServiceBinding(ConformsTo= WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{
public Service ()
{
 //Uncomment the following line if using designed components 
//InitializeComponent();
}
[WebMethod]
public int Area(int l, int b)
{
  return l * b;
}
[WebMethod]
public int Perimeter(int l, int b)
   {
  return 2 * (l +b);
  }
}

3) Build the solution and view the Service.asmx file in the browser. It will be something like below:



4) The Web Methods created will be evident and will be invoked via HTTP POST method if the links Area and Perimeter are clicked.

So here it is, a very basic Web Service has been coded and created, which will help us to calculate the area and perimeter of a rectangle.
You might want copy the Web Service URL because they are used later in the article. 
Next, we will see how to call and use the Web Service in times of need.
Well, there may be 3 methods by which a Web Service is called, depending upon the scenarios. 
1)       By HTTP POST method.
2)       By creating a Proxy Class 
3)       By using XMLHTTP Request object over SOAP 
Let's discuss each of them in brief:   
---------------------------------------------------------------------------------------------
1) HTTP POST method: This method can be used directly by calling the .asmx file from the client.
The most favorable method to execute an HTTP POST method is to have the codes inside an HTML file. Let’s get started to implement it.

Open your notepad and write the below lines of code in it:


<html>
<head>
<title>Web Service Call using HTTP POST</title>
<body>
<form action="http://localhost:43529/WebService/Service.asmx/Area" method="POST">
  <input name="l">
  <input name="b">
  <input type="submit" value="Click for Area">
</form>
<form action="http://localhost:43529/WebService/Service.asmx/Perimeter" method="POST">
    <input name="l">
  <input name="b">
  <input type="submit" value="Click for Perimeter">
</form>
 </body>
</head>

</html>
Just a friendly reminder, you would want change the Web Service URL in the above code, by your own URL, which you would have copied sometime earlier..  

When the button “Click for Area” or “Click for Perimeter” is clicked, we would see that the specific methods of the Web Service are called and the result is in the form of an XML.

One of the limitations of HTTP POST method can be thought as of: We are unable to build Web Methods which take complex types as parameter or return values.


------------------------------------------------------------------------------------------------------------

2) Creation of a Proxy Class: This method is widely used by developers. This method actually makes use of the full potential of a Web Service.
Let’s implement this and see its wonders.
We need to create one ASP.NET website to see and implement this method.
Open Visual Studio once again and click on New>Website>ASP.NET Website. Give the path where you want to place the code. 

This web site creation will open a Default.aspx page, its aspx.cs page, an App_Code folder and few more stuffs.
Open the Default.aspx page and design it as per below screenshot:
It’s basically 4 ASP label controls, 4 ASP text box controls and 1 ASP button bound under 1 div.

Now the most important aspect of this very method comes into play. Addition of the previously created Web Service.
Ø Right-click the project and click on “Add Web Reference”. The click will open a dialog box similar to the below screenshot.

Now, we need to catch the Web Service here either by adding the URL or by browsing through the various options given. Successful addition of the Web Service will yield “Web services found at this URL”. You would see the methods that you have actually created. You might want to change the name of the web reference from “localhost” to something else to avoid confusion and stuffs. Click on Add Reference and there you go. You have successfully added a Web Reference to your ASP.NET website.

The solution explorer will be something like below:

Now, once the Web Reference is added, let’s write some code in Code-Behind to make use of the web service.
Open Default.aspx.cs file and on Button Click event, add the following code:

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 public partialclass_Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnClick_Click(object sender, EventArgs e)
 {
 if (!string.IsNullOrEmpty(txtLength.Text) && !string.IsNullOrEmpty(txtBreadth.Text))
 {
Maths.Service obj = new Maths.Service();
 int l = Convert.ToInt32(txtLength.Text);
 int b = Convert.ToInt32(txtBreadth.Text);
txtArea.Text = Convert.ToString(obj.Area(l, b));
 txtPerimeter.Text = Convert.ToString(obj.Perimeter(l, b));
}
 }
 }

Now, let me explain the above code further. The bold line of code is what this method is all about, Creation of proxy class.
We are making an object of the Web Service added and then are using that object to call the individual methods of the Web Service. Rest of the code is self-explanatory, which will calculate the area and perimeter of the rectangle.
Build the solution and view the Default.aspx file in the browser.
Enter some values for length and breadth and click on the button.
You will see the area and perimeter displayed in the placed text-boxes.


------------------------------------------------------------------------------------------------------------

3) By using XMLHTTP Request object over SOAP:
First let’s see what SOAP is.
SOAP (Simple Object Access Protocol) is a protocol used for messaging and is completely XML based.
SOAP provides a complete set of rules for messages and also, rules for entities like message encoding, message handling etc. 
Syntax of a SOAP message will be as follows:
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body
</soap:Body>
</soap:Envelope>
A SOAP message is called an Envelope, which further has Header and Body tags.
Header tag is optional whereas Body tag is mandatory to use in order to submit a message.
vNow, let’s see what exactly XMLHttpRequest is.
The XMLHttpRequest specification defines an API that provides scripted client functionality for transferring data between a client and a server.
It is used to send HTTP or HTTPS requests directly to a web server and load the server response data directly back into the script.





SQL Table : Change Row As Column and Group Them... ( Setting column values as column names in the SQL query result )

Setting column values as column names in the SQL query result Problem Statement : id col1 col2 1 Pending 30 1 Resolved 48 ...