用DataGrid演示某字段为null保存xml时节点丢失的问题

当数据库字段为varchar/char等字符串类型时，如果某字段为null此时保存到xml中的节点将不会显示，处理办法可以将保存xml的字段类型设置为image类型，具体参见示例。(记得在.aspx文件头中加入这句：<%@ Page validateRequest="false"  %>)

1.html

< HTML >
     < HEAD >
         < title > StreamXml </ title >
         < meta  name ="GENERATOR"  Content ="Microsoft Visual Studio .NET 7.1" >
         < meta  name ="CODE_LANGUAGE"  Content ="C#" >
         < meta  name ="vs_defaultClientScript"  content ="JavaScript" >
         < meta  name ="vs_targetSchema"  content ="http://schemas.microsoft.com/intellisense/ie5" >
     </ HEAD >
     < body  MS_POSITIONING ="GridLayout" >
         < form  id ="Form1"  method ="post"  runat ="server" >
             < asp:TextBox  id ="TextBox1"  style ="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 16px"  runat ="server" ></ asp:TextBox >
             < asp:TextBox  id ="TextBox2"  style ="Z-INDEX: 102; LEFT: 240px; POSITION: absolute; TOP: 16px"  runat ="server"
                TextMode ="MultiLine"  Height ="160px"  Width ="352px" ></ asp:TextBox >
             < asp:Button  id ="Button1"  style ="Z-INDEX: 103; LEFT: 184px; POSITION: absolute; TOP: 16px"  runat ="server"
                Text ="保存" ></ asp:Button >
             < asp:DataGrid  id ="DataGrid1"  style ="Z-INDEX: 104; LEFT: 8px; POSITION: absolute; TOP: 48px"  runat ="server"
                BorderColor ="#CC9966"  BorderStyle ="None"  BorderWidth ="1px"  BackColor ="White"  CellPadding ="4"
                AutoGenerateColumns ="False"  OnItemCommand ="Compare" >
                 < FooterStyle  ForeColor ="#330099"  BackColor ="#FFFFCC" ></ FooterStyle >
                 < SelectedItemStyle  Font-Bold ="True"  ForeColor ="#663399"  BackColor ="#FFCC66" ></ SelectedItemStyle >
                 < ItemStyle  ForeColor ="#330099"  BackColor ="White" ></ ItemStyle >
                 < HeaderStyle  Font-Bold ="True"  ForeColor ="#FFFFCC"  BackColor ="#990000" ></ HeaderStyle >
                 < Columns >
                     < asp:BoundColumn  Visible ="False"  DataField ="UserID"  HeaderText ="UserID" ></ asp:BoundColumn >
                     < asp:BoundColumn  DataField ="UserName"  HeaderText ="UserName" ></ asp:BoundColumn >
                     < asp:TemplateColumn  HeaderText ="比较" >
                         < ItemTemplate >
                             < asp:Button  id ="Button2"  runat ="server"  Text ="比较"  CommandName ="Compare" ></ asp:Button >
                         </ ItemTemplate >
                     </ asp:TemplateColumn >
                 </ Columns >
                 < PagerStyle  HorizontalAlign ="Center"  ForeColor ="#330099"  BackColor ="#FFFFCC" ></ PagerStyle >
             </ asp:DataGrid >
             < asp:TextBox  id ="TextBox3"  style ="Z-INDEX: 105; LEFT: 240px; POSITION: absolute; TOP: 192px"
                runat ="server"  TextMode ="MultiLine"  Height ="144px"  Width ="352px" ></ asp:TextBox >
         </ form >
     </ body >
</ HTML >

2.cs代码

public   class  StreamXml : System.Web.UI.Page
     {
        protected System.Web.UI.WebControls.TextBox TextBox1;
        protected System.Web.UI.WebControls.TextBox TextBox2;
        protected System.Web.UI.WebControls.Button Button1;
        protected System.Web.UI.WebControls.TextBox TextBox3;
        protected System.Web.UI.WebControls.DataGrid DataGrid1;
    
        private void Page_Load(object sender, System.EventArgs e)
        {
            if(!Page.IsPostBack)
            {
                BindData();
            }
        }

        BindData#region BindData
        private void BindData()
        {
            string sql="select * from UserInfo";
            DataSet ds=GetDataSet(sql);
            this.DataGrid1.DataSource=ds;
            this.DataGrid1.DataBind();
        }
        #endregion
        
        SaveXml#region SaveXml
        private  void SaveXml(DataSet ds)
        {
            SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand comm=new SqlCommand("insert into StreamXml (UserID,XmlStream,XmlString) values(@UserID,@XmlStream,@XmlString)",conn);
            
            SqlParameter parm1=new SqlParameter("@UserID",SqlDbType.UniqueIdentifier,50);
            parm1.Value=new Guid(ds.Tables[0].Rows[0]["UserID"].ToString());
            
            //以流方式保存xml
            System.IO.MemoryStream mStream = new System.IO.MemoryStream();
            ds.WriteXml(mStream, XmlWriteMode.WriteSchema);
            SqlParameter parm2=new SqlParameter("@XmlStream",SqlDbType.Image);
            parm2.Value=mStream.ToArray();
            //以字符串方式保存xml
            SqlParameter parm3=new SqlParameter("@XmlString",SqlDbType.NVarChar,500);
            parm3.Value=ds.GetXml();

            comm.Parameters.Add(parm1);
            comm.Parameters.Add(parm2);            
            comm.Parameters.Add(parm3);    
            conn.Open();
            comm.ExecuteNonQuery();
            conn.Close();
        }
        #endregion

        SaveUserInfo#region SaveUserInfo
        private  void SaveUserInfo()
        {
            SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
            SqlCommand comm=new SqlCommand("insert into UserInfo (UserID,UserName) values(@UserID,@UserName)",conn);
            
            SqlParameter parm1=new SqlParameter("@UserID",SqlDbType.UniqueIdentifier,50);
            parm1.Value=new Guid(UserID);    

            SqlParameter parm2=new SqlParameter("@UserName",SqlDbType.NVarChar,50);
            if(this.TextBox1.Text!="")
            {
                parm2.Value=this.TextBox1.Text;
            }
            else
            {
                parm2.Value=System.DBNull.Value;
            }
            comm.Parameters.Add(parm1);
            comm.Parameters.Add(parm2);            
            conn.Open();
            comm.ExecuteNonQuery();
            conn.Close();
        }
        #endregion

        GetDataSet#region GetDataSet
        private DataSet GetDataSet(string sql)
        {
            string constring=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
            SqlDataAdapter    sda =new SqlDataAdapter(sql,constring);
            DataSet ds=new DataSet();
            sda.Fill(ds);
            return ds;
        }
        #endregion

        Compare event#region Compare event
        protected void Compare(object sender,DataGridCommandEventArgs e)
        {
            switch(Convert.ToString(((Button)e.CommandSource).CommandName))
            {
                case "Compare":                    
                    DataSet ds = new DataSet();
                    string sql="select * from StreamXml where UserID='"+e.Item.Cells[0].Text.ToString()+"'";
                    DataSet dsxml=GetDataSet(sql);            
                    Byte[] stream = (Byte[])dsxml.Tables[0].Rows[0]["XmlStream"];
                    ds.ReadXml(new System.IO.MemoryStream(stream),System.Data.XmlReadMode.ReadSchema);
                    
                    this.TextBox2.Text=ds.GetXml();//流
                    this.TextBox3.Text=dsxml.Tables[0].Rows[0]["XmlString"].ToString();//字符串
                    break;
            }            
        }
        #endregion

        Web Form Designer generated code#region Web Form Designer generated code
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            //
            InitializeComponent();
            base.OnInit(e);
        }
        
        /**//// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {    
            this.Button1.Click += new System.EventHandler(this.Button1_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion

        save#region save
        private void Button1_Click(object sender, System.EventArgs e)
        {
            string sql="select * from UserInfo where 1=2";
            DataSet ds=GetDataSet(sql);            
            DataRow row;
            if( ds.Tables[0].Rows.Count == 0 )
            {    
                row    = ds.Tables[0].NewRow();                
                row["UserID"]=System.Guid.NewGuid().ToString();
                UserID=row["UserID"].ToString();
                if(this.TextBox1.Text!="")
                {
                    row["UserName"]=this.TextBox1.Text;
                }
                else
                {
                    row["UserName"]=System.DBNull.Value;
                }
                ds.Tables[0].Rows.Add(row);
            }            
            SaveUserInfo();    

            SaveXml(ds);
            BindData();
        }
        #endregion

        property#region property
        private string UserID
        {
            get
            {
                if(ViewState["UserID"]!=null && ViewState["UserID"].ToString()!="")
                {
                    return ViewState["UserID"].ToString();
                }
                else
                {
                    return System.Guid.NewGuid().ToString();
                }
            }
            set
            {
                ViewState["UserID"]=value;
            }
        }
        #endregion
    }

3.数据库脚本

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[StreamXml] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ dbo ] . [ StreamXml ]
GO

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[UserInfo] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ dbo ] . [ UserInfo ]
GO

CREATE   TABLE   [ dbo ] . [ StreamXml ]  (
     [ UserID ]   [ uniqueidentifier ]   NULL  ,
     [ XmlStream ]   [ image ]   NULL  ,
     [ XmlString ]   [ varchar ]  ( 500 ) COLLATE Chinese_PRC_CI_AS  NULL  
)  ON   [ PRIMARY ]  TEXTIMAGE_ON  [ PRIMARY ]
GO

CREATE   TABLE   [ dbo ] . [ UserInfo ]  (
     [ UserID ]   [ uniqueidentifier ]   NULL  ,
     [ UserName ]   [ varchar ]  ( 50 ) COLLATE Chinese_PRC_CI_AS  NULL  
)  ON   [ PRIMARY ]
GO