"CREATE ENDPOINT" Transact-SQL allows us to create Native XML Web services using Transact-SQL. (Refer Books online for details)
The beauty of this example is: neither we create web service project in visual studio, nor we need data access layer to access SQL data. All that we need to do is copy and execute Transact-Sql script on your Sql server which will create desired end point.
Create Native XML Web Service
We will create Native XML Web Service with
1. Virtual directory http://PDesai222/MySqlEndPoint
2. Three web methods
I. Web method 'GetDbInfo' that executes system stored procedure 'sp_helpdb' in master db and lists Sql server databases
II. Web method 'GetSqlLogins' that executes 'xp_loginInfo' system stored proc in master db and lists Sql server login accounts
III. And web method 'GetTableList' that executes 'sp_tables' system tables and returns master db table names
Simplely copy and execute following Sql script on your Sql server which will create desired Native XML Web Service.
Transact-SQL to create My_Sql_EndPoint
/**********************************************************
In this example ‘PDesai222’ is my SQL server name change it
Please change it to your server name before executing script
Also change Grant permission statement to reflect your login
Instead of [MyDomain\MyLogin]
**********************************************************/
DROP ENDPOINT my_sql_endpoint;
GO
CREATE ENDPOINT my_sql_endpoint
STATE = STARTED
AS HTTP(
PATH = '/MySqlEndPoint',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
SITE = 'PDesai222'
)
FOR SOAP (
WEBMETHOD 'GetDbInfo'
(name='master.sys.sp_helpdb',
SCHEMA=STANDARD ),
WEBMETHOD 'GetTableList'
(name='master.sys.sp_Tables',
SCHEMA=STANDARD ),
WEBMETHOD 'GetSqlLogings'
(name='master.sys.xp_logininfo',
SCHEMA=STANDARD ),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'master',
NAMESPACE = 'http://www.GulbargaOnline.com/'
);
GO
GRANT CONNECT ON ENDPOINT::my_sql_endpoint TO [MyDomain\MyLogin]
Verify Native XML Web Service
Open browser (internet explorer) and type Url http://pdesai222/MySqlEndPoint?wsdl
How to retrieve data using XML Native Web Service?
- Create a C# ASP.NET web site project in Visual studio and add web reference to SOAP end point you just created (http://pdesai222/MySqlEndPoint?wsdl) and name it as pdesai222
- Add a GridView control and three Buttons by replacing your Default.Aspx code with following code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table style="width: 853px">
<tr></tr> <tr><td>
<asp:Button ID="GetDbInfo" Text="GetDbInfo" runat="server" CommandName="GetDbInfo" OnClick="Button_Click"/>
</td><td>
<asp:Button ID="GetSqlLogings" Text="GetSqlLogings" runat="server" CommandName="GetSqlLogings" OnClick="Button_Click"/>
</td><td>
<asp:Button ID="GetTableList" Text="GetTableList" runat="server" CommandName="GetTableList" OnClick="Button_Click"/>
</td></tr><tr></tr>
<tr>
<td colspan = "3">
<asp:GridView ID="GridView1" runat="server" Width="100%">
</asp:GridView>
</td> </tr>
</table>
</div>
</form>
</body>
</html>
3. Edit code behind page Default.Aspx.CS Add using to refer your web service
using pdesai222;
- Copy following code to code behind page Default.Aspx.CS
protected void Button_Click(object sender, EventArgs e)
{
pdesai222.my_sql_endpoint proxy = new my_sql_endpoint();
proxy.UseDefaultCredentials = true;
Object[] retvalue = null;
DataSet dataSet = null;
switch ((sender as Button).CommandName)
{
case "GetDbInfo":
retvalue = proxy.GetDbInfo(null);
dataSet = (DataSet)retvalue[0];
break;
case "GetSqlLogings":
System.Data.SqlTypes.SqlString privilege = new System.Data.SqlTypes.SqlString("Not wanted");
retvalue = proxy.GetSqlLogings(null, null, ref privilege);
dataSet = (DataSet)retvalue[0];
break;
case "GetTableList":
retvalue = proxy.GetTableList(null, null, null, null, true);
dataSet = (DataSet) retvalue[4];
break;
}
this.GridView1.DataSource = dataSet;
this.GridView1.DataBind();
}
Build and run your web site project and run it. Click command button to make SOAP request to your Navtive XML web service.
