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
boleh saran bg, sebaiknya ada video youtubenya juga jadi bagi pemula bisa mengikutin tutorial bg
BalasHapus