how to data retrieving from SQL using AJAX

Posted by on December 29, 2009 Leave a comment (0) Go to comments

Introduction:

In this article we are going to discuss about retrieving data’s from sql using ajax callback method’s.

Main:

Ajax Callback methods provided a much more visibility and efficiency for retrieving data’s in sql.Please check the below sample code,

<!--Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default"-->
 
<script type="text/javascript"><!--mce:0--></script>
 
<form id="form1" enctype="application/x-www-form-urlencoded">
<div>
 
<input id="Button1" onclick="GetProduct()" type="button" value="Get Product Details" />
<table id="DetailsView1" style="width: 400px; border-collapse: collapse; height: 50px; background-color: white; border: #3366cc 1px;" border="1" cellspacing="0" cellpadding="4" rules="all">
<tbody>
<tr style="color:#003399;background-color:White;">
<td>ProductID</td>
<td> </td>
</tr>
<tr style="color:#003399;background-color:White;">
<td>PcompanyName</td>
<td> </td>
</tr>
<tr style="color:#003399;background-color:White;">
<td>PcontactName</td>
<td> </td>
</tr>
<tr style="color:#003399;background-color:White;">
<td>PcontactTitle</td>
<td> </td>
</tr>
<tr style="color:#003399;background-color:White;">
<td>Address</td>
<td> </td>
</tr>
<tr style="color:#003399;background-color:White;">
<td>City</td>
<td> </td>
</tr>
<tr style="color:#003399;background-color:White;">
<td>Region</td>
<td> </td>
</tr>
<tr style="color:#003399;background-color:White;">
<td>PostalCode</td>
<td> </td>
</tr>
<tr style="color:#003399;background-color:White;">
<td>Country</td>
<td> </td>
</tr>
<tr style="color:#003399;background-color:White;">
<td>Phone</td>
<td> </td>
</tr>
<tr style="color:#003399;background-color:White;">
<td>Fax</td>
<td> </td>
</tr>
</tbody></table>
</div>
</form>

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Implements System.Web.UI.ICallbackEventHandler
Dim _callbackResult As String = Nothing
Public Function GetCallbackResult() As String _
Implements System.Web.UI.ICallbackEventHandler.GetCallbackResult
Return _callbackResult
End Function
 
Public Sub RaiseCallbackEvent(ByVal eventArgument As String) _
Implements System.Web.UI.ICallbackEventHandler.RaiseCallbackEvent
 
Dim conn As SqlConnection = New _
SqlConnection("Data Source=.;Initial Catalog=Northwind;User ID=sa")
Dim cmd As SqlCommand = New _
SqlCommand("Select * From Products Where ProductID = ’" &amp; _
eventArgument &amp; "’", conn)
 
conn.Open()
Dim MyReader As SqlDataReader
MyReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Dim MyValues(10) As String
While MyReader.Read()
MyValues(0) = MyReader("ProductID").ToString()
MyValues(1) = MyReader("PcompanyName").ToString()
MyValues(2) = MyReader("PcontactName").ToString()
MyValues(3) = MyReader("PcontactTitle").ToString()
MyValues(4) = MyReader("Address").ToString()
MyValues(5) = MyReader("City").ToString()
MyValues(6) = MyReader("Region").ToString()
MyValues(7) = MyReader("PostalCode").ToString()
MyValues(8) = MyReader("Country").ToString()
MyValues(9) = MyReader("Phone").ToString()
MyValues(10) = MyReader("Fax").ToString()
End While
Conn.Close()
_callbackResult = String.Join("|", MyValues)
End Sub
 
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim cbReference As String = _
Page.ClientScript.GetCallbackEventReference(Me, "arg", _
"GetPrdDetailsFromServer", "context")
Dim cbScript As String = "function UseCallback(arg, context)" &amp; _
"{" &amp; cbReference &amp; ";" &amp; "}"
Page.ClientScript.RegisterClientScriptBlock(Me.GetType(), _
"UseCallback", cbScript, True)
End Sub
End Class