使用 Oracle.DataAccess.Client 驱动 和 OleDB 调用Oracle 函数的区别
Oracle.DataAccess.Client 驱动 和 OleDB 调用Oracle 函数的区别
Oracle.DataAccess.Client 驱动 和 OleDB 调用Oracle 函数的区别主要体现在两个方面
1 命令文本的区别
CommandText:
Oracle.DataAccess.Client 驱动：
OracleCommand.CommandText ="Oracle函数名"
OleDB 驱动：
OleDBCommand.CommandText ="{ ? = call oracle函数名(?,?)}"
2 命令类型的区别
CommandType
目录
Oracle.DataAccess.Client 驱动 和 OleDB 调用Oracle 函数的区别
1 命令文本的区别
2 命令类型的区别
3 代码示列：
Oracle.DataAccess.Client 驱动：
OracleCommand.CommandType = CommandType.StoredProcedure;
OleDB 驱动：
OleDBCommand.CommandType = CommandType.Text;
3 代码示列：
using System;
using Oracle.DataAccess.Client;
using System.Data.OleDb;
using System.Data;
class Program
{
//Oracle.DataAccess.Client;
public void ExecuteOracleProcedure()
{
string oradb = "User Id=yourUsername;Password=yourPassword;Data Source=yourDataSource";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
//OracleCommand.CommandText ="Oracle函数名"；
OracleCommand cmd = new OracleCommand("GET_EMPLOYEE_NAME", conn);
//此处为 CommandType 取值CommandType.StoredProcedure，不能取CommandType.Text
cmd.CommandType = CommandType.StoredProcedure;
// 添加输入参数
cmd.Parameters.Add("EMPLOYEE_ID", OracleDbType.Int32).Value = 123;
// 添加输出参数
cmd.Parameters.Add("EMPLOYEE_NAME", OracleDbType.Varchar2, ParameterDirection.Output).Size = 100;
cmd.ExecuteNonQuery();
// 获取输出参数的值
string employeeName = cmd.Parameters["EMPLOYEE_NAME"].Value.ToString();
Console.WriteLine("Employee Name: " + employeeName);
conn.Close();
}
//oledb
public void ExecuteOleDBProcedure()
{
string oradb = "Provider=OraOLEDB.Oracle.1;User Id=yourUsername;Password=yourPassword;Data Source=yourDataSource";
OleDbConnection conn = new OleDbConnection(oradb);
conn.Open();
//OleDBCommand.CommandText ="{ ? = call oracle函数名(?,?)}"；函数有几个参数就添加几个问号，问号有“,”隔开
OleDbCommand cmd = new OleDbCommand("{ ? = call GET_EMPLOYEE_NAME(?,?)}", conn);
//此处为 CommandType 取值CommandType.Text
cmd.CommandType = CommandType.Text;
// 添加输入参数
cmd.Parameters.Add("EMPLOYEE_ID", OleDbType.Integer).Value = 123;
cmd.Parameters["EMPLOYEE_ID"].Direction = ParameterDirection.Input;
// 添加输出参数
cmd.Parameters.Add("EMPLOYEE_NAME", OleDbType.VarChar).Size = 100;
cmd.Parameters["EMPLOYEE_NAME"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
// 获取输出参数的值
string employeeName = cmd.Parameters["EMPLOYEE_NAME"].Value.ToString();
Console.WriteLine("Employee Name: " + employeeName);
conn.Close();
}
}