SQL调用Web Service

最新推荐文章于 2022-05-19 20:33:12 发布
原创最新推荐文章于 2022-05-19 20:33:12 发布 · 476 阅读
1 ·
CC 4.0 BY-SA版权
CLR 专栏收录该内容
9 篇文章
订阅专栏
本文介绍了一个在SQL Server中定义的用户自定义函数，用于通过SOAP协议调用Web服务，展示了如何使用C#进行参数编码、请求构建及响应解析。
using Microsoft.SqlServer.Server;
using System;
using System.Collections;
using System.Data.SqlTypes;
using System.IO;
using System.Net;
using System.Text;
using System.Xml;
using System.Xml.Serialization;

public class UserDefinedFunctionsz
{
    private static readonly Hashtable _xmlNamespaces = new Hashtable();

    [SqlFunction]
    public static string GetDataTable_P(string WSURL, string PName, string Values)
    {
        string result;
        try
        {
            Hashtable hashtable = new Hashtable();
            hashtable["PName"] = PName;
            hashtable["MaterialUse"] = Values;
            XmlDocument xmlDocument = UserDefinedFunctionsz.QuerySoapWebService(WSURL, PName, hashtable);
            result = xmlDocument.InnerText.ToString();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return result;
    }

    [SqlFunction]
    public static string GetDataTable_V(string WSURL, string PName, string Values)
    {
        string result;
        try
        {
            Hashtable hashtable = new Hashtable();
            hashtable["PName"] = PName;
            hashtable["MaterialUse"] = Values;
            XmlDocument xmlDocument = UserDefinedFunctionsz.QuerySoapWebService(WSURL, PName, hashtable);
            result = xmlDocument.InnerText.ToString();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return result;
    }

    public static XmlDocument QueryPostWebService(string URL, string MethodName, Hashtable Pars)
    {
        HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create(URL + "/" + MethodName);
        httpWebRequest.Method = "POST";
        httpWebRequest.ContentType = "application/x-www-form-urlencoded";
        UserDefinedFunctionsz.SetWebRequest(httpWebRequest);
        byte[] data = UserDefinedFunctionsz.EncodePars(Pars);
        UserDefinedFunctionsz.WriteRequestData(httpWebRequest, data);
        return UserDefinedFunctionsz.ReadXmlResponse(httpWebRequest.GetResponse());
    }
    public static XmlDocument QueryGetWebService(string URL, string MethodName, Hashtable Pars)
    {
        HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create(string.Concat(new string[]
        {
            URL,
            "/",
            MethodName,
            "?",
            UserDefinedFunctionsz.ParsToString(Pars)
        }));
        httpWebRequest.Method = "GET";
        httpWebRequest.ContentType = "application/x-www-form-urlencoded";
        UserDefinedFunctionsz.SetWebRequest(httpWebRequest);
        return UserDefinedFunctionsz.ReadXmlResponse(httpWebRequest.GetResponse());
    }
    public static XmlDocument QuerySoapWebService(string URL, string MethodName, Hashtable Pars)
    {
        if (UserDefinedFunctionsz._xmlNamespaces.ContainsKey(URL))
        {
            return UserDefinedFunctionsz.QuerySoapWebService(URL, MethodName, Pars, UserDefinedFunctionsz._xmlNamespaces[URL].ToString());
        }
        return UserDefinedFunctionsz.QuerySoapWebService(URL, MethodName, Pars, UserDefinedFunctionsz.GetNamespace(URL));
    }
    private static XmlDocument QuerySoapWebService(string URL, string MethodName, Hashtable Pars, string XmlNs)
    {
        UserDefinedFunctionsz._xmlNamespaces[URL] = XmlNs;
        HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create(URL);
        httpWebRequest.Method = "POST";
        httpWebRequest.ContentType = "text/xml; charset=utf-8";
        httpWebRequest.Headers.Add("SOAPAction", string.Concat(new string[]
        {
            "\"",
            XmlNs,
            XmlNs.EndsWith("/") ? "" : "/",
            MethodName,
            "\""
        }));
        UserDefinedFunctionsz.SetWebRequest(httpWebRequest);
        byte[] data = UserDefinedFunctionsz.EncodeParsToSoap(Pars, XmlNs, MethodName);
        UserDefinedFunctionsz.WriteRequestData(httpWebRequest, data);
        XmlDocument xmlDocument = new XmlDocument();
        XmlDocument xmlDocument2 = new XmlDocument();
        xmlDocument = UserDefinedFunctionsz.ReadXmlResponse(httpWebRequest.GetResponse());
        XmlNamespaceManager xmlNamespaceManager = new XmlNamespaceManager(xmlDocument.NameTable);
        xmlNamespaceManager.AddNamespace("soap", "http://schemas.xmlsoap.org/soap/envelope/");
        string innerXml = xmlDocument.SelectSingleNode("//soap:Body/*/*", xmlNamespaceManager).InnerXml;
        xmlDocument2.LoadXml("<root>" + innerXml + "</root>");
        UserDefinedFunctionsz.AddDelaration(xmlDocument2);
        return xmlDocument2;
    }
    private static string GetNamespace(string URL)
    {
        HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create(URL + "?WSDL");
        UserDefinedFunctionsz.SetWebRequest(httpWebRequest);
        WebResponse response = httpWebRequest.GetResponse();
        StreamReader streamReader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
        XmlDocument xmlDocument = new XmlDocument();
        xmlDocument.LoadXml(streamReader.ReadToEnd());
        streamReader.Close();
        return xmlDocument.SelectSingleNode("//@targetNamespace").Value;
    }
    private static byte[] EncodeParsToSoap(Hashtable Pars, string XmlNs, string MethodName)
    {
        XmlDocument xmlDocument = new XmlDocument();
        xmlDocument.LoadXml("<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:Envelope>");
        UserDefinedFunctionsz.AddDelaration(xmlDocument);
        XmlElement xmlElement = xmlDocument.CreateElement("soap", "Body", "http://schemas.xmlsoap.org/soap/envelope/");
        XmlElement xmlElement2 = xmlDocument.CreateElement(MethodName);
        xmlElement2.SetAttribute("xmlns", XmlNs);
        if (Pars != null)
        {
            foreach (string text in Pars.Keys)
            {
                XmlElement xmlElement3 = xmlDocument.CreateElement(text);
                xmlElement3.InnerXml = UserDefinedFunctionsz.ObjectToSoapXml(Pars[text]);
                xmlElement2.AppendChild(xmlElement3);
            }
        }
        xmlElement.AppendChild(xmlElement2);
        xmlDocument.DocumentElement.AppendChild(xmlElement);
        return Encoding.UTF8.GetBytes(xmlDocument.OuterXml);
    }
    private static string ObjectToSoapXml(object o)
    {
        XmlSerializer xmlSerializer = new XmlSerializer(o.GetType());
        MemoryStream memoryStream = new MemoryStream();
        xmlSerializer.Serialize(memoryStream, o);
        XmlDocument xmlDocument = new XmlDocument();
        xmlDocument.LoadXml(Encoding.UTF8.GetString(memoryStream.ToArray()));
        if (xmlDocument.DocumentElement != null)
        {
            return xmlDocument.DocumentElement.InnerXml;
        }
        return o.ToString();
    }
    private static void SetWebRequest(HttpWebRequest request)
    {
        request.Credentials = CredentialCache.DefaultCredentials;
        request.Timeout = 100000;
    }
    private static void WriteRequestData(HttpWebRequest request, byte[] data)
    {
        request.ContentLength = (long)data.Length;
        Stream requestStream = request.GetRequestStream();
        requestStream.Write(data, 0, data.Length);
        requestStream.Close();
    }
    private static byte[] EncodePars(Hashtable Pars)
    {
        return Encoding.UTF8.GetBytes(UserDefinedFunctionsz.ParsToString(Pars));
    }
    private static string ParsToString(Hashtable Pars)
    {
        StringBuilder stringBuilder = new StringBuilder();
        if (Pars != null)
        {
            foreach (string text in Pars.Keys)
            {
                if (stringBuilder.Length > 0)
                {
                    stringBuilder.Append("&");
                }
                stringBuilder.Append(text + "=" + Pars[text].ToString());
            }
        }
        return stringBuilder.ToString();
    }
    private static XmlDocument ReadXmlResponse(WebResponse response)
    {
        StreamReader streamReader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
        string xml = streamReader.ReadToEnd();
        streamReader.Close();
        XmlDocument xmlDocument = new XmlDocument();
        xmlDocument.LoadXml(xml);
        return xmlDocument;
    }
    private static void AddDelaration(XmlDocument doc)
    {
        XmlDeclaration newChild = doc.CreateXmlDeclaration("1.0", "utf-8", null);
        doc.InsertBefore(newChild, doc.DocumentElement);
    }
}