通过网上了解到.net连接Oracle主要有3种方法。
(1)System.Data.OracleClient
微软的System.Data.OracleClient可以直接引用,但是VS会提示“System.Data.OracleClient.OracleConnection”已过时。
(2)Oracle.DataAccess.Client
也叫ODP.net,是Oracle提供的数据库访问类库,不用安装Oracle客户端,只需要在oracle安装目录下找到Oracle.DataAccess.dll添加引用,但是缺点是要区分x86/x64版本。
(3)Oracle.ManagedDataAccess.Client
也是Oracle提供的数据库访问类库,无需安装oracle客户端,也不需要区分x86/x64版本,直接引用Oracle.ManagedDataAccess.dll 即可。
下面是用使用Oracle.ManagedDataAccess.Client连接Oracle例子:
1、配置Oracle连接字符串
本次例子是WinForm,app.config的Oracle连接字符串配置如下,关键信息:IP地址、服务名、用户名、密码。
2、简单写了个数据库访问类OracelHelper.cs
usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingOracle.ManagedDataAccess.Client;usingSystem.Data;namespacedemo
{public classOracelHelper
{private static readonly string connectionString = System.Configuration.ConfigurationManager.AppSettings["OracleConn"].ToString();public staticOracleConnection GetConn()
{var conn = newOracleConnection(connectionString);
conn.Open();returnconn;
}public static int ExecuteNonQuery(stringsql)
{using (var conn =GetConn())
{var cmd = newOracleCommand(sql, conn);int result =cmd.ExecuteNonQuery();returnresult;
}
}public static int ExecuteScalar(stringsql)
{using (var conn =GetConn())
{var cmd = newOracleCommand(sql, conn);object o =cmd.ExecuteScalar();returnConvert.ToInt32(o.ToString());
}
}public static OracleDataReader ExecuteReader(stringsql)
{var conn =GetConn();var cmd = newOracleCommand(sql, conn);var myReader =cmd.ExecuteReader(CommandBehavior.CloseConnection);returnmyReader;
}public static DataSet ExecDataSet(stringsql)
{using (var conn =GetConn())
{var cmd = newOracleCommand(sql, conn);
OracleDataAdapter da= newOracleDataAdapter(cmd);
DataSet ds= newDataSet();
da.Fill(ds);returnds;
}
}
}
}
3、如果是使用Dapper,可参考上篇文章“.net4.0使用Dapper操作MySql”,部分代码稍微修改如下
(1)Dapper封装
public classDapperHelper
{public staticOracleConnection OracleConnection()
{string oracleConnectionStr = System.Configuration.ConfigurationManager.AppSettings["OracleConn"].ToString();var connection = newOracleConnection(oracleConnectionStr);
connection.Open();returnconnection;
}
}
(2)使用Dapper进行select查询
using (IDbConnection conn =DapperHelper.OracleConnection())
{string sqlCommandStr = @"select * from user";
List userList = conn.Query(sqlCommandStr).ToList();//todo
}