讨论：通过操作dataset（从sql server中读取数据）写入XML和 反过来操作的性能问题

Question

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.IO;
using System.Data.SqlClient;
using System.Collections;namespace createxml
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        
        private void button1_Click(object sender, EventArgs e)
        {                          DataTableNameds = GetDataTableName();//获取Detainee里面的数据库用户表
             if (DataTableNameds != null)
             {                 for (int i = 0; i < DataTableNameds.Tables[0].Rows.Count; i++)
                 {
                     string connstr;
                     connstr = "server=.;uid=sa;pwd=;database=Detainee";//连接
                     SqlConnection sqlcon = new SqlConnection(connstr);
                     FileStream fs = null;
                     XmlTextWriter tw = null;
                     try
                     {
                         if (sqlcon.State == ConnectionState.Open) //判断连接是否打开
                         {
                             sqlcon.Close();
                         }
                         sqlcon.Open();
                         string sql = "select * from " + DataTableNameds.Tables[0].Rows[i][0].ToString();
                         SqlDataAdapter da = new SqlDataAdapter(sql, sqlcon);
                         DataSet ds = new DataSet();
                         da.Fill(ds);
                         ds.DataSetName = DataTableNameds.Tables[0].Rows[i][0].ToString();//DataSet为生成XML时XML的根节点名称           
                         string path = Application.StartupPath + "\\"+DataTableNameds.Tables[0].Rows[i][0].ToString()+".xml";
                         fs = new FileStream(path, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
                         tw = new XmlTextWriter(fs, System.Text.Encoding.GetEncoding("utf-8"));
                         tw.WriteStartDocument();
                         ds.WriteXml(tw, XmlWriteMode.WriteSchema);
                         
                     }
                     catch (Exception)
                     {                         throw;
                     }
                     finally
                     {
                         if (tw == null)
                         {
                             tw.Close();
                         }
                         if (fs == null)
                         {
                             fs.Close();
                         }
                         if (sqlcon == null)
                         {
                             sqlcon.Close();
                         }
                     }
                 }
                 MessageBox.Show("生成XML成功！");
             }
            
        }        private void button2_Click(object sender, EventArgs e)
        {            SqlConnection sqlcon = null;
            SqlCommand sqlcom = null;
            DataSet DataTableNameds = new DataSet();
            DataTableNameds = GetDataTableName();//获取Detainee里面的数据库用户表
             if (DataTableNameds != null)
             {                 for (int k = 0; k < DataTableNameds.Tables[0].Rows.Count; k++)
                 {
                     try
                     {
                         DataSet myds = new DataSet();
                         string XmlName = DataTableNameds.Tables[0].Rows[k][0].ToString() ;//获取XML的文件名(此名也是数据库中的表名)
                         myds.ReadXml(XmlName + ".xml");
                         ArrayList TableCoumnsName = new ArrayList();//存储表的列名
                         ArrayList TableCoumnsType = new ArrayList();//存储表列的数据类型
                         ArrayList TableRowData = new ArrayList();//存储表的行数据（以每一行为一个单位） 
                         string TableRowDataStr = "";//串联一行数据
                         string TableCoumnsStr = "";//串联列名
                         for (int i = 0; i < myds.Tables[0].Columns.Count; i++)
                         {
                             TableCoumnsName.Add(myds.Tables[0].Columns[i].ColumnName.ToString());
                             TableCoumnsType.Add(myds.Tables[0].Columns[i].DataType.Name.ToString());
                             TableCoumnsStr += myds.Tables[0].Columns[i].ColumnName.ToString() + ",";
                         }                         TableCoumnsStr = TableCoumnsStr.Substring(0, TableCoumnsStr.Length - 1);//列名构造成一个字符串
                         for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
                         {
                             TableRowDataStr = "";//串联一行数据(只有String类型和DateTime类型的值需要用‘’包住)
                             for (int j = 0; j < TableCoumnsName.Count; j++)
                             {
                                 if (TableCoumnsType[j].ToString().Equals("String") || TableCoumnsType[j].ToString().Equals("DateTime"))
                                 {
                                     if (myds.Tables[0].Rows[i][j].ToString() == "")
                                     {
                                         TableRowDataStr += "NULL,";
                                     }
                                     else
                                     {
                                         TableRowDataStr += "'" + myds.Tables[0].Rows[i][j].ToString() + "',";
                                     }
                                 }
                                 else
                                 {
                                     if (myds.Tables[0].Rows[i][j].ToString() == "")
                                     {
                                         TableRowDataStr += "NULL,";
                                     }
                                     else
                                     {
                                         TableRowDataStr += "" + myds.Tables[0].Rows[i][j].ToString() + ",";
                                     }
                                 }                             }
                             TableRowDataStr = TableRowDataStr.Substring(0, TableRowDataStr.Length - 1);//一行的数据构造成了一个字符串
                             TableRowData.Add(TableRowDataStr);//一行为一个单位存入
                         }
                         string connstr;
                         connstr = "server=.;uid=sa;pwd=;database=test";
 //test数据库的表结构和Detainee表结构一样
                         sqlcon = new SqlConnection(connstr);
                         sqlcon.Open();
                         for (int i = 0; i < TableRowData.Count; i++)
                         {
                             string sql = "insert into"+XmlName + "(" + TableCoumnsStr + ") values(" + TableRowData[i].ToString() + ")";
                             sqlcom = new SqlCommand(sql, sqlcon);
                             sqlcom.ExecuteNonQuery();
                         }
                         MessageBox.Show("添加成功！");
                     }
                     catch (Exception)
                     {
                         throw;
                     }
                     finally
                     {
                         if (sqlcon == null)
                         {
                             sqlcon.Close();
                         }
                     }
                 }
             }
        }
       }

Answer 1

 //查询某一数据库中的所以用户表名字(此处是查询Detainee数据库中的所有表名)
        public DataSet GetDataTableName()
        {
            string connstr;
            connstr = "server=.;uid=sa;pwd=;database=Detainee";//连接
            SqlConnection sqlcon = new SqlConnection(connstr);       
            try
            {
                if (sqlcon.State == ConnectionState.Open) //判断连接是否打开
                {
                    sqlcon.Close();
                }
                sqlcon.Open();
                string sql = "select   name   from   sysobjects   where   type='U' and id<>1977058079";
                SqlDataAdapter da = new SqlDataAdapter(sql, sqlcon);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    return ds;
                }
                else 
                {
                    return null;
                }
            }
            catch (Exception)
            {                throw;
            }
            finally
            {
                
                if (sqlcon == null)
                {
                    sqlcon.Close();
                }
            }
        }
    }如题：功能我都实现了，目前还没发现错。但我有几个疑问
1.我生成XML的时候 如果数据库表过的 耗时肯定会很长 给人的感觉向“死了”
2.写入的时候就更明显了（单个XML文件的数据过多会不会导致插入失败）
3.//串联一行数据(只有String类型和DateTime类型的值需要用‘’包住)，我觉得这个应该有问题
4.我最关系的问题，如果我要提升整体的性能，哪些地方需要改进（包括我上面没提到的）
谢谢各位~！分不够我可以再加（目前只能给100）

Answer 2

代码实现的功能就是标题说明的意思。代码直接复制下把数据库名改为你SQL上有的数据库就能用

Answer 3

读写文件IO操作，是比较耗时的...
提升性能，是否可以把xml文件放内存中处理，不写文件

Answer 4

访问数据库数据量大时，也可能有性能问题，尽量提高sql语句性能