mainform.cs

基于SQL CE存储数据的学生信息管理系统

				sqlEngine=new SqlCeEngine();
				sqlEngine.LocalConnectionString="Data Source=\\My Documents\\studinfo.sdf;password=";
				sqlEngine.CreateDatabase();
			}
			catch
			{
				
				MessageBox.Show("创建数据库失败！","建库错误",MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
				
			}
			finally
			{
				sqlEngine.Dispose();
			}
		}

		private void menuItem4_Click(object sender, System.EventArgs e)
		{
			conn=null;
			try
			{
				conn = new SqlCeConnection ("Data Source=\\My Documents\\studinfo.sdf;password=");
				conn.Open();
				SqlCeCommand cmd=conn.CreateCommand();
				cmd.CommandText="create table student(sid nchar(10) unique,sname nvarchar(20),ssex nchar(2),sage integer,sdept nvarchar(20))";				
				cmd.ExecuteNonQuery();
			}
			catch
			{
				MessageBox.Show("创建学生表失败！","建表错误",MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
			}
			finally
			{
				conn.Close();
			}
		}

		private void menuItem6_Click(object sender, System.EventArgs e)
		{
			dataGrid1.DataSource=null;
		}

		private void menuItem7_Click(object sender, System.EventArgs e)
		{
			if (MessageBox.Show("确定要退出程序吗？","提示",MessageBoxButtons.YesNo,MessageBoxIcon.Exclamation,MessageBoxDefaultButton.Button2)==DialogResult.Yes) 	
			{
				this.Close();
			}
		}

	
			
			
		

		private void button1_Click(object sender, System.EventArgs e)
		{
			AddForm addfrm=new AddForm();
			if (addfrm.ShowDialog()==DialogResult.OK)
			{
				SqlCeConnection conn=null;
				try
				{
					conn = new SqlCeConnection ("Data Source=\\My Documents\\studinfo.sdf;password=");
					conn.Open();
					SqlCeCommand cmd=conn.CreateCommand();
					cmd.CommandText="insert into student(sid,sname,ssex,sage,sdept) values(?,?,?,?,?)";
					cmd.Parameters.Add("sid",addfrm.textBox1.Text.ToString());
					cmd.Parameters.Add("sname",addfrm.textBox2.Text.ToString());
					cmd.Parameters.Add("ssex",addfrm.comboBox1.Text.ToString());
					cmd.Parameters.Add("sage",addfrm.numericUpDown1.Value);
					cmd.Parameters.Add("sdept",addfrm.comboBox2.Text.ToString());
					cmd.ExecuteNonQuery();

					cmd.CommandType=CommandType.TableDirect;
					cmd.CommandText="student";
					SqlCeDataAdapter da=new SqlCeDataAdapter(cmd);
					DataSet ds=new  DataSet ();
					da.Fill(ds);				
					dataGrid1.DataSource=ds.Tables[0];
				}
				catch
				{
					MessageBox.Show("添加数据失败！","错误",MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
				}
				finally
				{
					conn.Close();
				}
			}
		}

		private void button2_Click(object sender, System.EventArgs e)
		{
			int selectRow=dataGrid1.CurrentRowIndex;
			String sid=dataGrid1[selectRow,0].ToString();
			String sname=dataGrid1[selectRow,1].ToString();
			String ssex=dataGrid1[selectRow,2].ToString();
			String sage=dataGrid1[selectRow,3].ToString();
			String sdept=dataGrid1[selectRow,4].ToString();
			EditForm edtfrm=new EditForm(sid,sname,ssex,sage,sdept);
			if (edtfrm.ShowDialog()==DialogResult.OK )
			{
				SqlCeConnection conn=null;
				try
				{
					conn = new SqlCeConnection ("Data Source=\\My Documents\\studinfo.sdf;password=");
					conn.Open();
					SqlCeCommand cmd=conn.CreateCommand();
					cmd.CommandText="update student set sname=?,ssex=?,sage=?,sdept=? where sid=?";						
					cmd.Parameters.Add("sname",edtfrm.textBox2.Text.ToString());
					cmd.Parameters.Add("ssex",edtfrm.domainUpDown1.Text.ToString());
					cmd.Parameters.Add("sage",edtfrm.numericUpDown1.Value);
					cmd.Parameters.Add("sdept",edtfrm.comboBox2.Text.ToString());
					cmd.Parameters.Add("sid",edtfrm.textBox1.Text.ToString());
					cmd.ExecuteNonQuery();

					cmd.CommandType=CommandType.TableDirect;
					cmd.CommandText="student";
					SqlCeDataAdapter da=new SqlCeDataAdapter(cmd);
					DataSet ds=new  DataSet ();
					da.Fill(ds);				
					dataGrid1.DataSource=ds.Tables[0];
				}
				catch
				{
					MessageBox.Show("编辑数据失败！","错误",MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
				}
				finally
				{
					conn.Close();
				}
			}
           
		}

		private void button4_Click(object sender, System.EventArgs e)
		{
			SelectForm selectfrm=new SelectForm();
			if (selectfrm.ShowDialog()==DialogResult.OK)
			{
				SqlCeConnection conn=null;
				String tiaojian= selectfrm.domainUpDown1.Text.ToString();
				if (tiaojian=="学号")  tiaojian ="sid";
				if (tiaojian=="姓名")  tiaojian ="sname";
				if (tiaojian=="性别")  tiaojian ="ssex";
				if (tiaojian=="年龄")  tiaojian ="sage";
				if (tiaojian=="系部")  tiaojian ="sdept";
				String fuhao="",zhi="";
				if (selectfrm.radioButton1.Checked==true)
					fuhao=" = ";
				if (selectfrm.radioButton2.Checked==true)
					fuhao=" > ";
				if (selectfrm.radioButton3.Checked==true)
					fuhao=" < ";
				zhi=selectfrm.textBox1.Text.ToString();
				try
				{
					conn = new SqlCeConnection ("Data Source=\\My Documents\\studinfo.sdf;password=");
					conn.Open();
					SqlCeCommand cmd=conn.CreateCommand();
					cmd.CommandText="select * from student where "+ tiaojian+"  "+fuhao+zhi;
					//cmd.CommandType=CommandType.TableDirect;
					SqlCeDataAdapter da=new SqlCeDataAdapter(cmd);
					DataSet ds=new  DataSet ();
					da.Fill(ds);				
					dataGrid1.DataSource=ds.Tables[0];
				}
				catch
				{
					MessageBox.Show("查询数据失败！","错误",MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
				}
				finally
				{
					conn.Close();
				}
			}
		}

		private void button3_Click(object sender, System.EventArgs e)
		{
			if(MessageBox.Show("此操作将删除所选的学生信息，是否继续？", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
			{
				SqlCeConnection conn=null;
				int selectRow=dataGrid1.CurrentRowIndex;
				String selectid=dataGrid1[selectRow,0].ToString();
				try
				{
					conn = new SqlCeConnection ("Data Source=\\My Documents\\studinfo.sdf;password=");
					conn.Open();
					SqlCeCommand cmd=conn.CreateCommand();
					cmd.CommandText="delete student where sid=?";	
					cmd.Parameters.Add("sid",selectid);
					cmd.ExecuteNonQuery();

					cmd.CommandType=CommandType.TableDirect;
					cmd.CommandText="student";
					SqlCeDataAdapter da=new SqlCeDataAdapter(cmd);
					DataSet ds=new  DataSet ();
					da.Fill(ds);				
					dataGrid1.DataSource=ds.Tables[0];
				}
				catch
				{
					MessageBox.Show("删除数据失败！","错误",MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
				}
				finally
				{
					conn.Close();
				}
			}
		}

		private void button5_Click(object sender, System.EventArgs e)
		{
			SqlCeConnection conn=null;
			try
			{
				conn = new SqlCeConnection ("Data Source=\\My Documents\\studinfo.sdf;password=");
				conn.Open();
				SqlCeCommand cmd=conn.CreateCommand();
				cmd.CommandText="select * from student ";				
				SqlCeDataAdapter da=new SqlCeDataAdapter(cmd);
				DataSet ds=new  DataSet ();
				da.Fill(ds);				
				dataGrid1.DataSource=ds.Tables[0];
			}
			catch(SqlCeException sqlex)
			{
				foreach(SqlCeError sqlError in sqlex.Errors)
				{
					MessageBox.Show(sqlError.Message,"错误");
				}
			}
			finally
			{
				conn.Close();
			}
		}

		private void button6_Click(object sender, System.EventArgs e)
		{
			SqlCeConnection conn=null;
			try
			{
				conn = new SqlCeConnection ("Data Source=\\My Documents\\studinfo.sdf;password=");
				conn.Open();
				SqlCeCommand cmd=conn.CreateCommand();
				cmd.CommandText="select * from student order by sage desc ";					
				SqlCeDataAdapter da=new SqlCeDataAdapter(cmd);
				DataSet ds=new  DataSet ();
				da.Fill(ds);				
				dataGrid1.DataSource=ds.Tables[0];
				
			}
			catch(SqlCeException sqlex)
			{
				foreach(SqlCeError sqlError in sqlex.Errors)
				{
					MessageBox.Show(sqlError.Message,"错误");
				}
			}
			finally
			{
				conn.Close();
			}
		}

		private void button7_Click(object sender, System.EventArgs e)
		{
			SqlCeConnection conn=null;
			try
			{
				conn = new SqlCeConnection ("Data Source=\\My Documents\\studinfo.sdf;password=");
				conn.Open();
				SqlCeCommand cmd=conn.CreateCommand();
				cmd.CommandText="select ssex,avg(sage)from student group by ssex";				
				SqlCeDataAdapter da=new SqlCeDataAdapter(cmd);
				DataSet ds=new  DataSet ();
				da.Fill(ds);				
				dataGrid1.DataSource=ds.Tables[0];
			}
			catch(SqlCeException sqlex)
			{
				foreach(SqlCeError sqlError in sqlex.Errors)
				{
					MessageBox.Show(sqlError.Message,"错误");
				}
			}
			finally
			{
				conn.Close();
			}
		}

		private void MainForm_Load_1(object sender, System.EventArgs e)
		{
		      statusBar1.Text="                                                               今日日期:"+DateTime.Now.ToShortDateString();
		}

		private void dataGrid1_MouseUp(object sender, System.Windows.Forms.MouseEventArgs e)
		{
			DataGrid.HitTestInfo hitTest = dataGrid1.HitTest(e.X, e.Y);
			if (hitTest.Type == DataGrid.HitTestType.ColumnHeader)
			{
				 
				// Sort tapped column
				DataTable dataTable = (DataTable)dataGrid1.DataSource;
				DataView dataView = dataTable.DefaultView;
				string columnName = dataTable.Columns[hitTest.Column].ColumnName;
				if (dataView.Sort == columnName)
					dataView.Sort = columnName + " DESC";
				else
					dataView.Sort = columnName;
			}
		}
	}
}