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

}

 

下载 Demo