در این مقاله قصد داریم یک Stored Procedure را با یک روش امن، با استفاده از Stored Procedure های خود .NetFramework پاسخ دهیم.Stored Procedure یا Sp یا به زبان فارسی " رویه های ذخیره شده " اشیایی اجرا پذیر در بانک اطلاعاتی SQL Server هستند که شامل یک یا چندین دستور SQL می شود ، این رویه ها میتوانند پارامتر های ورودی و خروجی داشته باشند.

:Stored Procedure
 داخل این رویه ها  می توان به زبان SQL برنامه نویسی کرد .
مهم ترین کاربرد این رویه ها ذخیره کردن دستورات Select , Insert , Update , Delete هست یا ترکیبی از اینها .
نحوه ساخت این رویه ها به صورت زیر می باشد :

وارد بانک اطلاعاتی SQL Server شده ، پس از باز کردن بانک مورد نظر در قسمت Programmability وارد بخش Stored Procedure شوید .

بر روی Stored Procedure  کلیک راست کرده و New Stored Procedure را انتخاب نمایید .

یک مثال ابتدایی که یک Stored Procedure را با استفاده از My FrameWork های خود صدا زده است.در

TestMethod زیر آمده است:

[TestMethod]
public void NormalStoredProcedure_WhenCalledOnDbContext_ReturnsCorrectValues()
{
    // ARRANGE  
    const int expectedId = 10;
    const string expectedName = @"Dave";
    const bool expectedActive = true;

    var parameters = new NormalStoredProcedureParameters
    {
        Id = expectedId
    };
    var procedure = new NormalStoredProcedure(parameters);
    
    // ACT
    var resultSet = Context.ExecuteStoredProcedure(procedure);
    var results = resultSet.RecordSet1;
    var result = results.First();

    // ASSERT
    Assert.AreEqual(expectedId, result.Id);
    Assert.AreEqual(expectedName, result.Name);
    Assert.AreEqual(expectedActive, result.Active);
}

Contex ها از DBContex در EntityFramework ارث بری می شود، بنابر این ما می توانیم Stored Procedure را به وسیله ی Contex.StoredProcedure صدا بزنیم و شی Stored Procedure را ارسال کنیم.

Stored Procedure های FrameWork نیاز ندارند که شما از DBContex های EntityFramework استفاده کنید.

شما می توانید به جای صدا زدن Stored Procedure از یک Extention Method به نام DqlConnectionT

یا SqlConnection استفاده کنید.

[TestMethod]
public void NormalStoredProcedure_WhenCalledOnSqlConnection_ReturnsCorrectValues()
{
    // ARRANGE  
    const int expectedId = 10;
    const string expectedName = @"Dave";
    const bool expectedActive = true;

    var parameters = new NormalStoredProcedureParameters
    {
        Id = expectedId
    };
    //List<NormalStoredProcedureRecordSet1ReturnType> results;
    NormalStoredProcedureResultSet resultSet;
    var procedure = new NormalStoredProcedure(parameters);
    var connectionString = ConfigurationManager.ConnectionStrings["IntegrationTestConnection"].ConnectionString;
    
    // ACT
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        resultSet = connection.ExecuteStoredProcedure(procedure);
    }
    var results = resultSet.RecordSet1;
    var result = results.First();
    
    // ASSERT
    Assert.AreEqual(expectedId, result.Id);
    Assert.AreEqual(expectedName, result.Name);
    Assert.AreEqual(expectedActive, result.Active);
}

هر دو این نمونه ها بر اساس Stored Procedure های ساده است:

CREATE PROCEDURE dbo.NormalStoredProcedure
    @Id  INT
AS
BEGIN
    SELECT 
        @Id AS Id
    ,   'Dave' AS Name
    ,   CAST(1 AS BIT) AS Active
END

هر دو نمونه یک شی برای سی شارپ و Stoered Procedure و مجموعه ی نتیجه ، پارامترها، نوع بازگشت

را شرح داده است.

internal class NormalStoredProcedure
    : StoredProcedureBase<NormalStoredProcedureResultSet, NormalStoredProcedureParameters>
{
    public NormalStoredProcedure(NormalStoredProcedureParameters parameters)
        : base(parameters)
    {
    }
}

internal class NormalStoredProcedureResultSet
{
    public List<NormalStoredProcedureRecordSet1ReturnType> RecordSet1 { get; set; }

    public NormalStoredProcedureResultSet()
    {
        RecordSet1 = new List<NormalStoredProcedureRecordSet1ReturnType>();
    }
}

internal class NormalStoredProcedureParameters
{
    [ParameterDbType(SqlDbType.Int)]
    public int Id { get; set; }
}

internal class NormalStoredProcedureRecordSet1ReturnType
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool Active { get; set; }
}

نمونه ای از RecordSets چندگانه:

در مثال زیر FrameWork می تواند چندین RecordSets را با یک Stored Procedure برگردانند.در مثال زیر

دقت نمایید:

[TestMethod]
public void MultipleRecordSetStoredProcedure_WithThreeSelects_ReturnsThreeRecordSets()
{
    // ARRANGE
    const int expectedId = 10;
    const string expectedName = "Sid";
    const bool expectedActive = true;
    const decimal expectedPrice = 10.99M;
    Guid expectedUniqueIdentifier = Guid.NewGuid();
    const byte expectedCount = 17;
    var parameters = new MultipleRecordSetStoredProcedureParameters
    {
        Id = expectedId,
        Name = expectedName,
        Active = expectedActive,
        Price = expectedPrice,
        UniqueIdentifier = expectedUniqueIdentifier,
        Count = expectedCount
    };
    MultipleRecordSetStoredProcedureResultSet resultSet;
    var procedure = new MultipleRecordSetStoredProcedure(parameters);
    var connectionString = ConfigurationManager.ConnectionStrings["IntegrationTestConnection"].ConnectionString;

    // ACT
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        resultSet = connection.ExecuteStoredProcedure(procedure);
    }
    var results1 = resultSet.RecordSet1;
    var result1 = results1.First();

    var results2 = resultSet.RecordSet2;
    var result2 = results2.First();

    var results3 = resultSet.RecordSet3;
    var result3 = results3.First();

    // ASSERT
    Assert.AreEqual(expectedId, result1.Id);
    Assert.AreEqual(expectedName, result1.Name);

    Assert.AreEqual(expectedActive, result2.Active);
    Assert.AreEqual(expectedPrice, result2.Price);

    Assert.AreEqual(expectedUniqueIdentifier, result3.UniqueIdentifier);
    Assert.AreEqual(expectedCount, result3.Count);
}

در SQL برای Stored Procedure به صورت زیر صدا زده می شود:

CREATE PROCEDURE [dbo].[MultipleRecordSetStoredProcedure]
    @Id                 INT
,   @Name               VARCHAR(20)
,   @Active             BIT
,   @Price              DECIMAL(10, 4)
,   @UniqueIdentifier   UNIQUEIDENTIFIER
,   @Count              TINYINT
AS
BEGIN
    /* First Record Set */
    SELECT 
        @Id     AS Id
    ,   @Name   AS Name
    UNION
    SELECT
        17      AS Id
    ,   'Bill'  AS Name;

    /* Second Record Set */
    SELECT 
        @Active as Active
    ,   @Price  AS Price

    /* Third Record Set */
    SELECT
        @UniqueIdentifier   AS [UniqueIdentifier]
    ,   @Count              AS [Count]
    
END

و در آخر تمام این موارد باعث می شود که شما از Stored Procedure های زیر استفاده نمایید.

internal class MultipleRecordSetStoredProcedure
    : StoredProcedureBase<MultipleRecordSetStoredProcedureResultSet, MultipleRecordSetStoredProcedureParameters>
{
    public MultipleRecordSetStoredProcedure(MultipleRecordSetStoredProcedureParameters parameters)
        : base(parameters)
    {
    }
}

internal class MultipleRecordSetStoredProcedureParameters
{
    [ParameterDbType(SqlDbType.Int)]
    public int Id { get; set; }
    [Size(20)]
    public string Name { get; set; }
    [ParameterDbType(SqlDbType.Bit)]
    public bool Active { get; set; }
    [ParameterDbType(SqlDbType.Decimal)]
    [Precision(10)]
    [Scale(4)]
    public decimal Price { get; set; }
    [ParameterDbType(SqlDbType.UniqueIdentifier)]
    public Guid UniqueIdentifier { get; set; }
    [ParameterDbType(SqlDbType.TinyInt)]
    public byte Count { get; set; }
}

internal class MultipleRecordSetStoredProcedureResultSet
{
    public List<MultipleRecordSetStoredProcedureReturnType1> RecordSet1 { get; set; }
    public List<MultipleRecordSetStoredProcedureReturnType2> RecordSet2 { get; set; }
    public List<MultipleRecordSetStoredProcedureReturnType3> RecordSet3 { get; set; }

    public MultipleRecordSetStoredProcedureResultSet()
    {
        RecordSet1 = new List<MultipleRecordSetStoredProcedureReturnType1>();
        RecordSet2 = new List<MultipleRecordSetStoredProcedureReturnType2>();
        RecordSet3 = new List<MultipleRecordSetStoredProcedureReturnType3>();
    }
}

internal class MultipleRecordSetStoredProcedureReturnType1
{
    [ParameterDbType(SqlDbType.Int)]
    public int Id { get; set; }

    public string Name { get; set; }
}

internal class MultipleRecordSetStoredProcedureReturnType2
{
    [ParameterDbType(SqlDbType.Bit)]
    public bool Active { get; set; }

    [ParameterDbType(SqlDbType.Decimal)]
    public decimal Price { get; set; }
}

internal class MultipleRecordSetStoredProcedureReturnType3
{
    [ParameterDbType(SqlDbType.UniqueIdentifier)]
    public Guid UniqueIdentifier { get; set; }

    [ParameterDbType(SqlDbType.TinyInt)]
    public byte Count { get; set; }
}