XML读SQLSEVER再写回

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;

public partial class _Default : System.Web.UI.Page
{
    //绑定XML到GridView
    private void BindToXML()
    {
        DataSet dsxml = new DataSet();
        dsxml.ReadXml(Server.MapPath("test.xml"));
        if (dsxml.Tables[0].Rows.Count > 0)
        {
            gdvTest2.DataSource = dsxml;
            gdvTest2.DataBind();
        }
        else
        {
            Message.Text = "No Message in XML";
        }
    }

    //连接数据库方法
    DataSet GetData(String queryString)
    {
        String connectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
        DataSet ds = new DataSet();
        try
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
            adapter.Fill(ds);
        }
        catch
        {
            Message.Text = "Unable to connect to the database.";
        }
        return ds;

    }

    //Page_Load
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    //显示数据
    protected void btnShowData_Click(object sender, EventArgs e)
    {
        String queryString = "select * from XMLTestTbl";
        DataSet ds = GetData(queryString);
        if (ds.Tables.Count > 0)
        {
            gdvTest.DataSource = ds;
            gdvTest.DataBind();
        }
        else
        {
            Message.Text = "Unable to connect to the database.";
        }
    }

    //生成XML
    protected void btnCreatXML_Click(object sender, EventArgs e)
    {
        try
        {
            String queryString = "select * from XMLTestTbl";
            DataSet ds = GetData(queryString);
            ds.WriteXml(Server.MapPath("test.xml"));
            this.Message.Text = "create finished";
        }
        catch
        {
            Message.Text = "Unable to write XML.";
        }
    }

    //读取XML并更新数据库
    protected void btnReadXML_Click(object sender, EventArgs e)
    {
        try
        {
            //请空所有记录
            string constr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
            SqlConnection con2 = new SqlConnection(constr);
            SqlCommand cmd = new SqlCommand("TRUNCATE TABLE XMLTestTbl", con2);
            con2.Open();
            cmd.ExecuteNonQuery();
            //
            DataSet dsxml = new DataSet();
            dsxml.ReadXml(Server.MapPath("test.xml"));
            DataSet dsdb = new DataSet();
            SqlConnection con = new SqlConnection(constr);
            con.Open();
            SqlDataAdapter adapter = new SqlDataAdapter("select * from XMLTestTbl", con);
            SqlCommandBuilder scb = new SqlCommandBuilder(adapter);
            adapter.Update(dsxml);



            //int rowcount = dsxml.Tables[0].Rows.Count;
            //if (rowcount > 0)
            //{
            //    int columncount = dsxml.Tables[0].Columns.Count;
            //    //请空所有记录
            //    SqlConnection con = new SqlConnection(constr);
            //    SqlCommand cmd = new SqlCommand("TRUNCATE TABLE XMLTestTbl", con);
            //    con.Open();
            //    cmd.ExecuteNonQuery();


            //    //获取数据库源
            //    DataSet dsdb = new DataSet();
            //    SqlConnection con2 = new SqlConnection(constr);
            //    SqlDataAdapter adapter = new SqlDataAdapter("select * from XMLTestTbl", con2);
            //    SqlCommandBuilder scb = new SqlCommandBuilder(adapter);
            //    adapter.Fill(dsdb, "XML");
            //    //再建表
            //    for (int i = 0; i < rowcount; i++)
            //    {
            //        DataRow dr = dsdb.Tables["XML"].NewRow();
            //        for (int j = 0; j < columncount; j++)
            //        {
            //            dr[j] = dsxml.Tables[0].Rows[i][j];
            //        }
            //        dsdb.Tables["XML"].Rows.Add(dr);
            //    }
            //    adapter.Update(dsdb, "XML");
            //    this.Message.Text = "copy finished";
            //    con.Close();
            //    con2.Close();
            //}

        }
        catch
        {
            Message.Text = "Unable to update XML.";
        }
    }

    //显示XML文件
    protected void btnShowDataFromXML_Click(object sender, EventArgs e)
    {
        BindToXML();
    }

    //启用编辑
    protected void gdvTest2_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gdvTest2.EditIndex = e.NewEditIndex;
        BindToXML();
    }

    protected void gdvTest2_RowDeleted(object sender, GridViewDeletedEventArgs e)
    {


    }

    //删除　删除确认没做
    protected void gdvTest2_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int index = e.RowIndex;
        DataSet dsxml = new DataSet();
        dsxml.ReadXml(Server.MapPath("test.xml"));
        dsxml.Tables[0].Rows[index].Delete();
        dsxml.WriteXml(Server.MapPath("test.xml"));
        gdvTest2.DataSource = dsxml;
        gdvTest2.DataBind();
    }

    //增加
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        DataSet dsxml = new DataSet();
        dsxml.ReadXml(Server.MapPath("test.xml"));
        DataRow dr = dsxml.Tables[0].NewRow();
        dr[0] = this.txtColumn1.Text;
        dr[1] = this.txtColumn2.Text;
        dr[2] = this.txtColumn3.Text;
        dr[3] = this.txtColumn4.Text;
        txtColumn1.Text = txtColumn2.Text = txtColumn3.Text = txtColumn4.Text = "";
        dsxml.Tables[0].Rows.Add(dr);
        dsxml.WriteXml(Server.MapPath("test.xml"));
        gdvTest2.DataSource = dsxml;
        gdvTest2.DataBind();
    }

    //取消编辑
    protected void gdvTest2_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gdvTest2.EditIndex = -1;
        BindToXML();
    }

    //确认编辑
    protected void gdvTest2_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int index = e.RowIndex;
        DataSet dsxml = new DataSet();
        dsxml.ReadXml(Server.MapPath("test.xml"));
        ArrayList ALColumn = new ArrayList();
        ArrayList ALBox = new ArrayList();
        int columncount = dsxml.Tables[0].Columns.Count;
        for (int i = 0; i < columncount; i++)
        {
            string str = ((TextBox)gdvTest2.Rows[index].Cells[i + 2].Controls[0]).Text;
            dsxml.Tables[0].Rows[index][i] = str;
        }
        dsxml.WriteXml(Server.MapPath("test.xml"));
        gdvTest2.EditIndex = -1;
        BindToXML();
    }
}