C#连接Oracle数据库详细版（2022版vs）

小哆不迷糊

已于 2023-10-05 16:50:42 修改

阅读量4.5k

点赞数 13

分类专栏： C# C++ 文章标签：数据库 c# oracle 开发语言 asp.net

于 2023-08-05 11:53:31 首次发布

本文链接：https://blog.csdn.net/qq_65316236/article/details/132116376

版权

C# 同时被 2 个专栏收录

2 篇文章 1 订阅

订阅专栏

C++

1 篇文章 0 订阅

订阅专栏

1、创建一个窗体

2、窗体上加上以下几个控件（容器，单选框，文本，文本框，按钮，数据）

左边控件实现增删改查操作，右边数据控件是为了显示数据

3、添加驱动（重点）

点击引用右键选中【管理NuGet程序包】。

点击浏览输入oracle进行搜索，选中第二个，进行安装。

4、为了代码更整洁我们再创建一个类（使用类的时候要引用）

以下是类里的代码，封装了两个方法，方法一实现：查询，读取数据，方法二实现：插入、删除、修改数据。（新手的小伙伴可以直接复制，记得把db变量改成自己的数据库信息）

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace WindowsFormsApp2.myclass
{
    public class oraclecalss
    {
        //连接数据库的信息
        private static string db = "自己的数据库信息";
        //查询oracle数据库内容返回数据集
        public DataTable oracleTable(string ora)
        {
            //链接数据库
            OracleConnection con = new OracleConnection(db);
            //把数据放入数据集
            DataTable dt = new DataTable();
            try
            {
                //连接数据库
                con.Open();
                //查询数据
                OracleDataAdapter da = new OracleDataAdapter(ora, con);
                //查询道德数据放入数据集里
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                //失败出现异常，原因在ex.message里。
            }
            finally
            {
                //断开数据库
                con.Close();
            }
            //把数据集返回出去
            return dt;
        }
        //修改 删除 添加在oracle数据库中 ，返回一个数值
        public int Execoraint(string ora)
        {

            //初始值等于0
            int row = 0;
            //连接数据库
            OracleConnection con = new OracleConnection(db);
            con.Open();
            try
            {
                //查询数据
                OracleCommand cod = new OracleCommand(ora, con);
                row = cod.ExecuteNonQuery();
            }
            catch (Exception ex) { }
            finally
            {
                con.Close();
            }
            return row;
        }
    }
}

5、在formoracle.cs窗体中引用此类实现增删改查操作

1、刚进入窗体就显示所有数据

        //引用类 
        //myclass是我所有类的文件夹名，没有嵌套文件夹的语法oraclecalss _ora = new oraclecalss();
        myclass.oraclecalss _ora = new myclass.oraclecalss();
        //我把读取所有数据封装成一个方法了，在需要全部数据时直接调用。
        private void getAllData()
        {
            //ora是Oracle语句
            string ora = "select emp_no,emp_name,emp_tel,emp_email from test.myuser";
            //dataGridView1是你使用数据控件的名字。
            dataGridView1.DataSource = _ora.oracleTable(ora);
        }
        //刚进入窗体时执行的方法。
        private void Formoracle_Load(object sender, EventArgs e)
        {
            this.Text = "Oracle 数据库";
            //进入窗体调用封装的方法显示所有数据。
            getAllData();

        }

1.1效果图

2、添加数据操作

        //button按钮不同文字对应不同逻辑
        private void button1_Click(object sender, EventArgs e)
        {
            //获取所有文本框的内容
            string empnostr = empno.Text.Trim();
            string empnamestr = empname.Text.Trim();
            string emptelstr = emptel.Text.Trim();
            string empemailstr = empemail.Text.Trim();
            //按钮文字是添加时
            if (button1.Text == "添加")
            {
                //文本框不能为空
                if(empemailstr == "" || emptelstr == "" || empnamestr == "" || empnostr == "")
                {
                    MessageBox.Show("内容不能为空！");
                    return;
                }
                string ora = "insert into test.myuser values(" + empnostr + ",'" + empnamestr + "'," + emptelstr + ",'" + empemailstr + "')";
                int row = _ora.Execoraint(ora);
                if(row > 0)
                {
                    MessageBox.Show("添加成功");
                    //添加成功文本框为空
                    empno.Text = "";
                    empname.Text = "";
                    emptel.Text = "";
                    empemail.Text = "";
                    //添加成功后数据实时更新
                    getAllData();
                }
                else
                {
                    MessageBox.Show("添加失败");
                }

                
            }

2.1效果图

确定后数据实时更新

3、查询数据操作

//按钮文字是查询时
else if (button1.Text == "查询")
            {
                string ora = "select emp_no,emp_name,emp_tel,emp_email from test.myuser where emp_no like '%"+ empnostr +"%'";
                dataGridView1.DataSource = _ora.oracleTable(ora);
            }

3.1效果图

4、修改数据操作（修改和删除需要数据回显）

4.1数据回显

private void dataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            //判断只有在删除和修改时才能回显
            if (button1.Text == "修改" || button1.Text == "删除")
            {
                //在表格点击事件外面获取单元格索引
                //int index = dataGridView1.CurrentRow.Index;
                //MessageBox.Show("" + index);
                //获取索引
                int index = e.RowIndex;
                if (index >= 0)
                {
                    empno.Text = dataGridView1.Rows[index].Cells[0].Value.ToString();
                    empname.Text = dataGridView1.Rows[index].Cells[1].Value.ToString();
                    emptel.Text = dataGridView1.Rows[index].Cells[2].Value.ToString();
                    empemail.Text = dataGridView1.Rows[index].Cells[3].Value.ToString();
                    
                }
                
            }
            else if (button1.Text == "添加" || button1.Text == "查询")
                {
                    empno.Text = "";
                    empname.Text = "";
                    emptel.Text = "";
                    empemail.Text = "";
                }
        }

4.2修改数据

//按钮文字是修改时 
else if (button1.Text == "修改")
            {
                if (empnostr == "")
                {
                    MessageBox.Show("请选择你要修改的内容！");
                    return;
                }
                string ora = "update test.myuser set emp_name ='" + empname.Text.Trim() + "',emp_tel = " + emptel.Text.Trim()+ ",emp_email ='" + empemail.Text.Trim() + "' where emp_no = " + empno.Text.Trim();
                int row = _ora.Execoraint(ora);
                if (row > 0)
                {
                    MessageBox.Show("修改成功");
                    empno.Text = "";
                    empname.Text = "";
                    emptel.Text = "";
                    empemail.Text = "";
                    getAllData();
                }
                else
                {
                    MessageBox.Show("修改失败");
                }
            }

4.3效果图

5、删除数据操作

5.1数据回显

和修改回显一样。

5.2删除数据

//按钮文字是删除时 
else if (button1.Text == "删除")
            {
                string ora = "delete test.myuser where  emp_no = " + empno.Text.Trim();
                int row = _ora.Execoraint(ora);
                if (row > 0)
                {
                    MessageBox.Show("删除成功");
                    empno.Text = "";
                    empname.Text = "";
                    emptel.Text = "";
                    empemail.Text = "";
                    getAllData();
                }
                else
                {
                    MessageBox.Show("删除失败");
                }

            }
        }

5.3效果图

一样工号是禁用，以防修改。

6、为了看起来更清晰，以下是formoracle.cs窗体的整体代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApp2.NewFolder1
{
    public partial class Formoracle : Form
    {
        public Formoracle()
        {
            InitializeComponent();
        }
        
        private void label1_Click(object sender, EventArgs e)
        {

        }
        //引用类
        myclass.oraclecalss _ora = new myclass.oraclecalss();
        private void getAllData()
        {
            string ora = "select emp_no,emp_name,emp_tel,emp_email from test.myuser";
            dataGridView1.DataSource = _ora.oracleTable(ora);
        }
        private void Formoracle_Load(object sender, EventArgs e)
        {
            this.Text = "Oracle 数据库";
            //进入页面显示数据
            getAllData();

        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            //单选按钮切换button按钮文字
            if (radioButton1.Checked == true)
            {
                radioButton2.Checked = false;
                radioButton4.Checked = false;
                radioButton3.Checked = false;
                button1.Text = "添加";
                empno.ReadOnly = false;

            }
            else if (radioButton2.Checked == true)
            {
                radioButton1.Checked = false;
                radioButton3.Checked = false;
                radioButton4.Checked = false;
                button1.Text = "查询";
                empno.ReadOnly = false;
            }
            else if (radioButton3.Checked == true)
            {
                radioButton1.Checked = false;
                radioButton4.Checked = false;
                radioButton2.Checked = false;
                button1.Text = "修改";
                empno.ReadOnly = true;
            }
            else if (radioButton4.Checked == true)
            {
                radioButton2.Checked = false;
                radioButton3.Checked = false;
                radioButton1.Checked = false;
                button1.Text = "删除";
                empno.ReadOnly = true;
            }
        }

        private void label4_Click(object sender, EventArgs e)
        {

        }

        private void label3_Click(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void splitContainer1_Panel2_Paint(object sender, PaintEventArgs e)
        {

        }

        private void splitContainer1_Panel1_Paint(object sender, PaintEventArgs e)
        {

        }
       //button按钮不同文字对应不同逻辑
        private void button1_Click(object sender, EventArgs e)
        {
            //获取所有文本框的内容
            string empnostr = empno.Text.Trim();
            string empnamestr = empname.Text.Trim();
            string emptelstr = emptel.Text.Trim();
            string empemailstr = empemail.Text.Trim();
            if (button1.Text == "添加")
            {
                if(empemailstr == "" || emptelstr == "" || empnamestr == "" || empnostr == "")
                {
                    MessageBox.Show("内容不能为空！");
                    return;
                }
                string ora = "insert into test.myuser values(" + empnostr + ",'" + empnamestr + "'," + emptelstr + ",'" + empemailstr + "')";
                int row = _ora.Execoraint(ora);
                if(row > 0)
                {
                    MessageBox.Show("添加成功");
                    empno.Text = "";
                    empname.Text = "";
                    emptel.Text = "";
                    empemail.Text = "";
                    getAllData();
                }
                else
                {
                    MessageBox.Show("添加失败");
                }

                
            }
            else if (button1.Text == "查询")
            {
                string ora = "select emp_no,emp_name,emp_tel,emp_email from test.myuser where emp_no like '%"+ empnostr +"%'";
                dataGridView1.DataSource = _ora.oracleTable(ora);
            }
            else if (button1.Text == "修改")
            {
                if (empnostr == "")
                {
                    MessageBox.Show("请选择你要修改的内容！");
                    return;
                }
                string ora = "update test.myuser set emp_name ='" + empname.Text.Trim() + "',emp_tel = " + emptel.Text.Trim()+ ",emp_email ='" + empemail.Text.Trim() + "' where emp_no = " + empno.Text.Trim();
                int row = _ora.Execoraint(ora);
                if (row > 0)
                {
                    MessageBox.Show("修改成功");
                    empno.Text = "";
                    empname.Text = "";
                    emptel.Text = "";
                    empemail.Text = "";
                    getAllData();
                }
                else
                {
                    MessageBox.Show("修改失败");
                }
            }
            else if (button1.Text == "删除")
            {
                string ora = "delete test.myuser where  emp_no = " + empno.Text.Trim();
                int row = _ora.Execoraint(ora);
                if (row > 0)
                {
                    MessageBox.Show("删除成功");
                    empno.Text = "";
                    empname.Text = "";
                    emptel.Text = "";
                    empemail.Text = "";
                    getAllData();
                }
                else
                {
                    MessageBox.Show("删除失败");
                }

            }
        }

        private void empname_TextChanged(object sender, EventArgs e)
        {

        }

        private void empemail_TextChanged(object sender, EventArgs e)
        {

        }

        private void empno_TextChanged(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            //判断只有在删除和修改时才能回显
            if (button1.Text == "修改" || button1.Text == "删除")
            {
                //在表格点击事件外面获取单元格索引
                //int index = dataGridView1.CurrentRow.Index;
                //MessageBox.Show("" + index);
                //获取索引
                int index = e.RowIndex;
                if (index >= 0)
                {
                    empno.Text = dataGridView1.Rows[index].Cells[0].Value.ToString();
                    empname.Text = dataGridView1.Rows[index].Cells[1].Value.ToString();
                    emptel.Text = dataGridView1.Rows[index].Cells[2].Value.ToString();
                    empemail.Text = dataGridView1.Rows[index].Cells[3].Value.ToString();
                    
                }
                
            }
            else if (button1.Text == "添加" || button1.Text == "查询")
                {
                    empno.Text = "";
                    empname.Text = "";
                    emptel.Text = "";
                    empemail.Text = "";
                }
        }
    }
}