.NET连接、操作oracle数据库

.net连接oracle有三种方式，我采用的是使用微软自带的system.data.oracleclient

电脑上装了oracle11g client（x64）、和pl/sql，在安装的过程中也遇到了许多问题：

1、oracle安装，环境不满足最低要求

百度然后修改了两个.ora文件就好了，加上了win8的坏境

2、oracle安装，path过长无法自动配置环境变量

于是在path中删掉了一些没用的东西（比如已经卸载的软件的配置）

然后重启电脑！！！就好了，一定要重启！！！ 

3、pl/sql一直无法登陆

发现pl/sql装的是32位的，但是也有说不一定要统一，但我确实遇到了这个问题（大概是我菜）

 

然后就是开始连接数据库啦！

1、在引用中添加system.data.oracleclient;（直接右键添加引用就可以找到）

2、写上using System.Data.OracleClient;

public void ConnectDB()
        {
            OracleConnection conn = new OracleConnection("data source=**服务命名**;User Id=**用户名**;Password=**密码**;");
            try
            {
                conn.Open();
                string sql = "select * from atest_patient_info";
                OracleDataAdapter res = new OracleDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                res.Fill(dt);
                string a = null;
                for (int x = 0; x < dt.Rows.Count; x++)
                {
                    a += dt.Rows[x][1].ToString();
                    a += "\n";
                }
                MessageBox.Show(a);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }

然后是用于查找和删、改、增的函数（删、改、查用的是同一个函数）

private OracleConnection conn = new OracleConnection("data source=**服务命名**;User Id=**用户名**;Password=**密码**;"); 
public DataTable getPatientInfo(string sql)//查找
        {
            DataTable table = new DataTable();
            try
            {
                conn.Open();
                OracleDataAdapter res = new OracleDataAdapter(sql, conn);
                res.Fill(table);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                conn.Close();
            }
            return table;
        }

        public bool updataTables(string sql1, string sql2)//增、改、删
        {
            try
            {
                conn.Open();
                OracleCommand cmd1 = conn.CreateCommand();
                cmd1.CommandText = sql1;
                cmd1.ExecuteNonQuery();
                OracleCommand cmd2 = conn.CreateCommand();
                cmd2.CommandText = sql2;
                cmd2.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            finally
            {
                conn.Close();
            }
            return true;
        }

然后是调用函数的地方啦！

1、增加

        public void NewPatient()
        {
            string[] a = { IDText.Text, NameText.Text, SexBox.Text, AgeText.Text, NativeText.Text, ComplainsText.Text,
            PreComplaintText.Text, PreIllnessText.Text, PersonalHisText.Text, AdmittedTimeText.Text,RecordTimeText.Text};
            string sql1 = string.Format("insert into atest_patient_info values('{0}','{1}','{2}','{3}','{4}')",
                            a[0], a[1], a[2], a[3], a[4]);
            string sql2 = string.Format("insert into atest_admission_rec values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
                                        a[0], a[5], a[6], a[7], a[8], a[9], a[10]);
            bool check = db.updataTables(sql1, sql2);
            if(check) treeView1.Nodes.Add(IDText.Text, NameText.Text);
        }

2、更新数据

public void RefreshPatient()
        {
            string[] a = { IDText.Text, NameText.Text, SexBox.Text, AgeText.Text, NativeText.Text, ComplainsText.Text,
            PreComplaintText.Text, PreIllnessText.Text, PersonalHisText.Text, AdmittedTimeText.Text,RecordTimeText.Text};
            string sql1 = string.Format("update atest_patient_info set name='{0}',sex='{1}',age='{2}',native='{3}' where pid='{4}'",
                            a[1],a[2],a[3],a[4],a[0]);
            string sql2 = string.Format("update atest_admission_rec set complains='{0}',present='{1}',previous='{2}',personal='{3}'," +
                "intime='{4}',rectime='{5}' where pid='{6}'",a[5],a[6],a[7],a[8],a[9],a[10],a[0]);
            bool check = db.updataTables(sql1, sql2);
        }

3、删除数据

        public void DeletePatient()
        {
            //delete data from database    
            //**admission_rec has a foreign key references patient_info**
            //1、delete data from table admission_rec 
            //2、delete data from table patient_info
            string pid = IDText.Text;
            string sql1 = string.Format("delete from atest_admission_rec where pid='{0}'", pid);
            string sql2 = string.Format("delete from atest_patient_info where pid='{0}'", pid);
            bool check = db.updataTables(sql1, sql2);
            
            //delete from tree
            //if the sql is completed
            if(check) treeView1.SelectedNode.Remove();
        }

4、查找数据（返回数据存在datatable中）

public void SetBoxValue()
        {
            string sql = "select a.pid,a.name,a.sex,a.age,a.native,b.intime,b.rectime,b.complains,b.present,b.previous,b.personal from atest_patient_info a,atest_admission_rec b where a.pid=b.pid and a.pid='"+treeView1.SelectedNode.Name+"'";
            DataTable tb = db.getPatientInfo(sql);
            IDText.Text = tb.Rows[0][0].ToString();
            NameText.Text = tb.Rows[0][1].ToString();
            SexBox.Text = tb.Rows[0][2].ToString();
            AgeText.Text = tb.Rows[0][3].ToString();
            NativeText.Text = tb.Rows[0][4].ToString();
            AdmittedTimeText.Text = tb.Rows[0][5].ToString();
            RecordTimeText.Text = tb.Rows[0][6].ToString();
            ComplainsText.Text = tb.Rows[0][7].ToString();
            PreComplaintText.Text = tb.Rows[0][8].ToString();
            PreIllnessText.Text = tb.Rows[0][9].ToString();
            PersonalHisText.Text = tb.Rows[0][10].ToString();
        }

最后，感谢大神们！

参考链接：

    连接oracle数据库：https://www.cnblogs.com/mq0036/p/3678148.html
    读取数据：https://www.cnblogs.com/jiangxin/p/5546288.html
    增删改：https://blog.csdn.net/wulex/article/details/81007248