form1.cs

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

																					this.BtSearch,
																					this.txtSearch,
																					this.Cmbtype});
			this.GroupBox4.Location = new System.Drawing.Point(8, 16);
			this.GroupBox4.Name = "GroupBox4";
			this.GroupBox4.Size = new System.Drawing.Size(256, 56);
			this.GroupBox4.TabIndex = 0;
			this.GroupBox4.TabStop = false;
			// 
			// Label5
			// 
			this.Label5.Location = new System.Drawing.Point(88, 16);
			this.Label5.Name = "Label5";
			this.Label5.Size = new System.Drawing.Size(56, 16);
			this.Label5.TabIndex = 3;
			this.Label5.Text = "查询条件";
			// 
			// Label4
			// 
			this.Label4.Location = new System.Drawing.Point(8, 17);
			this.Label4.Name = "Label4";
			this.Label4.Size = new System.Drawing.Size(56, 16);
			this.Label4.TabIndex = 3;
			this.Label4.Text = "查询类型";
			// 
			// BtSearch
			// 
			this.BtSearch.Location = new System.Drawing.Point(176, 24);
			this.BtSearch.Name = "BtSearch";
			this.BtSearch.Size = new System.Drawing.Size(64, 24);
			this.BtSearch.TabIndex = 2;
			this.BtSearch.Text = "查询";
			this.BtSearch.Click += new System.EventHandler(this.BtSearch_Click);
			// 
			// txtSearch
			// 
			this.txtSearch.Location = new System.Drawing.Point(88, 32);
			this.txtSearch.Name = "txtSearch";
			this.txtSearch.Size = new System.Drawing.Size(56, 21);
			this.txtSearch.TabIndex = 1;
			this.txtSearch.Text = "";
			// 
			// Cmbtype
			// 
			this.Cmbtype.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
			this.Cmbtype.DropDownWidth = 64;
			this.Cmbtype.Items.AddRange(new object[] {
														 "图书编号",
														 "图书名称",
														 "图书总数量"});
			this.Cmbtype.Location = new System.Drawing.Point(8, 32);
			this.Cmbtype.Name = "Cmbtype";
			this.Cmbtype.Size = new System.Drawing.Size(64, 20);
			this.Cmbtype.TabIndex = 0;
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(292, 517);
			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);
			this.GroupBox5.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.GRDbooks)).EndInit();
			this.GroupBox6.ResumeLayout(false);
			this.GroupBox4.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// 应用程序的主入口点。
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}

		private void ToolBar1_ButtonClick(object sender, System.Windows.Forms.ToolBarButtonClickEventArgs e)
		{
			//工具条点击处理
			if(e.Button == TBfirst)
			{
				//如果点击了TBfirst按钮
				rownumber = 0;
				//记录移动到第一条
				TBNext.Enabled = true;
				//向后移动按钮设置为可以用
			}
			else if(e.Button ==TBpre)
			{
				//如果点击了TBpre按钮
				rownumber = rownumber - 1;
				//记录移动到前一条
				if(rownumber == -1)
				{
					//如果移动到了第一条
					TBpre.Enabled = false;
					//设置TBpre按钮为不可用
					return;
					//退出子程序
				}
				TBNext.Enabled = true;
				//向后移动按钮设置为可以用
			}
			else if(e.Button ==TBNext)
			{
				//如果点击了TBNext按钮
				rownumber = rownumber + 1;
				//记录移动到下一条
				if(rownumber == mytable.Rows.Count)
				{
					//如果移动到了最后一条
					TBNext.Enabled = false;
					//设置TBNext按钮为不可用
					return;
					//退出子程序
				}
				TBpre.Enabled = true;
				//向前移动按钮设置为可以用
			}
			else if(e.Button ==TBlast)
			{
				//如果点击了TBLast按钮
				rownumber = mytable.Rows.Count - 1;
				//记录移动到最后一条
				TBpre.Enabled = true;
				//向前移动按钮设置为可以用
			}
			else if(e.Button ==TBExit)
			{
				//如果点击了TBexit按钮
				DialogResult myresult;
				//定义对话框结果变量
				myresult = MessageBox.Show("要保存对图书信息的更改操作吗？"
					, "保存提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
				//取得用户的选择
				if(myresult == DialogResult.Yes)
				{
					//如果选择了yes
					BtSave.PerformClick();
					//执行保存按钮的操作
				}
				Application.Exit();
				//退出应用程序
				return;
				//退出子程序
			}
			try
			{
				myrow = mytable.Rows[rownumber];
				//取得当前行
				TxtBookID.Text = myrow[0].ToString();
				//显示图书编号
				TxtBookName.Text = myrow[1].ToString();
				//显示图书名称
				TxtState.Text = myrow[2].ToString();
				//显示图书当前状态
			}
			catch
			{
				MessageBox.Show("错误", "错误信息"
					, MessageBoxButtons.OK, MessageBoxIcon.Error);
				//显示错误信息
			}
		}

		private void BtAdd_Click(object sender, System.EventArgs e)
		{

			String insertSQL="INSERT INTO tblbooks ( BookID,"
				+" bookName, State) VALUES (@bookid, @bookname, @state)";
			//建立添加查询字符串
			myCmd =new OleDbCommand(insertSQL,myConnection);
			//建立添加命令
			myCmd.Parameters.Add(new OleDbParameter("@bookid",OleDbType.Char,10));
			myCmd.Parameters["@bookid"].Value=TxtBookID.Text;
			//建立参数用于存放书籍ID
			myCmd.Parameters.Add(new OleDbParameter("@bookname",OleDbType.Char,10));
			myCmd.Parameters["@bookname"].Value=TxtBookName.Text;
			//建立参数用于存放书籍名称
			myCmd.Parameters.Add(new OleDbParameter("@state",OleDbType.Char,1));
			myCmd.Parameters["@state"].Value=TxtBookID.Text;
			//建立参数用于存放书籍状态
			myCmd.Connection.Open();
			//打开连接
			try
			{
				myCmd.ExecuteNonQuery();
				//执行查询
				MessageBox.Show("添加成功");
			}
			catch
			{
				MessageBox.Show("添加发生错误检查参数是否和法");
				myCmd.Connection.Close();
				return;
			}
			myCmd.Connection.Close();
			//关闭数据库连接			
			//以下清空显示
			TxtBookID.Text = "";
			TxtBookName.Text = "";
			TxtState.Text = "";	
			OpenDb();
		}

		private void BtSave_Click(object sender, System.EventArgs e)
		{			
			myAdapter.Update(ds, "tblbooks");
		}

		private void BtDel_Click(object sender, System.EventArgs e)
		{
			String delSQL="DELETE FROM tblbooks WHERE"
				+" (((tblbooks.BookID)=[@bookid]))";
			myCmd =new OleDbCommand(delSQL,myConnection);
			//建立添加命令
			myCmd.Parameters.Add(new OleDbParameter("@bookid",OleDbType.Char,10));
			myCmd.Parameters["@bookid"].Value=TxtBookID.Text;
			//建立参数用于存放书籍ID
			myCmd.Connection.Open();
			//打开连接
			try
			{
				myCmd.ExecuteNonQuery();
				//执行查询
				MessageBox.Show("删除成功");
			}
			catch
			{
				MessageBox.Show("删除发生错误检查参数是否和法");
				myCmd.Connection.Close();
				return;
			}
			myCmd.Connection.Close();
			//关闭数据库连接	
			//以下清空显示
			TxtBookID.Text = "";
			TxtBookName.Text = "";
			TxtState.Text = "";
			OpenDb();
		}

		private void BtEdit_Click(object sender, System.EventArgs e)
		{
			String updateSQL="UPDATE tblbooks SET tblbooks.BookID"
				+" = [@bookid], tblbooks.bookName = [@bookname], "
				+"tblbooks.State = [@state] WHERE [tblbooks].[BookID]=[@bookid]";
			//建立添加查询字符串
			myCmd =new OleDbCommand(updateSQL,myConnection);
			//建立添加命令
			myCmd.Parameters.Add(new OleDbParameter("@bookid",OleDbType.Char,10));
			myCmd.Parameters["@bookid"].Value=TxtBookID.Text;
			//建立参数用于存放书籍ID
			myCmd.Parameters.Add(new OleDbParameter("@bookname",OleDbType.Char,10));
			myCmd.Parameters["@bookname"].Value=TxtBookName.Text;
			//建立参数用于存放书籍名称
			myCmd.Parameters.Add(new OleDbParameter("@state",OleDbType.Char,1));
			myCmd.Parameters["@state"].Value=TxtState.Text;
			//建立参数用于存放书籍状态
			myCmd.Connection.Open();
			//打开连接
			try
			{
				myCmd.ExecuteNonQuery();
				//执行查询
				MessageBox.Show("编辑成功");
			}
			catch
			{
				MessageBox.Show("编辑发生错误检查参数是否和法");
				myCmd.Connection.Close();
				return;
			}
			myCmd.Connection.Close();
			//关闭数据库连接	
			OpenDb();
		}

		private void BtSearch_Click(object sender, System.EventArgs e)
		{
			String SearchSQLStr="";
			//定义查询字符串
			OleDbDataAdapter Searchcmd ;
			//定义查询用ADODataSetCommand对象
			DataSet searchds = new DataSet();
			//定义查询DataSet对象
			DataTable Searchtable;
			//定义查询DataTable对象
			DataRow searchrow;
			//定义查询DataRow对象
			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;
						//退出子程序
					}
					SearchSQLStr = "SELECT BookID, bookName, State " +
						"FROM tblbooks " +
							"WHERE (BookID = '" + txtSearch.Text + "')";
					break;
				case 1:
					//建立按图书名称查询SQL语句
					if(txtSearch.Text.Length==0)
					{
						MessageBox.Show("请在文本框中输入查询需要的条件"
							, "操作失败"
							, MessageBoxButtons.OK, MessageBoxIcon.Error);
						//显示操作失败原因
						return;
						//退出子程序
					}
					SearchSQLStr = "SELECT BookID, bookName, State " +
						"FROM tblbooks " +
							"WHERE (bookName = '" + txtSearch.Text + "')";
					break;
				case 2:
					//建立查询查找图书总数
					SearchSQLStr = "SELECT COUNT(bookName) " +
						"FROM tblbooks ";
					Searchcmd = new OleDbDataAdapter(SearchSQLStr
										,"Provider=Microsoft.Jet.OLEDB.4.0;"
										+"Data Source=c:\\lib.mdb");
					//执行查询
					Searchcmd.Fill(searchds, "tblbooks");
					Searchtable = searchds.Tables[0];
					searchrow = Searchtable.Rows[0];
					MessageBox.Show("图书总数为：" + searchrow[0].ToString()
						, "信息框", MessageBoxButtons.OK, MessageBoxIcon.Information);
					//显示图书总数
					return;
					//退出子程序
			}
			Searchcmd =new OleDbDataAdapter(SearchSQLStr
				,"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\lib.mdb");
			//执行查询
			Searchcmd.Fill(searchds, "tblbooks");
			Searchtable = searchds.Tables[0];
			try
			{
				searchrow = Searchtable.Rows[0];
			}
			catch
			{
				//如果没有查找到
				MessageBox.Show("没有查找到该图书的信息。"
					, "查询结果", MessageBoxButtons.OK
					, MessageBoxIcon.Error);
				//显示没有查找到信息
				return;
				//退出子程序
			}
			TxtBookID.Text = searchrow[0].ToString();
			//显示图书编号
			TxtBookName.Text = searchrow[1].ToString();
			//显示图书名称
			TxtState.Text = searchrow[2].ToString();
			//显示图书当前状态
		}

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