ucvtfield.cs.svn-base

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

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using DevExpress.XtraEditors.Repository;

namespace dist.hb.EnvQueryStat.Manage
{
  public partial class ucVTField : UserControl
  {
    public ucVTField()
    {
      InitializeComponent();
    }
    System.Data.OleDb.OleDbDataAdapter m_currentDbDataAdapter;
    System.Data.DataSet m_currentDataSet;
    string m_strTbl = "FS_VTFieldDes";
    long  m_nTBid = -1;
    string m_strView = "";
    private OleDbConnection m_OleDbConn;

    public OleDbConnection OleDbConn
    {
      get
      {
        return m_OleDbConn;
      }
      set
      {
        if (value == null) return;
        if (value == m_OleDbConn) return;
        m_OleDbConn = value;
      }
    }

    private void ucVTField_Load(object sender, EventArgs e)
    {
       RepositoryItemLookUpEdit lookUpProperties;
      lookUpProperties = lookUpEdit1.Properties;
      lookUpProperties.ShowLines = true;
      InitVTData();
    }

    //初始化虚表数据
    private void InitVTData()
    {
        DataSet ds = new DataSet();
        System.Data.OleDb.OleDbDataAdapter dbAdapter = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM FS_VTable", m_OleDbConn);
        dbAdapter.Fill(ds, "FS_VTable");
        DataViewManager dvm = new DataViewManager(ds);
        DataView dvMain;

        dvMain = dvm.CreateDataView(ds.Tables["FS_VTable"]);

      // <lookUpEdit1>
      lookUpEdit1.DataBindings.Add("EditValue", dvMain, "VTID");
      lookUpEdit1.Properties.DataSource = dvMain;
      // </lookUpEdit1>

      }

    private void lookUpEdit1_EditValueChanged(object sender, EventArgs e)
    {
      if(lookUpEdit1.GetColumnValue("VName") == null) return;
      if (lookUpEdit1.GetColumnValue("VTID") == null) return;
      m_strView = lookUpEdit1.GetColumnValue("VName").ToString();
      m_nTBid = (int)lookUpEdit1.GetColumnValue("VTID");

      BindField(m_nTBid);
      //MessageBox.Show("测试","消息");
    }

    //将虚表字段绑定到虚表上
    private void BindField(long nVTid)
    {
      DataSet ds = new DataSet();
      string strSQL;
      strSQL = "SELECT VTID,VFieldID,FieldName,FieldAias,FieldType,DICT FROM FS_VTFieldDes WHERE VTID =" + nVTid.ToString();
      m_currentDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, m_OleDbConn);
      string strFillTbl = "FS_VTFieldDes";

      OleDbCommandBuilder custCB = new OleDbCommandBuilder(m_currentDbDataAdapter);//起什么作用？
      OleDbCommand cmd = new OleDbCommand();

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

      m_currentDbDataAdapter.Fill(ds, strFillTbl);
      gridControl1.DataSource = ds.Tables[strFillTbl];
      m_currentDataSet = ds;
    }

    //根据视图提取虚表字段
    private void btnInit_Click(object sender, EventArgs e)
    {
      if (m_nTBid == -1) return;

      //删除汉化的旧的记录
      DeleteOldTblContent(m_nTBid);
      BindField(m_nTBid);//重新绑定
      //重新填充
      DataTable tbl;
      tbl = m_currentDataSet.Tables["FS_VTFieldDes"];
      if (tbl == null) return;

      AutoFillVBField(ref  tbl, m_nTBid, m_strView);
    }

    private void AutoFillVBField(ref DataTable tbl, long nVBid, string strView)
    {
      int iCount;
      iCount = GetMaxValue("FS_VTFieldDes", "VFieldID") + 1;

      DataTable bt = OpenView(strView);
      if (bt == null)
      {
        MessageBox.Show("打开视图失败！", "消息");
        return;
      }


      //VTID,VFieldID,FieldName,FieldAias,FieldType,DICT
      for (int i = 0; i < bt.Columns.Count ; i++)
      {
        //Console.WriteLine(schemaTable.Columns[3].ToString() + " : " +
        string strFieldName = bt.Columns[i].ColumnName;//列名
        //获取列的别名
        string strFieldAias = GetViewAlias(strFieldName);
        if (strFieldAias == "") strFieldAias = strFieldName;

       // Console.WriteLine(bt.Columns[i].DataType.FullName);
        string typeName = GetTypeName(bt.Columns[i].DataType.FullName);//类型
        //if (i == bt.Columns.Count -1)///////////////////////////////////////////////////////////////////????????
        tbl.Rows.Add(new object[] { nVBid, iCount + i, strFieldName, strFieldAias, typeName, null });
      }

    }
    //大致匹配字段别名
    private string GetViewAlias(string strFieldName)
    {
      string  strReturn = "";
      using (OleDbConnection connection = new OleDbConnection(m_OleDbConn.ConnectionString))
      {
        string strSQL = "SELECT FieldAlias  FROM  FS_FieldChi where FieldName = '" + strFieldName + "'";
        OleDbCommand command = new OleDbCommand(strSQL, connection);
        connection.Open();
        OleDbDataReader reader = command.ExecuteReader();
        if (reader.Read())
        {
          strReturn = reader[0].ToString();
        }
        reader.Close();
      }
      return strReturn;
    }

    private DataTable OpenView(string strView)
    {
      DataSet ds = new DataSet();
      string strSQL;
      strSQL = "SELECT * from " + strView;

      System.Data.OleDb.OleDbDataAdapter dbAdapter;
      dbAdapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, m_OleDbConn);
      try
      {
        dbAdapter.Fill(ds, "view1");
      }
      catch(Exception e)
      {
        MessageBox.Show(e.Message);
        return null;
      }
      return ds.Tables["view1"];
    }

    private int GetMaxValue(string strTbl, string strField)
    {
      int iReturn = 0;
      using (OleDbConnection connection = new OleDbConnection(m_OleDbConn.ConnectionString))
      {
        string strSQL = "SELECT MAX(" + strField + ") AS index1 FROM " + strTbl;
        OleDbCommand command = new OleDbCommand(strSQL, connection);
        connection.Open();
        OleDbDataReader reader = command.ExecuteReader();
        if (reader.Read())
        {
          object obj = reader[0];
          string strValue = obj.ToString();
          if (strValue != "") iReturn = (int)reader[0];
        }
        reader.Close();
      }
      return iReturn;
    }

    private void DeleteOldTblContent(long nVBid)
    {
      string strSQL;
      strSQL = "DELETE  FROM FS_VTFieldDes WHERE VTID =" + nVBid.ToString();
      OleDbCommand command = new OleDbCommand(strSQL, m_OleDbConn);
      command.ExecuteNonQuery();
      command.Dispose();
    }

    private string GetTypeName(int iType)
    {
      string typeName = Enum.GetName(typeof(OleDbType), iType);
      if (typeName.Contains("Char")) return "字符";
      else if (typeName.Contains("Double")) return "浮点";
      else if (typeName.Contains("Integer")) return "整形";
      else if (typeName.Contains("Time")) return "日期";
      else return "字符";
    }
    private string GetTypeName(string strType)
    {
      if (strType.Contains("Char")) return "字符";
      else if (strType.Contains("String")) return "字符";
      else if (strType.Contains("Double")) return "浮点";
      else if (strType.Contains("Int")) return "整形";
      else if (strType.Contains("Date")) return "日期";
      else return "字符";
    }

    private void btnSave_Click(object sender, EventArgs e)
    {
      try
      {
        m_currentDbDataAdapter.Update(m_currentDataSet, m_strTbl);
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message, "提示");
      }
    }

    private void button1_Click(object sender, EventArgs e)
    {
      DataTable tbl;
      tbl = m_currentDataSet.Tables["FS_VTFieldDes"];
      if (tbl == null) return;

      AutoFillVBField(ref  tbl, m_nTBid, m_strView);
    }

  }
}