VS2010连接Oracle11g数据库

最新推荐文章于 2023-08-05 11:53:31 发布

whucv

最新推荐文章于 2023-08-05 11:53:31 发布

阅读量4k

点赞数 1

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/archielau/article/details/9972981

版权

数据库专栏收录该内容

3 篇文章

订阅专栏

一、使用Oracle.DataAccess.dll

在数据库ODT.Net的bin\2.x目录下找到Oracle.DataAccess.dll，添加引用到当前项目。

添加OracleDataAccess命名空间

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;
using OracleDataAccess;

在单击按钮事件下，添加连接数据库代码

(2)

private void button1_Click(object sender, EventArgs e)
        {
            string oradb = "Data Source=Orcl;User ID=hr;Password=hr;";

            OracleConnection conn = new OracleConnection(oradb);
          //  OracleConnection conn = new OracleConnection(); // C#
          //  conn.ConnectionString = oradb;

            conn.Open();
            string sql = " select department_name from departments where department_id = 10"; // C#
            OracleCommand cmd = new OracleCommand(sql, conn);
            cmd.CommandType = CommandType.Text;

            OracleDataReader dr = cmd.ExecuteReader(); // C#
            dr.Read();

            label1.Text = dr["department_name"].ToString(); // C# retrieve by column name
            label1.Text = dr.GetString(0).ToString();  // return a .NET data type
            label1.Text = dr.GetOracleString(0).ToString();  // return an Oracle data type

            label1.Text = dr.GetInt16("department_id").ToString();
            /*
             {
    conn.Open();

    OracleCommand cmd = new OracleCommand();
    cmd.Connection = conn;
    cmd.CommandText = "select department_name from departments where department_id = 10";
    cmd.CommandType = CommandType.Text;
        
    OracleDataReader dr = cmd.ExecuteReader();
    dr.Read();

    label1.Text = dr.GetString(0);
}


             */

            /*
             try
{
    conn.Open();

    OracleCommand cmd = new OracleCommand();
    cmd.Connection = conn;
    cmd.CommandText = "select department_name from departments where department_id = " + textBox1.Text;
    cmd.CommandType = CommandType.Text;

    if (dr.Read()) // C#
    {
        label1.Text = dr["department_name"].ToString();
                   // or use dr.GetOracleString(0).ToString()
    }
}
catch (Exception ex) // catches any error
{
    MessageBox.Show(ex.Message.ToString());
}
finally
{
    // In a real application, put cleanup code here.
}


             */
        }

单击按钮，返回如下界面

-------------------------------------------------------------------------------------------------------------------------

二、System.Data.OracleClient

Oracle开发工具：VS2010

数据库：oracl 11g

在连接oracle数据时，我们需要做以下的工作：

1、添加 oracleclient 引用和Configuration引用

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;
using System.Data.OracleClient;

using System.Configuration;

2、配置app.config

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="Orcl" connectionString="Data Source=Orcl;User ID=hr;Password=hr;"/>
  </connectionStrings>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup></configuration>

3、编写数据库操作代码：

 private void button1_Click(object sender, EventArgs e)
        {
            string oradb = ConfigurationManager.ConnectionStrings["Orcl"].ToString();
            OracleConnection conn = new OracleConnection(oradb);
          

            conn.Open();
            string sql = " select department_name from departments where department_id = 10"; // C#
            OracleCommand cmd = new OracleCommand(sql, conn);
            cmd.CommandType = CommandType.Text;

            OracleDataReader dr = cmd.ExecuteReader(); // C#
            dr.Read();

            label1.Text = dr["department_name"].ToString(); // C# retrieve by column name
            label1.Text = dr.GetString(0).ToString();  // return a .NET data type
            label1.Text = dr.GetOracleString(0).ToString();  // return an Oracle data type

 
        }

与第一种方法返回相同结果。

修改数据库

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;
using System.Data.OracleClient;
using System.Configuration;

namespace OracleAccess
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string oradb = ConfigurationManager.ConnectionStrings["Orcl"].ToString();
            OracleConnection conn = new OracleConnection(oradb);

            try
            {
                conn.Open();
                string sql = " select department_name from departments where department_id = 10"; // C#
                OracleCommand cmd = new OracleCommand(sql, conn);
                cmd.CommandType = CommandType.Text;

                OracleDataReader dr = cmd.ExecuteReader(); // C#
                while(dr.Read())
                {
                    label1.Text = dr["department_name"].ToString(); // C# retrieve by column name
                    label1.Text = dr.GetString(0).ToString();  // return a .NET data type
                    label1.Text = dr.GetOracleString(0).ToString();  // return an Oracle data type
                }
                dr.Close();//关闭reader.这是一定要写的  
            }
            catch
            {
                MessageBox.Show("erro");//如果发生异常，则提示出错  
            }
            finally
            {
                conn.Close();//关闭打开的连接  
            }

          
 
        }

        private void button2_Click(object sender, EventArgs e)
        {
            #region 从region到endregion是手工写的。别的都是系统自动生成的
            string oradb = ConfigurationManager.ConnectionStrings["Orcl"].ToString();
            OracleConnection conn = new OracleConnection(oradb);
           
            try
            {
                conn.Open();//打开指定的连接  
                OracleCommand com = conn.CreateCommand();
                com.CommandText = "update departments set department_name='archie' where department_id = 10";//写好想执行的Sql语句  
                com.ExecuteNonQuery();

            }
            catch
            {
                MessageBox.Show("erro");//如果发生异常，则提示出错  
            }
            finally
            {
                conn.Close();//关闭打开的连接  
            }

            #endregion  
        }
    }
}

点击更改，再重新查询

增删改查，以下还未实验

以users表为例，有三个字段，自增长的编号id，int类型；名称name，nvarchar类型，密码pwd，nvarchar类型

引入System.Data.OracleClient;命名空间

private void button3_Click(object sender, EventArgs e)
        {
             string oradb = ConfigurationManager.ConnectionStrings["Orcl"].ToString();
            OracleConnection conn = new OracleConnection(oradb);
           
            try
            {
                conn.Open();//打开指定的连接  
                string sql = "insert into departments(department_id,department_name,manager_id,location_id)values(:department_id:department_name,:manager_id,:location_id)";
                OracleCommand cmd = new OracleCommand(sql, conn);
                OracleParameter parn = new OracleParameter(":department_id", 1);
                cmd.Parameters.Add(parn);
                OracleParameter parp = new OracleParameter(":department_name","archie");
                cmd.Parameters.Add(parp);
                parn = new OracleParameter(":manager_id", 12);
                cmd.Parameters.Add(parn);
                parp = new OracleParameter(":location_id", 23);
                cmd.Parameters.Add(parp);
                int result = cmd.ExecuteNonQuery(); //result接收受影响行数，也就是说result大于0的话表示添加成功
                cmd.Dispose();
            }
            catch
            {
                MessageBox.Show("erro");//如果发生异常，则提示出错  
            }
            finally
            {
                conn.Close();//关闭打开的连接  
            } 
                 
        }

publicint Insert(stringname, string pwd)

{

OracleConnection conn = new OracleConnection(@"Data Source=SBZX;User ID=simis;Password=zeda");

conn.Open();

stringsql = "insert into users(name,pwd)values(:name,:pwd)";

OracleCommand cmd = new OracleCommand(sql,conn);

OracleParameter parn = new OracleParameter(":name", name);

cmd.Parameters.Add(parn);

OracleParameter parp = new OracleParameter(":pwd", name);

cmd.Parameters.Add(parp);

int result = cmd.ExecuteNonQuery(); //result接收受影响行数，也就是说result大于0的话表示添加成功

conn.Close();

cmd.Dispose();

return result;

}

publicint Update(int id)

{

OracleConnectionconn = new OracleConnection(@"Data Source=SBZX;User ID=simis;Password=zeda");

conn.Open();

stringsql = "delete from users where id=:id";

OracleCommandcmd = new OracleCommand(sql,conn);

OracleParameterpari = new OracleParameter(":id", id);

cmd.Parameters.Add(pari);

int result = cmd.ExecuteNonQuery(); //result接收受影响行数，也就是说result大于0的话表示添加成功

conn.Close();

cmd.Dispose();

return result;

}

publicint Insert(stringname, string pwd, intid)

{

OracleConnectionconn = new OracleConnection(@"Data Source=SBZX;User ID=simis;Password=zeda");

conn.Open();

stringsql = "update users set name=:name,pwd=:pwdwhere id=:id";

OracleCommandcmd = new OracleCommand(sql,conn);

OracleParameterparn = new OracleParameter(":name", name);

cmd.Parameters.Add(parn);

OracleParameterparp = new OracleParameter(":pwd", name);

cmd.Parameters.Add(parp);

OracleParameterpari = new OracleParameter(":id", id);

cmd.Parameters.Add(pari);

intresult = cmd.ExecuteNonQuery();

conn.Close();

cmd.Dispose();

returnresult;

}

publicDataTable Select()

{

OracleConnectionconn = new OracleConnection(@"Data Source=SBZX;User ID=simis;Password=zeda");

conn.Open();

stringsql = "select * from users";

OracleCommandcmd = new OracleCommand(sql,conn);

OracleDataAdapteroda = new OracleDataAdapter(cmd);

DataTable dt = new DataTable();

oda.Fill(dt);

conn.Close();

cmd.Dispose();

return dt;

}

方法写好后，下面举一个查询的例子，在form窗体中拖一个DataGridView,然后在Load方法中

    private void Form1_Load(object sender, EventArgs e)

              dataGridView1.DataSource = Select();

这样一运行，DataGridView中就会显示数据了