ODP.Net基础应用——连接数据库、查询数据

ODP.Net基础应用——连接数据库、查询数据

• 连接数据库
• 测试表的结构
• 查询多行数据
• 查询单个标量数据
• 处理NULL值
• 将数据写入Dataset
• 替代变量

---

连接数据库

使用OracleConnection类

OracleConnection _conn = new OracleConnection();
_conn.ConnectionString = "User Id=****;Password=****;" +
              "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=1521))" +
              "(CONNECT_DATA=(SID=***)))";
try
{
    _conn.Open();
    MessageBox.Show("Connection successful");
    _conn.Close();
    _conn.Dispose();
    _conn = null;
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString(), "Error connecting to Oracle");
}

测试表的结构

CREATE TABLE PRODUCTS (
ID VARCHAR2(10), 
NAME VARCHAR2(255), 
PRICE NUMBER(10,2), 
REMARKS VARCHAR2(4000),
CONSTRAINT PK PRIMARY KEY (ID)
);

查询多行数据

使用OracleDataReader类

OracleCommand _cmd = _conn.CreateCommand();
_cmd.CommandText = "SELECT * FROM PRODUCTS";
OracleDataReader _orcReader = _cmd.ExecuteReader();
if(_orcReader.HasRows)
{
      while(_orcReader.Read())
      {
          Console.WriteLine(_orcReader.GetString(_orcReader.GetOrdinal("ID")) + "  "+
              _orcReader.GetString(_orcReader.GetOrdinal("NAME")) + "  " + 
              _orcReader.GetDecimal(_orcReader.GetOrdinal("PRICE")) + "  " +
              _orcReader.GetString(_orcReader.GetOrdinal("REMARKS")));
       }
}

处理NULL值

if(_orcReader.HasRows)
{
      while(_orcReader.Read())
      {
          if(_orcReader.IsDBNull(_orcReader.GetOrdinal("PRICE")))
              Console.WriteLine(_orcReader.GetString(_orcReader.GetOrdinal("PRICE")));
       }
}

查询单个标量数据

OracleCommand _cmd = _conn.CreateCommand();
_cmd.CommandText = "SELECT SUM(PRICE) AS TotalPrice FROM PRODUCTS";
decimal _totalPrice = (decimal)_cmd.ExecuteScalar();
Console.WriteLine(_totalPrice);

将数据写入Dataset

private DataSet ds = null;

private void BTN_conn_Click(object sender, EventArgs e)
{
    OracleConnection _conn = new OracleConnection();
    _conn.ConnectionString = "User Id=****;Password=****;" +
              "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=***)(PORT=1521))" +
              "(CONNECT_DATA=(SID=***)))";
    try
    {
        _conn.Open();
        ds = new DataSet();
        string _sqlString = "SELECT * FROM PRODUCTS";
        OracleDataAdapter _dataAdapt = new OracleDataAdapter(_sqlString, _conn);
        _dataAdapt.Fill(ds);//数据填充
        _conn.Close();
        _conn.Dispose();
        _conn = null;
        f_DataGridView.DataSource = ds.Tables[0];
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString(), "Error connecting to Oracle");
    }
}

替代变量

_conn.Open();
ds = new DataSet();
string _sqlString = "SELECT * FROM PRODUCTS WHERE ID=:IDValue";
OracleCommand _cmd = new OracleCommand(_sqlString, _conn);

OracleParameter _para = _cmd.CreateParameter();
_para.ParameterName = "IDValue";
_para.OracleDbType = OracleDbType.Varchar2;
_para.Value = TBX_ID.Text;
_cmd.Parameters.Add(_para);

OracleDataAdapter _dataAdapt = new OracleDataAdapter(_cmd);
_dataAdapt.Fill(ds);//数据填充
_conn.Close();
_conn.Dispose();
_conn = null;
f_DataGridView.DataSource = ds.Tables[0];
f_DataGridView.Rows[0].Selected = false;

---

[1]: Pro ODP.NET FOR Oracle Database 11g,Edmund Zehoo.