在C#中操纵Oracle数据库

Oracle数据库相关操作：

--使用Oracle Database 11g express(下载)，SQL*Plus

--定义用户
SQL> connect sys/oracle as sysdba;
SQL> create user sa identified by sa;
SQL> grant dba to sa;
SQL> connect sa/sa;

--定义表：
SQL> create table test(a number primary key, b varchar2(20));

--插入数据：
SQL> insert into test values(1, 'hello') ;
SQL> insert into test values(2, 'world') ;
SQL> insert into test values(3, 'test oracle') ;
SQL> commit;

--Oracle存储过程用法

--定义：
SQL> create or replace procedure spSelect(rb out varchar2) as
  2  begin
  3  select b into rb from test where a=1;
  4  end;
  5  /
--调用：

SQL> set serveroutput on

SQL> declare rb varchar2(20);
  2  begin
  3  spSelect(rb);
  4  dbms_output.put_line(rb);
  5  end;
  6  /
--重新定义存储过程：
SQL> create or replace procedure spSelect(pa in number,rb out  varchar2) as
  2  begin
  3     select b into rb from test where a=pa;
  4  end;
  5  /

 C#操纵Oracle数据库：

  C#操纵Oracle数据库类似于SQL Server数据库，主要是连接串中提供者有所不同。

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.OleDb;//VS2010中没有：System.Data.OracleClient

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

        OleDbConnection cnn = null; //定义连接对象

        private void button1_Click(object sender, EventArgs e)//绑定数据到dataGridView
        {
            //绑定DataReader，以DataTable的方法
            OleDbCommand cmd = new OleDbCommand("select * from test", cnn);
            OleDbDataReader dr = cmd.ExecuteReader();
            DataTable dt=new DataTable();
            dt.Load(dr);
            dataGridView1.DataSource = dt;

            //另一种绑定方法，DataAdapter与DataSet的方法
            //OleDbDataAdapter da = new OleDbDataAdapter("select * from test", cnn); 
            //DataSet ds = new DataSet();
            //da.Fill(ds, "test");
            //dataGridView1.DataSource = ds.Tables[0]; 
        }

        private void Form1_Load(object sender, EventArgs e) //打开连接
        {
            string cnnStr = "Provider=OraOLEDB.Oracle.1; Data Source=xe;User ID=sa;Password=sa";
            cnn = new OleDbConnection(cnnStr);
            cnn.Open();
        }

        private void button5_Click(object sender, EventArgs e)//关闭连接与窗口
        {
            if (cnn.State == ConnectionState.Open) cnn.Close();
            this.Close();
        }

        private void button4_Click(object sender, EventArgs e)//插入
        {
            string sql=string.Format("insert into test values({0},'{1}')",textBox1.Text, textBox2.Text);
            OleDbCommand cmd = new OleDbCommand(sql, cnn);
            cmd.ExecuteNonQuery();
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)//关闭连接
        {
            if (cnn.State == ConnectionState.Open) cnn.Close();
        }

        private void button3_Click(object sender, EventArgs e)//修改
        {
            string sql = string.Format("update test set b='{1}' where a={0}", textBox1.Text, textBox2.Text);
            OleDbCommand cmd = new OleDbCommand(sql, cnn);
            cmd.ExecuteNonQuery();
        }

        private void button2_Click(object sender, EventArgs e)//删除
        {
            string sql = string.Format("delete from test where a={0}", textBox1.Text);
            OleDbCommand cmd = new OleDbCommand(sql, cnn);
            cmd.ExecuteNonQuery();
        }

        private void button6_Click(object sender, EventArgs e)//调用存储过程
        {
            OleDbCommand cmd = new OleDbCommand("spSelect", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("pa", textBox1.Text);
            cmd.Parameters.Add("rb", OleDbType.VarChar,20);
            cmd.Parameters["rb"].Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            textBox2.Text = cmd.Parameters["rb"].Value.ToString();
        }
    }
}