Microsoft Technologies Stored Procedure এবং Native SQL Integration গাইড ও নোট

LINQ এবং ORM (Object-Relational Mapping) পদ্ধতিতে ডেটাবেসের সাথে কাজ করা সাধারণত উন্নত এবং আবশ্যকীয় থাকে। তবে কিছু ক্ষেত্রে, বিশেষ করে যখন জটিল কুয়েরি বা পারফরম্যান্সের প্রশ্ন আসে, সেখানে Stored Procedure এবং Native SQL ব্যবহার করা উপকারী হতে পারে। NHibernate বা Entity Framework-এর মতো ORM টুলগুলোর সাথে Stored Procedure এবং Native SQL একত্রিত করা একটি শক্তিশালী টেকনিক হতে পারে।

Stored Procedure এবং Native SQL এর ধারণা

• Stored Procedure হল ডেটাবেসে সংরক্ষিত SQL স্ক্রিপ্ট যা পূর্বনির্ধারিত কার্যক্রম সম্পাদন করে, যেমন ডেটা ইনসার্ট, আপডেট বা কাস্টম কুয়েরি এক্সিকিউট করা।
• Native SQL বা Raw SQL হল ডেটাবেসের সাধারণ SQL ভাষায় লেখা কুয়েরি, যা ORM-এর মডেল থেকে পৃথকভাবে ডেটাবেসের সাথে সরাসরি ইন্টারঅ্যাক্ট করে।

NHibernate এ Stored Procedure এবং Native SQL একত্রিত করার মাধ্যমে আপনি আপনার অ্যাপ্লিকেশনে প্রয়োজনীয় বিশেষ কুয়েরি সহজে এক্সিকিউট করতে পারেন।

Stored Procedure এবং Native SQL ব্যবহার করার সুবিধা

• পারফরম্যান্স বৃদ্ধি: কিছু কেসে, ORM-এর কুইরি পরিকল্পনার তুলনায় Stored Procedure বা Native SQL অনেক বেশি দ্রুত কার্যকর হতে পারে।
• কাস্টম কুয়েরি লজিক: আপনি কাস্টম SQL কুয়েরি লিখতে পারবেন যা NHibernate বা Entity Framework-এর মতো ORM টুলে সহজে বাস্তবায়িত হতে পারে না।
• ডেটাবেস নির্ভরশীলতা কমানো: স্টোরড প্রোসিজারের মাধ্যমে, আপনি অপ্রত্যাশিত SQL পরিকল্পনার পরিবর্তে নির্দিষ্ট প্রোগ্রামেবল লজিক ব্যবহার করতে পারবেন।

NHibernate এ Stored Procedure ব্যবহার

NHibernate এ Stored Procedure কল করা সাধারণত Session.CreateSQLQuery() ব্যবহার করে করা হয়। নিচে একটি উদাহরণ দেওয়া হলো:

উদাহরণ:

using (var session = sessionFactory.OpenSession())
{
    // Stored Procedure কল করা
    var query = session.CreateSQLQuery("EXEC GetEmployeeDetails :employeeId")
                        .SetParameter("employeeId", 101)
                        .AddEntity(typeof(Employee));
    
    var employee = query.UniqueResult<Employee>();
    Console.WriteLine(employee.Name);
}

এখানে:

• CreateSQLQuery() ব্যবহার করে কাস্টম SQL কুয়েরি তৈরি করা হয়েছে।
• SetParameter() পদ্ধতি ব্যবহার করে Stored Procedure এর প্যারামিটার পাস করা হয়েছে।
• AddEntity() পদ্ধতি ব্যবহার করে আউটপুট ডেটা একটি Employee ক্লাসের অবজেক্টে ম্যাপ করা হয়েছে।

এটি Stored Procedure ব্যবহার করে Employee সম্পর্কিত ডেটা সংগ্রহ করে।

NHibernate এ Native SQL ব্যবহার

Native SQL ব্যবহার করা হলে, আপনি সরাসরি SQL কুয়েরি ব্যবহার করতে পারেন, যা ORM মডেলের সাথে কাজ করে না। এতে সাধারণত কাস্টম কুয়েরি এক্সিকিউট করা হয়।

উদাহরণ:

using (var session = sessionFactory.OpenSession())
{
    // Native SQL কুয়েরি এক্সিকিউট করা
    var query = session.CreateSQLQuery("SELECT * FROM Employee WHERE Salary > :salary")
                        .SetParameter("salary", 50000)
                        .AddEntity(typeof(Employee));

    var employees = query.List<Employee>();
    foreach (var employee in employees)
    {
        Console.WriteLine(employee.Name);
    }
}

এখানে:

• সরাসরি SQL Query ব্যবহার করা হয়েছে, যা Employee টেবিল থেকে ডেটা আনবে।
• SetParameter() ব্যবহার করে SQL কুয়েরির প্যারামিটার পাস করা হয়েছে।
• AddEntity() পদ্ধতি ব্যবহার করে আউটপুট ডেটা Employee ক্লাসে ম্যাপ করা হয়েছে।

Stored Procedure এবং Native SQL ব্যবহার করার নিয়মাবলী

1. Parameterization (প্যারামিটারাইজেশন): SQL ইনজেকশন প্রতিরোধ করতে, SQL কুয়েরি বা স্টোরড প্রোসিজারের প্যারামিটারাইজেশন ব্যবহার করুন। NHibernate এ SetParameter() পদ্ধতি ব্যবহার করে প্যারামিটার পাস করতে হবে।
2. Result Mapping (ফলাফল ম্যাপিং): AddEntity() বা AddScalar() পদ্ধতি ব্যবহার করে কুয়েরির ফলাফল সঠিক ডোমেইন মডেলে ম্যাপ করুন।
3. Transaction Management (ট্রানজেকশন ব্যবস্থাপনা): স্টোরড প্রোসিজার বা কাস্টম SQL কুয়েরি ব্যবহার করার সময় ট্রানজেকশন ম্যানেজমেন্ট সঠিকভাবে পরিচালনা করতে হবে, যাতে ডেটার একযোগিতা বজায় থাকে।
4. Error Handling (ত্রুটি হ্যান্ডলিং): স্টোরড প্রোসিজার বা Native SQL এর মাধ্যমে যদি কোনো ত্রুটি ঘটে, তবে সেগুলো সঠিকভাবে হ্যান্ডল করতে হবে এবং প্রয়োজনে ডেটাবেস লগিং এবং এক্সেপশন হ্যান্ডলিং ব্যবহার করতে হবে।

স্টোরড প্রোসিজার এবং নেটিভ SQL এর জন্য পরামর্শ

• স্টোরড প্রোসিজার ব্যবহার করুন যখন:
  • পুনরাবৃত্তি কার্যক্রমগুলির জন্য, যেমন ডেটা ইনসার্ট, আপডেট বা ডিলিট।
  • জটিল কুয়েরি বা লজিক যা বারবার ব্যবহৃত হয়, এর জন্য।
  • ডেটাবেস স্তরে লজিক কমপ্লেক্সিটি রাখতে চান।
• নেটিভ SQL ব্যবহার করুন যখন:
  • ORM কুয়েরি সক্ষমতা সীমিত হয়ে যায়, যেমন জটিল JOINs বা সাব-কুয়েরি।
  • পারফরম্যান্সের জন্য কাস্টম SQL কুয়েরি ব্যবহার করতে হয়।

সারাংশ

Stored Procedure এবং Native SQL LINQ বা ORM ভিত্তিক অ্যাপ্লিকেশনে কার্যকারিতা এবং পারফরম্যান্স বৃদ্ধি করতে ব্যবহার করা যায়। NHibernate এ, আপনি Stored Procedure এবং Native SQL মাধ্যমে ডেটাবেসের সাথে আরও কার্যকরভাবে ইন্টারঅ্যাক্ট করতে পারেন। তবে, এগুলোর ব্যবহারে সতর্কতা অবলম্বন করা উচিত যাতে SQL ইনজেকশন বা অন্যান্য সুরক্ষা সমস্যা থেকে দূরে থাকা যায়।

Stored Procedure হলো একটি প্রি-কম্পাইলড SQL কোড ব্লক, যা ডেটাবেসের ভিতরে সংরক্ষিত থাকে এবং ডেটাবেস অ্যাপ্লিকেশন থেকে পুনরায় ব্যবহার করা যায়। NHibernate ব্যবহার করে Stored Procedure এর মাধ্যমে কাস্টম SQL কোড চালানো সম্ভব। এটি ডেটাবেস অপারেশনগুলোকে আরও দ্রুত এবং সুরক্ষিত করতে সাহায্য করে, কারণ স্টোরড প্রোসিজারগুলো একবার তৈরি হওয়ার পর বারবার ব্যবহার করা যায় এবং ডেটাবেস সার্ভারের মধ্যে তা প্রি-কাম্পাইলড থাকে।

Stored Procedure ব্যবহার করা

NHibernate এ Stored Procedure ব্যবহার করার জন্য আপনি ISession এর মাধ্যমে সোজা SQL কোড এক্সিকিউট করতে পারেন অথবা Named Query হিসেবে স্টোরড প্রোসিডিউর ব্যবহার করতে পারেন। এখানে উভয়ের উদাহরণ দেওয়া হলো:

1. Direct Stored Procedure Execution

আপনি ISession এর মাধ্যমে সরাসরি Stored Procedure কল করতে পারেন।

উদাহরণস্বরূপ, একটি Stored Procedure যার নাম GetEmployeeById আছে, যা একটি নির্দিষ্ট EmployeeID দিয়ে ডেটা রিটার্ন করে:

CREATE PROCEDURE GetEmployeeById(@EmployeeID INT)
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID
END

এখন, আপনি এই স্টোরড প্রোসিডিউরটি NHibernate ব্যবহার করে কল করতে পারেন:

using (var session = sessionFactory.OpenSession())
{
    var sqlQuery = session.CreateSQLQuery("EXEC GetEmployeeById :employeeId")
                          .SetParameter("employeeId", 1)
                          .AddEntity(typeof(Employee));

    var employees = sqlQuery.List<Employee>();
}

এখানে:

• CreateSQLQuery ব্যবহার করে স্টোরড প্রোসিডিউর GetEmployeeById এক্সিকিউট করা হয়েছে।
• .SetParameter("employeeId", 1) এর মাধ্যমে প্রোসিডিউরের ইনপুট প্যারামিটার প্রদান করা হয়েছে।
• .AddEntity(typeof(Employee)) দিয়ে স্টোরড প্রোসিডিউর থেকে রিটার্ন হওয়া ডেটাকে Employee অবজেক্টে ম্যাপ করা হয়েছে।

2. Named Query ব্যবহার করে Stored Procedure কল করা

NHibernate এ আপনি Named Query ব্যবহার করে স্টোরড প্রোসিডিউর কল করতে পারেন। Named Query আপনার HQL বা SQL কোডকে আগেই ডিফাইন করে রাখে এবং পরে তার মাধ্যমে কুয়েরি বা স্টোরড প্রোসিডিউর কল করা যায়।

প্রথমে, hibernate.cfg.xml ফাইলে Named Query ডিফাইন করা হবে:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <sql-query name="GetEmployeeById">
        <![CDATA[
            EXEC GetEmployeeById :employeeId
        ]]>
    </sql-query>
</hibernate-mapping>

এখানে, GetEmployeeById নামক SQL Query হিসেবে স্টোরড প্রোসিডিউরটি ডিফাইন করা হয়েছে।

এখন, C# কোডের মাধ্যমে এই Named Query কল করা যাবে:

using (var session = sessionFactory.OpenSession())
{
    var query = session.GetNamedQuery("GetEmployeeById")
                       .SetParameter("employeeId", 1)
                       .List<Employee>();
}

এখানে:

• GetNamedQuery("GetEmployeeById") দিয়ে Named Query কল করা হয়েছে, যা আমাদের আগে hibernate.cfg.xml ফাইলে ডিফাইন করা ছিল।
• .SetParameter("employeeId", 1) এর মাধ্যমে প্যারামিটার পাস করা হয়েছে এবং .List<Employee>() ব্যবহার করে রিটার্ন হওয়া ডেটা Employee অবজেক্টে ম্যাপ করা হয়েছে।

3. Stored Procedure Configuration

NHibernate এ স্টোরড প্রোসিডিউরের কনফিগারেশন সাধারণত SQL কোডের মাধ্যমে পরিচালিত হয়, যেমন উপরে দেখানো হয়েছে। তবে, কিছু কেসে আপনি NHibernate Configuration ফাইলে স্টোরড প্রোসিডিউরের নাম এবং প্যারামিটার ডিফাইন করতে পারেন।

এখানে, hibernate.cfg.xml ফাইলের মাধ্যমে Named Query এবং Stored Procedure কনফিগারেশন:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <sql-query name="GetEmployeeById">
        <![CDATA[
            EXEC GetEmployeeById :employeeId
        ]]>
    </sql-query>
</hibernate-mapping>

এটি একইভাবে কাজ করে যেমন উপরে Named Query এ ব্যবহার করা হয়েছে। আপনি একইভাবে Stored Procedure কনফিগার করে কোডের মাধ্যমে কুয়েরি চালাতে পারবেন।

4. Transaction Management with Stored Procedures

স্টোরড প্রোসিডিউরের সাথে কাজ করার সময় ট্রানজেকশন ব্যবস্থাপনা অত্যন্ত গুরুত্বপূর্ণ। NHibernate এ ট্রানজেকশন ব্যবস্থাপনা session.BeginTransaction() এবং session.GetTransaction() এর মাধ্যমে করা হয়।

এখানে একটি উদাহরণ দেওয়া হলো যেখানে স্টোরড প্রোসিডিউর ব্যবহারের পর ট্রানজেকশন পরিচালনা করা হয়েছে:

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        try
        {
            var sqlQuery = session.CreateSQLQuery("EXEC GetEmployeeById :employeeId")
                                  .SetParameter("employeeId", 1)
                                  .AddEntity(typeof(Employee));

            var employees = sqlQuery.List<Employee>();
            transaction.Commit();  // Commit the transaction
        }
        catch (Exception)
        {
            transaction.Rollback();  // Rollback in case of error
            throw;
        }
    }
}

এখানে:

• transaction.Commit() এর মাধ্যমে টランজেকশন কমিট করা হয়েছে।
• transaction.Rollback() যদি কোনো ত্রুটি ঘটে তবে টランজেকশন রোলব্যাক করা হয়েছে।

সারাংশ

• Stored Procedures ব্যবহার করে আপনি NHibernate এর মাধ্যমে কাস্টম SQL কোড চালাতে পারেন, যা ডেটাবেসের পারফরম্যান্স বৃদ্ধি এবং নিরাপত্তা নিশ্চিত করতে সাহায্য করে।
• আপনি ISession বা Named Query ব্যবহার করে স্টোরড প্রোসিডিউর কল করতে পারেন।
• ট্রানজেকশন ব্যবস্থাপনা নিশ্চিত করে যে স্টোরড প্রোসিডিউর এক্সিকিউশন সঠিকভাবে সম্পন্ন হচ্ছে এবং ডেটা ত্রুটি ছাড়া ডেটাবেসে সেভ হচ্ছে।

Stored Procedures হল পূর্বনির্ধারিত SQL কোডের ব্লক যা ডেটাবেসে সংরক্ষিত থাকে এবং যেগুলি সাধারণত ডেটাবেস অপারেশন (যেমন INSERT, UPDATE, DELETE, SELECT) সম্পাদন করার জন্য ব্যবহার করা হয়। NHibernate এ Stored Procedures ব্যবহার করে ডেটাবেসের সাথে কাজ করার জন্য Entity Mapping করতে হলে কিছু কনফিগারেশন ও কৌশল অনুসরণ করতে হয়।

NHibernate দ্বারা Stored Procedure থেকে Entity Mapping করার জন্য দুইটি প্রধান পদ্ধতি রয়েছে:

1. Native SQL Queries ব্যবহার করা
2. Stored Procedure Call এর মাধ্যমে Entity Return করা

1. Native SQL Queries ব্যবহার করে Stored Procedure থেকে Entity Mapping করা

Native SQL Queries ব্যবহার করে আপনি সরাসরি ডেটাবেসে থাকা Stored Procedure কল করতে পারেন এবং তার ফলাফলকে NHibernate Entity তে ম্যাপ করতে পারেন।

Example:

ধরা যাক, আমাদের একটি Employee টেবিল আছে এবং একটি Stored Procedure আছে যা সমস্ত Employee রেকর্ড ফেচ করবে।

Stored Procedure (SQL):

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT EmployeeID, Name, Position FROM Employees
END

এখন, NHibernate ব্যবহার করে এই Stored Procedure থেকে ডেটা লোড করব।

1.1 Entity Class

public class Employee
{
    public virtual int EmployeeId { get; set; }
    public virtual string Name { get; set; }
    public virtual string Position { get; set; }
}

1.2 Native SQL Query দিয়ে Stored Procedure কল করা

using (var session = sessionFactory.OpenSession())
{
    var query = session.CreateSQLQuery("EXEC GetAllEmployees")
                       .AddEntity(typeof(Employee));  // Mapping the result to Employee entity
    var employees = query.List<Employee>();
    
    foreach(var employee in employees)
    {
        Console.WriteLine($"ID: {employee.EmployeeId}, Name: {employee.Name}, Position: {employee.Position}");
    }
}

এখানে:

• CreateSQLQuery মেথডের মাধ্যমে আমরা Native SQL কল করছি যা Stored Procedure GetAllEmployees এর এক্সিকিউশন করবে।
• .AddEntity(typeof(Employee)) মেথডটি নিশ্চিত করে যে, ফলাফলগুলি Employee Entity-তে ম্যাপ করা হবে।
• .List<Employee>() মেথডের মাধ্যমে, আমরা সেই ফলাফলকে Employee অবজেক্টের তালিকায় পরিণত করছি।

2. Stored Procedure Call দিয়ে Entity Return করা

এছাড়া, আপনি NHibernate-এ Stored Procedure কল করে Entity রিটার্ন করতে পারেন, যেখানে Stored Procedure নিজেই ডেটা রিটার্ন করবে এবং সে ডেটা একটি IList তে রিটার্ন হবে।

Example:

ধরা যাক, একটি Stored Procedure আছে যা নির্দিষ্ট EmployeeId এর উপর ভিত্তি করে একজন Employee রেকর্ড রিটার্ন করবে।

Stored Procedure (SQL):

CREATE PROCEDURE GetEmployeeById @EmployeeId INT
AS
BEGIN
    SELECT EmployeeID, Name, Position FROM Employees WHERE EmployeeID = @EmployeeId
END

2.1 Entity Class (যেমন আগে ছিল)

public class Employee
{
    public virtual int EmployeeId { get; set; }
    public virtual string Name { get; set; }
    public virtual string Position { get; set; }
}

2.2 Stored Procedure Call দিয়ে Entity রিটার্ন করা

using (var session = sessionFactory.OpenSession())
{
    var query = session.GetNamedQuery("GetEmployeeById")
                       .SetParameter("EmployeeId", 1); // Pass the parameter for the procedure
                       
    var employee = query.UniqueResult<Employee>();  // Getting a single result
    if (employee != null)
    {
        Console.WriteLine($"ID: {employee.EmployeeId}, Name: {employee.Name}, Position: {employee.Position}");
    }
}

এখানে:

• Stored Procedure কল করার জন্য GetNamedQuery ব্যবহার করা হয়েছে। এটি একটি Named SQL Query কল করে এবং আমরা SetParameter মেথড দিয়ে ইনপুট প্যারামিটার প্রদান করেছি।
• .UniqueResult<Employee>() মেথডটি একক Employee অবজেক্ট রিটার্ন করে (যেহেতু আমরা একটি নির্দিষ্ট EmployeeId অনুসন্ধান করছি)।

3. Fluent NHibernate ব্যবহার করে Stored Procedure Mapping

যদি আপনি Fluent NHibernate ব্যবহার করতে চান, তবে আপনাকে কিছুটা ভিন্ন কনফিগারেশন করতে হবে।

3.1 Fluent Configuration Example:

public class EmployeeMap : ClassMap<Employee>
{
    public EmployeeMap()
    {
        Table("Employees");
        Id(x => x.EmployeeId);
        Map(x => x.Name);
        Map(x => x.Position);
    }
}

public class StoredProcedureExample
{
    public void GetEmployees()
    {
        using (var session = sessionFactory.OpenSession())
        {
            var query = session.CreateSQLQuery("EXEC GetAllEmployees")
                               .AddEntity<Employee>();  // Map result to Employee entity
            var employees = query.List<Employee>();

            foreach(var employee in employees)
            {
                Console.WriteLine($"ID: {employee.EmployeeId}, Name: {employee.Name}, Position: {employee.Position}");
            }
        }
    }
}

4. Stored Procedure Return Value Map করা

যদি Stored Procedure একাধিক কলাম বা রিটার্ন ভ্যালু ফেরত দেয় এবং আপনি সেই ভ্যালুগুলোকে একাধিক Entity বা Value Types এ ম্যাপ করতে চান, তখন NHibernate এ .AddScalar() ব্যবহার করে সেই মানগুলো ম্যাপ করতে পারেন।

Example:

using (var session = sessionFactory.OpenSession())
{
    var query = session.CreateSQLQuery("EXEC GetEmployeeDetails @EmployeeId")
                       .AddScalar("EmployeeId", NHibernate.Util.Int32)
                       .AddScalar("Name", NHibernate.Util.String)
                       .AddScalar("Position", NHibernate.Util.String);

    var result = query.SetParameter("EmployeeId", 1).List<object[]>();
    foreach (var record in result)
    {
        var employeeId = (int)record[0];
        var name = (string)record[1];
        var position = (string)record[2];

        Console.WriteLine($"ID: {employeeId}, Name: {name}, Position: {position}");
    }
}

এখানে:

• .AddScalar() মেথডটি ব্যবহার করে আমরা প্রত্যেক কলামকে সঠিক টাইপে ম্যাপ করছি।
• List<object[]> এর মাধ্যমে আমরা Stored Procedure থেকে আসা ডেটাকে অ্যারে হিসেবে সংগ্রহ করছি এবং তারপরে সেই ডেটা দিয়ে প্রাসঙ্গিক কাজ করছি।

উপসংহার

NHibernate এ Stored Procedure থেকে Entity Mapping করার মাধ্যমে, আপনি ডেটাবেসের অভ্যন্তরে থাকা জটিল লজিক এবং কোয়েরিগুলি আপনার অ্যাপ্লিকেশনে সহজভাবে ব্যবহৃত করতে পারেন। এটা পারফরম্যান্স উন্নত করতে সহায়তা করে, কারণ Stored Procedure সাধারণত ডেটাবেসে দ্রুত এক্সিকিউট হয় এবং এটি ডেটাবেস সার্ভারে লজিক প্রক্রিয়াজাত করে।

NHibernate-এর একটি সুবিধা হলো Native SQL ব্যবহারের মাধ্যমে আপনি সরাসরি SQL কুয়েরি চালাতে পারেন, যা NHibernate-এর HQL (Hibernate Query Language) অথবা Criteria API এর তুলনায় অনেক বেশি শক্তিশালী এবং নমনীয়। যখন আপনার জটিল কুয়েরি বা SQL বৈশিষ্ট্য (যেমন, মাল্টি-টেবিল জয়েন, গ্রুপ বাই, অগ্রাধিকার শর্ত) প্রয়োগ করতে হয়, তখন Native SQL একটি আদর্শ সমাধান হতে পারে।

Native SQL কী?

Native SQL হল সেই SQL কুয়েরি যা আপনি সরাসরি ডেটাবেসের সাথে কাজ করার জন্য ব্যবহার করেন, যেটি সাধারণত HQL বা Criteria API এর সাথে তুলনা করা হয় না। Native SQL এর মাধ্যমে আপনি সম্পূর্ণ SQL ভাষার ক্ষমতা ব্যবহার করতে পারেন, তবে এর জন্য আপনার টেবিল এবং কলাম নাম সঠিকভাবে উল্লেখ করতে হবে, কারণ এটি অবজেক্ট মডেল (যেমন, ক্লাস এবং প্রপার্টি) এর পরিবর্তে ডেটাবেস টেবিল এবং কলামগুলির উপর কাজ করে।

Native SQL কুয়েরি Execute করার পদ্ধতি

NHibernate-এ Native SQL ব্যবহার করে কুয়েরি করার জন্য ISession অবজেক্টের CreateSQLQuery মেথড ব্যবহার করা হয়। এই মেথডটি আপনাকে ডেটাবেসের SQL কুয়েরি সরাসরি চালানোর সুযোগ দেয়। এছাড়াও, এর মাধ্যমে রেজাল্ট সেট (result set) অবজেক্ট মডেল অথবা সিম্পল টেবিল কোলাম ভ্যালু হিসেবে ফেরত পেতে পারেন।

1. Native SQL Query Execute করা (কেবল SQL Query)

এখানে একটি সিম্পল উদাহরণ দেওয়া হলো যেখানে একটি Native SQL কুয়েরি ব্যবহার করে Employee টেবিলের সব ডেটা পুনরুদ্ধার করা হবে:

using (ISession session = sessionFactory.OpenSession())
{
    // Native SQL কুয়েরি তৈরি
    var query = session.CreateSQLQuery("SELECT * FROM Employee");

    // Query Execute করা এবং Result Set পাওয়া
    var result = query.List<object[]>();  // Return type: object[] (rows)

    // ফলাফল প্রসেস করা
    foreach (var row in result)
    {
        Console.WriteLine($"Id: {row[0]}, Name: {row[1]}, Age: {row[2]}");
    }
}

এখানে List<object[]> ব্যবহার করা হয়েছে, কারণ SQL কুয়েরি সাধারণত ডেটাবেস থেকে রেকর্ডের একটি অ্যারে (array) ফেরত দেয়। প্রতিটি object[] অ্যারের একটি রেকর্ডের ভ্যালু ধারণ করে (প্রথম কলাম, দ্বিতীয় কলাম ইত্যাদি)।

2. Entity Class ব্যবহার করে Native SQL Execute করা

ধরুন, আপনি Employee ক্লাসের সব ডেটা পুনরুদ্ধার করতে চান এবং এই ডেটা Employee অবজেক্ট হিসেবে ফেরত পেতে চান। এ ক্ষেত্রে, Native SQL ব্যবহার করে Result Transformer সেট করতে হবে যাতে রেজাল্টগুলো Employee অবজেক্টে ম্যাপ করা যায়।

using (ISession session = sessionFactory.OpenSession())
{
    // Native SQL কুয়েরি তৈরি, যেখানে SQL থেকে ভ্যালু পড়া হবে
    var query = session.CreateSQLQuery("SELECT Id, Name, Age FROM Employee");

    // Result Transformer ব্যবহার করে রেজাল্টকে Entity Class এ ম্যাপ করা
    query.SetResultTransformer(Transformers.AliasToBean<Employee>());

    // কুয়েরি Execute করা এবং Employee অবজেক্টস এর লিস্ট পাওয়া
    var employees = query.List<Employee>();

    // ফলাফল প্রসেস করা
    foreach (var employee in employees)
    {
        Console.WriteLine($"Id: {employee.Id}, Name: {employee.Name}, Age: {employee.Age}");
    }
}

এখানে SetResultTransformer ব্যবহার করা হয়েছে, যা SQL কুয়েরির রেজাল্টগুলো Employee অবজেক্টে রূপান্তরিত করে। Transformers.AliasToBean<Employee>() নিশ্চিত করে যে SQL এর কলামগুলোকে সঠিকভাবে Employee ক্লাসের প্রপার্টিতে ম্যাপ করা হবে।

3. Parameters সহ Native SQL Execute করা

যখন SQL কুয়েরিতে প্যারামিটার ব্যবহার করতে হয়, তখন SetParameter বা SetParameterList মেথড ব্যবহার করা হয়। উদাহরণস্বরূপ, এখানে Employee টেবিলের Age এর উপর ভিত্তি করে কিছু কুয়েরি তৈরি করা হচ্ছে:

using (ISession session = sessionFactory.OpenSession())
{
    // Native SQL কুয়েরি তৈরি, যেখানে :age প্যারামিটার ব্যবহার করা হয়েছে
    var query = session.CreateSQLQuery("SELECT Id, Name, Age FROM Employee WHERE Age > :age");

    // প্যারামিটার সেট করা
    query.SetParameter("age", 30);

    // রেজাল্ট প্রসেস করা এবং Employee অবজেক্টে ম্যাপ করা
    query.SetResultTransformer(Transformers.AliasToBean<Employee>());
    var employees = query.List<Employee>();

    foreach (var employee in employees)
    {
        Console.WriteLine($"Id: {employee.Id}, Name: {employee.Name}, Age: {employee.Age}");
    }
}

এখানে, :age হল প্যারামিটার, যেটি SetParameter মেথড দিয়ে ডাইনামিকভাবে প্রদান করা হয়েছে।

4. Complex Join Query Execute করা

ধরা যাক, Employee এবং Department টেবিল দুটি সংযুক্ত করে একটি জটিল কুয়েরি তৈরি করতে চান। Native SQL এর মাধ্যমে এটি সহজেই করা যেতে পারে।

using (ISession session = sessionFactory.OpenSession())
{
    // Native SQL কুয়েরি তৈরি, যেখানে Employee এবং Department টেবিল জয়েন করা হয়েছে
    var query = session.CreateSQLQuery("SELECT e.Id, e.Name, e.Age, d.Name AS DepartmentName FROM Employee e JOIN Department d ON e.DepartmentId = d.Id");

    // Result Transformer ব্যবহার করে রেজাল্টকে Entity Class এ ম্যাপ করা
    query.SetResultTransformer(Transformers.AliasToBean<EmployeeWithDepartment>());

    var employees = query.List<EmployeeWithDepartment>();

    foreach (var employee in employees)
    {
        Console.WriteLine($"Id: {employee.Id}, Name: {employee.Name}, Age: {employee.Age}, Department: {employee.DepartmentName}");
    }
}

এখানে EmployeeWithDepartment হল একটি নতুন ক্লাস যা Employee এবং Department এর তথ্য ধারণ করে। আপনি এই ধরনের কমপ্লেক্স কুয়েরি ব্যবহার করে বিভিন্ন টেবিলের ডেটা একত্রিত করতে পারেন।

Native SQL এর সুবিধা এবং অসুবিধা

সুবিধা:

• SQL এর সম্পূর্ণ ক্ষমতা: Native SQL দিয়ে আপনি ডেটাবেসের সব বৈশিষ্ট্য (যেমন, জয়েন, সাব-কুয়েরি, অ্যাগ্রিগেট ফাংশন ইত্যাদি) ব্যবহার করতে পারেন।
• ফাস্ট এক্সিকিউশন: জটিল কুয়েরির ক্ষেত্রে Native SQL অনেক দ্রুত কাজ করতে পারে, কারণ এতে ORM-এর অতিরিক্ত লজিক ব্যবহার করা হয় না।
• ফ্লেক্সিবিলিটি: কখনো কখনো HQL বা Criteria API এর সীমাবদ্ধতা থেকে বের হতে Native SQL ব্যবহার করা হয়।

অসুবিধা:

• প্রোগ্রামিং পরিপূর্ণতা: Native SQL তে ডেটাবেসের টেবিল এবং কলাম নাম উল্লেখ করতে হয়, ফলে অবজেক্ট মডেল এবং ডেটাবেস স্কিমার মধ্যে সিঙ্ক্রোনাইজেশন সমস্যা হতে পারে।
• পোর্টেবিলিটি সমস্যা: SQL ডেটাবেস ভেন্ডরের নির্ভরতা বাড়ে, কারণ Native SQL এর কুয়েরি ডাটাবেস নির্দিষ্ট হতে পারে।

Native SQL এর মাধ্যমে জটিল কুয়েরি এক্সিকিউট করার ফলে আপনি অনেক বেশি নিয়ন্ত্রণ এবং নমনীয়তা পেতে পারেন। তবে, এটা ব্যবহারের সময় সঠিক কনফিগারেশন এবং ডেটাবেস স্কিমার সাথে সিঙ্ক্রোনাইজ থাকা জরুরি।

NHibernate তে, Native SQL ব্যবহার করে আপনি সরাসরি SQL কুয়েরি চালাতে পারেন। তবে কখনও কখনও আপনাকে SQL কুয়েরির ফলাফলকে NHibernate এর ডোমেন অবজেক্টে ম্যাপ করতে হতে পারে। এই কাজটি করার জন্য, NHibernate এর ResultTransformer ব্যবহার করা হয়। ResultTransformer হল একটি টুল যা SQL কুয়েরির রিজাল্টকে হাই-লেভেল অবজেক্টে বা কাস্টম অবজেক্টে রূপান্তরিত করতে সাহায্য করে।

Native SQL কী?

Native SQL হল SQL কুয়েরি যা সরাসরি ডেটাবেসের ভাষায় লেখা হয়, যেমন SQL Server, MySQL, PostgreSQL ইত্যাদিতে ব্যবহার করা হয়। NHibernate এ Native SQL ব্যবহার করে, আপনি সাধারণ SQL কুয়েরি লিখতে পারেন যা NHibernate এর হাই-লেভেল (object-relational) ফিচারগুলোর বাইরে চলে যায়।

Result Transformer কী?

ResultTransformer একটি ইন্টারফেস যা Native SQL কুয়েরির ফলাফলকে আপনার নির্দিষ্ট অবজেক্টে রূপান্তরিত করতে ব্যবহৃত হয়। সাধারণত, SQL কুয়েরির রেজাল্ট গুলো object[] বা IDataReader এর আকারে আসে। ResultTransformer এর মাধ্যমে এই রেজাল্টগুলোকে NHibernate এর ডোমেন অবজেক্ট বা কাস্টম অবজেক্টে রূপান্তর করা হয়।

ResultTransformer ব্যবহার করার প্রক্রিয়া

ধরা যাক, আপনি একটি Employee এবং Department টেবিলের সাথে কাজ করছেন এবং আপনি SQL কুয়েরির মাধ্যমে উভয়ের তথ্য একত্রে পেতে চান।

1. Employee এবং Department ক্লাস উদাহরণ

public class Employee
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual int Age { get; set; }
    public virtual string Department { get; set; }
}

public class Department
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

2. Native SQL কুয়েরি উদাহরণ

এখন, আপনি একটি Native SQL কুয়েরি চালাতে চান যা Employee এবং Department এর ডেটা একসাথে ফেরত দিবে।

using NHibernate;
using NHibernate.Transform;
using System;
using System.Collections.Generic;

public class EmployeeService
{
    private readonly ISession session;

    public EmployeeService(ISession session)
    {
        this.session = session;
    }

    public void GetEmployeesWithDepartments()
    {
        string sql = @"
            SELECT e.Id AS EmployeeId, e.Name AS EmployeeName, e.Age, d.Name AS DepartmentName
            FROM Employee e
            JOIN Department d ON e.Department = d.Name";
        
        var query = session.CreateSQLQuery(sql);
        
        // ResultTransformer ব্যবহার করে SQL রেজাল্টকে কাস্টম অবজেক্টে রূপান্তরিত করা
        query.SetResultTransformer(Transformers.AliasToBean<EmployeeDepartmentDTO>());

        // রেজাল্ট ফিরিয়ে আনা
        var result = query.List<EmployeeDepartmentDTO>();

        foreach (var item in result)
        {
            Console.WriteLine($"Employee: {item.EmployeeName}, Department: {item.DepartmentName}, Age: {item.Age}");
        }
    }
}

এখানে:

• CreateSQLQuery ব্যবহার করে একটি Native SQL কুয়েরি চালানো হয়েছে।
• SetResultTransformer দিয়ে, কুয়েরির রেজাল্টকে EmployeeDepartmentDTO অবজেক্টে রূপান্তর করা হয়েছে।

3. DTO (Data Transfer Object) ক্লাস

এখানে, আমরা একটি DTO (Data Transfer Object) তৈরি করেছি, যেটি SQL কুয়েরির ফলাফল ধারণ করবে।

public class EmployeeDepartmentDTO
{
    public virtual int EmployeeId { get; set; }
    public virtual string EmployeeName { get; set; }
    public virtual int Age { get; set; }
    public virtual string DepartmentName { get; set; }
}

4. ResultTransformer এর সুবিধা

ResultTransformer ব্যবহার করে আপনি সহজে Native SQL কুয়েরির ফলাফলকে কাস্টম অবজেক্টে রূপান্তর করতে পারেন। এটি বিভিন্ন সিচুয়েশনে উপকারী যেখানে আপনি কেবল নির্দিষ্ট ডেটা বা কমপ্লেক্স অবজেক্টের প্রপার্টি চান।

যেমন:

• AliasToBean: একটি DTO ক্লাসে SQL কুয়েরির রেজাল্ট ম্যাপ করতে ব্যবহার করা হয়।
• Transformers.AliasToEntityMap: এটি SQL কুয়েরির রেজাল্টকে কাস্টম মেপিং আকারে ফেরত দেয়, যেখানে কলাম নামগুলো ম্যাপ করা হয় একটি ডিকশনারি হিসেবে।

এছাড়া, আপনি আরও অনেক ধরনের ResultTransformer ব্যবহার করতে পারেন, যেমন:

• Transformers.AliasToBean: SQL কুয়েরির রেজাল্টকে DTO ক্লাসে ম্যাপ করে।
• Transformers.AliasToEntityMap: SQL কুয়েরির রেজাল্টকে একটি ডিকশনারিতে রূপান্তরিত করে, যেখানে কী হল কলাম নাম এবং মান হল কলাম এর ভ্যালু।

Native SQL এবং ResultTransformer এর উপকারিতা

• ফ্লেক্সিবিলিটি: Native SQL এর মাধ্যমে আপনি যেকোনো ধরনের কাস্টম কুয়েরি লিখতে পারেন এবং এর ফলাফলকে নিজস্ব ক্লাসে রূপান্তর করতে পারেন।
• পারফরম্যান্স: যখন আপনি ডেটাবেস থেকে অনেক জটিল ডেটা নিয়ে আসছেন, তখন ResultTransformer আপনার ডেটাকে সঠিকভাবে ম্যাপ করে দ্রুত আউটপুট পেতে সাহায্য করে।
• কাস্টম কুয়েরি: কিছু ক্ষেত্রে, NHibernate এর হাই-লেভেল কুয়েরি মেথড ব্যবহার করা সঠিক হতে পারে না, তখন Native SQL একটি কার্যকর পছন্দ।

সারাংশ

Native SQL এবং ResultTransformer ব্যবহার করে, NHibernate এর সাথে SQL কুয়েরি চালানো এবং কুয়েরির রেজাল্টকে কাস্টম অবজেক্টে রূপান্তর করা যায়। এটি ডেটাবেস কুয়েরি পরিচালনা করার একটি শক্তিশালী এবং ফ্লেক্সিবল পদ্ধতি, বিশেষ করে যখন আপনাকে জটিল কুয়েরি বা কাস্টম ডেটা লোড করতে হয়।