建立并发布一个Web Service, 关键代码如下
[WebMethod]
public DataSet ExecSql(string strSql,string strConn)
{
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
在Infopath 中引用该 Web Reference, 实现Sql Server的访问,示例为根据用户的number取得用户名.
XPathNavigator xnDoc = this.MainDataSource.CreateNavigator();
XPathNavigator xnParam = xnDoc.SelectSingleNode("/my:myFields/my:param", this.NamespaceManager);
XPathNavigator xnResult = xnDoc.SelectSingleNode("/my:myFields/my:result", this.NamespaceManager);
string connString = "server=myserver;database=TestDB;uid=user;pwd=pass;";
local.Service1 sqlhelper = new local.Service1();
string strSql = "select * from operators where number='" + xnParam.Value + "'";
DataSet dtResult = sqlhelper.ExecSql(strSql, connString);
xnResult.SetValue(dtResult.Tables[0].Rows[0]["First_Name"].ToString());