Infinite Scrolling In ASP.NET With jQuery

28. April 2012 12:53

ASP.NET Jquery T-SQL 

I didn't create the whole solution by myself. The initial idea is from the All-In-One Code Framework sample. I was just customizing my BlogEngine and for one of the module I thought it would be nice not to use paging and I should go for infinite scrolling stuff. This idea seems cool to me and without wasting any time I just do a quick web search and I came across a solution which is a part of a All-In-One Code Framework. This was not the complete solution I was looking for but I can re-use the jQuery part in the sample.

Assembling jQuery Part

I just re-use the all jQuery script as it is without any major modification. The only change I made is the name of the web method in the url parameter of the Ajax method I have in page code behind and change the method name to InfiniteScroll:

function InfiniteScroll() {
    $('#divPostsLoader').html('<img src="images/loader.gif">');

    //send a query to server side to present new content
    $.ajax({
        type: "POST",
        url: "Default.aspx/GetData",
        data: "{}",
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        success: function (data) {
            if (data != "") {
                $('.divLoadData:last').after(data.d);
            }
            $('#divPostsLoader').empty();
        }
    })
};

I already have 2 divs in the page where the data is being loaded when the user scrolls down at the bottom of the page. This is the simplest Ajax call written in jQuery and I assume you are aware of the syntax and methods and require no explanation. What I require here is to call this function when the user scrolls at the bottom of the page. So how to know when the user scrolls and reach at the bottom of the page? Here is the method which fires the InfiniteScroll() function:

$(window).scroll(function () {
    if ($(window).scrollTop() == $(document).height() - $(window).height()) {
        InfiniteScroll();
    }
});

Make sure you put the scroll function inside the document.ready function. Now when the user scrolls at the bottom of the page then it will call the InfiniteScroll() function which in turn loads new data in the divLoadData div.

The Code-Behind

In the code-behind create a WebMethod function that returns a HTML string. For this example I am loading all my blog posts title, post date and slug from my blog engine database which is a SQL Server CE 4.0. Here is the method:

[WebMethod]
public static string GetData()
{
    RecordCount = RecordCount + 10;
    string Sql = "SELECT Title, DateCreated, Slug FROM be_Posts ORDER BY Title OFFSET " + FirstCount + " ROWS FETCH NEXT 10 ROWS ONLY";
    FirstCount = RecordCount;
    StringBuilder sb = new StringBuilder();
    dt = new DataTable();
    da = new SqlCeDataAdapter(Sql, con);
    con.Open();
    da.Fill(dt);

    DataView dv = dt.DefaultView;

    foreach (DataRowView row in dv)
    {
        sb.AppendFormat("<p>Post Title" + " <strong>" + row["Title"] + "</strong>");
        sb.AppendFormat("<p>Post Date" + " <strong>" + row["DateCreated"] + "</strong>");
        sb.AppendFormat("<p>Slug" + " <strong>" + row["Slug"] + "</strong>");
        sb.AppendFormat("<hr/>");
    }

    sb.AppendFormat("<divstyle='height:15px;'></div>");
    con.Close();
    return sb.ToString();
}

Infinite scrolling is nothing but is a sort of automatic paging. Every time a user scrolls down at the bottom of the page, the query gets fired and gets the new set of data. As I am using SQL CE 4.0 the paging query is different than that of the SQL Server 2008.

Query for SQL CE 4.0:

SELECT Title, DateCreated, Slug FROM be_Posts ORDER BY Title OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Change the OFFSET and FETCH NEXT to get the next result set.

Query For SQL Server 2008:

SELECT Title, DateCreated, Slug FROM ( 
     SELECT 
          ROW_NUMBER() OVER (ORDER BY title) AS row, Title, DateCreated, Slug
     FROM be_Posts
) AS a WHERE row BETWEEN 1 AND 10

I have changed the query in the above code to get me the set of next results every time a user scrolls at the bottom of the page. When the user scrolls down I increment the FirstCount with 10 which is a value of RecordCount variable. I have attached the complete solution so you can try. I don't have the database included in the solution as it is my blog database. You have to create one to test it out.

Download: InfiniteScroll.zip (2.28 mb)

Currently rated 3.5 by 2 people

Using SQL CLR Stored Procedure To Track IP Address

24. April 2010 00:24

C# SQL Server T-SQL 

Recently I was asked by one of my friend how to call a web service directly from a stored procedure. He needs to track IP addresses using a stored procedures so, I suggested him two ways to call a web service using the SQL Procedure.

  • Using SP_OACREATE with MSXML
  • Create an assembly using SQL CLR using C#.

I personally tried using the first way to call a web service but was not successful. Then I learnt about SQL CLR programming with Visual Studio and C# and get it done in a first go. As a good friend I create the whole project for my friend. And if your thinking that this is going to be a difficult, this is not so.

To start creating a project, open Visual Studio and create a new project.

Before the project gets loaded completely, you will be asked to add database reference. Here select the database you want to build the assembly for. If you don’t want to add a database reference then just simply cancel. But if you choose the reference to add at this point then this would really be of help as this will allow you to deploy your assembly directly to the SQL Server database but if you have not then you need to write some SQL scripts.

NOTE: If you have added a database reference then it doesn’t mean that your assembly is build specifically for that database you can still deploy your assembly in other database also.

As I am creating a stored procedure, I then have to add a New Item to my project. Right-click project choose Add>Stored Procedure.  Here give a name of your choice to stored procedure. In my case it is IPInfo.

Change the static method here to accept a parameter. Our parameter will be an IP address, which I have set as of string type.

public static void GetIPInfo(string IP)
{  
//Do something here
}

Now the question here is from where we can the information of the IP Addresses? I do some search and found a website http://www.geobytes.com. It offers some rich information of the IP Address you want to track, so out of five sources I choose to go with geobytes.

Lets take a quick look at the code. This is not a web service as you can see from the code below, I am just making a http request and then get the response in an XML form so I can easily traverse the nodes and read/save the information I received.

string XMLResponse;
IPAddress = IP;
byte[] XMLResults;
string str = "http://www.geobytes.com/IpLocator.htm?GetLocation&template=xml.txt"
+ "&IpAddress=" + IP;
XMLResults = new WebClient().DownloadData(str.ToString());
XMLResponse = Encoding.UTF8.GetString(XMLResults);

Note the fourth line where I have built the URL. The parameter Template should always have a value to xml.txt. If you change the name, it won’t throw any error but the data received cannot be read and saved in a database table. Convert the response to string format and then read the nodes one by one using the below code.

try
        {
            XmlDocument document = new XmlDocument();
            document.LoadXml(XMLResponse);
            XmlNode documentElement = document.DocumentElement;
            if (documentElement.HasChildNodes)
            {
                for (int i = 0; i < documentElement.ChildNodes.Count; i++)
                {
                    if (documentElement.ChildNodes[i].NodeType == XmlNodeType.Element)
                    {
                        switch (documentElement.ChildNodes[i].Name)
                        {
                            case "locationcode":
                                LocationCode = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "fips":
                                FIPS104 = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "iso2":
                                ISO2 = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "iso3":
                                ISO3 = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "ison":
                                ISON = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "internet":
                                Internet = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "countryid":
                                CountryID = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "country":
                                Country = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "regionid":
                                RegionID = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "region":
                                Region = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "code":
                                RegionCode = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "adm":
                                Admn1Code = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "cityid":
                                CityID = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "city":
                                City = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "latitude":
                                Latitude = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "longitude":
                                Longitude = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "timezone":
                                TimeZone = documentElement.ChildNodes[i].InnerText;
                                break;

                            case "certainty":
                                Certainty = documentElement.ChildNodes[i].InnerText;
                                break;
                        }
                    }
                }
            }
        }
        catch (Exception) { }

I traversed through each node and save the response data in a variable I have already declared in my program using a switch case. Once all the response has been assigned to the variable save the response to the database.

try
        {
            using (SqlConnection con = new SqlConnection("context connection=true"))
            {
                string strSQL = "INSERT INTO IPTrack values ('" + IPAddress + "','" + LocationCode + "','" + FIPS104 + "','" + ISO2 + "','" + ISO3 + "','" + ISON + "','" + Internet + "','" + CountryID + "','" + Country +
                    "','" + RegionID + "','" + Region + "','" + RegionCode + "','" + Admn1Code + "','" + CityID + "','" + City + "','" + Latitude + "','" + Longitude + "','" + TimeZone + "','" + Certainty + "')";
                SqlCommand cmd = new SqlCommand(strSQL, con);
                //SqlContext.Pipe.ExecuteAndSend(cmd);
                con.Open();
                SqlContext.Pipe.ExecuteAndSend(cmd);
                //cmd.ExecuteNonQuery();
            }
        }
        catch (Exception) { }
    }

Build you project and you will see a DLL file in your debug/release folder. But this will not work. You will still have to set some permission level to assembly and to SQL Server database.

Set Assembly Permissions

Before you build the final version of the assembly go the project properties and set the permissions as shown below.

Under Build select option for Generate Serialization Assembly to On.

Under Database select Permission Level to Unsafe

SQL Server Permissions

If you find problems while deploying your assembly, then make sure you have CLR enabled and set the Trustworthy property for the database to ON.

You can find all the scripts in the attached solution below.

Deploying your assembly

To deploy your assembly use the below SQL command:

CREATE ASSEMBLY IPTracker from 'C:\IPInfo.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

To create a stored procedure whcih in trun calls the SQL method from the deployed assembly:

CREATE PROC TrackIP(@IP as nvarchar(50))AS 
-- [Assembly Name].[Class Name].[CLR function Name]
EXTERNAL NAME IPTracker.StoredProcedures.GetIPInfo 
GO

And execute the procedure to get the IP information

Download: IPInfo.zip (19.46 kb)

Currently rated 5.0 by 2 people

SQL Server performance and NOCOUNT

25. February 2010 20:44

SQL Server T-SQL 

Performance of SQL server matters while working with large enterprise applications and where performance really bothers the business workflow. When NOCOUNT is ON, the count of rows by the execution of the query (T-SQL statements) is not returned. You must have noticed that when you perform operations by executing INSERT, UPDATE, DELETE or SELECT statements, the server returns the number of rows. The count of rows is necessary while debugging your queries, when you are done with debugging you can turn NOCOUNT ON. To view the number of rows affected use the command SET NOCOUNT OFF. You can try writing a normal select query with NOCOUNT as OFF, when NOCOUNT is OFF you can see the number of rows affected, but when you set NOCOUNT ON, you will not see any message related to the count of rows which are affected when you execute select statement or T-SQL statement.

 No Rating

Using OpenXML to insert data in SQL Server

19. February 2010 22:44

SQL Server T-SQL 

Microsoft SQL Server provides the mechanism to save XML data to the SQL table using OpenXML. We can use a stored procedure and pass a XML text as a string. It is useful when we have a large data in XML format and we want to save the whole data in different tables.

So first start declaring the variables. The first declare the document variable and a XML document variable as Varchar (8000) or Varchar(MAX) - It's a same thing

DECLARE @idoc int
DECLARE @doc varchar(8000)

Now we can set the value to the @doc variable, the @doc variable will accept the XML file as a parameter. We can also have a complete XML data in the parameter only if the XML data is less.

OpenXML takes 3 parameters:

  • The handler which we have declare at the begining will holds the XML document in the memory.
  • XPath variable to access the various elements of the XML document.
  • The last parameter here '2' allows us to access XPath as elements.

Here I have read a XML file in the document variable and store in the table. Here is the full SQL query to achieve this. You can also convert the below code to stored procedure and pass XML file or string as a parameter

DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='<ROOT><student>
<id>1</id>
<name>Prashant</name>
<age>32</age>
</student>
<student>
<id>2</id>
<name>Swami</name>
<age>42</age>
</student>
<student>
<id>3</id>
<name>Ash</name>
<age>23</age>
</student>
<student>
<id>4</id>
<name>Kris</name>
<age>12</age>
</student>
<student>
<id>5</id>
<name>Derek</name>
<age>75</age>
</student>
</ROOT>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
Insert into Students SELECT id, name, age from 
OPENXML (@idoc, '/ROOT/student',2)
WITH (id  int, name varchar(50), age int)
Currently rated 4.0 by 4 people

Use Sql query to write/create a file

11. December 2009 14:33

SQL Server T-SQL 

This SQL stored procedure will allow you to write to the file on your file system. Note the file system will be the the same on which the SQL server is running. If you are using this with your local SQL server then it will write and create files on your local file system and if you are on the remote machine, the file system will be the remote file system.

Create Procedure  [dbo].[USP_SaveFile](@text as NVarchar(Max),@Filename Varchar(200)) 
AS 
Begin 
 
declare @Object int,
        @rc int, -- the return code from sp_OA procedures 
        @FileID Int 
 
EXEC @rc = sp_OACreate 'Scripting.FileSystemObject', @Object OUT 
EXEC @rc = sp_OAMethod  @Object , 'OpenTextFile' , @FileID OUT , @Filename , 2 , 1 
Set  @text = Replace(Replace(Replace(@text,'&','&'),'<' ,'<'),'>','>')
EXEC @rc = sp_OAMethod  @FileID , 'WriteLine' , Null , @text  
Exec @rc = master.dbo.sp_OADestroy @FileID   
 
Declare @Append  bit
Select  @Append = 0
 
If @rc <> 0
Begin
    Exec @rc = master.dbo.sp_OAMethod @Object, 'SaveFile',null,@text ,@Filename,@Append
       
End

Exec @rc = master.dbo.sp_OADestroy @Object 
    
End 

But before you start using this procedure you need to reconfigure some advanced SQL server settings. Use the below configuration query to enable 'OLE Automation Procedures'. If this is not enabled and you try executing the procedure you will get errors.

Use master
GO
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
--To enable Ole automation feature
EXEC sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

The first parameter will take the text to be written to the file and the second parameter will take the complete path of the file to be created with the text in it. You can also use the same procedure to wite binary files to the file system, you just need to check and change the file extension in the second parameter. Usage:

EXEC USP_SaveFile 'Microsoft SQL Server 2008', 'C:\MSSQL.txt' 
Currently rated 4.0 by 1 person

«12»