?using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
//using System.Data.OracleClient;
using System.Configuration;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;


namespace ConnetOracleTest
{
    public partial class frmOracleTest : Form
    {
        private readonly string oracleConnectiongString = string.Empty;
        private bool checkBoxChange = true;
        public frmOracleTest()
        {
            InitializeComponent();
            oracleConnectiongString = ConfigurationManager.AppSettings["OracleConnectionString"].ToString().Trim();
        }
        private void frmOracleTest_Load(object sender, EventArgs e)
        {     
            this.AcceptButton = this.btnExecute;
            this.txtSql.Focus();
        }
        private void btnExecute_Click(object sender, EventArgs e)
        {  
            string longSql = this.txtSql.Text.Trim();
            if (String.IsNullOrEmpty(longSql))
            {
                MessageBox.Show("Sql語(yǔ)句不能為空.");
                return;
            }
            string[] sqls=longSql.Split(new char[]{';'});
            try
            {
                this.Cursor = Cursors.WaitCursor;
                ExecuteInsertOrUpdateSql(sqls);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.Substring(ex.Message.LastIndexOf(":") + 1));
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }
        private void ExecuteInsertOrUpdateSql(String[] oraSqls)
        {
            
            using ( OracleConnection oraConn = new OracleConnection(oracleConnectiongString))
            {
                oraConn.Open();
                OracleTransaction oraTr = oraConn.BeginTransaction(IsolationLevel.ReadCommitted);
                OracleCommand oraComm = oraConn.CreateCommand();
                try
                {
                    for (int i = 0; i < oraSqls.Length; i++)
                    {
                        oraComm.CommandText = oraSqls[i];
                        oraComm.ExecuteNonQuery();
                    }
                    oraTr.Commit();
                }
                catch (Exception ex)
                {
                   oraTr.Rollback();
                   throw new Exception(ex.Message);
                }
            }
        }
        private DataTable SearchSql(String sql)
        {
            DataTable dtData = new DataTable();
            using (OracleConnection oraConn = new OracleConnection(oracleConnectiongString))
            {
                OracleCommand oraComm = new OracleCommand(sql, oraConn);    
                OracleDataAdapter orada = new OracleDataAdapter(oraComm);
                try
                {
                    oraComm.Connection.Open();
                    orada.Fill(dtData);       
                }
                catch (OracleException ox)
                {
                    switch (ox.Number)
                    {
                        case 1:
                            throw new Exception("Error attempting to insert duplicate data.");
                            break;
                        case 942:
                            throw new Exception("表或視圖不存在");
                            break;
                        case 12545:
                            throw new Exception("The database is unavailable.");
                            break;
                        default:
                            throw new Exception("Database error:" + ox.Message.Substring(ox.Message.LastIndexOf(":")+1));
                            break;

                    }
                }
                catch (Exception ex)
                {
                   throw new Exception(ex.Message.ToString());
                }
                finally 
                {
                    oraComm.Connection.Close();
                }

            }
            return dtData;
        }
        private DataTable UpdateSql(DataTable updatedt,String sql)
        {
            DataTable dtData = new DataTable();
            using (OracleConnection oraConn = new OracleConnection(oracleConnectiongString))
            {
                OracleCommand oraComm = new OracleCommand(sql, oraConn);
                OracleDataAdapter orada = new OracleDataAdapter(oraComm);
                OracleCommandBuilder oracb = new OracleCommandBuilder(orada);
                try
                { 
                    oraComm.Connection.Open();
                    orada.Update(updatedt);
                }
                catch (OracleException ox)
                {
                    switch (ox.Number)
                    {
                        case 1:
                            throw new Exception("Error attempting to insert duplicate data.");
                            break;
                        case 942:
                            throw new Exception("表或視圖不存在");
                            break;
                        case 12545:
                            throw new Exception("The database is unavailable.");
                            break;
                        default:
                            throw new Exception("Database error:" + ox.Message.Substring(ox.Message.LastIndexOf(":") + 1));
                            break;

                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message.ToString());
                }
                finally
                {
                    oraComm.Connection.Close();
                }

            }
            return dtData;
        }
        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
               
                this.Cursor = Cursors.WaitCursor;
                string sql = this.txtSql.Text.Trim();
                if (String.IsNullOrEmpty(sql) || !sql.Contains("select"))
                {
                    MessageBox.Show("Sql語(yǔ)句為空或查詢語(yǔ)句有誤。");
                    return;
                }
                BindingSource bindSrc = new BindingSource();
                bindSrc.Clear();
                string dtime1 = DateTime.Now.Second + ":" + DateTime.Now.Millisecond;////////
                bindSrc.DataSource = SearchSql(sql);
                string dtime2 = "--" + DateTime.Now.Second + ":" + DateTime.Now.Millisecond;///////////
               
                this.dgvData.DataSource = bindSrc;
   
                string dtime3 = "--" + DateTime.Now.Second + ":" + DateTime.Now.Millisecond;/////////
                bindSrc.AllowNew = false;
                this.dgvData.ReadOnly = false;
                foreach (DataGridViewColumn col in this.dgvData.Columns)
                {
                    if (dgvData.Columns[0].Equals(col))
                        col.ReadOnly = false;
                    else col.ReadOnly = true;
                }
                string dtime4 = "--" + DateTime.Now.Second + ":" + DateTime.Now.Millisecond;/////////
                MessageBox.Show(dtime1 + dtime2 + dtime3+dtime4);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
            finally 
            {
                this.Cursor = Cursors.Default;
            }
        }

        private void txtSql_TextChanged(object sender, EventArgs e)
        {
            if (this.txtSql.Text.StartsWith("select"))
            {
                this.AcceptButton = this.btnSearch;
            }
        }

        private void btnupdate_Click(object sender, EventArgs e)
        {
            try
            {
                this.Cursor = Cursors.WaitCursor;
                BindingSource operBs = (BindingSource)this.dgvData.DataSource;
                UpdateSql((DataTable)operBs.DataSource,this.txtSql.Text.Trim());
                operBs.AllowNew=false;
                this.dgvData.ReadOnly = false;
                foreach (DataGridViewColumn col in this.dgvData.Columns)
                {
                    if (dgvData.Columns[0].Equals(col))
                        col.ReadOnly = false;
                    else col.ReadOnly = true;
                }
                MessageBox.Show("更新完成");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            BindingSource operBs=(BindingSource)this.dgvData.DataSource;
            string dtime1 = DateTime.Now.Second + ":" + DateTime.Now.Millisecond;////////
            List<string> deleteRows = new List<string>();
            foreach (DataGridViewRow row in this.dgvData.Rows)
            {
                DataGridViewCheckBoxCell cbCell = (DataGridViewCheckBoxCell)row.Cells[0];
                if ((bool)cbCell.FormattedValue)
                {
                   // operBs.Remove(row.DataBoundItem);
                    deleteRows.Add(row.Cells["id"].Value.ToString());
                }
            }
            string dtime2 = "--"+DateTime.Now.Second + ":" + DateTime.Now.Millisecond;////////
            if (deleteRows.Count == operBs.List.Count)
            {
                operBs.DataSource = null;
            }
            else
            {
                foreach (string del in deleteRows)
                {
                    int index = operBs.Find("id", del);
                    if (index > -1)
                        operBs.RemoveAt(index);
                }
            }
            string dtime3 = "--" + DateTime.Now.Second + ":" + DateTime.Now.Millisecond;////////
            MessageBox.Show(dtime1 + dtime2+dtime3);
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            BindingSource operBs = (BindingSource)this.dgvData.DataSource;
            operBs.AllowNew = true;
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            this.dgvData.ReadOnly = false;
            foreach (DataGridViewColumn col in this.dgvData.Columns)
            {
                    col.ReadOnly = false;       
            }
        }

        private void dgvData_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
             DataGridView dgv = (DataGridView)sender;
             using (SolidBrush b = new SolidBrush(dgv.RowHeadersDefaultCellStyle.ForeColor))
             {  
                 if (e.RowIndex < 100)
                     e.Graphics.DrawString(e.RowIndex.ToString(System.Globalization.CultureInfo.CurrentUICulture), e.InheritedRowStyle.Font, b, e.RowBounds.Location.X + dgv.RowHeadersWidth / 3, e.RowBounds.Location.Y + (e.RowBounds.Height - e.InheritedRowStyle.Font.GetHeight()) / 2);               
                 else  if(e.RowIndex<1000)
                     e.Graphics.DrawString(e.RowIndex.ToString(System.Globalization.CultureInfo.CurrentUICulture), e.InheritedRowStyle.Font, b,e.RowBounds.Location.X + dgv.RowHeadersWidth / 4, e.RowBounds.Location.Y + (e.RowBounds.Height - e.InheritedRowStyle.Font.GetHeight()) / 2);               
                 else
                     e.Graphics.DrawString(e.RowIndex.ToString(System.Globalization.CultureInfo.CurrentUICulture), e.InheritedRowStyle.Font, b, e.RowBounds.Location.X + dgv.RowHeadersWidth / 5, e.RowBounds.Location.Y + (e.RowBounds.Height - e.InheritedRowStyle.Font.GetHeight()) / 2);
                 
             }
        }

        private void cbSelectAll_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBoxChange)
            {
                if (((CheckBox)sender).Checked)
                {
                    foreach (DataGridViewRow row in this.dgvData.Rows)
                    {
                        row.Cells[0].Value = "True";
                    }
                }
                else
                {
                    foreach (DataGridViewRow row in this.dgvData.Rows)
                    {
                        row.Cells[0].Value = "False";
                    }
                }
            }
            else
            {
                checkBoxChange = true;
            }
        }

        private void dgvData_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            //if (e.ColumnIndex == 0 && this.cbSelectAll.Checked)
            //{
            //    if (((DataGridView)sender).Rows[e.RowIndex].Cells[e.ColumnIndex].FormattedValue.ToString().Equals("True"))
            //    {
            //        this.cbSelectAll.Checked = false;
            //        checkBoxChange = false;
            //    }
            //}
        }

        private void dgvData_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            this.cbSelectAll.Checked = false;
        }

        private void dgvData_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
           // dgvData_CellValueChanged(sender, e);
        }
      
    }
}