基于微软企业库5.0连接oracle数据库进行企业级开发 很多人都习惯与用微软企业库5.0连接Sql Server进行企业级开发。 下面讲述微软企业库5.0连接oracle数据库的网上资料比较少,里面有很多细节点需要注意。 因此写一篇这样的技术博客共享给大家,目的希望大家少走弯路,希望能够减少程序员加班熬夜的数量。
技术架构: UI层(aspx)+BLL层+DAL层(微软企业库5.0)+数据库(oracle)。 (一)准备工作 1.Oracle数据库及开发包下载
先注册一个账户到Oracle官方网站(www.Oracle.com) 下载安装包2个安装包OracleXE112_Win32.zip(Oracle Express版),ODTwithODAC112030.zip(相当于.net开发连接Oracle数据库SDK) 1)ODTwithODAC112030.zip(ODAC 11.2 Release 4 (11.2.0.3.0) with Oracle Developer Tools for Visual Studio) 下载地址:http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html
2)OracleXE112_Win32.zip(Oracle Database Express Edition 11g Release 2) 下载地址:http://www.oracle.com/technetwork/products/express-edition/downloads/index.html
2.微软企业库5.0下载 http://entlib.codeplex.com/ http://www.microsoft.com/en-us/download/details.aspx?id=15104
(二)应用
安装完以上程序包后打开VS2010->工具->连接到数据库
更改数据源
连接成功后
连接成功后操作数据库
1.配置数据库连接字符串 <dataConfiguration defaultDatabase= "ORACLE"/> <connectionStrings> <add name="ORACLE" connectionString="DATA SOURCE=10.168.78.188;PASSWORD=xxx;PERSIST SECURITY INFO=True;USER ID=SYSTEM" providerName= "Oracle.DataAccess.Client"/> </connectionStrings> 2.增删改查基本操作的关键代码 建立表sql脚本: CREATE TABLE "C_SYSCODE" ( "CODEID" NUMBER(10,0) NOT NULL, "CODENO" VARCHAR2(8 CHAR) NOT NULL, "CODENAME" VARCHAR2(60 CHAR) NOT NULL, "ISMX" VARCHAR2(20 BYTE) NOT NULL, "PARENTCODENO" VARCHAR2(8 CHAR) NOT NULL, "ZT" VARCHAR2(20 BYTE) NOT NULL) STORAGE ( NEXT 1048576 )
/ CREATE UNIQUE INDEX "SYS_C007211" ON "C_SYSCODE" ( "CODEID") / ALTER TABLE "C_SYSCODE" ADD ( CONSTRAINT "SYS_C007211"
PRIMARY KEY ( "CODEID") USING INDEX "SYS_C007211" ENABLE
VALIDATE ) /
1)增加 string strSql = "INSERT INTO C_SysCode(CodeID,CODENO,CODENAME,ISMX,ParentCodeNO,ZT)VALUES(:CodeID,:CODENO,:CODENAME,:ISMX,:ParentCodeNO,:ZT)"; DbCommand dbCommand = db.GetSqlStringCommand(strSql); db.AddInParameter(dbCommand, "CodeID", DbType.Int32, model.CodeID); db.AddInParameter(dbCommand, "CODENO", DbType.String, model.CODENO); db.AddInParameter(dbCommand, "CODENAME", DbType.String, model.CODENAME); db.AddInParameter(dbCommand, "ISMX", DbType.String, model.ISMX); db.AddInParameter(dbCommand, "ParentCodeNO", DbType.String, model.ParentCodeNO); db.AddInParameter(dbCommand, "ZT", DbType.String, model.ZT); db.ExecuteNonQuery(dbCommand); 2)删除 string strSql = "DELETE FROM C_SysCode WHERE CodeID=:CodeID "; DbCommand dbCommand = db.GetSqlStringCommand(strSql); db.AddInParameter(dbCommand, "CodeID", DbType.String, CodeID); db.ExecuteNonQuery(dbCommand); 3)修改 string strSql = "UPDATE C_SysCode SET CODENO=:CODENO,CODENAME=:CODENAME,ISMX=:ISMX,ParentCodeNO=:ParentCodeNO,ZT=:ZT WHERE CodeID=:CodeID"; DbCommand dbCommand = db.GetSqlStringCommand(strSql); db.AddInParameter(dbCommand, "CODENO", DbType.String, model.CODENO); db.AddInParameter(dbCommand, "CODENAME", DbType.String, model.CODENAME); db.AddInParameter(dbCommand, "ISMX", DbType.String, model.ISMX); db.AddInParameter(dbCommand, "ParentCodeNO", DbType.String, model.ParentCodeNO); db.AddInParameter(dbCommand, "ZT", DbType.String, model.ZT); db.AddInParameter(dbCommand, "CodeID", DbType.Int16, model.CodeID); db.ExecuteNonQuery(dbCommand); 4)查询 StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT CodeID,CODENO,CODENAME,ISMX,ParentCodeNO,ZT "); strSql.Append(" FROM C_SysCode "); strSql.Append(" WHERE CodeID=:CodeID ");
DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()); db.AddInParameter(dbCommand, "CodeID", DbType.String, CodeID);
SFS.Model.C_SysCode model = null; using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { if (dataReader.Read()) { ... } } return model; 注意细节点: 1)Sql Server很多函数在oracle不能用。 例如:ISNULL函数在oracle中不能用。 2)传递参数不能用“@”要用“:” 例如: Sql Server中写成 SELECT * FROM C_SysCode where ParentCodeNO=@ParentCodeNO Oracle要写成 SELECT * FROM C_SysCode where ParentCodeNO=:ParentCodeNO 3)注意参数传递顺序 4)注意Sql语法的差别 例如:不能用“[]”。 Sql Server中写成 SELECT * FROM [C_SysCode] Oracle要写成 SELECT * FROM C_SysCode (三)运行效果