Insert/Read record(s) to/from Database Through jquery in asp.net by calling webservice (.asmx) methodWebservice method can be called via jquery $.ajax({}) if the underlying webservice is marked with Attribute
[System.Web.Script.Services.ScriptService]
and the underlying method should be marked as
[WebMethod]
[System.Web.Script.Services.ScriptMethod(ResponseFormat = ResponseFormat.Json)]
In this article we will learn how to call webservice method from asp.net application to insert data to database i will consider a database named "test" and the table name is "Employee(EmployeeID, Name, NationalIDNo, PhoneNo)". I want to pass the Employee as an object. So we will require to code a separate class from employee. Add the Employee.cs to your project. this class should include all the columns in the employee table with datatypes. This class should look like this.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 | using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
/// <summary>
/// Summary description for Employee
/// </summary>
public class Employee
{
public Employee()
{
//
// TODO: Add constructor logic here
//
}
public int EmployeeID { get; set; }
public string Name { get; set; }
public string NationalIDNo { get; set; }
public string PhoneNo { get; set; }
} |
and also we require to get the employee records from the database. for this in this article i am using the class GlobalData.cs (you can use any logic to retrieve data from the database. this class looks like this. but before this class you should add connection string to web.config
1
2
3
4
5 | <connectionstrings>
<add connectionstring="Data Source=NORTH\SQLSERVER2008;Initial Catalog=test;Integrated Security=SSPI" name="ConString2" providername="System.Data.SqlClient">
</add>
</connectionstrings>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51 | using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
/// <summary>
/// Summary description for GlobalData
/// </summary>
public class GlobalData
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConString2"].ConnectionString);
public GlobalData()
{
//
// TODO: Add constructor logic here
//
}
public DataTable SelectRecords(string query)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.Connection = con;
cmd.CommandTimeout = 0;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
public string GetMaxID(string tblName, string colName)
{
SqlDataAdapter da = new SqlDataAdapter("select max(" + colName + ") from " + tblName, con);
string toReturn = "";
con.Open();
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
toReturn = dt.Rows[0][0].ToString();
con.Close();
return toReturn;
}
public void ExecuteQuery(string query)
{
SqlCommand com = new SqlCommand(query, con);
con.Open();
com.ExecuteNonQuery();
con.Close();
}
}
|
Next we require to add webservice to our web application right. To Add webservice to your application in solution explorer right click on your project and select add new item. then Select [Web Service] with (.asmx) extension. this web service should look like this. web service name should be (InvoiceService.asmx) in this case.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43 | using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Script.Services;
using System.Web.Services;
/// <summary>
/// Summary description for EmployeeService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// 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 EmployeeService : System.Web.Services.WebService
{
public EmployeeService() {
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
[System.Web.Script.Services.ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public bool InsertEmployee(Employee emp)
{
GlobalData db = new GlobalData();
string EmployeeID = db.GetMaxID("Employee", "EmployeeID");
emp.EmployeeID = int.Parse(EmployeeID);
string query = "Insert into Employee values(N'" + emp.Name + "','" + emp.NationalIDNo + "','" + emp.PhoneNo + "')";
db.ExecuteQuery(query);
return true;
}
[WebMethod]
[System.Web.Script.Services.ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public IList<Employee> GetEmployees()
{
GlobalData db = new GlobalData();
IList<Employee> lstToReturn = db.SelectRecords("Select * from Employee").ToList<Employee>();
return lstToReturn;
}
}
|
For this line of Code.
IList lstToReturn = db.SelectRecords("Select * from Employee").ToList();
To convert DataTable to list we require to add extension method to DataTable class. To Add extension methods right click on your project and select add Class named (Extension) should look like this.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49 | using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using System.Web;
public static class Extensions
{
public static IList<T> ToList<T>(this DataTable table) where T : new()
{
var props = typeof(T).GetProperties().ToList();
var result = new List<T>();
Parallel.ForEach(table.AsEnumerable(), row =>
result.Add(DataRowToObject<T>(row, props)));
return result;
}
private static T DataRowToObject<T>(DataRow row, IList<PropertyInfo> props) where T : new()
{
T item = new T();
foreach (var prop in props)
{
if (row.Table.Columns.Contains(prop.Name))
{
Type proptype = prop.PropertyType;
var targetType = IsNullableType(prop.PropertyType) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType;
var propertyVal = row[prop.Name];
try
{
propertyVal = Convert.ChangeType(propertyVal, targetType);
prop.SetValue(item, propertyVal, null);
}
catch(InvalidCastException ae)
{
propertyVal = 0;
}
}
}
return item;
}
private static bool IsNullableType(Type type)
{
return type.IsGenericType && type.GetGenericTypeDefinition().Equals(typeof(Nullable<>));
}
public static bool HasProperty(this object obj, string propertyName)
{
return obj.GetType().GetProperty(propertyName) != null;
}
}
|
Note. your master page. should include reference to jquery.js & bootstrap.js & bootstrap.css
Finally your page.aspx should look like this. html & javascript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100 | <%@ Page Title="" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="frmEmployee.aspx.cs" Inherits="frmEmployee" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="Server">
<h3>Employee</h3>
<hr />
<div class="row">
<div class="form-horizontal">
<div class="form-group">
<label class="col-md-2">Employee Name</label>
<div class="col-md-4">
<input type="text" id="txtEmployeeName" name="txtEmployeeName" class="form-control" />
</div>
<label class="col-md-2">National ID #</label>
<div class="col-md-4">
<input type="text" id="txtNationalID" name="txtNationalID" class="form-control" />
</div>
</div>
</div>
<div class="form-group">
<label class="col-md-2">Phone No</label>
<div class="col-md-4">
<input type="text" id="txtphNo" name="txtphNo" class="form-control" />
</div>
<div class="col-md-2">
<a class="btn btn-success" id="btnSave" href="#" onclick="AddEmployee()"><i class="glyphicon glyphicon-save"></i> Save</a>
</div>
</div>
</div>
<br />
<div class="row">
<div id="msg">
</div>
</div>
<div class="row">
<div id="emp">
</div>
</div>
<script>
$(function () {
GetEmployees();
});
function GetEmployees() {
$.ajax({
type: "POST",
url: "EmployeeService.asmx/GetEmployees",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (data) {
console.log(data);
var tbl = "<table class='table table-bordered'><thead><th>EmployeeID</th><th>Name</th><th>National ID</th><th>Phone No</th></thead><tbody>";
$.each(data.d, function (i, x) {
tbl += "<tr>";
tbl += "<td>" + x.EmployeeID + "</td>";
tbl += "<td>" + x.Name + "</td>";
tbl += "<td>" + x.NationalIDNo + "</td>";
tbl += "<td>" + x.PhoneNo + "</td>";
tbl += "</tr>";
});
tbl += "</tbody></table>";
$('#emp').html(tbl);
},
failure: function (response) {
alert(response.d);
}
});
}
function AddEmployee() {
debugger;
var empid = 0;
var name = $('#txtEmployeeName').val();
var NID = $('#txtNationalID').val();
var phNo = $('#txtphNo').val();
var emp = { EmployeeID: empid, Name: name, NationalIDNo: NID, PhoneNo: phNo };
$.ajax({
type: "POST",
url: "EmployeeService.asmx/InsertEmployee",
data: JSON.stringify({ 'emp': emp }),
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (data) {
if (data) {
$('#msg').html('<div class="alert alert-success">Data Saved Successfully</div>');
GetEmployees();
$('#msg').fadeOut(3000);
}
},
failure: function (response) {
alert(response.d);
}
});
}
</script>
</asp:Content>
|
0 Comments