Параметризовани упити и ускладиштене процедуре

Ускладиштене процедуре (енг. Stored Procedures) су унапред дефинисане SQL скрипте које се чувају на SQL Server-у и могу се позивати из .NET Framework апликација. Оне омогућавају централизацију логике, побољшавају перформансе и повећавају безбедност.

Зашто је добра идеја да SQL упите пишеш у SQL Server-у, а не у самој .NET Framework апликацији? Што се перформанси тиче, ако се процедура налази у SQL Server-у, онда ће SQL Server бити задужен да компајлира и оптимизује SQL процедуре, што убрзава њихово извршавање. Што се безбедности тиче, због параметризације упита смањује се ризик од SQL Injection-а и омогућава се контрола приступа. Што се тиче одржавања кода, промене у процедури се врше на SQL Server-у, без потребе за ажурирањем C# кода у апликацији. На крају, једна ускладиштена процедура у SQL Server-у може да се позива из различитих делова .NET Framework апликације.

О ускладиштеним процедурама учио си у III разреду у оквиру наставног предмета Базе података, али је сада право време да се подсетиш како се креирају ускладиштене процедуре и како се параметризују упити. Пример претраге купаца по земљи из претходне лекције је идеалан кандидат за ускладиштену процедуру. Дакле, треба да креираш процедуру која прима један параметар, @Country, и враћа све купце из те земље.

У SMSS у бази података Northwind пронађи ставку Programmability, па у оквиру ње пронађи ставку Stored Procedures. Десним тастером миша кликни на Stored Procedures па одабери Stored Procedure…. Отвориће се шаблон за креирање упита који креира ускладиштене процедуре, а који изгледа овако:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
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 <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Пракса је да се све ускладиштене процедуре које креира програмер именују са префиксом usp_, јер системске ускладиштене процедуре имају префикс sp_. Твоја ускладиштена процедура ће у овом случају бити прилично једноставна:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.usp_GetCustomersByCountry
    @Country NVARCHAR(15)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CustomerID, CompanyName, City, Country 
    FROM dbo.Customers 
    WHERE Country = @Country
END
GO

У менију кликни на дугме Execute како би се извршио дати упит и креирала ускладиштена процедура. Када добијеш поруку Commands completed successfully. на тастатури притисни тастер F5, па провери да ли се у секцији Programmability / Stored Procedures налази ускладиштена процедура dbo.usp_GetCustomersByCountry. Сада можеш да отвориш свој Windows Forms пројекат из претходне лекције и измениш кôд на следећи начин…

private void btnSearch_Click(object sender, EventArgs e)
{
    string country = txtCountry.Text;
    string connString = "Data Source=LOCALHOST\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.usp_GetCustomersByCountry", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = country;
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader.HasRows)
            {
                // Prikaz rezultata...
            }
            else
            {
                MessageBox.Show("Није пронађен ниједан купац из унете земље.");
            }
        }
    }
}

…или, наравно, можеш користити и једноставнију методу AddWithValue, мада си научио зашто је Add често бољи избор:

cmd.Parameters.AddWithValue("@Country", country);

Шта се променило у коду ове методе у односу на кôд из претходне лекције? Из кода је нестао SQL упит, а у коду се појавила линија која означава да је упит дефинисан као ускладиштена процедура:

cmd.CommandType = CommandType.StoredProcedure;

Значи, овим се SQL Server-у наговештава да уместо SQL упита очекује позив ускладиштене процедуре. Такође, уместо SQL упита, у SqlCommand наводи се име процедуре usp_GetCustomersByCountry, а параметри се додају на исти начин као код параметризованих упита у претходној лекцији. Обрати пажњу да, уколико се параметри ускладиштене процедуре не поклапају по имену или редоследу са оним што апликација шаље, извршавање може бити неуспешно или дати неочекиване резултате.

Ако желиш да провериш да ли твоја процедура ради без апликације, можеш је тестирати директно у SSMS-у. Кликом на дугме New Query у менију SSMS-а креирај нови упит, унеси…

EXEC dbo.usp_GetCustomersByCountry @Country = 'Germany'

…па кликни на дугме у менију Execute. Као резултат добићеш табелу:

CustomerID	CompanyName	City	Country
ALFKI	Alfreds Futterkiste	Berlin	Germany
…	…	…	…

Ово потврђује да је твоја ускладиштена процедура исправна и спремна за коришћење у апликацији.