Detailed description of how to convert SQL server to oracle developed by asp.net, sqloracle

Detailed description of how to convert SQL server to oracle developed by asp.net, sqloracle

Preface

Some time ago, our company had to transfer SQL server to oracle, and found that there was less information on the Internet. So I would like to share my experiences and record the problems here. I will not talk much about them below, let's take a look at the detailed introduction:

I started researching for a while and then downloaded it.

Oracle 11g and PL/SQL (client) are different from SQL server. oracle does not have its own client and needs to use third-party software to run PL/SQL, PL/SQL I think it is more stable. But if both of them are installed, they will complement each other.

Oracle is prone to errors without listening. For more information, see

Http://www.bkjia.com/article/91184.htm

And then use

Create a table space and search for the table space on the Internet.

After everything is installed, SQL server is switched to oracle.

First of all, I have tried many methods for database conversion, but it is a problem more or less. Because it is two different databases, I decided to write a program for conversion.

Post Code

Link string

<add key="OracleConnectionString" value="Password=123;User ID=SA;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost )(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)))"/>  <add key="SqlServerConnectionString" value="server=localhost;database=Table;uid=sa;pwd=123"/>

Using System; using System. collections. generic; using System. componentModel; using System. data; using System. data. oracleClient; using System. data. sqlClient; using System. drawing; using System. linq; using System. text; using System. threading. tasks; using System. windows. forms; namespace TransplantSQL {public partial class Form1: Form {public static string OracleConnectionString = System. configuration. configura TionSettings. appSettings ["OracleConnectionString"]; public static string SqlServerConnectionString = System. configuration. configurationSettings. appSettings ["SqlServerConnectionString"]; public Form1 () {InitializeComponent ();} private void button2_Click (object sender, EventArgs e) {OracleConnection con = new OracleConnection (OracleConnectionString ); try {con. open (); if (con. state = System. data. ConnectionState. open) {label5.Text = "connection successful" ;}} catch (OracleException se) {label5.Text = "connection failed" ;}finally {con. close () ;}} private void button3_Click (object sender, EventArgs e) {SqlConnection con = new SqlConnection (SqlServerConnectionString); try {con. open (); if (con. state = System. data. connectionState. open) {label4.Text = "connection succeeded";} catch (SqlException se) {label4.Text = "connection failed ";} Finally {con. close () ;}} private void button#click (object sender, EventArgs e) {if (textBox1.Text = "") {DataTable tablenames = GetTableNames (); foreach (DataRow item in tablenames. rows) {string tablename = item ["Name"]. toString (). toUpper (); setdata (tablename) ;}} else {setdata (textBox1.Text);} label2.Text = "successful";} private static void setdata (string tablename) {// check whether the table exists. If not, add int e. T = Convert. toInt32 (GetSingle ("select count (*) from user_tables where table_name = '" + tablename + "'"); if (et <= 0) {DataTable tableInfo = GetTableInfo (tablename); string addtablesql = "create table {0} ({1})"; string cs = string. empty; string biaoshi = string. empty; foreach (DataRow citem in tableInfo. rows) {cs + = citem ["field name"]. toString (); if (citem ["type"]. toString () = "int" | citem ["type"]. to String () = "bit" | citem ["type"]. toString () = "decimal") {cs + = "NUMBER (" + (Convert. toInt32 (citem ["length"])> 38? 38: Convert. ToInt32 (citem ["length"]) + (Convert. ToInt32 (citem [""])> 0? ("," + Convert. toInt32 (citem [""]): "") + ")";} else if (citem ["type"]. toString () = "nvarchar" | citem ["type"]. toString () = "float") {cs + = "VARCHAR2 (" + (Convert. toInt32 (citem ["length"]) =-1? 4000: Convert. toInt32 (citem ["length"]) * 2) + ")";} else if (citem ["type"]. toString () = "datetime") {cs + = "DATE";} cs + = citem ["primary key"]. toString () = "1 "? "Primary key": ""; if (citem [""]. toString () = "1") {biaoshi = citem ["field name"]. toString ();} cs + = citem ["Default Value"]. toString ()! = ""? "Default" + citem ["default"]. toString () + "": ""; cs + = citem ["allow null"]. toString () = "1 "? ",": "Not null,";} cs = cs. substring (0, cs. length-1); string tempsql = string. format (addtablesql, tablename, cs); GetSingle (tempsql); if (biaoshi! = String. empty) {# region automatically identifies int xuliehao = 0 if no serial number is available. try {xuliehao = Convert. toInt32 (GetSingle (string. format (@ "select Seq _ {0 }. nextval from sys. dual ", tablename);} catch {} if (xuliehao <= 0) {# region in order to keep the sequence from repeating, the maximum value is int max = Convert. toInt32 (GetSingle (string. format ("select max ({1}) from {0}", tablename, biaoshi), null); # endregion string sequence = string. format (@ "create sequ Ence Seq _ {0} start with {1} increment by 1 nomaxvalue minvalue 1 nocycle nocache ", tablename, (max + 1); // create an ID GetSingle (sequence );} # endregion # create a sequence trigger string chufaqisql = string. format (@ "create or replace trigger t _ {0} before insert on {0} for each row when (new. {1} is null) begin select Seq _ {0 }. nextval into: new. {1} from dual; end; ", tablename, biaoshi); // create the trigger GetSingle (chufaqisql); # en Dregion # region create a unique constraint // string weiyisql = string. format (@ "create unique index U _ {0} on {0} ({1})", tablename, biaoshi); // GetSingle (weiyisql ); # endregion} // int count = Convert. toInt32 (GetSingle ("SELECT count (1) FROM" + tablename); // if (count <10000) // {DataSet ds = Query ("SELECT * FROM" + tablename); DataTable dt = ds. tables [0]; string columnsNames = string. empty; string values = string. E Mpty; for (int I = 0; I <dt. columns. count; I ++) {columnsNames + = dt. columns [I]. columnName + ",";} columnsNames = columnsNames. substring (0, columnsNames. length-1); foreach (DataRow dr in dt. rows) {values = string. empty; for (int I = 0; I <dt. columns. count; I ++) {if (dr [I]! = DBNull. Value) {if (dr [I]. ToString ()! = "") {If (dt. columns [I]. dataType = Type. getType ("System. double ") | dt. columns [I]. dataType = Type. getType ("System. decimal ") | dt. columns [I]. dataType = Type. getType ("System. int32 ") {values + = dr [I] +", ";} else if (dt. columns [I]. dataType = Type. getType ("System. string ") {values + =" '"+ dr [I]. toString (). replace ('\ '', ''') +"', ";} else if (dt. columns [I]. dataType = Type. getType ("System. D AteTime ") {values + =" to_date ('"+ dr [I] +"', 'yyyy/MM/DD HH24: MI: ss '),";} else if (dt. columns [I]. dataType = Type. getType ("System. boolean ") {if (dr [I]. toString () = "False") {values + = "0,";} else {values + = "1 ,";}}} else {values + = "chr (32)," ;}} else {values + = "NULL," ;}} values = values. substring (0, values. length-1); string osql = "Insert into" + tablename + "(" + columnsNam Es + ") values (" + values + ")"; GetSingle (osql) ;}//}} private static DataTable GetTableNames () {string SQL = string. format (@ "SELECT Name FROM SysObjects Where XType = 'U' order by Name"); DataSet ds = Query (SQL); return ds. tables [0];} private static DataTable GetTableInfo (string tableName) {string SQL = string. format (@ "SELECT (case when. colorder = 1 then d. name else null end) Table name,. colorder Field number,. name field name, (case when COLUMNPROPERTY (. id,. name, 'isidentity ') = 1 then '1' else' end) identifier, (case when (SELECT count (*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id =. id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id =. id) AND (colid in (SELECT colid FROM syscolumns WHERE (id =. id) AND (name =. name) AND (xtype = 'pk')> 0 then'1 'else' end) Primary Key, B. name type,. length occupies the number of bytes, COLUMNPROPERTY (. id,. name, 'precision ') as length, isnull (COLUMNPROPERTY (. id,. name, 'Scale'), 0) as decimal places, (case when. isnullable = 1 then '1' else' end) allows null, REPLACE (isnull (e. text, ''), '(',''), ')', '') default value, isnull (g. [value], '') AS [description] FROM syscolumns a left join policypes B on. xtype = B. xusertype inner join sysobjects d on. id = d. id and d. xtype = 'U' and d. name <> 'dtpro Perties 'left join syscomments e on. cdefault = e. id left join sys. extended_properties g on. id = g. major_id AND. colid = g. minor_id left join sys. extended_properties f on d. id = f. class and f. minor_id = 0 where B. name is not null And d. name = '{0}' order by. id,. colorder ", tableName); DataSet ds = Query (SQL); return ds. tables [0];} public static DataSet Query (string SQLString) {using (SqlConnection con Nection = new SqlConnection (SqlServerConnectionString) {DataSet ds = new DataSet (); try {connection. open (); SqlDataAdapter command = new SqlDataAdapter (SQLString, connection); command. fill (ds, "ds");} catch (System. data. sqlClient. sqlException ex) {throw new Exception (ex. message);} return ds ;}} public static object GetSingle (string SQLString) {using (OracleConnection connection = new Oracl EConnection (OracleConnectionString) {using (OracleCommand cmd = new OracleCommand () {try {PrepareCommand (cmd, connection, null, SQLString, null); object obj = cmd. executeScalar (); cmd. parameters. clear (); if (Object. equals (obj, null) | (Object. equals (obj, System. DBNull. value) {return null;} else {return obj ;}} catch (OracleException e) {throw e ;}}} private static void PrepareCo Mmand (OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string parameter text, OracleParameter [] partition parms) {if (conn. State! = ConnectionState. Open) conn. Open (); cmd. Connection = conn; cmd. CommandText = plain text; if (trans! = Null) cmd. Transaction = trans; cmd. CommandType = CommandType. Text; // specify type; if (partition parms! = Null) {foreach (OracleParameter parameter in parameter parms) {if (parameter. direction = ParameterDirection. inputOutput | parameter. direction = ParameterDirection. input) & (parameter. value = null) {parameter. value = DBNull. value;} cmd. parameters. add (parameter) ;}} public static object GetSingle (string SQLString, params SqlParameter [] partition parms) {using (SqlConnection connection = new SqlCon Nection (SqlServerConnectionString) {using (SqlCommand cmd = new SqlCommand () {try {PrepareCommand (cmd, connection, null, SQLString, prepare parms); object obj = cmd. executeScalar (); cmd. parameters. clear (); if (Object. equals (obj, null) | (Object. equals (obj, System. DBNull. value) {return null;} else {return obj;} catch (System. data. sqlClient. sqlException e) {throw e ;}}} private stati C void PrepareCommand (SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string plain text, SqlParameter [] partition parms) {if (conn. State! = ConnectionState. Open) conn. Open (); cmd. Connection = conn; cmd. CommandText = plain text; if (trans! = Null) cmd. Transaction = trans; cmd. CommandType = CommandType. Text; // specify type; if (partition parms! = Null) {foreach (SqlParameter parameter in parameter parms) {if (parameter. direction = ParameterDirection. inputOutput | parameter. direction = ParameterDirection. input) & (parameter. value = null) {parameter. value = DBNull. value;} cmd. parameters. add (parameter );}}}}}

This code can be modified to meet your database conversion needs.

Oracle does not grow on its own, but it is a series of other sequences that can be triggered by triggers. It's good to say that at one o'clock.

Then I changed my original program string link to the oracle link and posted it.

FirstSystem.Data.SqlClient;Change referenceSystem.Data.OracleClient;

After reading the error, you can change it from SQL to Oracle.

Then I posted some non-error messages, but the difference between sqlserver and oracle

Query the first data entry

select * from (SELECT * FROM Table) where rownum<100

Convert other formats into string formats

Select to_char (other formats, string formats) as shown inselect to_char(sysdate,'yyyy-mm-dd') from dual

String Truncation

(Truncated string, start position, length)

select substr('111222',3,2) from dual

Convert string format to time format

select to_date('2017-08-03','yyyy-mm-dd') from dual

SQL parameter placeholders, which cannot be:

SELECT * FROM Table where ID=:ID

System time

getdate()Changesysdate

Obtain the ID of the current auto-increment Column

select Seq_Table.currval from dual

Seq_Table is the name of the auto-increment column. Each table is different, so you need to find the current

Name of the auto-increment column set in the table

After all the changes are completed, the program will not report an error.

There will be 32-bit and 64-bit bugs. All the online saying is that the project attribute is changed to X86 X64.

However, this is useless to me and I think it will cause a lot of problems for my original project after I change it.

So after a long study, I finally got several results.

First, if it is a console or form program, you can simply hook the first 32 bits in the project.

If it's an asp.net page program, it's not that simple.

I use a 64-bit system on the local machine, and then I have installed oracle and the client with 64-bit data. In principle, I should not report a 64-bit dislocation to me.

Then I will install 32-bit Instant Client Setup on the Internet.

Then I can install a program,

However, I failed to use the same method on the server again. I found a lot of information and finally re-installed iis (in fact, I had to re-register the framework)

It is best to install the 32-bit version the same as the oracle version.

At that time, I got a lot of versions for debugging. Sometimes, when installing Instant Client Setup, it will be stuck at a certain point. You can try it several times. If it still doesn't work, try again!

After installation, you can find it in the control panel.

In addition, the Backup Recovery command is pasted and opened with CMD to run and modify the corresponding parameters.

Exp sa/123@ORCL file=C:\OracleBack\back_%date:~0,4%%date:~5,2%%date:~8,2%.dmp owner=saIMP sa/123 BUFFER=64000 FILE=C:\OracleBack\back_20170821.dmp FROMUSER=SA TOUSER=SA

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.