SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Non-Member
    Join Date
    Mar 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation SqlDataSource / LINQ to SQL to DataSet

    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.

    Code Csharp:
    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

  2. #2
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    You are most likely going to have to write an extension method. There is one in System.Data.DataSetExtensions, but it only works with IEnumerable<DataRow>. If generic T is anything else, the method won't show. Take a look at this blog for a better explanation of how to do this.

    http://blogs.msdn.com/b/aconrad/arch...e-project.aspx


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •