Results 1 to 6 of 6

Thread: [RESOLVED] How do I change this code to the results of a query?

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [RESOLVED] How do I change this code to the results of a query?

    I am using MVC and JSON and a bunch of other things that are new to me, but my basic problem is this (please don't think I am in the wrong forum. This is a database question, I believe...)

    How do I take something hardcoded like this:
    Code:
            public JsonResult GridData(string sidx, string sord, int page, int rows)
            {
                int totalPages = 1; // we'll implement later
                int pageSize = rows;
                int totalRecords = 3; // implement later
    
                var jsonData = new
                {
                    total = totalPages,
                    page = page,
                    records = totalRecords,
                    rows = new[]{
                        new {id = 1, cell = new[] {"1", "-7", "Is this a good question?"}},
                        new {id = 2, cell = new[] {"2", "15", "Is this a blatant ripoff?"}},
                        new {id = 3, cell = new[] {"3", "23", "Why is the sky blue?"}}
                    }
                };
                return Json(jsonData);
            }
    and change the "rows" section to get back three rows (or more) from a stored procedure call? If I get back a dataset, I would have my three rows. But how do I process the dataset using a loop such as this?

    The big picture of what I want to do is get data that will be presented in a grid and serialize it so it's JSON and the client can process it. Most of my work is done for me in this snippet, except I'm stuck on the most critical part!

    Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How do I change this code to the results of a query?

    Either like the outer look or the inner loop - depending on if your rows are DATAROWVIEW or DATAROW

    Code:
    For Each drv2 As DataRowView In casefileassocBS.List
        If drv2("FileId").ToString = lvie.Tag.ToString Then
            For Each drv3 As DataRow In caseBAL_C.casefileTable.Rows
                If drv3("FileId").ToString = drv2("AssocFileId").ToString Then
                    cfa = drv3("VisualName").ToString
                End If
            Next
        End If
    Next

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: How do I change this code to the results of a query?

    Got it:

    Code:
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    XXXSearchResults srchResult = new XXXSearchResults
                    {
                        Name = dr.ItemArray[0].ToString(),
                        Fax = dr.ItemArray[3].ToString(),
                        State = dr.ItemArray[4].ToString(),
                        SecurityQAndA = dr.ItemArray[5].ToString().Trim() + "  " + dr.ItemArray[6].ToString().Trim(),
                        ActionLink = dr.ItemArray[7].ToString()
                    };
                    searchResults.Add(srchResult);
                }
                JsonResult jJsonSerialized = Json(searchResults, JsonRequestBehavior.AllowGet);
    
                return jJsonSerialized;
    Which is actually modeled after:
    Code:
                List<PhoneNumber> phoneNumbers = new List<PhoneNumber>();
    
                PhoneNumber num1 = new PhoneNumber { Number = "555 123 4567", Description = "George" }; 
                PhoneNumber num2 = new PhoneNumber { Number = "555 765 4321", Description = "Kevin" }; 
                PhoneNumber num3 = new PhoneNumber { Number = "555 555 4781", Description = "Sam" }; 
    
                phoneNumbers.Add(num1); 
                phoneNumbers.Add(num2); 
                phoneNumbers.Add(num3); 
    
                return Json(phoneNumbers, JsonRequestBehavior.AllowGet);
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How do I change this code to the results of a query?

    Quote Originally Posted by MMock View Post
    Which is actually modeled after:
    Code:
                List<PhoneNumber> phoneNumbers = new List<PhoneNumber>();
                PhoneNumber num1 = new PhoneNumber { Number = "555 123 4567", Description = "George" }; 
    ...            phoneNumbers.Add(num1); 
    ...            return Json(phoneNumbers, JsonRequestBehavior.AllowGet);
    With my datarow loop though

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: How do I change this code to the results of a query?

    Absolutely! I even ran your code through a C# converter because I wasn't sure how to do a foreach in C#. I just meant what I coded was more like that example than what I posted in #1 which I really don't quite understand because of the extra fields in the jsonData - totalrecords, totalpages, pagesize.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: How do I change this code to the results of a query?

    I put "extras" in my json return data all the time - to indicate how I might want the browser to hide or show buttons. JSON is so flexible in that regard.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

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



Click Here to Expand Forum to Full Width