infopath表单中涉及从数据库中取值的函数实现

最近在开发东莞国税网上办税报表系统，用到Infopath service作为表单服务器，后台数据存储采用Oracle，其中涉及了从数据库中取值写入Infopath 表单的需求，代码如下：

 

/// <summary>
    /// 取本年累计
    /// </summary>
    /// <param name="strTableName">表名</param>
    /// <param name="xpn"></param>
    /// <param name="xnm"></param>
    /// <param name="bizDateBegin">业务期始</param>
    /// <param name="NSRSBH">纳税人号</param>
    /// <returns></returns>
    public static string GetSumValue(XPathNavigator xpn, XmlNamespaceManager xnm, string NSRSBH, int thisYear)
    {
        ILog log = LogManager.GetLogger("InfoPath");

        string SQL;
        //int thisyear = DateTime.Now.Year;
        SQL = @"select ";
        XPathNavigator Xpn = null;
        Xpn = xpn.SelectSingleNode("/my:root/my:SumValue1", xnm);
        string tablename = Xpn.GetAttribute(@"TableName", Xpn.NamespaceURI);

        List<string> Columns = new List<string>();
        if (xpn != null)
        {
            XPathNodeIterator itor = Xpn.SelectChildren(XPathNodeType.Element);
            int j = 0;

            while (itor.MoveNext())
            {

                SQL += " sum(" + itor.Current.Name.Substring(0, itor.Current.Name.Length - 2).Replace("my:", "") + ") " + itor.Current.Name.Substring(0, itor.Current.Name.Length - 2).Replace("my:", "") + ",";

                Columns.Add(itor.Current.Name.Replace("my:", ""));
                j++;

            }

            SQL = SQL.Substring(0, SQL.Length - 1);
            SQL += " from " + tablename + " where  SSSQ_Q between to_date('" + thisYear.ToString() + "-1-1','YYYY-MM-DD') and to_date('" + thisYear.ToString() + "-12-31','YYYY-MM-DD') and NSRSBH='" + NSRSBH + "'";

            log.Info("[SQL]:" + SQL);

            OracleConnection conn = new OracleConnection(ConfigurationManager.AppSettings["FormDate_ConnectionString"]);
            try
            {
                conn.Open();
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandText = SQL;
                OracleDataReader odr = cmd.ExecuteReader();
                if (odr.Read())
                {
                    for (int i = 0; i < odr.FieldCount; i++)
                    {
                        NavSetValue(xpn, "/my:root/my:SumValue1/my:" + Columns[i], xnm, ((String.IsNullOrEmpty(odr[i].ToString())) ? "0" : odr[i].ToString()));
                    }

                }
                odr.Close();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

        }

        return SQL;

    }

 

 

前台调用方式：

            int thisyear = Convert.ToInt32(Request.QueryString["bizDateBegin"].Substring(0, 4));
            Xpn = XmlFormView.XmlForm.MainDataSource.CreateNavigator().SelectSingleNode(Constants.DS_SUMVALUE, XmlFormView.XmlForm.NamespaceManager);
                //是否有本年累计
                if (Xpn != null)
                {
                    InfopathHelper.GetSumValue(XmlFormView.XmlForm.MainDataSource.CreateNavigator(), XmlFormView.XmlForm.NamespaceManager, Request.QueryString["pid"], thisyear);
                }