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 "<NewDataSe t />". 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.P age
{
DataSet ds;
protected void Page_Load(objec t sender, EventArgs e)
{
ds = new DataSet();
}
protected void Button1_Click(o bject sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@ "Data Source=.\SQLEXP RESS;Initial Catalog=SqlXmlT est;Integrated Security=True") ;
conn.Open();

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

ds.ReadXml(comm .ExecuteXmlRead er());
lit.Text = Server.HtmlEnco de(ds.GetXml()) ;

conn.Close();

DataGrid1.DataS ource = ds;
DataGrid1.DataM ember = "Goal";
DataGrid1.DataB ind();
}
protected void Button2_Click(o bject sender, EventArgs e)
{
lit.Text = Server.HtmlEnco de(ds.GetXml()) ;
}
}

--
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )

[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_ms n_DOT_comwrote in message
news:eu******** ******@TK2MSFTN GP02.phx.gbl...
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 "<NewDataSe t />". 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.P age
{
DataSet ds;
protected void Page_Load(objec t sender, EventArgs e)
{
ds = new DataSet();
}
protected void Button1_Click(o bject sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@ "Data
Source=.\SQLEXP RESS;Initial Catalog=SqlXmlT est;Integrated Security=True") ;
conn.Open();

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

ds.ReadXml(comm .ExecuteXmlRead er());
lit.Text = Server.HtmlEnco de(ds.GetXml()) ;

conn.Close();

DataGrid1.DataS ource = ds;
DataGrid1.DataM ember = "Goal";
DataGrid1.DataB ind();
}
protected void Button2_Click(o bject sender, EventArgs e)
{
lit.Text = Server.HtmlEnco de(ds.GetXml()) ;
}
}

--
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )

[sloan]

Here is a refactor:

public partial class SqlXml : System.Web.UI.P age
{
DataSet m_modelDS;
protected void Page_Load(objec t 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=.\SQLEXP RESS;Initial Catalog=SqlXmlT est;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.ReadX ml(comm.Execute XmlReader());
lit.Text = Server.HtmlEnco de(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.DataS ource = m_modelDS;
DataGrid1.DataM ember = "Goal";
DataGrid1.DataB ind();
}
}
protected void Button1_Click(o bject sender, EventArgs e)
{

LoadData()
PersistData();
BindData()

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

lit.Text = Server.HtmlEnco de(m_modelDS.Ge tXml());
}
}


"Greg Collins [Microsoft MVP]" <gcollins_AT_ms n_DOT_comwrote in message
news:eu******** ******@TK2MSFTN GP02.phx.gbl...
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 "<NewDataSe t />". 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.P age
{
DataSet ds;
protected void Page_Load(objec t sender, EventArgs e)
{
ds = new DataSet();
}
protected void Button1_Click(o bject sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@ "Data
Source=.\SQLEXP RESS;Initial Catalog=SqlXmlT est;Integrated Security=True") ;
conn.Open();

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

ds.ReadXml(comm .ExecuteXmlRead er());
lit.Text = Server.HtmlEnco de(ds.GetXml()) ;

conn.Close();

DataGrid1.DataS ource = ds;
DataGrid1.DataM ember = "Goal";
DataGrid1.DataB ind();
}
protected void Button2_Click(o bject sender, EventArgs e)
{
lit.Text = Server.HtmlEnco de(ds.GetXml()) ;
}
}

--
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )

[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.

--
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )