Enterprise-level development by connecting to Oracle databases based on Microsoft enterprise database 5.0

Enterprise-level development by connecting to oracle databases based on Microsoft enterprise database 5.0
Many people are used to connecting SQL Server with Microsoft enterprise database 5.0 for enterprise-level development.
The following describes that Microsoft enterprise database 5.0 has less online data to connect to the oracle database. There are many details to be aware.
Therefore, I wrote such a technical blog to share it with you. I hope you can avoid detours and reduce the number of programmers working overtime and staying up late.

Technical Architecture: UI Layer (aspx) + BLL layer + DAL layer (Microsoft enterprise database 5.0) + database (oracle ).
(1) Preparations
1. Download Oracle database and Development Kit

First register an account to the official Oracle website (www.Oracle.com)
Download the installation package 2 installation package oraclexe1__win32.zip (Oracle express),odtwithodac112030.zip (equivalent to. net development and connection to the Oracle database 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)oraclexe1__win32.zip (Oracle Database Express Edition 11g Release 2)
: Http://www.oracle.com/technetwork/products/express-edition/downloads/index.html

2. Download the Microsoft enterprise database 5.0
Http://entlib.codeplex.com/
Http://www.microsoft.com/en-us/download/details.aspx? Id = 15104

 

(2) Applications

After installing the preceding package, open VS2010> Tools> connect to the database.

Change Data Source

After successful connection

Database Operations after successful connection

 

1. Configure the database connection string
<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. Key code for adding, deleting, modifying, and querying basic operations
Create a table SQL script:
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) Add
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) Delete
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) modify
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) Query
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;
Note:
1) Many SQL Server functions are not available in oracle.
For example, the ISNULL function cannot be used in oracle.
2) You cannot use "@" to use ":" To pass parameters.
For example:
Written in SQL Server
SELECT * FROM C_SysCode where ParentCodeNO = @ ParentCodeNO
Oracle needs to be written
SELECT * FROM C_SysCode where ParentCodeNO =: ParentCodeNO
3) Pay attention to the parameter transmission Sequence
4) Pay attention to the differences in SQL syntax
For example, "[]" cannot be used.
Written in SQL Server
SELECT * FROM [C_SysCode]
Oracle needs to be written
SELECT * FROM C_SysCode
(3) Running Effect