private void button_insert_200_Click(object sender, EventArgs e) { string SQL = null; string sno, sname, ssex, sbirthday, tc; Random ran = new Random(); int SuccessCount = 0; while (SuccessCount < 200) { sno = ran.Next(100000, 999999).ToString(); sname = "TEST" + SuccessCount.ToString(); if (ran.Next(0, 1) == 0) { ssex = "男"; } else { ssex = "女"; } sbirthday = DateTime.Today.ToShortDateString().Trim(); tc = ran.Next(0, 100).ToString(); SQL = string.Format(@"INSERT INTO {0} (SNO,SNAME,SSEX,SBIRTHDAY,TC) values ('{1}','{2}','{3}',to_date('{4}','YYYY/MM/DD'),'{5}')", DB_TABLE, sno, sname, ssex, sbirthday, tc); if (OracleHelper.ExecuteSQL(SQL) > 0) { SuccessCount++; } } MessageBox.Show("SQL insert 200 times done."); this.DialogResult = DialogResult.OK; }
private void Button_confirm_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(textbox_sno.Text) || string.IsNullOrEmpty(textbox_sname.Text) || string.IsNullOrEmpty(combobox_ssex.Text)) { MessageBox.Show("请检查填写"); return; } Thread.CurrentThread.CurrentCulture = new CultureInfo("zh-cn"); string sno = textbox_sno.Text.Trim(); string sname = textbox_sname.Text.Trim(); string ssex = combobox_ssex.Text.Trim(); string sbirthday = dtpicker_sbirthday.Value.ToShortDateString().Trim(); string tc = textbox_tc.Text.Trim(); string SQL = null; if (isEdit) { if (string.IsNullOrEmpty(old_tc)) { SQL = string.Format(@"UPDATE {0} SET SNO='{1}',SNAME='{2}',SSEX='{3}',SBIRTHDAY=to_date('{4}','YYYY/MM/DD'),TC={5} WHERE SNO='{6}' AND SNAME='{7}' AND SSEX='{8}'", DB_TABLE, sno, sname, ssex, sbirthday, tc, old_sno, old_sname, old_ssex); } else { SQL = string.Format(@"UPDATE {0} SET SNO='{1}',SNAME='{2}',SSEX='{3}',SBIRTHDAY=to_date('{4}','YYYY/MM/DD'),TC={5} WHERE SNO='{6}' AND SNAME='{7}' AND SSEX='{8}' AND TC='{9}'", DB_TABLE, sno, sname, ssex, sbirthday, tc, old_sno, old_sname, old_ssex, old_tc); } } if (isInsert) { if (string.IsNullOrEmpty(tc)) { SQL = string.Format(@"INSERT INTO {0} (SNO,SNAME,SSEX,SBIRTHDAY) values ('{1}','{2}','{3}',to_date('{4}','YYYY/MM/DD'))", DB_TABLE, sno, sname, ssex, sbirthday); } else { SQL = string.Format(@"INSERT INTO {0} (SNO,SNAME,SSEX,SBIRTHDAY,TC) values ('{1}','{2}','{3}',to_date('{4}','YYYY/MM/DD'),'{5}')", DB_TABLE, sno, sname, ssex, sbirthday, tc); } } Console.WriteLine(SQL); MessageBox.Show("SQL:\n[" + SQL + "]", "DEBUG"); if (OracleHelper.ExecuteSQL(SQL) > 0) { this.DialogResult = DialogResult.OK; } else { this.DialogResult = DialogResult.Abort; } }
private void button_excel2db_Click(object sender, EventArgs e) { button_excel2db.Enabled = false; SetStatus("DOING", false); string filePath = null; using (OpenFileDialog ofd = new OpenFileDialog()) { ofd.Filter = "xlsx文件|*.xlsx|xls文件|*.xls"; ofd.RestoreDirectory = true; ofd.FilterIndex = 0; if (ofd.ShowDialog() == DialogResult.OK) { filePath = ofd.FileName; MessageBox.Show(filePath); } else { SetStatus("CANCELED", false); return; } } DataTable dtResult = null; int totalSheet = 0; //No of sheets on excel file using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';")) { objConn.Open(); OleDbCommand cmd = new OleDbCommand(); OleDbDataAdapter oleda = new OleDbDataAdapter(); DataSet ds = new DataSet(); DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetName = string.Empty; if (dt != null) { var tempDataTable = (from dataRow in dt.AsEnumerable() where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase") select dataRow).CopyToDataTable(); dt = tempDataTable; totalSheet = dt.Rows.Count; sheetName = dt.Rows[0]["TABLE_NAME"].ToString(); } cmd.Connection = objConn; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM [" + sheetName + "]"; oleda = new OleDbDataAdapter(cmd); oleda.Fill(ds, "excelData"); dtResult = ds.Tables["excelData"]; objConn.Close(); } string SQL = null; int SuccessCount = 0; foreach (DataRow row in dtResult.Rows) { SQL = string.Format(@"INSERT INTO {0} (SNO,SNAME,SSEX,SBIRTHDAY,TC) values ('{1}','{2}','{3}',to_date('{4}','YYYY/MM/DD'),'{5}')", textBox_DATABASE.Text + '.' + textBox_TABLE.Text, row["SNO"].ToString(), row["SNAME"].ToString(), row["SSEX"].ToString(), row["SBIRTHDAY"].ToString(), row["TC"].ToString()); if (OracleHelper.ExecuteSQL(SQL) > 0) { SuccessCount++; } } MessageBox.Show("Insert done\ntimes = " + SuccessCount); SetStatus("OK", true); button_excel2db.Enabled = true; }
private void DataGridView_CellClick(object sender, DataGridViewCellEventArgs e) { //MessageBox.Show("mod\nrow=" + e.RowIndex + "\ncol=" + e.ColumnIndex); if (e.ColumnIndex == 5) { //修改 //MessageBox.Show("mod\nrow=" + e.RowIndex); string sno = dataGridView.Rows[e.RowIndex].Cells[0].Value.ToString(); string sname = dataGridView.Rows[e.RowIndex].Cells[1].Value.ToString(); string ssex = dataGridView.Rows[e.RowIndex].Cells[2].Value.ToString(); string sbirthday = dataGridView.Rows[e.RowIndex].Cells[3].Value.ToString(); string tc = dataGridView.Rows[e.RowIndex].Cells[4].Value.ToString(); SetStatus("DOING", false); DataForm df = new DataForm(sno, sname, ssex, sbirthday, tc, textBox_DATABASE.Text + '.' + textBox_TABLE.Text, 1); switch (df.ShowDialog(this)) { case DialogResult.OK: SetStatus("SUCCESS", true); re_dataGridView(); break; case DialogResult.Cancel: SetStatus("CANCELED", false); break; default: SetStatus("ERROR", false); break; } } if (e.ColumnIndex == 6) { //删除 //MessageBox.Show("del\nrow="+e.RowIndex); string sno = dataGridView.Rows[e.RowIndex].Cells[0].Value.ToString(); string sname = dataGridView.Rows[e.RowIndex].Cells[1].Value.ToString(); string ssex = dataGridView.Rows[e.RowIndex].Cells[2].Value.ToString(); string sbirthday = dataGridView.Rows[e.RowIndex].Cells[3].Value.ToString(); string SQL = string.Format(@"DELETE FROM {0}.{1} WHERE SNO='{2}' AND SNAME='{3}' AND SSEX='{4}'", textBox_DATABASE.Text.Trim(), textBox_TABLE.Text.Trim(), sno, sname, ssex); //MessageBox.Show("SQL:\n[" + SQL + "]", "DEBUG"); SetStatus("DOING", false); DialogResult dr = new DialogResult(); dr = MessageBox.Show("SQL:\n[" + SQL + "]\n\n请确认是否删除", "WARNING", MessageBoxButtons.YesNo); if (dr != DialogResult.Yes) { MessageBox.Show("操作已取消"); SetStatus("CANCELED", false); return; } if (OracleHelper.ExecuteSQL(SQL) > 0) { SetStatus("SUCCESS", true); re_dataGridView(); } else { SetStatus("ERROR/NOT FOUND", false); } } }