C#数据统计方法

数据统计：

    5月13日，这一天都在郁闷中，罪魁祸首就是“它”和“他”，在数据表格里面，Country这个字段，下面的有“其它国家”这种类型的数据，而我在查询分析器以及VS代码上，查询Country='其它国家'的数据并统计，然而由于没有注意细节问题，把这个条件写成了Country='其他国家'，折腾一整天，不知道哪里错了（查询条件很多，没有注意这个细节的问题），然后怎么查找，都是NULL。最后终于用了关键字Country='%他%'，查出问题之所在，发现我一天的时间就被这个低级的错误给折腾了。
   
下面会写些数据统计的方法：
    
--------------------------ADO.NET方法(一)---------------------------------------------------
public string ddtest(string country2, int type, string guojia)
 {

    SqlConnection sqlConn = new SqlConnection("Data Source=192.168.0.103;Initial Catalog=cnicifTest1;User ID=aspuser;pwd=000000");
    SqlDataAdapter adapter = new SqlDataAdapter("select sum(TradeNumRMB)/10000 from Form_TradeProjectItemInfo_vw where TradeProjectType2TradeType=" + type + "  and Country2='" + country2 + "' and 参展商所在国家 ='" + guojia + "'", sqlConn);
    //用来自动生产更新命令
    SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
    sqlConn.Open();
    DataSet ds = new DataSet();
    adapter.Fill(ds);
    sqlConn.Close()
    return ds.Tables[0].Rows[0][0].ToString();
 }

--------------------------ADO.NET方法(二)---------------------------------------------------
        public static decimal GetInvestFinancProInfoNum(string invest, int m, string location, DateTime DateStart, DateTime DateEnd)
        {
            string sql = "select sum(TradeNumRMB) from Form_TradeProjectItemInfo_vw where 参展商所在国家=@invest and TradeProjectType2TradeType=@m and Country2=@location and TradeDate between @DateStart and @DateEnd";
            QueryCommand cmd = new QueryCommand(sql, "CNICIFProvider");
            cmd.Parameters.Add("@m", m, DbType.String);
            cmd.Parameters.Add("@invest", invest, DbType.String);
            cmd.Parameters.Add("@location", location, DbType.String);
            cmd.Parameters.Add("@DateStart", DateStart, DbType.DateTime);
            cmd.Parameters.Add("@DateEnd", DateEnd, DbType.DateTime);
            DataTable dtb = DataService.GetDataSet(cmd).Tables[0];
            if (dtb.Rows.Count >= 1)
            {
                if (dtb.Rows[0][0].ToString() != "")
                {
                    return decimal.Parse(dtb.Rows[0][0].ToString()) / 100000000;
                }
                else
                {
                    return 0;
                }
            }
            else
            {
                return 0;
            }
        }
--------------------------SubSonic方法-----------------------------------------------
 public partial class InvestFinancProInfo : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.txtStartDate.Attributes.Add("onfocus", "WdatePicker({startDate:'2009-05-15',isShowClear:false,readOnly:true})");
                this.txtEndDate.Attributes.Add("onfocus", "WdatePicker({startDate:'2009-05-19',isShowClear:false,readOnly:true})");
                SetTable();
            }
        }

        protected void ButtonFind_Click(object sender, EventArgs e)
        {
            DateTime startdate = DateTime.Parse(txtStartDate.Text.ToString());
            DateTime enddate = DateTime.Parse(txtEndDate.Text.ToString());
            string yy = txtStartDate.Text.ToString();
            string tt = txtEndDate.Text.ToString();
            if (tt.CompareTo(yy) == 1)
            {
                //InvestMethod(ref startdate, ref enddate);
                SetTable();
            }
            else
            {
                Response.Write("<script>alert('起始时间小于结束时间，请重新选择！');</script>");
            }
        }
        private void SetTable()
        {
            List<SiteInfo> listSiteInfo = SiteInfo.GetListForCurrentYear();//得到今年的会场
            List<int> ids = new List<int>();
            foreach (SiteInfo si in listSiteInfo)
            {
                ids.Add(si.SiteId);
            }

            ZXBTrade zxb = new ZXBTrade(StartDate, EndDate, ids);
            //大陆投资项目
            lblMainlandInvestOver.Text = zxb.MainlandInvestOver.ToString("0.###");
            lblMainlandInvestGAT.Text = zxb.MainlandInvestGAT.ToString("0.###");
            lblMainlandInvestInner.Text = zxb.MainlandInvestInner.ToString("0.###");
            this.lblMainlandInvestTotal.Text = (zxb.MainlandInvestOver + zxb.MainlandInvestGAT + zxb.MainlandInvestInner).ToString("0.###");

            //大陆融资项目
            lblMainlandLoanOver.Text = zxb.MainlandLoanOver.ToString("0.###");
            lblMainlandLoanGAT.Text = zxb.MainlandLoanGAT.ToString("0.###");
            lblMainlandLoanInner.Text = zxb.MainlandLoanInner.ToString("0.###");
            this.lblMainlandLoanTotal.Text = (zxb.MainlandLoanOver + zxb.MainlandLoanOver + zxb.MainlandLoanInner).ToString("0.###");

            //国外及港澳台商投资项目
            lblOverGATInvestOver.Text = zxb.OverGATInvestOver.ToString("0.###");
            lblOverGATInvestGAT.Text = zxb.OverGATInvestGAT.ToString("0.###");
            lblOverGATInvestInner.Text = zxb.OverGATInvestInner.ToString("0.###");
            this.lblOverGATInvestTotal.Text = (zxb.OverGATInvestOver + zxb.OverGATInvestGAT + zxb.OverGATInvestInner).ToString("0.###");

            //国外及港澳台商融资项目
            lblOverGATLoanOver.Text = zxb.OverGATLoanOver.ToString("0.###");
            lblOverGATLoanGAT.Text = zxb.OverGATLoanGAT.ToString("0.###");
            lblOverGATLoanInner.Text = zxb.OverGATLoanInner.ToString("0.###");
            this.lblOverGATLoanTotal.Text = (zxb.OverGATLoanOver + zxb.OverGATLoanGAT + zxb.OverGATLoanInner).ToString("0.###");

            //合计
            decimal OT = 0.0M, GT = 0.0M, IT = 0.0M;
            OT = zxb.MainlandInvestOver + zxb.MainlandLoanOver + zxb.OverGATInvestOver + zxb.OverGATLoanOver;
            GT = zxb.MainlandInvestGAT + zxb.MainlandLoanGAT + zxb.OverGATInvestGAT + zxb.OverGATLoanGAT;
            IT = zxb.MainlandInvestInner + zxb.MainlandLoanInner + zxb.OverGATInvestInner + zxb.OverGATLoanInner;
            this.lblOverTotal.Text = OT.ToString("0.###");
            this.lblGATTotal.Text = GT.ToString("0.###");
            this.lblInnerTotal.Text = IT.ToString("0.###");
            this.lblTotal.Text = (OT + GT + IT).ToString("0.###");
        }

        public DateTime StartDate
        {
            get
            {
                DateTime startDate = new DateTime(2009, 5, 15);
                try
                {
                    startDate = DateTime.Parse(txtStartDate.Text.Trim());
                }
                catch { }

                return startDate;
            }
        }

        public DateTime EndDate
        {
            get
            {
                DateTime endDate = new DateTime(2009, 5, 19);
                try
                {
                    endDate = DateTime.Parse(txtEndDate.Text.Trim());
                }
                catch { }

                return endDate;
            }
        }

        #region 投融资项目统计处理
        class ZXBTrade
        {
            List<TradeProjectInfo> listTradeProject = new List<TradeProjectInfo>();//交易详情列表--要置换得相应的Item列表
            List<SiteInfo> listSiteInfo = new List<SiteInfo>();//会场列表

            //*************************************投融资项目*************************************
            //大陆投资项目
            public decimal MainlandInvestOver;
            public decimal MainlandInvestGAT;
            public decimal MainlandInvestInner;
            //大陆融资项目
            public decimal MainlandLoanOver;
            public decimal MainlandLoanGAT;
            public decimal MainlandLoanInner;
            //国外及港澳台商投资项目
            public decimal OverGATInvestOver;
            public decimal OverGATInvestGAT;
            public decimal OverGATInvestInner;
            //国外及港澳台商融资项目
            public decimal OverGATLoanOver;
            public decimal OverGATLoanGAT;
            public decimal OverGATLoanInner;

            public ZXBTrade(DateTime pStartTime, DateTime pEndTime, List<int> SiteIds)
            {

                listSiteInfo = SiteInfo.GetListForCurrentYear();//得到今年的会场
                listTradeProject = TradeProjectInfo.GetListForSiteList(pStartTime, pEndTime, SiteIds);

                //项目交易
                foreach (TradeProjectInfo tpi in listTradeProject)
                {
                    SiteInfo si = GetSite(tpi.SiteId);

                    List<TradeProjectItemInfo> listTradeProjectItemInfo = tpi.GetItemList();
                    foreach (TradeProjectItemInfo tpii in listTradeProjectItemInfo)
                    {
                        decimal theMoneySum = (tpii.TradeNumRMB ?? 0) / 100000000;//交易转换为亿
                        if (tpii.TradeProjectType2TradeType == 3)//投资
                        {
                            //中国大陆投资
                            if (tpi.Country == "中国大陆" && tpii.Country == "其它国家")
                            {
                                MainlandInvestOver += theMoneySum;
                            }
                            if (tpi.Country == "中国大陆" && (tpii.Country == "中国香港" || tpii.Country == "中国澳门" || tpii.Country == "中国台湾"))
                            {
                                MainlandInvestGAT += theMoneySum;
                            }
                            if (tpi.Country == "中国大陆" && tpii.Country == "中国大陆")
                            {
                                MainlandInvestInner += theMoneySum;
                            }
                            //国外及港澳台投资
                            if ((tpi.Country == "中国香港" || tpi.Country == "中国澳门" || tpi.Country == "中国台湾" || tpi.Country == "其它国家") && (tpii.Country == "中国香港" || tpii.Country == "中国澳门" || tpii.Country == "中国台湾" || tpii.Country == "其它国家"))
                            {
                                OverGATInvestOver += theMoneySum;
                            }
                            if ((tpi.Country == "中国香港" || tpi.Country == "中国澳门" || tpi.Country == "中国台湾" || tpi.Country == "其它国家") && (tpii.Country == "中国香港" || tpii.Country == "中国澳门" || tpii.Country == "中国台湾"))
                            {
                                OverGATInvestGAT += theMoneySum;
                            }
                            if ((tpi.Country == "中国香港" || tpi.Country == "中国澳门" || tpi.Country == "中国台湾" || tpi.Country == "其它国家") && tpii.Country == "中国大陆")
                            {
                                OverGATInvestInner += theMoneySum;
                            }
                        }
                        if (tpii.TradeProjectType2TradeType == 4)//融资
                        {
                            //中国大陆融资
                            if (tpi.Country == "中国大陆" && tpii.Country == "其它国家")
                            {
                                MainlandLoanOver += theMoneySum;
                            }
                            if (tpi.Country == "中国大陆" && (tpii.Country == "中国香港" || tpii.Country == "中国澳门" || tpii.Country == "中国台湾"))
                            {
                                MainlandLoanGAT += theMoneySum;
                            }
                            if (tpi.Country == "中国大陆" && tpii.Country == "中国大陆")
                            {
                                MainlandLoanInner += theMoneySum;
                            }
                            //国外及港澳台融资
                            if ((tpi.Country == "中国香港" || tpi.Country == "中国澳门" || tpi.Country == "中国台湾" || tpi.Country == "其它国家") && (tpii.Country == "中国香港" || tpii.Country == "中国澳门" || tpii.Country == "中国台湾" || tpii.Country == "其它国家"))
                            {
                                OverGATLoanOver += theMoneySum;
                            }
                            if ((tpi.Country == "中国香港" || tpi.Country == "中国澳门" || tpi.Country == "中国台湾" || tpi.Country == "其它国家") && (tpii.Country == "中国香港" || tpii.Country == "中国澳门" || tpii.Country == "中国台湾"))
                            {
                                OverGATLoanGAT += theMoneySum;
                            }
                            if ((tpi.Country == "中国香港" || tpi.Country == "中国澳门" || tpi.Country == "中国台湾" || tpi.Country == "其它国家") && tpii.Country == "中国大陆")
                            {
                                OverGATLoanInner += theMoneySum;
                            }
                        }
                    }
                }
            }

            public SiteInfo GetSite(int pSiteId)
            {
                foreach (SiteInfo siteInfo in this.listSiteInfo)
                {
                    if (siteInfo.SiteId == pSiteId)
                    {
                        return siteInfo;
                    }
                }
                return null;
            }
        }
        #endregion
    } 

转载于:https://www.cnblogs.com/Gemgin/archive/2013/06/13/3136370.html