VS2010连接Oracle11g数据库

来源：互联网发布：网络聊天编辑：程序博客网时间：2024/04/30 05:11

一、使用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中就会显示数据了