asp.net开发sql server转换成oracle的方法详解

前言

因为前段时间我们公司项目 要把sql server 转oracle，发现网上这方面的资料较少，所以在这里分享一下心得，也记录一下问题，下面话不多说了，来一起看看详细的介绍：

开始我研究了一段时间 然后下载了

oracle 11g 版本 和 pl/sql（客户端） 和sql server 不同的是 oracle 没有自己的客户端 需要用第三方的软件运行 pl/sql 就是一个 sqldeveloper 也是一个，pl/sql 我觉得比较稳定一点。但是2个都安装的话 刚好互补了

oracle 容易出现 无监听 什么 的错误 可以参考

然后再用

创建表空间 和用户 这些网上都找得到的

好了 东西都安装好了 下面开始 sql server 转 oracle

首先是数据库的转换 我试了很多种方式，都是多多少少都是有点问题，因为是2个不同的数据库，最后我还是决定自己写个程序 转换

代码贴出来

链接字符串

<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.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 = "连接成功";
  }
  }
  catch (oracleexception se)
  {
  label5.text = "连接失败";
  }

  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 = "连接成功";
  }
  }
  catch (sqlexception se)
  {
  label4.text = "连接失败";
  }

  finally
  {
  con.close();
  }
 }

 private void button1_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 = "成功";
 }

 private static void setdata(string tablename)
 {
  // 查找有没有此表 如果没有就加
  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["字段名"].tostring();
   if (citem["类型"].tostring() == "int" || citem["类型"].tostring() == "bit" || citem["类型"].tostring() == "decimal")
   {
   cs += " number(" + (convert.toint32(citem["长度"]) > 38 ? 38 : convert.toint32(citem["长度"])) + (convert.toint32(citem["小数位数"])>0?(","+convert.toint32(citem["小数位数"])):"") + ")";
   }
   else if (citem["类型"].tostring() == "nvarchar" || citem["类型"].tostring() == "float")
   {
   cs += " varchar2(" + (convert.toint32(citem["长度"]) == -1 ? 4000 : convert.toint32(citem["长度"]) * 2) + ")";
   }
   else if (citem["类型"].tostring() == "datetime")
   {
   cs += " date";
   }
   
   cs += citem["主键"].tostring() == "1" ? " primary key " : "";
   if (citem["标识"].tostring() == "1")
   {
   biaoshi = citem["字段名"].tostring();
   }
   cs += citem["默认值"].tostring() != "" ? " default " + citem["默认值"].tostring() + " " : "";
   cs += citem["允许空"].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 判断是否有序列号 没有就创建 就是自动标识
   int xuliehao = 0;
   try
   {
   xuliehao = convert.toint32(getsingle(string.format(@"select seq_{0}.nextval from sys.dual", tablename)));
   }
   catch { }
   if (xuliehao <= 0)
   {
   #region 为了让序列不重复 取最大值为min 值
   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));//创建标识
   getsingle(sequence);
   }
   #endregion
   #region 创建序列的触发器
   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);//创建触发器
   getsingle(chufaqisql);
   #endregion
   #region 创建唯一约束
   //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 (case when a.colorder=1 then d.name else null end) 表名, 
  a.colorder 字段序号,a.name 字段名,
  (case when columnproperty( a.id,a.name,'isidentity')=1 then '1'else '' end) 标识, 
  (case when (select count(*) from sysobjects 
  where (name in (select name from sysindexes 
  where (id = a.id) and (indid in 
  (select indid from sysindexkeys 
  where (id = a.id) and (colid in 
  (select colid from syscolumns where (id = a.id) and (name = a.name))))))) 
  and (xtype = 'pk'))>0 then '1' else '' end) 主键,b.name 类型,a.length 占用字节数, 
  columnproperty(a.id,a.name,'precision') as 长度, 
  isnull(columnproperty(a.id,a.name,'scale'),0) as 小数位数,(case when a.isnullable=1 then '1'else '' end) 允许空, 
  replace(replace(isnull(e.text,''),'(',''),')','') 默认值,isnull(g.[value], ' ') as [说明]
  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 syscomments e on a.cdefault=e.id 
  left join sys.extended_properties g on a.id=g.major_id and a.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 a.id,a.colorder", tablename);
  dataset ds = query(sql);
  return ds.tables[0];
 }

 public static dataset query(string sqlstring)
 {
  using (sqlconnection connection = 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 oracleconnection(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 preparecommand(oraclecommand cmd, oracleconnection conn, oracletransaction trans, string cmdtext, 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
   {
   preparecommand(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, sqlparameter[] 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);
  }
  }
 }
 }
}

这个代码适应于我的数据库转换 大家需要的话 可以修改一下

其中oracle 没有自增长的 ，，而是序列 另外序列可以用触发器触发 麻烦了一点 整的来说 还算好啦

然后就是把我原来的程序 字符串链接改成oracle 的链接 上面贴出来了

首先system.data.sqlclient;引用 改成system.data.oracleclient;

然后 在看报错的地方通通改掉就行了 从sql 改为 oracle 就行

然后我贴出一些 不报错 但是sqlserver 和oracle 不同的地方

查询前多少条数据

select * from (select * from table) where rownum<100

其他格式转化成字符串格式

select to_char(其他格式,字符串格式) 如select to_char(sysdate,'yyyy-mm-dd') from dual

字符串截取

（截取的字符串，开始的位置，长度）

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

字符串格式转化为时间格式

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

sql参数化占位符，不能用@用:

select * from table where id=:id

系统时间

getdate()改成sysdate

获取当前自动增长列id

select seq_table.currval from dual

seq_table是自动增长列的名字，每个表都不一样，所以需要找到当前

表设置的自动增长列对应的名字

都改完之后 等程序不报错了 运行

会出现32和64位的什么破问题 网上各种说法的都有 说项目属性中 改成 x86 x64

但是这些对我都不管用 并且我觉得 这些东西改了之后 会对我原有的项目造成很多问题

所以我研究了很久 最终得出几个结果

首先 如果是控制台 或窗体 程序的话 直接把项目中 首选32位 勾上就行了

如果是 asp.net 页面程序就没这么简单了

我本机是64位系统 然后我安装oracle 和客户端都是64位 按道理不应该给我报什么64位错啊

然后网上说要安装32位instant client setup

然后我就下了一个 安装了 程序就可以了 ，

但是我在服务器上面 再次用同样的方法又不行了，找了很多资料 最终 重新安装了一下iis好了（其实是要重新注册一遍framework）

安装32位的版本最好和oracle 版本一样

那时候我下了很多个版本 调试 有的时候 安装 instant client setup的时候会卡在一个点不动，可以多试几次 如果还是不行 在重新找一个吧！

安装完后 在控制面板里面可以找到的

另外贴出备份 还原的 命令 用cmd打开 运行 修改对应参数

exp sa/123@orcl file=c:\oracleback\back_%date:~0,4%%date:~5,2%%date:~8,2%.dmp owner=sa

imp sa/123 buffer=64000 file=c:\oracleback\back_20170821.dmp fromuser=sa touser=sa

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。