C#使用Oracle.ManagedDataAccess操作oracle数据库

准备工作

一、准备好需要连接的数据库

CREATE TABLE StuClass(
       Class_Id int primary key not null,
       Class_Name varchar2(50)
)
CREATE TABLE Students(
       Stu_Id int primary key not null,
       Stu_Name varchar(20),
       --Stu_Sex varchar(4) check(Stu_Sex="男" or Stu_Sex="女"),
       Stu_Sex varchar(4),
       Stu_Birthday date null,
       Class_Id numeric(10),
       constraint fk_column foreign key(Class_Id)  --外键
       references StuClass(Class_Id)
       on delete cascade
)

INSERT INTO StuClass (Class_Id,Class_Name) VALUES(101,'高一（1）班')
INSERT INTO StuClass (Class_Id,Class_Name) VALUES(102,'高一（2）班')

INSERT INTO Students (Stu_Name,Stu_Sex,Stu_Birthday,Class_Id) VALUES(1,'孙七','男',to_date ( '2008-07-02' , 'YYYY-MM-DD HH24:MI:SS'),102)
INSERT INTO Students (Stu_Name,Stu_Sex,Stu_Birthday,Class_Id) VALUES(2,'张三','女',TO_DATE('2019-07-02 15:31:34','YYYY-MM-DD HH24:MI:SS'),101)
INSERT INTO Students (Stu_Id,Stu_Name,Stu_Sex,Stu_Birthday,Class_Id) VALUES(3,'赵六','男',to_date ( '1998-08-03 15:31:34' , 'YYYY-MM-DD HH24:MI:SS'),102)

二、引用Oracle.ManagedDataAccess的dll包，这里使用NuGet安装

搜索Oracle.ManagedDataAccess，点击右侧得安装

下载完成之后看一下依赖中是否有dll

包安装完成之后咱们就可以来写代码啦

新建一个DataBases基础类，用来访问数据库

class DataBases
    {
        OracleConnection conn;
        public DataBases()//初始化数据库连接字符串
        {
        //数据源字符串其中localhost是你oracle数据库的机器ip,port是你oracle数据库的端口号,IRON是你的用户名
            string connString = (@"DATA SOURCE= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl)));PASSWORD=IRON123;USER ID=IRON");
            conn = new OracleConnection(connString);
        }
        public OracleDataReader getShow(string sql)
        {
            conn.Open();
            OracleCommand cmd = new OracleCommand(sql, conn);
            OracleDataReader rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return rd;
        }
        /// <summary>
        /// 查询操作  方法跟ADO.NET的操作基本一样
        /// </summary>
        /// <param name="sql">要执行的sql脚本</param>
        /// <returns></returns>
        public DataSet Queues(string sql)
        {
            conn.Open();
            OracleCommand cmd = new OracleCommand(sql, conn);
            OracleDataAdapter dap = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            dap.Fill(ds);
            dap.Dispose();
            //int sdr = cmd.ExecuteNonQuery();
            conn.Close();
            return ds;
        }
        /// <summary>
        /// 添加方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Inserts(string sql)
        {
            conn.Open();
            OracleCommand cmd = new OracleCommand(sql, conn);
            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
    }

基本类完成后咱们就可以通过这个类来访问到数据库啦，接着写个测试，操作一下数据库

class Program
    {
        static DataBases db = new DataBases();
        static void Main(string[] args)
        {
            try
            {
                while (true)
                {
                Console.WriteLine("输入操作：1/添加,2/查询，c/重新输入，e/退出");
                    string caozuo = Console.ReadLine();
                    switch (caozuo)
                    {
                        case "1":
                            Console.WriteLine("添加,按c查重新选择操作");
                            string strAdd = Console.ReadLine();
                            if (strAdd=="c")
                            {
                                break;
                            }
                            else
                            {
                                Console.Write("编号："); int Stu_Id = int.Parse(Console.ReadLine());
                                Console.Write("姓名："); string Stu_Name = Console.ReadLine();
                                Console.Write("性别："); string Stu_Sex = Console.ReadLine();
                                Console.Write("生日："); DateTime Stu_Birthday = Convert.ToDateTime(Console.ReadLine());
                                Console.Write("班级编号："); int Class_Id = int.Parse(Console.ReadLine());
                                string sqladd=string.Format(@"INSERT INTO Students (Stu_Id,Stu_Name,Stu_Sex,Stu_Birthday,Class_Id) 
VALUES("+ Stu_Id + ",'"+ Stu_Name + "','"+ Stu_Sex + "',TO_DATE('" + Stu_Birthday + "','YYYY-MM-DD HH24:MI:SS')," + Class_Id + ")");
                                int i = db.Inserts(sqladd);
                                if (i > 0)
                                {
                                    Console.WriteLine("添加成功！");
                                }
                                else
                                {
                                    Console.WriteLine("添加失败！");
                                }
                                break;
                            }
                            case "2":
                            string sql = "";
                            Console.WriteLine("按1查询全部，按2地址查询,按c查重新选择操作");
                            string qu = Console.ReadLine();
                            if (qu == "1")
                            {
                                sql = string.Format("select * from Students");
                            }
                            else if (qu == "2")
                            {
                                Console.WriteLine("请输入地址：");
                                string str = Console.ReadLine();
                                sql = "select * from t_curriculum t join UserInfo u on t.stuid=u.userid where address like '%" + str + "%'";
                            }
                            else if (qu=="c")
                            {
                                break;
                            }
                            DataTable dt = db.Queues(sql).Tables[0];
                            foreach (DataRow item in dt.Rows)
                            {
                                string strResult = item["stu_id"] + "\t" + item["stu_name"] + "\t" + item["stu_sex"] + "\t" + item["stu_birthday"] + "\t" + item["class_id"];
                                Console.WriteLine(strResult);
                            }
                            break;
                        case "c":
                            Console.Clear();
                            continue;
                        case "e":
                            return;
                        default:
                            Console.WriteLine("请输入正确的操作！");
                            Console.Clear();
                            break;
                    }
                    }
            }            
             catch (Exception e)
            {
                Console.WriteLine("错误：" + e.Message);
                throw;
            }
        }
   }

IronMan1024