Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2149
  • Last Modified:

Dynamic setting up jQuery Flexigrid in Dot Net

With a good deal of wisdom from one of the Experts here I have built a little application in Dot Net that makes a valid JSON string for flexigrid and then displays it and it looks fine (although the date field is not as expected but appears like "Date (1349440000)" or simlar).

Is it possible to access the URL property of the flexigrid at run time to point it to another file/URL and also can I build all the colModel elements which represent the columns that the JSON string data contains?
0
Howard Bash
Asked:
Howard Bash
  • 12
  • 7
3 Solutions
 
leakim971PluritechnicianCommented:
Hello hbash,

>Is it possible to access the URL property of the flexigrid at run time to point it to another file/URL ?
Yes, you can change the url before reload the grid with : $("#flex1").flexigrid()[0].p.url = "path/to/newFile.txt";

>can I build all the colModel elements which represent the columns that the JSON string data contains?
Yes, you need to add the function onSuccess as parameter in the json parameter of the flexigrid

Check the following :

(look the onSuccess json member at the end)





$("#flex1").flexigrid({
            url: 'json.txt',
            method: 'GET',
            dataType: 'json',
            colModel: [
                { display: 'FirstName', name: 'fn', width: 40, sortable: true, align: 'center' },
                { display: 'LastName', name: 'ln', width: 180, sortable: true, align: 'left' },
                { display: 'Creation Date', name: 'cd', width: 120, sortable: true, align: 'left' },
                { display: 'Last Access Date', name: 'lad', width: 130, sortable: true, align: 'left' },
                { display: 'Description', name: 'desc', width: 80, sortable: true, align: 'right' }
            ],
            buttons: [
                { name: 'Add', bclass: 'add', onpress: test },
                { name: 'Delete', bclass: 'delete', onpress: test },
                { separator: true }
            ],
            searchitems: [
                { display: 'FirstName', name: 'iso' },
                { display: 'LastName', name: 'name', isdefault: true }
            ],
            sortname: "FirstName",
            sortorder: "asc",
            usepager: true,
            singleSelect: true,
            title: 'Persons',
            useRp: true, rp: 15,
            showTableToggleBtn: true,
            width: 700,
            height: 200,
            onSuccess: function (data) {
                $("tbody tr", "#flex1").each(function () {
                    cd = $("td div", $(this)).eq(2).text().replace(/\/Date\(|\)\//g, ""); // remove /Date( and )/ in the third column
                    la = $("td div", $(this)).eq(3).text().replace(/\/Date\(|\)\//g, ""); // remove /Date( and )/ in the fourth column
                    $("td div", $(this)).eq(2).text(cd); // replace the value of the third column
                    $("td div", $(this)).eq(3).text(la); // replace the value of the fourth column
                });
            }
        });

Open in new window

0
 
Howard BashSenior Software EngineerAuthor Commented:
What I am looking to do is to build the grid based on the dataset / datatable/ resultset.  So,  along with building the JSON string,  unless the grid will only be used for a particular query,  I would need to change the colModel column descriptions as wellas the sortname.

I am not clear about what  your onSuccess function does.  Can you explain it a bit?

Thanks.
0
 
leakim971PluritechnicianCommented:
>I am not clear about what  your onSuccess function does.  Can you explain it a bit?

Yes of course. the onSuccess function is called just after the table (the flexigrid is a table of course) is populated with the json content.
So you can modify the table content if needed.
In the example (previous post) we remove the << /Date( >> and << )/ >> in the date columns (Creation Date and Last Access Date)
 
More info about onSuccess here : http://api.jquery.com/jQuery.ajax/

>What I am looking to do is to build the grid based on the dataset / datatable/ resultset

I understand, you may reconsider a solution with a web service
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
leakim971PluritechnicianCommented:
Just for information you have better documented jquery plugin to manage table for example :

http://www.webdesignbooth.com/15-great-jquery-plugins-for-better-table-manipulation/

But perhaps you already saw this and did a choice...
0
 
Howard BashSenior Software EngineerAuthor Commented:
I hope I won't need the web service.  I wrote an HTTPHandler ProcessRequest which build and returns the JSON string.  It accepts a dataset and is based on one of the links you sent.  So,  if I can tweak a page with the flexigrid (change the colModel column descriptions,  repoint the URL property,  changed the sortname) and then have the grid refresh,  I "think"  I can get there without the web service.

Is the onSuccess event too late to modify the grid column attributes as the JSON string will have already been loaded?

I would have thought the grid start with the colModel section as follows:
 colModel: [
                ]

and you would insert lines like the following between the square brackets.  If that is the case you can build these "display:" lines dynamically for the grid.
                { display: 'FirstName', name: 'fn', width: 40, sortable: true, align: 'center' }
of course I haven't a clue how to do that as well.


0
 
Howard BashSenior Software EngineerAuthor Commented:
I haven't seen the list you referred to in the 15-great-jquery-plugins...  Any recommendations on alternative grids?  Flexigrid is a good looking widget.
0
 
leakim971PluritechnicianCommented:
> if I can tweak a page with the flexigrid (change the colModel column descriptions,
yes you, globaly all the parameters is referenced by this object : $("#flex1").flexigrid()[0].p.
So your colModel array(5) is referenced by : $("#flex1").flexigrid()[0].p.colModel

$("#flex1").flexigrid()[0].p.colModel[0].display return FirstName
$("#flex1").flexigrid()[0].p.colModel[1]["display"] return LastName
$("#flex1").flexigrid()[0].p.colModel[2].display return Creation Date

you can modify column name :
$("#flex1").flexigrid()[0].p.colModel[0]["display"] = "New Column Name"

>Is the onSuccess event too late to modify the grid column attributes as the JSON string will have already been loaded?
Yes and no. Yes because the table is already populated with json value. No because you get the hand just after, on a fast PC, the process of changing the table content is not visible I think. So it's eventually a visual problem

>Any recommendations on alternative grids?
Depend the time you have to achieve your project, I would go to DataTables better documented. Check the examples.

For flexigrid, I can propose you a part of code for the web service if needed.
0
 
Howard BashSenior Software EngineerAuthor Commented:
Can I add columns to the colModel "collection" ?
0
 
Howard BashSenior Software EngineerAuthor Commented:
And I would love an example of the web service coding technique you have in mind. Thanks.
0
 
leakim971PluritechnicianCommented:
Yes, of course :

(you should have data for it in your json string)
nColumn = $("#flex1").flexigrid()[0].p.colModel.length;
$("#flex1").flexigrid()[0].p.colModel[nColumn] = { display: 'MiddleName', name: 'mn', width: 40, sortable: true, align: 'center' };

Open in new window

0
 
leakim971PluritechnicianCommented:
>And I would love an example of the web service coding technique you have in mind.

We use DataContract : http://msdn.microsoft.com/en-us/library/ms731138.aspx

The one representing the final JSON object :

mean : { "page":<< page number >>, "total":<< total >>, "rows": [ << list of rows >> ] }
<DataContract()> _
    Public Class Persons

        Private _page As Integer
        Private _total As Integer
        Private _rows As List(Of Row)

        <DataMember(Name:="page")> _
        Public Property page() As Integer
            Get
                Return _page
            End Get
            Set(ByVal value As Integer)
                _page = value
            End Set
        End Property

        <DataMember(Name:="total")> _
        Public Property total() As Integer
            Get
                Return _total
            End Get
            Set(ByVal value As Integer)
                _total = value
            End Set
        End Property

        <DataMember(Name:="rows")> _
        Public Property rows() As List(Of Row)
            Get
                Return _rows
            End Get
            Set(ByVal value As List(Of Row))
                _rows = value
            End Set
        End Property

        Public Sub New()
        End Sub

        Public Sub New(ByVal page As Integer, ByVal total As String, ByVal rows As List(Of Row))
            Me._page = page
            Me._total = total
            Me._rows = rows
        End Sub

    End Class

Open in new window

0
 
leakim971PluritechnicianCommented:
In the previous DataContract we use the following in code snippet :

mean a row : { "id":<< id of the row >>, "cell": << list of cells >> }

<DataContract()>
    Public Class Row
        Private _id As String
        Private _cell As List(Of String)

        <DataMember(Name:="id")> _
        Public Property id() As String
            Get
                Return _id
            End Get
            Set(ByVal value As String)
                _id = value
            End Set
        End Property

        <DataMember(Name:="cell")> _
        Public Property cell() As List(Of String)
            Get
                Return _cell
            End Get
            Set(ByVal value As List(Of String))
                _cell = value
            End Set
        End Property

        Public Sub New()
        End Sub

        Public Sub New(ByVal id As String, ByVal cell As List(Of String))
            Me._id = id
            Me._cell = cell
        End Sub

    End Class

Open in new window

0
 
leakim971PluritechnicianCommented:
The web method getPersons() using the DataContract in the code snippet :

(I don't use a DataTable but manually create the data as you can see)


<WebMethod()> _
    <Script.Services.ScriptMethod(ResponseFormat:=Script.Services.ResponseFormat.Json)> _
    Public Function getPersons() As String

        Dim l1 As List(Of String) = New List(Of String)() From {"Howard", "Ba", "01/01/2010", "01/01/2010", "This is a test record and also the first record."}
        Dim l2 As List(Of String) = New List(Of String)() From {"Ron", "Ba", "01/01/2010", "01/01/2010", "This is the second record added"}
        Dim l3 As List(Of String) = New List(Of String)() From {"Net", "Ba", "01/01/2010", "01/01/2010", "Duh Mud"}
        Dim row1 As New Row("1", l1)
        Dim row2 As New Row("2", l2)
        Dim row3 As New Row("3", l3)

        Dim rows As New List(Of Row)
        rows.Add(row1)
        rows.Add(row2)
        rows.Add(row3)

        Dim persons As New Persons(1, 1, rows)

        Dim ms As MemoryStream = New MemoryStream()
        Dim ser As DataContractJsonSerializer = New DataContractJsonSerializer(GetType(Persons))
        ser.WriteObject(ms, persons)
        Dim json() As Byte = ms.ToArray()
        ms.Close()
        Return Encoding.UTF8.GetString(json, 0, json.Length)

    End Function

Open in new window

0
 
leakim971PluritechnicianCommented:
Getting the data from the database :


<WebMethod()> _
    <Script.Services.ScriptMethod(ResponseFormat:=Script.Services.ResponseFormat.Json)> _
    Public Function getPersons() As String

        Dim myConn As SqlConnection = New SqlConnection("Initial Catalog=ExpertsExchange;Data Source=localhost;Integrated Security=SSPI;")
        Dim myCmd As SqlCommand = myConn.CreateCommand()
        myCmd.CommandText = "SELECT id,FirstName,LastName,CreationDate,LastAccessDate,Description FROM dbo.hbash"
        myConn.Open()
        Dim myReader As SqlDataReader = myCmd.ExecuteReader()
        Dim rows As New List(Of Row)
        Do While myReader.Read()
            rows.Add(New Row(myReader.GetInt32(0), New List(Of String)() From {myReader.GetString(1), myReader.GetString(2), myReader.GetDateTime(3).ToString(), myReader.GetDateTime(4).ToString(), myReader.GetString(5)}))
        Loop

        Dim persons As New Persons(1, rows.Count, rows)

        Dim ms As MemoryStream = New MemoryStream()
        Dim ser As DataContractJsonSerializer = New DataContractJsonSerializer(GetType(Persons))
        ser.WriteObject(ms, persons)
        Dim json() As Byte = ms.ToArray()
        ms.Close()
        Return Encoding.UTF8.GetString(json, 0, json.Length)

    End Function

Open in new window

0
 
Howard BashSenior Software EngineerAuthor Commented:
In the previous post you have ... From {myReader.GetString(1), ...

What is "From"?
0
 
leakim971PluritechnicianCommented:
a keyword to initialize collection, replace by :

New List(Of String)(New String() {myReader.GetString(1
0
 
Howard BashSenior Software EngineerAuthor Commented:
This line:
        Do While myReader.Read()
-->>            rows.Add(New Row(myReader.GetInt32(0), New List(Of String)() {myReader.GetString(1), myReader.GetString(2), myReader.GetDateTime(3).ToString(), myReader.GetDateTime(4).ToString(), myReader.GetString(5)}))
        Loop

does not compile.

"Value of type 'String' cannot be converted to 'System.Collections.Generic.List(Of String) "


0
 
leakim971PluritechnicianCommented:
You miss (New String()


            rows.Add(New Row(myReader.GetInt32(0), New List(Of String)(New String() {myReader.GetString(1), myReader.GetString(2), myReader.GetDateTime(3).ToString(), myReader.GetDateTime(4).ToString(), myReader.GetString(5)})))
0
 
leakim971PluritechnicianCommented:
Thanks for the points!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 12
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now