访问ORACLE数据库
一．头语
1. 在 Oracle 中，先创建数据库，再建立用户，表在用户下面。
2.System.Data.OracleClient 和 System.Data.OleDb 命名空间
（1）.虽然通过这两个命名空间的类都可以访问 Oracle 数据库， System.Data.OracleClient 命名空间中的类要比 System.Data.OleDb 命名空间的类效率高一些
（2）. OleDb可以访问Access,SQL Server,Oracle,等数据库 OracleClient只能访问Oracle数据库，和SqlClient 类似(3) System.Data.OracleClient的针对性要好一点
比如数据类型，System.Data.OleDb.OleDbType 枚举中所列的就没有 System.Data.OracleClient.OracleType 枚举中的那些有针对性；另外，Oracle 的 Number 类型如果数字巨大，超出 .NET 数据类型范围的情况中，就必须使用 System.Data.OracleClient 中的专门类 -- OracleNumber 类型。
3.Oracle 客户端工具（安装在计算机上）
无论是 System.Data.OleDb 还是 System.Data.OracleClient 访问 Oracle 都需要在 .NET 运行的机器安装 Oracle 客户端组件
二．主要内容
1.数据库连接
(1)连接是建立与数据库会话的操作，会话是一系列查看、插入、更新、删除和用数据库执行其他管理命令的操作；
(2)连接对象必须实例化并打开以建立与数据库的会话
（3）要用OracleClient方法，须在C#中预先添加类名空间
OracleClient连接方法
using System.Data.OracleClient;
string myConnString = "user id=test;data source=zkqx;password=*****";
OracleConnection myConnection = new OracleConnection(myConnString);
myConnection.Open();
.. .. .. .. ..
myConnection.Close();
OleDb连接方法
using System.Data.OleDb；
conn = new OleDbConnection(@”Provider=Microsoft.Jet.OLEDB.4.0；
User id = ;Password = ;Data Source=D:\My Documents\student.mdb”)
// OleDbConnection 用于建立连接
Conn.Open();
.. .. .. .. ..
Conn.Close();
2.查询数据
< 1 > Oracle查询方法
用DataReader 方法访问
myConnection.Open()
//用连接的对象创建一个命令对象
OracleCommand catCMD = myConnection.CreateCommand();
//通过初始化命令对象的CommandText特性，指定应该操作哪个命令对象；
catCMD.CommandText = " SELECT name,age FROM student ";
//根据命令对象创建一个读取器；
OracleDataReader myReader = catCMD.ExecuteReader();
myReader.Read()；
……………..
myConnection.Close()
用DataAdapter方法访问
myConnection.Open()
string sqlString ＝ insert into student (学号)values (9527);
//OracleDataAdapter 用于更新数据源
OracleDataAdapter myAdapter = new OracleDataAdapter(sqlString, myConnection);
DataTable myDataTable = new DataTable();//DataSet 数据在内存中的缓存，就是在内存中建立一个与数据库一致的表
myAdapter.Fill(myDataTable);
……………
myConnection.Close()
< 2 > OleDb查询方法
用DataReader 方法访问
myConnection.Open()
OleDbDataReader dbReader = null;
OleDbCommand cmd = Conn.CreateCommand();
//OleDbCommand 用于执行SQL命令
cmd.CommandText=”SELECT * FROM student”;
dbReader=cmd.ExecuteReader();
用DataAdapter方法访问
myConnection.Open();
sqlString = "SELECT * FROM student";
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(sqlString,myConnection)
DataTable myDataTable = new DataTable();
myAdapter.Fill(myDataTable);
//etc.
curConnection.Close();
3.insert
(1)OracleClient方法
public class OracleClientAccess //声明一个类
{
private string cnnString;
private OracleConnection curConnection;
public bool Insert( ) //类方法
{
try
{
curConnection.Open();
string sqlString = Select * From Student;
OracleDataAdapter myAdapter = new OracleDataAdapter(sqlString,curConnection);
DataTable myDataTable = new DataTable();
curAdapter.Fill(myDataTable);
DataRow myRow=myDataTable.NewRow()
myRow[“学号”]＝”9528”;
myRow[“姓名”]＝”小宝”
MyDataTable.Rows.Add(myRow)
return true;
}
catch
{
return false;
}
finally
{
curConnection.Close();
}
}
public OracleClientAccess(string userName, string userPwd, string dataSource)
{
cnnString = "user id=" + userName + ";data source=" + dataSource + ";password=" + userPwd;
curConnection = new OracleConnection(cnnString);
}
}
private void btnInsert_Click(object sender, EventArgs e)
{
OracleClientAccess tmpAccess = new OracleClientAccess("test", "test", "zzora");
if (tmpAccess.Insert( ) = = true) { MessageBox.Show("Insert Success!");}//调用类方法
else
{
MessageBox.Show("Insert Failed!");
}
}
(2)OleDb方法
public class OleDbClientAccess //声明一个类
{
private string cnnString;
private OleDbConnection curConnection;
public bool Insert(string sqlString) //类方法
{
try
{
curConnection.Open();
OleDbDataAdapter myAdapter = new OleDbDataAdapter(“SELECT * FROM Student”,curConnection);
DataTable myDataTable = new DataTable();
curAdapter.Fill(myDataTable);
DataRow myRow=new DataRow();
myRow[“学号”]＝”9528”;
myRow[“姓名”]＝”小宝”
MyDataTable.Rows.Add(myRow)
return true;
}
catch
{
return false;
}
finally
{
curConnection.Close();
}
}
public OleDbClientAccess(string userName, string userPwd, string dataSource)
{
cnnString = "user id=" + userName + ";data source=" + dataSource + ";password=" + userPwd;
curConnection = new OleDbConnection(cnnString);
}
}
private void btnInsert_Click(object sender, EventArgs e)
{
OleDbClientAccess tmpAccess = new OleDbClientAccess("test", "test", "zzora");
if (tmpAccess.Insert(“Insert into student(学号，姓名)value(9528,”小宝”)” ) = = false) { MessageBox.Show("Insert Success!");}//调用类方法
else
{
MessageBox.Show("Insert Failed!");
}
}
4.update
(1)OracleClient方法
public class OracleClientAccess //声明一个类
{
private string cnnString;
private OracleConnection curConnection;
public bool Update(string sqlString) //类方法
{
OracleCommand curCmd = curConnection.CreateCommand();
curCmd.CommandText = sqlString;
curConnection.Open();
try
{
try
{
curCmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
}
finally
{
curConnection.Close();
}
}
public OracleClientAccess(string userName, string userPwd, string dataSource)
{
cnnString = "user id=" + userName + ";data source=" + dataSource + ";password=" + userPwd;
curConnection = new OracleConnection(cnnString);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
OracleClientAccess tmpAccess = new OracleClientAccess("test", "test", "zzora");
if (tmpAccess.Insert("UPDATE student SET name = “小王‘“
，age=25,number=”9527” WHERE ID=5”)) = = true) { MessageBox.Show("Insert Success!");}//调用类方法
else
{
MessageBox.Show("Insert Failed!");
}
}
(2)OleDb方法
class OldDbAccess //声明一个类
{
private string cnnString;
private OleDbConnection curConnection;
public bool Update(string sqlString) //类方法
{
OleDbCommand curCmd = curConnection.CreateCommand();
curCmd.CommandText = sqlString;
curConnection.Open();
try
{
try
{
curCmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
}
finally
{
curConnection.Close();
}
}
public OldDbAccess(string userName, string userPwd, string dataSource)
{
cnnString = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;" +
"User ID=" + userName + ";Password=" + userPwd + ";Data Source=" + dataSource;
curConnection = new OleDbConnection(cnnString);
}
}
private void btnInsert_Click(object sender, EventArgs e)
{
OleDbClientAccess tmpAccess = new OleDbClientAccess("test", "test", "zzora");
if (tmpAccess.Insert("UPDATE student SET name = “小王‘“
，age=25,number=”9527” WHERE ID=5”) = = true) //调用类方法
{
MessageBox.Show("Insert Success!");
}
else
{
MessageBox.Show("Insert Failed!");
}
}
5.delete
(1)OracleClient方法
public class OracleClientAccess //声明一个类
{
private string cnnString;
private OracleConnection curConnection;
public bool Delete(string sqlString) //类方法
{
OracleCommand curCmd = curConnection.CreateCommand();
curCmd.CommandText = sqlString;
curConnection.Open();
try
{
try
{
curCmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
}
finally
{
curConnection.Close();
}
}
public OracleClientAccess(string userName, string userPwd, string dataSource)
{
cnnString = "user id=" + userName + ";data source=" + dataSource + ";password=" + userPwd;
curConnection = new OracleConnection(cnnString);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
OracleClientAccess tmpAccess = new OracleClientAccess("test", "test", "zzora");
if (tmpAccess.Insert("DELETE FROM student WHERE ID=5”)) = = true) { MessageBox.Show("Insert Success!");}//调用类方法
else
{
MessageBox.Show("Insert Failed!");
}
}
(2)OleDb方法
class OldDbAccess //声明一个类
{
private string cnnString;
private OleDbConnection curConnection;
public bool Delete(string sqlString) //类方法
{
OleDbCommand curCmd = curConnection.CreateCommand();
curCmd.CommandText = sqlString;
curConnection.Open();
try
{
try
{
curCmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
}
finally
{
curConnection.Close();
}
}
public OldDbAccess(string userName, string userPwd, string dataSource)
{
cnnString = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;" +
"User ID=" + userName + ";Password=" + userPwd + ";Data Source=" + dataSource;
curConnection = new OleDbConnection(cnnString);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
OleDbClientAccess tmpAccess = new OleDbClientAccess("test", "test", "zzora");
if (tmpAccess.Insert("DELETE FROM student WHERE ID=5”) = = true)) //调用类方法
{
MessageBox.Show("Insert Success!");
}
else
{
MessageBox.Show("Insert Failed!");
}
}
三．总结
(1) 用连接的对象创建一个命令对象；
(2) 通过初始化命令对象的CommandText特性，指定应该操作哪个命令对象；
(3) 根据命令对象创建一个读取器；