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.