An explanation of how SQL Server translates to Oracle in the ASP.

This article mainly introduces the information about the conversion of SQL Server into Oracle in ASP, and the steps introduced by the example code and the text will be very detailed, which has a certain reference value to the study or work of everyone. The friends who need to study together below.

Objective

Because the previous period of time our company project to the SQL Server to Oracle, found that there is less information on the Internet, so here to share the experience, but also record the problem, the following words do not say, come together to see the detailed introduction:

At first I studied for some time and then downloaded the

Oracle 11g version and PL/SQL (client), unlike the one that Oracle does not have its own client needs to run with third-party software PL/SQL is a sqldeveloper is also a, PL/SQL I feel a little more stable. But 2 of them are all installed in a complementary way.

Oracle's easy-to-listen-nothing error can be found in

Http://www.jb51.net/article/91184.htm

and then use

Create table spaces and users these are available on the web.

All right, everything's installed. Start SQL Server to Oracle

The first is the conversion of the database I tried a lot of ways, are more or less a bit of a problem, because it is 2 different databases, and finally I decided to write a program to convert

Code to paste it out

Link string

<add key= "oracleconnectionstring" value= "password=123; User id=sa;data source= (description= (address_list= (address=) (PROTOCOL=TCP) (Host=localhost)) (PORT=1521 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 {Publ IC static string oracleconnectionstring = system.configuration.configurationsettings.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 succeeded"; }} 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 Button1_Click (object sender, EventArgs e) {if (TextBox1.Text = = "") {DataTable tablenames = GetTable  Names (); foreach (DataRow item in Tablenames. Rows) {String tablename = item["Name"]. ToString ().   ToUpper ();  SetData (tablename);  }} else {SetData (TextBox1.Text); } label2. Text = "Success"; } private static void SetData (String tablename) {//Find there is no this table if not added int et = 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[fieldName "].   ToString (); if (citem["type"]. ToString () = = "int" | | citem["type"]. ToString () = = "Bit" | | citem["type"]. ToString () = = "decimal") {cs + = "Number (" + (Convert.ToInt32 (citem["Length"]) > 38? 38:convert.toint32 (citem["Length")) + (Convert.ToInt32 (citem["decimal Place"]) >0? ("," +convert.toint32 (citem["decimal Place"]))   + ")"; } 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["id"]. ToString () = = "1") {Biaoshi = citem["field name"].   ToString (); } cs + = citem["Default value"]. ToString ()! = ""? "Default" + citem["defaults".   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 Determine if there is a serial number is not created is automatically identified int xuliehao = 0; try {Xuliehao = Convert.ToInt32 (Getsingle (String.   Format (@ "Select Seq_{0}.nextval from Sys.dual", TableName)); } catch {} if (Xuliehao <= 0) {#region in order for the sequence to not repeat the maximum value to the min value int max = Convert.ToInt32 (Getsingle (String.   Format ("select Max ({1}) from {0}", TableName, Biaoshi), null)); #endregion string sequence = string. Format (@ "Create sequence seq_{0} start with {1} increment by 1 nomaxvalue minvalue 1 nocycle nocache", TableName, (max+1))   ;//create Identity getsingle (sequence); #endregion #region Create a sequence of triggers 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 Trigger Getsingle (CHUFAQISQL); #endregion #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.  Empty; 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.DateTime")) {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 + "(" + Columnsnames + ") 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" A.colorder=1 then d.name else null end) Table name, A.colorder field ordinal, a.name The field name, (case when ColumnProperty (A.id,a.name, ' isidentity ') =1 and then ' 1 ' else ' end) is identified (case if (SELECT count (*) from sysobjects where (the name in (the select name from sysindexes WHERE (id = a.id) and (Indid in (select Indid from Sysindexke YS WHERE (id = a.id) and (Colid in (SELECT colid from syscolumns WHERE (id = a.id) and (name = A.name))))) and (XT ype = ' PK ') >0 then ' 1 ' else ' end) primary key, b.name type, a.length bytes occupied, columnproperty (A.id,a.name, ' PRECISION ') as length, is Null (ColumnProperty (a.id,a.name, ' scale '), 0) as decimal place, (case time a.isnullable=1 then ' 1 ' else ' end) allows null, replace (replace ( IsNull (E.text, '), ' (', '), ') ', ') default value, IsNull (G.[value], ') as [description] from syscolumns a left join systypes B on a.xtype= B.xusertype INNER join sysobjects D on a.id=d.id and d.xtype= ' U ' and d.name<> ' dtproperties ' left join Syscomment S E on a.cdefault=e.id left joins Sys.extended_properties G on a.id=g.major_id and a.colid=g.minor_id left join Sys.exte Nded_properties F on D.ID=f.class and F.minor_id=0 where b.name is isn't null and D.name= ' {0} ' ORDER by A.id,a.colorder ', tableName);  DataSet ds = Query (sql); Return DS. Tables[0]; The public static DataSet Query (string SQLString) {using (SqlConnection connection = new SqlConnection (sqlserverconnection  String) {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 OracleConnection (Oracleco nnectionstring) {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 {returnObj   }} catch (OracleException e) {throw e; }}}} private static void PrepareCommand (OracleCommand cmd, OracleConnection conn, oracletransaction trans, string cmd Text, oracleparameter[] cmdparms) {if (conn. state! = ConnectionState.Open) Conn.  Open (); Cmd.  Connection = conn;  Cmd.commandtext = Cmdtext; if (trans! = null) cmd.  Transaction = trans;  Cmd.commandtype = Commandtype.text;//cmdtype; if (cmdparms! = null) {foreach (OracleParameter parameter in cmdparms) {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[] cmdparms) {using (SqlConnection connection = new SqlConnection (sqlserverconnectionstring)) {using (SqlCommand cmd = new SqlCommand ()) {try {Preparecomma nd (cmd, connection, NULL, SqlstriNg, cmdparms); 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 static void PrepareCommand (SqlCommand cmd, SqlConnection conn, sqltransaction trans, string cmdtext, SQL Parameter[] cmdparms) {if (conn. state! = ConnectionState.Open) Conn.  Open (); Cmd.  Connection = conn;  Cmd.commandtext = Cmdtext; if (trans! = null) cmd.  Transaction = trans;  Cmd.commandtype = Commandtype.text;//cmdtype; if (cmdparms! = null) {foreach (SqlParameter parameter in cmdparms) {if (parameter. Direction = = Parameterdirection.inputoutput | | Parameter. Direction = = ParameterDirection.Input) && (parameter. Value = = null) {parameter.   Value = DBNull.Value; } cmd.  Parameters.Add (parameter); }  } } }}

This code is adapted to my database conversion you can change it if you need it.

Where Oracle is not self-growing, but sequences other sequences can be triggered by triggers the trouble is a little bit of the whole thing.

And then I posted the link on my original program string to Oracle.

First the System.Data.SqlClient; reference is changed toSystem.Data.OracleClient;

And get rid of the wrong place in the newspaper. Change from SQL to Oracle

And then I post something that doesn't give an error but SQL Server and Oracle are different places

How many data before querying

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

Convert other formats into string format

Select To_char (other format, string format) asselect to_char(sysdate,'yyyy-mm-dd') from dual

String interception

(truncated string, starting 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 parameterized placeholder, cannot be used with @:

SELECT * from Table where id=:id

System time

getdate()Change intosysdate

Gets the current auto-Grow column ID

Select Seq_table.currval from dual

Seq_table is the name of the autogrow column, each table is different, so you need to find the current

Table sets the name of the auto-grow column corresponding to

After the completion of the program will not error the operation

There's going to be 32 and 64 bits of what's broken. There are all kinds of statements on the Internet. Change the project attribute to X86 X64

But it doesn't work for me, and I think these things are going to cause a lot of problems with my original project.

So I studied for a long time and finally came up with a few results

First, if it's a console or a form program, just tick the 32-bit preference in the project.

If it's an ASP, it's not that simple.

I'm a 64-bit system, and then I install Oracle and the client is 64-bit, so I shouldn't have to report 64 mistakes.

And then said online to install the 32-bit instant Client setup

And then I'm going to have a program installed,

But I used the same method on the server again, and found a lot of information eventually reinstall IIS OK (in fact, to re-register the framework)

Installing a 32-bit version is best as with the Oracle version

At that time, I had a lot of revisions. debugging sometimes when installing Instant Client setup will be stuck at a point, you can try a few more times if it is not a re-find it!

After installation, you can find it in the Control Panel.

Also post backup restore commands with CMD open to run modify 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