Quartz 5

10. EF Stored Proc with Parameters

4 min read

হ্যালো হাসিব! আমরা ডাটাবেসের আরেকটি এডভান্সড টপিক শেষ করলাম। আগের লেকচারে আমরা শুধু ডাটা রিড (SELECT) করার জন্য Stored Procedure (SP) ব্যবহার করেছিলাম। কিন্তু এই লেকচারে আমরা দেখলাম কীভাবে Parameters পাস করে Non-query অপারেশন (INSERT, UPDATE, DELETE) এর জন্য SP লিখতে এবং এক্সিকিউট করতে হয়।

পুরো লেকচারটির একটি কুইক রিভিশন সামারি নিচে দিচ্ছি:

📝 Lecture Summary at a Glance

  • Two Execution Methods:

  • FromSqlRaw / FromSql: ডাটা রিড করার জন্য (যেমন SELECT), যা Model Object-এর লিস্ট রিটার্ন করে।

  • ExecuteSqlRaw / ExecuteSql: Non-query অপারেশনের জন্য (যেমন INSERT, UPDATE), যা কয়টি Row অ্যাফেক্ট হয়েছে তার সংখ্যা (int) রিটার্ন করে।

  • Parameters in SP: Microsoft.Data.SqlClient নেমস্পেস থেকে SqlParameter ক্লাস ব্যবহার করে C# অবজেক্টের ভ্যালুগুলোকে ডাটাবেসের SP প্যারামিটারে ম্যাপ করা হয়।

  • Creation via Migration: আগের মতোই একটি Empty Migration তৈরি করে তার Up() মেথডে Raw SQL দিয়ে SP তৈরি করতে হয়।

  • Benefits: জটিল লজিক ডাটাবেসের ভেতরেই প্রসেস করা যায় এবং C# কোড টাচ না করেই শুধু SP পরিবর্তন করে বিজনেস লজিক চেঞ্জ করা যায়।

🧠 Comprehensive Breakdown & Deep Dive

১. Code-First Migration দিয়ে INSERT Stored Procedure তৈরি করা [Importance: 9/10]

  • The “Why”: যখন তোমার Person ইনসার্ট করার সাথে সাথে আরও অনেকগুলো টেবিলে ডাটা আপডেট করতে হবে (যেমন লগ রাখা বা নোটিফিকেশন টেবিল আপডেট করা), তখন C# কোড থেকে বারবার ডাটাবেস কল না করে, একটি SP লিখে সবগুলো কাজ একসাথে ডাটাবেসেই করে ফেলা ভালো।

💻 Migration Implementation (Raw SQL): আগের মতোই একটি মাইগ্রেশন ফাইলে Up() মেথডে SP তৈরি করতে হবে। এখানে খেয়াল করার বিষয় হলো, প্যারামিটারগুলো @ চিহ্ন দিয়ে ডিফাইন করতে হয়।

protected override void Up(MigrationBuilder migrationBuilder)
{
    string spSql = @"
        CREATE PROCEDURE dbo.InsertPerson
        (
            @PersonId uniqueidentifier,
            @PersonName nvarchar(max),
            @Email nvarchar(max)
        )
        AS
        BEGIN
            INSERT INTO dbo.persons (PersonId, PersonName, Email)
            VALUES (@PersonId, @PersonName, @Email)
        END";
 
    migrationBuilder.Sql(spSql);
}

২. SqlParameter তৈরি করা [Importance: 8/10]

  • The “Why”: ডাটাবেসের SP প্যারামিটারগুলো (যাদের আগে @ আছে) C#-এর অবজেক্ট সরাসরি চেনে না। তাই C# প্রপার্টিকে SQL-এর প্যারামিটারে কনভার্ট করার জন্য ADO.NET-এর SqlParameter ক্লাস ব্যবহার করতে হয়।

💻 Code Implementation (Legacy Approach as per Lecture):

using Microsoft.Data.SqlClient;
 
public int sp_InsertPerson(Person person)
{
    // C# অবজেক্ট থেকে SQL প্যারামিটারের অ্যারে তৈরি
    SqlParameter[] parameters = new SqlParameter[]
    {
        new SqlParameter("@PersonId", person.PersonId),
        new SqlParameter("@PersonName", person.PersonName),
        new SqlParameter("@Email", person.Email)
    };
 
    // ExecuteSqlRaw ব্যবহার করে ডাটাবেসে কমান্ড পাঠানো
    return Database.ExecuteSqlRaw(
        "EXEC dbo.InsertPerson @PersonId, @PersonName, @Email", 
        parameters
    );
}

৩. Modern .NET 10 Upgrades & Best Practices (The Smart Way) [Importance: 10/10]

  • The Reality: হাসিব, লেকচারে যে পদ্ধতি (SqlParameter Array এবং ExecuteSqlRaw) দেখানো হয়েছে, তা অনেক পুরনো এবং বেশ ঝামেলার। .NET 8/10-এ এটি অনেক বেশি সহজ এবং নিরাপদ করা হয়েছে। String Interpolation ব্যবহার করলে EF Core ইন্টারনালি নিজেই প্যারামিটারগুলো ম্যাপ করে নেয়, আলাদা করে SqlParameter বানানোর কোনো দরকারই নেই!

💻 Code Implementation (.NET 10 Smart Approach):

// ❌ Old Way (Too much code & risk of SQL injection if not careful):
var rows = Database.ExecuteSqlRaw("EXEC dbo.InsertPerson @Name", new SqlParameter("@Name", name));
 
// ✅ Modern .NET 10 Way (Clean, Fast & Auto-parameterized):
public async Task<int> sp_InsertPersonAsync(Person person)
{
    // ExecuteSqlAsync ব্যবহার করা হয়েছে যা নিরাপদ এবং অ্যাসিনক্রোনাস
    int rowsAffected = await Database.ExecuteSqlAsync(
        $"EXEC dbo.InsertPerson @PersonId = {person.PersonId}, @PersonName = {person.PersonName}, @Email = {person.Email}"
    );
 
    return rowsAffected;
}

৪. Service Layer-এ SP কল করা [Importance: 9/10]

Service ক্লাসের AddPerson মেথডে আগের EF Core এর কোডটি ( _db.Persons.Add() এবং _db.SaveChanges() ) রিপ্লেস করে শুধু আমাদের বানানো মেথডটি কল করতে হবে।

public PersonResponse AddPerson(PersonAddRequest request)
{
    // ১. DTO থেকে Entity তে কনভার্ট
    Person person = request.ToEntity();
    person.PersonId = Guid.NewGuid();
 
    // ২. Stored Procedure কল করা
    int rowsAffected = _db.sp_InsertPerson(person); // (বা .NET 10 এর ক্ষেত্রে await sp_InsertPersonAsync(person))
 
    // ৩. Response রিটার্ন করা
    return person.ToResponse();
}

🚀 Best Practices Summary for Enterprise Apps

১. Performance Choice: ছোটখাটো ইনসার্ট বা আপডেটের জন্য SP ব্যবহার করার দরকার নেই। EF Core-এর বিল্ট-ইন .Add() বা .ExecuteUpdate() যথেষ্ট ফাস্ট। SP শুধুমাত্র তখনই ব্যবহার করবে যখন মাল্টিপল টেবিলের জয়েন, ইনসার্ট বা ডাটাবেস লেভেলের জটিল লজিক (Business rules inside DB) রান করার প্রয়োজন হয়। ২. Async Everything: Data Access লেয়ারে সব অপারেশন Task এবং async/await দিয়ে লিখবে। এটি হাই-ট্রাফিক অ্যাপ্লিকেশনে (যেমন তোমার SaaS প্রোজেক্ট) সার্ভারের রেসপন্স টাইম অনেক ভালো রাখে। ৩. Avoid ExecuteSqlRaw: সবসময় ExecuteSql বা ExecuteSqlAsync (string interpolation) ব্যবহার করবে, এটি SQL Injection থেকে শতভাগ সিকিউরিটি দেয়।

লেকচারার অ্যাসাইনমেন্ট হিসেবে Update এবং Delete অপারেশনের জন্য SP বানাতে বলেছেন। তুমি যদি সেগুলো নিজে ট্রাই করতে চাও বা পরের লেকচারের ট্রান্সক্রিপ্ট দিতে চাও, আমি রেডি আছি!

Graph View

Backlinks