asp.net 开发 sql server 转 oracle
前段时间我们公司项目 要把sql server 转oracle
分享一下心得 也记录一下问题
开始我研究了一段时间 然后下载了
oracle 11g 版本 和 PL/SQL（客户端） 和sql server 不同的是 oracle 没有自己的客户端 需要用第三方的软件运行 PL/SQL 就是一个 sqldeveloper 也是一个，PL/SQL 我觉得比较稳定一点。但是2个都安装的话 刚好互补了
oracle 容易出现 无监听 什么 的错误 可以参考
http://jingyan.baidu.com/article/03b2f78c7a0ab75ea237ae33.html
然后再用 
创建表空间 和用户 这些网上都找得到的
好了 东西都安装好了 下面开始 sql server 转 oracle
首先是数据库的转换 我试了很多种方式，都是多多少少都是有点问题，因为是2个不同的数据库，最后我还是决定自己写个程序 转换
代码贴出来
链接字符串
1 <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)))"/> 2 3 <add key="SqlServerConnectionString" value="server=localhost;database=Table;uid=sa;pwd=123"/>
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Data.OracleClient; 6 using System.Data.SqlClient; 7 using System.Drawing; 8 using System.Linq; 9 using System.Text; 10 using System.Threading.Tasks; 11 using System.Windows.Forms; 12 13 namespace TransplantSQL 14 { 15 public partial class Form1 : Form 16 { 17 public static string OracleConnectionString = System.Configuration.ConfigurationSettings.AppSettings["OracleConnectionString"]; 18 public static string SqlServerConnectionString = System.Configuration.ConfigurationSettings.AppSettings["SqlServerConnectionString"]; 19 public Form1() 20 { 21 InitializeComponent(); 22 } 23 24 private void button2_Click(object sender, EventArgs e) 25 { 26 OracleConnection con = new OracleConnection(OracleConnectionString); 27 try 28 { 29 con.Open(); 30 if (con.State == System.Data.ConnectionState.Open) 31 { 32 label5.Text = "连接成功"; 33 } 34 } 35 catch (OracleException se) 36 { 37 label5.Text = "连接失败"; 38 } 39 40 finally 41 { 42 con.Close(); 43 } 44 } 45 46 private void button3_Click(object sender, EventArgs e) 47 { 48 SqlConnection con = new SqlConnection(SqlServerConnectionString); 49 try 50 { 51 con.Open(); 52 if (con.State == System.Data.ConnectionState.Open) 53 { 54 label4.Text = "连接成功"; 55 } 56 } 57 catch (SqlException se) 58 { 59 label4.Text = "连接失败"; 60 } 61 62 finally 63 { 64 con.Close(); 65 } 66 } 67 68 private void button1_Click(object sender, EventArgs e) 69 { 70 if (textBox1.Text == "") 71 { 72 DataTable tablenames = GetTableNames(); 73 foreach (DataRow item in tablenames.Rows) 74 { 75 string tablename = item["Name"].ToString().ToUpper(); 76 setdata(tablename); 77 } 78 } 79 else 80 { 81 setdata(textBox1.Text); 82 } 83 84 label2.Text = "成功"; 85 } 86 87 private static void setdata(string tablename) 88 { 89 // 查找有没有此表 如果没有就加 90 int et = Convert.ToInt32(GetSingle("select count(*) from user_tables where table_name = '" + tablename + "'")); 91 if (et <= 0) 92 { 93 DataTable tableInfo = GetTableInfo(tablename); 94 string addtablesql = "CREATE TABLE {0}({1})"; 95 string cs = string.Empty; 96 string biaoshi = string.Empty; 97 foreach (DataRow citem in tableInfo.Rows) 98 { 99 cs += citem["字段名"].ToString(); 100 if (citem["类型"].ToString() == "int" || citem["类型"].ToString() == "bit" || citem["类型"].ToString() == "decimal") 101 { 102 cs += " NUMBER(" + (Convert.ToInt32(citem["长度"]) > 38 ? 38 : Convert.ToInt32(citem["长度"])) + (Convert.ToInt32(citem["小数位数"])>0?(","+Convert.ToInt32(citem["小数位数"])):"") + ")"; 103 } 104 else if (citem["类型"].ToString() == "nvarchar" || citem["类型"].ToString() == "float") 105 { 106 cs += " VARCHAR2(" + (Convert.ToInt32(citem["长度"]) == -1 ? 4000 : Convert.ToInt32(citem["长度"]) * 2) + ")"; 107 } 108 else if (citem["类型"].ToString() == "datetime") 109 { 110 cs += " DATE"; 111 } 112 113 cs += citem["主键"].ToString() == "1" ? " primary key " : ""; 114 if (citem["标识"].ToString() == "1") 115 { 116 biaoshi = citem["字段名"].ToString(); 117 } 118 cs += citem["默认值"].ToString() != "" ? " default " + citem["默认值"].ToString() + " " : ""; 119 cs += citem["允许空"].ToString() == "1" ? "," : " NOT NULL,"; 120 } 121 cs = cs.Substring(0, cs.Length - 1); 122 string tempsql = string.Format(addtablesql, tablename, cs); 123 GetSingle(tempsql); 124 if (biaoshi != string.Empty) 125 { 126 #region 判断是否有序列号 没有就创建 就是自动标识 127 int xuliehao = 0; 128 try 129 { 130 xuliehao = Convert.ToInt32(GetSingle(string.Format(@"select Seq_{0}.nextval from sys.dual", tablename))); 131 } 132 catch { } 133 if (xuliehao <= 0) 134 { 135 #region 为了让序列不重复 取最大值为min 值 136 int max = Convert.ToInt32(GetSingle(string.Format("select max({1}) from {0}", tablename, biaoshi),null)); 137 #endregion 138 string sequence = string.Format(@"create sequence Seq_{0} start with {1} increment by 1 nomaxvalue minvalue 1 nocycle nocache", tablename, (max+1));//创建标识 139 GetSingle(sequence); 140 } 141 #endregion 142 #region 创建序列的触发器 143 string chufaqisql = string.Format(@"CREATE OR REPLACE TRIGGER T_{0} 144 BEFORE INSERT ON {0} FOR EACH ROW WHEN (new.{1} is null) 145 begin 146 select Seq_{0}.nextval into:new.{1} from dual; 147 end;", tablename, biaoshi);//创建触发器 148 GetSingle(chufaqisql); 149 #endregion 150 #region 创建唯一约束 151 //string weiyisql = string.Format(@"create unique index U_{0} on {0} ({1})", tablename, biaoshi); 152 //GetSingle(weiyisql); 153 #endregion 154 } 155 //int count = Convert.ToInt32(GetSingle("SELECT count(1) FROM " + tablename)); 156 //if (count < 10000) 157 //{ 158 DataSet ds = Query("SELECT * FROM " + tablename); 159 DataTable dt = ds.Tables[0]; 160 string columnsNames = string.Empty; 161 string values = string.Empty; 162 for (int i = 0; i < dt.Columns.Count; i++) 163 { 164 columnsNames += dt.Columns[i].ColumnName + ","; 165 166 } 167 columnsNames = columnsNames.Substring(0, columnsNames.Length - 1); 168 foreach (DataRow dr in dt.Rows) 169 { 170 values = string.Empty; 171 for (int i = 0; i < dt.Columns.Count; i++) 172 { 173 if (dr[i] != DBNull.Value) 174 { 175 if (dr[i].ToString() != "") 176 { 177 if (dt.Columns[i].DataType == Type.GetType("System.Double") 178 || dt.Columns[i].DataType == Type.GetType("System.Decimal") 179 || dt.Columns[i].DataType == Type.GetType("System.Int32")) 180 { 181 values += dr[i] + ","; 182 } 183 else if (dt.Columns[i].DataType == Type.GetType("System.String")) 184 { 185 values += "'" + dr[i].ToString().Replace('\'', '‘') + "',"; 186 } 187 else if (dt.Columns[i].DataType == Type.GetType("System.DateTime")) 188 { 189 values += "to_date('" + dr[i] + "','YYYY/MM/DD HH24:MI:SS'),"; 190 } 191 else if (dt.Columns[i].DataType == Type.GetType("System.Boolean")) 192 { 193 if (dr[i].ToString() == "False") 194 { 195 values += "0,"; 196 } 197 else { values += "1,"; } 198 } 199 } 200 else 201 { 202 values += "chr(32),"; 203 } 204 } 205 else 206 { 207 values += "NULL,"; 208 } 209 } 210 values = values.Substring(0, values.Length - 1); 211 string osql = "Insert into " + tablename + "(" + columnsNames + ") values(" + values + ")"; 212 GetSingle(osql); 213 } 214 //} 215 } 216 } 217 218 private static DataTable GetTableNames() 219 { 220 string sql = string.Format(@"SELECT Name FROM SysObjects Where XType='U' ORDER BY Name"); 221 DataSet ds = Query(sql); 222 return ds.Tables[0]; 223 } 224 private static DataTable GetTableInfo(string tableName) 225 { 226 string sql = string.Format(@"SELECT (case when a.colorder=1 then d.name else null end) 表名, 227 a.colorder 字段序号,a.name 字段名, 228 (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '' end) 标识, 229 (case when (SELECT count(*) FROM sysobjects 230 WHERE (name in (SELECT name FROM sysindexes 231 WHERE (id = a.id) AND (indid in 232 (SELECT indid FROM sysindexkeys 233 WHERE (id = a.id) AND (colid in 234 (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) 235 AND (xtype = 'PK'))>0 then '1' else '' end) 主键,b.name 类型,a.length 占用字节数, 236 COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度, 237 isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '1'else '' end) 允许空, 238 REPLACE(REPLACE(isnull(e.text,''),'(',''),')','') 默认值,isnull(g.[value], ' ') AS [说明] 239 FROM syscolumns a 240 left join systypes b on a.xtype=b.xusertype 241 inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 242 left join syscomments e on a.cdefault=e.id 243 left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id 244 left join sys.extended_properties f on d.id=f.class and f.minor_id=0 245 where b.name is not null 246 And d.name='{0}' 247 order by a.id,a.colorder", tableName); 248 DataSet ds = Query(sql); 249 return ds.Tables[0]; 250 } 251 252 public static DataSet Query(string SQLString) 253 { 254 using (SqlConnection connection = new SqlConnection(SqlServerConnectionString)) 255 { 256 DataSet ds = new DataSet(); 257 try 258 { 259 connection.Open(); 260 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); 261 command.Fill(ds, "ds"); 262 } 263 catch (System.Data.SqlClient.SqlException ex) 264 { 265 throw new Exception(ex.Message); 266 } 267 return ds; 268 } 269 } 270 271 public static object GetSingle(string SQLString) 272 { 273 using (OracleConnection connection = new OracleConnection(OracleConnectionString)) 274 { 275 using (OracleCommand cmd = new OracleCommand()) 276 { 277 try 278 { 279 PrepareCommand(cmd, connection, null, SQLString, null); 280 object obj = cmd.ExecuteScalar(); 281 cmd.Parameters.Clear(); 282 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 283 { 284 return null; 285 } 286 else 287 { 288 return obj; 289 } 290 } 291 catch (OracleException e) 292 { 293 throw e; 294 } 295 } 296 } 297 } 298 299 private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms) 300 { 301 if (conn.State != ConnectionState.Open) 302 conn.Open(); 303 cmd.Connection = conn; 304 cmd.CommandText = cmdText; 305 if (trans != null) 306 cmd.Transaction = trans; 307 cmd.CommandType = CommandType.Text;//cmdType; 308 if (cmdParms != null) 309 { 310 311 312 foreach (OracleParameter parameter in cmdParms) 313 { 314 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && 315 (parameter.Value == null)) 316 { 317 parameter.Value = DBNull.Value; 318 } 319 cmd.Parameters.Add(parameter); 320 } 321 } 322 } 323 324 public static object GetSingle(string SQLString, params SqlParameter[] cmdParms) 325 { 326 using (SqlConnection connection = new SqlConnection(SqlServerConnectionString)) 327 { 328 using (SqlCommand cmd = new SqlCommand()) 329 { 330 try 331 { 332 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 333 object obj = cmd.ExecuteScalar(); 334 cmd.Parameters.Clear(); 335 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 336 { 337 return null; 338 } 339 else 340 { 341 return obj; 342 } 343 } 344 catch (System.Data.SqlClient.SqlException e) 345 { 346 throw e; 347 } 348 } 349 } 350 } 351 352 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms) 353 { 354 if (conn.State != ConnectionState.Open) 355 conn.Open(); 356 cmd.Connection = conn; 357 cmd.CommandText = cmdText; 358 if (trans != null) 359 cmd.Transaction = trans; 360 cmd.CommandType = CommandType.Text;//cmdType; 361 if (cmdParms != null) 362 { 363 364 365 foreach (SqlParameter parameter in cmdParms) 366 { 367 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && 368 (parameter.Value == null)) 369 { 370 parameter.Value = DBNull.Value; 371 } 372 cmd.Parameters.Add(parameter); 373 } 374 } 375 } 376 } 377 }
这个代码适应于我的数据库转换 大家需要的话 可以修改一下
其中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
(11G中有个新特性，当表无数据时，不分配segment，以节省空间，这样就会没有导出数据 最蠢最实用的方法就是插入一条数据 然后删除掉 这样分配了segment 就可以导出了)
IMP sa/123 BUFFER=64000 FILE=C:\OracleBack\back_20170821.dmp FROMUSER=SA TOUSER=SA
