queryanalyzer.cs

这是用C#数据库编程的实例

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;


namespace databrowser
{
	/// <summary>
	/// QueryAnalyzer 的摘要说明。
	/// </summary>
	public class QueryAnalyzer : System.Windows.Forms.Form
	{
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.RichTextBox richTextBox1;
		private System.Windows.Forms.TabControl tabControl1;
		private System.Windows.Forms.TabPage tabPage1;
		private System.Windows.Forms.ListView listView1;
		private System.Windows.Forms.ComboBox cmbDatabase;
		private System.Windows.Forms.Button btExe;
		/// <summary>
		/// 必需的设计器变量。
		/// </summary>
		private System.ComponentModel.Container components = null;
		
		public QueryAnalyzer()
		{
			//重载构造函数
			InitializeComponent();
			//初始化组件			
			cmbDatabase.Items.Add("Access");
			//在列表框里增加access选项
			cmbDatabase.Items.Add("SQL Server");
			//在列表框里增加sql选项
		}
		public QueryAnalyzer(ref String ConnectionString)
		{
			//重载构造函数
			InitializeComponent();
			cmbDatabase.Items.Add("Access");
			//在列表框里增加access选项
			cmbDatabase.Items.Add("SQL Server");
			//在列表框里增加sql选项
			m_sConnecionStringAccess = ConnectionString;
			//取得链接字符串
			cmbDatabase.Enabled = false;
			//将cmbDatabase按钮设置为不可用
		}
		/// <summary>
		/// 清理所有正在使用的资源。
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{
			this.cmbDatabase = new System.Windows.Forms.ComboBox();
			this.label1 = new System.Windows.Forms.Label();
			this.btExe = new System.Windows.Forms.Button();
			this.richTextBox1 = new System.Windows.Forms.RichTextBox();
			this.tabControl1 = new System.Windows.Forms.TabControl();
			this.tabPage1 = new System.Windows.Forms.TabPage();
			this.listView1 = new System.Windows.Forms.ListView();
			this.tabControl1.SuspendLayout();
			this.tabPage1.SuspendLayout();
			this.SuspendLayout();
			// 
			// cmbDatabase
			// 
			this.cmbDatabase.Location = new System.Drawing.Point(96, 8);
			this.cmbDatabase.Name = "cmbDatabase";
			this.cmbDatabase.Size = new System.Drawing.Size(121, 20);
			this.cmbDatabase.TabIndex = 0;
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(24, 8);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(48, 16);
			this.label1.TabIndex = 1;
			this.label1.Text = "数据库";
			// 
			// btExe
			// 
			this.btExe.Location = new System.Drawing.Point(320, 0);
			this.btExe.Name = "btExe";
			this.btExe.Size = new System.Drawing.Size(75, 24);
			this.btExe.TabIndex = 2;
			this.btExe.Text = "执行";
			this.btExe.Click += new System.EventHandler(this.btExe_Click);
			// 
			// richTextBox1
			// 
			this.richTextBox1.Location = new System.Drawing.Point(0, 32);
			this.richTextBox1.Name = "richTextBox1";
			this.richTextBox1.Size = new System.Drawing.Size(440, 112);
			this.richTextBox1.TabIndex = 3;
			this.richTextBox1.Text = "";
			this.richTextBox1.KeyDown += new System.Windows.Forms.KeyEventHandler(this.richTextBox1_KeyDown);
			// 
			// tabControl1
			// 
			this.tabControl1.Alignment = System.Windows.Forms.TabAlignment.Bottom;
			this.tabControl1.Controls.AddRange(new System.Windows.Forms.Control[] {
																					  this.tabPage1});
			this.tabControl1.Location = new System.Drawing.Point(0, 152);
			this.tabControl1.Name = "tabControl1";
			this.tabControl1.SelectedIndex = 0;
			this.tabControl1.Size = new System.Drawing.Size(448, 136);
			this.tabControl1.TabIndex = 4;
			// 
			// tabPage1
			// 
			this.tabPage1.Controls.AddRange(new System.Windows.Forms.Control[] {
																				   this.listView1});
			this.tabPage1.Location = new System.Drawing.Point(4, 4);
			this.tabPage1.Name = "tabPage1";
			this.tabPage1.Size = new System.Drawing.Size(440, 111);
			this.tabPage1.TabIndex = 0;
			this.tabPage1.Text = "结果";
			// 
			// listView1
			// 
			this.listView1.Anchor = (((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) 
				| System.Windows.Forms.AnchorStyles.Left) 
				| System.Windows.Forms.AnchorStyles.Right);
			this.listView1.Name = "listView1";
			this.listView1.Size = new System.Drawing.Size(440, 104);
			this.listView1.TabIndex = 0;
			// 
			// QueryAnalyzer
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(448, 301);
			this.Controls.AddRange(new System.Windows.Forms.Control[] {
																		  this.tabControl1,
																		  this.richTextBox1,
																		  this.btExe,
																		  this.label1,
																		  this.cmbDatabase});
			this.Name = "QueryAnalyzer";
			this.Text = "查询分析器";
			this.tabControl1.ResumeLayout(false);
			this.tabPage1.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion
		private String m_sConnectionString="";
		//定义一个链接字符串
		private String m_sConnecionStringAccess;
		//定义一个链接Access字符串
		private void richTextBox1_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
		
		}
		private void ExecuteAccess(ref String connString)
		{
			//自定义执行链接函数
			CAccessDatabase database =new CAccessDatabase(connString);
			//定义数据库对象（自己创建的类）
			bool mySuccessflsg=database.Connect();
			//用布尔变量来判断是否链接成功
			if(mySuccessflsg)
			{
				//如果链接成功
				DataSet myDataSet=null;
				//建立一个空的DataSet对象
				String SQL= richTextBox1.Text.ToString();
				//取得查询字符串
				String mySQLError="";
				//定义出错字符串
				bool myQueryflag=
					database.ExecuteSQL(SQL, ref myDataSet,ref mySQLError);
				//定义布尔变量来判断查询执行正确与否
				if(myQueryflag)
				{
					//如果查询正确
					DisplayData(ref myDataSet);
					//显示myDataSet
				}
				else
				{
					DisplayData(ref mySQLError);
					//如果错误显示错误信息
				}//if
			}//if
		}
		private void ExecuteSQL(ref String connString)
		{
			//自定义执行链接函数
			CDatabase database=new CDatabase(connString);
			//定义数据库对象（自己创建的类）
			bool mySuccessflsg=database.Connect();
			//用布尔变量来判断是否链接成功
			if(mySuccessflsg)
			{
				//如果链接成功
				DataSet myDataSet =null;
				//建立一个空的DataSet对象
				String SQL= richTextBox1.Text.ToString();
				//取得查询字符串
				String mySQLError="";
				//定义出错字符串
				bool myQueryflag=
					database.ExecuteSQL(SQL,ref myDataSet,ref mySQLError);
				//定义布尔变量来判断查询执行正确与否
				if(myQueryflag)
				{
					//如果查询正确
					DisplayData(ref myDataSet);
					//显示myDataSet
				}
				else
				{
					DisplayData(ref mySQLError);
					//如果错误显示错误信息
				}
			}//if
		}
		private void DisplayData(ref DataSet myDataSet)
		{
			//重载显示数据函数
			try
			{
				int itableCount = myDataSet.Tables.Count;
				//取得myDataSet对象中的table数量
				int iTabCount= tabControl1.TabCount;
				//取得tab页数量
				int i;
				//定义整形变量
				for(i=1;i<iTabCount;i++)
				{
					tabControl1.Controls.RemoveAt(i);
					//先删除所有的页
				}
				ListView [] myListView=new ListView[itableCount];
				//定义和表单同样多的列表对象数组来存放表单
				TabPage [] myTabPages=new TabPage[itableCount];
				//定义和表单同样多的tab页对象来存放表单
				myListView[0] = listView1;
				//取得列表对象listView1
				i = 0;
				for(i=0;i<itableCount;i++)
				{
					//对所有的表单进行操作
					myListView[i] = new ListView();
					//建立新列表对象
					myListView[i].Size = listView1.Size;
					//设置列表对象尺寸
					myListView[i].ForeColor=
						System.Drawing.SystemColors.WindowText;
					//设置前景色
					myListView[i].TabIndex = i;
					//设置焦点切换顺序
					myListView[i].Anchor =AnchorStyles.Bottom & AnchorStyles.Left 
						& AnchorStyles.Right & AnchorStyles.Top;
					myTabPages[i] = new TabPage("结果" + (i + 1));
					//建立结果页
					myTabPages[i].Size = tabPage1.Size;
					//设置页的尺寸
					myTabPages[i].TabIndex = i;
					//设置焦点切换顺序
					myTabPages[i].Anchor = AnchorStyles.Bottom & AnchorStyles.Left 
						& AnchorStyles.Right & AnchorStyles.Top;
						
					tabControl1.Controls.Add(myTabPages[i]);
					//将tab页增加到
					myTabPages[i].Controls.Add(myListView[i]);
					//将myListView控件增加到tab页里
				}//for
				int iListViewinArray =0;
				foreach(DataTable myTable in myDataSet.Tables)
				{
					//对表单集合里面的所有表单进行操作
					myListView[iListViewinArray].Clear();
					//清空列表
					myListView[iListViewinArray].View = View.Details;
					//设置列表中为
					foreach(DataColumn myColumn in myTable.Columns)
					{
						//对表单里的每一个列进行操作
						ColumnHeader myColumnHeader=new ColumnHeader();
						//定义列标题
						myColumnHeader.Text = myColumn.ColumnName.ToString();
						//取得列标题
						myListView[iListViewinArray].Columns.Add(myColumnHeader);
						//在列表里增加列标题
					}
					foreach(DataRow myRow in myTable.Rows)
					{
						//取得表单里所有行
						String [] myValues=new String[myTable.Columns.Count];
						//取得表单中的列数目
						i = 0;
						foreach(DataColumn myColumn in myTable.Columns)
						{
							//对表单中所有列进行操作
							myValues[i] = myRow[myColumn].ToString();
							//取得myColumn列myRow行的值，并转化成字符串
							i += 1;
						}						
						ListViewItem myListItem =new ListViewItem(myValues);
						//增加myValues个列表项
						myListView[iListViewinArray].Items.Add(myListItem);
						//增加列表项
					}
					iListViewinArray += 1;
				}
			}//try
			catch(Exception e)
			{
				MessageBox.Show("错误发生在显示结果中"+e.ToString()
					, "显示错误", MessageBoxButtons.OK);
				//反馈异常
			}
		}

		private void btExe_Click(object sender, System.EventArgs e)
		{
			ExecuteAccess(ref m_sConnecionStringAccess);
		}
	//displaydata
		private void DisplayData(ref String mySQLErro)
		{
			//显示错误信息
			int iTabCount=tabControl1.TabCount;
			//取得tab页数量
			int i;
			//定义整形变量
			for(i=1;i<iTabCount ;i++)
			{
				//对每一个页进行操作
				tabControl1.Controls.RemoveAt(i);
				//先删除所有的页
			}
			listView1.Clear();
			//清空列表
			listView1.View = View.Details;
			//设置列表为显示详细资料模式
			ColumnHeader myColumnHeader= new ColumnHeader();
			//定义列标题
			myColumnHeader.Text = "SQL Query returns Error";
			//设置列标题
			listView1.Columns.Add(myColumnHeader);
			//增加列标题
			listView1.Items.Add(mySQLErro);
			//增加错误信息
		}
	}
}