InfoPath 通过Web Service 访问Sql Server

建立并发布一个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());