XML, DataSet, DataGrid

[Greg Collins [Microsoft MVP]]

I have a SQL2005 XML column I load into a DataSet.
I then bind to a DataGrid.

If I set an asp:Literal text value to the DataSet.GetXml() when I first load it into the DataSet, it displays correctly.
If I later try to display the XML content in the asp:Literal control, then I get "<NewDataSet />". Why is that? Why can't I get the actual XML back from the DataSet? I'm new to DataSets, so I'm probably just missing something basic.

Here's the code... I'm leaving out the ASP.NET markup... if you feel you need to see that too, please let me know.

Basically, I have a text box where the user enters a SQL record #, then they click a button to load it. Then a second button is clicked to display the XML content in the literal control. Currently I also fill the literal with the XML in the first button click for debug purposes.


public partial class SqlXml : System.Web.UI.Page
{
DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
ds = new DataSet();
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=SqlXmlTest;Integrated Security=True");
conn.Open();

string sql = "SELECT [Data] FROM [Record] WHERE ([UserId] = " + tbUserId.Text + ")";
SqlCommand comm = new SqlCommand(sql, conn);

ds.ReadXml(comm.ExecuteXmlReader());
lit.Text = Server.HtmlEncode(ds.GetXml());

conn.Close();

DataGrid1.DataSource = ds;
DataGrid1.DataMember = "Goal";
DataGrid1.DataBind();
}
protected void Button2_Click(object sender, EventArgs e)
{
lit.Text = Server.HtmlEncode(ds.GetXml());
}
}

 

[sloan]

The member variable (ds) does not persist .......

You have to persist it somewhere.

So basically, when you click the button, your code does a page_load, and all
you have is (ds = new DataSet())

You have to persist the ds to something.

Session, Application, ViewState .... something if you want to "reclaim it"
on a postback page.





"Greg Collins [Microsoft MVP]" <gcollins_AT_msn_DOT_com> wrote in message
I have a SQL2005 XML column I load into a DataSet.
I then bind to a DataGrid.

If I set an asp:Literal text value to the DataSet.GetXml() when I first load
it into the DataSet, it displays correctly.
If I later try to display the XML content in the asp:Literal control, then I
get "<NewDataSet />". Why is that? Why can't I get the actual XML back from
the DataSet? I'm new to DataSets, so I'm probably just missing something
basic.

Here's the code... I'm leaving out the ASP.NET markup... if you feel you
need to see that too, please let me know.

Basically, I have a text box where the user enters a SQL record #, then they
click a button to load it. Then a second button is clicked to display the
XML content in the literal control. Currently I also fill the literal with
the XML in the first button click for debug purposes.


public partial class SqlXml : System.Web.UI.Page
{
DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
ds = new DataSet();
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data
Source=.\SQLEXPRESS;Initial Catalog=SqlXmlTest;Integrated Security=True");
conn.Open();

string sql = "SELECT [Data] FROM [Record] WHERE ([UserId] = " +
tbUserId.Text + ")";
SqlCommand comm = new SqlCommand(sql, conn);

ds.ReadXml(comm.ExecuteXmlReader());
lit.Text = Server.HtmlEncode(ds.GetXml());

conn.Close();

DataGrid1.DataSource = ds;
DataGrid1.DataMember = "Goal";
DataGrid1.DataBind();
}
protected void Button2_Click(object sender, EventArgs e)
{
lit.Text = Server.HtmlEncode(ds.GetXml());
}
}

 

[sloan]

Here is a refactor:





public partial class SqlXml : System.Web.UI.Page
{
DataSet m_modelDS;
protected void Page_Load(object sender, EventArgs e)
{
// m_modelDS= new DataSet(); // you don't need this
}

private void LoadData()

{

if (null!= Session["mykey"] )
{
m_modelDS = Session["mykey"] as DataSet;
if (null!= this.m_modelDS)
{
return;
}
}


SqlConnection conn = new SqlConnection(@"Data
Source=.\SQLEXPRESS;Initial Catalog=SqlXmlTest;Integrated Security=True");
conn.Open();

string sql = "SELECT [Data] FROM [Record] WHERE ([UserId] = " +
tbUserId.Text + ")";
SqlCommand comm = new SqlCommand(sql, conn);

m_modelDS = new DataSet();//you may or may not needt his I cant
remember off hand.

m_modelDS.ReadXml(comm.ExecuteXmlReader());
lit.Text = Server.HtmlEncode(ds.GetXml());

conn.Close();

}

private void PersistData()
{
if (null!= this.m_modelDS)
{
Session["mykey"] = this.m_modelDS;
}
}

private void BindData()
{
if(null!= this.m_modelDS)
{
DataGrid1.DataSource = m_modelDS;
DataGrid1.DataMember = "Goal";
DataGrid1.DataBind();
}
}


protected void Button1_Click(object sender, EventArgs e)
{

LoadData()
PersistData();
BindData()

}
protected void Button2_Click(object sender, EventArgs e)
{
LoadData(); // follow this, and if its not the first run thru, it
shoudl get teh Session object .

lit.Text = Server.HtmlEncode(m_modelDS.GetXml());
}
}








"Greg Collins [Microsoft MVP]" <gcollins_AT_msn_DOT_com> wrote in message
I have a SQL2005 XML column I load into a DataSet.
I then bind to a DataGrid.

If I set an asp:Literal text value to the DataSet.GetXml() when I first load
it into the DataSet, it displays correctly.
If I later try to display the XML content in the asp:Literal control, then I
get "<NewDataSet />". Why is that? Why can't I get the actual XML back from
the DataSet? I'm new to DataSets, so I'm probably just missing something
basic.

Here's the code... I'm leaving out the ASP.NET markup... if you feel you
need to see that too, please let me know.

Basically, I have a text box where the user enters a SQL record #, then they
click a button to load it. Then a second button is clicked to display the
XML content in the literal control. Currently I also fill the literal with
the XML in the first button click for debug purposes.


public partial class SqlXml : System.Web.UI.Page
{
DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
ds = new DataSet();
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data
Source=.\SQLEXPRESS;Initial Catalog=SqlXmlTest;Integrated Security=True");
conn.Open();

string sql = "SELECT [Data] FROM [Record] WHERE ([UserId] = " +
tbUserId.Text + ")";
SqlCommand comm = new SqlCommand(sql, conn);

ds.ReadXml(comm.ExecuteXmlReader());
lit.Text = Server.HtmlEncode(ds.GetXml());

conn.Close();

DataGrid1.DataSource = ds;
DataGrid1.DataMember = "Goal";
DataGrid1.DataBind();
}
protected void Button2_Click(object sender, EventArgs e)
{
lit.Text = Server.HtmlEncode(ds.GetXml());
}
}

 

[Greg Collins [Microsoft MVP]]

Maybe I'm just too novice with this stuff.

What my original intention was (though surely I missed in my execution of it) was to
1. Pull XML from SQL
2. Load it into a DataSet.
3. Bind it to some ASP.NET control for editing.
4. Allow editing of the data.
5. Get the updated data from the control as XML
6. Post it back to SQL.

The sample code was an attempt to do 1-5 above... my novice assumption was that the dataset would automatically be updated during the edtiting of the datagrid due to the binding--therefore it seemed the most likely place to go for the XML, which was surprisingly blank.

As I understand your update, button2 will pull the stored "original" xml from the session variable, and not the updated xml after the user has completed their edits.