Microsoft Technologies Native SQL ব্যবহার করে Complex Query Execute করা গাইড ও নোট

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 এর মাধ্যমে জটিল কুয়েরি এক্সিকিউট করার ফলে আপনি অনেক বেশি নিয়ন্ত্রণ এবং নমনীয়তা পেতে পারেন। তবে, এটা ব্যবহারের সময় সঠিক কনফিগারেশন এবং ডেটাবেস স্কিমার সাথে সিঙ্ক্রোনাইজ থাকা জরুরি।