ibatis.net Project database SQL Server migration to Oracle

Recently completed a (IBATIS.NET+MVC) project database + code migration work, you can toss me ~ ~ ~

Ibatis.net is an ORM framework, specifically introduced can ask Niang. I didn't use the ORM framework before, so I didn't come here, but I climbed out of a hole and fell into another pit ~ ~ ~

The project was originally Sqlserver2008, and now I'm going to go to Oracle, so I finished the data migration first and then the Code migration.

Database migration

1. Database Installation and Configuration

Skip over.

2. Table Structure Migration

1) Create a Physicaldatamodel,dbms select Sqlserver2008 with PowerDesigner;

2) Select Database->connect ... to connect to SQL Server database;

3) Select Database->update Model from Database ..., get the table structure and view of SQL Server database, do not get constraint relationship , etc. (it will affect data import);

4) Select Database->change Current DBMS ..., select new DBMS as ORACLE10GR2;

5) Modify the user to change the original dbo to the schema name (username) of our Oracle.

90% of the work PowerDesigner has been completed for me, the rest is to modify the field type and length of work.

Modify the field type and the length of the section, mainly including:

1) Change the converted number and integer type to number (10). SQL Server self-increment sequence after conversion by default is number (6), this is certainly not enough;

2) Change the converted float type to number (12,2);

3) Increase the VARCHAR2 length by 1 time times, such as varchar (50) in SQL Server, set to VARCHAR2 (in Oracle) ( NOTE: varchar (50) is different from VARCHAR2 (50) ）。

Once the table field type and length have been modified, the tables are created in bulk.

3. Data import

Data import fails with Sqlserver2008 DTS, which typically has the following types of data import failures:

1) The field type length in the target table is not enough;

2) There is a constraint relationship between tables, such as importing data into a child table first, and no data for the primary table.

After the DTS import is complete, there is no return to the previous function unless an import error occurs. This is too impersonal, too unkind ~ ~ ~

4, the main work has been completed, the rest is to create sequences and stored procedures things. This part of the database is basically done. In the Type field length, I rework 1 times-_-has more than 100 sheets, pit AH ~ ~ ~

Code Migration for projects

1, ibatis configuration file modification

Providers.config file included in

<provider name="oracleClient1.0" description="Oracle, Microsoft provider V1.0.5000.0" enabled="true" assemblyName="System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" connectionClass="System.Data.OracleClient.OracleConnection" commandClass="System.Data.OracleClient.OracleCommand" parameterClass="System.Data.OracleClient.OracleParameter" parameterDbTypeClass="System.Data.OracleClient.OracleType" parameterDbTypeProperty="OracleType" dataAdapterClass="System.Data.OracleClient.OracleDataAdapter" commandBuilderClass="System.Data.OracleClient.OracleCommandBuilder" usePositionalParameters="false" useParameterPrefixInSql="true" useParameterPrefixInParameter="false" parameterPrefix=":" allowMARS="false" />

Connection string settings in the Sqlmap.config file

?

<database>   <provider name="oracleClient1.0" />   <dataSource name="ORCL" connectionString="Data Source=ORCL;user=user name;password=password"/> </database>

2. Installation of Oracle Client

began to think that Oracle9i Lite client can be done, the result of the old tip: Unable to open connection to "Oracle, Microsoft provider V1.0.5000.0". So download and install the ORACLE10GR2 client, or not. So I found a ibatis.net+oracle on the Internet WinForm version of the demo, the test is normal, you can confirm that is not a configuration file and connection string problems. It is not possible to port code to a Web project, even if it is deployed to IIS and simulates a 32-bit run (the development machine is a win7x64 system) is an error. 32-bit mode will prompt for a higher client version when running. Is it a newly installed client when the newly added path in the environment variable does not take effect? So hug to try the mentality, restart the computer, finally God is Favor program Ape ~ ~ ~ To fix.

3. SQL Statement Code Modification

1) Dbtype=int are replaced with Dbtype=number

2) Encounter < etc will destroy the XML file format symbol, put it in <! [cdata[...] > In

3) Oracle's self-increment sequence

?

<insert id="Insert" parameterClass="Account">        <selectKey property="UserID" resultClass="int" type="pre">          SELECT SEQ_ACCOUNT_ID.NEXTVAL AS VALUE FROM DUAL        </selectKey>        INSERT INTO Account (        USERID        ,Name        , AccountNo        , MobileNo        , Password        ) VALUES (        #UserID#        ,#Name,dbType=VarChar#        , #AccountNo,dbType=VarChar#        , #MobileNo,dbType=VarChar#        , #Password,dbType=VarChar#        )      </insert>

?

<resultMaps>        <resultMap id="FullResultMap" class="Account">            <result property="UserID" column="UserID" dbType="Int"/>

?

<select id="XXXXXXXX" parameterClass="Hashtable" resultMap="NonLobResultMap">   SELECT * FROM (   SELECT rownum as rn   ,ID   , Title   FROM Article   WHERE (OwnerID = #OwnerID,dbType=Number#)   ORDER BY CreateTime DESC   ) tb   <![CDATA[   where rn <= $ReturnCount$   ]]> </select>

?

<select id="Search" parameterClass="Hashtable" resultMap="FullResultMap">   SELECT *   From(   SELECT rownum as RowNumber,Article.*   FROM  Article   <include refid="SearchWhere"></include>   <![CDATA[   order by ID DESC   )  tb where RowNumber >=( ($PageIndex$ - 1) * $PageSize$ + 1) and RowNumber<=( $PageIndex$ * $PageSize$)   ]]> </select>

?

<select id="XXX" parameterClass="Citys" resultMap="FullResultMap" extends="FindAll">       where  (Namelike ‘%‘|| #Name,dbType=VarChar#||‘%‘)     </select>

?

<parameterMaps>   <!-- Stored procedure parameter -->   <parameterMap id="parm_sp_XXXXXX" class="Hashtable">     <parameter property="AAAAAA" column="IN_AAAAAA" direction="Input" />     <parameter property="BBBBBB" column="IN_BBBBBB" direction="Input" />     <parameter property="Result" column="OUT_REF_CUR" dbType="Cursor" direction="Output" />   </parameterMap> </parameterMaps>   <statements>   <!-- Stored procedure -->   <procedure id="GetXXXXXXXXXXXX" parameterMap="parm_sp_XXXXXX" resultMap="YYYYYYYYYYYYY">     proc_Stored procedure name   </procedure>   </stateme

9) SELECT *, and then add other fields, you need to add the table name in front of the *, such as t.*

10) then change some SQL commands to Oracle's commands

SQL statements in the script file, it is recommended that stored procedures, parameters, sequences, and so on are capitalized.

Summarize

First Contact ibatis.net, through the code generator for us to complete most of the project, the development is indeed very convenient, in the late to do the database migration, the workload is much less than the traditional Factory mode or IOC, modified directly on the corresponding XML modification, very intuitive and convenient.

ibatis.net Project Database SQL Server to Oracle