Binding jqxGrid to SQL Database using ASP.NET Web Service ( Responsive & Right-to-Left & Farsi )

Masoud Ehteshami (Iran)

Rate me:

0.00/5 (No votes)

12 May 2015CPOL

11.6K

main source : https://www.jqwidgets.com

Introduction

This article is going to show you how to implement a asp .net webservice and use it to bind jqxgrid with your Sql DataBase.

Background

how a jqxgrid can be responsive and right to left speccially for Persian Language Support in web forms.

زبان فارسی

Using the code

Create an Asp .Net Web forms project .

Add an Asp .Net WebService to your solution too.

WebService can return DataSet.

here is my asmx file :

C++

//

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace JQX_WS
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]

    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]

    public class Grid : System.Web.Services.WebService
    {
        public static DataSet GetData(SqlCommand cmd)
        {
            
           string  strConnString = @"You Conmnection String";
            using (SqlConnection con = new SqlConnection(strConnString))
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    adapter.SelectCommand = cmd;
                    using (DataSet ds = new DataSet())
                    {
                        adapter.Fill(ds);
                        return ds;
                    }
                }
            }
        }
    }
}

//

in aspx file u have to get the source data with ajax GET request from a method on your page.

and my web page aspx file is like this :

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="JQXDemos._Default" %>

<!DOCTYPE html>
<html lang="en">
<head>
    <title></title>
    <link href="Styles/jqx.base.css" rel="stylesheet" type="text/css" />
    <link href="Styles/jqx.classic.css" rel="stylesheet" type="text/css" />
    <script src="Scripts/jquery-1.11.1.min.js" type="text/javascript"></script>
    <script src="Scripts/jqxcore.js" type="text/javascript"></script>
    <script src="Scripts/jqxbuttons.js" type="text/javascript"></script>
    <script src="Scripts/jqxdata.js" type="text/javascript"></script>
    <script src="Scripts/jqxgrid.js" type="text/javascript"></script>
    <script src="Scripts/jqxgrid.selection.js" type="text/javascript"></script>
    <script src="Scripts/jqxmenu.js" type="text/javascript"></script>
    <script src="Scripts/jqxscrollbar.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            //Getting the source data with ajax GET request
            source = {
                datatype: "xml",
                datafields: [
                { name: 'username' },
                { name: 'name' },
                { name: 'tell' }               
                ],
                async: false,
                record: 'Table',
                url: 'Default.aspx/GetCustomers',
            };
            var dataAdapter = new $.jqx.dataAdapter(source,
                {  contentType: 'application/json; charset=utf-8'}
            );
            $("#jqxgrid").jqxGrid({
             width: '100%',                             
              
                source: dataAdapter,
                theme:'classic',
                   
                
                autoheight: true,
             
           columns: [
                    { text: 'نام کاربری', dataField: 'username',   align: 'right',  cellsalign: 'right'},
                    { text: 'نام', dataField: 'name',  align: 'right',  cellsalign: 'right'},
                    { text: 'تلفن', dataField: 'tell', align: 'right',  cellsalign: 'right' }                    
                ]
            });
        });
    </script>
</head>
<body class="default">
      <div id="jqxgrid" style="font-family:Tahoma">
       </div>
</body>
</html>


Remember to set the Language of your code snippet using the Language dropdown.

columns are in farsi . use :

autoheight: true,

to fit height .

align: 'right',  cellsalign: 'right'

for right-to-left layout of items.

then finally in your .CS code raise get dataset from your webService and convert it to XML :

here is CS code of Default.aspx.cs :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using System.Web.Script.Services;
using System.Data;
using System.Data.SqlClient;

// Binding jqxGrid to SQL Database using ASP.NET Web Service  ( Responsive & Right-to-Left & Farsi )

// By Masoud Ehteshami -------->

namespace JQXDemos
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
          
        }
        
        [WebMethod]
        [ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Xml)]
        public static string GetCustomers()
        {
            //your SQL query
            string query = "SELECT username,name , tell FROM operator";

            SqlCommand cmd = new SqlCommand(query);

            // fill the DataSet. I created a Web Service in a project named JQX_WS
            DataSet data = JQX_WS.Grid.GetData(cmd);

            // return the table as XML.
            System.IO.StringWriter writer = new System.IO.StringWriter();
            data.Tables[0].WriteXml(writer, XmlWriteMode.WriteSchema, false);

            return writer.ToString();
        }
       
    }
}

main source : jquerywidgets.com

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Masoud Ehteshami (Iran)

Software Developer (Senior)

Iran (Islamic Republic of)

This member doesn't quite have enough reputation to be able to display their biography and homepage.