参考了网上一些文章. 不足之处请多指正.
GO
CREATE TABLE [ dbo ] . [ tb ] (
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ v ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
CREATE PROC [ dbo ] . [ getMyData ]
AS
SELECT ID,V FROM tb
GO
CREATE PROC [ dbo ] . [ getMyDataByID ] ( @id INT )
AS
SELECT ID,V FROM tb WHERE ID = @ID
GO
CREATE ENDPOINT myTestPoint
STATE = STARTED
AS HTTP(
PATH = ' /sql ' ,
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
SITE = ' FCUANDY ' -- 机器名,也可以是ip
)
FOR SOAP (
WEBMETHOD ' GetMyData '
(name = ' test.dbo.getMyData ' ,
SCHEMA = STANDARD ),
WEBMETHOD ' GetMyDataByID '
(name = ' master.dbo.getMyDataByID ' ),
WSDL = DEFAULT ,
SCHEMA = STANDARD,
DATABASE = ' test ' ,
NAMESPACE = ' http://tempUri.org/ '
);
GO
----------------------------------------------------------
打开桌面 > 我的电脑 > 右击 > 管理 > 用户管理 > 增加 一个名为 testPoint 的windows用户,密码为 test123
打开 SQL server Management studio > 安全性 > 登陆名 > 新建
选中windows身份验证 > 搜索 > 定位到 testPoint 这个windows帐户
安全对象选项> 增加> 特定类型的所有对象> 钩选端点> 选中你要操作的 端点 > 钩选下方 connect 权限
当然,这步操作可以使用 sp_addlogin 及 grant connect 语句来完成
--------------------------------------------------------------------
接下来创建client调用.我使用的是c#,创建了一个 控制台程序来测试
增加web引用,然后通过. 将控制台程序发布到局域网其它机器,通过.
program.cs
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
namespace MyEndPointClient
... {
class Program
...{
static void Main(string[] args)
...{
test();
}
private static void test()
...{
testEndPoint.myTestPoint p = new MyEndPointClient.testEndPoint.myTestPoint();
p.UseDefaultCredentials = false;
p.Credentials = new System.Net.NetworkCredential(getUser(keyType.user), getUser(keyType.pass));
DataSet ds=(DataSet)((p.GetMyData())[0]);
StringBuilder sb = new StringBuilder();
int i=0;
foreach (DataRow row in ds.Tables[0].Rows )
...{
i++;
sb.Append("Row " + i.ToString() + " : " + row[1].ToString() + " ");
}
Console.WriteLine(sb.ToString());
Console.ReadLine();
}
private static string getUser(keyType t)
...{
if(t==keyType.user)
return ConfigurationManager.AppSettings["user"];
else
return ConfigurationManager.AppSettings["pass"];
}
private enum keyType
...{
user = 0,
pass = 1
}
}
}
app.config
< configuration >
< configSections >
< sectionGroup name ="applicationSettings" type ="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
< section name ="MyEndPointClient.Properties.Settings" type ="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission ="false" />
</ sectionGroup >
< section name ="microsoft.web.services3" type ="Microsoft.Web.Services3.Configuration.WebServicesConfiguration, Microsoft.Web.Services3, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
</ configSections >
< appSettings >
< add key ="user" value ="testPoint" />
< add key ="pass" value ="test123" />
</ appSettings >
< applicationSettings >
< MyEndPointClient .Properties.Settings >
< setting name ="MyEndPointClient_testEndPoint_myTestPoint" serializeAs ="String" >
< value > http://fcuandy/sql </ value >
</ setting >
</ MyEndPointClient.Properties.Settings >
</ applicationSettings >
</ configuration >
执行结果:
Row 1 : dd
Row 2 : ee
Row 3 : ff