ADO.NET_第八篇_OracleCommand_执行多个SQL语句01

+++ 下面三个例子演示如何用OracleCommand执行多个SQL语句
++ 例一
using System.Text;
using System.Data.OracleClient;
protected void Page_Load(object sender, EventArgs e)
{
    ArrayList commands = new ArrayList();
    string connStr = "Data Source=ora9; uid=scott; pwd=tiger; unicode=true";
    string insertSql = "INSERT INTO AB(A,B) VALUES(:aVar,:bVar)";
    OracleConnection conn = new OracleConnection(connStr);
    conn.Open();
    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);
    }
    OracleTransaction tran = conn.BeginTransaction();
    try
    {
        foreach (OracleCommand cmd in commands)
        {
            cmd.Connection = conn;
            cmd.Transaction = tran;
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }
        tran.Commit();
    }
    catch
    {
        tran.Rollback();
    }
    finally
    {
        conn.Dispose();
    }
}
+++ 说明
(1) 本例用OracleCommand执行多个SQL语句；
(2) 在进行批量操作时使用事务是必需的。

 

++ 例二
protected void Page_Load(object sender, EventArgs e)
{
    string connStr = "data source=ora9; uid=scott; pwd=tiger; unicode=true";
    System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connStr);
    conn.Open();
    System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
    cmd.Connection = conn;
    cmd.CommandText = "select * from emp";
    System.Data.OracleClient.OracleDataReader drEmpTbl = cmd.ExecuteReader();
    Response.Write("EMP表：" + "</br>");
    while (drEmpTbl.Read())
    {
        Response.Write(drEmpTbl.GetInt32(0).ToString() + ", " + drEmpTbl.GetString(1) + "</br>");
    }
    drEmpTbl.Close();
    Response.Write("</br>");
    cmd.Connection = conn;
    cmd.CommandText = "select * from dept";
    Response.Write("DEPT表：" + "</br>");
    System.Data.OracleClient.OracleDataReader drDeptTbl = cmd.ExecuteReader();
    while (drDeptTbl.Read())
    {
        Response.Write(drDeptTbl.GetInt32(0).ToString() + ", " + drDeptTbl.GetString(1) + "</br>");
    }
    drDeptTbl.Close();
    conn.Close();
}
+++ 说明
1) 本例也是用OracleCommand执行多个SQL语句，注意和例一的区别；
2) OracleCommand的操作比较复杂，但它可以完成所有的数据库操作。因为它比较复杂，所有微软有个免费框架Pet Shop，它是一个很有启发性的数据库层设计，它的其中一个目的就是为了封装OracleCommand的操作。