ZDIRY-TUFWT-EBONM-EYJ00-IDBLANTER.COM
ZDIRY-TUFWT-EBONM-EYJ00

BLANTERWISDOM105

Select, Insert, Update, Delete Menggunakan Store Procedure SQL Server di ASP NET MVC

7/06/2020

Latar Belakang

Salah satu manfaat dari Store Procedures SQL Server adalah dapat menampung beban yang tadinya ada di aplikasi Front End sekarang beralih ke Store Procedures di SQL Server. Mengapa hal bisa dilakukan karena pada aplikasi Front End dapat hanya memanggil Stored Procedures dan mengirimkan beberapa parameter yang dibutuhkan saja. Dengan memanfaatkan kemampuan Stored Procedures ini, Agung Panduan mencoba menjabarkan sebuah contoh proses Select, Insert, Update, dan Delete menggunakan Stored Procedure dengan parameter.

Sebelum melanjutkan ke pembahasan yang lebih detailnya sebaiknya anda membuat Class Model di ASP NET MVC seperti berikut ini,


public class CRUDStoreProcedures
{
    public string Name { get; set; }
    public string Gender { get; set; }
    public string Email { get; set; }
    public string ClassM { get; set; }

    public IList<CRUDStoreProcedures> listData { get; set; }
}

Model ini akan digunakan untuk menampung value yang akan dikirim sebagai parameter maupun menampung hasil dari read data.

Dan buat table di database SQL server,


USE [TEST]

CREATE TABLE [dbo].[CLASS](
	[Name] [varchar](30) NOT NULL,
	[Email] [varchar](50) NOT NULL,
	[Gender] [varchar](10) NOT NULL,
	[ClassM] [varchar](7) NOT NULL
) ON [PRIMARY]

GO

INSERT [dbo].[CLASS] ([Name], [Email], [Gender], [ClassM]) VALUES (N'ag', N'ag@gmail.com', N'Male', N'Class1')

Select Data Store Procedures Di ASP NET

Kita disini akan memanggil store procedures dan mengirimkan beberapa parameter yang dibutuhkan. Dan hasil read data akan disimpan pada list,


string connectionString = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
        SqlConnection connection;

        public List<CRUDStoreProcedures> getAllDataUsingProcedure(CRUDStoreProcedures data, int rowStart, int rowEnd)
        {
            List<CRUDStoreProcedures> ListDataClass = new List<CRUDStoreProcedures>();

            connection = new SqlConnection(connectionString);
            connection.Open();
            SqlCommand com = new SqlCommand("SP_GetDataClass", connection);
            com.CommandType = CommandType.StoredProcedure;

            //CARA 1
            if (data.Name == null || data.Email == null)
            {
                com.Parameters.AddWithValue("@Name", DBNull.Value);
                com.Parameters.AddWithValue("@Email", DBNull.Value);
                com.Parameters.AddWithValue("@RowStart", 0);
                com.Parameters.AddWithValue("@RowEnd", 10);
            }
            else
            {
                com.Parameters.AddWithValue("@Name", data.Name);
                com.Parameters.AddWithValue("@Email", data.Email);
                com.Parameters.AddWithValue("@RowStart", rowStart);
                com.Parameters.AddWithValue("@RowEnd", rowEnd);
            }


            //CARA 2
            //if (data.Name == null || data.Gender==null)
            //{
            //    com.Parameters.Add("@Name", SqlDbType.VarChar, 30).Value = DBNull.Value;
            //    com.Parameters.Add("@Email", SqlDbType.VarChar, 30).Value = DBNull.Value;
            //    com.Parameters.Add("@RowStart", SqlDbType.Int, 30).Value =0;
            //    com.Parameters.Add("@RowEnd", SqlDbType.Int, 30).Value = 10;
            //}
            //else
            //{ agungpanduan.com
            //    com.Parameters.Add("@Name", SqlDbType.VarChar, 30).Value = data.Name;
            //    com.Parameters.Add("@Email", SqlDbType.VarChar, 30).Value = data.Emial;
            //    com.Parameters.Add("@RowStart", SqlDbType.Int, 30).Value = rowStart;
            //    com.Parameters.Add("@RowEnd", SqlDbType.Int, 30).Value = rowEnd;
            //}


            IDataReader reader = com.ExecuteReader();

            while (reader.Read())
            {
                CRUDStoreProcedures classm = new CRUDStoreProcedures()
                {
                    Name = Convert.ToString(reader["Name"]), //Convert.ToInt32(reader["id"]),
                    Gender = Convert.ToString(reader["Gender"]),
                    Email = Convert.ToString(reader["Email"]),
                    ClassM = Convert.ToString(reader["ClassM"]),
                };

                ListDataClass.Add(classm);
            }

            reader.Close();
            connection.Close();

            return ListDataClass;
        }

Catatan: DBNull.Value digunakan untuk mengatasi apabila nilai parameter awal masih kosong atau search pertama kali saat loading halaman awal pada gridview.

Setelah Method GetData telah dibuat sekarang buatkan Stored Procedures dengan nama SP_GetDataClass di database SQL Server anda dengan kode di bawah ini,


USE [TEST]
GO
/****** Object:  StoredProcedure [dbo].[SP_GetDataClass]    Script Date: 30/06/2020 09:31:05 ******/
/****** AGUNGPANDUAN.COM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_GetDataClass] 
	@Name varchar(30),
	@Email varchar(10),
	@RowStart int,
	@RowEnd int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM (SELECT ROW_NUMBER() over (order by [Name] asc) row,
                   [Name],[Email],[Gender],[ClassM] FROM [dbo].[CLASS] Where 1=1
                   AND (NULLIF(@Name,'') IS NULL OR [Name] like '%'+@Name+'%')
                    AND (NULLIF(@Email ,'') IS NULL OR [Email ] like '%'+@Email +'%')
) x WHERE x.row between cast(@RowStart AS varchar) AND cast (@RowEnd as varchar); END

Insert Data Store Procedures Di ASP NET

Beda dengan Select Data, kita akan mengirimkan parameter Output yang akan menampung pesan error yang terjadi pada saat proses execute stored procedures. Walaupun paremeter output ini bisa juga digunakan untuk Select data.

Pada Method RepoResult Insert di bawah ini, kita akan mengakses Stored Procedures dengan nama SP_InsertData. Parameter input yang akan dikirimkan ke Stored Procedures adalah @Name, @Email, @Gender, dan @ClassM sedangkan untuk menampung output pesan kesalahan ketika proses execute di stored procedures adalah @ro_v_err_mesg dan ro_n_return_value. Tidak hanya itu kita kan melakukan proses validasi apakah data yang ditampung ke parameter input itu null atau tidak jika null maka harus di set sebagai DBNull.value. Dengan set DBNull.value ini akan menghindari pesan kesalahan “Additional information Procedure or function '' excepts parameter '@Name', which was not supplied”.


public RepoResult Insert(CRUDStoreProcedures Data)
{
    SqlTransaction trx = null;
    connection = new SqlConnection(connectionString);
    connection.Open();
    trx = connection.BeginTransaction();

    SqlCommand cmd = new SqlCommand("[dbo].[SP_InsertData]", connection, trx);
    cmd.CommandType = CommandType.StoredProcedure;
    RepoResult repoResult = new RepoResult();

    var outputErrMesg = new System.Data.SqlClient.SqlParameter("@ro_v_err_mesg", System.Data.SqlDbType.VarChar, 2000);
    outputErrMesg.Direction = System.Data.ParameterDirection.Output;

    var retVal = new System.Data.SqlClient.SqlParameter("@ro_n_return_value", System.Data.SqlDbType.Int);
    retVal.Direction = System.Data.ParameterDirection.Output;
    
    cmd.Parameters.Add(outputErrMesg);
    cmd.Parameters.Add(retVal);
    if (Data.Name == null || Data.Gender==null || Data.Email ==null || Data.ClassM == null)
    {
        cmd.Parameters.Add("@Name", SqlDbType.VarChar, 30).Value = DBNull.Value;
        cmd.Parameters.Add("@Email", SqlDbType.VarChar, 30).Value = DBNull.Value;
        cmd.Parameters.Add("@Gender", SqlDbType.VarChar, 30).Value = DBNull.Value;
        cmd.Parameters.Add("@ClassM", SqlDbType.VarChar, 30).Value = DBNull.Value;
    }
    else
    {
        cmd.Parameters.Add("@Name", SqlDbType.VarChar, 30).Value = Data.Name;
        cmd.Parameters.Add("@Email", SqlDbType.VarChar, 30).Value = Data.Email;
        cmd.Parameters.Add("@Gender", SqlDbType.VarChar, 30).Value = Data.Gender;
        cmd.Parameters.Add("@ClassM", SqlDbType.VarChar, 30).Value = Data.ClassM;
    }

    cmd.ExecuteNonQuery();
    trx.Commit();
    connection.Close();

    repoResult.Result = RepoResult.VALUE_SUCCESS;
    if ((int?)retVal.Value != 0)
    {
        repoResult.Result = RepoResult.VALUE_ERROR;
        string errMesg = string.Empty;
        if (outputErrMesg != null && outputErrMesg.Value != null)
        {
            errMesg = outputErrMesg.Value.ToString();
        }
        repoResult.ErrMesgs = new string[1];
        repoResult.ErrMesgs[0] = errMesg;
    }
   
    return repoResult;
}

Setelah Method Insert telah dibuat sekarang buatkan Stored Procedures dengan nama SP_InsertData di database SQL Server anda dengan kode di bawah ini,


CREATE PROCEDURE [dbo].[SP_InsertData]
       @ro_v_err_mesg varchar(2000) output,
	   @ro_n_return_value INT OUTPUT,
       @Name varchar(30),
	   @Email varchar(50),
	   @Gender varchar(10),
	   @ClassM varchar(7)
AS
BEGIN TRY
       SET NOCOUNT ON;
	   set @ro_v_err_mesg=''
       set @ro_n_return_value = 0
        --Tambahkan parameter
       if @ro_n_return_value <> 0
       begin
              return @ro_n_return_value
       end
       /****** AGUNGPANDUAN.COM ******/
       --INPUTKAN PROSES INSERT ATAU UPDATE
	   if @Name = Null or @Email = Null
	   begin
	   	set @ro_n_return_value = 1
	   	set @ro_v_err_mesg = 'ERROR: No Data Stored'
	   end
	   else
	   begin
			INSERT INTO [dbo].[CLASS]
						([Name]
						,[Email]
						,[Gender]
						,[ClassM])
			VALUES
                  (@Name, @Email, @Gender, @ClassM)

	   end

       return @ro_n_return_value
END TRY
BEGIN CATCH
       DECLARE @ErrorMessage NVARCHAR(4000),
                     @ErrorSeverity INT,
                     @ErrorState INT,
                     @ErrorLine INT

       Select @ErrorMessage =ERROR_MESSAGE(),
              @ErrorSeverity = ERROR_SEVERITY(),
              @ErrorState = ERROR_SEVERITY(),
              @ErrorLine =ERROR_LINE()
       /****** AGUNGPANDUAN.COM ******/
       set @ro_n_return_value = 2
       Set @ro_v_err_mesg = 'ERROR: SP_InsertData: ' + @ErrorMessage + ', at line = ' + CAST(@ErrorLine as varchar)

       PRINT 'SP_InsertData: ' + @ErrorMessage + ', at line = ' + CAST(@ErrorLine as varchar)

       RETURN @ro_n_return_value
END CATCH

Update Data Store Procedures Di ASP NET

Untuk metode update di ASP NET tidak berbeda jauh dengan Insert Data hanya cukup merubah nama stored procedures. Biasanya untuk update memerlukan sebuah key (kata kunci) untuk memastikan data tersebut exist. Jadi kita memerlukan proses Validasi Exist Data di Stored Procedures.


CREATE PROCEDURE [dbo].[SP_UpdateData]
       @ro_v_err_mesg varchar(2000) output, -- nilai yang akan dikembalikan ke ASP NET
	   @ro_n_return_value INT OUTPUT, --nilai yang akan dikembalikan ke ASP NET
       @Name varchar(30),
	   @Email varchar(50),
	   @Gender varchar(10),
	   @ClassM varchar(7)
AS
BEGIN TRY
       SET NOCOUNT ON;
	   declare
	     @existcount int

	   set @ro_v_err_mesg=''
       set @ro_n_return_value = 0
        --Tambahkan parameter
       /****** AGUNGPANDUAN.COM ******/
       if @ro_n_return_value <> 0
       begin
              return @ro_n_return_value
       end

       --INPUTKAN PROSES INSERT ATAU UPDATE
	   if @Name = Null or @Email = Null or @Gender = Null or @ClassM = Null
	   begin
	   	set @ro_n_return_value = 1
	   	set @ro_v_err_mesg = 'ERROR: No Data Stored'
	   end
	   else
	   begin
			set @existcount = (SELECT COUNT(1) From [dbo].[CLASS] WHERE [Name] = @Name and [Email]=@Email)
			if @existcount = 1 
			begin 
				UPDATE [dbo].[CLASS]
					SET [Gender] = @Gender
						,[ClassM] = @ClassM
					WHERE [Name] = @Name and [Email]=@Email
			end
			else
			begin
				 if @ro_n_return_value <> 0
				 begin
					return @ro_n_return_value
				 end
				set @ro_n_return_value = 1
	   			set @ro_v_err_mesg = 'ERROR: Data Not Exist'
			end
	   end

       return @ro_n_return_value
END TRY
BEGIN CATCH
       DECLARE @ErrorMessage NVARCHAR(4000),
                     @ErrorSeverity INT,
                     @ErrorState INT,
                     @ErrorLine INT

       Select @ErrorMessage =ERROR_MESSAGE(),
              @ErrorSeverity = ERROR_SEVERITY(),
              @ErrorState = ERROR_SEVERITY(),
              @ErrorLine =ERROR_LINE()

       set @ro_n_return_value = 2
       Set @ro_v_err_mesg = 'ERROR: SP_InsertData: ' + @ErrorMessage + ', at line = ' + CAST(@ErrorLine as varchar)
       /****** AGUNGPANDUAN.COM ******/
       PRINT 'SP_InsertData: ' + @ErrorMessage + ', at line = ' + CAST(@ErrorLine as varchar)

       RETURN @ro_n_return_value
END CATCH

Delete Data Store Procedures Di ASP NET

Untuk metode Delete di ASP NET tidak berbeda jauh dengan Insert Data hanya cukup merubah nama stored procedures dan proses Delete memerlukan sebuah key (kata kunci) untuk memastikan data tersebut exist.


CREATE PROCEDURE [dbo].[SP_DeleteData]
       @ro_v_err_mesg varchar(2000) output, -- nilai yang akan dikembalikan ke ASP NET
	   @ro_n_return_value INT OUTPUT, --nilai yang akan dikembalikan ke ASP NET
       @Name varchar(30),
	   @Email varchar(50)
AS
BEGIN TRY
       SET NOCOUNT ON;
	   declare
	     @existcount int

	   set @ro_v_err_mesg=''
       set @ro_n_return_value = 0
        --Tambahkan parameter
		--agungpanduan.com

       if @ro_n_return_value <> 0
       begin
              return @ro_n_return_value
       end

       --INPUTKAN PROSES INSERT ATAU UPDATE
	   --agungpanduan.com
	   if @Name = Null or @Email = Null
	   begin
	   	set @ro_n_return_value = 1
	   	set @ro_v_err_mesg = 'ERROR: No Data Stored'
	   end
	   else
	   begin
			set @existcount = (SELECT COUNT(1) From [dbo].[CLASS] WHERE [Name] = @Name and [Email]=@Email)
			if @existcount = 1 
			begin 
				DELETE FROM [dbo].[CLASS]
				WHERE [Name] = @Name and [Email]=@Email
			end
			else
			begin
				 if @ro_n_return_value <> 0
				 begin
					return @ro_n_return_value
				 end
				set @ro_n_return_value = 1
	   			set @ro_v_err_mesg = 'ERROR: Data Not Exist'
			end
	   end

       return @ro_n_return_value
END TRY
BEGIN CATCH
       DECLARE @ErrorMessage NVARCHAR(4000),
                     @ErrorSeverity INT,
                     @ErrorState INT,
                     @ErrorLine INT

       Select @ErrorMessage =ERROR_MESSAGE(),
              @ErrorSeverity = ERROR_SEVERITY(),
              @ErrorState = ERROR_SEVERITY(),
              @ErrorLine =ERROR_LINE()

       set @ro_n_return_value = 2
       Set @ro_v_err_mesg = 'ERROR: SP_InsertData: ' + @ErrorMessage + ', at line = ' + CAST(@ErrorLine as varchar)
		--agungpanduan.com
       PRINT 'SP_InsertData: ' + @ErrorMessage + ', at line = ' + CAST(@ErrorLine as varchar)

       RETURN @ro_n_return_value
END CATCH

Demikianlah penjelasan tentang cara penggunaan Stored Procedures untuk keperluan CRUD di ASP NET. Jika anda membutuhkan file untuk contoh project ini anda dapat mengunjungi situs github.com/agungpanduan

Share This :

0 Comments