Hi Everyone,
I have looked at a number of threads from various sites with similar problems. However, any code posted as a solution I have not been able to get working in the context of my Web App.
I’m having trouble taking a query, whether it be via LINQ to SQL or SqlDataSource and then passing/converting it to a DataSet.
The reason it goes to a DataSet is because I have also taken an example of a paging Repeater that uses DataSet but reads an XML. Implementing it would cut down on loading time as there’ll be a lot of records in the Repeater thus makes it easier for the end-user.
I’m completely baffled on this, my last attempt, in the code below, I try taking the results from a SQL Query and creating an XML from the them, to which the DataSet.ReadXML() method can read, this is a long inefficient way to do it and even so, it doesnt work at that.
My preferred way would be to take the results from a ‘Guestbook.GetModerated()’ method I have ready via LINQ to SQL which returns results in the form of a Generic list and pass that through etc. …something like DataSet items = Guestbook.GetModerated();
That returns a error stating it cannot convert Generic List to DataSet and I dont know how to convert it 
Any help is much appreciated.
public void ItemsGet()
{
DataSet Items = new DataSet();
string ConnectionString = @"Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\BookingSys.mdf;Integrated Security=True;User Instance=True";
string QueryString = "SELECT * FROM [tbl_Guestbook] WHERE Moderated = 1 ORDER BY GuestbookID DESC for XML PATH";
SqlConnection con = new SqlConnection(ConnectionString);
SqlDataSource SqlDataSource = new SqlDataSource();
con.Open();
using (con)
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM [tbl_Guestbook] WHERE Moderated = 1 ORDER BY GuestbookID DESC for XML PATH";
XmlReader r = cmd.ExecuteXmlReader();
XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
XmlWriter w = XmlWriter.Create(@"\\Temp\\gkInfoModerated.xml", settings);
using (w)
{
w.WriteStartElement("root");
while (!r.EOF)
{
w.WriteNode(r, true);
}
w.WriteEndElement();
}
}
SqlDataSource.ConnectionString = ConnectionString;
SqlDataSource.SelectCommand = QueryString;
Items.ReadXml(MapPath(@"\\Temp\\gkInfoModerated.xml"));
// Populate the repeater control with the Items DataSet
PagedDataSource objPds = new PagedDataSource();
objPds.DataSource = Items.Tables[0].DefaultView;
objPds.AllowPaging = true;
objPds.PageSize = 10;
objPds.CurrentPageIndex = CurrentPage;
lblCurrentPage.Text = "Page: " + (CurrentPage + 1).ToString() + " of "
+ objPds.PageCount.ToString();
// Disable Prev or Next buttons if necessary
cmdPrev.Enabled = !objPds.IsFirstPage;
cmdNext.Enabled = !objPds.IsLastPage;
rptGuestbookView.DataSource = objPds;
rptGuestbookView.DataBind();
}
Regards,
Daniel 