ODP.Net基础应用——数据更新

• 插入、删除、更新
• 利用DataGridView控件更新数据库

---

插入、删除、更新

OracleCommand.ExecuteNonQuery()方法可执行不需要返回值的声明，如INSERT,UPDATE,DELETE,ALTER TABLE和CREATE TABLE。

try
{
    _conn.Open();

    //insert
    OracleCommand _cmd = _conn.CreateCommand();
    _cmd.CommandText = "INSERT INTO PRODUCTS(ID,NAME,PRICE) VALUES(:v_Id,:v_Name,:v_Price)";
    _cmd.Parameters.Add(new OracleParameter("v_Id", "M1"));
    _cmd.Parameters.Add(new OracleParameter("v_Name", "Mudguards"));
    _cmd.Parameters.Add(new OracleParameter("v_Price", "250.5"));
    _cmd.ExecuteNonQuery();

    //update
    _cmd.CommandText = "UPDATE PRODUCTS SET REMARKS=:v_Remarks WHERE ID=:v_Id";
    _cmd.Parameters.Clear();
    _cmd.Parameters.Add(new OracleParameter("v_Remarks", "Quality mud guards"));
    _cmd.Parameters.Add(new OracleParameter("v_Id", "M1"));
    _cmd.ExecuteNonQuery();

    _cmd.CommandText = "DELETE FROM PRODUCTS WHERE ID=:v_Id";
    _cmd.Parameters.Clear();
    _cmd.Parameters.Add(new OracleParameter("v_Id", "M1"));
    _cmd.ExecuteNonQuery();

    _conn.Close();
    _conn.Dispose();
    _conn = null;
    Console.WriteLine("done");
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString(), "Error connecting to Oracle");
}

利用DataGridView控件更新数据库

下面做一个相对完整的小应用，实现以下功能：
1、连接数据库并将表格的内容显示在DataGridView控件中；
2、用户可更改DataGridView控件中的内容，包括修改已存在的行，增加行，删除行；
3、提交到数据库，进行表格数据的更新。
先贴代码：

private DataSet ds = null;
 private string connString = "User Id=****;Password=****;" +
                "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=****)(PORT=1521))" +
                "(CONNECT_DATA=(SID=****)))";

//连接数据库
private void BTN_conn_Click(object sender, EventArgs e)
{
    OracleConnection _conn = new OracleConnection(connString);
    try
    {
        _conn.Open();
        if (ds == null)
            ds = new DataSet();
        else
            ds.Clear();

        OracleCommand _cmd = _conn.CreateCommand();
        _cmd.CommandText = "SELECT * FROM PRODUCTS";

        OracleDataAdapter _adapter= new OracleDataAdapter(_cmd);
        _adapter.Fill(ds);

        _conn.Close();
        _conn.Dispose();
        _conn = null;
        Console.WriteLine("done");
        f_DataGridView.DataSource = ds.Tables[0];
        f_DataGridView.Rows[0].Selected = false;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString(), "Error connecting to Oracle");
    }
}

//增加一行(关闭了控件的AllowUserToAddRows,因为嫌丑，所里这里加了一个增加行的接口)
private void Btn_AddRow_Click(object sender, EventArgs e)
{
    if (ds == null || ds.Tables.Count <=0 || ds.Tables[0].Rows.Count <= 0)
        return;
    DataRow dr = ds.Tables[0].NewRow();
    ds.Tables[0].Rows.Add(dr);
}

//提交更新，根据表格结构定义OracleDataAdapter的UpdateCommand、InsertCommand和DeleteCommand
private void Btn_UpdateTable_Click(object sender, EventArgs e)
{
    string _sqlString;
    try
    {
        OracleConnection _conn = new OracleConnection(connString);
        _conn.Open();
        OracleCommand _cmd = _conn.CreateCommand();
        OracleDataAdapter _adapter = new OracleDataAdapter(_cmd);

        //update
        _sqlString = "UPDATE PRODUCTS SET NAME=:v_Name,PRICE=:v_Price,REMARKS=:v_Remarks WHERE ID=:v_Id";
        _adapter.UpdateCommand = new OracleCommand(_sqlString, _conn);
        _adapter.UpdateCommand.Parameters.Add(new OracleParameter("v_Name", OracleDbType.Varchar2, 255, "NAME"));
        _adapter.UpdateCommand.Parameters.Add(new OracleParameter("v_Price", OracleDbType.Decimal, 10, "PRICE"));
        _adapter.UpdateCommand.Parameters.Add(new OracleParameter("v_Remarks", OracleDbType.Varchar2, 4000, "REMARKS"));
        _adapter.UpdateCommand.Parameters.Add(new OracleParameter("v_Id", OracleDbType.Varchar2, 10, "ID"));

        //insert
        _sqlString = "INSERT INTO PRODUCTS(NAME,PRICE,REMARKS,ID) VALUES(:v_Name,:v_Price,:v_Remarks,:v_Id)";
        _adapter.InsertCommand = new OracleCommand(_sqlString, _conn);
        _adapter.InsertCommand.Parameters.Add(new OracleParameter("v_Name", OracleDbType.Varchar2, 255, "NAME"));
        _adapter.InsertCommand.Parameters.Add(new OracleParameter("v_Price", OracleDbType.Decimal, 10, "PRICE"));
        _adapter.InsertCommand.Parameters.Add(new OracleParameter("v_Remarks", OracleDbType.Varchar2, 4000, "REMARKS"));
        _adapter.InsertCommand.Parameters.Add(new OracleParameter("v_Id", OracleDbType.Varchar2, 10, "ID"));

        //delete
        _sqlString = "DELETE FROM PRODUCTS WHERE ID=:v_Id";
        _adapter.DeleteCommand = new OracleCommand(_sqlString, _conn);
        _adapter.DeleteCommand.Parameters.Add(new OracleParameter("v_Id", OracleDbType.Varchar2, 10, "ID"));

        _adapter.Update(ds);
        _conn.Close();
        _conn.Dispose();
        _conn = null;
        MessageBox.Show("Dataset committed");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

删除操作这里并没有写，有兴趣的可以自己尝试一下，删除指定行的代码可以使用：

f_DataGridView.Rows.Remove(f_DataGridView.SelectedRows[0]);

运行程序并连接数据库：

修改表格内容并提交：

再次连接数据库查看表格内容：

命令对象生成

当表格的列非常多的时候，定义UpdateCommand、InsertCommand和DeleteCommand将会十分繁琐。ODP.Net提供了OracleCommandBuilder类，用以自动生成Update、Insert和Delete命令对象。

下面对提交更新的函数进行修改：

private void Btn_UpdateTable_Click(object sender, EventArgs e)
{
    string _sqlString;
    try
    {
        OracleConnection _conn = new OracleConnection(connString);
        _conn.Open();
        _sqlString = "SELECT * FROM PRODUCTS";
        OracleCommand _cmd = _conn.CreateCommand();
        OracleDataAdapter _adapter = new OracleDataAdapter(_sqlString, _conn);
        OracleCommandBuilder _cmdBuilder = new OracleCommandBuilder(_adapter);

        ds.Tables[0].Columns["ID"].Unique = true;//指定Unique列，以告诉OracleCommandBuilder哪个是主键

        _adapter.Update(ds);
        _conn.Close();
        _conn.Dispose();
        _conn = null;
        MessageBox.Show("Dataset committed");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

再来编译运行，添加一列，提交

notes：

OracleCommandBuilder只能用于单表更新，如果在查询中用到了连接进行多表查询，则只能hand-code定义UpdateCommand、InsertCommand和DeleteCommand。