SQLServer與Oracle數(shù)據(jù)遷移報(bào)告

1、SQL Server 與 Oracle數(shù)據(jù)交換組 長(zhǎng)：組 員：學(xué) 院：專 業(yè)：教 師：一、設(shè)計(jì)目的綜合運(yùn)用所學(xué)Web數(shù)據(jù)庫(kù)以及c#編程相關(guān)知識(shí),查詢SQL Server、Oracle數(shù)據(jù)庫(kù)的相關(guān)信息，完成數(shù)據(jù)從SQL Server到Oracle的遷移二、設(shè)計(jì)要求v 顯示SQL Server 2000數(shù)據(jù)庫(kù)中的對(duì)象v 顯示所有視圖v 允許通過(guò)選擇某一個(gè)或幾個(gè)字段，將數(shù)據(jù)讀出并且保存在某個(gè)XML文件中v 完成數(shù)據(jù)從SQL Server到Oracle的遷移 三、程序環(huán)境數(shù)據(jù)庫(kù): SQL Server 2000, Oracle10g開發(fā)工具: Microsoft Visual Studio 2008開

2、發(fā)技術(shù): ASP.NET4、 系統(tǒng)詳細(xì)設(shè)計(jì)1.顯示SQL Server 2000數(shù)據(jù)庫(kù)中的對(duì)象在第一個(gè)DropDownList控件利用自動(dòng)綁定的功能，新建與SQLServer2000的master庫(kù)的連接，運(yùn)用SQL查詢語(yǔ)句在表SYSOBJECTS中查出所有數(shù)據(jù)庫(kù)名，并綁定到控件中。設(shè)置另一個(gè)DropDownList控件用來(lái)讓用戶選擇想要查詢的數(shù)據(jù)庫(kù)對(duì)象。數(shù)據(jù)庫(kù)對(duì)象有視圖、索引、表結(jié)構(gòu)、存儲(chǔ)過(guò)程、用戶、觸發(fā)器和函數(shù)這幾項(xiàng)。再設(shè)置另外一個(gè)DropdownList控件，用手動(dòng)綁定數(shù)據(jù)的方法，利用控件的SelectView的方法將數(shù)據(jù)名保存在變量dataBaseName中，然后通過(guò)SQL語(yǔ)句查詢出這

3、個(gè)庫(kù)的所有表名，包括系統(tǒng)表和用戶表。設(shè)置Datagridview控件，用來(lái)接收查詢結(jié)果。在查詢按鈕控件中設(shè)置事件，當(dāng)按下按鈕根據(jù)所想查詢的數(shù)據(jù)庫(kù)名，表名和對(duì)象名使用sqlDataAdapter執(zhí)行sql語(yǔ)句，并用Dataset中的FILL方法將查詢結(jié)果存儲(chǔ)在Gridview的Datasource并綁定，這樣就能顯示出所查詢的數(shù)據(jù)庫(kù)對(duì)象的結(jié)果。2. 顯示所有視圖在第一個(gè)功能中只是把某個(gè)庫(kù)中所有的視圖名和相關(guān)的字段顯示出來(lái)，并沒有顯示出某個(gè)視圖的詳細(xì)信息。增加一個(gè)輸入框，用來(lái)輸入想要查詢的特點(diǎn)的視圖名，利用存儲(chǔ)過(guò)程查詢出建立視圖的語(yǔ)句并顯示在Gridview2控件中，利用查詢語(yǔ)句查詢出所選視圖的詳

4、細(xì)信息并顯示在Gridview3中。至此完成顯示所有視圖的功能。3. 將所查詢出的結(jié)果保存在某個(gè)XML文件中設(shè)置“保存XML”控件，當(dāng)按下控件后，將剛才的保存的datasource里的信息，通過(guò)WriteXML方法將數(shù)據(jù)保存為XML文件，通過(guò)所選擇的表面的變量來(lái)設(shè)置存儲(chǔ)的XML的文件名。4. 完成數(shù)據(jù)從SQLServer到ORacle的數(shù)據(jù)遷移設(shè)置導(dǎo)入Oracle按鈕的控件，在事件中打開與Oracle的連接，將剛才保存的XML文件讀入到DataSet中，然后在TableCheck()方法中實(shí)現(xiàn)在Oracle中建表，在TableInsert()方法中將XML文件的數(shù)據(jù)插入到表。完成數(shù)據(jù)從SQLS

5、erver2000到Oracle的遷移。5. 主要程序代碼/將所查詢的結(jié)果保存在XML文件中 protected void ButtonXML_Click(object sender, EventArgs e) const string ob1 = "視圖" const string ob2 = "索引" const string ob3 = "表結(jié)構(gòu)" const string ob4 = "存儲(chǔ)過(guò)程" const string ob5 = "函數(shù)" const string ob6 = &q

6、uot;用戶" const string ob7 = "觸發(fā)器" string dataBaseName = DropDownListSelectDB.SelectedValue; string dataBaseObject = DropDownListSelectDBO.SelectedValue; string dataBaseTable = DropDownListTable.SelectedValue; string sql = "Data Source=pc-201110171850;Integrated Security=True;Datab

7、ase = " + dataBaseName; SqlConnection thisConnection = new SqlConnection(sql); thisConnection.Open(); string sqlSelectTable = "use" + " " + dataBaseName + " select name from sysobjects where type = 'u' or type = 's'" string sqlselectneirong = "

8、use" + " " + dataBaseName + " select * from dbo." + dataBaseTable; string sqlSelectview = "use" + " " + dataBaseName + " select table_catalog as '庫(kù)名', table_schema,table_name as '視圖名',crdate as '創(chuàng)建時(shí)間',refdate as '更新時(shí)間'

9、 from sysobjects,information_schema.views where type = 'v' and table_name = name " string sqlSelectColid = "use" + " " + dataBaseName + " select SC.colid as '索引', SO.name as '表名',SC.name as '表列名',ST.name as '類型' from sysobjects SO

10、,syscolumns SC, systypes ST where SO.id = SC.id and SO.xtype = 'v' order by SC.colid" string sqlSelectProcedure = "use" + " " + dataBaseName + " select name as '存儲(chǔ)過(guò)程',id,uid as '用戶id' from sysobjects where type = 'p'" string sqlSelec

11、tUser = "use" + " " + dataBaseName + " select uid as '用戶id',name as '用戶', roles as '角色' from sysusers order by uid " string sqlSelectTf = "use" + " " + dataBaseName + " select x.id, as '表函數(shù)', x.uid as '

12、用戶id', as '用戶' from sysobjects x ,sysobjects y where x.uid = y.uid and x.type = 'TF' order by x.id" string sqlSelectFn = "use" + " " + dataBaseName + " select x.id, as '標(biāo)量函數(shù)', x.uid as '用戶id', as '用戶' from s

13、ysobjects x ,sysobjects y where x.uid = y.uid and x.type = 'FN' order by x.id" string sqlSelectIf = "use" + " " + dataBaseName + " select x.id, as '內(nèi)嵌表函數(shù)', x.uid as '用戶id', as '用戶' from sysobjects x ,sysobjects y where x.uid

14、= y.uid and x.type = 'IF' order by x.id" string sqlSelectTr = "use" + " " + dataBaseName + " select uid,name as '觸發(fā)器' from sysobjects where type = 'tr'"/"use" + " " + dataBaseName + "select name from sysobjects where

15、 type ='u'" switch (dataBaseObject) case ob1: SqlDataAdapter thisReader = new SqlDataAdapter(sqlSelectview, thisConnection); DataSet ds = new DataSet(); thisReader.Fill(ds); ds.WriteXml("D:Visual Studio 2008WebApplication4WebApplication4" + dataBaseTable + ".xml"); b

16、reak; case ob2: SqlDataAdapter thisReader = new SqlDataAdapter(sqlSelectColid, thisConnection); DataSet ds = new DataSet(); thisReader.Fill(ds); ds.WriteXml("D:Visual Studio 2008WebApplication4WebApplication4" + dataBaseTable + ".xml"); break; case ob3: SqlDataAdapter thisReader

17、= new SqlDataAdapter(sqlselectneirong, thisConnection); DataSet ds = new DataSet(); thisReader.Fill(ds); ds.WriteXml("D:Visual Studio 2008WebApplication4WebApplication4" + dataBaseTable + ".xml"); break; case ob4: SqlDataAdapter thisReader = new SqlDataAdapter(sqlSelectProcedure,

18、 thisConnection); DataSet ds = new DataSet(); thisReader.Fill(ds); ds.WriteXml("D:Visual Studio 2008WebApplication4WebApplication4" + dataBaseTable + ".xml"); break; case ob5: SqlDataAdapter thisReader = new SqlDataAdapter(sqlSelectTf, thisConnection); DataSet ds = new DataSet();

19、 thisReader.Fill(ds); ds.WriteXml("D:Visual Studio 2008WebApplication4WebApplication4" + dataBaseTable + ".xml"); break; case ob6: SqlDataAdapter thisReader = new SqlDataAdapter(sqlSelectUser, thisConnection); DataSet ds = new DataSet(); thisReader.Fill(ds); ds.WriteXml("D:V

20、isual Studio 2008WebApplication4WebApplication4" + dataBaseTable + ".xml"); break; case ob7: SqlDataAdapter thisReader = new SqlDataAdapter(sqlSelectTr, thisConnection); DataSet ds = new DataSet(); thisReader.Fill(ds); ds.WriteXml("D:Visual Studio 2008WebApplication4WebApplicatio

21、n4" + dataBaseTable + ".xml"); break; LabelResult.Text = "已將查詢的結(jié)果保存在XML文件中，其中路徑為D:Visual Studio 2008WebApplication4WebApplication4" ; thisConnection.Close(); #region 添加XML數(shù)據(jù)到數(shù)據(jù)庫(kù) private void TableInsert() string dataBaseTable = DropDownListTable.SelectedValue; OracleConnecti

22、on conn = new OracleConnection(strConn); try conn.Open(); / 遍歷DataTable中的Rows foreach (DataRow dr in dataTableXml.Rows) string sqlCmd = "Insert Into " + tableName + "(" / string sqlCmd = "Insert Into " + tableName + " (" / 遍歷Datatable的列 for (int i = 0; i <

23、dataTableXml.Columns.Count; i+) /添加列的字段名 sqlCmd += dataTableXml.Columnsi.ColumnName.ToString() + "," sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ") Values(" / 遍歷Datatable的列 for (int i = 0; i < dataTableXml.Columns.Count; i+) /添加列值到row sqlCmd += "'" + dri.To

24、String() + "'," sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ")" OracleCommand cmd = new OracleCommand(sqlCmd, conn); cmd.ExecuteNonQuery(); Message.Text = "Xml文件中的數(shù)據(jù)成功加載到數(shù)據(jù)庫(kù)中！" catch Message.Text = "在添加數(shù)據(jù)時(shí)產(chǎn)生了錯(cuò)誤！" finally conn.Close(); #endregion 6. 系統(tǒng)運(yùn)行結(jié)果截圖7. 系統(tǒng)調(diào)試通過(guò)功能測(cè)試，發(fā)現(xiàn)的問(wèn)題：1不能允許選定特定的一個(gè)或多個(gè)字段并保存為XML文件2在第三張表上進(jìn)行分頁(yè)查詢的操作會(huì)使整個(gè)頁(yè)面重載3在查詢出三個(gè)表后，表格的大小不一4不能固定表格的大小5不能直接在表中選定內(nèi)容進(jìn)行查詢五、系統(tǒng)關(guān)鍵知識(shí)簡(jiǎn)介:這次大作業(yè)的關(guān)鍵技術(shù)就是建立與數(shù)據(jù)庫(kù)的聯(lián)系，主要使用的就是ADO.NET(ActiveX Data Objects)。ADO(ActiveX Data Objects)是ActiveX數(shù)據(jù)訪問(wèn)對(duì)象的集合，