dbmaintainfrm.cs

能对数据库进行维护

				int s=Convert.ToInt32(dataGrid1[CR,IdentityColIndex]);//dataGrid的列序号也就是主键的值
				DataRow myrow= ds.Tables[0].Rows.Find(s);//找到对应的行
				myrow.Delete();
				dataGrid1.Refresh();//更新dataGrid
				updatatoDB2();
				dataGridDataBiding("Select * from "+tablename+"");
				this.dataGrid1.CurrentRowIndex=CR;
			
				//			
				//			string constr1=da.getconnstr();//da为全局变量 是另外一个类的实例
				//			SqlConnection conn= new SqlConnection(constr1);
				//			conn.Open();
				//			SqlDataAdapter Da=new SqlDataAdapter ();
				//			Da.SelectCommand=new SqlCommand(SQL,conn);
				//			SqlCommandBuilder cb=new SqlCommandBuilder(Da);
				//			Da.SelectCommand   =  cb.GetInsertCommand(); 
				//			Da.UpdateCommand   =  cb.GetUpdateCommand(); 
				//			Da.DeleteCommand   =  cb.GetDeleteCommand(); 	
				//			DataSet dsChanges = ds.GetChanges();
				//			if (dsChanges != null)
				//			{
				//				Da.Update(dsChanges,tablename);
				//				ds.AcceptChanges();
				//			}
		
			}
		}

		#endregion

		#region //菜单保存
		private void menuItem3_Click(object sender, System.EventArgs e)
		{
			
			updatatoDB();
		}
		#endregion
       


		#region // 点击右键选中一行，弹出菜单
		private void dataGrid1_MouseDown(object sender, System.Windows.Forms.MouseEventArgs e)
		{
			if (e.Button==MouseButtons.Right)
			{
				System.Drawing.Point   pt    =   new   Point(e.X,   e.Y);     
				DataGrid.HitTestInfo   hti   =   dataGrid1.HitTest(pt);     
				if(hti.Type   ==   DataGrid.HitTestType.Cell)     
				{    
					dataGrid1.UnSelect(this.dataGrid1.CurrentRowIndex);//先清楚当前选择
					dataGrid1.CurrentCell   =   new   DataGridCell(hti.Row,   hti.Column); //确定点击位置
                      dataGrid1.Select(hti.Row);  //选中当前行
					
				}    

			}
		}


		#endregion
		
		

		#region 增加一行 焦点自动到最底部
		private void menuItem1_Click(object sender, System.EventArgs e)//增加一行
		{
			this.dataGrid1.UnSelect(this.dataGrid1.CurrentRowIndex);
			int f=ds.Tables[0].Rows.Count;
			this.dataGrid1.CurrentCell=new DataGridCell(f,1);
		}
		#endregion

		#region //保存到数据库
		private void updatatoDB()
		{	
//			MessageBox.Show(this.dataGrid1.CurrentRowIndex.ToString());
			GetFocus(this.dataGrid1.CurrentRowIndex+1,1);
//			MessageBox.Show(this.dataGrid1.CurrentRowIndex.ToString());
			
//			GetFocus(this.dataGrid1.CurrentRowIndex-1,1);
//			MessageBox.Show(this.dataGrid1.CurrentRowIndex.ToString());

			if (ds.HasChanges())
			{
				if (MessageBox.Show("数据已经更改，是否要保存到数据库？","重要提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
				{
					try
					{
						constr=da.getconnstr();
						DataSet newds=ds.GetChanges();
						DataSet lastds=da.UpdateByDataSet(newds,tablename,constr);
						ds.AcceptChanges();
						dataGrid1.Refresh();
					}
					catch (System.Exception ex)
					{
						MessageBox.Show(ex.ToString());
					}
				} 
				else
				{
					ds.RejectChanges();
					dataGrid1.Refresh();
				}
			}
		}
		private void updatatoDB2()
		{
			CR=this.dataGrid1.CurrentRowIndex;
//			this.dataGrid1[CR,0]=0;
		    GetFocus(this.dataGrid1.CurrentRowIndex+1,1);

			if (ds.HasChanges())
			{
					try
					{
						constr=da.getconnstr();
						DataSet newds=ds.GetChanges();
						DataSet lastds=da.UpdateByDataSet(newds,tablename,constr);
//						ds.AcceptChanges();
//						dataGrid1.Refresh();
					}
					catch (System.Exception ex)
					{
						MessageBox.Show(ex.ToString());
					}
				} 
             else
				return;
			
		}
		#endregion

		#region// dataGrid焦点从一行单元格移动到另外一行单元格 ds.haschangs才为真 所以要设置焦点
		private void GetFocus(int row,int col)
		{
			//先把焦点移动到DataGrid
			this.dataGrid1.Focus();   
			//把焦点移动到DataGridCell
			DataGridCell dgc = new DataGridCell(row,col); 
			this.dataGrid1.CurrentCell = dgc; 
			DataGridTextBoxColumn dgtb = (DataGridTextBoxColumn)dataGrid1.TableStyles[0].GridColumnStyles[col]; 
			//设置焦点
			dgtb.TextBox.Focus();
			 
		}          
		
		#endregion

		#region//判断表的所有者 决定权限
		public string  permition()
		{
			string sql2="select  user_name(uid)   from   sysobjects   where   name='"+tablename+"' ";//查询表的所有者是谁！！
			string owner=null;
			try
			{
				DataTable mydt=da.GetDataTable(sql2);
				owner=mydt.Rows[0][0].ToString().Trim();
				this.ownerPermiton=owner;
			}
			catch (System.Exception ex)
			{
				MessageBox.Show(ex.ToString());
			}
			return owner;
		}
		#endregion

		#region//查询相关代码
		private void cmbSearch1_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			this.cmbMath1.Text="";
			this.cmbMath1.Items.Clear();
			int i=this.cmbSearch1.SelectedIndex+1;
			
			if (ds.Tables[0].Rows[0][i].GetType().ToString()=="System.String")
			{
				this.cmbMath1.Items.Add("包含");
				this.cmbMath1.Items.Add("不包含");


			} 
			else
			{
				this.cmbMath1.Items.Add(">");
				this.cmbMath1.Items.Add("<");
				this.cmbMath1.Items.Add("=");
				this.cmbMath1.Items.Add(">=");
				this.cmbMath1.Items.Add("<=");
				this.cmbMath1.Items.Add("<>");
			}
		}

		private void cmbMatch2_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			if (this.cmbSearch1.Text=="")
			{
				MessageBox.Show("请先选择检索项");
			} 
		}

		private void cmbSearch2_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			this.cmbMatch2.Text="";
			this.cmbMatch2.Items.Clear();
		    int j=this.cmbSearch2.SelectedIndex+1;
			
			if (ds.Tables[0].Rows[0][j].GetType().ToString()=="System.String")
			{
				this.cmbMatch2.Items.Add("包含");
				this.cmbMatch2.Items.Add("不包含");


			} 
			else
			{
				this.cmbMatch2.Items.Add(">");
				this.cmbMatch2.Items.Add("<");
				this.cmbMatch2.Items.Add("=");
				this.cmbMatch2.Items.Add(">=");
				this.cmbMatch2.Items.Add("<=");
				this.cmbMatch2.Items.Add("<>");
			}
		}

		private void cmbMath1_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			
			if (this.cmbSearch1.Text=="")
			{
				MessageBox.Show("请先选择检索项");
			} 
		}

		private void txtKey1_TextChanged(object sender, System.EventArgs e)
		{
			if (this.cmbSearch1.Text==""||this.cmbMath1.Text=="")
			{
				MessageBox.Show("请先输入检索条件");
			}
		}

		private void txtKey2_TextChanged(object sender, System.EventArgs e)
		{
			if (this.cmbSearch2.Text==""||this.cmbMatch2.Text=="")
			{
				MessageBox.Show("请先输入检索条件");
			}
		}
		public string sqlSearch()
		{
			if (txtKey1.Text.ToString().Trim()=="")
			{
				MessageBox.Show("请输入查询关键字");
			} 
			string sql=null;
			sql="Select * from "+tablename+" where ";
			string sql1=null;
			string sql2=null;
			string a=this.cmbSearch1.Text.ToString().Trim();
			string b=this.cmbMath1.Text.ToString().Trim();
			string c=this.txtKey1.Text.ToString().Trim();
			if (this.cmbMath1.Text=="包含"||this.cmbMath1.Text=="不包含")
			{
				if (this.cmbMath1.Text=="包含")
				{
					
					sql1=""+a+" like'%"+c+"%'";
				} 
				else
				{
					sql1=""+a+" not like'%"+c+"%'";
				}  
			}
			else
				if (c.IndexOf("0123456789")!=0)
			{
				sql1=""+a+""+b+""+c+"";
			} 
			else
			{
				MessageBox.Show("关键字必须为数字");
			}
			sql=sql+sql1;
			if (this.txtKey2.Text.ToString().Trim()!="")
			{
				string e=this.cmbSearch2.Text.ToString().Trim();
				string f=this.cmbMatch2.Text.ToString().Trim();
				string g=this.txtKey2.Text.ToString().Trim();
				if (this.cmbMatch2.Text=="包含"||this.cmbMatch2.Text=="不包含")
				{
					if (this.cmbMatch2.Text=="包含")
					{
					
						sql1=""+e+" like'%"+g+"%'";
					} 
					else
					{
						sql1=""+e+" not like'%"+g+"%'";
					}  
				}
				else
					if (g.IndexOf("0123456789")!=0)
				{
					sql2=""+e+""+f+""+g+"";
				} 
				else
				{
					MessageBox.Show("关键字必须为数字");
				}
				sql=""+sql+"and "+sql2+"";
			} 
			MessageBox.Show(sql.Trim());
			return sql;		
		}

		private void btnSearch_Click(object sender, System.EventArgs e)
		{
		  SQL=sqlSearch();
		dataGridDataBiding(SQL);
		}

		private void cmbMath1_Click(object sender, System.EventArgs e)
		{
		
			if (this.cmbSearch1.Text=="")
			{
				MessageBox.Show("请先选择检索项");
			} 
		}

		private void cmbMatch2_Click(object sender, System.EventArgs e)
		{
		
			if (this.cmbSearch2.Text=="")
			{
				MessageBox.Show("请先选择检索项");
			} 
		}
		#endregion

		private void DBMaintainFrm_Closing(object sender, System.ComponentModel.CancelEventArgs e)
		{
			GetFocus(this.dataGrid1.CurrentRowIndex+1,1);
			if (ds.HasChanges())
			{
				if (MessageBox.Show("数据已经更改，是否要保存到数据库？","重要提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
				{
					try
					{
						constr=da.getconnstr();
						DataSet newds=ds.GetChanges();
						DataSet lastds=da.UpdateByDataSet(newds,tablename,constr);
						ds.AcceptChanges();
						dataGrid1.Refresh();
					}
					catch (System.Exception ex)
					{
						MessageBox.Show(ex.ToString());
					}
				} 
				else
				{
					ds.RejectChanges();
					dataGrid1.Refresh();
				}
			}
		}
	}
		
}