In this tutorial you will show how to create json webservice asp.net by return JSON format data from a Web Service and consume it from jQuery, the first step is connecting to the MySQL database.
<connectionStrings>
<add name="Books" connectionString="Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=books;uid=root;pwd=password;option=3;" providerName="System.Data.Odbc"/>
</connectionStrings>
Here you will show how an ODBC connection is used to query the MySQL database:
1: using System;
2: using System.Web;
3: using System.Collections;
4: using System.Web.Services;
5: using System.Web.Services.Protocols;
6: using System.Data;
7: using System.Data.Odbc;
8: using System.Web.Script.Serialization;
9: using System.Web.Script.Services;
10:
11: /// <summary>
12: /// Web services to query the book database. All methods return JSON data.
13: /// </summary>
14: [WebService(Description = "Web services to query the book database.", Namespace = "http://www.williamsportwebdeveloper.com/")]
15: [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
16: [ScriptService]
17: public class BookServices : System.Web.Services.WebService {
18:
19: public BookServices () {
20:
21: //Uncomment the following line if using designed components
22: //InitializeComponent();
23: }
24:
25: [WebMethod(Description = "Gets the books matching part of a title.")]
26: [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
27: public string GetBooksByTitle(string strTitle) {
28: OdbcConnection objConnection = new OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Books"].ConnectionString);
29: OdbcCommand objCommand = new OdbcCommand("SELECT * FROM reading WHERE Title LIKE \%" + strTitle + "% ORDER BY BookNum;", objConnection);
30: DataSet objDataSet = new DataSet();
31: OdbcDataAdapter objDataAdapter = new OdbcDataAdapter(objCommand);
32: objDataAdapter.Fill(objDataSet, "reading");
33: objConnection.Close();
34:
35: // Create a multidimensional jagged array
36: string[][] JaggedArray = new string[objDataSet.Tables[0].Rows.Count][];
37: int i = 0;
38: foreach (DataRow rs in objDataSet.Tables[0].Rows)
39: {
40: JaggedArray[i] = new string[] { rs["BookNum"].ToString(), rs["Title"].ToString(), rs["Author"].ToString() };
41: i = i + 1;
42: }
43:
44: // Return JSON data
45: JavaScriptSerializer js = new JavaScriptSerializer();
46: string strJSON = js.Serialize(JaggedArray);
47: return strJSON;
48: }
49:
50: [WebMethod(Description = "Gets the books matching part of an author is name.")]
51: [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
52: public string GetBooksByAuthor(string strAuthor)
53: {
54: OdbcConnection objConnection = new OdbcConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Books"].ConnectionString);
55: OdbcCommand objCommand = new OdbcCommand("SELECT * FROM reading WHERE Author LIKE \%" + strAuthor + "% ORDER BY BookNum;", objConnection);
56: DataSet objDataSet = new DataSet();
57: OdbcDataAdapter objDataAdapter = new OdbcDataAdapter(objCommand);
58: objDataAdapter.Fill(objDataSet, "reading");
59: objConnection.Close();
60:
61: // Create a multidimensional jagged array
62: string[][] JaggedArray = new string[objDataSet.Tables[0].Rows.Count][];
63: int i = 0;
64: foreach (DataRow rs in objDataSet.Tables[0].Rows)
65: {
66: JaggedArray[i] = new string[] { rs["BookNum"].ToString(), rs["Title"].ToString(), rs["Author"].ToString() };
67: i = i + 1;
68: }
69:
70: // Return JSON data
71: JavaScriptSerializer js = new JavaScriptSerializer();
72: string strJSON = js.Serialize(JaggedArray);
73: return strJSON;
74: }
75:
76: }
This JavaScript is binding function prototypes to the button click events:
$(document).ready(function() {
2: $("#btnTitleQuery").bind("click", function() {
3: $("#query_results").empty();
4: $("#query_results").append(<table id="ResultsTable" class="BooksTable"><tr><th>BookNum</th><th>Title</th><th>Author</th></tr>);
5: $.ajax({
6: type: "POST",
7: contentType: "application/json; charset=utf-8",
8: url: "BookServices.asmx/GetBooksByTitle",
9: data: { strTitle: " + $("#txtTitle").val() + " },
10: dataType: "json",
11: success: function(msg) {
12: var c = eval(msg.d);
13: for (var i in c) {
14: $("#ResultsTable tr:last").after("<tr><td>" + c[i][0] + "</td><td>" + c[i][1] + "</td><td>" + c[i][2] + "</td></tr>");
15: }
16: }
17: });
18: })
19: $("#btnAuthorQuery").bind("click", function() {
20: $("#query_results").empty();
21: $("#query_results").append(<table id="ResultsTable" class="BooksTable"><tr><th>BookNum</th><th>Title</th><th>Author</th></tr>);
22: $.ajax({
23: type: "POST",
24: contentType: "application/json; charset=utf-8",
25: url: "BookServices.asmx/GetBooksByAuthor",
26: data: { strAuthor: " + $("#txtAuthor").val() + " },
27: dataType: "json",
28: success: function(msg) {
29: var c = eval(msg.d);
30: for (var i in c) {
31: $("#ResultsTable tr:last").after("<tr><td>" + c[i][0] + "</td><td>" + c[i][1] + "</td><td>" + c[i][2] + "</td></tr>");
32: }
33: }
34: });
35: })
36: });
Original Source:
Related Stuff
-
MooV: Using cutting edge Video phones and Software Video Phones - coupling all that with VoIP and empowering the disabled.
-
Moo Telecom: VoIP communications made easy - Ring anyway with the fun and ease of using a normal phone
-
TagR:Mobile Social Network with Real Time Locations Based services, and Ambience Intelligence, VoiP, IM, Skype, Googletalk, Mapping, Flickr, Events, Calendaring, Scheduling, SecondLife Support
-
ClearSMS : ClearSMS is a Web-based application that lets you send bulk SMS messages to your customers, contacts, or just about anyone.
-
Jajah:jah is a VoIP (Voice over IP) provider, founded by Austrians Roman Scharf and Daniel Mattes in 2005[1]. The Jajah headquarters are located in Mountain View, CA, USA, and Luxembourg. Jajah maintains a development centre in Israel.
-
Skype: It’s free to download and free to call other people on Skype. Skype the number one voice over ip software
- PrivatePhone: a free local phone number with voicemail and messages you can check online or from any phone.