强大的 将XML文件的数据导入到数据库的 程序 只需输入xml文件名即可自动创建

通过网上查资料并经过多天整理好的一个程序,以供需要的朋友学习：

前台：

 

 

后台：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace XML
{
    public partial class tst : System.Web.UI.Page
    {
        protected DataSet dataSetXml;
        protected DataTable dataTableXml;
        protected string tableName;
        protected string strConn = ConfigurationManager.AppSettings["ConnectionString2"].ToString();
        protected void Page_Load(object sender, EventArgs e)
        {
           
        }
        protected void buttonSubmit_Click(object sender, EventArgs e)
        {
            Message.Text = "";
            dataSetXml = new DataSet();
            dataTableXml = new DataTable();
            try
            {
                dataSetXml.ReadXml(HttpContext.Current.Server.MapPath(textBoxXml.Text));//读取xml地址
                for (int i = 0; i < dataSetXml.Tables.Count;i++ ) {

                dataTableXml = dataSetXml.Tables[i];//循环取出所有的表
                }
             
                GridView1.DataSource = dataTableXml.DefaultView;//可查看所有表的数据
                GridView1.DataBind();
                tableName = textBoxXml.Text.Substring(0, textBoxXml.Text.Length - 4);

            }
            catch
            {
                Message.Text = "不能打开Xml文件或者是Xml文件格式错误";
            }
            if (dataTableXml.Rows.Count > 0)
            {
                TableCheck();
                TableInsert();
            }
        }
        /// <summary>
        /// 检查并创建表
        /// </summary>
        private void TableCheck()
        {
            SqlConnection conn = new SqlConnection(strConn);
            try
            {
                conn.Open();
                int count = 0;
                //conn.Open();
                DataTable schemaTable = conn.GetSchema("TABLES", new string[] { "asd", tableName });
                string sqlCmd = "";
                for (int j = 0; j < dataSetXml.Tables.Count; j++)//循环所有的表
                {
                    sqlCmd = "create table " + dataSetXml.Tables[j] + "(";//循环创建所有的表名

                if (schemaTable.Rows.Count < 1)
                {
                        
                     for (int i = 0; i <dataSetXml.Tables[j].Columns.Count; i++)
                      {
                          sqlCmd +=dataSetXml.Tables[j].Columns[i].ToString()+ " varchar(200),";//循环创建所有表的列名
                      }               
                 }
                    sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ")";//删除最后一个“,”符号                
                    SqlCommand createcmd = new SqlCommand();
                    createcmd.Connection = conn;
                    createcmd.CommandText = sqlCmd;
                    createcmd.ExecuteNonQuery();
                }
            }
            catch(Exception e)
            {
                Message.Text = e.Message;// "SQL命令或语句连接出问题";
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 对表进行插入数据操作
        /// </summary>
        private void TableInsert()
        {
            //OracleConnection conn = new OracleConnection(strConn);
            SqlConnection conn = new SqlConnection(strConn);
            try
            {
                conn.Open();
                for (int k = 0; k < dataSetXml.Tables.Count;k++ )//循环所有的表
                {
                    foreach (DataRow dr in dataSetXml.Tables[k].Rows)
                    {
                        string  sqlCmd = "Insert Into " + dataSetXml.Tables[k] + "(";//循环插入所有的表
                        for (int i = 0; i < dataSetXml.Tables[k].Columns.Count; i++)//循环所有表的列
                        {
                            sqlCmd += dataSetXml.Tables[k].Columns[i].ColumnName.ToString() + ",";//循环用‘，’隔开
                        }
                        sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ") Values(";
                        for (int j = 0; j< dataSetXml.Tables[k].Columns.Count; j++)//循环用‘‘’隔开
                        {
                            sqlCmd += "'" + dr[j].ToString() + "',";
                        }
                          sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ")";
                         SqlCommand cmd = new SqlCommand(sqlCmd, conn);
                        cmd.ExecuteNonQuery();
                        Message.Text = "Xml文件中的数据成功加载到数据库中！";
                    }
                }
        }
            catch(Exception e)
            {
                Message.Text = e.Message;//在添加数据时产生了错误！";
            }
            finally
            {
                conn.Close();
            }
        }   
    }
}