Call cross domain WebService(asmx) from Jquery with Jqgrid

Hi Friends,

Today I am going to demonstrate how to call cross domain Webserver(asmx) service from jquery and bind the Json result with Jqgrid.

Please download Jqgrid library from here: JQueryFiles

Take an example of a webservice that return three columns named "ProductID", "ProductName", "Price" in Json format. Below is the sample code for the same.
 
[WebMethod]

        [ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true)]
        public void GetProductList()
        {
            string result = string.Empty;
            var products = new List<Product>();
            //DataSet ds = new DataSet();
            try
            {
                for (int i = 0; i < 50; i++)
                {
                    var product = new Product();
                    product.ProductID = i;
                    product.ProductName = "Product " + i;
                    product.Price = i * 20;
 
                    products.Add(product);
                }
 
                result = new JavaScriptSerializer().Serialize(products);
 
                // Prepare
                string callback = HttpContext.Current.Request.Params["callback"];
                string json = "{d: " + result + "}";
                string response = string.IsNullOrEmpty(callback) ? json : string.Format("{0}({1});", callback, json);
 
                // Response
                HttpContext.Current.Response.ContentType = "application/json";
                HttpContext.Current.Response.Write(response);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

Put the following tag in your webservice web.config under <system.web> tag

 <webServices>
      <protocols>
        <add name="HttpGet"/>
      </protocols>
    </webServices>

Assume webservice is hosted on : http://localhost:1235/ProductWebService.asmx

Now put the below listed jquery and css files on your master page inside the head tag.

 <script src="/JQueryFiles/js/jquery-1.7.2.min.js" type="text/javascript"></script>
    <link href="/JQueryFiles/css/ui.jqgrid.css" rel="stylesheet" type="text/css" />
    <link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.2/themes/redmond/jquery-ui.css" />
    <link rel="stylesheet" type="text/css" href="~/JQueryFiles/jquery.jqGrid-3.7.2/css/ui.jqgrid.css" />
    <script src="/JQueryFiles/js/grid.locale-en.js" type="text/javascript"></script>
    <script src="/JQueryFiles/js/jquery.jqGrid.min.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(function () {
            $.jgrid.no_legacy_api = true;
            $.jgrid.useJSON = true;
        });
    </script>

Now the following code is placed on your web page

<script type="text/javascript">
        var lastsel = 0;
        var gridData;
        var selectedRowIndex = 0;
        $(function () {
            BindGrid();
        });
 
        function BindGrid() {
 
            // jQuery functions go here...
            $("#jqGrid").jqGrid({
                url: 'http://localhost:1235/ProductWebService.asmx/GetProductList?callback=?',
                datatype: "jsonp",
                mtype: 'GET',
                ajaxGridOptions: { contentType: 'application/json; charset=utf-8' }, //Specified content type of output.
                serializeGridData: function (postData) {
 
                    try {
                        return JSON.stringify(postData);
                    } catch (e) {
 
                    }
                },
                jsonReader: { repeatitems: false, root: "d" },
                colNames: ['ID'
                                            , 'Name'
                                            , 'Price'
 
                                        ],
 
                colModel: [{ name: 'ProductID', index: 'ProductID', sorttype: "int" }
                                            , { name: 'ProductName', index: 'ProductName', sorttype: "string" }
                                            , { name: 'Price', index: 'Price', sorttype: 'decimal;' }
 
 
                                        ],
                rowNum: 20,
                rowList: [10, 20, 30],
                autowidth: true,
                pager: '#jqGridPager',
                sortname: 'id',
                viewrecords: true,
                sortorder: "desc",
                loadonce: true,
                height: 230,
                caption: "Product List",
                ignoreCase: true,
 
                gridComplete: function () {
                    gridData = $("#jqGrid").jqGrid('getRowData');
                    if (selectedRowIndex > 0) {
                        jQuery("#jqGrid").setSelection(selectedRowIndex, true);
                    }
 
                    //Set default values in Key hidded field and CommandName hidden field.
                    document.getElementById('<%= hfRowKey.ClientID %>').value = "";
                    document.getElementById('<%= hfCommandName.ClientID %>').value = "";
                },
                onSelectRow: function (id) {
                    selectedRowIndex = id;
 
                }
 
            }).jqGrid('navGrid', '#jqGridPager', { edit: false, add: false, del: false, search: true })
                            .navButtonAdd('#jqGridPager',
                            { caption: "Edit", onClickButton: function (id) {
                                var RowKey = 0;
                                if (selectedRowIndex > 0) {
 
                                    RowKey = $('#jqGrid').getCell(selectedRowIndex, 'ID');
                                    if (RowKey > 0) {
                                        document.getElementById('<%= hfRowKey.ClientID %>').value = "" + RowKey;
                                        document.getElementById('<%= hfCommandName.ClientID %>').value = "edit";
 
                                    }
 
                                    $("#" + '<%= btnAddNew.ClientID %>').click();
                                }
                                else {
                                    alert("Please select row for editing.");
                                }
 
 
                            }, position: "last"
                            })
                            .navButtonAdd('#jqGridPager', { caption: "Delete", buttonimg: "../Images/delete-icon.png",
                                onClickButton: function (id) {
                                    var RowKey = 0;
                                    if (selectedRowIndex > 0) {
                                        var r = confirm("Are you sure, you want to delete this record : " + $('#jqGrid').getCell(selectedRowIndex, 'Name') + " ?");
                                        if (r == true) {
                                            RowKey = $('#jqGrid').getCell(selectedRowIndex, 'ID');
                                            if (RowKey > 0) {
                                                document.getElementById('<%= hfRowKey.ClientID %>').value = "" + RowKey;
                                                document.getElementById('<%= hfCommandName.ClientID %>').value = "delete";
                                            }
                                            $("#" + '<%= btnAddNew.ClientID %>').click();
                                        }
                                    }
                                    else {
                                        alert("Please select row to delete.");
                                    }
 
                                }, position: "last"
                            });
        }
    </script>

 <asp:HiddenField ID="hfRowKey" runat="server" Value="" />
    <asp:HiddenField ID="hfCommandName" runat="server" Value="" />
    <asp:Button ID="btnAddNew" runat="server" Text="Add New" OnClientClick="javascript:return false;"
        Style="display: none;" CommandArgument="testarg" />
    <table id="jqGrid" style="width: 100%;">
    </table>
    <div id="jqGridPager">
    </div>


Result: 

Search any string on from all the table in sql server 2008

CREATE TABLE #Results (
 ColumnName NVARCHAR(370)
 ,ColumnValue NVARCHAR(3630)
 )

SET NOCOUNT ON

DECLARE @SearchStr NVARCHAR(100)

SET @SearchStr = 'I plan to make changes to my practice based on this activity'

DECLARE @TableName NVARCHAR(256)
 ,@ColumnName NVARCHAR(128)
 ,@SearchStr2 NVARCHAR(110)

SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

WHILE @TableName IS NOT NULL
BEGIN
 SET @ColumnName = ''
 SET @TableName = (
   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
   )

 WHILE (@TableName IS NOT NULL)
  AND (@ColumnName IS NOT NULL)
 BEGIN
  SET @ColumnName = (
    SELECT MIN(QUOTENAME(COLUMN_NAME))
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
     AND TABLE_NAME = PARSENAME(@TableName, 1)
     AND DATA_TYPE IN (
      'char'
      ,'varchar'
      ,'nchar'
      ,'nvarchar'
      ,'int'
      ,'decimal'
      )
     AND QUOTENAME(COLUMN_NAME) > @ColumnName
    )

  IF @ColumnName IS NOT NULL
  BEGIN
   INSERT INTO #Results
   EXEC (
     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
     )
  END
 END
END

SELECT ColumnName
 ,ColumnValue
FROM #Results

DROP TABLE #Results