CSDN博客

img qing0991

将XML文件导入到Oracle数据库中

发表于2008/9/28 19:42:00  3847人阅读

分类: Xml

前台:

  1. <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  3. <html xmlns="http://www.w3.org/1999/xhtml" >
  4. <head runat="server">
  5.     <title>将Xml文件导入到数据库</title>
  6. </head>
  7. <body>
  8.     <form id="form1" runat="server">
  9.     <div>
  10.         <TABLE id="Table1" cellSpacing="3" cellPadding="3" bgColor="#99ccff" border="0">
  11.                 <TR>
  12.                     <TD align="center" colSpan="2"><STRONG><FONT size="6">读取Xml,导入Oracle并显示</FONT></STRONG></TD>
  13.                 </TR>
  14.                 <TR>
  15.                     <TD colSpan="2"><FONT size="2">读取Xml文件中的内容,并将其内容导入到Oracle数据库</FONT></TD>
  16.                 </TR>
  17.                 <TR>
  18.                     <TD bgColor="#cccccc" style="width: 129px; height: 27px;"><FONT size="2"><strong>请输入Xml文件名</strong></FONT></TD>
  19.                     <TD bgColor="#cccccc" style="height: 27px">
  20.                         <asp:TextBox ID="textBoxXml" runat="server" Font-Names="Arial" Font-Size="X-Small"
  21.                             Width="500px"></asp:TextBox></TD>
  22.                 </TR>               
  23.                 <TR>
  24.                     <TD colSpan="2" style="height: 18px">
  25.                         <asp:Label id="Message" runat="server" Font-Bold="True" Font-Size="X-Small" ForeColor="Red"></asp:Label></TD>
  26.                 </TR>
  27.                 <TR>
  28.                     <TD style="width: 129px; height: 17px"></TD>
  29.                     <TD style="height: 17px">
  30.                         <asp:Button id="buttonSubmit" runat="server" Text="Submit" OnClick="buttonSubmit_Click"></asp:Button></TD>
  31.                 </TR>
  32.             </TABLE>
  33.         <br />
  34.         <strong><span style="color: #0066ff">测试从Xml文件是否能成功读取数据</span><br />
  35.             <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
  36.                 <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
  37.                 <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
  38.                 <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
  39.                 <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
  40.                 <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
  41.                 <AlternatingRowStyle BackColor="White" />
  42.             </asp:GridView>
  43.         </strong>
  44.     </div>
  45.     </form>
  46. </body>
  47. </html>

后台:

  1. using System;
  2. using System.Data;
  3. using System.Configuration;
  4. using System.Web;
  5. using System.Web.Security;
  6. using System.Web.UI;
  7. using System.Web.UI.WebControls;
  8. using System.Web.UI.WebControls.WebParts;
  9. using System.Web.UI.HtmlControls;
  10. using System.Data.OracleClient;
  11. public partial class _Default : System.Web.UI.Page 
  12. {
  13.     protected DataSet dataSetXml;
  14.     protected DataTable dataTableXml;
  15.     protected string tableName;
  16.     protected string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString;
  17.     protected void Page_Load(object sender, EventArgs e)
  18.     {
  19.     }
  20.     protected void buttonSubmit_Click(object sender, EventArgs e)
  21.     {
  22.         Message.Text = "";
  23.         dataSetXml = new DataSet();
  24.         dataTableXml = new DataTable();
  25.         try
  26.         {
  27.             dataSetXml.ReadXml(HttpContext.Current.Server.MapPath(textBoxXml.Text));
  28.             dataTableXml = dataSetXml.Tables[0];
  29.             GridView1.DataSource = dataTableXml.DefaultView;
  30.             GridView1.DataBind();
  31.             tableName = textBoxXml.Text.Substring(0, textBoxXml.Text.Length - 4);
  32.         }
  33.         catch
  34.         {
  35.             Message.Text = "不能打开Xml文件或者是Xml文件格式错误";
  36.         }
  37.         if (dataTableXml.Rows.Count > 0)
  38.         {
  39.             TableCheck();
  40.             TableInsert();
  41.         }
  42.     }
  43.     private void TableCheck()
  44.     {
  45.         OracleConnection conn = new OracleConnection(strConn);
  46.         try
  47.         {
  48.             conn.Open();
  49.             int count = 0;
  50.             //conn.Open();
  51.             DataTable schemaTable = conn.GetSchema("TABLES"new string[] { "SCOTT",tableName});            
  52.             string sqlCmd = "";
  53.             if (schemaTable.Rows.Count < 1)
  54.             {
  55.                 sqlCmd = "create table scott." + tableName + "(";
  56.                 for (int i = 0; i < dataTableXml.Columns.Count; i++)
  57.                 {
  58.                     sqlCmd += dataTableXml.Columns[i].ColumnName.ToString() + " varchar2(100),";
  59.                 }
  60.                 sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ")";//删除最后一个“,”符号                
  61.                 OracleCommand createcmd = new OracleCommand();
  62.                 createcmd.Connection = conn;
  63.                 createcmd.CommandText = sqlCmd;
  64.                 createcmd.ExecuteNonQuery();
  65.             }
  66.         }
  67.         catch
  68.         {
  69.             Message.Text = "SQL命令或语句连接出问题";
  70.         }
  71.         finally
  72.         {
  73.             conn.Close();
  74.         }
  75.     }
  76.     private void TableInsert()
  77.     {
  78.         OracleConnection conn = new OracleConnection(strConn);
  79.         try
  80.         {
  81.             conn.Open();
  82.             foreach (DataRow dr in dataTableXml.Rows)
  83.             {
  84.                 string sqlCmd = "Insert Into scott." + tableName + "(";
  85.                 for (int i = 0; i < dataTableXml.Columns.Count; i++)
  86.                 {
  87.                     sqlCmd += dataTableXml.Columns[i].ColumnName.ToString() + ",";//添加列的字段名
  88.                 }
  89.                 sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ") Values(";
  90.                 for (int i = 0; i < dataTableXml.Columns.Count; i++)
  91.                 {
  92.                     sqlCmd += "'" + dr[i].ToString() + "',";
  93.                 }
  94.                 sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ")";
  95.                 OracleCommand cmd = new OracleCommand(sqlCmd, conn);
  96.                 cmd.ExecuteNonQuery();
  97.                 Message.Text = "Xml文件中的数据成功加载到数据库中!";
  98.             }
  99.         }
  100.         catch
  101.         {
  102.             Message.Text = "在添加数据时产生了错误!";
  103.         }
  104.         finally
  105.         {
  106.             conn.Close();
  107.         }
  108.     }    
  109. }

 

0 0

相关博文

我的热门文章

img
取 消
img