UltraGrid本地Oracle数据库增删改查

UltraGrid本地Oracle数据库增删改查

{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            DoQuery();
        }
        string con = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = orcl)));User ID=数据库名;password=密码";
        
        private void Form1_Load(object sender, EventArgs e)
        {
            
        }

        private void DoQuery()
        {
            OracleConnection conn = new OracleConnection(con);
            try
            {
                conn.Open();
                string x = ((Infragistics.Win.UltraWinToolbars.TextBoxTool)this.ultraToolbarsManager1.Toolbars[0].Tools["TextBoxTool1"]).Text;
                string sql = "select *from teacher where name=" + x;
                if (x == "")
                {
                    sql = "select *from teacher";
                }
                sql = sql + " order by id";
                OracleDataAdapter oda = new OracleDataAdapter(sql,conn);
                DataSet ds = new DataSet();
                oda.Fill(ds);
                ultraGrid1.DataSource = ds.Tables[0];
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        private void DoDel()
        {
            OracleConnection conn = new OracleConnection(con);
            try
            {
                UltraGridRow aa = ultraGrid1.ActiveRow;//获取行数
                string change = aa.Cells[0].Value.ToString().Trim();
                conn.Open();
               // string change = this.ultraGrid1.Rows[0].Cells[0].Value.ToString().Trim();
                MessageBox.Show(change);
                int id = int.Parse(change);
                string sql = "delete teacher where id=" + id;
                OracleCommand ocd = new OracleCommand(sql,conn);
                ocd.ExecuteNonQuery();
                MessageBox.Show("删除成功");
                DoQuery();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        private void DoUpdate()
        {
            OracleConnection conn = new OracleConnection(con);
            try
            {
                
                UltraGridRow bb = ultraGrid1.ActiveRow;
                string id = bb.Cells["ID"].Text.ToString().Trim();
                string name = bb.Cells["NAME"].Text.ToString().Trim();
                string age = bb.Cells["AGE"].Text.ToString().Trim();
                string time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                conn.Open();
                int a = int.Parse(age);
                string sql = "update teacher t set t.name='"+name+"',t.age='"+a+"',t.time=to_date('"+time+"','yyyy-MM-dd HH24:mi:ss')"+" where t.id='"+id+"'";
                MessageBox.Show(sql);
                OracleCommand cmd = new OracleCommand(sql, conn);
                cmd.ExecuteNonQuery();
                DoQuery();
            }catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        private void DoAdd()
        {
            OracleConnection conn = new OracleConnection(con);
            try
            {
                conn.Open();
                UltraGridRow ugr = ultraGrid1.ActiveRow;
                string id = ugr.Cells[0].Value.ToString().Trim();
                string id2 = "select max(ID) from teacher";
                string name = ugr.Cells[1].Value.ToString().Trim();
                string age = ugr.Cells[2].Value.ToString().Trim();
                string time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                int ID = int.Parse(id);
                string sql = "insert into teacher values(("+id2+")+1,'"+name+"',"+age+",to_date('"+time+"','yyyy-MM-dd HH24:mi:ss'"+"))";
                MessageBox.Show(sql);
                OracleCommand cmd = new OracleCommand(sql, conn);
                cmd.ExecuteNonQuery();
                DoQuery();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        private void ultraToolbarsManager1_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e)
        {
            switch (e.Tool.Key)
            {
                case "DoQuery":
                    DoQuery();
                    break;
                case "DoDel":
                    DoDel();
                    break;
                case "DoUpdate":
                    DoUpdate();
                    break;
                case "DoAdd":
                    DoAdd();
                    break;
            }
        }
    }

效果图如下