Beispiel #1
0
        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;
        }
Beispiel #2
0
        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);
                }
            }
        }