1.
Create Database & Table
Sebelum membuat web service kita siapkan terlebih
dahulu database dan table di SQL Server yang akan di olah di web service.
Database name : DBWS
Table name : WSDATA
Table field :
|
Field
|
DataType
|
|
PersonalID
|
Int (PK)
|
|
PersonalName
|
Nvarchar(50)
|
|
Address
|
Nvarchar(100)
|
|
Gender
|
Nvarchar(20)
|
|
Hoby
|
Nvarchar(100)
|
Untuk proses insert, update, delete, select data di
SQL tambahkan stored procedure berikut :
·
Stored Procedure Insert
Untuk proses insert
bisa dilakukan dengan dua cara yaitu dengan bulk insert atau insert data satu
persatu. Bulk insert adalah melakukan insert data yang jumlahnya banyak tanpa
harus melakukan looping sedangkan jika melakukan insert data yang jumlahnya
banyak tanpa proses bulk insert akan memakan waktu lebih lama karena harus
melakukan looping sebanyak jumlah data yang akan di insert, dan yang akan
digunakan disini adalah bulk insert. untuk melakukan proses bulk insert di SQL
kita harus membuat dataType yang strukturnya sama dengan table tujuan insert
data, datatype ini berfungsi sebagai penampung data yang akan di insert ke
table. Dan berikut datatype yang untuk proses insert ke table WSDATA :
|
CREATE TYPE [dbo].[WSDATAype]
AS TABLE(
[PersonalID]
[int] NOT NULL,
[PersonalName]
[nvarchar](50)
NULL,
[Address]
[nvarchar](100)
NULL,
[Gender]
[nvarchar](20)
NULL,
[Hoby]
[nvarchar](100)
NULL
)
|
Setelah membuat
dataType tambahkan stored procedure dibawah ini :
|
CREATE PROCEDURE SP_INSERT_WSDATA
-- Add the parameters for the stored procedure here
@WsDataInsert WSDATAype READONLY
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO WSDATA(PersonalID,PersonalName,[Address],Gender,Hoby)
SELECT PersonalID,PersonalName,[Address],Gender,Hoby FROM
@WsDataInsert;
END
GO
|
·
Stored Procedure Update
|
CREATE PROCEDURE SP_UPDATE_WSDATA
-- Add the parameters for the stored procedure here
@PersonalID
int,
@PersonalName
Nvarchar(50),
@Address
Nvarchar(100),
@Gender Nvarchar(20),
@Hoby Nvarchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
UPDATE WSDATA SET
PersonalName = @PersonalName, [Address] =
@Address, Gender =
@Gender, Hoby =
@Hoby
WHERE PersonalID =
@PersonalID
END
GO
|
·
Stored Procedure Delete
|
CREATE PROCEDURE SP_DELETE_WSDATA
-- Add the parameters for the stored procedure here
@PersonalID
int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DELETE WSDATA WHERE
PersonalID = @PersonalID
END
GO
|
·
Stored Procedure Select
-> Select All Data
|
CREATE PROCEDURE SP_SELECT_ALL_WSDATA
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * FROM WSDATA
END
GO
|
-> Select Dengan Parameter
|
CREATE PROCEDURE SP_SELECT_WSDATA
-- Add the parameters for the stored procedure here
@PersonalID
int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * FROM WSDATA WHERE
PersonalID = @PersonalID
END
GO
|
Sampai disini persiapan dari SQL sudah selesai
selanjutnya di web service
1.
Create New Project
-> Buka Visual Studio pilih File->New->Project
->Masukkan Nama
Project dan pilih framework seperti pada gambar dibawah dan centang Application
insights.
Jika sudah pilih OK, maka akan langsung tercreate project seperti
berikut :
Sampai ditahap ini
create project webservice sudah selesai, untuk proses selanjutnya tinggal
membuang function atau class sesuai kebutuhan.
3.
Setting Web Config
Web config
fungsinya sebagai tempat referensi dari object-object yang akan digunakan dalam
aplikasi, seperti connection string, dan pengaturan lainnya bisa dilakukan di
web config.
Setting web config
seperti dibawah ini :
|
<?xml version="1.0"?>
<configuration>
<appSettings>
<add key="aspnet:UseTaskFriendlySynchronizationContext" value="true"/>
<add key="ConString" value="Data Source=IP Server;Initial Catalog=Nama
Database;Persist Security Info=True;User ID=UserDatabase;Password=PasswordDatabase;Pooling=false" />
</appSettings>
<system.web>
<compilation debug="true" targetFramework="4.5.2"/>
<httpRuntime targetFramework="4.5.2"/>
<httpModules>
<add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule,
Microsoft.AI.Web"/>
</httpModules>
</system.web>
<system.serviceModel>
<services>
<service name="WSUX.Service1">
<endpoint address="../Service1.svc" binding="webHttpBinding" contract="WSUX.IService1" behaviorConfiguration="webBehaviour" />
</service>
</services>
<behaviors>
<serviceBehaviors>
<behavior>
<!-- To avoid
disclosing metadata information, set the values below to false before
deployment -->
<serviceMetadata httpGetEnabled="true" httpsGetEnabled="true"/>
<!-- To
receive exception details in faults for debugging purposes, set the value
below to true. Set to false before
deployment to avoid disclosing exception information -->
<serviceDebug includeExceptionDetailInFaults="false"/>
</behavior>
</serviceBehaviors>
<endpointBehaviors>
<behavior name="webBehaviour">
<webHttp />
</behavior>
</endpointBehaviors>
</behaviors>
<protocolMapping>
<add binding="basicHttpsBinding" scheme="https"/>
</protocolMapping>
<serviceHostingEnvironment aspNetCompatibilityEnabled="true" multipleSiteBindingsEnabled="true" minFreeMemoryPercentageToActivateService="0"/>
</system.serviceModel>
<system.webServer>
<modules runAllManagedModulesForAllRequests="true">
<remove name="ApplicationInsightsWebTracking"/>
<add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule,
Microsoft.AI.Web"
preCondition="managedHandler"/>
</modules>
<!--
To browse web app root directory during debugging, set the value below
to true.
Set to false before deployment to avoid disclosing web app folder
information.
-->
<directoryBrowse enabled="true"/>
<validation validateIntegratedModeConfiguration="false"/>
</system.webServer>
</configuration>
|
4.
Menambah Class & Interface URL
-> Class disini
dibuat untuk menampung function untuk interaksi ke SQL dan Class sebagai Entity
untuk menampung result data dari SQL dan mengirim parameter ke SQL.
·
Class interaksi ke SQL
Class ini berisi
function untuk konek ke SQL dan menjalankan perintah SQL Query.
Untuk menambahkan
class baru didalam project klik kanan pada project solution ->add->New
Items->pilih Class dan setelah itu masukkan nama Class yang akan dibuat.
Jika sudah klik Add
dan akan terbentuk sebuah class baru dan tambahkan variable string seperti
berikut :
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace WSUX
{
Public static class SQLFunct
{
public static string sqlMessage;
}
}
|
Selanjutnya
tambahkan function yang dibutuhakan, Membaca koneksi string dari web config,
Set koneksi, Execute Query, Execute Reader, Execute Scalar
-> Function Membaca
Koneksi String dari web config
|
public static string
GetAppSetting(string key)
{
try
{
return ConfigurationManager.AppSettings[key];
}
catch
(Exception x)
{
return "";
}
}
|
-> Function Set
Koneksi
|
public static string
GetConnString()
{
string connString = "";
connString = GetAppSetting("ConString");
return connString;
}
|
-> Function
Execute Query
Function ini
diguanakan untuk menjalan Query SQL seperti Insert, Update dan Delete
|
public static string ExecuteNonQuery(SqlCommand command)
{
string connString = GetConnString();
string result = "";
SqlConnection con = new SqlConnection(connString);
if
(con.State == ConnectionState.Closed)
{
con.Open();
}
SqlTransaction transaction = con.BeginTransaction();
try
{
command.Transaction =
transaction;
command.CommandTimeout =
600000;
command.CommandType =
System.Data.CommandType.StoredProcedure;
command.Connection = con;
command.ExecuteNonQuery();
transaction.Commit();
transaction.Dispose();
con.Close();
con.Dispose();
sqlMessage = "1";
return sqlMessage;
}
catch
(Exception x)
{
if (x.Message.Contains("Violation
of PRIMARY KEY"))
{
sqlMessage = x.Message;
}
else
{
sqlMessage = "0";
}
transaction.Rollback();
transaction.Dispose();
con.Close();
con.Dispose();
return sqlMessage;
}
finally
{
con.Close();
}
}
|
-> Function
Execute Reader
Function ini
diguanakan untuk menjalan Query Select
|
public static DataTable
ExecuteReader(SqlCommand command)
{
string connString = GetConnString();
SqlConnection con = new SqlConnection(connString);
DataTable dtTable = new DataTable();
try
{
//string ConnString = GetAppSetting(connString);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
command.CommandTimeout =
600000;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Connection = con;
SqlDataReader reader;
reader =
command.ExecuteReader();
dtTable.Load(reader);
reader.Close();
con.Close();
con.Dispose();
return dtTable;
}
catch
(Exception x)
{
return dtTable;
}
finally
{
con.Close();
}
}
|
-> Function
Excute Scalar
Function ini
digunakan untuk menjalankan query select yang hanya mengembalikan nilai satu
field record data.
|
public static string
ExecuteScalar(SqlCommand command)
{
string connString = GetConnString();
SqlConnection con = new SqlConnection(connString);
try
{
//string ConnString =
GetAppSetting(connString);
string result = "";
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlTransaction transaction = con.BeginTransaction();
command.Transaction =
transaction;
command.CommandTimeout =
600000;
command.CommandType =
System.Data.CommandType.StoredProcedure;
command.Connection = con;
result =
command.ExecuteScalar().ToString();
transaction.Commit();
transaction.Dispose();
con.Close();
con.Dispose();
return result;
}
catch
(Exception x)
{
return "error " + x.Message;
}
finally
{
con.Close();
}
}
|
·
Class Entity
Class Entity ini
digunakan untuk menampung hasil result query dari SQL yang akan ditampilkan
dalam bentuk json dan untuk mengirimkan parameter ke SQL.
Buat class baru
dengan nama Ent_WSDATA
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace WSUX
{
public class Ent_WSDATA
{
public string
PersonalID { get; set; }
public string
PersonalName { get; set; }
public string Address {
get; set; }
public string Gender { get; set; }
public string Hoby { get; set; }
}
}
|
·
Membuat Interface URL
Tambahkan interface
berikut di IService1.cs untuk proses insert, update, delete, dan select data
|
[OperationContract]
[WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "INSERTDATA")]
string
INSERTDATA(Stream param);
[OperationContract]
[WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "UPDATEWSDATA")]
string UPDATEWSDATA(Stream param);
[OperationContract]
[WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "DELETEWSDATA")]
string
DELETEWSDATA(Stream param);
[OperationContract]
[WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "GETDATA")]
List<Ent_WSDATA> GETDATA(Stream param);
|
·
Implement Interface URL
Setelah membuat
interface di IService1.cs selanjutnya implment Interface tersebut di
Service1.svc seperti berikut :
-> Implement
INSERTDATA
|
public string INSERTDATA(Stream param)
{
try
{
StreamReader reader = new StreamReader(param);
string JSONdata = reader.ReadToEnd();
JavaScriptSerializer jss = new JavaScriptSerializer();
List<Ent_WSDATA> listWSDATA = jss.Deserialize<List<Ent_WSDATA>>(JSONdata);
DataTable dtWSDATA = SQLFunct.ToDataTable<Ent_WSDATA>(listWSDATA);
string connString = SQLFunct.GetConnString();
SqlConnection con = new SqlConnection(connString);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand command = new SqlCommand("SP_INSERT_WSDATA", con);
command.Parameters.Add(new SqlParameter("@WsDataInsert", SqlDbType.Structured));
command.Parameters["@WsDataInsert"].Value = dtWSDATA;
command.Parameters["@WsDataInsert"].TypeName = "WSDATAype";
string result = SQLFunct.ExecuteNonQuery(command);
return "1";
}
catch
(Exception x)
{
return x.Message;
}
}
|
-> Implement
UPDATEDATA
|
public string UPDATEWSDATA(Stream param)
{
try
{
StreamReader reader = new StreamReader(param);
string JSONdata = reader.ReadToEnd();
JavaScriptSerializer jss = new JavaScriptSerializer();
Ent_WSDATA ListUpdateWsData = jss.Deserialize<Ent_WSDATA>(JSONdata);
SqlCommand command = new SqlCommand("SP_UPDATE_WSDATA");
command.Parameters.Add(new SqlParameter("@PersonalID",
ListUpdateWsData.PersonalID));
command.Parameters.Add(new SqlParameter("@PersonalName", ListUpdateWsData.PersonalName));
command.Parameters.Add(new SqlParameter("@Address",
ListUpdateWsData.Address));
command.Parameters.Add(new SqlParameter("@Gender",
ListUpdateWsData.Gender));
command.Parameters.Add(new SqlParameter("@Hoby",
ListUpdateWsData.Hoby));
SQLFunct.ExecuteNonQuery(command);
string result;
if (SQLFunct.sqlMessage == "1")
{
result = "1";
}
else
{
result = SQLFunct.sqlMessage;
}
return result;
}
catch (Exception)
{
throw;
}
}
|
-> Implement
DELETEDATA
|
public string DELETEWSDATA(Stream param)
{
try
{
StreamReader reader = new StreamReader(param);
string JSONdata = reader.ReadToEnd();
JavaScriptSerializer jss = new JavaScriptSerializer();
Ent_WSDATA ListUpdateWsData = jss.Deserialize<Ent_WSDATA>(JSONdata);
SqlCommand command = new SqlCommand("SP_DELETE_WSDATA");
command.Parameters.Add(new SqlParameter("@PersonalID",
ListUpdateWsData.PersonalID));
SQLFunct.ExecuteNonQuery(command);
string result;
if (SQLFunct.sqlMessage == "1")
{
result = "1";
}
else
{
result = SQLFunct.sqlMessage;
}
return result;
}
catch
(Exception)
{
throw;
}
}
|
-> Implement
GETDATA
|
public List<Ent_WSDATA>
GETWSDATA(Stream param)
{
List<Ent_WSDATA> ListWSDATA = new List<Ent_WSDATA>();
try
{
StreamReader reader = new StreamReader(param);
string JSONdata = reader.ReadToEnd();
JavaScriptSerializer jss = new JavaScriptSerializer();
Ent_WSDATA prm = jss.Deserialize<Ent_WSDATA>(JSONdata);
SqlCommand command = new SqlCommand();
if (prm.PersonalID == "")
{
command = new SqlCommand("SP_SELECT_ALL_WSDATA");
}
else
{
command = new SqlCommand("SP_SELECT_WSDATA");
command.Parameters.Add(new SqlParameter("@PersonalID",
prm.PersonalID));
}
DataTable dtListWSDATA = SQLFunct.ExecuteReader(command);
ListWSDATA = (from DataRow row in dtListWSDATA.Rows
select new Ent_WSDATA
{
PersonalID
= row["PersonalID"].ToString(),
PersonalName = row["PersonalName"].ToString(),
Address =
row["Address"].ToString(),
Gender =
row["Gender"].ToString(),
Hoby = row["Hoby"].ToString()
}).ToList();
return ListWSDATA;
}
catch
(Exception x)
{
return ListWSDATA;
throw;
}
}
|
1.
Testing WSDATA
·
Insert Data
Sebelum Insert
Selanjutnya tambahkan source code berikut di dalam method
protected void
Application_BeginRequest(object
sender, EventArgs e)
{
}
|
try
{
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Origin", "*");
if (HttpContext.Current.Request.HttpMethod == "OPTIONS")
{
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Methods", "POST, PUT, DELETE");
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Headers", "Content-Type, Accept");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.SuppressContent
= true;
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
}
catch
(Exception x)
{
string msg = x.Message;
}
|
Ok. Semoga Bermanfaat
