学步园

    由于在介绍通用数据管理模块功能的时候用到了许多通用的功能，例如word和excel与DataGridView控件的相互导入导出、写日志到数据库中等等。为了后面每一篇文章都能清楚的知道每一个功能具体是怎么实现的，现在先统一把用到的所有公用功能统一介绍和实现。由于SqlHelper类，也就是数据库的一些通用操作已经在登录模块中介绍，就不在重复介绍了。本篇将相介绍通用工具类CommonTools，由于代码中有比较详细的注释了，就不详细解析具体代码的功能了。这个类实现的功能如下。

1.从DataGridView控件导出数据到Word文档

/// <summary><br /> /// 从DataGridView控件导出数据到word文档<br /> /// </summary><br /> /// <param name="dgv"></param><br /> public static void ExportDataGridViewToWord(DataGridView dgv)<br /> {<br /> //打开保存的文件名<br /> SaveFileDialog sfile = new SaveFileDialog();<br /> sfile.AddExtension = true;<br /> sfile.DefaultExt = ".doc";<br /> sfile.Filter = "(*.doc)|*.doc";<br /> if (sfile.ShowDialog() == DialogResult.OK)<br /> {<br /> object path = sfile.FileName;<br /> Object none = System.Reflection.Missing.Value;<br /> Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.Application();<br /> Microsoft.Office.Interop.Word.Document document = wordApp.Documents.Add(ref none, ref none, ref none, ref none);<br /> //建立表格<br /> Microsoft.Office.Interop.Word.Table table = document.Tables.Add(document.Paragraphs.Last.Range, dgv.Rows.Count, dgv.Columns.Count, ref none, ref none);<br /> try<br /> {<br /> //设置标题<br /> for (int i = 0; i < dgv.Columns.Count; i++)<br /> {<br /> table.Cell(0, i + 1).Range.Text = dgv.Columns[i].HeaderText;<br /> }<br /> //填充数据<br /> for (int i = 1; i < dgv.Rows.Count; i++)<br /> {<br /> for (int j = 0; j < dgv.Columns.Count; j++)<br /> {<br /> table.Cell(i + 1, j + 1).Range.Text = dgv[j, i - 1].Value.ToString();<br /> }<br /> }<br /> document.SaveAs(ref path, ref none, ref none, ref none, ref none, ref none,<br /> ref none, ref none, ref none, ref none, ref none, ref none, ref none,<br /> ref none, ref none, ref none);<br /> document.Close(ref none, ref none, ref none);<br /> MessageBox.Show("导出成功！");<br /> }<br /> finally<br /> {<br /> wordApp.Quit(ref none, ref none, ref none);<br /> }<br /> }<br /> }

2.导出DataGridView中的数据到excel，实现代码如下：

/// <summary><br /> /// 导出DataGridView中的数据到excel<br /> /// </summary><br /> /// <param name="dataGridview1">DataGridView控件</param><br /> public static void ExportDataGridViewToExcel(DataGridView dataGridview1)<br /> {<br /> SaveFileDialog saveFileDialog = new SaveFileDialog();<br /> saveFileDialog.Filter = "Execl files (*.xls)|*.xls";<br /> saveFileDialog.FilterIndex = 0;<br /> saveFileDialog.RestoreDirectory = true;<br /> saveFileDialog.CreatePrompt = true;<br /> saveFileDialog.Title = "导出Excel文件到";</p> <p> saveFileDialog.ShowDialog();</p> <p> Stream myStream;<br /> myStream = saveFileDialog.OpenFile();<br /> StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));<br /> string str = "";<br /> try<br /> {<br /> //写标题<br /> for (int i = 0; i < dataGridview1.ColumnCount; i++)<br /> {<br /> if (i > 0)<br /> {<br /> str += "\t";<br /> }<br /> str += dataGridview1.Columns[i].HeaderText;<br /> }</p> <p> sw.WriteLine(str);<br /> //写内容<br /> for (int j = 0; j < dataGridview1.Rows.Count; j++)<br /> {<br /> string tempStr = "";<br /> for (int k = 0; k < dataGridview1.Columns.Count; k++)<br /> {<br /> if (k > 0)<br /> {<br /> tempStr += "\t";<br /> }<br /> tempStr += dataGridview1.Rows[j].Cells[k].Value.ToString();<br /> }<br /> sw.WriteLine(tempStr);<br /> }<br /> sw.Close();<br /> myStream.Close();<br /> }<br /> catch (Exception e)<br /> {<br /> MessageBox.Show(e.Message);<br /> }<br /> finally<br /> {<br /> sw.Close();<br /> myStream.Close();<br /> }<br /> }

3.导入XML文件的数据到Oracle数据库

/// <summary><br /> /// 导入XML文件的数据到Oracle数据库中<br /> /// </summary><br /> /// <param name="strXmlFileName">Xml文件名称(完整的路径)</param><br /> /// <param name="strDatabaseName">导入的数据库名称</param><br /> public static void ImportXmlToOracle(string strXmlFileName, string strDatabaseName)<br /> {<br /> NewDataSet nds = new NewDataSet();<br /> string sql = string.Empty;</p> <p> foreach (System.Data.DataTable dt in nds.Tables)<br /> {<br /> sql = "select * from ";<br /> sql += strDatabaseName + "_" + dt.TableName;</p> <p> OracleDataAdapter oda = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);</p> <p> OracleCommandBuilder builder = new OracleCommandBuilder(oda);<br /> oda.Fill(nds.Tables[dt.TableName]);<br /> }</p> <p> try<br /> {<br /> XmlDocument doc = new XmlDocument();<br /> doc.Load(strXmlFileName);<br /> XmlNodeList nodeList = doc.GetElementsByTagName("tableType");<br /> string strTableType = string.Empty;<br /> foreach (XmlNode node in nodeList)<br /> {<br /> if (node.Name == "tableType")<br /> {<br /> strTableType += node.InnerText + ";";<br /> }<br /> }</p> <p> strTableType = strTableType.Substring(0, strTableType.Length - 1);<br /> foreach (XmlNode node in nodeList)<br /> {<br /> if (node.Name == "tableType")<br /> {<br /> node.InnerText = strTableType;<br /> break;<br /> }<br /> }</p> <p> nodeList = doc.GetElementsByTagName("graphOverview");<br /> foreach (XmlNode node in nodeList)<br /> {<br /> if (node.Name == "graphOverview")<br /> {<br /> string str = string.Empty;<br /> if (node.InnerText.IndexOf(".jpg".ToUpper()) != -1)<br /> {<br /> str = node.InnerText.Substring(node.InnerText.IndexOf(".jpg".ToUpper()) + 4);<br /> }<br /> else if (node.InnerText.IndexOf(".jpg") != -1)<br /> {<br /> str = node.InnerText.Substring(node.InnerText.IndexOf(".jpg") + 4);<br /> }<br /> node.InnerText = str;</p> <p> break;<br /> }<br /> }</p> <p> doc.Save("temp.xml");<br /> nds.ReadXml("temp.xml");<br /> System.IO.File.Delete("temp.xml");</p> <p> foreach (System.Data.DataTable dt in nds.Tables)<br /> {<br /> if (dt.Rows.Count > 0)<br /> {<br /> string strTableName = strDatabaseName + "_" + dt.TableName;<br /> sql = "select * from " + strTableName;<br /> DataSet ds = new DataSet();</p> <p> OracleDataAdapter oda = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> OracleCommandBuilder builder = new OracleCommandBuilder(oda);<br /> oda.Fill(ds);<br /> oda.Update(dt);<br /> }<br /> }<br /> }<br /> catch (Exception ex)<br /> {<br /> MessageBox.Show(ex.Message);<br /> LogHelp.writeLog(FrmMain.metaUsername, "元数据目录管理",<br /> "导入元数据文件<" + strXmlFileName + ">失败!");<br /> }<br /> }

     这个函数主要用在元数据管理子系统，把XML格式的数据导入Oracle数据库，这里XML格式的文件是通过一个XSD文件定义格式得到的，XSD文件就是Xml数据格式的约束文件，就是通过它定义XML具体的格式。然而微软有一个工具可以根据这个XSD文件生成对应的数据集类，在我的代码里就是NewDataSet类。这个数据集类就可以管理对应的格式的所有XML生成的表结构数据。这里还需要说明一点的就是管理元数据需要一个单独的元数据库，也就是要求重新建立一个Oracle数据库，但是用C#代码创建Oracle数据库确实很有难度，所以我选择了一种比较巧妙的实现方式，就是把所有属于元数据的表用前缀标明（如metadata_名称），这样每次操作元数据库就只需要找到相应前缀的表就可以了，具体在元数据管理子系统有详细介绍实现方案。

    因为XML文件里面包含有图片数据（经过64base编码的字符串表示），所以在处理XML文件需要先处理一下这个XML文件的图片字段，才有上面新建一个临时的XML文件，然后用数据集类对象读入XML数据，然后删除临时的XML文件。最后还是例如数据集适配器来插入元数据到Oracle数据库。

4.根据元数据的ID删除一条元数据（从Oracle数据库）：

/// <summary><br /> /// 根据元数据ID删除一条元数据，一条元数据涉及到每一个元数据库的表<br /> /// </summary><br /> /// <param name="strMdid">元数据ID</param><br /> /// <param name="strDatabaseName">元数据名称</param><br /> public static void DelOneMetaData(string strMdid, string strDatabaseName)<br /> {<br /> SqlHelper sh = new SqlHelper();<br /> OracleConnection conn = new OracleConnection(ConfigurationSettings.AppSettings["ConnectionString"]);<br /> conn.Open();<br /> //创建一个 OracleTransaction 对象，然后调用 OracleConnection 对象<br /> //的 BeginTransaction() 方法启动事务。<br /> OracleTransaction trans = conn.BeginTransaction();<br /> //创建一个 OracleCommand 对象，用于存储 SQL 语句。</p> <p> OracleCommand cmd = conn.CreateCommand();<br /> cmd.Transaction = trans;<br /> string sql = string.Empty;<br /> //xxx_metadata<br /> sql = "delete from " + strDatabaseName + "_metadata";<br /> sql += " where mdid like'" + strMdid + "'";<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();</p> <p> sql = "select metadata_id from " + strDatabaseName + "_metadata";<br /> sql += " where mdid like'" + strMdid + "'";<br /> OracleDataReader odr1 = sh.ReturnDataReader(sql);<br /> if (odr1.Read())<br /> {<br /> //xxx_continfo<br /> sql = "delete from " + strDatabaseName + "_continfo";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();</p> <p> sql = "select continfo_id from " + strDatabaseName + "_continfo";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataReader odr2 = sh.ReturnDataReader(sql);<br /> if (odr2.Read())<br /> {<br /> //xxx_layerinfo<br /> sql = "delete from " + strDatabaseName + "_layerinfo";<br /> sql += " where continfo_id=" + odr2[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();<br /> //xxx_tableinfo<br /> sql = "delete from " + strDatabaseName + "_tableinfo";<br /> sql += " where continfo_id=" + odr2[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();<br /> }<br /> //xxx_graph<br /> sql = "delete from " + strDatabaseName + "_graph";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();<br /> //xxx_idpoc<br /> sql = "delete from " + strDatabaseName + "_idpoc";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();</p> <p> sql = "select idpoc_id from " + strDatabaseName + "_idpoc";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataReader odr3 = sh.ReturnDataReader(sql);<br /> if (odr3.Read())<br /> {<br /> //xxx_contact<br /> sql = "delete from " + strDatabaseName + "_contact";<br /> sql += " where idpoc_id=" + odr3[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();</p> <p> sql = "select contact_id from " + strDatabaseName + "_contact";<br /> sql += " where idpoc_id=" + odr3[0].ToString();<br /> OracleDataReader odr4 = sh.ReturnDataReader(sql);<br /> if (odr4.Read())<br /> {<br /> //xxx_cntadd<br /> sql = "delete from " + strDatabaseName + "_cntadd";<br /> sql += " where contact_id=" + odr4[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();</p> <p> //xxx_cntphone<br /> sql = "delete from " + strDatabaseName + "_cntphone";<br /> sql += " where contact_id=" + odr4[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();</p> <p> sql = "select cntphone_id from " + strDatabaseName + "_cntphone";<br /> sql += " where contact_id=" + odr4[0].ToString();<br /> OracleDataReader odr5 = sh.ReturnDataReader(sql);<br /> if (odr5.Read())<br /> {<br /> //xxx_voicenum<br /> sql = "delete from " + strDatabaseName + "_voicenum";<br /> sql += " where cntphone_id=" + odr4[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();<br /> }</p> <p> }<br /> }<br /> //xxx_keyword<br /> sql = "delete from " + strDatabaseName + "_keyword";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();<br /> //xxx_onlinesrc<br /> sql = "delete from " + strDatabaseName + "_onlinesrc";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();</p> <p> sql = "select onlinesrc_id from " + strDatabaseName + "_onlinesrc";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataReader odr6 = sh.ReturnDataReader(sql);<br /> if (odr6.Read())<br /> {<br /> //xxx_dtbrlinkage<br /> sql = "delete from " + strDatabaseName + "_dtbrlinkage";<br /> sql += " where onlinesrc_id=" + odr6[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();</p> <p> //xxx_dtdllinkage<br /> sql = "delete from " + strDatabaseName + "_dtdllinkage";<br /> sql += " where onlinesrc_id=" + odr6[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();<br /> }<br /> //xxx_tpcat<br /> sql = "delete from " + strDatabaseName + "_tpcat";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> cmd.CommandText = sql;<br /> cmd.ExecuteNonQuery();<br /> try<br /> {<br /> trans.Commit();<br /> }<br /> catch (Exception)<br /> {<br /> trans.Rollback();<br /> LogHelp.writeLog(FrmMain.metaUsername, "元数据编辑", "删除元数据<" + strMdid + ">失败！");<br /> MessageBox.Show("删除元数据<" + strMdid + ">失败！");<br /> }<br /> finally<br /> {<br /> conn.Close();<br /> }<br /> }<br /> }

    因为根据XSD文件解析生成的数据库表有十几个，所以对每一个表单独采用事务操作，就是这十几个表其实是一个整体，可以完整的表示一条元数据（元数据：描述数据的数据，这里就是描述空间数据特性的数据）。

5.根据元数据的ID从Oracle查询得到一条元数据，和删除一条元数据类似，也需要十几个表一起操作：

/// <summary><br /> /// 根据元数据ID得到有一条元数据的数据集<br /> /// </summary><br /> /// <param name="strMdid">元数据ID</param><br /> /// <param name="strDatabaseName">元数据名称</param><br /> /// <returns></returns><br /> public static NewDataSet GetOneMetaData(string strMdid, string strDatabaseName)<br /> {<br /> NewDataSet nds = new NewDataSet();<br /> SqlHelper sh = new SqlHelper();</p> <p> string sql = string.Empty;<br /> //xxx_metadata<br /> sql = "select * from " + strDatabaseName + "_metadata";<br /> sql += " where mdid like'" + strMdid + "'";<br /> OracleDataAdapter oda1 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda1.Fill(nds.Tables["metadata"]);</p> <p> sql = "select metadata_id from " + strDatabaseName + "_metadata";<br /> sql += " where mdid like'" + strMdid + "'";<br /> OracleDataReader odr1 = sh.ReturnDataReader(sql);<br /> if (odr1.Read())<br /> {<br /> //xxx_continfo<br /> sql = "select * from " + strDatabaseName + "_continfo";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataAdapter oda2 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda2.Fill(nds.Tables["continfo"]);</p> <p> sql = "select continfo_id from " + strDatabaseName + "_continfo";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataReader odr2 = sh.ReturnDataReader(sql);<br /> if (odr2.Read())<br /> {<br /> //xxx_layerinfo<br /> sql = "select * from " + strDatabaseName + "_layerinfo";<br /> sql += " where continfo_id=" + odr2[0].ToString();<br /> OracleDataAdapter oda8 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda8.Fill(nds.Tables["layerinfo"]);<br /> //xxx_tableinfo<br /> sql = "select * from " + strDatabaseName + "_tableinfo";<br /> sql += " where continfo_id=" + odr2[0].ToString();<br /> OracleDataAdapter oda9 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda9.Fill(nds.Tables["tableinfo"]);<br /> }<br /> //xxx_graph<br /> sql = "select * from " + strDatabaseName + "_graph";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataAdapter oda3 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda3.Fill(nds.Tables["graph"]);<br /> //xxx_idpoc<br /> sql = "select * from " + strDatabaseName + "_idpoc";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataAdapter oda4 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda4.Fill(nds.Tables["idpoc"]);</p> <p> sql = "select idpoc_id from " + strDatabaseName + "_idpoc";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataReader odr3 = sh.ReturnDataReader(sql);<br /> if (odr3.Read())<br /> {<br /> //xxx_contact<br /> sql = "select * from " + strDatabaseName + "_contact";<br /> sql += " where idpoc_id=" + odr3[0].ToString();<br /> OracleDataAdapter oda12 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda12.Fill(nds.Tables["contact"]);</p> <p> sql = "select contact_id from " + strDatabaseName + "_contact";<br /> sql += " where idpoc_id=" + odr3[0].ToString();<br /> OracleDataReader odr4 = sh.ReturnDataReader(sql);<br /> if (odr4.Read())<br /> {<br /> //xxx_cntadd<br /> sql = "select * from " + strDatabaseName + "_cntadd";<br /> sql += " where contact_id=" + odr4[0].ToString();<br /> OracleDataAdapter oda13 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda13.Fill(nds.Tables["cntadd"]);</p> <p> //xxx_cntphone<br /> sql = "select * from " + strDatabaseName + "_cntphone";<br /> sql += " where contact_id=" + odr4[0].ToString();<br /> OracleDataAdapter oda14 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda14.Fill(nds.Tables["cntphone"]);</p> <p> sql = "select cntphone_id from " + strDatabaseName + "_cntphone";<br /> sql += " where contact_id=" + odr4[0].ToString();<br /> OracleDataReader odr5 = sh.ReturnDataReader(sql);<br /> if (odr5.Read())<br /> {<br /> //xxx_voicenum<br /> sql = "select * from " + strDatabaseName + "_voicenum";<br /> sql += " where cntphone_id=" + odr4[0].ToString();<br /> OracleDataAdapter oda15 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda15.Fill(nds.Tables["voicenum"]);<br /> }</p> <p> }<br /> }<br /> //xxx_keyword<br /> sql = "select * from " + strDatabaseName + "_keyword";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataAdapter oda5 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda5.Fill(nds.Tables["keyword"]);<br /> //xxx_onlinesrc<br /> sql = "select * from " + strDatabaseName + "_onlinesrc";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataAdapter oda6 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda6.Fill(nds.Tables["onlinesrc"]);</p> <p> sql = "select onlinesrc_id from " + strDatabaseName + "_onlinesrc";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataReader odr6 = sh.ReturnDataReader(sql);<br /> if (odr6.Read())<br /> {<br /> //xxx_dtbrlinkage<br /> sql = "select * from " + strDatabaseName + "_dtbrlinkage";<br /> sql += " where onlinesrc_id=" + odr6[0].ToString();<br /> OracleDataAdapter oda10 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda10.Fill(nds.Tables["dtbrlinkage"]);</p> <p> //xxx_dtdllinkage<br /> sql = "select * from " + strDatabaseName + "_dtdllinkage";<br /> sql += " where onlinesrc_id=" + odr6[0].ToString();<br /> OracleDataAdapter oda11 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda11.Fill(nds.Tables["dtdllinkage"]);<br /> }<br /> //xxx_tpcat<br /> sql = "select * from " + strDatabaseName + "_tpcat";<br /> sql += " where metadata_id=" + odr1[0].ToString();<br /> OracleDataAdapter oda7 = new OracleDataAdapter(sql,<br /> ConfigurationSettings.AppSettings["ConnectionString"]);<br /> oda7.Fill(nds.Tables["tpcat"]);<br /> }<br /> return nds;<br /> }

6.导出Oracle数据库的数据到XML文件中

/// <summary><br /> /// 导出Oracle数据库的数据到XML文件中<br /> /// </summary><br /> /// <param name="strXmlPath">导出的目录</param><br /> /// <param name="strDatabaseName">导出的数据库名称</param><br /> public static void ExportXmlFromOracle(string strXmlPath, string strDatabaseName)<br /> {<br /> NewDataSet nds;<br /> SqlHelper sh = new SqlHelper();<br /> string sql = string.Empty;<br /> sql = "select mdid from " + strDatabaseName + "_metadata";</p> <p> OracleDataReader odr = sh.ReturnDataReader(sql);</p> <p> while (odr.Read())<br /> {<br /> string strMdid = odr[0].ToString();<br /> //根据元数据的ID得到一条元数据<br /> nds = GetOneMetaData(strMdid, strDatabaseName);</p> <p> nds.WriteXml(strXmlPath + "\\" + strMdid + ".xml");<br /> }<br /> }

7.下面两个函数实现treeview控件向XML转换，主要用于对元数据的编辑后保存到XML文件中。

#region 遍历tree并实现向XML的转化<br /> /// <summary><br /> /// 遍历treeview并实现向XML的转化<br /> /// </summary><br /> /// <param name="advTree">树控件对象</param><br /> /// <param name="strXmlFilePath">XML输出路径</param><br /> public static void TreeToXML(AdvTree advTree, string strXmlFilePath)<br /> {<br /> XmlTextWriter xtw = new XmlTextWriter(strXmlFilePath, null);<br /> XmlDocument xmlDoc = new XmlDocument();<br /> //控制格式并写XML的开头<br /> xtw.Formatting = Formatting.Indented;<br /> xtw.WriteStartDocument(true);<br /> xtw.WriteStartElement("metadata");<br /> xtw.WriteStartAttribute("xsi","noNamespaceSchemaLocation", "http://www.w3.org/2001/XMLSchema-instance");<br /> xtw.WriteEndAttribute();<br /> xtw.WriteEndElement();<br /> xtw.WriteEndDocument();<br /> xtw.Close();</p> <p> xmlDoc.Load(strXmlFilePath);<br /> XmlNode xnRoot = xmlDoc.SelectSingleNode("metadata");</p> <p> TransTreeToXml(xmlDoc, advTree.FindNodeByName("metadata").Nodes, (XmlElement)xnRoot, strXmlFilePath);<br /> }</p> <p> /// <summary><br /> /// 转化Tree节点的数据到XML文档<br /> /// </summary><br /> /// <param name="xmlDoc">XML文档对象</param><br /> /// <param name="nodes">Tree的节点集合</param><br /> /// <param name="xeRoot">根节点元素</param><br /> /// <param name="strXmlPath">XML的节点路径</param><br /> private static void TransTreeToXml(XmlDocument xmlDoc,<br /> NodeCollection nodes, XmlElement xeRoot, String strXmlPath)<br /> {<br /> XmlElement xe;<br /> //遍历节点集合中的所有节点<br /> foreach (Node node in nodes)<br /> {<br /> //根据节点的名称创建一个新的XML节点元素<br /> xe = xmlDoc.CreateElement(node.Name);<br /> //将新建的XML节点元素添加到父亲节点（也就是传递进来的根节点）<br /> xeRoot.AppendChild(xe);<br /> //如果节点还有子节点就递归调用本函数<br /> if (node.Nodes.Count > 0)<br /> {<br /> TransTreeToXml(xmlDoc, node.Nodes, xe, strXmlPath);<br /> }<br /> else<br /> {<br /> //因为Tree节点数据的格式是：（名称：值），所以根据格式取出具体的值<br /> string strTemp = node.Text.Substring(node.Text.IndexOf(':')+1);<br /> //这个值不是空就填写新建的XML节点的值<br /> if (strTemp != "")<br /> {<br /> //特殊处理graphOverview节点，因为它涉及到图形的二进制数据，需要转化为字符串<br /> if (xe.Name == "graphOverview")<br /> {<br /> //以下就是将图形二进制数据转化为字符串<br /> Bitmap bmp = new Bitmap(strTemp);<br /> MemoryStream ms = new MemoryStream();<br /> bmp.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);<br /> byte[] arr = new byte[ms.Length];<br /> ms.Position = 0;<br /> ms.Read(arr, 0, (int)ms.Length);<br /> ms.Close();<br /> String strbaser64 = Convert.ToBase64String(arr);<br /> //给节点填充值<br /> xe.InnerText = strbaser64;<br /> }<br /> else<br /> {<br /> //给节点填充值<br /> xe.InnerText = strTemp;<br /> }<br /> }<br /> }<br /> }<br /> xmlDoc.Save(strXmlPath);<br /> }</p> <p> #endregion

8.遍历XML并实现向tree控件的转化

#region 遍历XML并实现向tree的转化<br /> /// <summary><br /> /// 遍历XML并实现向tree的转化<br /> /// </summary><br /> /// <param name="strXmlFilePath">XML输入路径</param><br /> /// <param name="advTree">树控件对象</param><br /> public static void XMLToTree(string strXmlFilePath, AdvTree advTree)<br /> {<br /> //新建XML文档对象并载入XML文件数据<br /> XmlDocument xmlDoc = new XmlDocument();<br /> xmlDoc.Load(strXmlFilePath);<br /> //找到根节点<br /> XmlNodeList xnl = xmlDoc.GetElementsByTagName("metadata");<br /> XmlNode root = xnl[0];<br /> //根据找到的根节点遍历整个XML文件的节点<br /> foreach (XmlNode xn in root.ChildNodes)<br /> {<br /> //找到Tree对于的节点<br /> Node n = advTree.FindNodeByName(xn.Name);<br /> //如果有子节点就调用递归转化函数<br /> if (n.HasChildNodes)<br /> {<br /> TransXMLToTree(xn.ChildNodes, advTree);<br /> }<br /> else<br /> {<br /> //如果节点的内容不为空就为Tree控件节点添加值<br /> if (xn.InnerText != "")<br /> {<br /> //标记已经有值，方便以后对Tree的编辑操作<br /> n.Tag = "yes";<br /> n.Text += xn.InnerText;<br /> //如果此节点有父亲节点就把对于的父亲节点也标记为有值<br /> while (n.Parent != null)<br /> {<br /> Node np = n.Parent;<br /> np.Tag = "yes";<br /> n = n.Parent;<br /> }<br /> }<br /> }<br /> }<br /> }</p> <p> /// <summary><br /> /// 递归转化XML文件的节点到Tree的节点<br /> /// </summary><br /> /// <param name="xmlNodes">XML文件的节点集合</param><br /> /// <param name="advTree">Tree控件</param><br /> private static void TransXMLToTree(XmlNodeList xmlNodes, AdvTree advTree)<br /> {<br /> //循环遍历节点集合<br /> foreach (XmlNode xn in xmlNodes)<br /> {<br /> //根据XML的节点找到对于的Tree节点<br /> Node n = advTree.FindNodeByName(xn.Name);<br /> //如果还有子节点就递归转换<br /> if (n.HasChildNodes)<br /> {<br /> TransXMLToTree(xn.ChildNodes, advTree);<br /> }<br /> else<br /> {<br /> //如果节点的内容不为空就为Tree控件节点添加值<br /> if (xn.InnerText != "")<br /> {<br /> //标记已经有值，方便以后对Tree的编辑操作<br /> n.Tag = "yes";<br /> n.Text += xn.InnerText;<br /> //如果此节点有父亲节点就把对于的父亲节点也标记为有值<br /> while (n.Parent != null)<br /> {<br /> Node np = n.Parent;<br /> np.Tag = "yes";<br /> n = n.Parent;<br /> }<br /> }<br /> }<br /> }<br /> }<br /> #endregion

9.将DataGridView显示的内容导出Excel

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

10.excel数据导入到DataGridView控件中

/// <summary><br /> /// Excel数据导入方法<br /> /// </summary><br /> /// <param name="filePath"></param><br /> /// <param name="dgv"></param><br /> public static void EcxelToDataGridView(string filePath, DataGridView dgv)<br /> {<br /> try<br /> {<br /> //根据路径打开一个Excel文件并将数据填充到DataSet中<br /> string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0'";<br /> OleDbConnection conn = new OleDbConnection(strConn);<br /> conn.Open();<br /> string strExcel = "";<br /> OleDbDataAdapter myCommand = null;<br /> DataSet ds = null;<br /> strExcel = "select * from [sheet1$]";<br /> myCommand = new OleDbDataAdapter(strExcel, strConn);<br /> ds = new DataSet();<br /> myCommand.Fill(ds, "table1");</p> <p> //根据Excel的行逐一对上面构造的DataTable的列进行赋值<br /> foreach (DataRow excelRow in ds.Tables[0].Rows)<br /> {<br /> int i = 0;<br /> DataRow dr = ((System.Data.DataTable)dgv.DataSource).Rows.Add();<br /> for (i = 0; i < ds.Tables[0].Columns.Count; ++i)<br /> {<br /> dr[i + 1] = excelRow[i];<br /> }<br /> }<br /> }<br /> catch (System.Exception ex)<br /> {<br /> MessageBox.Show(ex.Message);<br /> }<br /> }<br />

11.数据备份与恢复

/// <summary><br /> /// 数据库备份或恢复<br /> /// </summary><br /> /// <param name="strCmd">备份或恢复命令</param><br /> /// <param name="strDmpFileName">dmp文件名称</param><br /> /// <param name="strLogFileName">log文件名称</param><br /> /// <returns>true:成功;false:失败</returns><br /> public static bool DBBackup(string strCmd, string strDmpFileName, string strLogFileName)<br /> {<br /> // 返回值<br /> bool result = false;<br /> // cmd中输入的参数内容<br /> string arg = strCmd + " FILE= " + strDmpFileName + " LOG= " + strLogFileName;<br /> try<br /> {<br /> // 如果dmp文件不存在，创建文件并释放<br /> if (!File.Exists(strDmpFileName))<br /> {<br /> File.Create(strDmpFileName).Dispose();<br /> }<br /> // 如果log文件不存在，创建文件并释放<br /> if (!File.Exists(strLogFileName))<br /> {<br /> File.Create(strLogFileName).Dispose();<br /> }<br /> // 开启新进程<br /> Process p = new Process();<br /> // 打开运行cmd<br /> p.StartInfo.FileName = "cmd.exe";<br /> // 要运行的命令<br /> p.StartInfo.Arguments = arg;</p> <p> // 关闭shell的使用<br /> p.StartInfo.UseShellExecute = false;<br /> //p.StartInfo.RedirectStandardInput = true;<br /> // 设置显示cmd运行界面<br /> p.StartInfo.CreateNoWindow = false;</p> <p> // 启动进程<br /> p.Start();</p> <p> p.WaitForExit();<br /> p.Dispose();<br /> result = true;<br /> }<br /> catch (Exception ex)<br /> {<br /> MessageBox.Show(ex.Message);<br /> return false;<br /> }<br /> return result;<br /> }

    总结：这些功能放在一个类中可能不是很合理，但是当时是为了自己编程的方便，只要有多处实现的一些独立的功能，我就加入到这个类中来，并全部用静态的方法实现。