基于ArcGIS10.0和Oracle10g的空間數(shù)據(jù)管理平臺(tái)五（C#開發(fā)）-通用工具類

由于在介紹通用數(shù)據(jù)管理模塊功能的時(shí)候用到了許多通用的功能，例如word和excel與DataGridView控件的相互導(dǎo)入導(dǎo)出、寫日志到數(shù)據(jù)庫中等等。為了后面每一篇文章都能清楚的知道每一個(gè)功能具體是怎么實(shí)現(xiàn)的，現(xiàn)在先統(tǒng)一把用到的所有公用功能統(tǒng)一介紹和實(shí)現(xiàn)。由于SqlHelper類，也就是數(shù)據(jù)庫的一些通用操作已經(jīng)在登錄模塊中介紹，就不在重復(fù)介紹了。本篇將相介紹通用工具類CommonTools，由于代碼中有比較詳細(xì)的注釋了，就不詳細(xì)解析具體代碼的功能了。這個(gè)類實(shí)現(xiàn)的功能如下。

1.從DataGridView控件導(dǎo)出數(shù)據(jù)到Word文檔

        /// <summary>
        /// 從DataGridView控件導(dǎo)出數(shù)據(jù)到word文檔
        /// </summary>
        /// <param name="dgv"></param>
        public static void ExportDataGridViewToWord(DataGridView dgv)
        {
            //打開保存的文件名
            SaveFileDialog sfile = new SaveFileDialog();
            sfile.AddExtension = true;
            sfile.DefaultExt = ".doc";
            sfile.Filter = "(*.doc)|*.doc";
            if (sfile.ShowDialog() == DialogResult.OK)
            {
                object path = sfile.FileName;
                Object none = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.Application();
                Microsoft.Office.Interop.Word.Document document = wordApp.Documents.Add(ref none, ref none, ref none, ref none);
                //建立表格
                Microsoft.Office.Interop.Word.Table table = document.Tables.Add(document.Paragraphs.Last.Range, dgv.Rows.Count, dgv.Columns.Count, ref none, ref none);
                try
                {
                    //設(shè)置標(biāo)題
                    for (int i = 0; i < dgv.Columns.Count; i++)
                    {
                        table.Cell(0, i + 1).Range.Text = dgv.Columns[i].HeaderText;
                    }
                    //填充數(shù)據(jù)
                    for (int i = 1; i < dgv.Rows.Count; i++)
                    {
                        for (int j = 0; j < dgv.Columns.Count; j++)
                        {
                            table.Cell(i + 1, j + 1).Range.Text = dgv[j, i - 1].Value.ToString();
                        }
                    }
                    document.SaveAs(ref path, ref none, ref none, ref none, ref none, ref none,
                        ref none, ref none, ref none, ref none, ref none, ref none, ref none, 
                        ref none, ref none, ref none);
                    document.Close(ref none, ref none, ref none);
                    MessageBox.Show("導(dǎo)出成功！");
                }
                finally
                {
                    wordApp.Quit(ref none, ref none, ref none);
                }
            }
        }

2.導(dǎo)出DataGridView中的數(shù)據(jù)到excel，實(shí)現(xiàn)代碼如下：

        /// <summary>
        /// 導(dǎo)出DataGridView中的數(shù)據(jù)到excel
        /// </summary>
        /// <param name="dataGridview1">DataGridView控件</param>
        public static void ExportDataGridViewToExcel(DataGridView dataGridview1)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "導(dǎo)出Excel文件到";

            saveFileDialog.ShowDialog();

            Stream myStream;
            myStream = saveFileDialog.OpenFile();
            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
            string str = "";
            try
            {
                //寫標(biāo)題  
                for (int i = 0; i < dataGridview1.ColumnCount; i++)
                {
                    if (i > 0)
                    {
                        str += "\t";
                    }
                    str += dataGridview1.Columns[i].HeaderText;
                }

                sw.WriteLine(str);
                //寫內(nèi)容 
                for (int j = 0; j < dataGridview1.Rows.Count; j++)
                {
                    string tempStr = "";
                    for (int k = 0; k < dataGridview1.Columns.Count; k++)
                    {
                        if (k > 0)
                        {
                            tempStr += "\t";
                        }
                        tempStr += dataGridview1.Rows[j].Cells[k].Value.ToString();
                    }
                    sw.WriteLine(tempStr);
                }
                sw.Close();
                myStream.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                sw.Close();
                myStream.Close();
            }
        }

3.導(dǎo)入XML文件的數(shù)據(jù)到Oracle數(shù)據(jù)庫

        /// <summary>
        /// 導(dǎo)入XML文件的數(shù)據(jù)到Oracle數(shù)據(jù)庫中
        /// </summary>
        /// <param name="strXmlFileName">Xml文件名稱(完整的路徑)</param>
        /// <param name="strDatabaseName">導(dǎo)入的數(shù)據(jù)庫名稱</param>
        public static void ImportXmlToOracle(string strXmlFileName, string strDatabaseName)
        {
            NewDataSet nds = new NewDataSet();
            string sql = string.Empty;

            foreach (System.Data.DataTable dt in nds.Tables)
            {
                sql = "select * from ";
                sql += strDatabaseName + "_" + dt.TableName;

                OracleDataAdapter oda = new OracleDataAdapter(sql,
                                ConfigurationSettings.AppSettings["ConnectionString"]);
            
                OracleCommandBuilder builder = new OracleCommandBuilder(oda);
                oda.Fill(nds.Tables[dt.TableName]);
            }

            try
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(strXmlFileName);
                XmlNodeList nodeList = doc.GetElementsByTagName("tableType");
                string strTableType = string.Empty;
                foreach (XmlNode node in nodeList)
                {
                    if (node.Name == "tableType")
                    {
                        strTableType += node.InnerText + ";";
                    }
                }

                strTableType = strTableType.Substring(0, strTableType.Length - 1);
                foreach (XmlNode node in nodeList)
                {
                    if (node.Name == "tableType")
                    {
                        node.InnerText = strTableType;
                        break;
                    }
                }

                nodeList = doc.GetElementsByTagName("graphOverview");
                foreach (XmlNode node in nodeList)
                {
                    if (node.Name == "graphOverview")
                    {
                        string str = string.Empty;
                        if (node.InnerText.IndexOf(".jpg".ToUpper()) != -1)
                        {
                            str = node.InnerText.Substring(node.InnerText.IndexOf(".jpg".ToUpper()) + 4);
                        }
                        else if (node.InnerText.IndexOf(".jpg") != -1)
                        {
                            str = node.InnerText.Substring(node.InnerText.IndexOf(".jpg") + 4);
                        }
                        node.InnerText = str;
                        
                        break;
                    }
                }
                
                doc.Save("temp.xml");
                nds.ReadXml("temp.xml");
                System.IO.File.Delete("temp.xml");

                foreach (System.Data.DataTable dt in nds.Tables)
                {
                    if (dt.Rows.Count > 0)
                    {
                         string strTableName = strDatabaseName + "_" + dt.TableName;
                         sql = "select * from " + strTableName;
                         DataSet ds = new DataSet();

                         OracleDataAdapter oda = new OracleDataAdapter(sql,
                             ConfigurationSettings.AppSettings["ConnectionString"]);
                         OracleCommandBuilder builder = new OracleCommandBuilder(oda);
                         oda.Fill(ds);
                         oda.Update(dt);
                     }
                 }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                LogHelp.writeLog(FrmMain.metaUsername, "元數(shù)據(jù)目錄管理",
                    "導(dǎo)入元數(shù)據(jù)文件<" + strXmlFileName + ">失敗!");
            }
        }

這個(gè)函數(shù)主要用在元數(shù)據(jù)管理子系統(tǒng)，把XML格式的數(shù)據(jù)導(dǎo)入Oracle數(shù)據(jù)庫，這里XML格式的文件是通過一個(gè)XSD文件定義格式得到的，XSD文件就是Xml數(shù)據(jù)格式的約束文件，就是通過它定義XML具體的格式。然而微軟有一個(gè)工具可以根據(jù)這個(gè)XSD文件生成對(duì)應(yīng)的數(shù)據(jù)集類，在我的代碼里就是NewDataSet類。這個(gè)數(shù)據(jù)集類就可以管理對(duì)應(yīng)的格式的所有XML生成的表結(jié)構(gòu)數(shù)據(jù)。這里還需要說明一點(diǎn)的就是管理元數(shù)據(jù)需要一個(gè)單獨(dú)的元數(shù)據(jù)庫，也就是要求重新建立一個(gè)Oracle數(shù)據(jù)庫，但是用C#代碼創(chuàng)建Oracle數(shù)據(jù)庫確實(shí)很有難度，所以我選擇了一種比較巧妙的實(shí)現(xiàn)方式，就是把所有屬于元數(shù)據(jù)的表用前綴標(biāo)明（如metadata_名稱），這樣每次操作元數(shù)據(jù)庫就只需要找到相應(yīng)前綴的表就可以了，具體在元數(shù)據(jù)管理子系統(tǒng)有詳細(xì)介紹實(shí)現(xiàn)方案。

因?yàn)閄ML文件里面包含有圖片數(shù)據(jù)（經(jīng)過64base編碼的字符串表示），所以在處理XML文件需要先處理一下這個(gè)XML文件的圖片字段，才有上面新建一個(gè)臨時(shí)的XML文件，然后用數(shù)據(jù)集類對(duì)象讀入XML數(shù)據(jù)，然后刪除臨時(shí)的XML文件。最后還是例如數(shù)據(jù)集適配器來插入元數(shù)據(jù)到Oracle數(shù)據(jù)庫。

4.根據(jù)元數(shù)據(jù)的ID刪除一條元數(shù)據(jù)（從Oracle數(shù)據(jù)庫）：

        /// <summary>
        /// 根據(jù)元數(shù)據(jù)ID刪除一條元數(shù)據(jù)，一條元數(shù)據(jù)涉及到每一個(gè)元數(shù)據(jù)庫的表
        /// </summary>
        /// <param name="strMdid">元數(shù)據(jù)ID</param>
        /// <param name="strDatabaseName">元數(shù)據(jù)名稱</param>
        public static void DelOneMetaData(string strMdid, string strDatabaseName)
        {
            SqlHelper sh = new SqlHelper();
            OracleConnection conn = new OracleConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            conn.Open();
            //創(chuàng)建一個(gè) OracleTransaction 對(duì)象，然后調(diào)用 OracleConnection 對(duì)象
            //的 BeginTransaction() 方法啟動(dòng)事務(wù)。
            OracleTransaction trans = conn.BeginTransaction();
            //創(chuàng)建一個(gè) OracleCommand 對(duì)象，用于存儲(chǔ) SQL 語句。

            OracleCommand cmd = conn.CreateCommand();
            cmd.Transaction = trans;
            string sql = string.Empty;
            //xxx_metadata
            sql = "delete from " + strDatabaseName + "_metadata";
            sql += " where mdid like'" + strMdid + "'";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            sql = "select metadata_id from " + strDatabaseName + "_metadata";
            sql += " where mdid like'" + strMdid + "'";
            OracleDataReader odr1 = sh.ReturnDataReader(sql);
            if (odr1.Read())
            {
                //xxx_continfo
                sql = "delete from " + strDatabaseName + "_continfo";
                sql += " where metadata_id=" + odr1[0].ToString();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "select continfo_id from " + strDatabaseName + "_continfo";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataReader odr2 = sh.ReturnDataReader(sql);
                if (odr2.Read())
                {
                    //xxx_layerinfo
                    sql = "delete from " + strDatabaseName + "_layerinfo";
                    sql += " where continfo_id=" + odr2[0].ToString();
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    //xxx_tableinfo
                    sql = "delete from " + strDatabaseName + "_tableinfo";
                    sql += " where continfo_id=" + odr2[0].ToString();
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                //xxx_graph
                sql = "delete from " + strDatabaseName + "_graph";
                sql += " where metadata_id=" + odr1[0].ToString();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                //xxx_idpoc
                sql = "delete from " + strDatabaseName + "_idpoc";
                sql += " where metadata_id=" + odr1[0].ToString();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "select idpoc_id from " + strDatabaseName + "_idpoc";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataReader odr3 = sh.ReturnDataReader(sql);
                if (odr3.Read())
                {
                    //xxx_contact
                    sql = "delete from " + strDatabaseName + "_contact";
                    sql += " where idpoc_id=" + odr3[0].ToString();
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    sql = "select contact_id from " + strDatabaseName + "_contact";
                    sql += " where idpoc_id=" + odr3[0].ToString();
                    OracleDataReader odr4 = sh.ReturnDataReader(sql);
                    if (odr4.Read())
                    {
                        //xxx_cntadd
                        sql = "delete from " + strDatabaseName + "_cntadd";
                        sql += " where contact_id=" + odr4[0].ToString();
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();

                        //xxx_cntphone
                        sql = "delete from " + strDatabaseName + "_cntphone";
                        sql += " where contact_id=" + odr4[0].ToString();
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();

                        sql = "select cntphone_id from " + strDatabaseName + "_cntphone";
                        sql += " where contact_id=" + odr4[0].ToString();
                        OracleDataReader odr5 = sh.ReturnDataReader(sql);
                        if (odr5.Read())
                        {
                            //xxx_voicenum
                            sql = "delete from " + strDatabaseName + "_voicenum";
                            sql += " where cntphone_id=" + odr4[0].ToString();
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }

                    }
                }
                //xxx_keyword
                sql = "delete from " + strDatabaseName + "_keyword";
                sql += " where metadata_id=" + odr1[0].ToString();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                //xxx_onlinesrc
                sql = "delete from " + strDatabaseName + "_onlinesrc";
                sql += " where metadata_id=" + odr1[0].ToString();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "select onlinesrc_id from " + strDatabaseName + "_onlinesrc";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataReader odr6 = sh.ReturnDataReader(sql);
                if (odr6.Read())
                {
                    //xxx_dtbrlinkage
                    sql = "delete from " + strDatabaseName + "_dtbrlinkage";
                    sql += " where onlinesrc_id=" + odr6[0].ToString();
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

                    //xxx_dtdllinkage
                    sql = "delete from " + strDatabaseName + "_dtdllinkage";
                    sql += " where onlinesrc_id=" + odr6[0].ToString();
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                //xxx_tpcat
                sql = "delete from " + strDatabaseName + "_tpcat";
                sql += " where metadata_id=" + odr1[0].ToString();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                try
                {
                    trans.Commit();
                }
                catch (Exception)
                { 
                    trans.Rollback();
                    LogHelp.writeLog(FrmMain.metaUsername, "元數(shù)據(jù)編輯", "刪除元數(shù)據(jù)<" + strMdid + ">失敗！");
                    MessageBox.Show("刪除元數(shù)據(jù)<" + strMdid + ">失敗！");
                }
                finally
                {
                    conn.Close();
                }
            }
        }

因?yàn)楦鶕?jù)XSD文件解析生成的數(shù)據(jù)庫表有十幾個(gè)，所以對(duì)每一個(gè)表單獨(dú)采用事務(wù)操作，就是這十幾個(gè)表其實(shí)是一個(gè)整體，可以完整的表示一條元數(shù)據(jù)（元數(shù)據(jù)：描述數(shù)據(jù)的數(shù)據(jù)，這里就是描述空間數(shù)據(jù)特性的數(shù)據(jù)）。

5.根據(jù)元數(shù)據(jù)的ID從Oracle查詢得到一條元數(shù)據(jù)，和刪除一條元數(shù)據(jù)類似，也需要十幾個(gè)表一起操作：

       /// <summary>
        /// 根據(jù)元數(shù)據(jù)ID得到有一條元數(shù)據(jù)的數(shù)據(jù)集
        /// </summary>
        /// <param name="strMdid">元數(shù)據(jù)ID</param>
        /// <param name="strDatabaseName">元數(shù)據(jù)名稱</param>
        /// <returns></returns>
        public static NewDataSet GetOneMetaData(string strMdid, string strDatabaseName)
        {
            NewDataSet nds = new NewDataSet();
            SqlHelper sh = new SqlHelper();

            string sql = string.Empty;
            //xxx_metadata
            sql = "select * from " + strDatabaseName + "_metadata";
            sql += " where mdid like'" + strMdid + "'";
            OracleDataAdapter oda1 = new OracleDataAdapter(sql,
                ConfigurationSettings.AppSettings["ConnectionString"]);
            oda1.Fill(nds.Tables["metadata"]);

            sql = "select metadata_id from " + strDatabaseName + "_metadata";
            sql += " where mdid like'" + strMdid + "'";
            OracleDataReader odr1 = sh.ReturnDataReader(sql);
            if (odr1.Read())
            {
                //xxx_continfo
                sql = "select * from " + strDatabaseName + "_continfo";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataAdapter oda2 = new OracleDataAdapter(sql,
                    ConfigurationSettings.AppSettings["ConnectionString"]);
                oda2.Fill(nds.Tables["continfo"]);

                sql = "select continfo_id from " + strDatabaseName + "_continfo";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataReader odr2 = sh.ReturnDataReader(sql);
                if (odr2.Read())
                {
                    //xxx_layerinfo
                    sql = "select * from " + strDatabaseName + "_layerinfo";
                    sql += " where continfo_id=" + odr2[0].ToString();
                    OracleDataAdapter oda8 = new OracleDataAdapter(sql,
                        ConfigurationSettings.AppSettings["ConnectionString"]);
                    oda8.Fill(nds.Tables["layerinfo"]);
                    //xxx_tableinfo
                    sql = "select * from " + strDatabaseName + "_tableinfo";
                    sql += " where continfo_id=" + odr2[0].ToString();
                    OracleDataAdapter oda9 = new OracleDataAdapter(sql,
                        ConfigurationSettings.AppSettings["ConnectionString"]);
                    oda9.Fill(nds.Tables["tableinfo"]);
                }
                //xxx_graph
                sql = "select * from " + strDatabaseName + "_graph";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataAdapter oda3 = new OracleDataAdapter(sql,
                    ConfigurationSettings.AppSettings["ConnectionString"]);
                oda3.Fill(nds.Tables["graph"]);
                //xxx_idpoc
                sql = "select * from " + strDatabaseName + "_idpoc";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataAdapter oda4 = new OracleDataAdapter(sql,
                    ConfigurationSettings.AppSettings["ConnectionString"]);
                oda4.Fill(nds.Tables["idpoc"]);

                sql = "select idpoc_id from " + strDatabaseName + "_idpoc";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataReader odr3 = sh.ReturnDataReader(sql);
                if (odr3.Read())
                {
                    //xxx_contact
                    sql = "select * from " + strDatabaseName + "_contact";
                    sql += " where idpoc_id=" + odr3[0].ToString();
                    OracleDataAdapter oda12 = new OracleDataAdapter(sql,
                        ConfigurationSettings.AppSettings["ConnectionString"]);
                    oda12.Fill(nds.Tables["contact"]);

                    sql = "select contact_id from " + strDatabaseName + "_contact";
                    sql += " where idpoc_id=" + odr3[0].ToString();
                    OracleDataReader odr4 = sh.ReturnDataReader(sql);
                    if (odr4.Read())
                    {
                        //xxx_cntadd
                        sql = "select * from " + strDatabaseName + "_cntadd";
                        sql += " where contact_id=" + odr4[0].ToString();
                        OracleDataAdapter oda13 = new OracleDataAdapter(sql,
                            ConfigurationSettings.AppSettings["ConnectionString"]);
                        oda13.Fill(nds.Tables["cntadd"]);

                        //xxx_cntphone
                        sql = "select * from " + strDatabaseName + "_cntphone";
                        sql += " where contact_id=" + odr4[0].ToString();
                        OracleDataAdapter oda14 = new OracleDataAdapter(sql,
                            ConfigurationSettings.AppSettings["ConnectionString"]);
                        oda14.Fill(nds.Tables["cntphone"]);

                        sql = "select cntphone_id from " + strDatabaseName + "_cntphone";
                        sql += " where contact_id=" + odr4[0].ToString();
                        OracleDataReader odr5 = sh.ReturnDataReader(sql);
                        if (odr5.Read())
                        {
                            //xxx_voicenum
                            sql = "select * from " + strDatabaseName + "_voicenum";
                            sql += " where cntphone_id=" + odr4[0].ToString();
                            OracleDataAdapter oda15 = new OracleDataAdapter(sql,
                                ConfigurationSettings.AppSettings["ConnectionString"]);
                            oda15.Fill(nds.Tables["voicenum"]);
                        }

                    }
                }
                //xxx_keyword
                sql = "select * from " + strDatabaseName + "_keyword";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataAdapter oda5 = new OracleDataAdapter(sql,
                    ConfigurationSettings.AppSettings["ConnectionString"]);
                oda5.Fill(nds.Tables["keyword"]);
                //xxx_onlinesrc
                sql = "select * from " + strDatabaseName + "_onlinesrc";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataAdapter oda6 = new OracleDataAdapter(sql,
                    ConfigurationSettings.AppSettings["ConnectionString"]);
                oda6.Fill(nds.Tables["onlinesrc"]);

                sql = "select onlinesrc_id from " + strDatabaseName + "_onlinesrc";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataReader odr6 = sh.ReturnDataReader(sql);
                if (odr6.Read())
                {
                    //xxx_dtbrlinkage
                    sql = "select * from " + strDatabaseName + "_dtbrlinkage";
                    sql += " where onlinesrc_id=" + odr6[0].ToString();
                    OracleDataAdapter oda10 = new OracleDataAdapter(sql,
                        ConfigurationSettings.AppSettings["ConnectionString"]);
                    oda10.Fill(nds.Tables["dtbrlinkage"]);

                    //xxx_dtdllinkage
                    sql = "select * from " + strDatabaseName + "_dtdllinkage";
                    sql += " where onlinesrc_id=" + odr6[0].ToString();
                    OracleDataAdapter oda11 = new OracleDataAdapter(sql,
                        ConfigurationSettings.AppSettings["ConnectionString"]);
                    oda11.Fill(nds.Tables["dtdllinkage"]);
                }
                //xxx_tpcat
                sql = "select * from " + strDatabaseName + "_tpcat";
                sql += " where metadata_id=" + odr1[0].ToString();
                OracleDataAdapter oda7 = new OracleDataAdapter(sql,
                    ConfigurationSettings.AppSettings["ConnectionString"]);
                oda7.Fill(nds.Tables["tpcat"]);
            }
            return nds;
        }

6.導(dǎo)出Oracle數(shù)據(jù)庫的數(shù)據(jù)到XML文件中

        /// <summary>
        /// 導(dǎo)出Oracle數(shù)據(jù)庫的數(shù)據(jù)到XML文件中
        /// </summary>
        /// <param name="strXmlPath">導(dǎo)出的目錄</param>
        /// <param name="strDatabaseName">導(dǎo)出的數(shù)據(jù)庫名稱</param>
        public static void ExportXmlFromOracle(string strXmlPath, string strDatabaseName)
        {
            NewDataSet nds;
            SqlHelper sh = new SqlHelper();
            string sql = string.Empty;
            sql = "select mdid from " + strDatabaseName + "_metadata";

            OracleDataReader odr = sh.ReturnDataReader(sql);

            while (odr.Read())
            {
                string strMdid = odr[0].ToString();
                //根據(jù)元數(shù)據(jù)的ID得到一條元數(shù)據(jù)
                nds = GetOneMetaData(strMdid, strDatabaseName);

                nds.WriteXml(strXmlPath + "\\" + strMdid + ".xml");
            }
        }

7.下面兩個(gè)函數(shù)實(shí)現(xiàn)treeview控件向XML轉(zhuǎn)換，主要用于對(duì)元數(shù)據(jù)的編輯后保存到XML文件中。

        #region 遍歷tree并實(shí)現(xiàn)向XML的轉(zhuǎn)化
        /// <summary>      
        /// 遍歷treeview并實(shí)現(xiàn)向XML的轉(zhuǎn)化   
        /// </summary>      
        /// <param name="advTree">樹控件對(duì)象</param>      
        /// <param name="strXmlFilePath">XML輸出路徑</param> 
        public static void TreeToXML(AdvTree advTree, string strXmlFilePath)
        {
            XmlTextWriter xtw = new XmlTextWriter(strXmlFilePath, null);
            XmlDocument xmlDoc = new XmlDocument();
            //控制格式并寫XML的開頭
            xtw.Formatting = Formatting.Indented;
            xtw.WriteStartDocument(true);
            xtw.WriteStartElement("metadata");
            xtw.WriteStartAttribute("xsi","noNamespaceSchemaLocation", "http://www.w3.org/2001/XMLSchema-instance");
            xtw.WriteEndAttribute();
            xtw.WriteEndElement();
            xtw.WriteEndDocument();
            xtw.Close();

            xmlDoc.Load(strXmlFilePath);
            XmlNode xnRoot = xmlDoc.SelectSingleNode("metadata");

            TransTreeToXml(xmlDoc, advTree.FindNodeByName("metadata").Nodes, (XmlElement)xnRoot, strXmlFilePath);
        }

        /// <summary>
        /// 轉(zhuǎn)化Tree節(jié)點(diǎn)的數(shù)據(jù)到XML文檔
        /// </summary>
        /// <param name="xmlDoc">XML文檔對(duì)象</param>
        /// <param name="nodes">Tree的節(jié)點(diǎn)集合</param>
        /// <param name="xeRoot">根節(jié)點(diǎn)元素</param>
        /// <param name="strXmlPath">XML的節(jié)點(diǎn)路徑</param>
        private static void TransTreeToXml(XmlDocument xmlDoc, 
            NodeCollection nodes, XmlElement xeRoot, String strXmlPath)
        {
            XmlElement xe;
            //遍歷節(jié)點(diǎn)集合中的所有節(jié)點(diǎn)
            foreach (Node node in nodes)
            {
                //根據(jù)節(jié)點(diǎn)的名稱創(chuàng)建一個(gè)新的XML節(jié)點(diǎn)元素
                xe = xmlDoc.CreateElement(node.Name);
                //將新建的XML節(jié)點(diǎn)元素添加到父親節(jié)點(diǎn)（也就是傳遞進(jìn)來的根節(jié)點(diǎn)）
                xeRoot.AppendChild(xe);
                //如果節(jié)點(diǎn)還有子節(jié)點(diǎn)就遞歸調(diào)用本函數(shù)
                if (node.Nodes.Count > 0)
                {
                    TransTreeToXml(xmlDoc, node.Nodes, xe, strXmlPath);
                }
                else
                {
                    //因?yàn)門ree節(jié)點(diǎn)數(shù)據(jù)的格式是：（名稱：值），所以根據(jù)格式取出具體的值
                    string strTemp = node.Text.Substring(node.Text.IndexOf(':')+1);
                    //這個(gè)值不是空就填寫新建的XML節(jié)點(diǎn)的值
                    if (strTemp != "")
                    {
                        //特殊處理graphOverview節(jié)點(diǎn)，因?yàn)樗婕暗綀D形的二進(jìn)制數(shù)據(jù)，需要轉(zhuǎn)化為字符串
                        if (xe.Name == "graphOverview")
                        {
                            //以下就是將圖形二進(jìn)制數(shù)據(jù)轉(zhuǎn)化為字符串
                            Bitmap bmp = new Bitmap(strTemp);
                            MemoryStream ms = new MemoryStream();   
                            bmp.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);   
                            byte[] arr = new byte[ms.Length];   
                            ms.Position = 0;   
                            ms.Read(arr, 0, (int)ms.Length);   
                            ms.Close();   
                            String strbaser64 = Convert.ToBase64String(arr);
                            //給節(jié)點(diǎn)填充值
                            xe.InnerText = strbaser64;
                        }
                        else
                        {
                            //給節(jié)點(diǎn)填充值
                            xe.InnerText = strTemp;
                        }
                    }
                }
            }
            xmlDoc.Save(strXmlPath);
        }

        #endregion

8.遍歷XML并實(shí)現(xiàn)向tree控件的轉(zhuǎn)化

        #region 遍歷XML并實(shí)現(xiàn)向tree的轉(zhuǎn)化
        /// <summary>      
        /// 遍歷XML并實(shí)現(xiàn)向tree的轉(zhuǎn)化 
        /// </summary>      
        /// <param name="strXmlFilePath">XML輸入路徑</param>      
        /// <param name="advTree">樹控件對(duì)象</param>  
        public static void XMLToTree(string strXmlFilePath, AdvTree advTree)
        {
            //新建XML文檔對(duì)象并載入XML文件數(shù)據(jù)
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(strXmlFilePath);
            //找到根節(jié)點(diǎn)
            XmlNodeList xnl = xmlDoc.GetElementsByTagName("metadata");
            XmlNode root = xnl[0];
            //根據(jù)找到的根節(jié)點(diǎn)遍歷整個(gè)XML文件的節(jié)點(diǎn)
            foreach (XmlNode xn in root.ChildNodes)
            {
                //找到Tree對(duì)于的節(jié)點(diǎn)
                Node n = advTree.FindNodeByName(xn.Name);
                //如果有子節(jié)點(diǎn)就調(diào)用遞歸轉(zhuǎn)化函數(shù)
                if (n.HasChildNodes)
                {
                    TransXMLToTree(xn.ChildNodes, advTree);
                }
                else
                {
                    //如果節(jié)點(diǎn)的內(nèi)容不為空就為Tree控件節(jié)點(diǎn)添加值
                    if (xn.InnerText != "")
                    {
                        //標(biāo)記已經(jīng)有值，方便以后對(duì)Tree的編輯操作
                        n.Tag = "yes";
                        n.Text += xn.InnerText;
                        //如果此節(jié)點(diǎn)有父親節(jié)點(diǎn)就把對(duì)于的父親節(jié)點(diǎn)也標(biāo)記為有值
                        while (n.Parent != null)
                        {
                            Node np = n.Parent;
                            np.Tag = "yes";
                            n = n.Parent;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 遞歸轉(zhuǎn)化XML文件的節(jié)點(diǎn)到Tree的節(jié)點(diǎn)
        /// </summary>
        /// <param name="xmlNodes">XML文件的節(jié)點(diǎn)集合</param>
        /// <param name="advTree">Tree控件</param>
        private static void TransXMLToTree(XmlNodeList xmlNodes, AdvTree advTree)
        { 
            //循環(huán)遍歷節(jié)點(diǎn)集合
            foreach (XmlNode xn in xmlNodes)
            {
                //根據(jù)XML的節(jié)點(diǎn)找到對(duì)于的Tree節(jié)點(diǎn)
                Node n = advTree.FindNodeByName(xn.Name);
                //如果還有子節(jié)點(diǎn)就遞歸轉(zhuǎn)換
                if (n.HasChildNodes)
                {
                    TransXMLToTree(xn.ChildNodes, advTree);
                }
                else
                {
                    //如果節(jié)點(diǎn)的內(nèi)容不為空就為Tree控件節(jié)點(diǎn)添加值
                    if (xn.InnerText != "")
                    {
                        //標(biāo)記已經(jīng)有值，方便以后對(duì)Tree的編輯操作
                        n.Tag = "yes";
                        n.Text += xn.InnerText;
                        //如果此節(jié)點(diǎn)有父親節(jié)點(diǎn)就把對(duì)于的父親節(jié)點(diǎn)也標(biāo)記為有值
                        while (n.Parent != null)
                        {
                            Node np = n.Parent;
                            np.Tag = "yes";
                            n = n.Parent;
                        }
                    }
                }
            }
        }
        #endregion

9.將DataGridView顯示的內(nèi)容導(dǎo)出Excel

        /// <summary>
        /// 直接將DataGridView顯示的內(nèi)容導(dǎo)出Excel
        /// </summary>
        /// <param name="m_DataView"></param>
        public static void DataToExcel(DataGridView m_DataView)
        {
            int iRows = 0;   
            int iCols = 0;   
            int iTrueCols = 0;   
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();   
            Microsoft.Office.Interop.Excel._Workbook wb = app.Workbooks.Add(System.Reflection.Missing.Value);   
            Microsoft.Office.Interop.Excel._Worksheet ws = null;  
            if (wb.Worksheets.Count > 0)   
            {    
                ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);  
            }  
            else  
            {   
                wb.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, 
                    System.Reflection.Missing.Value, System.Reflection.Missing.Value);   
                ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);   
            }   
            if (ws != null)    
            {   
                ws.Name = "sheet1";
                iRows = m_DataView.Rows.Count;          //加上列頭行   
                iTrueCols = m_DataView.Columns.Count;   //包含隱藏的列，一共有多少列    
                //求列數(shù)，省略Visible = false的列   
                for (int i = 0; i < m_DataView.Columns.Count; i++)   
                {   
                   if (m_DataView.Columns[i].Visible) iCols++;   
                }   
                string[,] dimArray = new string[iRows + 1, iCols];   
 
                for (int j = 0, k = 0; j < iTrueCols; j++)   
                {   
                    //省略Visible = false的列
                    if (m_DataView.Columns[j].Visible)   
                    {   
                        dimArray[0, k] = m_DataView.Columns[j].HeaderText;   
                        k++;   
                    }   
                }   
                for (int i = 0; i < iRows; i++)   
                {   
                    for (int j = 0, k = 0; j < iTrueCols; j++)   
                    {   
                        //省略Visible = false的列
                        if (m_DataView.Columns[j].Visible)   
                        {
                            if (m_DataView.Rows[i].Cells[j].Value != null)
                            {
                                dimArray[i + 1, k] = m_DataView.Rows[i].Cells[j].Value.ToString();
                            }
                               
                            k++;   
                        }   
                    }   
                }    
                ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows + 1, iCols]).Value2 = dimArray;     
                ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iCols]).Font.Bold = true;   
                ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows + 1, iCols]).Font.Size = 10.0;    
                ws.get_Range(ws.Cells[1, 1], ws.Cells[iRows + 1, iCols]).RowHeight = 14.25;   
               //ws.Columns[.ColumnWidth = datagridview.Columns[0].Width;   
                for (int j = 0, k = 0; j < iTrueCols; j++)   
                {   
                    //省略Visible = false的列
                    if (m_DataView.Columns[j].Visible)   
                    {   
                        ws.get_Range(ws.Cells[1, k + 1], ws.Cells[1, k + 1]).ColumnWidth =
                            (m_DataView.Columns[j].Width / 8.4) > 255 ? 255 : (m_DataView.Columns[j].Width / 8.4);   
                        //ws.Columns.c = datagridview.Columns[j].Width;   
                        k++;   
                    }   
                }   
            }
            //設(shè)置禁止彈出保存和覆蓋的詢問提示框   
            app.DisplayAlerts = false;
            app.AlertBeforeOverwriting = false;
            app.Visible = false;
            //保存工作簿  
            try
            {
                app.Save("temp");
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                app.UserControl = false;
                app.Quit();
                File.Delete("temp.xlwx");
            }
        }

10.excel數(shù)據(jù)導(dǎo)入到DataGridView控件中

        /// <summary>
        /// Excel數(shù)據(jù)導(dǎo)入方法
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="dgv"></param>
        public static void EcxelToDataGridView(string filePath, DataGridView dgv)
        { 
            try
            {
                //根據(jù)路徑打開一個(gè)Excel文件并將數(shù)據(jù)填充到DataSet中
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0'";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataSet ds = null;
                strExcel = "select  * from   [sheet1$]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                myCommand.Fill(ds, "table1");

                //根據(jù)Excel的行逐一對(duì)上面構(gòu)造的DataTable的列進(jìn)行賦值
                foreach (DataRow excelRow in ds.Tables[0].Rows)
                {
                    int i = 0;
                    DataRow dr = ((System.Data.DataTable)dgv.DataSource).Rows.Add();
                    for (i = 0; i < ds.Tables[0].Columns.Count; ++i)
                    {
                        dr[i + 1] = excelRow[i];
                    }
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

11.數(shù)據(jù)備份與恢復(fù)

        /// <summary>
        /// 數(shù)據(jù)庫備份或恢復(fù)
        /// </summary>
        /// <param name="strCmd">備份或恢復(fù)命令</param>
        /// <param name="strDmpFileName">dmp文件名稱</param>
        /// <param name="strLogFileName">log文件名稱</param>
        /// <returns>true:成功;false:失敗</returns>
        public static bool DBBackup(string strCmd, string strDmpFileName, string strLogFileName)
        {
            // 返回值
            bool result = false;
            // cmd中輸入的參數(shù)內(nèi)容
            string arg = strCmd + " FILE= " + strDmpFileName + " LOG= " + strLogFileName;
            try
            {
                // 如果dmp文件不存在，創(chuàng)建文件并釋放
                if (!File.Exists(strDmpFileName))
                {
                    File.Create(strDmpFileName).Dispose();
                }
                // 如果log文件不存在，創(chuàng)建文件并釋放
                if (!File.Exists(strLogFileName))
                {
                    File.Create(strLogFileName).Dispose();
                }
                // 開啟新進(jìn)程
                Process p = new Process();
                // 打開運(yùn)行cmd
                p.StartInfo.FileName = "cmd.exe";
                // 要運(yùn)行的命令
                p.StartInfo.Arguments = arg;
                
                // 關(guān)閉shell的使用
                p.StartInfo.UseShellExecute = false;
                //p.StartInfo.RedirectStandardInput = true; 
                // 設(shè)置顯示cmd運(yùn)行界面
                p.StartInfo.CreateNoWindow = false;

                // 啟動(dòng)進(jìn)程
                p.Start();
                
                p.WaitForExit();
                p.Dispose();
                result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
            return result;
        }

總結(jié)：這些功能放在一個(gè)類中可能不是很合理，但是當(dāng)時(shí)是為了自己編程的方便，只要有多處實(shí)現(xiàn)的一些獨(dú)立的功能，我就加入到這個(gè)類中來，并全部用靜態(tài)的方法實(shí)現(xiàn)。

posted @ 2011-11-20 18:47 薔薇理想人生閱讀(1118) 評(píng)論(0) 收藏舉報(bào)

刷新頁面返回頂部

基于ArcGIS10.0和Oracle10g的空間數(shù)據(jù)管理平臺(tái)五（C#開發(fā)）-通用工具類

公告