uctransfield.cs

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

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;

namespace dist.hb.EnvQueryStat.Manage
{
	public partial class ucTransField : UserControl
	{
		System.Data.OleDb.OleDbDataAdapter m_currentDbDataAdapter;
		System.Data.DataSet m_currentDataSet;
		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 ucTransField()
		{
			InitializeComponent();
		}

		//<gridControl1>
		private DataTable data;
		private void InitData()
		{
			data = new DataTable("ColumnsTable");
			data.BeginInit();
			AddColumn(data, "ID", System.Type.GetType("System.Int32"), true);
			AddColumn(data, "First Name", System.Type.GetType("System.String"));
			AddColumn(data, "Last Name", System.Type.GetType("System.String"));
			AddColumn(data, "Payment Type", System.Type.GetType("System.String"));
			AddColumn(data, "Customer", System.Type.GetType("System.Boolean"));
			AddColumn(data, "Payment Amount", System.Type.GetType("System.Single"));
			data.EndInit();
		}
		//</gridControl1>

		private void AddColumn(DataTable data, string name, System.Type type) { AddColumn(data, name, type, false); }
		private void AddColumn(DataTable data, string name, System.Type type, bool ro)
		{
			DataColumn col;
			col = new DataColumn(name, type);
			col.Caption = name;
			col.ReadOnly = ro;
			data.Columns.Add(col);
		}

		private void FillData()
		{
			string[,] sNames = new string[,] { {
				 "Elizabeth", "Lincoln"}, {"Yang", "Wang"}, { 
				 "Patricio", "Simpson"}, {"Francisco", "Chang"}, { 
				 "Ann", "Devon"}, {"Roland", "Mendel"}, { 
				 "Paolo", "Accorti"}, {"Diego", "Roel"}};
			string[] sType = new string[] { "Visa", "Master", "Cash" };
			data.Clear();
			Random rnd = new Random();
			for (int i = 0; i <= sNames.GetUpperBound(0); i++)
				data.Rows.Add(new object[] { i + 1, sNames[i, 0], sNames[i, 1], sType[i % 3], rnd.Next(-1, 1), rnd.Next(10000) * 0.01 });
		}

		//<gridControl1>
		private void InitGridColumns()
		{
			gridControl1.DataSource = data;
			//The line bellow creates columns on the fly.
			gridControl1.DefaultView.PopulateColumns();
			gridView1.Columns["Payment Amount"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
			gridView1.Columns["Payment Amount"].DisplayFormat.FormatString = "c";
			gridView1.BestFitColumns();
		}
	
		private void panel1_Paint(object sender, PaintEventArgs e)
		{

		}

		private void btnInit_Click(object sender, EventArgs e)
		{
			//删除汉化的旧的记录
			string strTbl;
			strTbl = comboBox1.Text;
			DeleteOldTblContent(strTbl);
			BindField(comboBox1.Text);//重新绑定
			//重新填充
			DataTable tbl;
			tbl = m_currentDataSet.Tables["FS_FieldChi"];
			if (tbl == null) return;

			AutoFillTbl(ref  tbl, strTbl);
		}

		private void AutoFillTbl(ref DataTable tbl, string strTbl)
		{
			int iCount;
			iCount = GetMaxValue("FS_FieldChi", "FieldID") + 1;

			DataTable schemaTable;
			schemaTable = m_OleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
			new Object[] { null, null, strTbl, null });

			//List the schema info for the Employees table
			//in the format Field Descriptor :Field Value.
			for (int i = 0; i < schemaTable.Rows.Count; i++)
			{
				//Console.WriteLine(schemaTable.Columns[3].ToString() + " : " +
				string strFieldName = schemaTable.Rows[i][3].ToString();//列明

				int iType = (int)schemaTable.Rows[i][11];
				string typeName = GetTypeName(iType);//类型

				//Console.WriteLine(schemaTable.Columns[11].ToString() + " : " + typeName);
				// FieldID,TableName,FieldName,FieldAlias,FieldType 
				tbl.Rows .Add(new object[] { iCount + i, strTbl,strFieldName, strFieldName, typeName});
			}

		}

		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 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 void DeleteOldTblContent(string strTbl)
		{
			string strSQL;
			strSQL = "DELETE  FROM FS_FieldChi WHERE TableName ='" + strTbl + "'";
			OleDbCommand command = new OleDbCommand(strSQL, m_OleDbConn);
			command.ExecuteNonQuery();
			command.Dispose();
		}

		private void ucTransField_Load(object sender, EventArgs e)
		{
			DataTable schemaTable = m_OleDbConn.GetOleDbSchemaTable(
						OleDbSchemaGuid.Tables,
						new object[] { null, null, null, "TABLE" });
			//for (int i = 0; i < schemaTable.Columns.Count; i++)
			//{
			//  Console.WriteLine(schemaTable.Columns[i].ToString());
			//}
			comboBox1.DataSource = schemaTable;
			comboBox1.DisplayMember = "TABLE_NAME"; 
		}

		private void comboBox1_SelectedValueChanged(object sender, EventArgs e)
		{
			//Console.WriteLine(comboBox1.Text);
			BindField(comboBox1.Text);
		}

		private void BindField(string strTbl)
		{
			DataSet ds = new DataSet();
			string strSQL;
			strSQL = "SELECT FieldID,TableName,FieldName,FieldAlias,FieldType FROM FS_FieldChi WHERE TableName ='" + strTbl +"'";
			m_currentDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(strSQL, m_OleDbConn);
			string strFillTbl = "FS_FieldChi";

			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 btnSave_Click(object sender, EventArgs e)
		{
			try
			{
				m_currentDbDataAdapter.Update(m_currentDataSet, "FS_FieldChi");
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message,"提示");
			}
		}
	}
}