connect jqgrid to database

I have a jqgrid and I want to connect to database in c# (Webforms) and I write below code,but it doesn t show any data in my jqgrid

would you please check my code and tell me what should I do?

<!------Gridpage.aspx-----------!>

<head runat="server">
    <title></title>
     <link rel="Stylesheet" type="text/css" media="screen" href="Themes/jquery-ui-1.8.2.custom.css" />
     <link rel="Stylesheet" type="text/css" media="screen" href="Themes/ui.jqgrid.css" />
     <script type="text/javascript" src="js/jquery-1.4.2.min.js"></script>
     <script type="text/javascript" src="js/grid.locale-en.js"></script>
     <script type="text/javascript" src="js/jquery.jqGrid.min.js"></script>
     <script type="text/javascript" >
         jQuery(document).ready(function () {
             jQuery("#Grid1").jqGrid({
                 url: 'data.aspx',
                 datatype: 'json',
                 mtype: 'GET',
                 colNames: ['BookID', 'BookName'],
                 colModel: [{ name: 'BookID', index: 'BookID', width: 200 },
                   { name: 'BookName', index: 'BookName', width: 300}],
                 pager: jQuery('#pager'),
                 sortname: 'id',
                 viewrecoreds: true,
                 imgpath: 'Themes/images'
             }).navGrid(pager,{edit:true,add:true,del:true,refresh:true,search:true});
         });


       

     </script>
</head>
<body>
    <form id="form1" runat="server">
    <table id="Grid1" class="scroll" align="center" width="100%"></table>
    <div id="pager" class="scroll" style="text-align:center;">
    

              

    

    </div>
    </form>
</body>

   public DataTable GetDataTable(string sidx, string sord, int page, int pageSize)
        {
            int startIndex = (page - 1) * pageSize;
            int endIndex = page * pageSize;
            string sql = @"WITH PAGED_CUSTOMERS  AS
                   { 
                        SELECT  BookID, BookName
                        ROW_NUMBER() OVER (ORDER BY " + sidx + @" " + sord + @") AS RowNumber
                        FROM BOOKS
                   }";
            DataTable dt = new DataTable();
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=.;Initial Catalog=library;Integrated Security=True"].ConnectionString);
            SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlcon);
            var rows = adapter.Fill(dt);
            Console.Write("rows");
            return dt;

        }

Re: connect jqgrid to database

ofcourse it wont . in grid.base.js check out addJSONData and see how result should be sent from server

To give you some idea modified your code . go through this

public struct s_GridResult
    {
      public int page;             // Current page of data that is being viewed/requested
      public int total;            // Total number of pages avaialble to view in the entire list
      public int records;          // Number of records available in the rows[] array
      public s_RowData[] rows;     // Rows of data
    }


    public struct s_RowData
    {
      public int id;               // ItemID value for the row
      public string[] cell;        // Array of strings that hold the field values for the given row
    }


///




public s_GridResult GetDataTable(string sidx, string sord, int page, int pageSize)
        {
            int startIndex = (page - 1) * pageSize;
            int endIndex = page * pageSize;
            string sql = @"WITH PAGED_CUSTOMERS  AS
                   {
                        SELECT  BookID, BookName
                        ROW_NUMBER() OVER (ORDER BY " + sidx + @" " + sord + @") AS RowNumber
                        FROM BOOKS
                   }";
            DataTable dt = new DataTable();
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=.;Initial Catalog=library;Integrated Security=True"].ConnectionString);
            SqlDataAdapter adapter = new SqlDataAdapter(sql, sqlcon);
            var rows = adapter.Fill(dt);
             s_GridResult result = new s_GridResult();
             List<s_RowData> rowsadded = new List<s_RowData>();
            //
            //  here for each row in dt create new row object of s_RowData and add the row in a list like
            //  s_RowData newrow = new s_RowData();
            //  newrow.id = item.ID;
            //   newrow.cell = new string[fields.Length];  //total number of columns
            //  newrow.cell[0] = "Content for first column"; ..etc
            //  rowsadded.Add(newrow);
            //
            result.page = page;
            result.total = dt.rows.count ...
            result.record =  rowsadded.count
            return  result;


        }

Re: connect jqgrid to database

thanks for your consideration

but it still doesnt work.I want to create jqgrid but my problem is that I dont know how to connect my jqgrid to my database.

in my database I have a table(Books) which has 9 fields (BookId,BookName,MenuID,RegDate,Author,Publisher,Price,Description,Status)

I want to show all this fields in jqgrid.what should I do?

in all codes that I found the page that connect to database is written in php or mvc but I want to write in c# (webform).

would you please help me more

Re: connect jqgrid to database

Did u try and modified what i wrote , because that does cover what you asking for .. what you can do is , show me the updated code and i can try help to complete it .

Re: connect jqgrid to database

also i noticed your jquery code is wrong a bit , pager should be id and not jquery element also the url . try this

jQuery("#Grid1").jqGrid({
                 url: 'data.aspx/GetDataTable',
                 datatype: 'json',
                 mtype: 'GET',
                 colNames: ['BookID', 'BookName'],
                 colModel: [{ name: 'BookID', index: 'BookID', width: 200 },
                   { name: 'BookName', index: 'BookName', width: 300}],
                 pager: '#pager',
                 sortname: 'id',
                 viewrecoreds: true,
                 imgpath: 'Themes/images'
             }).navGrid(pager,{edit:true,add:true,del:true,refresh:true,search:true});

Re: connect jqgrid to database

public struct s_GridResult
    {
        public int page;
        public int total;
        public int record;
        public s_RowData[] rows;


    }
    public struct s_RowData
    {
        public int id;
        public string[] cell;
    }


    public s_GridResult GetDataTable(string sidx, string sord, int page, int pagesize)
    {
        int startindex = (page - 1) * pagesize;
        int endindex = page * pagesize;
        string sql = @" WITH PAGED_CUSTOMERS AS
                      {
                          SELECT BookID,BookName
                          ROW_NUMBER() OVER(ORDER BY " + sidx + @" " + sord + @") AS ROWNUMBER
                          FROM BOOKS}";
        DataTable dt = new DataTable();
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=.;Initial Catalog=library;Integrated Security=True"].ConnectionString);
        SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
        var rows = adapter.Fill(dt);
        s_GridResult result = new s_GridResult();
        List<s_RowData> rowsadded = new List<s_RowData>();
          

         //I dont know what should I write here


        result.page = page;
        result.total = dt.Rows.Count;
        result.record = rowsadded.Count;
        return result;
    }

Re: connect jqgrid to database

update getdatatable to this . This doesnt take care of paging right now , but we can go to that on next level.

[WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public s_GridResult GetDataTable(string sidx, string sord, int page, int pagesize)
        {
            int startindex = (page - 1) * pagesize;
            int endindex = page * pagesize;
            string sql = @" WITH PAGED_CUSTOMERS AS
                      {
                          SELECT BookID,BookName
                          ROW_NUMBER() OVER(ORDER BY " + sidx + @" " + sord + @") AS ROWNUMBER
                          FROM BOOKS}";
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=.;Initial Catalog=library;Integrated Security=True"].ConnectionString);
            SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
            var rows = adapter.Fill(dt);
            s_GridResult result = new s_GridResult();
            List<s_RowData> rowsadded = new List<s_RowData>();
            int idx = 1;
            foreach (DataRow row in dt.Rows)
            {
                  s_RowData newrow = new s_RowData();
                  newrow.id = idx++;
                  newrow.cell = new string[2];  //total number of columns
                  newrow.cell[0] = row[0].ToString();
                  newrow.cell[1] = row[1].ToString();
                  rowsadded.Add(newrow);
            }
            result.rows = rowsadded.ToArray();
            result.page = page;
            result.total = dt.Rows.Count;
            result.record = rowsadded.Count;
            return result;
        }

Re: connect jqgrid to database

I write the exact code you told me but it still doesnt work. grid is created but my data does'nt load in it and it`s empty.

would you please tell me what to do?

Re: connect jqgrid to database

can u open in firefox and see what is the response result ?

use https://addons.mozilla.org/en-US/firefox/addon/1843/ for checking out what gets posted and what is the response

Re: connect jqgrid to database

Ok i understand your problem now :) .... loads of stuff to do make jqgrid to work with webmethod

firstly download JSON plugin from this link

http://code.google.com/p/jquery-json/downloads/detail?name=jquery.json-2.2.min.js

then your jquery code should look like this

// change these references as per your own solution
 <script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script>
    <script src="Scripts/jquery.json-2.2.min.js" type="text/javascript"></script>
    <script src="Scripts/jquery-ui-1.8.5.custom.min.js" type="text/javascript"></script>
    <script src="src/i18n/grid.locale-en.js" type="text/javascript"></script>
    <script src="src/jquery.jqGrid.min.js" type="text/javascript"></script>


  <script>


$(document.ready(){
// Update default settings
//required to make it work for JSON data handling
// this just overrides ajax settings and datatype
$.extend($.jgrid.defaults,
         {datatype: 'json'},
         {ajaxGridOptions: { contentType: "application/json" ,
           success: function(data, textStatus) {
            if (textStatus == "success") {
                var thegrid = $("#Grid1")[0];
                thegrid.addJSONData(data.d);
                thegrid.grid.hDiv.loading = false;
                switch (thegrid.p.loadui) {
                    case "disable":
                        break;
                    case "enable":
                        $("#load_" + thegrid.p.id).hide();
                        break;
                    case "block":
                        $("#lui_" + thegrid.p.id).hide();
                        $("#load_" + thegrid.p.id).hide();
                        break;
                }
            }
        }
         }
                 

    });
          jQuery("#Grid1").jqGrid({
              url: 'WebForm3.aspx/GetDataTable',
               dataType: "json",
              mtype: 'POST',
              colNames: ['BookID', 'BookName'],
              colModel: [{ name: 'BookID', index: 'BookID', width: 200 },
                   { name: 'BookName', index: 'BookName', width: 300}],
              pager: '#pager',
              sortname: 'id',
              viewrecoreds: true,
              imgpath: 'Themes/images',
              serializeGridData: function(data)
               {
                return  $.toJSON(data);   /// done to override default serialization
                }
                        

          }).navGrid("#pager", { edit: true, add: true, del: true, refresh: true, search: true });


      });
  </script>

Now update the webmethod definition to this

[WebMethod]        
        public static s_GridResult GetDataTable(string _search ,string nd ,int rows ,int page ,string  sidx , string sord )
        {  

 int startindex = (page - 1) * pagesize;
            int endindex = page * pagesize;
            string sql = @" WITH PAGED_CUSTOMERS AS
                      {
                          SELECT BookID,BookName
                          ROW_NUMBER() OVER(ORDER BY " + sidx + @" " + sord + @") AS ROWNUMBER
                          FROM BOOKS}";
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=.;Initial Catalog=library;Integrated Security=True"].ConnectionString);
            SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
            var rows = adapter.Fill(dt);
            s_GridResult result = new s_GridResult();
            List<s_RowData> rowsadded = new List<s_RowData>();
            int idx = 1;
            foreach (DataRow row in dt.Rows)
            {                  
                  s_RowData newrow = new s_RowData();
                  newrow.id = idx++;
                  newrow.cell = new string[2];  //total number of columns  
                  newrow.cell[0] = row[0].ToString();
                  newrow.cell[1] = row[1].ToString();
                  rowsadded.Add(newrow);                    
            }
            result.rows = rowsadded.ToArray();
            result.page = page;
            result.total = dt.Rows.Count;
            result.record = rowsadded.Count;
            return result;
}

Hope this works for u now :) let me know if you get stuck up on something

Re: connect jqgrid to database

But do understand this is just a quick fix , to give you some idea how to do it . But if you want a long term and permanent solution ,

then you should customize grid.base.js .

Re: connect jqgrid to database

I checked it with firebug and it seems that data.aspx/GetDataTable in html page it is not invoked.

what should i do?

Re: connect jqgrid to database

did u try the updated code i posted?

Re: connect jqgrid to database

no I cant download this jquery.json from above link ,would you do me a favor and send this file to my email adress please?

my email address is bahareh992sh@yahoo.com .I would appreciate it.thank you

Re: connect jqgrid to database

done , let me know if you still get probs with this

Re: connect jqgrid to database

your email was empty! Did you send jquery.json.min.js?

Re: connect jqgrid to database

done again

Re: connect jqgrid to database

thanks alot for your email

when I run my grid I faced with this error:

Microsoft JScript runtime error: Object doesn't support this property or method

and I really dont know what to do now?!!

Re: connect jqgrid to database

check references for javascript files in your page , they may be not correct.

Re: connect jqgrid to database

when I run my grid I faced with this error :document.ready is not a function

this error is shown in Firebug and my code is exactly similar as your code.!