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();
}
}
}