clsprocstatitem.cs

以构建的方式来实现对通用的查询统计

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using dist.hb.EnvQueryStat.Pub;

namespace dist.hb.EnvQueryStat.StatSet
{
  class clsProcStatItem
  {
    public clsProcStatItem()
    {
      repositoryItemComboBox_statField = null;
      procAttFilter = null;
      gridView2 = null;
    }

    private OleDbConnection m_OleDbConn;
    public OleDbConnection OleDbConn
    {
      get { return m_OleDbConn; }
      set
      {
        if (value == null) return;
        if (value == m_OleDbConn) return;
        m_OleDbConn = value;
      }
    }
    public DevExpress.XtraEditors.Repository.RepositoryItemComboBox repositoryItemComboBox_statField;
    public DevExpress.XtraGrid.Views.Grid.GridView gridView2;
    public clsProcAttFilter procAttFilter;

    System.Data.OleDb.OleDbDataAdapter m_StatItemDbDataAdapter;
    System.Data.DataSet m_StatItemDataSet;
    public System.Data.DataTable m_StatItemTable;
    string m_strStatItemTbl = "FS_StatItem";

    private void Initialize()
    {
      if (procAttFilter == null || repositoryItemComboBox_statField == null) return;
      DataRow[] DRS = procAttFilter.GetStatField();
      if (DRS == null) return;
      repositoryItemComboBox_statField.Items.Clear();
      foreach (DataRow dr in DRS)
      {
        repositoryItemComboBox_statField.Items.Add(dr["FieldAias"].ToString());
      }

    }

    //处理数据加载
    public void LoadStatItemTable()
    {
      if (procAttFilter == null) return;
      DataSet ds = new DataSet();
      string strSQL;
      strSQL = "SELECT StatItemID,StatInstID,StatField,StatMethod,IsRenField FROM "
                 + m_strStatItemTbl + " where StatInstID = " + procAttFilter.StatInstID.ToString();
      m_StatItemDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, m_OleDbConn);

      OleDbCommandBuilder custCB = new OleDbCommandBuilder(m_StatItemDbDataAdapter);
      OleDbCommand cmd = new OleDbCommand();

      cmd.Connection = m_OleDbConn;
      //cmd.Transaction = m_tran;
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = strSQL;
      m_StatItemDbDataAdapter.SelectCommand = cmd;

      m_StatItemDbDataAdapter.Fill(ds, m_strStatItemTbl);
      m_StatItemDataSet = ds;
      m_StatItemTable = ds.Tables[m_strStatItemTbl];
      gridView2.GridControl.DataSource = m_StatItemTable;
      Initialize();
    }

    public void SaveStatItemTable()
    {
      if (procAttFilter == null) return;
      try
      {
        DeleteStatItemTable();//在执行前先删除。
        int KeyID = clsDbPub.GetMaxValue(m_OleDbConn, m_strStatItemTbl, "StatItemID");

        //要处理主键和外键的编辑
        int i = 0;
        foreach (DataRow row in m_StatItemTable.Rows)
        {
          if (row.RowState == DataRowState.Deleted) continue;

          i = i + 1;
          row["StatInstID"] = procAttFilter.StatInstID;
          row["StatItemID"] = KeyID + i;
        }
        if (m_StatItemTable.Rows.Count <= 0) return;
        SaveStatItemTable(m_StatItemTable.Rows);
        //m_AttFilterDbDataAdapter.Update(m_AttFilterDataSet, m_strAttFilterTbl);
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message, "提示");
      }
    }

    private void SaveStatItemTable(DataRowCollection drc)
    {
      DataSet ds = new DataSet();
      string strSQL;
      strSQL = "SELECT StatItemID,StatInstID,StatField,StatMethod,IsRenField FROM "
                 + m_strStatItemTbl + " where StatInstID = " + procAttFilter.StatInstID.ToString();
      OleDbDataAdapter Adapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, m_OleDbConn);

      OleDbCommandBuilder custCB = new OleDbCommandBuilder(Adapter);
      OleDbCommand cmd = new OleDbCommand();

      cmd.Connection = m_OleDbConn;
      //cmd.Transaction = m_tran;
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = strSQL;
      Adapter.SelectCommand = cmd;

      Adapter.Fill(ds, m_strStatItemTbl);//
      DataTable dt = ds.Tables[m_strStatItemTbl];
      foreach (DataRow row in drc)
      {
        if (row.RowState == DataRowState.Deleted) continue;
        dt.Rows.Add(row.ItemArray);
      }
      Adapter.Update(ds, m_strStatItemTbl);
    }

    public void DeleteStatItemTable()
    {
      if (procAttFilter == null) return;
      string strSQL;
      strSQL = "DELETE  FROM " + m_strStatItemTbl + " WHERE StatInstID =" + procAttFilter.StatInstID.ToString();
      OleDbCommand command = new OleDbCommand(strSQL, m_OleDbConn);
      command.ExecuteNonQuery();
      command.Dispose();
    }


  }
}