form1.cs

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

			this.txtSearch.Text = "";
			// 
			// Label5
			// 
			this.Label5.Location = new System.Drawing.Point(88, 16);
			this.Label5.Name = "Label5";
			this.Label5.Size = new System.Drawing.Size(72, 15);
			this.Label5.TabIndex = 0;
			this.Label5.Text = "查询条件";
			// 
			// Label4
			// 
			this.Label4.Location = new System.Drawing.Point(16, 17);
			this.Label4.Name = "Label4";
			this.Label4.Size = new System.Drawing.Size(72, 15);
			this.Label4.TabIndex = 0;
			this.Label4.Text = "查询类型";
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(304, 485);
			this.Controls.AddRange(new System.Windows.Forms.Control[] {
																		  this.GroupBox3,
																		  this.GroupBox2,
																		  this.ToolBar1,
																		  this.GroupBox1});
			this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;
			this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
			this.Name = "Form1";
			this.Text = "Form1";
			this.GroupBox1.ResumeLayout(false);
			this.GroupBox2.ResumeLayout(false);
			this.GroupBox3.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.GRDbooks)).EndInit();
			this.GroupBox5.ResumeLayout(false);
			this.GroupBox4.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// 应用程序的主入口点。
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}
		//----------------------------------------------
		//以下定义了一些DAO对象
		//和一些SQL打开数据库字符串
		//设计了一个OPenDB（）函数
		//用DAO对象打开了数据库
		//----------------------------------------------
		DAO.Database Mydb;
		//定义一个DAO.Database数据库对象
		DAO.Workspace MyWs;
		//定义一个工作空间DAO.Workspace
		String MySql ;
		//定义一个字符串用来存放SQL查询语言
		DAO.Recordset Myrec;
		//定义一个数据项DAO.Recordset
		DAO.DBEngine de=new DAO.DBEngineClass();
		//定义一个数据引擎DAO.DBEngine()
		public bool OpenDb()
		{
			//定义一个数据打开函数
			String dbstr;
			//定义一个字符串来存放数据库所在目录
			dbstr ="c:\\lib.mdb";
			//取得数据库所在目录
			MyWs = de.Workspaces[0];
			//建立一个工作空间
			Mydb = MyWs.OpenDatabase(dbstr,null,null,null);
			//打开数据库存放于Mydb
			MySql = "select * from tblstudents";
			//设置SQL打开方式
			Myrec = Mydb.OpenRecordset(MySql,
				DAO.RecordsetTypeEnum.dbOpenDynaset,
				DAO.RecordsetOptionEnum.dbDenyWrite,
				DAO.LockTypeEnum.dbOptimistic);
			//打开数据项
			return true;
		}

		private void ToolBar1_ButtonClick(object sender, System.Windows.Forms.ToolBarButtonClickEventArgs e)
		{
			if(e.Button==TBfirst)
			{
				//如果点击了TBfirst按钮
				Myrec.MoveFirst();
				//记录移动到第一条
				TBNext.Enabled = true;
				//向后移动按钮设置为可以用
			}
			else if(e.Button==TBpre)
			{
				//如果点击了TBpre按钮
				Myrec.MovePrevious();
				//记录移动到前一条
				if(Myrec.EOF)
				{
					//如果移动到了第一条
					TBpre.Enabled = false;
					//设置TBpre按钮为不可用
					return;
					//退出子程序
				}
				TBNext.Enabled = true;
				//向后移动按钮设置为可以用
			}
			else if(e.Button==TBNext)
			{
				//如果点击了TBNext按钮
				Myrec.MoveNext();
				//记录移动到下一条
				if(Myrec.EOF)
				{
					//如果移动到了最后一条
					TBNext.Enabled = false;
					//设置TBNext按钮为不可用
					return;
					//退出子程序
				}
				TBpre.Enabled = true;
				//向前移动按钮设置为可以用
			}
			else if(e.Button==TBlast)
			{
				//如果点击了TBLast按钮
				Myrec.MoveLast(0);
				//记录移动到最后一条
				TBpre.Enabled =true;
				//向前移动按钮设置为可以用
			}
			else if(e.Button==TBExit)
			{
				//如果点击了TBexit按钮
				Application.Exit();
				//退出应用程序
				return;
				//退出子程序
			}
			//应用数据库连接函数，打开数据连接
			TxtNumber.Text = Myrec.Fields[0].Value.ToString();
			//显示学号
			TxtName.Text = Myrec.Fields[1].Value.ToString();
			//显示姓名
			TxtPassword.Text = Myrec.Fields[2].Value.ToString();
			//显示密码     
		}

		private void BtAdd_Click(object sender, System.EventArgs e)
		{						
			String addSQLstr ;
			//定义一个SQL查询字符串
			addSQLstr = "select * from tblstudents where Number='"
				+ TxtNumber.Text + "'";
			//查看要添加的记录是否在数据中
			Myrec.Close();
			Myrec = Mydb.OpenRecordset(addSQLstr,
				DAO.RecordsetTypeEnum.dbOpenDynaset,
				DAO.RecordsetOptionEnum.dbDenyWrite,
				DAO.LockTypeEnum.dbOptimistic);
			//取得查询结果
			if(Myrec.RecordCount > 0)
			{
				//如果数据库中已经存在该记录
				MessageBox.Show("数据库中已经包含有该记录了，"
					+"如果要更改该记录请选用编辑按钮", "信息框",
					MessageBoxButtons.OK, MessageBoxIcon.Error);
				//显示添加操作失败的信息
				return ;
				//退出子程序
			}
			try
			{
				Myrec.AddNew();
				//增加新记录
				Myrec.Fields[0].Value = TxtNumber.Text;
				//给myrec.Fields[0]赋值
				Myrec.Fields[1].Value = TxtName.Text;
				//给myrec.Fields[1]赋值
				Myrec.Fields[2].Value = TxtPassword.Text;
				//给myrec.Fields(2)赋值
				Myrec.Update(1,false);
				//保存数据
				//以下清空文本框
				TxtNumber.Text = "";
				TxtName.Text = "";
				TxtPassword.Text = "";
			}
			catch
			{
				MessageBox.Show("保存错误","错误");
			}
			Myrec.Close();
			//关闭临时Recordset对象
			OpenDb();
		}

		private void BtDel_Click(object sender, System.EventArgs e)
		{
			String DelSQLstr;
			//定义一个SQL查询字符串
			DelSQLstr = "select * from tblstudents where Number='"
				+ TxtNumber.Text + "'";
			//查看要删除的记录是否在数据中
			Myrec.Close();
			Myrec = Mydb.OpenRecordset(DelSQLstr,
				DAO.RecordsetTypeEnum.dbOpenDynaset,
				DAO.RecordsetOptionEnum.dbDenyWrite,
				DAO.LockTypeEnum.dbOptimistic);
			//取得查询结果
			if(Myrec.RecordCount ==0 )
			{
				//如果数据库中不存在该记录
				MessageBox.Show("数据库库中不存在此记录，无法完成删除操作。"
					, "信息框", MessageBoxButtons.OK, MessageBoxIcon.Error);
				//显示删除操作失败的信息
				return;
				//退出子程序
			}
			Myrec.Delete();
			//删除当前记录
			//以下清空文本框
			TxtNumber.Text = "";
			TxtName.Text = "";
			TxtPassword.Text = "";
			Myrec.Close();
			OpenDb();
		}

		private void BtEdit_Click(object sender, System.EventArgs e)
		{
			String addSQLstr ;
			//定义一个SQL查询字符串
			addSQLstr = "select * from tblstudents where Number='"
				+ TxtNumber.Text + "'";
			//查看要添加的记录是否在数据中
			Myrec.Close();
			Myrec = Mydb.OpenRecordset(addSQLstr,
				DAO.RecordsetTypeEnum.dbOpenDynaset,
				DAO.RecordsetOptionEnum.dbDenyWrite,
				DAO.LockTypeEnum.dbOptimistic);
			//取得查询结果
			if(Myrec.RecordCount == 0)
			{
				//如果数据库中不存在该记录
				MessageBox.Show("数据库库中不包含有该记录，编辑操作失败。"
					, "信息框", MessageBoxButtons.OK, MessageBoxIcon.Error);
				//显示编辑操作失败的信息
				return;
				//退出子程序
			}
			Myrec.Edit();
			//增加新记录
			Myrec.Fields[0].Value = TxtNumber.Text;
			//给myrec.Fields[0]赋值
			Myrec.Fields[1].Value = TxtName.Text;
			//给myrec.Fields[1]赋值
			Myrec.Fields[2].Value = TxtPassword.Text;
			//给myrec.Fields(2)赋值
			Myrec.Update(1,false);
			//保存数据
			Myrec.Close();
			OpenDb();
		}

		private void BtSearch_Click(object sender, System.EventArgs e)
		{
			String SearchSQLStr="";
			//定义查询字符串
			switch(Cmbtype.SelectedIndex) 
			{
				case -1:
					MessageBox.Show("请先选择查询类型。", "查询操作失败", 
						MessageBoxButtons.OK, MessageBoxIcon.Information);
					//显示查询操作失败的原因
					return;
					//退出子程序					
				case 0:
					//建立按学号查询SQL语句
					if(txtSearch.Text.Length==0)
					{
						MessageBox.Show("请在文本框中输入查询需要的条件",
							"操作失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
						//显示操作失败原因
						return;
						//退出子程序
					}//if
					SearchSQLStr = "SELECT [Number], Name, [PassWord] " +
						"FROM(tblstudents)" +
						"WHERE ([Number] = '" + txtSearch.Text + "')";
					break;
				case 1:
					//建立按姓名查询SQL语句
					if(txtSearch.Text.Length==0)
					{
						MessageBox.Show("请在文本框中输入查询需要的条件", "操作失败",
							MessageBoxButtons.OK, MessageBoxIcon.Error);
						//显示操作失败原因
						return;
						//退出子程序
					}//if
					SearchSQLStr = "SELECT [Number], Name, [PassWord]" +
						"FROM(tblstudents)" +
						"WHERE (Name = '" + txtSearch.Text + "')";
					break;
				case 2:
					//建立查询查找学总人数
					SearchSQLStr = "SELECT COUNT(Name)" +
						"FROM(tblstudents)";
					Myrec = Mydb.OpenRecordset(SearchSQLStr, 
						DAO.RecordsetTypeEnum.dbOpenDynaset,
						DAO.RecordsetOptionEnum.dbDenyWrite,
						DAO.LockTypeEnum.dbOptimistic);
					//执行查询
					MessageBox.Show("学生总人数为：" + Myrec.Fields[0].Value.ToString(),
						"信息框",MessageBoxButtons.OK, MessageBoxIcon.Information);
					//显示学生总人数
					return;
					//退出子程序
				default:
					break;
			}
			Myrec.Close();
			Myrec = Mydb.OpenRecordset(SearchSQLStr,
				DAO.RecordsetTypeEnum.dbOpenDynaset,
				DAO.RecordsetOptionEnum.dbDenyWrite,
				DAO.LockTypeEnum.dbOptimistic);
			//执行查询
			if(Myrec.RecordCount == 0)
			{
				//如果没有查找到
				MessageBox.Show("没有查找到该学生的信息。"
					, "查询结果", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
				//退出子程序
			}
			TxtNumber.Text = Myrec.Fields[0].Value.ToString();
			//显示学号
			TxtName.Text = Myrec.Fields[1].Value.ToString();
			//显示姓名
			TxtPassword.Text = Myrec.Fields[2].Value.ToString();
			//显示密码   
		}

		private void BtSearchBooks_Click(object sender, System.EventArgs e)
		{
			String SearchSQLStr;
			//定义查询字符串
			if(txtSearchNumber.Text.Length==0)
			{
				MessageBox.Show("请在文本框中输入要查询的学号"
					, "操作失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
				//退出子程序
			}
			SearchSQLStr = "SELECT  tblborrow.[Number]," +
				"tblborrow.BookID,tblborrow.BorrowData,"+
				"tblbooks.bookName,tblbooks.State " +
				"FROM tblborrow INNER JOIN tblbooks ON " +
				"tblborrow.BookID = tblbooks.BookID " +
				" WHERE (tblborrow.[Number] = '" + txtSearchNumber.Text + "')";
			//建立查询语句
			System.Data.OleDb.OleDbDataAdapter adocmd ;
			//建立ADODataSetCommand对象
			adocmd =new System.Data.OleDb.OleDbDataAdapter(SearchSQLStr
							 , "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\lib.mdb");
			//建立ADODataSetCommand对象
			DataSet ds =new DataSet();
			//建立DataSet对象
			adocmd.Fill(ds, "tblborrow");
			//将查询结果装入ds
			GRDbooks.DataSource = ds.Tables[0].DefaultView;
			//在DBgird控件中显示查询结果
		}
	}
}