ADO.NET_06_OracleCommand
本文内容
- ExecuteNonQuery 方法
- ExecuteScalar 方法
- ExecuteReader 方法
- 执行 PL SQL
- 执行存储过程
表结构
create table AB
(
A VARCHAR2(10),
B VARCHAR2(10),
C BLOB,
D CLOB
)
存储过程
create or replace procedure p_add(v_a in varchar2, v_b in varchar2 ) is
begin
INSERT INTO AB (a, b) VALUES (v_a, v_b);
end p_add;
ExecuteNonQuery 方法
执行SQL
Oraclecommand.ExecuteNonQuery 方法主要是用来执行目录操作（如查询数据库结构或创建数据库对象），或通过执行 UPDATE、INSERT、DELETE 这样的 DML 操作。
string connStr = "Data Source=ora11; uid=scott; pwd=tiger; unicode=true";
string aVar = "AAA";
string bVar = "AAA";
string dVar = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA";
string sqlStr = "INSERT INTO AB(A,B,D) VALUES(':aVar',':bVar',':dVar')";
sqlStr = sqlStr.Replace(":aVar", aVar).Replace(":bVar", bVar).Replace(":dVar", dVar);
OracleConnection conn = new OracleConnection(connStr);
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sqlStr;
cmd.CommandType = CommandType.Text;
try
{
conn.Open();
cmd.ExecuteNonQuery();
Response.Write("成功.");
//int rowAffected = cmd.ExecuteNonQuery();
//Response.Write("受影响的行：" + rowAffected);
}
catch (Exception ex)
{
Response.Write("失败." + ex);
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
说明
1) Oraclecommand.ExecuteNonQuery 方法对于 UPDATE、INSERT、DELETE 语句，返回值为该命令所影响的行数；对于其他类型的语句，返回值为-1；如果发生回滚，返回值为-1；
2) 本例直接创建 SQL 语句，也可以用 OracleParameter 来执行带参数的SQL；
执行带参数的SQL
string connStr = "Data Source=ora11; uid=scott; pwd=tiger; unicode=true";
string insertSql = "INSERT INTO AB(A,B) VALUES(:aVar,:bVar)";
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
OracleCommand cmd = new OracleCommand(insertSql, conn);
OracleParameter[] paras = {
new OracleParameter("aVar", OracleType.NVarChar, 10),
new OracleParameter("bVar", OracleType.NVarChar, 10)};
paras[0].Value = "AAA";
paras[1].Value = "AAA";
foreach (OracleParameter para in paras)
cmd.Parameters.Add(para);
cmd.CommandType = CommandType.Text;
try
{
cmd.ExecuteNonQuery();
Response.Write("成功.");
//int rowAffected = cmd.ExecuteNonQuery();
//Response.Write("受影响的行：" + rowAffected);
}
catch
{
Response.Write("失败.");
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
说明
1) 虽然用 OracleParameter 执行 SQL 语句很灵活，但在实际项目中往往不这么做，而是直接创建 SQL 语句。直接构造 SQL 语句很直观，调试程序时很容易检查 SQL 语句是否正确。
2) 那么什么情况下使用 OracleParameter 呢？当操作数据库的大字段（如Clob 和 Blob）时，如操作 Clob 字段，如果直接构造 SQL 语句可能存在字符串长度限制。这点，在 Oracle 9i 上很明显，但 Oracle 11g 上就基本上看不出来。为了保险起见，这种情况我通常使用 OracleParameter。
3) OracleParameter 有7个构造函数，自己到 MSDN 看吧，找一个自己最喜欢的方式就行。
ExecuteScalar 方法
string connStr = "Data Source=ora11; uid=scott; pwd=tiger; unicode=true";
string sqlStr = "SELECT COUNT(*) FROM EMP";
OracleConnection conn = new OracleConnection(connStr);
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sqlStr;
cmd.CommandType = CommandType.Text;
try
{
conn.Open();
Int32 count = Convert.ToInt32(cmd.ExecuteScalar());
Response.Write("成功." + count);
}
catch (Exception ex)
{
Response.Write("失败." + ex);
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
说明
(1) Oraclecommand.ExecuteScalar 方法返回结果中数据集的个数，通常用于用户登录；
(2) 另外，该方法返回查询结果集中的第一行第一列。可以将本例的SQL改成 "SELECT * FROM EMP WHERE EMPNO='" + "7369" + "'"，用 OracleDataAdapter 获得填充数据集，输出结果，试试看。
ExecuteReader 方法
string connStr = "Data Source=ora11; uid=scott; pwd=tiger; unicode=true";
string sqlStr = "SELECT d FROM ab";
OracleConnection conn = new OracleConnection(connStr);
OracleCommand cmd = new OracleCommand();
OracleDataReader dr = null;
cmd.Connection = conn;
cmd.CommandText = sqlStr;
cmd.CommandType = CommandType.Text;
try
{
conn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr[0].ToString().Length > 0)
Response.Write(dr[0].ToString() + "</br>");
}
}
catch (Exception ex)
{
Response.Write("失败." + ex);
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
执行多个 SQL
执行多个 OracleCommand
ArrayList commands = new ArrayList();
string connStr = "Data Source=ora11; uid=scott; pwd=tiger; unicode=true";
string insertSql = "INSERT INTO AB(A,B) VALUES(:aVar,:bVar)";
OracleConnection conn = new OracleConnection(connStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
for (int i = 0; i <= 10; i++)
{
OracleCommand cmd = new OracleCommand(insertSql, conn);
OracleParameter[] paras = {
new OracleParameter("aVar", OracleType.NVarChar, 10),
new OracleParameter("bVar", OracleType.NVarChar, 10)};
paras[0].Value = "AAA" + i;
paras[1].Value = "BBB" + i;
foreach (OracleParameter para in paras)
cmd.Parameters.Add(para);
cmd.CommandType = CommandType.Text;
commands.Add(cmd);
}
try
{
foreach (OracleCommand cmd in commands)
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
tran.Commit();
Response.Write("成功.");
}
catch (Exception ex)
{
tran.Rollback();
Response.Write("失败." + ex);
}
finally
{
foreach (OracleCommand cmd in commands)
{
cmd.Dispose();
}
tran.Dispose();
conn.Dispose();
conn.Close();
}
执行 PL SQL
StringBuilder sqlSb = new StringBuilder();
string connStr = "Data Source=ora11; uid=scott; pwd=tiger; unicode=true";
OracleConnection conn = new OracleConnection(connStr);
sqlSb.Append("BEGIN ");
for (int i = 0; i <= 10; i++)
{
string aCol = "AAA" + i;
string bCol = "BBB" + i;
string insertSql = "INSERT INTO AB(A,B) VALUES('" + aCol + "','" + bCol + "'); ";
sqlSb.Append(insertSql);
}
sqlSb.Append(" END;");
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sqlSb.ToString();
cmd.CommandType = CommandType.Text;
try
{
conn.Open();
cmd.ExecuteNonQuery();
Response.Write("成功.");
}
catch (Exception ex)
{
Response.Write("失败." + ex);
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
执行存储过程
string connStr = "Data Source=ora11; uid=scott; pwd=tiger; unicode=true";
OracleConnection conn = new OracleConnection(connStr);
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "p_add";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter[] paras = {
OracleParameter("v_a", OracleType.NVarChar, 10),
OracleParameter("v_b", OracleType.NVarChar, 10)};
paras[0].Value = "AAA";
paras[1].Value = "AAA";
foreach (OracleParameter para in paras)
cmd.Parameters.Add(para);
try
{
conn.Open();
cmd.ExecuteNonQuery();
Response.Write("成功.");
}
catch (Exception ex)
{
Response.Write("失败." + ex);
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
