Dll引用
默认情况下,微软提供了System.Data.OracleClient.dll 类库。在Visio Studio2013后,微软System.Data.OracleClient.dll 不再做更新发布。此时,只能采用Oracle自带的相关类库文件,否则将提示如下警示
system.data.oracleclient.oracleconnection 已过时 oracleconnection has been deprecated http://go.microsoft.com/fwlink/?linkID=144260
Oracle自带的相关类库文件文件名称为Oracle.DataAccess.dll ,位于 %OracleHome%\ODP.NET\bin\2.x 。
%OracleHome% 为Oracle安装位置,例如本例安装oracle 在C:\app\pcitc\product\11.2.0\dbhome_1,那么相关类库文件路径就为
C:\app\pcitc\product\11.2.0\dbhome_1\ODP.NET\bin\2.x
参数化查询
为安全起见,需要对相关参数做参数化处理,具体范例如下
private string OracleSearchDemo(string cadqueueId)
{
string address = null;
using (OracleConnection cn = GetOraConnection())
{
string sqlGetAddress = "Select SvjDataAddress From Cadqueue Where CadqueueId = :CadqueueId";
OracleCommand cmd = new OracleCommand(sqlGetAddress, cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new OracleParameter("CadqueueId", cadqueueId));
cn.Open();
OracleDataReader dtr = cmd.ExecuteReader();
if (dtr.Read())
{
address = dtr["SvjDataAddress"].ToString();
// 修改数据
// string sqlChangeStatus = "update cadqueue q set q.status = :status Where CadqueueId = :CadqueueId";
// cmd.CommandText = sqlChangeStatus;
// cmd.Parameters.Clear();
// cmd.Parameters.Add("CadqueueId", cadqueueId);
// cmd.ExecuteNonQuery();
}
dtr.Close();
cn.Close();
}
return address;
}
private OracleConnection GetOracleConnection()
{
OracleConnection conn = new OracleConnection(); // connectString 最好通过 app.config 配置
conn.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=***.***.***.***)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=***)));Persist Security Info=True;User ID=***;Password=***;" ;
return conn;
}
连接串配置化
为方便部署,需要将相关连接数据库信息做成配置信息,通过配置信息读取,具体做法如下:
在工程配置文件添加节点信息
<appSettings>
<add key="Connstr" value="Data Source=dataNode;User ID=admin112;Password=ast112;Persist Security Info=True"/>
</appSettings>
在代码中访问节点信息
public static readonly string ConnectionStrings = ConfigurationManager.AppSettings["Connstr"];
//
// TODO: 在此处添加构造函数逻辑
//
private static OracleConnection GetOracleConnection()
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = ConnectionStrings;
return conn;
}