Skip to main content

Membuat CRUD Dengan Web Service C# & SQL Server


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

Labels:

Comments

  1. Andika_0172 January 2021 at 03:12

    http://www.iqprogramming.site/2013/08/tutorial-sql-server-contoh-aplikasi.html

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Create a REST API in SAP ABAP — Step-by-step Guide

How to Create a REST API in SAP ABAP — Step-by-step Guide Summary: This tutorial shows how to build a REST API in SAP ABAP by creating a handler class, configuring SICF service, registering endpoints with cl_rest_router , implementing endpoint logic (example GET method), and testing the API. Based on an internal implementation reference. Why expose REST APIs from SAP? REST APIs allow SAP systems to integrate with web, mobile, and external services using standard HTTP and JSON payloads. Implementing REST endpoints in ABAP provides secure, reusable, and maintainable integration points for modern applications. Prerequisites Access to an SAP system with authorization to create classes (SE24) and SICF services (SICF). Familiarity with ABAP object-oriented concepts and basic SAP transaction codes. ABAP classes CL_REST_RESOURCE , CL_REST_ROUTER and utilities like /UI2/CL_JSON . High-level overview (4 steps) Create an API handler class (e.g. ZCL_API_HANDLER ). R...
Post a Comment
Read more

IT Asset Management Dengan PHP MySQL

Pada postingan kali saya akan share sebuah aplikasi IT Asset management yang fungsinya untuk memonitoring semua Asset khusus IT, contohnya : Laptop/komputer , Printer, Router, Hardisk, dll. Dalam aplkasi ini kita bisa mengetahui Asset IT posisinya dimana dan digunakan oleh siapa. untuk data-data yang dibutuhkan antara lain : 1. data kategori asset dalam menu ini kita bisa meng-input jenis2 kategory asset : tambah kategori asset : 2. data department 3. data karyawan 4. data department per karyawan 5. data asset location  6. data satuan asset dan untuk transaksi yang ada dalam aplikasi ini adalah,  1. create asset, pada menu create asset ini kita akan mengalokasikan sebuah asset ke karyawan/personnel tampilannya seperti berikut: setelah klik tombol save akan muncul seperti dibawah : untuk melihat detail asset yang sudah dibuat tadi, kita bisa pilih...
55 comments
Read more

SAP ABAP - User Exit Set Batch Characteristic Value In MIGO Goods Receipt

Customer Exit  :  MBCFC004 ( EXIT_SAPMM07M_004) Set Up Customer Exit for Classification of User-Defined Characteristics You use SAP enhancement MBCFC004 EXIT_SAPMM07M_004, which contains function module exit EXIT_SAPMM07M_004 to classify user-defined characteristics automatically during goods movements in Inventory Management. This is only possible for characteristics which are not assigned values during quality inspection. Requirements 1. The class of the batch to be classified must be known.  This means that a class must be assigned either to the material or at least to one batch of this material. 2. The exit call must be activated for the respective movement type in activity Activate batch classification during goods movements in IM using indicator 'Extended classification' . Open tcode OMC...
Post a Comment
Read more