Sample code to create Native XML Web Service using SQL 2005 Transact-SQL script

  • Article

"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 https://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 = 'https://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 https://pdesai222/MySqlEndPoint?wsdl

How to retrieve data using XML Native Web Service?

  1.  Create a C# ASP.NET web site project in Visual studio and add web reference to SOAP end point you just created (https://pdesai222/MySqlEndPoint?wsdl) and name it as pdesai222
  2. 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" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 <html xmlns="https://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;

  1. 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.