📄 queryemployeesalary.cs
字号:
if(numrows<2)
{
MessageBox.Show("对不起,您不能发送email!!!\n\n 提示:\n 导入数据库不含数据行！！\n 请重新导入数据库");
return;
}
int namefield=0,emailfield=0,bankcardfield=0;
int line=0;
if(numrows>=6) numrows=6;
int i1=0;
for(i1=0;i1<numrows;i1++)
{
int senderfeild=0;
for(int i2=0;i2<countfields;i2++)
{
string fieldname=this.dataGrid1[i1,i2].ToString().Trim();
switch(fieldname)
{
case "姓名":namefield=i2; senderfeild++;break;
case "Email":emailfield=i2;senderfeild++;break;
case "银行卡号":bankcardfield=i2;senderfeild++;break; }
}
if(senderfeild==3)
{
line=i1;
break;
}
}
if(i1==numrows)
{
MessageBox.Show("对不起,您不能发送email!!!\n\n 提示:\n 导入数据库必须包含关键字段（姓名，Email， 银行卡号）！！\n 请重新导入数据库");
return;
}
for(int i3=0;i3<=line;i3++)
{
if(this.dataGrid1.IsSelected(i3))
{
MessageBox.Show("此行非数据行，请选择数据行(从"+(line+2)+"行起)！！！");
return;
}
}
ArrayList eml = new ArrayList();
eml.Clear();
int num=((DataSet)(this.dataGrid1.DataSource)).Tables[0].Rows.Count;
for(int i=line+1;i<num;i++)
{
if(this.dataGrid1.IsSelected(i))
{
string name=this.dataGrid1[i,namefield].ToString().Trim();
string email=this.dataGrid1[i,emailfield].ToString().Trim();
string ID=this.dataGrid1[i,bankcardfield].ToString().Trim();
try
{
if(dbCon==null) dbCon = MainForm.getConnection();
OleDbCommand cmd = dbCon.CreateCommand();
cmd.CommandText = "select Email from employee where 姓名='"+name+"' and Email='"+email+"' and 银行卡号='"+ID+"' and 状态=0";
OleDbDataReader reader = cmd.ExecuteReader();
int readerflag=0;
while(reader.Read())
{
eml.Add(reader.GetString(0));
readerflag=1;
}
if(readerflag==0)
{
MessageBox.Show(" Email发送失败!!!\n\n 第"+i+"行记录数据(姓名:"+name+",Email:"+email+",银行卡号:"+ID+") 可能有误\n 或 该库信息已被删除 或 信息有误\n\n 请修正!!!");
reader.Close();
cmd.Dispose();
return;
}
reader.Close();
cmd.Dispose();
}
catch(Exception ee)
{
MessageBox.Show(ee.ToString());
}
}
}
salary.EmailSender emailsender=new salary.EmailSender(eml,this.dataGrid1);
emailsender.ShowDialog();
}
private void menuItem2_Click(object sender, System.EventArgs e)
{
System.Data.DataSet ds = (System.Data.DataSet)this.dataGrid1.DataSource;
if(ds==null|| ds.Tables.Count<=0)
{
MessageBox.Show("您没查询或导入数据表，不能修改！！！\n 请进行查询或导入数据表！！");
return;
}
int countfields=((System.Data.DataSet)this.dataGrid1.DataSource).Tables[0].Columns.Count;
int numrows=((DataSet)(this.dataGrid1.DataSource)).Tables[0].Rows.Count;
if(numrows<2)
{
MessageBox.Show("对不起,您不能修改!!!\n\n 提示:\n 数据库不含数据行！！\n 请重新导入数据库");
return;
}
int namefield=0,emailfield=0,bankcardfield=0;
int line=0;
if(numrows>=6) numrows=6;
int i1=0;
for(i1=0;i1<numrows;i1++)
{
int senderfeild=0;
for(int i2=0;i2<countfields;i2++)
{
string fieldname=this.dataGrid1[i1,i2].ToString().Trim();
switch(fieldname)
{
case "姓名":namefield=i2; senderfeild++;break;
case "Email":emailfield=i2;senderfeild++;break;
case "银行卡号":bankcardfield=i2;senderfeild++;break;
}
}
if(senderfeild==3)
{
line=i1;
break;
}
}
if(i1==numrows)
{
MessageBox.Show("对不起,您不能修改!!!\n\n 提示:\n 数据库必须包含关键字段（姓名，Email， 银行卡号）！！\n 请重新导入数据库");
return;
}
for(int i3=0;i3<=line;i3++)
{
if(this.dataGrid1.IsSelected(i3))
{
MessageBox.Show("此行非数据行，请选择数据行(从"+(line+2)+"行起)！！！");
return;
}
}
HrSalary.salary.SalaryModify salarmodify=new HrSalary.salary.SalaryModify(this.dataGrid1,namefield,emailfield,bankcardfield);
salarmodify.Owner=this;
salarmodify.ShowDialog();
}
private void button2_Click(object sender, System.EventArgs e)
{
System.Data.DataSet ds = (System.Data.DataSet)this.dataGrid1.DataSource;
if(ds==null|| ds.Tables.Count<=0)
{
MessageBox.Show("您没查询或导入数据表，不能保存入库l！！！\n 请进行查询或导入数据表！！");
return;
}
DialogResult result=MessageBox.Show ("您将覆盖现有数据，确实要保存吗? ", "保存提示框",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if(result== DialogResult.No )
return;
string error="";
try
{
if(dbCon==null) dbCon = MainForm.getConnection();
OleDbCommand cmd = dbCon.CreateCommand();
OleDbTransaction dbTrans = dbCon.BeginTransaction();
cmd.Transaction = dbTrans;
bool onError = false;
try
{
cmd.CommandText ="delete * from tmp_table";// "truncate table mydatatable";
int count=cmd.ExecuteNonQuery();
countDgRows = ((DataSet)(this.dataGrid1.DataSource)).Tables[0].Rows.Count;
countDgColumns = ((DataSet)(this.dataGrid1.DataSource)).Tables[0].Columns.Count;
for(int i=0;i<countDgRows;i++)
{
string tablefield="";// sql= "insert into tmp_table(A) values('";
string sql,sql0="";
int j=0;
string A="";
sql0+=i.ToString ()+"','";
for(j=1;j<countDgColumns;j++)
{
A="A"+j.ToString();
tablefield+=A+",";
sql0+=this.dataGrid1[i,j-1]+"','";
}
if(j==countDgColumns)
{
A="A"+j.ToString();
tablefield+=A;
sql0+=this.dataGrid1[i,j-1];//sql+=this.dataGrid1[i,0]+"')";
}
sql="insert into tmp_table(A0,"+tablefield+")"+" values('"+ sql0+"')";
cmd.CommandText=sql;
int rs = cmd.ExecuteNonQuery();
if(rs!=1)
{
onError = true;
break;
}
}
}
catch(Exception ee)
{
error=ee.ToString();
onError = true;
}
if(onError)
{
dbTrans.Rollback();
MessageBox.Show("保存数据失败\n"+error);
}
else
{
dbTrans.Commit();
MessageBox.Show("保存数据成功");
}
try
{
cmd.Dispose();
}
catch(Exception){}
}
catch(Exception eee)
{
MessageBox.Show(eee.ToString());
}
}
private string number_Input_Check(string caption,string text,int maxvalue)
{
string error="";
text=text.Trim();
if(text=="" )
{
error=caption+"不能为空！！\n";
return(error);
}
int i=0;
for(i=0;i<text.Length;i++)
{
if(text[i]>'9' || text[i]<'0')
break;
}
if(i<text.Length)
{
error=caption+"输入格式不正确，请修正！！！\n";
return(error);
}
//int maxvalue=65535;
// MessageBox.Show( pid.CompareTo(j.ToString()).ToString());
if(text.Length>maxvalue.ToString().Length || ( (text.Length==maxvalue.ToString().Length ) && text.CompareTo(maxvalue.ToString())>0 ))
{
error=caption+" 输入值溢出(最大值：65535)，请修正！！！\n";
return(error);
}
//MessageBox.Show(i.ToString()+":"+pid.Length.ToString());
return(error);
}
private int find_fieldNum(string sql)
{
int i=0;
try
{
if(dbCon==null) dbCon = MainForm.getConnection();
OleDbCommand cmd = dbCon.CreateCommand();
cmd.CommandText =sql;
OleDbDataReader reader = cmd.ExecuteReader();
if(reader.Read())
{
for(i=0;i<51;i++)
{
if(reader.GetValue(i).ToString().Trim().Equals(""))
break;
}
}
reader.Close();
cmd.Dispose();
}
catch(Exception e)
{
MessageBox.Show(e.ToString());
}
return(i-1);
}
private void queryCheck(string sql,int countfield)
{
if(this.radioButton2.Checked)
{
if(this.textBox3.Text.Equals("") || this.textBox3.Text=="")
{
MessageBox.Show("您没输入姓名，请重新输入");
this.textBox3.Focus();
return;
}
if(!find_namefield(sql,countfield))
{
MessageBox.Show("数据库不含\"姓名\"字段,您不能按姓名查询！！！");
return;
}
sql+= " where A0='0' or A"+this.namecolumn.ToString()+"='姓名' or A" +this.namecolumn.ToString()+" like '%"+this.textBox3.Text+"%'";
}
showData(sql);
}
public void showData(string sql)
{
//MessageBox.Show(sql);
try
{
if(dbCon==null) dbCon = MainForm.getConnection();
dbAdapter = new OleDbDataAdapter(sql , dbCon);
dbAdapter.TableMappings.Add("Table","employee");
ds=new DataSet("employee");
ds.Clear();
dbAdapter.Fill(ds);
this.dataGrid1.SetDataBinding(ds,"employee");
this.totalNumber.Text =((int)(ds.Tables["employee"].Rows.Count-1)).ToString();
}
catch(Exception ee)
{
MessageBox.Show(ee.ToString());
}
}
private bool find_namefield(string sql,int countfield)
{
bool namefieldflag=false;
if(dbCon==null) dbCon = MainForm.getConnection();
OleDbCommand cmd = dbCon.CreateCommand();
cmd.CommandText =sql;
OleDbDataReader reader = cmd.ExecuteReader();
int line = 0;
try
{
while(reader.Read())
{
if(line>6) break;
for(int i=0;i<countfield;i++)
{
if(reader.GetString(i).Equals("姓名"))
{
namefieldflag=true;
this.namecolumn=i+1;
this.namerow=line;
break;
}
}
line++;
}
}
catch(Exception ee)
{
MessageBox.Show(ee.ToString());
}
reader.Close();
cmd.Dispose();
return namefieldflag;
}
private void button4_Click(object sender, System.EventArgs e)
{
string sql0 = "select * from tmp_table where A0='0' ";
int countfield=find_fieldNum(sql0);
if(countfield<1)
{
MessageBox.Show("库中为空表,请导入并保存数据表!!!");
return;
}
string sql = "select ";
string A="";
string tablefield="";
int i=1;
for(i=1;i<countfield;i++)
{
A="A"+i.ToString();
tablefield+=A+",";
}
tablefield+="A"+i.ToString();
sql +=tablefield+" from tmp_table";
queryCheck(sql,countfield);
}
private void QueryEmployeeSalary_Load(object sender, System.EventArgs e)
{
}
private void contextMenu1_Popup(object sender, System.EventArgs e)
{
this.menuItem1.Visible=true;
this.menuItem2.Visible=true;
}
private void button5_Click(object sender, System.EventArgs e)
{
System.Data.DataSet ds = (System.Data.DataSet)this.dataGrid1.DataSource;
if(ds==null|| ds.Tables.Count<=0)
{
MessageBox.Show("您没查询或导入数据表，不能导出Excel！！！\n 请进行查询或导入数据表！！");
return;
}
Excel.Application ExcelObj = null;
ExcelObj = new Excel.Application();
ExcelObj.Visible =false;
SaveFileDialog sf = new SaveFileDialog();
HrSalary.util.GridUtil gridutil=new HrSalary.util.GridUtil();
gridutil.GridXls(sf,this.dataGrid1,ExcelObj);
ExcelObj.Quit();
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -