(X86,X64)SQLServer 调用Webservice

在SQLServer 中调用Webservice的问题以及解决办法；安装SQLServer，根据操作系统自动选择32位或64位，（如操作系统是32位，SQLServer也默认32位）如下：

1. 在 32位系统SQLServer调用Webservice的方法；网上很多，这里只做个提示；以下方法来自于互联网：

步骤：1.更改全局配置sp_configure 'show advanced options'， 1；GO RECONFIGURE；GO sp_configure 'Ole Automation Procedures'， 1；GO RECONFIGURE；GO

      2.安装SoapToolkit（网上现有的方案大多没有提到这步）

     3.SQL直接调用webservice（略去触发器和存储过程）

     ——创建MSSOAP.SoapClient组件（如果安装的是SoapToolkit30，应该是MSSOAP.SoapClient30，否则是MSSOAP.SoapClient），SQL语句如下：

      DECLARE @scid int declare @rt int declare @Para nvarchar(10) --接口输入参数

     declare @Para1 nvarchar(10) DECLARE @result nvarchar(2000) --接口输出参数

     ECLARE @ret int --接口输出参数

    set @Para = 'XXX' set @Para1 = 'string' set @ret = -2;

    BEGIN TRY 

            EXEC @rt=sp_OACreate 'MSSOAP.SoapClient30',@scid out

            EXEC @rt = sp_OAMethod @scid, 'mssoapinit', null, 'http://tony-hp/CEWebService/Service1.asmx?WSDL','Service1'

            EXEC @rt = sp_OAMethod @scid, 'HelloWorld', @result out --HelloWorld为接口;@result out为返回参数,必须写上,若无为null

   END TRY  

    BEGIN CATCH SELECT 'There was an error! ' + ERROR_MESSAGE()

    return; 

   END CATCH 

    IF @rt <> 0

    BEGIN   

      EXEC sp_OAGetErrorInfo @scid 

    END

    else

    begin

      select @result as result             --结果

    end

      select @ret; 

      EXEC @rt = sp_OADestroy @scid --释放掉@scid

GO

  注：这种方法适用webservice返回类型为字符串且长度不大于4000的类型，有一定的局限性（并且MSSOAP.SoapClient组件不支持64位系统，我测试的结果是这样的，不知是否有高人可以解决；）

2. 64位系统:

     --在Sql Server中执行这段代码可以开启CLR

     exec sp_configure 'show advanced options', '1'; go reconfigure; go exec sp_configure 'clr enabled', '1' go reconfigure; exec sp_configure 'show advanced options', '1'; go

    --目标数据库的可信任属性需要设为false,可以使用以下语句启用:

   alter database [数据库] set trustworthy on --确保部署程序集时所使用的账号是目标数据库的所有者,如不是可以使用以下语句:

   exec sp_changedbowner 'sa'

   方法1：网上有例子，（1）打开vs ，新建数据库项目中Microsoft SQL Server中的SQL CLR项目

            （2)  添加web引用，名称为：Service，以及用户定义函数；

             (3)  在项目属性页面中的数据库中的权限级别：设置成外部；在生产事件中的生产后事件命令行中输入："C:\Program Files\Microsoft Visual Studio 9.0\SDK\v3.5\Bin\sgen.exe" /force  "$(TargetPath)"--Sgen.exe的说明请网上查看；

             (4) 在用户定义函数中加入：（webservice没有参数，直接返回一个字符串，项目名称：Test）

             [Microsoft.SqlServer.Server.SqlFunction]    

           public static string WebFunction()     {

           Service.Service1 myService = new Service.Service1();//实例化一个webservice的

           string result = myService.HelloWorld();//调用webservice方法        

           return result;  }

          (5) 点击项目部署，部署成功了，会向数据库插入一个名称为WebFunction的标量函数，以及一个类库Test.dll;

             在项目中会出现2个dll：Test.dll,Test.XmlSerializers.dll;把Test.XmlSerializers复制到服务器上，并且在当前数据库添加程序集中添加Test.XmlSerializers.dll;

         (6) SQL调用webservice 例子：select dbo.WebFunction()  ; 结果为：HelloWorld；

          注：这种方法的不好之处，就是不能动态去修改Webservice地址以及参数；如果有多个webservice，尽量实例化多个，这样可以用多个不同的标量函数来调用；

               （如webservice地址以及参数变化，必须重新部署）

       方法2：1. 与方法1一样创建项目；只有第一步一样（项目名称为Test1）；

                2.SQLServer 中注册dll

              Exec sp_configure 'show advanced options','1'; go reconfigure; go exec sp_configure 'clr enabled','1' go

               alter database [数据库] set trustworthy on EXEC   sp_changedbowner 'sa'  --确保部署程序集时所使用的账号是目标数据库的所有者

               go

              create assembly  -[System.Web] from --'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Net.dll'---64位系统               'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'--32位系统

               with permission_set = UNSAFE go

               3. 在用户定义函数中加入以下代码：在项目中可以添加在SQL注册的dll

[Microsoft.SqlServer.Server.SqlFunction]    

public static string Test1(string WSURL, string WSName, string Z_DocName, string Z_TYPE, string Z_DATA)    

 {        

 try     

    {            

 Hashtable pars = new Hashtable();        

     pars["Z_DocName"] = Z_DocName;        

     pars["Z_TYPE"] = Z_TYPE;          

   pars["Z_DATA"] = Z_DATA;         

    XmlDocument doc = QuerySoapWebService(WSURL, WSName, pars);       

      return doc.InnerText.ToString();     

    }      

   catch (Exception e)   

      {             return "F:" + e.ToString();         }     }

    //<webServices>    

   //  <protocols>     //    <add name="HttpGet"/>    

   //    <add name="HttpPost"/>    

   //  </protocols>     //</webServices>

    private static Hashtable _xmlNamespaces = new Hashtable();//缓存xmlNamespace，避免重复调用GetNamespace

    /**/     /// <summary>     /// 需要WebService支持Post调用     /// </summary>    

 public static XmlDocument QueryPostWebService(String URL, String MethodName, Hashtable Pars)   

  {        

HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(URL + "/" + MethodName);      

   request.Method = "POST";        

request.ContentType = "application/x-www-form-urlencoded";      

   SetWebRequest(request);        

byte[] data = EncodePars(Pars);     

    WriteRequestData(request, data);

        return ReadXmlResponse(request.GetResponse());  

   }   

  /**/     /// <summary>    

 /// 需要WebService支持Get调用     /// </summary>   

  public static XmlDocument QueryGetWebService(String URL, String MethodName, Hashtable Pars)    

 {        

 HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(URL + "/" + MethodName + "?" + ParsToString(Pars));      

   request.Method = "GET";        

request.ContentType = "application/x-www-form-urlencoded";        

 SetWebRequest(request);        

 return ReadXmlResponse(request.GetResponse());  

   }

    /**/     /// <summary>     /// 通用WebService调用(Soap),参数Pars为String类型的参数名、参数值     /// </summary>    

 public static XmlDocument QuerySoapWebService(String URL, String MethodName, Hashtable Pars)    

{        

 if (_xmlNamespaces.ContainsKey(URL))      

   {            

 return QuerySoapWebService(URL, MethodName, Pars, _xmlNamespaces[URL].ToString());   

      }        

else        

{            

 return QuerySoapWebService(URL, MethodName, Pars, GetNamespace(URL));  

       }    

}

    private static XmlDocument QuerySoapWebService(String URL, String MethodName, Hashtable Pars, string XmlNs)     {    

       _xmlNamespaces[URL] = XmlNs;//加入缓存，提高效率       

  HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(URL);      

   request.Method = "POST";      

   request.ContentType = "text/xml; charset=utf-8";     

    request.Headers.Add("SOAPAction", "\"" + XmlNs + (XmlNs.EndsWith("/") ? "" : "/") + MethodName + "\"");   

      SetWebRequest(request);       

  byte[] data = EncodeParsToSoap(Pars, XmlNs, MethodName);   

      WriteRequestData(request, data);       

  XmlDocument doc = new XmlDocument(), doc2 = new XmlDocument();     

    doc = ReadXmlResponse(request.GetResponse());

        XmlNamespaceManager mgr = new XmlNamespaceManager(doc.NameTable);    

     mgr.AddNamespace("soap", "http://schemas.xmlsoap.org/soap/envelope/");        

 String RetXml = doc.SelectSingleNode("//soap:Body/*/*", mgr).InnerXml;    

     doc2.LoadXml("<root>" + RetXml + "</root>");      

   AddDelaration(doc2);        

 return doc2;    

 }    

private static string GetNamespace(String URL)   

  {        

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(URL + "?WSDL");    

     SetWebRequest(request);        

 WebResponse response = request.GetResponse();       

  StreamReader sr = new StreamReader(response.GetResponseStream(), Encoding.UTF8);    

     XmlDocument doc = new XmlDocument();      

   doc.LoadXml(sr.ReadToEnd());    

     sr.Close();        

 return doc.SelectSingleNode("//@targetNamespace").Value;   

  }   

  private static byte[] EncodeParsToSoap(Hashtable Pars, String XmlNs, String MethodName)  

   {         XmlDocument doc = new XmlDocument();       

  doc.LoadXml("<soap:Envelope xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" rel="nofollow" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" rel="nofollow" xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\"></soap:Envelope>");       

  AddDelaration(doc);        

 XmlElement soapBody = doc.CreateElement("soap", "Body", "http://schemas.xmlsoap.org/soap/envelope/");       

  XmlElement soapMethod = doc.CreateElement(MethodName);      

   soapMethod.SetAttribute("xmlns", XmlNs);    

     if (Pars != null)         {         

    foreach (string k in Pars.Keys)         

    {                

 XmlElement soapPar = doc.CreateElement(k);      

           soapPar.InnerXml = ObjectToSoapXml(Pars[k]);         

        soapMethod.AppendChild(soapPar);         

    }        

 }        

 soapBody.AppendChild(soapMethod);      

   doc.DocumentElement.AppendChild(soapBody);     

    return Encoding.UTF8.GetBytes(doc.OuterXml);   

}    

private static string ObjectToSoapXml(object o)     {   

      XmlSerializer mySerializer = new XmlSerializer(o.GetType());        

 MemoryStream ms = new MemoryStream();      

   mySerializer.Serialize(ms, o);       

  XmlDocument doc = new XmlDocument();      

     if (doc.DocumentElement != null)     

    {          

   return doc.DocumentElement.InnerXml;      

   }         else       

  {         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 = data.Length;     

    Stream writer = request.GetRequestStream();      

   writer.Write(data, 0, data.Length);        

 writer.Close();     }

    private static byte[] EncodePars(Hashtable Pars)  

   {         return Encoding.UTF8.GetBytes(ParsToString(Pars));     }

    private static String ParsToString(Hashtable Pars)    

 {         StringBuilder sb = new StringBuilder();      

   if (Pars != null)        

 {            

foreach (string k in Pars.Keys)

            {    

             if (sb.Length > 0)     

            {                    

 sb.Append("&");          

       }                

sb.Append(HttpUtility.UrlEncode(k) + "=" + HttpUtility.UrlEncode(Pars[k].ToString()));     

           // sb.Append(k + "=" + Pars[k].ToString());      

       }        

}        

 return sb.ToString();

    }

    private static XmlDocument ReadXmlResponse(WebResponse response)   

  {         StreamReader sr = new StreamReader(response.GetResponseStream(), Encoding.UTF8);      

   String retXml = sr.ReadToEnd();        

 sr.Close();        

XmlDocument doc = new XmlDocument();     

    doc.LoadXml(retXml);       

  return doc;     }

    private static void AddDelaration(XmlDocument doc)   

  {         XmlDeclaration decl = doc.CreateXmlDeclaration("1.0", "utf-8", null);     

    doc.InsertBefore(decl, doc.DocumentElement);     }

  (4) 部署项目，结果与方法1中的第5步一样，SQL 数据库中的会存在Test1标量函数，以及程序集中有Test1.dll

  (5) 调用实例：print dbo.Test1('http://192.168.0.12/Web/Service1.asmx','DoTest','AAA','1','123')

   参数说明：  webservice 地址，方法名，后面3个位输入参数；标量函数不支持output；

  也可以创建存储过程：如下：

 [Microsoft.SqlServer.Server.SqlProcedure]   --部署创建存储过程

 [Microsoft.SqlServer.Server.SqlFunction]   --部署创建标量函数

 

 转载请注明出处；谢谢！

转载于:https://www.cnblogs.com/dong-1984dd/archive/2012/01/17/SQL-Web.html