Quartz 5

9. EF Stored Proc

5 min read

হ্যালো হাসিব! আজকের লেকচারটি খুবই ইন্টারেস্ট্রিং এবং অ্যাডভান্সড। আমরা শিখবো কীভাবে Code-First Approach মেইনটেইন করে ডাটাবেসে Stored Procedure তৈরি করতে হয় এবং EF Core দিয়ে তা কল করতে হয়।

তুমি যেহেতু Fedora Linux এবং VS Code ব্যবহার করছো, তাই লেকচারের ভিজ্যুয়াল স্টুডিওর Package Manager Console-এর কমান্ডগুলোর বদলে আমি তোমাকে গাইড করবো কীভাবে .NET CLI দিয়ে লিনাক্স টার্মিনালেই এই কাজগুলো স্মার্টলি করে ফেলা যায়।

প্রথমে রিভিশনের সুবিধার জন্য পুরো লেকচারের একটি শর্ট সামারি দেখে নেওয়া যাক।

📝 Lecture Summary at a Glance

  • Stored Procedure (SP): একাধিক বা জটিল SQL Statement-কে ডাটাবেসে একটি অবজেক্ট হিসেবে গ্রুপ করে রাখা, যা অ্যাপ্লিকেশন থেকে মাত্র একটি ডেটাবেস কলের মাধ্যমে এক্সিকিউট করা যায়। এটি নেটওয়ার্ক ট্রাফিক কমায় এবং পারফরম্যান্স বাড়ায়।
  • Creating SP in Code-First: Code-First-এ সরাসরি ডাটাবেসে গিয়ে SP তৈরি করা নিষিদ্ধ। এর পরিবর্তে একটি empty Migration তৈরি করে তার Up() মেথডে Raw SQL দিয়ে SP তৈরি করতে হয় এবং Down() মেথডে তা DROP করতে হয়।
  • Calling SP for Queries: ডাটাবেস থেকে ডাটা রিড (SELECT) করার জন্য FromSqlRaw মেথড ব্যবহার করে EXEC ProcedureName কমান্ড চালানো হয়।
  • Fixing Test Errors: DbContext-এর কনস্ট্রাক্টর পরিবর্তন করলে Unit Test প্রজেক্টে যে কম্পাইল এরর আসে, তা DbContextOptionsBuilder ব্যবহার করে কীভাবে অবজেক্ট তৈরি করে ফিক্স করতে হয়, তা প্র্যাক্টিক্যালি দেখানো হয়েছে।

🧠 Comprehensive Breakdown & Deep Dive

১. Stored Procedure কী এবং কেন এটি ব্যবহার করবে? [Importance: 9/10]

  • The “Why”: সাধারণ LINQ কুয়েরিতে প্রতিবার কোড রান করার সময় EF Core ব্যাকএন্ডে একটি একক SQL Statement তৈরি করে ডাটাবেসে পাঠায়। কিন্তু তোমার অ্যাপ্লিকেশনে যদি এমন কোনো জটিল কাজ থাকে যেখানে ৪-৫টি বড় বড় SQL কুয়েরি একসাথে চালানো দরকার (যেমন জটিল রিপোর্ট জেনারেশন বা মাল্টিপল টেবিল জয়েন), তখন প্রতিটার জন্য আলাদা আলাদা ডাটাবেস কল করা মোটেও বুদ্ধিমানের কাজ নয়। এতে নেটওয়ার্ক লেটেন্সি (Latency) বাড়বে।
  • Stored Procedure-এর সুবিধা: তুমি সবগুলো SQL কমান্ডকে ডাটাবেসের ভেতরেই একটি ফাংশন বা প্রসিডিউরের মতো করে সেভ করে রাখবে। অ্যাপ্লিকেশন থেকে শুধু সেই প্রসিডিউরের নাম ধরে ডাকবে। পুরো কাজটা ডাটাবেস সার্ভারের ভেতরেই একবারে প্রসেস হয়ে যাবে, ফলে অ্যাপ্লিকেশন অনেক ফাস্ট হবে।

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

  • The “Why”: কোড-ফার্স্ট মেথডে ডাটাবেসের সব স্ট্রাকচার Git-এ ট্র্যাক রাখতে হয়। তুমি যদি সরাসরি SQL Server-এ গিয়ে SP তৈরি করো, টিমের বাকিরা (যেমন রাইসা বা আদোর) যখন কোড পুল করবে, তাদের লোকাল ডাটাবেসে সেই SP থাকবে না। তাই মাইগ্রেশনের মাধ্যমে SP তৈরি করাই বেস্ট প্র্যাকটিস।

💻 Linux Terminal / VS Code CLI Command: প্রথমে টার্মিনালে একটি ফাঁকা (Empty) Migration ফাইল তৈরি করো:

dotnet ef migrations add GetPersonsStoredProcedure --project Entities --startup-project CrudExample

📁 Migration ফাইল ইমপ্লিমেন্টেশন: কমান্ডটি রান করার পর Migrations ফোল্ডারে যে নতুন ফাইলটি তৈরি হবে, তার Up() এবং Down() মেথড এভাবে এডিট করতে হবে:

using Microsoft.EntityFrameworkCore.Migrations;
 
namespace Entities.Migrations;
 
public partial class GetPersonsStoredProcedure : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Stored Procedure তৈরির Raw SQL
        string spSql = @"
            CREATE PROCEDURE dbo.GetAllPersons
            AS
            BEGIN
                SELECT PersonId, PersonName, Gender, Address FROM dbo.persons
            END";
 
        // MigrationBuilder এর মাধ্যমে SQL এক্সিকিউট করা
        migrationBuilder.Sql(spSql);
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // রোলব্যাক বা ডাউনগ্রেড করার সময় SP ডিলিট করার কোড
        migrationBuilder.Sql("DROP PROCEDURE dbo.GetAllPersons");
    }
}

এরপর টার্মিনালে dotnet ef database update --project Entities --startup-project CrudExample রান করলেই ডাটাবেসে SP তৈরি হয়ে যাবে। তুমি চাইলে ডাটাবেসের Programmability -> Stored Procedures ফোল্ডারে এটি দেখতে পাবে।

৩. Unit Test-এর কম্পাইলেশন এরর ফিক্স করা [Importance: 7/10]

  • The “Why”: লেকচারে মাইগ্রেশন তৈরি করার সময় একটি বিল্ড এরর এসেছিল। কারণ DbContext-এ আমরা কনস্ট্রাক্টর যোগ করার পর টেস্ট প্রজেক্টের কোডগুলো ভেঙে গিয়েছিল। টেস্ট ক্লাসে যেখানে ম্যানুয়ালি new PersonsDbContext() করা হচ্ছিল, সেখানে এখন DbContextOptions পাস করতে হবে।

💻 Test Code Fix Example:

// টেস্ট প্রজেক্টে DbContext এর জন্য অপশন বিল্ড করা
var optionsBuilder = new DbContextOptionsBuilder<PersonsDbContext>();
optionsBuilder.UseSqlServer("Your_Test_Connection_String");
 
// ইনজেকশনের জন্য অবজেক্ট তৈরি
var dbContext = new PersonsDbContext(optionsBuilder.Options);
var countriesService = new CountriesService(dbContext);
var personsService = new PersonsService(dbContext, countriesService);

৪. DbContext এবং Service লেয়ার থেকে Stored Procedure কল করা [Importance: 10/10]

  • The “Why”: ডাটাবেসে SP তো তৈরি হলো, কিন্তু কোড থেকে একে কল করার জন্য EF Core-এর FromSqlRaw মেثড ব্যবহার করতে হয়।

💻 DbContext ইমপ্লিমেন্টেশন: তোমার PersonsDbContext.cs ফাইলে নতুন একটি কাস্টম মেথড তৈরি করো:

public class PersonsDbContext : DbContext
{
    public DbSet<Person> Persons { get; set; }
 
    // Stored Procedure কল করার কাস্টম মেথড
    public virtual List<Person> sp_GetAllPersons()
    {
        // EXEC কমান্ড দিয়ে SP কল করা এবং টু-লিস্ট দিয়ে মেমোরিতে ডাটা আনা (Materialization)
        return Persons.FromSqlRaw("EXEC dbo.GetAllPersons").ToList();
    }
}

💻 Service লেয়ারে ব্যবহার: এবার PersonsService.cs ফাইলের GetAllPersons() মেথডে পুরনো LINQ কুয়েরি বদলে সরাসরি আমাদের তৈরি করা SP মেথডটি কল করে দাও:

public List<PersonResponse> GetAllPersons()
{
    // Stored Procedure থেকে ডাটা নিয়ে আসা হচ্ছে
    var persons = _db.sp_GetAllPersons();
 
    // আগের মতোই Response DTO তে ম্যাপ করে রিটার্ন করা
    return persons.Select(p => ConvertPersonToPersonResponse(p)).ToList();
}

🚀 Modern .NET 10 Updates & Best Practices

.NET 10-এর আধুনিক এবং নিরাপদ স্টাইল (FromSql)

লেকচারে FromSqlRaw ব্যবহার করা হয়েছে। হাসিব, .NET 7/8 থেকে শুরু করে লেটেস্ট .NET 10-এ FromSqlRaw ব্যবহার করা একদমই ডিসকারেজ করা হয় (যদি না খুব জটিল ডাইনামিক কুয়েরি হয়)। কারণ এটি সঠিকভাবে হ্যান্ডেল না করলে SQL Injection Security Vulnerability তৈরি হতে পারে।

আধুনিক .NET-এ এর চেয়ে অনেক নিরাপদ এবং ক্লিন মেথড হলো FromSql (যা ইন্টারনালি String Interpolation কে নিরাপদ SQL Parameter-এ রূপান্তর করে)।

// ❌ Old & Less Safe Style:
_context.Persons.FromSqlRaw("EXEC dbo.GetAllPersons").ToList();
 
// ✅ Modern .NET 10 Safe Style:
_context.Persons.FromSql($"EXEC dbo.GetAllPersons").ToList();

💡 Best Practices for Stored Procedures in EF Core

১. Keep Logic in Code, Use SP Only for Performance: বিজনেস লজিক সবসময় C# কোডে (Service Layer) রাখার চেষ্টা করবে। ডাটাবেসের ভেতরে SP-তে অতিরিক্ত লজিক বা IF-ELSE লিখলে কোড মেইনটেইন করা এবং ডিবাগ করা খুব কঠিন হয়ে পড়ে।

২. Always Use Async Extensions: ডাটাবেস কল যেহেতু টাইম-কন্সিউমিং, তাই থ্রেড ব্লকিং এড়াতে লেকচারের ToList() এর জায়গায় সবসময় ToListAsync() ব্যবহার করবে।

public async Task<List<Person>> sp_GetAllPersonsAsync()
{
    return await Persons.FromSql($"EXEC dbo.GetAllPersons").ToListAsync();
}

৩. Match Schema Exactly: মনে রাখবে, SP থেকে যে Column গুলো রিটার্ন আসবে, সেগুলোর নাম এবং ডাটা টাইপ যেন তোমার C# Person মডেলের প্রপার্টির সাথে ১০০% ম্যাচ করে। ম্যাচ না করলে EF Core ডাটা ম্যাপ (Materialize) করতে পারবে না এবং রানটাইমে এরর দেবে।

পরবর্তী লেকচারে কীভাবে Stored Procedure-এ প্যারামিটার পাস করতে হয় এবং Insert/Update/Delete করতে হয় তা দেখানো হবে। তুমি প্রস্তুত থাকলে পরের ট্রান্সক্রিপ্টটি দিতে পারো!

Graph View

Backlinks