C# QueryDataRes.getTable方法代码示例

 /// <summary>
 /// ����ͣ������Ʊ��
 /// </summary>
 private void searchPark()
 {
     QueryDataRes query = new QueryDataRes("cardone");
     NameObjectList ps = new NameObjectList();
     DataTable tab = query.getTable("devlist", ps);
     if (null == tab || tab.Rows.Count < 1)
         return;
     for (int i = 0; i < tab.Rows.Count; i++)
     {
         DataRow dr = tab.Rows[i];
         string commitype = Convert.ToString(dr["���ʷ�ʽ"]);
         if ("1" == commitype) commitype = "Ser";
         if ("2" == commitype) commitype = "TCP";
         if ("Ser" != commitype && "TCP" != commitype)
             commitype = "UDP";
         string port = Convert.ToString(dr["�˿�"]);
         if ("Ser" == commitype)
             port = "COM" + port;
         //����ֶ�˳�����, �豸����, �豸����, IP��ַ, �˿�, ͨѶ���, ͨѶվַ, ��������, Ĭ������, �����ַ
         string[] ipAddrs = new string[10];
         ipAddrs[0] = i.ToString();
         ipAddrs[1] = "ͣ������Ʊ��";
         ipAddrs[2] = Convert.ToString(dr["����"]);
         ipAddrs[3] = Convert.ToString(dr["��ַ"]);
         ipAddrs[4] = port;
         ipAddrs[5] = commitype;
         ipAddrs[6] = Convert.ToString(dr["�豸��ַ"]);
         ipAddrs[7] = "";
         ipAddrs[8] = "";
         ipAddrs[9] = "";
         DataGridViewRow drPark = this.getRow(ipAddrs[3], port, ipAddrs[6]);
         if (null != drPark)
             drPark.SetValues(ipAddrs);
         else
             this.gdGrid.Rows.Add(ipAddrs);
     }
 }

        private static NameObjectList SetMasterData(UnitItem unititem,XmlDocument xmlsys,QueryDataRes query,XmlNodeList xmlNodes, string userid, NameObjectList paramlist)
        {
            for (int m = 0; m < unititem.WorkItemList.Length; m++)
            {
                if (unititem.WorkItemList[m].ItemType != WorkItemType.MasterData) continue;
                string dataname = unititem.WorkItemList[m].DataSrc;
                DataTable MData = query.getTable(dataname, paramlist, getStrParams(xmlsys, unititem.WorkItemList[m]));

                for (int n = 0; n < MData.Columns.Count; n++)
                {
                    string strM = MData.Columns[n].ColumnName;
                    paramlist.Add(strM, MData.Rows[0][strM].ToString());
                }

                for (int i = 0; i < xmlNodes.Count; i++)
                {
                    for (int j = 0; j < MData.Columns.Count; j++)
                    {
                        string strM = MData.Columns[j].ColumnName;
                        if (xmlNodes[i].InnerText==dataname + "." + strM)
                        {
                            if (MData.Rows[0][strM] == null) continue;
                            xmlNodes[i].ChildNodes[0].InnerText = MData.Rows[0][strM].ToString(); ;
                        }
                    }
                }

            }

            return paramlist;

        }

 public static DataSet PrnDataSet(UnitItem unititem, Hashtable itemhash, XmlDocument xmlsys)
 {
     DataSet dataset = new DataSet();
     QueryDataRes query = new QueryDataRes(unititem.DataSrcFile);
     NameObjectList paramlist = BuildParamList.BuildParams(xmlsys);
     DataTable tab = new DataTable();
     for (int i = 0; i < unititem.WorkItemList.Length; i++)
     {
         string dataname = unititem.WorkItemList[i].DataSrc;
         if (itemhash["[table]" + dataname] != null)
         {
             tab = query.getTable(dataname, paramlist, getStrParams(xmlsys, unititem.WorkItemList[i]));
             dataset.Tables.Add(tab);
         }
         if (itemhash["[dtable]" + dataname] != null)
         {
             tab = query.getTable(dataname, paramlist, getStrParams(xmlsys, unititem.WorkItemList[i]));
             dataset.Tables.Add(tab);
         }
         if (itemhash["[mtable]" + dataname] != null)
         {
             tab = query.getTable(dataname, paramlist, getStrParams(xmlsys, unititem.WorkItemList[i]));
             dataset.Tables.Add(tab);
         }
         
     }
     return dataset;
 }

 /// <summary>
 /// 定时执行业务
 /// </summary>
 /// <param name="obj"></param>
 void executeTimer(object obj)
 {
     DateTime dt = DateTime.Today.AddDays(-1);
     NameObjectList ps = new NameObjectList();
     ps.Add("开始日期", dt);
     ps.Add("结束日期", dt);
     QueryDataRes query = new QueryDataRes("基础类");
     DataTable tab = query.getTable("考勤人员列表", null);
     if (null == tab || tab.Rows.Count < 1)
     {
         isTmRunning = false;
         return;
     }
     NameValueCollection data = new NameValueCollection();
     data["服务"] = "考勤作业";
     DataColumnCollection cols = tab.Columns;
     foreach (DataRow dr in tab.Rows)
     {
         foreach (DataColumn c in cols)
             ps[c.ColumnName] = Convert.ToString(dr[c]);
         try
         {
             bool b = query.ExecuteNonQuery("考勤人员列表", ps, ps, ps);
             if (b) continue;
         }
         catch (Exception ex)
         {
             foreach (string key in ps.AllKeys)
                 data.Add(key, Convert.ToString(ps[key]));
             LogMessage(ex, data, EventLogEntryType.Error);
             continue;
         }
         foreach (string key in ps.AllKeys)
             data.Add(key, Convert.ToString(ps[key]));
         LogMessage("考勤作业失败！", data, EventLogEntryType.Warning);
     }
     isTmRunning = false;
 }

        public static XmlDocument makeprint(UnitItem UnitItem,XmlDocument xmlsys,string UserId)
        {
            //写入Excel的方法：
            //定义需要参数。<明细>5,2,10,15;
            string TemplatePath = HttpContext.Current.Server.MapPath(DataAccRes.AppSettings("TpFilePath"));    //存放源文件的文件夹路径。
            //初始化XML命名空间
            string nsp = "urn:schemas-microsoft-com:office:spreadsheet";
            XmlDocument xmldoc = new XmlDocument();
            try
            {
                xmldoc.Load(TemplatePath + "\\" + UnitItem.FilePrnTp);
            }
            catch
            {
                string strMSG = "请关闭正在打开的模板文件" + UnitItem.FilePrnTp + "!";
                System.Web.HttpContext.Current.Response.Write("	<script language=\"javascript\">	");
                System.Web.HttpContext.Current.Response.Write("alert('" + strMSG + "')");
                System.Web.HttpContext.Current.Response.Write("	</script>							");
                return null;
            }
            XmlNamespaceManager xmlNsmgl = new XmlNamespaceManager(xmldoc.NameTable);
            xmlNsmgl.AddNamespace("docpro", nsp);

            //读出"Cell"节点中的定义值，得到循环数据区位置值
            XmlNodeList xmlNodes = xmldoc.DocumentElement.SelectNodes("//docpro:Cell", xmlNsmgl);

            //修改：<明细>改为#dataitem name,ItemHash存贮dataitem name, 起始(行，列),列数,每页行数
            Hashtable ItemHash = PrnDataItems(xmlNodes);
            //得到模板用到的所有参数
            
            QueryDataRes query = new QueryDataRes(UnitItem.DataSrcFile);
            NameObjectList paramlist = BuildParamList.BuildParams(xmlsys);
            XmlNodeList xmlNodesRow = xmldoc.DocumentElement.SelectNodes("//docpro:Row", xmlNsmgl);
            XmlNodeList xnode = null;
            User userRight = new User(UserId);
            SetParamData(xmlNodes,userRight,paramlist);
            //先处理主表，并将主表记录做为参数记录在paramlist
            paramlist = SetMasterData(UnitItem,xmlsys,query,xmlNodes, UserId, paramlist);
            setpageSetup(xmldoc, userRight, paramlist);
            for (int i = 0; i < UnitItem.WorkItemList.Length; i++)
            {
                if (UnitItem.WorkItemList[i].ItemType == WorkItemType.MasterData) continue;
                string dataname = UnitItem.WorkItemList[i].DataSrc;
                if (ItemHash["[table]" + dataname] != null || ItemHash["[dtable]" + dataname] != null || ItemHash["[mtable]" + dataname] != null)
                {
                    DataTable oData = query.getTable(dataname, paramlist, getStrParams(xmlsys, UnitItem.WorkItemList[i]));
                    string[] arrDetail = null;
                    if(ItemHash["[table]" + dataname] != null)
                        arrDetail = leofun.getArrayFromString(ItemHash["[table]" + dataname].ToString(), ",");
                    if (ItemHash["[dtable]" + dataname] != null)
                        arrDetail = leofun.getArrayFromString(ItemHash["[dtable]" + dataname].ToString(), ",");
                    if (ItemHash["[mtable]" + dataname] != null)
                        arrDetail = leofun.getArrayFromString(ItemHash["[mtable]" + dataname].ToString(), ",");

                    int startrow = toInt(arrDetail[0]) - 1;
                    int startcol = toInt(arrDetail[1]);
                    int fldcount = toInt(arrDetail[2]);
                    int prnPageSize = toInt(arrDetail[3]);
                    
                    XmlNode rowboot = xmlNodesRow[startrow].ParentNode;

                    string[] arrfld = null;
                    arrfld = getExcelFields(xmlNodesRow[startrow]); //当为散步分布时，不能取出字段名
                    for (int m = 0; m < oData.Rows.Count; m++)
                    {if (prnPageSize == 1) continue;
                        XmlNode baknode = xmlNodesRow[startrow].CloneNode(true);
                        xnode = xmlNodesRow[startrow + m].ChildNodes;
                        for (int n = 0; n < fldcount; n++)
                            if (xnode[n].ChildNodes[0] == null) xnode[n].AppendChild(baknode.ChildNodes[n].ChildNodes[0]);
                    }
                    if ((ItemHash["[dtable]" + dataname] != null || ItemHash["[mtable]" + dataname] != null) && oData.Rows.Count>0)
                        SetMTableData(dataname, oData, xmlNodes);
                    else
                    {
                        for (int m = 0; m < oData.Rows.Count; m++)
                        {
                            xnode = xmlNodesRow[startrow + m].ChildNodes;
                            for (int n = 0; n < fldcount; n++)
                            {
                                string fldname = arrfld[n].Replace(dataname+".","");
                                //处理单表格数据
                                string valfld="";
                                if (m>oData.Rows.Count)
                                    valfld = (null == oData.Rows[m][fldname]) ? "" : oData.Rows[m][fldname].ToString();
                                switch (oData.Columns[fldname].DataType.Name)
                                {
                                    case "Double":
                                        if (valfld != "")
                                            valfld = Convert.ToDouble(valfld).ToString("#0.00");
                                        break;
                                    case "DateTime":
                                        if (valfld == "") break;
                                        valfld = Convert.ToDateTime(valfld).ToString("hh:mm");
                                        break;
                                }
                                if (xnode[n] != null && xnode[n].ChildNodes[0] != null)
                                    xnode[n].ChildNodes[0].InnerText = valfld;
                            }
                        }

//.........这里部分代码省略.........

		protected void Page_Load(object sender, System.EventArgs e)
		{
			// 在此处放置用户代码以初始化页面
			XmlDocument		xmldoc=new XmlDocument();
			//读取用户参数
			StreamReader streamreader=new StreamReader(this.Request.InputStream,Encoding.UTF8);
			xmldoc.LoadXml(streamreader.ReadToEnd());
			NameObjectList[]	paramlist=BuildParamList.BuildParamsList(xmldoc);
			try
			{
				string	unitName=paramlist[0]["UnitName"].ToString();
                unitItem = new UnitItem(this.MapPath(DataAccRes.AppSettings("WorkConfig")), unitName);
			}
			catch ( Exception ex )
			{
				ExceptionManager.Publish( ex );
				return;
			}

			QueryDataRes	query=new QueryDataRes(unitItem.DataSrcFile);
			DataSet			ds=new DataSet(unitItem.UnitName);
			ds.EnforceConstraints=false;
			string			itemdata=paramlist[0]["DataItem"].ToString();
			string[]		dataItemList=itemdata.Split(",".ToCharArray());

			for(int i=0;i<dataItemList.Length;i++)
			{
				if(""==dataItemList[i])		continue;
                for(int j=0;j<paramlist.Length;j++)
                {
                    DataTable tab = query.getTable(dataItemList[i], paramlist[j]);
                    ds.Merge(tab);
                }
			}
            //增加行光标列
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                DataTable tab = ds.Tables[i];
                if (null != tab && !tab.Columns.Contains("RowNum"))
                    tab.Columns.Add("RowNum", Type.GetType("System.Int32"));
                for (int j = 0; null != tab && j < tab.Rows.Count; j++)
                    tab.Rows[j]["RowNum"] = j + 1;
            }
            //数字是0的,改为空值显示
            for (int s = 0; s < ds.Tables.Count; s++)
            {
                DataTable tab = ds.Tables[s];
                if (null == tab) continue;
                for (int i = 0; i < tab.Columns.Count; i++)
                {
                    DataColumn col = tab.Columns[i];
                    if ("RowNum" == col.ColumnName) continue;
                    if ("Decimal" != col.DataType.Name && "Double" != col.DataType.Name && "Int16" != col.DataType.Name
                            && "Int32" != col.DataType.Name && "Int64" != col.DataType.Name && "Single" != col.DataType.Name
                             && "UInt16" != col.DataType.Name && "UInt32" != col.DataType.Name && "UInt64" != col.DataType.Name)
                        continue;
                    for (int j = 0; j < tab.Rows.Count; j++)
                    {
                        if (null == tab.Rows[j][i] || DBNull.Value == tab.Rows[j][i])
                            continue;
                        decimal num = Convert.ToDecimal(tab.Rows[j][i]);
                        if (0 == num)
                            tab.Rows[j][i] = DBNull.Value;
                    }
                }
            }

            this.xmldocSchema.LoadXml(ds.GetXmlSchema());
			XmlNamespaceManager		xmlNsMgl=new XmlNamespaceManager(this.xmldocSchema.NameTable);
			XmlNode	xmlRootEle=this.xmldocSchema.DocumentElement;
			for(int i=0;i<xmlRootEle.Attributes.Count;i++)
			{
				string	strPrefix=xmlRootEle.Attributes[i].Prefix;
				string	strLocalName=xmlRootEle.Attributes[i].LocalName;
				string	strURI=xmlRootEle.Attributes[i].Value;
				if("xmlns"==strLocalName)	
					xmlNsMgl.AddNamespace(string.Empty,strURI);
				if("xmlns"!=strPrefix)	continue;
				xmlNsMgl.AddNamespace(strLocalName,strURI);
			}
			this._xmlNsMglSchema=xmlNsMgl;
			this.setSchema(ds);

			QueryDataRes	dictQuery=new QueryDataRes(unitItem.DictColSrcFile);
			WorkItem	workitem=null;
			for(int i=0;i<unitItem.WorkItemList.Length;i++)
				if(unitItem.WorkItemList[i].ItemName==paramlist[0]["WorkItem"].ToString())
				{
					workitem=unitItem.WorkItemList[i];
					break;
				}
			for(int i=0;i<workitem.DictCol.Length;i++)
			{
				DictColumn	dictcol=workitem.DictCol[i];
                if (dictcol.DataSrc.Trim().Length > 0)
                {
                    bool isContinue = false;
                    for (int k = 0; k < i; k++)
                        if (dictcol.DataSrc == workitem.DictCol[k].DataSrc)
                        {
//.........这里部分代码省略.........

 /// <summary>
 /// 初始化创建单元数据集
 /// </summary>
 /// <param name="unitItem">单元实例</param>
 /// <param name="ps">传递的环境参数</param>
 /// <returns>单元的数据集</returns>
 public DataSet BuildDataset(UnitItem unitItem, NameObjectList ps)
 {
     DataSet ds = new DataSet(unitItem.UnitName);
     QueryDataRes query = new QueryDataRes(unitItem.DataSrcFile);
     QueryDataRes queryD = query;
     if (!string.IsNullOrEmpty(unitItem.DictColSrcFile) && unitItem.DictColSrcFile != unitItem.DataSrcFile)
         queryD = new QueryDataRes(unitItem.DictColSrcFile);
     foreach (WorkItem item in unitItem.WorkItemList)
     {
         if (string.IsNullOrEmpty(item.DataSrc))
             continue;
         //填充数据项
         DataTable tab = null;
         string[] psmacro = ParamManager.setMacroParam(MacroPmType.FW, item.InitFilter);
         if (string.IsNullOrEmpty(item.CountDataSrc))
         {
             if (!BindManager.regexSQL.IsMatch(item.DataSrc))
                 tab = query.getTable(item.DataSrc, ps, psmacro);
             else
                 tab = query.GetDataTableBySql(item.DataSrc);
         }
         else
         {
             string[] psString = ParamManager.setMacroParam(MacroPmType.FW, item.InitFilter);
             tab = query.getTable(item.CountDataSrc, ps, psmacro);
             if (null != tab && tab.Rows.Count > 0 && tab.Columns.Contains("记录数量"))
                 psString = ParamManager.setMacroParam(psString, MacroPmType.topnum, Convert.ToString(tab.Rows[0]["记录数量"]));
             tab = query.getTable(item.DataSrc, ps, psmacro);
         }
         if (null == tab) continue;
         //填充字典数据集
         foreach (DictColumn c in item.DictCol)
         {
             if (string.IsNullOrEmpty(c.DataSrc) || string.IsNullOrEmpty(c.ValueCol))
                 continue;
             if (!tab.Columns.Contains(c.ColumnName) || ds.Tables.Contains(c.DataSrc))
                 continue;
             if (!BindManager.regexSQL.IsMatch(c.DataSrc))
                 queryD.FillDataSet(c.DataSrc, ps, ds);
             else
             {
                 DataTable tabtemp = queryD.GetDataTableBySql(c.DataSrc);
                 tabtemp.TableName = c.ColumnName;
                 ds.Tables.Add(tabtemp);
             }
         }
         ds.Tables.Add(tab);
         BindManager.setTablepro(tab, item);
     }
     return ds;
 }

 /// <summary>
 /// 定时执行考勤机业务
 /// </summary>
 /// <param name="obj"></param>
 void tmWorkGather(object obj)
 {
     QueryDataRes query = new QueryDataRes("门禁管理");
     NameObjectList ps = new NameObjectList();
     DataTable tab = query.getTable("门禁管理", ps);
     if (null == tab || tab.Rows.Count < 1)
         return;
     myLog.WriteLine(DateTime.Now.ToString("HH:mm:ss.fff") + " 启动定时执行");
     CommiManager commimgr = CommiManager.GlobalManager;
     for (int i = 0; i < tab.Rows.Count; i++)
     {
         DataRow dr = tab.Rows[i];
         if (null == dr || DBNull.Value == dr["ID"] || DBNull.Value == dr["站址"])
             continue;
         if ("考勤机" != Convert.ToString(dr["控制器类型"]))
             continue;
         string devid = Convert.ToString(dr["ID"]);
         int station = Convert.ToInt32(dr["站址"]);
         string devname = devname = Convert.ToString(dr["名称"]);
         myLog.WriteLine(DateTime.Now.ToString("HH:mm:ss.fff") + " 考勤设备：" + Convert.ToString(dr["站址"]) + " " + devname);
         CommiTarget target = this.getTarget(dr);
         if (null == target) continue;
         DeviceDoor device = new DeviceDoor();
         target.setProtocol(Protocol.PTLDoor);
         device.IsPatrol = false;
         device.dtBeat = DateTime.Now;
         device.SetDevice(commimgr, target, devid, station);
         string msg = device.GatherData(true);
         string cardnum = basefun.valtag(msg, "{卡号}");
         myLog.WriteLine(DateTime.Now.ToString("HH:mm:ss.fff") + " 采集信息   0：" + msg);
         if ("0" == cardnum || "16777215" == cardnum)
         {
             if (device.IsEndReadDevice(-1))
                 continue;
         }
         int index = 1;
         while (!string.IsNullOrEmpty(cardnum))
         {
             msg = device.GatherData(false);
             myLog.WriteLine(DateTime.Now.ToString("HH:mm:ss.fff") + " 采集信息" + Convert.ToString(index++).PadLeft(4, ' ') + "：" + msg);
             cardnum = basefun.valtag(msg, "{卡号}");
             if ("0" == cardnum || "16777215" == cardnum)
             {
                 if (device.IsEndReadDevice(-1))
                     break;
             }
         }
     }
 }

 /// <summary>
 /// 加载数据
 /// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 private void btLoadData_Click(object sender, EventArgs e)
 {
     QueryDataRes query = new QueryDataRes("resources");
     DataTable tab = query.getTable("orggroup", new NameObjectList());
     //字段：ID、PID、名称、代码
 }

 /// <summary>
 /// 搜索停车场验票机
 /// </summary>
 private void searchPark()
 {
     QueryDataRes query = new QueryDataRes("cardone");
     NameObjectList ps = new NameObjectList();
     DataTable tab = query.getTable("devlist", ps);
     if (null == tab || tab.Rows.Count < 1)
         return;
     for (int i = 0; i < tab.Rows.Count; i++)
     {
         DataRow dr = tab.Rows[i];
         string commitype = Convert.ToString(dr["访问方式"]);
         if ("1" == commitype) commitype = "Ser";
         if ("2" == commitype) commitype = "TCP";
         if ("Ser" != commitype && "TCP" != commitype)
             commitype = "UDP";
         string port = Convert.ToString(dr["端口"]);
         if ("Ser" == commitype)
             port = "COM" + port;
         //表格字段顺序：设备类型, 设备名称, IP地址, 端口, 通讯类别, 通讯站址, 物理地址
         string[] ipAddrs = new string[7];
         ipAddrs[0] = "停车场验票机";
         ipAddrs[1] = Convert.ToString(dr["名称"]);
         ipAddrs[2] = Convert.ToString(dr["地址"]);
         ipAddrs[3] = port;
         ipAddrs[4] = commitype;
         ipAddrs[5] = Convert.ToString(dr["设备地址"]);
         ipAddrs[6] = "";
         DataGridViewRow drPark = this.getRow(ipAddrs[2], port, ipAddrs[5]);
         if (null != drPark)
             drPark.SetValues(ipAddrs);
         else
             this.gdGrid.Rows.Add(ipAddrs);
     }
 }