آموزش اتصال C# به پایگاه داده SQL Server: راهنمای کامل

اتصال برنامه‌های C# به پایگاه داده SQL Server یکی از اساسی‌ترین مهارت‌ها برای توسعه‌دهندگان نرم‌افزار است. این اتصال به برنامه‌های کاربردی شما امکان می‌دهد تا داده‌ها را ذخیره، بازیابی، به‌روزرسانی و حذف کنند، که این قابلیت ستون فقرات هر نرم‌افزار کاربردی پیچیده‌ای را تشکیل می‌دهد. از سیستم‌های مدیریت مشتری (CRM) و برنامه‌های حسابداری گرفته تا پلتفرم‌های تجارت الکترونیک و برنامه‌های سازمانی بزرگ، تقریباً تمامی راهکارهای نرم‌افزاری نیاز به تعامل با یک پایگاه داده قوی دارند. SQL Server مایکروسافت به دلیل قابلیت اطمینان، مقیاس‌پذیری و مجموعه‌ای غنی از ویژگی‌ها، انتخابی محبوب در میان کسب‌وکارها و توسعه‌دهندگان است.

این راهنمای جامع برای توسعه‌دهندگان C# طراحی شده است که به دنبال درک عمیق و کاربردی از چگونگی برقراری ارتباط مؤثر و ایمن با SQL Server هستند. ما از مفاهیم پایه‌ای و پیش‌نیازها شروع می‌کنیم و به تدریج به سمت تکنیک‌های پیشرفته‌تر مانند ADO.NET، Entity Framework Core و بهترین شیوه‌های امنیتی و عملکردی حرکت خواهیم کرد. هدف این است که شما را با دانش و ابزارهایی مجهز کنیم تا بتوانید راهکارهای پایگاه داده قدرتمند، مقیاس‌پذیر و قابل نگهداری را در برنامه‌های C# خود پیاده‌سازی کنید.

در طول این مقاله، ما به بررسی چندین روش اتصال و تعامل با SQL Server خواهیم پرداخت، از جمله استفاده مستقیم از ADO.NET برای کنترل دقیق بر عملیات داده، و همچنین بهره‌گیری از Object-Relational Mappers (ORMs) مانند Entity Framework Core برای افزایش بهره‌وری و کاهش کدنویسی تکراری. همچنین، اهمیت مدیریت خطا، امنیت و بهینه‌سازی عملکرد را در تمام مراحل کار با پایگاه داده برجسته خواهیم کرد. با پایان این راهنما، شما نه تنها قادر به اتصال C# به SQL Server خواهید بود، بلکه می‌توانید این کار را به شیوه‌ای حرفه‌ای، ایمن و کارآمد انجام دهید.

چرا اتصال C# به SQL Server اهمیت دارد؟

در دنیای توسعه نرم‌افزار، داده‌ها پادشاه هستند. بدون قابلیت ذخیره و بازیابی دائمی اطلاعات، اکثر برنامه‌ها نمی‌توانند فراتر از یک ابزار موقت عمل کنند. اتصال C# به SQL Server این قابلیت حیاتی را فراهم می‌کند و مزایای متعددی را به همراه دارد که آن را به یک مهارت ضروری تبدیل می‌کند:

• پایداری داده‌ها (Data Persistence): برنامه‌ها می‌توانند اطلاعات را به صورت دائمی ذخیره کنند، به طوری که حتی پس از بسته شدن برنامه یا خاموش شدن سیستم، داده‌ها حفظ می‌شوند. این برای هر برنامه‌ای که نیاز به حافظه و سابقه دارد، حیاتی است.
• مقیاس‌پذیری (Scalability): SQL Server یک پایگاه داده رابطه‌ای قدرتمند است که قادر به مدیریت حجم عظیمی از داده‌ها و تعداد زیادی از کاربران همزمان است. اتصال C# به آن، به برنامه‌های شما اجازه می‌دهد تا با رشد کسب‌وکار و افزایش نیازهای داده، به طور مؤثر مقیاس‌پذیر باشند.
• دسترسی چند کاربره (Multi-User Access): در محیط‌های سازمانی، چندین کاربر نیاز دارند به طور همزمان به داده‌ها دسترسی پیدا کنند و آن‌ها را تغییر دهند. SQL Server از مکانیزم‌های قفل‌گذاری و مدیریت تراکنش‌ها برای اطمینان از سازگاری و یکپارچگی داده‌ها در چنین سناریوهایی پشتیبانی می‌کند، و C# به شما امکان می‌دهد این قابلیت‌ها را از طریق کد خود مدیریت کنید.
• مدیریت داده‌های پیچیده (Complex Data Management): SQL Server ابزارهای پیشرفته‌ای برای سازماندهی، جستجو و تجزیه و تحلیل داده‌ها فراهم می‌کند. این شامل جداول، روابط، نماها (Views)، رویه‌های ذخیره‌شده (Stored Procedures)، توابع (Functions) و تریگرها (Triggers) است. C# به شما امکان می‌دهد تا با این ساختارها تعامل داشته باشید و از قابلیت‌های قدرتمند SQL Server بهره‌برداری کنید.
• امنیت (Security): SQL Server مکانیزم‌های امنیتی قوی برای کنترل دسترسی به داده‌ها ارائه می‌دهد. با اتصال ایمن از C#، می‌توانید اطمینان حاصل کنید که فقط کاربران مجاز به داده‌های حساس دسترسی دارند و از حملاتی مانند SQL Injection جلوگیری شود.
• یکپارچگی و سازگاری داده‌ها (Data Integrity and Consistency): پایگاه داده‌های رابطه‌ای مانند SQL Server قواعدی برای اطمینان از صحت و سازگاری داده‌ها (مانند کلیدهای اصلی و خارجی، قیدها) اعمال می‌کنند. C# به شما کمک می‌کند تا برنامه‌هایی بسازید که این قواعد را رعایت کرده و داده‌های قابل اعتماد را حفظ کنند.

موارد استفاده کلیدی:

• برنامه‌های سازمانی (Enterprise Applications): سیستم‌های ERP، CRM، مدیریت منابع انسانی و سایر برنامه‌های اصلی که ستون فقرات عملیات یک شرکت را تشکیل می‌دهند.
• توسعه وب (Web Development – ASP.NET): وب‌سایت‌ها و وب‌سرویس‌های پویا که نیاز به ذخیره و بازیابی اطلاعات کاربر، محصولات، سفارشات و غیره دارند. ASP.NET Core که بر پایه C# است، به طور گسترده‌ای با SQL Server استفاده می‌شود.
• برنامه‌های دسکتاپ (Desktop Applications): نرم‌افزارهای ویندوز فرمز (Windows Forms) یا WPF که نیاز به ذخیره‌سازی داده‌های محلی یا مرکزی دارند.
• تجزیه و تحلیل داده‌ها و هوش تجاری (Data Analytics & BI): برنامه‌هایی که داده‌ها را از SQL Server استخراج کرده، پردازش می‌کنند و گزارش‌ها یا داشبوردهای تحلیلی تولید می‌کنند.

در مجموع، توانایی اتصال و تعامل با SQL Server از طریق C# نه تنها به شما امکان می‌دهد برنامه‌هایی بسازید که داده‌ها را مدیریت کنند، بلکه برنامه‌هایی را خلق کنید که قوی، امن، مقیاس‌پذیر و در نهایت برای کاربران خود ارزشمند باشند.

پیش‌نیازها و ابزارهای لازم

قبل از اینکه بتوانیم کدنویسی برای اتصال C# به SQL Server را آغاز کنیم، لازم است که محیط توسعه خود را آماده کرده و ابزارهای مورد نیاز را نصب نماییم. همچنین، آشنایی با مفاهیم پایه‌ای برنامه‌نویسی C# و SQL برای درک بهتر مطالب پیش رو ضروری است.

نرم‌افزارها:

برای شروع کار، به نرم‌افزارهای زیر نیاز دارید:

• Visual Studio:
  • Visual Studio یک محیط توسعه یکپارچه (IDE) از مایکروسافت است که برای توسعه برنامه‌های C# ضروری است. می‌توانید نسخه Community را که رایگان است و تمام ویژگی‌های لازم برای توسعه فردی و تیم‌های کوچک را دارد، دانلود کنید.
  • نصب Workloads: هنگام نصب Visual Studio، مطمئن شوید که Workloads مربوط به “ASP.NET and web development” (اگر قصد توسعه وب دارید)، “Desktop development with .NET” (برای برنامه‌های دسکتاپ) و “Data storage and processing” را انتخاب کنید. این Workloads شامل SDKها و ابزارهای لازم برای کار با پایگاه داده هستند.
• SQL Server:
  • شما به یک نمونه از SQL Server نیاز دارید. گزینه‌های مختلفی وجود دارد:
    • SQL Server Express Edition: این نسخه رایگان و سبک است و برای توسعه، تست و برنامه‌های کوچک مناسب است.
    • SQL Server Developer Edition: این نسخه نیز رایگان است و شامل تمام ویژگی‌های نسخه Enterprise می‌شود، اما فقط برای محیط‌های توسعه و تست مجاز است، نه برای تولید.
    • SQL Server Standard/Enterprise Edition: این‌ها نسخه‌های تجاری هستند که برای محیط‌های تولیدی در مقیاس بزرگ استفاده می‌شوند.
  • برای اهداف آموزشی و توسعه، SQL Server Express یا Developer Edition کافی است.
• SQL Server Management Studio (SSMS):
  • SSMS یک ابزار قدرتمند مبتنی بر رابط کاربری گرافیکی (GUI) است که به شما امکان می‌دهد پایگاه داده‌های SQL Server خود را مدیریت، کوئری و بهینه‌سازی کنید. این ابزار برای ایجاد جداول، وارد کردن داده‌ها، اجرای کوئری‌ها و عیب‌یابی ضروری است. SSMS معمولاً به صورت جداگانه از SQL Server نصب می‌شود.

مفاهیم برنامه‌نویسی:

قبل از ورود به جزئیات اتصال، آشنایی با مفاهیم زیر به شما کمک می‌کند:

• مبانی C# و برنامه‌نویسی شیءگرا (OOP):
  • آشنایی با سینتکس C#، انواع داده‌ها، متغیرها، حلقه‌ها، شرط‌ها، توابع، کلاس‌ها، اشیاء و اصول OOP مانند کپسوله‌سازی (Encapsulation)، وراثت (Inheritance) و چندریختی (Polymorphism) ضروری است.
  • مفهوم مدیریت حافظه و Garbage Collection در .NET.
• مبانی SQL (Structured Query Language):
  • DDL (Data Definition Language): دستوراتی مانند CREATE TABLE، ALTER TABLE، DROP TABLE برای تعریف و تغییر ساختار پایگاه داده.
  • DML (Data Manipulation Language): دستوراتی مانند SELECT (بازیابی داده)، INSERT (افزودن داده)، UPDATE (به‌روزرسانی داده) و DELETE (حذف داده).
  • آشنایی با مفاهیم کلید اصلی (Primary Key)، کلید خارجی (Foreign Key)، ایندکس‌ها (Indexes) و روابط بین جداول (Relationships).
  • درک مفهوم JOINs برای ترکیب داده‌ها از چندین جدول.
• مفاهیم ADO.NET (ActiveX Data Objects .NET):
  • ADO.NET چارچوبی در .NET است که برای تعامل با پایگاه داده‌ها استفاده می‌شود. درک کلی از نقش اشیاء اصلی آن (مانند Connection، Command، DataReader، DataAdapter، DataSet) به شما در درک روش‌های اتصال کمک می‌کند.

آماده‌سازی پایگاه داده:

برای تست اتصالات، نیاز به یک پایگاه داده نمونه در SQL Server دارید. مراحل زیر را در SSMS دنبال کنید:

1. اتصال به SQL Server: SSMS را باز کنید و به نمونه SQL Server خود متصل شوید (معمولاً (localdb)\MSSQLLocalDB یا SQLEXPRESS برای نسخه‌های اکسپرس).
2. ایجاد یک پایگاه داده جدید:

   در Object Explorer، روی پوشه “Databases” راست‌کلیک کرده و “New Database…” را انتخاب کنید. نام پایگاه داده را CompanyDB بگذارید و OK کنید.

   CREATE DATABASE CompanyDB;

3. ایجاد یک جدول نمونه:

   کوئری جدیدی باز کنید (New Query) و پایگاه داده CompanyDB را انتخاب کنید. سپس کد زیر را اجرا کنید تا یک جدول Employees ایجاد شود:

   USE CompanyDB;
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY IDENTITY(1,1),
       FirstName NVARCHAR(50) NOT NULL,
       LastName NVARCHAR(50) NOT NULL,
       Department NVARCHAR(50),
       Salary DECIMAL(18, 2)
   );

4. وارد کردن داده‌های نمونه:

   برای اینکه داده‌هایی برای کار داشته باشید، چند ردیف به جدول Employees اضافه کنید:

   USE CompanyDB;
   INSERT INTO Employees (FirstName, LastName, Department, Salary) VALUES
   ('علیرضا', 'محمدی', 'فروش', 65000.00),
   ('سارا', 'احمدی', 'بازاریابی', 72000.50),
   ('رضا', 'کریمی', 'توسعه نرم‌افزار', 88000.75),
   ('مریم', 'حسینی', 'منابع انسانی', 60000.00),
   ('امیر', 'نوری', 'توسعه نرم‌افزار', 95000.00);

5. تأیید داده‌ها:

   برای اطمینان از اینکه جدول و داده‌ها به درستی ایجاد شده‌اند، کوئری زیر را اجرا کنید:

   USE CompanyDB;
   SELECT * FROM Employees;

با آماده‌سازی این ابزارها و درک این مفاهیم، شما آماده ورود به دنیای اتصال C# به SQL Server خواهید بود.

متدهای اصلی اتصال: ADO.NET Foundations

ADO.NET (ActiveX Data Objects .NET) مجموعه‌ای از کلاس‌ها، رابط‌ها و اشیاء است که به توسعه‌دهندگان دات‌نت امکان می‌دهد با پایگاه داده‌های رابطه‌ای و غیررابطه‌ای تعامل داشته باشند. ADO.NET زیربنای تمام روش‌های ارتباطی C# با SQL Server است، حتی اگر از ORM‌هایی مانند Entity Framework استفاده کنید، در نهایت آن‌ها نیز از ADO.NET در پشت صحنه بهره می‌برند.

مفهوم ADO.NET:

ADO.NET از دو جزء اصلی تشکیل شده است:

1. ارائه‌دهندگان داده .NET ( .NET Data Providers): این‌ها مجموعه‌ای از کلاس‌ها هستند که برای اتصال به یک منبع داده خاص (مانند SQL Server، Oracle، MySQL) و اجرای دستورات بر روی آن طراحی شده‌اند. هر ارائه‌دهنده داده مجموعه‌ای از کلاس‌های اصلی را فراهم می‌کند:
   • Connection: برای برقراری و مدیریت اتصال به پایگاه داده.
   • Command: برای اجرای دستورات SQL یا Stored Procedures.
   • DataReader: یک جریان سریع و فقط خواندنی (forward-only, read-only) برای بازیابی داده‌ها.
   • DataAdapter: پلی بین DataSet و پایگاه داده برای پر کردن DataSet و به‌روزرسانی پایگاه داده.

   برای SQL Server، ما از ارائه‌دهنده داده SqlClient استفاده می‌کنیم که شامل کلاس‌هایی مانند SqlConnection، SqlCommand، SqlDataReader و SqlDataAdapter می‌شود.

2. اشیاء DataSet:
   • DataSet: یک کش داده در حافظه است که می‌تواند شامل چندین DataTable باشد. DataSet می‌تواند داده‌ها را به صورت قطع‌اتصال (disconnected) از پایگاه داده نگهداری و پردازش کند. این به معنای آن است که پس از پر شدن DataSet، نیازی به اتصال مداوم به پایگاه داده برای کار با داده‌ها نیست.
   • DataTable: نشان‌دهنده یک جدول منفرد در DataSet است.
   • DataRow و DataColumn: نشان‌دهنده سطرها و ستون‌های یک DataTable.
   • DataRelation: برای تعریف روابط بین DataTableها در یک DataSet.

معماری Connected و Disconnected:

• معماری Connected (متصل): در این رویکرد، برنامه برای انجام عملیات داده، اتصال خود را به پایگاه داده باز نگه می‌دارد. DataReader یک مثال از این معماری است؛ در حالی که داده‌ها خوانده می‌شوند، اتصال باز است. این روش برای عملیات سریع و فقط خواندنی مناسب است.
• معماری Disconnected (قطع‌اتصال): در این رویکرد، داده‌ها از پایگاه داده به یک DataSet در حافظه منتقل می‌شوند، اتصال بسته می‌شود و برنامه می‌تواند به صورت آفلاین با داده‌ها کار کند. پس از انجام تغییرات، اتصال مجدداً باز شده و تغییرات به پایگاه داده ارسال می‌شوند. DataSet/DataAdapter مثالی از این معماری هستند. این برای سناریوهایی مناسب است که نیاز به کار با مجموعه‌ای از داده‌ها برای مدت طولانی دارید.

Connection String (رشته اتصال):

Connection String مجموعه‌ای از پارامترهای پیکربندی است که به ارائه‌دهنده داده می‌گوید چگونه به پایگاه داده متصل شود. این رشته شامل اطلاعاتی مانند آدرس سرور، نام پایگاه داده، و اطلاعات احراز هویت است. دقت در ساخت و مدیریت Connection String حیاتی است.

اجزای اصلی Connection String:

• Server / Data Source: نام یا آدرس IP سرور SQL Server.
  • مثال برای سرور محلی: .، (local)، localhost، (localdb)\MSSQLLocalDB (برای LocalDB در Visual Studio).
  • مثال برای نمونه نام‌گذاری شده: MyServer\SQLEXPRESS.
  • مثال برای سرور ریموت: 192.168.1.100.
• Database / Initial Catalog: نام پایگاه داده‌ای که می‌خواهید به آن متصل شوید.
• احراز هویت (Authentication):
  • Integrated Security=True / Integrated Security=SSPI: احراز هویت ویندوز (Windows Authentication). برنامه با هویت کاربر ویندوز فعلی به SQL Server متصل می‌شود. این روش معمولاً ایمن‌تر و توصیه‌شده‌تر است.
  • User ID=YourUsername; Password=YourPassword;: احراز هویت SQL Server (SQL Server Authentication). شما نام کاربری و رمز عبور را مستقیماً در Connection String فراهم می‌کنید. این روش باید با احتیاط استفاده شود، زیرا رمز عبور در کد یا فایل پیکربندی ذخیره می‌شود.

مثال‌هایی از Connection String:

// 1. Windows Authentication (LocalDB - common in Visual Studio)
string connectionString1 = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=CompanyDB;Integrated Security=True;";

// 2. Windows Authentication (SQL Server Express)
string connectionString2 = "Data Source=.\\SQLEXPRESS;Initial Catalog=CompanyDB;Integrated Security=True;";

// 3. SQL Server Authentication (with username and password)
string connectionString3 = "Data Source=MyServer;Initial Catalog=CompanyDB;User ID=sa;Password=YourStrongPassword;";

// 4. Multiple Active Result Sets (MARS) - allows multiple default result sets on a single connection
string connectionString4 = "Data Source=MyServer;Initial Catalog=CompanyDB;Integrated Security=True;MultipleActiveResultSets=True;";

// 5. Connection Timeout
string connectionString5 = "Data Source=MyServer;Initial Catalog=CompanyDB;Integrated Security=True;Connect Timeout=30;"; // 30 seconds

بهترین روش‌ها برای ذخیره‌سازی Connection String:

هرگز Connection String را مستقیماً در کد خود هاردکد نکنید (به جز برای مثال‌های آموزشی). این یک خطر امنیتی بزرگ است.

• برای برنامه‌های دسکتاپ (Windows Forms/WPF):

  در فایل App.config پروژه خود ذخیره کنید. این فایل XML است و می‌توانید Connection String را در بخش <connectionStrings> قرار دهید.

  <?xml version="1.0" encoding="utf-8" ?>
  <configuration>
    <connectionStrings>
      <add name="CompanyDBConnection"
           connectionString="Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=CompanyDB;Integrated Security=True;"
           providerName="System.Data.SqlClient" />
    </connectionStrings>
  </configuration>
          

  برای دسترسی به آن در C#:

  string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;

  برای استفاده از ConfigurationManager، باید رفرنس به اسمبلی System.Configuration را به پروژه خود اضافه کنید.

• برای برنامه‌های وب (ASP.NET/ASP.NET Core):

  در فایل Web.config (برای ASP.NET Framework) یا appsettings.json (برای ASP.NET Core) ذخیره کنید. appsettings.json روش مدرن‌تر و توصیه‌شده‌تر است.

  appsettings.json (ASP.NET Core):

  {
    "ConnectionStrings": {
      "CompanyDBConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=CompanyDB;Integrated Security=True;"
    }
  }
          

  برای دسترسی به آن در ASP.NET Core، معمولاً از طریق Dependency Injection و رابط IConfiguration انجام می‌شود.

• محیط‌های تولیدی (Production Environments):

  در محیط‌های تولیدی، بهترین روش‌ها شامل استفاده از ابزارهای مدیریت راز (Secret Management) مانند Azure Key Vault، AWS Secrets Manager یا HashiCorp Vault است. این روش‌ها به شما امکان می‌دهند Connection Stringها و سایر اطلاعات حساس را به صورت ایمن خارج از کد و فایل‌های پیکربندی برنامه ذخیره کنید.

با درک این مبانی، آماده‌ایم تا به سراغ روش‌های عملی اتصال و تعامل با SQL Server در C# برویم.

روش اول: اتصال و اجرای کوئری با ADO.NET (Connected Architecture)

این روش، که به آن “معماری متصل” نیز گفته می‌شود، پایه و اساس تعامل با پایگاه داده در ADO.NET است. در این رویکرد، برنامه برای انجام عملیات داده، اتصال خود را به پایگاه داده باز نگه می‌دارد. این روش کنترل دقیق‌تری بر فرآیند ارتباط فراهم می‌کند و برای عملیات‌های سریع و کارآمد، به ویژه برای خواندن داده‌ها (مانند استفاده از SqlDataReader)، بسیار مناسب است.

باز کردن و بستن اتصال:

شیء SqlConnection مسئول برقراری و مدیریت اتصال به پایگاه داده SQL Server است. استفاده صحیح از این شیء برای جلوگیری از نشت منابع (resource leaks) بسیار مهم است.

ایجاد و باز کردن اتصال:

using System.Data.SqlClient;

public class ConnectedDataAccess
{
    private string connectionString;

    public ConnectedDataAccess(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public void TestConnection()
    {
        SqlConnection connection = null;
        try
        {
            connection = new SqlConnection(connectionString);
            connection.Open();
            Console.WriteLine("اتصال با موفقیت به پایگاه داده برقرار شد.");
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در اتصال به پایگاه داده: {ex.Message}");
        }
        finally
        {
            if (connection != null && connection.State == System.Data.ConnectionState.Open)
            {
                connection.Close();
                Console.WriteLine("اتصال بسته شد.");
            }
        }
    }
}

استفاده از using statement (توصیه شده):

بهترین روش برای مدیریت منابعی مانند SqlConnection که نیاز به آزادسازی دارند، استفاده از دستور using است. این دستور تضمین می‌کند که شیء Dispose() شود (و در نتیجه Close() شود)، حتی اگر خطایی رخ دهد.

using System.Data.SqlClient;
using System.Configuration; // برای App.config

public class ConnectedDataAccess
{
    private string connectionString;

    public ConnectedDataAccess()
    {
        // فرض می‌کنیم Connection String در App.config با نام "CompanyDBConnection" تعریف شده است.
        this.connectionString = ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;
    }

    public void TestConnectionUsingUsingStatement()
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                Console.WriteLine("اتصال با موفقیت به پایگاه داده برقرار شد.");
            } // اتصال در اینجا به صورت خودکار بسته و Dispose می‌شود.
            Console.WriteLine("اتصال بسته شد.");
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در اتصال به پایگاه داده: {ex.Message}");
        }
    }
}

اجرای SELECT و خواندن داده‌ها با SqlDataReader:

SqlDataReader یک راه سریع و کارآمد برای خواندن داده‌ها به صورت فقط خواندنی (read-only) و فقط رو به جلو (forward-only) است. این به معنای آن است که شما نمی‌توانید به عقب برگردید یا داده‌ها را تغییر دهید. SqlDataReader کمترین سربار را دارد و برای بازیابی حجم زیادی از داده‌ها بسیار بهینه است.

using System.Data.SqlClient;
using System.Configuration;
using System.Data; // برای ConnectionState

public class Employee
{
    public int EmployeeID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Department { get; set; }
    public decimal Salary { get; set; }
}

public class ConnectedDataAccess
{
    private string connectionString;

    public ConnectedDataAccess()
    {
        this.connectionString = ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;
    }

    public List<Employee> GetEmployees()
    {
        List<Employee> employees = new List<Employee>();
        string query = "SELECT EmployeeID, FirstName, LastName, Department, Salary FROM Employees";

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            employees.Add(new Employee
                            {
                                EmployeeID = reader.GetInt32(reader.GetOrdinal("EmployeeID")),
                                FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
                                LastName = reader.GetString(reader.GetOrdinal("LastName")),
                                Department = reader.IsDBNull(reader.GetOrdinal("Department")) ? null : reader.GetString(reader.GetOrdinal("Department")),
                                Salary = reader.GetDecimal(reader.GetOrdinal("Salary"))
                            });
                        }
                    }
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در بازیابی اطلاعات کارمندان: {ex.Message}");
        }
        return employees;
    }

    // مثال استفاده:
    public static void Main(string[] args)
    {
        ConnectedDataAccess dataAccess = new ConnectedDataAccess();
        List<Employee> allEmployees = dataAccess.GetEmployees();

        Console.WriteLine("\n--- لیست کارمندان ---");
        foreach (var emp in allEmployees)
        {
            Console.WriteLine($"ID: {emp.EmployeeID}, نام: {emp.FirstName} {emp.LastName}, دپارتمان: {emp.Department}, حقوق: {emp.Salary:C}");
        }
    }
}

توضیحات:

• SqlCommand: این شیء برای تعریف دستور SQL یا نام Stored Procedure استفاده می‌شود.
• ExecuteReader(): این متد یک SqlDataReader را برمی‌گرداند.
• reader.Read(): هر بار که این متد فراخوانی می‌شود، DataReader به سطر بعدی می‌رود. اگر سطر دیگری وجود داشته باشد، true برمی‌گرداند، در غیر این صورت false.
• reader.GetInt32()، reader.GetString()، reader.GetDecimal(): متدهای تایپ‌شده برای خواندن داده‌ها از ستون‌ها. بهتر است از GetOrdinal() برای دریافت ایندکس ستون استفاده کنید تا از خطاهای احتمالی در صورت تغییر ترتیب ستون‌ها جلوگیری شود.
• reader.IsDBNull(): برای بررسی اینکه آیا یک ستون حاوی مقدار NULL در پایگاه داده است یا خیر.

اجرای INSERT, UPDATE, DELETE با ExecuteNonQuery:

برای اجرای دستورات SQL که داده‌ها را تغییر می‌دهند (INSERT, UPDATE, DELETE) و هیچ مجموعه نتیجه‌ای را بر نمی‌گردانند (فقط تعداد ردیف‌های تحت تأثیر را برمی‌گردانند)، از متد ExecuteNonQuery() شیء SqlCommand استفاده می‌شود.

استفاده از پارامترها برای جلوگیری از SQL Injection (بسیار مهم!):

هرگز مقادیر را مستقیماً به رشته کوئری الحاق نکنید. این کار شما را در برابر حملات SQL Injection آسیب‌پذیر می‌کند. همیشه از پارامترها استفاده کنید.

using System.Data.SqlClient;
using System.Configuration;

public class ConnectedDataAccess
{
    private string connectionString;

    public ConnectedDataAccess()
    {
        this.connectionString = ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;
    }

    public int AddEmployee(Employee newEmployee)
    {
        string query = "INSERT INTO Employees (FirstName, LastName, Department, Salary) VALUES (@FirstName, @LastName, @Department, @Salary); SELECT SCOPE_IDENTITY();";
        int newEmployeeId = -1;

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    // افزودن پارامترها
                    command.Parameters.AddWithValue("@FirstName", newEmployee.FirstName);
                    command.Parameters.AddWithValue("@LastName", newEmployee.LastName);
                    command.Parameters.AddWithValue("@Department", newEmployee.Department ?? (object)DBNull.Value); // مدیریت مقادیر null
                    command.Parameters.AddWithValue("@Salary", newEmployee.Salary);

                    connection.Open();
                    // برای بازیابی EmployeeID جدید که توسط IDENTITY ایجاد شده است
                    newEmployeeId = Convert.ToInt32(command.ExecuteScalar());
                    Console.WriteLine($"کارمند '{newEmployee.FirstName} {newEmployee.LastName}' با موفقیت اضافه شد. ID: {newEmployeeId}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در افزودن کارمند: {ex.Message}");
        }
        return newEmployeeId;
    }

    public int UpdateEmployeeSalary(int employeeId, decimal newSalary)
    {
        string query = "UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID";
        int rowsAffected = 0;

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@NewSalary", newSalary);
                    command.Parameters.AddWithValue("@EmployeeID", employeeId);

                    connection.Open();
                    rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"تعداد ردیف‌های به‌روز شده: {rowsAffected}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در به‌روزرسانی حقوق کارمند: {ex.Message}");
        }
        return rowsAffected;
    }

    public int DeleteEmployee(int employeeId)
    {
        string query = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";
        int rowsAffected = 0;

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@EmployeeID", employeeId);

                    connection.Open();
                    rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"تعداد ردیف‌های حذف شده: {rowsAffected}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در حذف کارمند: {ex.Message}");
        }
        return rowsAffected;
    }

    // مثال استفاده:
    public static void Main(string[] args)
    {
        // فرض کنید کلاس ConnectedDataAccess و Employee در دسترس هستند
        ConnectedDataAccess dataAccess = new ConnectedDataAccess();

        // افزودن کارمند جدید
        Employee newEmp = new Employee { FirstName = "پرویز", LastName = "سعادتی", Department = "حسابداری", Salary = 58000.00m };
        int addedId = dataAccess.AddEmployee(newEmp);

        // به روز رسانی حقوق کارمند با ID 3
        dataAccess.UpdateEmployeeSalary(3, 92000.00m);

        // حذف کارمند با ID 1 (اگر وجود دارد)
        dataAccess.DeleteEmployee(1);

        // مشاهده وضعیت فعلی
        List<Employee> currentEmployees = dataAccess.GetEmployees();
        Console.WriteLine("\n--- لیست کارمندان پس از عملیات ---");
        foreach (var emp in currentEmployees)
        {
            Console.WriteLine($"ID: {emp.EmployeeID}, نام: {emp.FirstName} {emp.LastName}, دپارتمان: {emp.Department}, حقوق: {emp.Salary:C}");
        }
    }
}

توضیحات:

• command.Parameters.AddWithValue(): ساده‌ترین راه برای اضافه کردن پارامترها. این متد نوع داده SQL را به صورت خودکار حدس می‌زند، اما برای کنترل دقیق‌تر یا برای کار با NULLها، می‌توانید از Add() و تعیین صریح SqlDbType استفاده کنید.
• DBNull.Value: برای ارسال مقادیر null به پایگاه داده، باید از DBNull.Value استفاده کنید نه null C#.
• ExecuteNonQuery(): تعداد ردیف‌های متأثر از عملیات را برمی‌گرداند.
• SELECT SCOPE_IDENTITY(): در متد AddEmployee، از این دستور SQL برای بازیابی ID آخرین ردیف درج شده در جدول فعلی استفاده می‌کنیم. سپس نتیجه را با ExecuteScalar() می‌خوانیم.

اجرای توابع و Stored Procedures با ExecuteScalar:

متد ExecuteScalar() برای اجرای کوئری‌هایی استفاده می‌شود که تنها یک مقدار (یک سطر و یک ستون) را برمی‌گردانند. این متد برای بازیابی مقادیر تجمعی مانند COUNT(*)، SUM() یا فراخوانی توابع اسکالر در SQL Server بسیار مفید است.

using System.Data.SqlClient;
using System.Configuration;

public class ConnectedDataAccess
{
    private string connectionString;

    public ConnectedDataAccess()
    {
        this.connectionString = ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;
    }

    public int GetEmployeeCount()
    {
        string query = "SELECT COUNT(*) FROM Employees";
        int count = 0;

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    connection.Open();
                    object result = command.ExecuteScalar();
                    if (result != null)
                    {
                        count = Convert.ToInt32(result);
                    }
                    Console.WriteLine($"تعداد کل کارمندان: {count}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در شمارش کارمندان: {ex.Message}");
        }
        return count;
    }

    public decimal GetTotalSalaryByDepartment(string departmentName)
    {
        string query = "SELECT SUM(Salary) FROM Employees WHERE Department = @DepartmentName";
        decimal totalSalary = 0.0m;

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@DepartmentName", departmentName);
                    connection.Open();
                    object result = command.ExecuteScalar();
                    if (result != null && result != DBNull.Value)
                    {
                        totalSalary = Convert.ToDecimal(result);
                    }
                    Console.WriteLine($"مجموع حقوق در دپارتمان '{departmentName}': {totalSalary:C}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در محاسبه مجموع حقوق: {ex.Message}");
        }
        return totalSalary;
    }

    // مثال استفاده:
    public static void Main(string[] args)
    {
        ConnectedDataAccess dataAccess = new ConnectedDataAccess();
        dataAccess.GetEmployeeCount();
        dataAccess.GetTotalSalaryByDepartment("توسعه نرم‌افزار");
        dataAccess.GetTotalSalaryByDepartment("فروش");
        dataAccess.GetTotalSalaryByDepartment("حسابداری"); // اگر کارمندی با این دپارتمان اضافه کرده باشید
    }
}

توضیحات:

• ExecuteScalar(): اولین ستون از اولین سطر مجموعه نتایج را برمی‌گرداند. اگر مجموعه نتایج خالی باشد، null را برمی‌گرداند.
• حتماً نتیجه را برای DBNull.Value بررسی کنید، به خصوص در توابع تجمیعی مانند SUM که در صورت عدم وجود سطر تطابق، NULL را برمی‌گردانند.

این بخش، اصول اولیه کار با ADO.NET در معماری متصل را پوشش می‌دهد. این روش برای کنترل دقیق، عملکرد بالا و سناریوهایی که نیاز به خواندن سریع جریان‌های داده دارید، بسیار قدرتمند است.

روش دوم: کار با داده‌های Disconnected با DataSet و SqlDataAdapter

برخلاف معماری متصل که نیاز به اتصال مداوم به پایگاه داده دارد، معماری قطع‌اتصال (Disconnected Architecture) به شما امکان می‌دهد تا داده‌ها را از پایگاه داده بازیابی کنید، اتصال را ببندید و سپس به صورت آفلاین با داده‌ها در حافظه کار کنید. پس از انجام تغییرات، اتصال مجدداً برقرار می‌شود تا تغییرات به پایگاه داده بازگردانده شوند. این روش برای برنامه‌هایی که نیاز به کش کردن داده‌ها، انجام عملیات پیچیده بر روی آن‌ها بدون درگیری مداوم با پایگاه داده و سپس به‌روزرسانی دسته‌ای دارند، بسیار مناسب است. عناصر اصلی در این روش DataSet و SqlDataAdapter هستند.

معرفی DataSet و DataTable:

• DataSet:

  یک کش داده در حافظه است که شبیه به یک پایگاه داده کوچک عمل می‌کند. DataSet می‌تواند شامل یک یا چند DataTable باشد و حتی قادر است روابط (DataRelation) بین این جداول را نیز نگهداری کند. این قابلیت به شما اجازه می‌دهد تا داده‌ها را از چندین جدول در پایگاه داده واکشی کرده و آن‌ها را به صورت یکپارچه در حافظه مدیریت کنید.

  DataSet وضعیت هر ردیف (مانند اضافه شده، تغییر یافته، حذف شده) را ردیابی می‌کند، که این ویژگی برای به‌روزرسانی دسته‌ای به پایگاه داده بسیار مفید است.

• DataTable:

  نشان‌دهنده یک جدول منفرد در DataSet است. هر DataTable شامل مجموعه‌ای از DataColumnها (برای تعریف ساختار ستون‌ها) و DataRowها (برای نگهداری داده‌های واقعی) است.

پر کردن DataSet با SqlDataAdapter:

SqlDataAdapter نقش پل ارتباطی بین DataSet و پایگاه داده را ایفا می‌کند. این شیء از دستورات SQL (یا Stored Procedures) برای پر کردن DataSet با داده‌ها و سپس برای به‌روزرسانی پایگاه داده با تغییرات اعمال شده در DataSet استفاده می‌کند.

using System.Data.SqlClient;
using System.Configuration;
using System.Data; // برای DataSet, DataTable, SqlDataAdapter

public class DisconnectedDataAccess
{
    private string connectionString;

    public DisconnectedDataAccess()
    {
        this.connectionString = ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;
    }

    public DataSet GetEmployeesDataSet()
    {
        DataSet ds = new DataSet();
        // SELECT Command برای پر کردن DataSet
        string selectQuery = "SELECT EmployeeID, FirstName, LastName, Department, Salary FROM Employees";

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection))
                {
                    // Fill متد اتصال را باز می‌کند، داده‌ها را می‌خواند و DataSet را پر می‌کند، سپس اتصال را می‌بندد.
                    adapter.Fill(ds, "EmployeesTable"); // نامگذاری DataTable در DataSet
                    Console.WriteLine("DataSet با داده‌های کارمندان پر شد.");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در پر کردن DataSet: {ex.Message}");
        }
        return ds;
    }

    public void DisplayDataSetContent(DataSet ds, string tableName)
    {
        if (ds == null || !ds.Tables.Contains(tableName))
        {
            Console.WriteLine($"جدول '{tableName}' در DataSet یافت نشد.");
            return;
        }

        DataTable dt = ds.Tables[tableName];
        Console.WriteLine($"\n--- محتوای DataTable '{tableName}' ---");
        foreach (DataRow row in dt.Rows)
        {
            Console.WriteLine($"ID: {row["EmployeeID"]}, نام: {row["FirstName"]} {row["LastName"]}, دپارتمان: {row["Department"]}, حقوق: {row["Salary"]}");
        }
    }

    // مثال استفاده:
    public static void Main(string[] args)
    {
        DisconnectedDataAccess dataAccess = new DisconnectedDataAccess();
        DataSet employeeDataSet = dataAccess.GetEmployeesDataSet();
        dataAccess.DisplayDataSetContent(employeeDataSet, "EmployeesTable");
    }
}

ویرایش و به‌روزرسانی داده‌ها در DataSet:

پس از پر کردن DataSet، می‌توانید به صورت آفلاین داده‌ها را ویرایش کنید. DataSet وضعیت تغییرات (RowState) را برای هر ردیف ردیابی می‌کند. هنگامی که آماده ارسال تغییرات به پایگاه داده هستید، متد Update() از SqlDataAdapter را فراخوانی می‌کنید.

تنظیم دستورات INSERT, UPDATE, DELETE برای DataAdapter:

برای اینکه SqlDataAdapter بتواند تغییرات را به پایگاه داده اعمال کند، باید دستورات SQL مربوط به InsertCommand، UpdateCommand و DeleteCommand آن را تنظیم کنید. این دستورات نیز باید از پارامترها برای جلوگیری از SQL Injection استفاده کنند.

using System.Data.SqlClient;
using System.Configuration;
using System.Data;

public class DisconnectedDataAccess
{
    private string connectionString;

    public DisconnectedDataAccess()
    {
        this.connectionString = ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;
    }

    public void PerformCrudOperationsOnDataSet()
    {
        DataSet ds = new DataSet();
        DataTable dtEmployees;

        string selectQuery = "SELECT EmployeeID, FirstName, LastName, Department, Salary FROM Employees";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection))
            {
                // تنظیم دستور INSERT
                adapter.InsertCommand = new SqlCommand(
                    "INSERT INTO Employees (FirstName, LastName, Department, Salary) VALUES (@FirstName, @LastName, @Department, @Salary); SELECT SCOPE_IDENTITY();", connection);
                adapter.InsertCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "FirstName");
                adapter.InsertCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName");
                adapter.InsertCommand.Parameters.Add("@Department", SqlDbType.NVarChar, 50, "Department").IsNullable = true;
                adapter.InsertCommand.Parameters.Add("@Salary", SqlDbType.Decimal, 18, "Salary");
                // اگر ID توسط DB ایجاد می‌شود، باید آن را به DataTable برگردانیم.
                adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;


                // تنظیم دستور UPDATE
                adapter.UpdateCommand = new SqlCommand(
                    "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, Department = @Department, Salary = @Salary WHERE EmployeeID = @EmployeeID;", connection);
                adapter.UpdateCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "FirstName");
                adapter.UpdateCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName");
                adapter.UpdateCommand.Parameters.Add("@Department", SqlDbType.NVarChar, 50, "Department").IsNullable = true;
                adapter.UpdateCommand.Parameters.Add("@Salary", SqlDbType.Decimal, 18, "Salary");
                adapter.UpdateCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 4, "EmployeeID").SourceVersion = DataRowVersion.Original;
                // برای تشخیص تغییرات concurrency از Optimistic Concurrency استفاده می‌کنیم.
                // اگر در زمان آپدیت سطر تغییر کرده باشد، آپدیت انجام نشود.
                adapter.UpdateCommand.Parameters.Add("@Original_FirstName", SqlDbType.NVarChar, 50, "FirstName").SourceVersion = DataRowVersion.Original;
                adapter.UpdateCommand.Parameters.Add("@Original_LastName", SqlDbType.NVarChar, 50, "LastName").SourceVersion = DataRowVersion.Original;
                // ... ادامه برای سایر ستون‌ها در شرط WHERE برای Optimistic Concurrency


                // تنظیم دستور DELETE
                adapter.DeleteCommand = new SqlCommand(
                    "DELETE FROM Employees WHERE EmployeeID = @EmployeeID;", connection);
                adapter.DeleteCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 4, "EmployeeID").SourceVersion = DataRowVersion.Original;
                // برای Optimistic Concurrency، می‌توان سایر ستون‌های اصلی را نیز به عنوان پارامتر Original اضافه کرد.

                // پر کردن DataSet
                adapter.Fill(ds, "EmployeesTable");
                dtEmployees = ds.Tables["EmployeesTable"];

                Console.WriteLine("\n--- وضعیت اولیه DataSet ---");
                DisplayDataSetContent(ds, "EmployeesTable");

                // 1. افزودن یک سطر جدید
                DataRow newRow = dtEmployees.NewRow();
                newRow["FirstName"] = "زهرا";
                newRow["LastName"] = "مرادی";
                newRow["Department"] = "پشتیبانی";
                newRow["Salary"] = 48000.00m;
                dtEmployees.Rows.Add(newRow);
                Console.WriteLine("\n--- پس از افزودن سطر جدید ---");
                DisplayDataSetContent(ds, "EmployeesTable"); // هنوز ID از DB نیامده است.

                // 2. ویرایش یک سطر موجود (مثلاً کارمند با ID 3)
                DataRow rowToUpdate = dtEmployees.Select("EmployeeID = 3").FirstOrDefault();
                if (rowToUpdate != null)
                {
                    rowToUpdate["Salary"] = 99000.00m; // افزایش حقوق
                    Console.WriteLine("\n--- پس از ویرایش حقوق کارمند ID 3 ---");
                    DisplayDataSetContent(ds, "EmployeesTable");
                }

                // 3. حذف یک سطر (مثلاً کارمند با ID 2)
                DataRow rowToDelete = dtEmployees.Select("EmployeeID = 2").FirstOrDefault();
                if (rowToDelete != null)
                {
                    rowToDelete.Delete();
                    Console.WriteLine("\n--- پس از علامت‌گذاری کارمند ID 2 برای حذف ---");
                    // سطر هنوز در DataTable وجود دارد اما RowState آن به Deleted تغییر کرده است.
                    DisplayDataSetContent(ds, "EmployeesTable");
                }

                // ارسال تغییرات به پایگاه داده
                int rowsAffected = adapter.Update(ds, "EmployeesTable");
                Console.WriteLine($"\nتعداد ردیف‌های متأثر در پایگاه داده: {rowsAffected}");

                // تأیید تغییرات در DataSet پس از Update
                ds.AcceptChanges();
                Console.WriteLine("\n--- وضعیت DataSet پس از اعمال تغییرات در DB ---");
                DisplayDataSetContent(ds, "EmployeesTable");
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در عملیات DataSet: {ex.Message}");
        }
    }

    public void DisplayDataSetContent(DataSet ds, string tableName)
    {
        if (ds == null || !ds.Tables.Contains(tableName))
        {
            Console.WriteLine($"جدول '{tableName}' در DataSet یافت نشد.");
            return;
        }

        DataTable dt = ds.Tables[tableName];
        Console.WriteLine($"\n--- محتوای DataTable '{tableName}' ({dt.Rows.Cast<DataRow>().Count(r => r.RowState != DataRowState.Deleted)} ردیف فعال) ---");
        foreach (DataRow row in dt.Rows)
        {
            // برای نمایش فقط ردیف‌های غیر حذف شده، یا برای مشاهده RowState
            if (row.RowState != DataRowState.Deleted)
            {
                Console.WriteLine($"ID: {row["EmployeeID"]}, نام: {row["FirstName"]} {row["LastName"]}, دپارتمان: {row["Department"]}, حقوق: {row["Salary"]}, وضعیت: {row.RowState}");
            }
            else
            {
                 Console.WriteLine($"ID: {row["EmployeeID", DataRowVersion.Original]}, وضعیت: {row.RowState} (علامت‌گذاری شده برای حذف)");
            }
        }
    }

    // مثال استفاده:
    public static void Main(string[] args)
    {
        DisconnectedDataAccess dataAccess = new DisconnectedDataAccess();
        dataAccess.PerformCrudOperationsOnDataSet();
    }
}

توضیحات:

• SqlDataAdapter.InsertCommand، UpdateCommand، DeleteCommand: شما باید این دستورات را برای انجام عملیات مربوطه تنظیم کنید. هر SqlCommand نیاز به پارامترهای مناسب دارد.
• SqlParameter.SourceColumn: نام ستونی در DataTable که مقدار پارامتر از آن گرفته می‌شود.
• SqlParameter.SourceVersion: مشخص می‌کند که مقدار پارامتر از نسخه اصلی (Original) یا فعلی (Current) ردیف گرفته شود. DataRowVersion.Original برای شرط WHERE در دستورات UPDATE و DELETE ضروری است تا Optimistic Concurrency را پیاده‌سازی کند (یعنی اگر سطر از زمان خوانده شدن تغییر کرده باشد، به‌روزرسانی نشود).
• adapter.Update(ds, "TableName"): این متد تمام ردیف‌های تغییر یافته (اضافه شده، ویرایش شده، حذف شده) در DataTable مشخص شده را بررسی کرده و دستورات InsertCommand، UpdateCommand یا DeleteCommand مربوطه را بر اساس RowState هر ردیف اجرا می‌کند.
• DataRow.RowState: وضعیت یک ردیف در DataTable را نشان می‌دهد (Added، Modified، Deleted، Unchanged).
• ds.AcceptChanges(): پس از Update موفقیت‌آمیز، این متد را فراخوانی کنید تا RowState تمام ردیف‌ها به Unchanged تغییر کرده و نسخه اصلی (Original Version) آن‌ها با نسخه فعلی (Current Version) همگام شود. RejectChanges() نیز وجود دارد که تغییرات را لغو می‌کند.
• adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;: این خط برای InsertCommand مهم است تا مقدار IDENTITY تولید شده توسط پایگاه داده (که توسط SELECT SCOPE_IDENTITY() برگردانده می‌شود) به ستون EmployeeID ردیف جدید در DataTable اختصاص یابد.

مزایا و معایب DataSet:

مزایا:

• کار آفلاین (Offline Work): قابلیت کار با داده‌ها بدون نیاز به اتصال مداوم به پایگاه داده.
• کشینگ داده (Data Caching): داده‌ها در حافظه کش می‌شوند که می‌تواند عملکرد را برای دسترسی‌های مکرر بهبود بخشد.
• Data Binding آسان: DataSet به راحتی می‌تواند به کنترل‌های UI در برنامه‌های دسکتاپ (Windows Forms/WPF) متصل شود.
• مدیریت روابط: امکان تعریف روابط بین چندین جدول و اجرای عملیات‌های مرتبط.
• پشتیبانی از تراکنش‌های محلی: می‌توان چندین تغییر را در DataSet انجام داد و سپس آن‌ها را به صورت یک تراکنش به پایگاه داده ارسال کرد.

معایب:

• سربار (Overhead): برای عملیات‌های ساده CRUD یا حجم کمی از داده‌ها، DataSet می‌تواند سربار زیادی داشته باشد.
• داده‌های منسوخ (Stale Data): از آنجایی که داده‌ها به صورت آفلاین کار می‌شوند، ممکن است داده‌ها در DataSet با داده‌های واقعی در پایگاه داده همگام نباشند، به خصوص در محیط‌های چند کاربره. نیاز به مکانیزم‌هایی برای رفرش کردن داده‌ها دارید.
• کدنویسی بیشتر: در مقایسه با ORMها، نیاز به کدنویسی دستی بیشتری برای مدیریت SqlCommandها و پارامترها دارید.
• عدم Type Safety قوی: دسترسی به ستون‌ها با نام رشته‌ای (row["ColumnName"]) مستعد خطاهای زمان کامپایل نیست و ممکن است در زمان اجرا خطا ایجاد کند.

در مجموع، DataSet و SqlDataAdapter ابزارهای قدرتمندی برای سناریوهای خاص هستند، اما برای توسعه مدرن C# و پایگاه داده، به ویژه با ظهور ORMها، کمتر مورد استفاده قرار می‌گیرند، مگر اینکه نیازهای خاصی برای کشینگ آفلاین یا Data Binding در برنامه‌های قدیمی‌تر داشته باشید.

روش سوم: استفاده از Stored Procedures

Stored Procedures (رویه‌های ذخیره‌شده) مجموعه‌ای از دستورات SQL هستند که به صورت یک واحد منطقی در پایگاه داده کامپایل و ذخیره می‌شوند. استفاده از آن‌ها در C# یک رویکرد بسیار رایج و توصیه‌شده برای تعامل با پایگاه داده است، به خصوص در محیط‌های سازمانی و سیستم‌های پربار.

چرا Stored Procedures؟

استفاده از Stored Procedures مزایای قابل توجهی دارد:

• امنیت (Security):
  • جلوگیری از SQL Injection: از آنجایی که Stored Procedures از پارامترها استفاده می‌کنند و طرح اجرایی آن‌ها از قبل کامپایل شده است، نفوذگر نمی‌تواند دستورات SQL مخرب را به کوئری تزریق کند.
  • کنترل دسترسی دقیق: می‌توانید به کاربران پایگاه داده اجازه دهید تا فقط Stored Procedures را اجرا کنند، بدون اینکه به جداول پایه دسترسی مستقیم داشته باشند. این به شما امکان می‌دهد امنیت بر پایه نقش (Role-Based Security) را به بهترین نحو پیاده‌سازی کنید.
• عملکرد (Performance):
  • کامپایل از پیش: Stored Procedures یک بار کامپایل و در حافظه کش می‌شوند. این به معنای کاهش سربار پردازش در هر بار اجرا و افزایش سرعت پاسخگویی است.
  • کاهش ترافیک شبکه: به جای ارسال چندین دستور SQL جداگانه، تنها یک نام Stored Procedure و پارامترهای آن از کلاینت به سرور ارسال می‌شود که حجم داده‌های مبادله شده را کاهش می‌دهد.
• قابلیت نگهداری (Maintainability):
  • منطق کسب‌وکار مربوط به داده‌ها می‌تواند در یک مکان مرکزی (پایگاه داده) نگهداری شود. اگر تغییری در منطق نیاز باشد، فقط باید Stored Procedure را در پایگاه داده به‌روزرسانی کنید، نه اینکه برنامه‌های کلاینت را دوباره کامپایل و توزیع کنید.
• قابلیت استفاده مجدد (Reusability):
  • یک Stored Procedure را می‌توان توسط چندین برنامه کاربردی مختلف یا حتی بخش‌های مختلف یک برنامه استفاده کرد.
• کاهش خطا: منطق کسب‌وکار در سمت سرور و نزدیک به داده‌ها پیاده‌سازی می‌شود، که می‌تواند به کاهش احتمال خطاهای منطقی و اطمینان از یکپارچگی داده‌ها کمک کند.
• پیچیدگی عملیات: برای عملیات‌های پیچیده‌ای که شامل چندین مرحله، تراکنش‌ها یا منطق شرطی هستند، Stored Procedures بسیار کارآمدتر از کوئری‌های دینامیک هستند.

ایجاد Stored Procedure در SQL Server:

بیایید چند Stored Procedure نمونه برای عملیات CRUD روی جدول Employees ایجاد کنیم.

USE CompanyDB;
GO

-- Stored Procedure برای بازیابی همه کارمندان
CREATE PROCEDURE sp_GetAllEmployees
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department, Salary
    FROM Employees;
END;
GO

-- Stored Procedure برای افزودن کارمند جدید
CREATE PROCEDURE sp_AddEmployee
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Department NVARCHAR(50),
    @Salary DECIMAL(18, 2)
AS
BEGIN
    INSERT INTO Employees (FirstName, LastName, Department, Salary)
    VALUES (@FirstName, @LastName, @Department, @Salary);

    SELECT SCOPE_IDENTITY() AS NewEmployeeID; -- برای برگرداندن ID تولید شده
END;
GO

-- Stored Procedure برای به‌روزرسانی حقوق کارمند
CREATE PROCEDURE sp_UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(18, 2)
AS
BEGIN
    UPDATE Employees
    SET Salary = @NewSalary
    WHERE EmployeeID = @EmployeeID;
END;
GO

-- Stored Procedure برای حذف کارمند
CREATE PROCEDURE sp_DeleteEmployee
    @EmployeeID INT
AS
BEGIN
    DELETE FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;
GO

-- Stored Procedure با پارامتر خروجی برای دریافت تعداد کارمندان
CREATE PROCEDURE sp_GetEmployeeCountWithOutput
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*) FROM Employees;
END;
GO

فراخوانی Stored Procedure از C#:

فراخوانی Stored Procedures از C# بسیار شبیه به اجرای دستورات SQL معمولی است، با این تفاوت که CommandType را روی StoredProcedure تنظیم می‌کنید و پارامترها را با دقت بیشتری مدیریت می‌کنید.

using System.Data.SqlClient;
using System.Configuration;
using System.Data; // برای CommandType, ParameterDirection

public class StoredProcedureDataAccess
{
    private string connectionString;

    public StoredProcedureDataAccess()
    {
        this.connectionString = ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;
    }

    public List<Employee> GetEmployeesUsingSP()
    {
        List<Employee> employees = new List<Employee>();
        string spName = "sp_GetAllEmployees";

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(spName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure; // تعیین نوع دستور به عنوان Stored Procedure

                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            employees.Add(new Employee
                            {
                                EmployeeID = reader.GetInt32(reader.GetOrdinal("EmployeeID")),
                                FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
                                LastName = reader.GetString(reader.GetOrdinal("LastName")),
                                Department = reader.IsDBNull(reader.GetOrdinal("Department")) ? null : reader.GetString(reader.GetOrdinal("Department")),
                                Salary = reader.GetDecimal(reader.GetOrdinal("Salary"))
                            });
                        }
                    }
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در فراخوانی Stored Procedure 'sp_GetAllEmployees': {ex.Message}");
        }
        return employees;
    }

    public int AddEmployeeUsingSP(Employee newEmployee)
    {
        string spName = "sp_AddEmployee";
        int newEmployeeId = -1;

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(spName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.AddWithValue("@FirstName", newEmployee.FirstName);
                    command.Parameters.AddWithValue("@LastName", newEmployee.LastName);
                    command.Parameters.AddWithValue("@Department", newEmployee.Department ?? (object)DBNull.Value);
                    command.Parameters.AddWithValue("@Salary", newEmployee.Salary);

                    connection.Open();
                    // sp_AddEmployee یک نتیجه (NewEmployeeID) برمی‌گرداند، بنابراین از ExecuteScalar استفاده می‌کنیم.
                    object result = command.ExecuteScalar();
                    if (result != null && result != DBNull.Value)
                    {
                        newEmployeeId = Convert.ToInt32(result);
                    }
                    Console.WriteLine($"کارمند '{newEmployee.FirstName} {newEmployee.LastName}' با موفقیت با SP اضافه شد. ID: {newEmployeeId}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در فراخوانی Stored Procedure 'sp_AddEmployee': {ex.Message}");
        }
        return newEmployeeId;
    }

    public int UpdateEmployeeSalaryUsingSP(int employeeId, decimal newSalary)
    {
        string spName = "sp_UpdateEmployeeSalary";
        int rowsAffected = 0;

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(spName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.AddWithValue("@EmployeeID", employeeId);
                    command.Parameters.AddWithValue("@NewSalary", newSalary);

                    connection.Open();
                    rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"تعداد ردیف‌های به‌روز شده با SP: {rowsAffected}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در فراخوانی Stored Procedure 'sp_UpdateEmployeeSalary': {ex.Message}");
        }
        return rowsAffected;
    }

    public int DeleteEmployeeUsingSP(int employeeId)
    {
        string spName = "sp_DeleteEmployee";
        int rowsAffected = 0;

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(spName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@EmployeeID", employeeId);

                    connection.Open();
                    rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"تعداد ردیف‌های حذف شده با SP: {rowsAffected}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در فراخوانی Stored Procedure 'sp_DeleteEmployee': {ex.Message}");
        }
        return rowsAffected;
    }

    // مثال با پارامتر خروجی
    public int GetEmployeeCountUsingSPWithOutput()
    {
        string spName = "sp_GetEmployeeCountWithOutput";
        int employeeCount = 0;

        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(spName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    // تعریف پارامتر خروجی
                    SqlParameter countParam = new SqlParameter("@EmployeeCount", SqlDbType.Int);
                    countParam.Direction = ParameterDirection.Output;
                    command.Parameters.Add(countParam);

                    connection.Open();
                    command.ExecuteNonQuery(); // برای Stored Procedure با پارامتر خروجی

                    // خواندن مقدار پارامتر خروجی
                    if (countParam.Value != DBNull.Value)
                    {
                        employeeCount = Convert.ToInt32(countParam.Value);
                    }
                    Console.WriteLine($"تعداد کارمندان از طریق SP با پارامتر خروجی: {employeeCount}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"خطا در فراخوانی Stored Procedure 'sp_GetEmployeeCountWithOutput': {ex.Message}");
        }
        return employeeCount;
    }

    // مثال استفاده:
    public static void Main(string[] args)
    {
        StoredProcedureDataAccess spDataAccess = new StoredProcedureDataAccess();

        // تست Get
        List<Employee> employees = spDataAccess.GetEmployeesUsingSP();
        Console.WriteLine("\n--- کارمندان با SP ---");
        foreach (var emp in employees)
        {
            Console.WriteLine($"ID: {emp.EmployeeID}, نام: {emp.FirstName} {emp.LastName}");
        }

        // تست Add
        Employee newEmpSp = new Employee { FirstName = "کریم", LastName = "حاتمی", Department = "حسابداری", Salary = 55000.00m };
        int newId = spDataAccess.AddEmployeeUsingSP(newEmpSp);

        // تست Update
        spDataAccess.UpdateEmployeeSalaryUsingSP(3, 100000.00m); // فرض بر این است که ID 3 وجود دارد.

        // تست Delete
        // spDataAccess.DeleteEmployeeUsingSP(4); // حذف کارمند با ID 4

        // تست پارامتر خروجی
        spDataAccess.GetEmployeeCountUsingSPWithOutput();
    }
}

توضیحات:

• command.CommandType = CommandType.StoredProcedure;: این خط به ADO.NET می‌گوید که رشته CommandText یک نام Stored Procedure است، نه یک دستور SQL مستقیم.
• پارامترها:
  • برای هر پارامتر ورودی در Stored Procedure، یک SqlParameter به command.Parameters اضافه می‌کنید. استفاده از AddWithValue() معمولاً کافی است، اما می‌توانید با Add() و تعیین صریح SqlDbType و ParameterDirection.Input (پیش‌فرض) کنترل بیشتری داشته باشید.
  • برای پارامترهای خروجی (OUTPUT در T-SQL)، باید Direction پارامتر را روی ParameterDirection.Output تنظیم کنید. پس از اجرای ExecuteNonQuery() یا ExecuteReader()، می‌توانید مقدار پارامتر خروجی را از طریق خاصیت Value آن پارامتر بخوانید.
• ExecuteNonQuery() vs ExecuteScalar() vs ExecuteReader():
  • از ExecuteNonQuery() برای Stored Proceduresی که هیچ مجموعه نتیجه‌ای برنمی‌گردانند (مانند UPDATE, DELETE یا Stored Procedures با پارامترهای خروجی) استفاده کنید.
  • از ExecuteScalar() برای Stored Proceduresی که یک مقدار تکی برمی‌گردانند (مانند sp_AddEmployee که SCOPE_IDENTITY() را برمی‌گرداند) استفاده کنید.
  • از ExecuteReader() برای Stored Proceduresی که مجموعه نتایجی (مانند SELECT) برمی‌گردانند، استفاده کنید.

استفاده از Stored Procedures یک روش قدرتمند و امن برای تعامل با پایگاه داده است و باید در پروژه‌های جدی C# به شدت مورد توجه قرار گیرد.

روش چهارم: مدل‌سازی داده با ORM (Object-Relational Mapping)

Object-Relational Mapping (ORM) یک تکنیک برنامه‌نویسی است که شکاف بین مدل‌های شیءگرای یک زبان برنامه‌نویسی (مانند C#) و مدل‌های رابطه‌ای یک پایگاه داده (مانند SQL Server) را پر می‌کند. به جای اینکه مستقیماً با جداول و ستون‌های پایگاه داده کار کنید و دستورات SQL دستی بنویسید، ORM به شما امکان می‌دهد تا با اشیاء C# (که به آن‌ها “موجودیت” یا “Entity” گفته می‌شود) در کد خود تعامل داشته باشید. این اشیاء نمایانگر سطرها در جداول پایگاه داده هستند و ORM مسئول ترجمه عملیات روی این اشیاء به دستورات SQL و برعکس است.

مفهوم ORM:

ORM عملیات پایگاه داده را انتزاعی می‌کند. شما به جای نوشتن کوئری‌های SQL مانند SELECT * FROM Employees WHERE Department = 'IT'، می‌توانید از LINQ (Language Integrated Query) در C# استفاده کنید که بسیار شبیه به کوئری‌نویسی در اشیاء است: context.Employees.Where(e => e.Department == "IT").ToList(). ORM این LINQ را به SQL مناسب ترجمه می‌کند و نتایج را به اشیاء C# نگاشت می‌دهد.

مزایای ORM:

• افزایش بهره‌وری: کدنویسی کمتر و سریع‌تر، به خصوص برای عملیات CRUD استاندارد.
• Type Safety: کار با اشیاء C# به جای رشته‌های SQL، خطاهای زمان اجرا را به خطاهای زمان کامپایل تبدیل می‌کند.
• کاهش کد boilerplate: ORM بسیاری از کارهای تکراری مربوط به اتصال، اجرای کوئری و نگاشت را به صورت خودکار انجام می‌دهد.
• پشتیبانی از LINQ: امکان نوشتن کوئری‌ها به زبان C# که خوانایی و نگهداری کد را بهبود می‌بخشد.
• انعطاف‌پذیری پایگاه داده: بسیاری از ORMها مستقل از پایگاه داده هستند و می‌توانید بدون تغییر کد برنامه، پایگاه داده خود را تغییر دهید (البته نیاز به پیکربندی مجدد دارید).
• مفهوم‌سازی شیءگرا: به توسعه‌دهندگان اجازه می‌دهد تا در چارچوب تفکر شیءگرا باقی بمانند.

معایب ORM:

• منحنی یادگیری: یادگیری یک ORM جدید می‌تواند زمان‌بر باشد.
• کاهش کنترل بر SQL: برای کوئری‌های بسیار پیچیده یا بهینه‌سازی‌های خاص پایگاه داده، ORM ممکن است SQL بهینه تولید نکند. در چنین مواردی ممکن است نیاز به نوشتن کوئری‌های خام SQL داشته باشید.
• سربار عملکردی: در برخی سناریوها، ORM می‌تواند سربار عملکردی جزئی ایجاد کند.
• “مشکل عدم تطابق امپدانس”: تفاوت‌های ذاتی بین مدل شیءگرا و مدل رابطه‌ای می‌تواند منجر به چالش‌هایی در طراحی شود.

Entity Framework Core (EF Core): معرفی و نصب

Entity Framework Core (EF Core) یک ORM سبک‌وزن، توسعه‌پذیر و cross-platform از مایکروسافت است. این ابزار به توسعه‌دهندگان .NET امکان می‌دهد با استفاده از اشیاء .NET با پایگاه داده‌ها تعامل داشته باشند. EF Core جایگزین نسل قبلی Entity Framework (Full Framework) شده و به طور گسترده در پروژه‌های مدرن ASP.NET Core و .NET استفاده می‌شود.

نصب EF Core:

برای شروع کار با EF Core، باید پکیج‌های NuGet مربوطه را نصب کنید. حداقل نیاز دارید به:

1. Microsoft.EntityFrameworkCore.SqlServer: ارائه‌دهنده SQL Server برای EF Core.
2. Microsoft.EntityFrameworkCore.Tools: شامل ابزارهایی برای مدیریت Migrationها و Scaffold کردن (برای Database-First).
3. Microsoft.EntityFrameworkCore.Design: برای فعال کردن قابلیت‌های Design-time (مانند Migrationها).

در Visual Studio، در Package Manager Console (Tools > NuGet Package Manager > Package Manager Console)، دستورات زیر را اجرا کنید (مطمئن شوید که پروژه مناسبی را انتخاب کرده‌اید):

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.EntityFrameworkCore.Design

Code-First Development با EF Core:

در رویکرد Code-First، شما ابتدا کلاس‌های مدل C# خود را تعریف می‌کنید، و سپس EF Core از این کلاس‌ها برای تولید یا به‌روزرسانی طرح (Schema) پایگاه داده استفاده می‌کند. این رویکرد در توسعه جدید و محیط‌هایی که توسعه‌دهندگان کنترل بیشتری بر طرح پایگاه داده دارند، محبوب است.

1. تعریف کلاس‌های مدل:

این کلاس‌ها نمایانگر جداول در پایگاه داده و ستون‌های آن‌ها هستند.

using System.ComponentModel.DataAnnotations; // برای Key
using System.ComponentModel.DataAnnotations.Schema; // برای Column

public class Employee
{
    [Key] // مشخص می‌کند که این ستون کلید اصلی است.
    public int EmployeeID { get; set; }

    [Required] // مشخص می‌کند که این ستون نمی‌تواند NULL باشد.
    [Column(TypeName = "nvarchar(50)")] // مشخص می‌کند که نوع داده در DB چگونه باشد.
    public string FirstName { get; set; }

    [Required]
    [Column(TypeName = "nvarchar(50)")]
    public string LastName { get; set; }

    [Column(TypeName = "nvarchar(50)")]
    public string Department { get; set; }

    [Column(TypeName = "decimal(18, 2)")]
    public decimal Salary { get; set; }
}

2. تعریف کلاس DbContext:

DbContext رابط اصلی شما با پایگاه داده است. این کلاس شامل DbSetهایی برای هر مدل است که نمایانگر مجموعه‌ای از موجودیت‌ها در پایگاه داده است.

using Microsoft.EntityFrameworkCore;

public class CompanyDbContext : DbContext
{
    public CompanyDbContext(DbContextOptions<CompanyDbContext> options) : base(options)
    {
    }

    // DbSet برای جدول Employees
    public DbSet<Employee> Employees { get; set; }

    // می‌توانید تنظیمات اضافی برای مدل‌های خود را در اینجا پیکربندی کنید (Fluent API)
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // مثال: برای تعریف نوع داده دقیق‌تر یا روابط
        // modelBuilder.Entity<Employee>()
        //     .Property(e => e.Salary)
        //     .HasColumnType("decimal(18, 2)");
    }
}

3. پیکربندی Connection String در Startup.cs (برای ASP.NET Core) یا Program.cs (برای Console App):

برای ASP.NET Core: در Startup.cs در متد ConfigureServices:

public void ConfigureServices(IServiceCollection services)
{
    // ...
    services.AddDbContext<CompanyDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("CompanyDBConnection")));
    // ...
}

و Connection String در appsettings.json:

{
  "ConnectionStrings": {
    "CompanyDBConnection": "Server=(localdb)\\MSSQLLocalDB;Database=CompanyDB_EFCore;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

برای Console Application: می‌توانید DbContextOptions را به صورت دستی بسازید و آن را به کانستراکتور DbContext ارسال کنید. یا از DbContextOptionsBuilder استفاده کنید.

using Microsoft.EntityFrameworkCore;
using System.Configuration; // برای App.config

public class Program
{
    public static void Main(string[] args)
    {
        var connectionString = ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;

        var optionsBuilder = new DbContextOptionsBuilder<CompanyDbContext>();
        optionsBuilder.UseSqlServer(connectionString);

        using (var context = new CompanyDbContext(optionsBuilder.Options))
        {
            // اطمینان از ایجاد پایگاه داده و اعمال Migrationها
            context.Database.Migrate(); // اعمال Migrationهای در حال انتظار
            Console.WriteLine("پایگاه داده ایجاد یا به‌روزرسانی شد.");

            // ادامه عملیات CRUD
        }
    }
}

برای اینکه ConfigurationManager در Console App کار کند، باید فایل App.config را اضافه کنید (مانند آنچه در بخش Connection String توضیح داده شد) و رفرنس به System.Configuration را در پروژه خود اضافه کنید.

4. Migrationها:

Migrationها به EF Core اجازه می‌دهند تا تغییرات در مدل‌های شما را به تغییرات در طرح پایگاه داده ترجمه کنند. این یک راه عالی برای مدیریت تکامل طرح پایگاه داده در طول زمان است.

• اضافه کردن Migration اولیه:

  در Package Manager Console:

  Add-Migration InitialCreate

  این دستور یک فایل Migration ایجاد می‌کند که حاوی کدی برای ایجاد جدول Employees بر اساس کلاس Employee شماست.

• اعمال Migration به پایگاه داده:

  در Package Manager Console:

  Update-Database

  این دستور پایگاه داده را بر اساس Migrationهای ایجاد شده، ایجاد یا به‌روزرسانی می‌کند.

Database-First Development با EF Core:

در رویکرد Database-First، شما یک پایگاه داده موجود دارید و می‌خواهید EF Core کلاس‌های مدل و DbContext را از آن پایگاه داده برای شما تولید کند (Scaffold کند). این رویکرد برای پروژه‌هایی با پایگاه داده‌های موجود مناسب است.

برای Scaffold کردن، از Package Manager Console استفاده کنید:

Scaffold-DbContext "Server=(localdb)\\MSSQLLocalDB;Database=CompanyDB;Trusted_Connection=True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -ContextDir Data -Context CompanyDbContext

توضیحات:

• اولین آرگومان، Connection String به پایگاه داده موجود شماست.
• دومین آرگومان، ارائه‌دهنده EF Core است (Microsoft.EntityFrameworkCore.SqlServer).
• -OutputDir Models: مشخص می‌کند که کلاس‌های مدل (مانند Employee.cs) در پوشه Models ایجاد شوند.
• -ContextDir Data: مشخص می‌کند که کلاس DbContext (مثلاً CompanyDbContext.cs) در پوشه Data ایجاد شود.
• -Context CompanyDbContext: نام کلاس DbContext را مشخص می‌کند.

این دستور کلاس‌های Employee و CompanyDbContext را بر اساس طرح پایگاه داده CompanyDB تولید می‌کند.

عملیات CRUD با EF Core:

پس از تعریف مدل و DbContext، عملیات CRUD بسیار ساده‌تر می‌شود.

using Microsoft.EntityFrameworkCore;
using System.Linq; // برای Linq
using System.Collections.Generic; // برای List

public class EmployeeService
{
    private readonly CompanyDbContext _context;

    public EmployeeService(CompanyDbContext context)
    {
        _context = context;
    }

    // C - Create
    public int AddEmployee(Employee newEmployee)
    {
        _context.Employees.Add(newEmployee);
        _context.SaveChanges(); // تغییرات را به پایگاه داده اعمال می‌کند
        Console.WriteLine($"کارمند '{newEmployee.FirstName} {newEmployee.LastName}' با EF Core اضافه شد. ID: {newEmployee.EmployeeID}");
        return newEmployee.EmployeeID;
    }

    // R - Read
    public List<Employee> GetAllEmployees()
    {
        return _context.Employees.ToList(); // همه کارمندان را از پایگاه داده بازیابی می‌کند.
    }

    public Employee GetEmployeeById(int id)
    {
        return _context.Employees.FirstOrDefault(e => e.EmployeeID == id); // کارمند با ID مشخص را بازیابی می‌کند.
    }

    public List<Employee> GetEmployeesByDepartment(string department)
    {
        return _context.Employees.Where(e => e.Department == department).ToList(); // فیلتر کردن با LINQ
    }

    // U - Update
    public bool UpdateEmployeeSalary(int employeeId, decimal newSalary)
    {
        var employee = _context.Employees.FirstOrDefault(e => e.EmployeeID == employeeId);
        if (employee != null)
        {
            employee.Salary = newSalary;
            _context.SaveChanges();
            Console.WriteLine($"حقوق کارمند ID {employeeId} به {newSalary:C} به‌روزرسانی شد.");
            return true;
        }
        Console.WriteLine($"کارمند با ID {employeeId} یافت نشد.");
        return false;
    }

    // D - Delete
    public bool DeleteEmployee(int employeeId)
    {
        var employee = _context.Employees.FirstOrDefault(e => e.EmployeeID == employeeId);
        if (employee != null)
        {
            _context.Employees.Remove(employee);
            _context.SaveChanges();
            Console.WriteLine($"کارمند با ID {employeeId} حذف شد.");
            return true;
        }
        Console.WriteLine($"کارمند با ID {employeeId} برای حذف یافت نشد.");
        return false;
    }

    // مثال استفاده (در Main متد یا سرویس دیگری):
    public static void Main(string[] args)
    {
        // فرض کنید CompanyDbContext به درستی پیکربندی و Injection شده است.
        // برای Console App، همانند مثال بالا DbContextOptionsBuilder را استفاده کنید.
        var connectionString = ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;
        var optionsBuilder = new DbContextOptionsBuilder<CompanyDbContext>();
        optionsBuilder.UseSqlServer(connectionString);

        using (var context = new CompanyDbContext(optionsBuilder.Options))
        {
            context.Database.Migrate(); // اطمینان از اعمال migration ها

            EmployeeService service = new EmployeeService(context);

            // افزودن
            Employee emp1 = new Employee { FirstName = "علی", LastName = "رضایی", Department = "IT", Salary = 75000.00m };
            service.AddEmployee(emp1);

            // بازیابی همه
            List<Employee> allEmps = service.GetAllEmployees();
            Console.WriteLine("\n--- همه کارمندان (EF Core) ---");
            foreach (var emp in allEmps)
            {
                Console.WriteLine($"ID: {emp.EmployeeID}, نام: {emp.FirstName} {emp.LastName}, حقوق: {emp.Salary:C}");
            }

            // بازیابی بر اساس ID
            Employee foundEmp = service.GetEmployeeById(emp1.EmployeeID);
            if (foundEmp != null)
            {
                Console.WriteLine($"\nکارمند یافت شده: {foundEmp.FirstName} {foundEmp.LastName}");
            }

            // به روز رسانی
            service.UpdateEmployeeSalary(emp1.EmployeeID, 80000.00m);

            // حذف
            service.DeleteEmployee(foundEmp.EmployeeID);
        }
    }
}

توضیحات:

• _context.Employees.Add(newEmployee): شیء را به DbSet اضافه می‌کند. EF Core آن را در حالت Added ردیابی می‌کند.
• _context.SaveChanges(): این متد تغییرات ردیابی شده را به پایگاه داده ارسال می‌کند. EF Core دستورات SQL مناسب (INSERT, UPDATE, DELETE) را بر اساس RowState داخلی اشیاء تولید و اجرا می‌کند.
• _context.Employees.ToList(): همه موجودیت‌ها را از جدول Employees بازیابی می‌کند.
• _context.Employees.FirstOrDefault(e => e.EmployeeID == id): یک موجودیت را بر اساس یک شرط بازیابی می‌کند.
• _context.Employees.Where(e => e.Department == department).ToList(): یک مجموعه از موجودیت‌ها را بر اساس یک شرط بازیابی می‌کند.
• _context.Employees.Remove(employee): شیء را برای حذف علامت‌گذاری می‌کند.
• LINQ to Entities: کوئری‌های LINQ (مانند Where، OrderBy، Select) توسط EF Core به SQL ترجمه می‌شوند و در سمت پایگاه داده اجرا می‌شوند، نه در حافظه.

مقایسه ADO.NET و EF Core (یا سایر ORMها):

• ADO.NET (Connected/Disconnected):
  • کنترل کامل و دقیق بر دستورات SQL و فرآیند ارتباط با پایگاه داده.
  • بهترین عملکرد ممکن، زیرا هیچ لایه انتزاعی وجود ندارد.
  • مناسب برای کوئری‌های بسیار پیچیده و بهینه‌سازی‌های خاص پایگاه داده.
  • نیاز به کدنویسی بیشتر، مدیریت دستی پارامترها و نگاشت داده‌ها به اشیاء.
  • پتانسیل بالاتر برای خطاهای SQL Injection در صورت عدم استفاده صحیح از پارامترها.
• EF Core (ORM):
  • بهره‌وری بالاتر برای توسعه، به خصوص برای عملیات CRUD استاندارد.
  • Type Safety قوی‌تر و استفاده از LINQ برای کوئری‌نویسی.
  • انتزاع پیچیدگی‌های پایگاه داده، اجازه می‌دهد تا توسعه‌دهنده بر منطق کسب‌وکار تمرکز کند.
  • پشتیبانی داخلی از مفاهیمی مانند Identity Management، Change Tracking و Lazy Loading.
  • ممکن است برای برخی کوئری‌های پیچیده، SQL بهینه تولید نکند.
  • منحنی یادگیری اولیه.

برای اکثر پروژه‌های مدرن C#، به خصوص پروژه‌های جدید و پروژه‌های وب (ASP.NET Core)، استفاده از EF Core به دلیل افزایش بهره‌وری و Type Safety قویاً توصیه می‌شود. ADO.NET برای سناریوهای خاصی که نیاز به کنترل دقیق بر عملکرد و کوئری‌های بسیار بهینه‌شده دارید، همچنان یک گزینه قدرتمند است.

مدیریت خطا و امنیت در اتصال به پایگاه داده

در هر برنامه کاربردی که با پایگاه داده تعامل دارد، مدیریت خطا و تضمین امنیت از اهمیت بالایی برخوردار است. نادیده گرفتن این جنبه‌ها می‌تواند منجر به خرابی برنامه، از دست رفتن داده‌ها، یا حتی آسیب‌پذیری‌های امنیتی جدی شود.

مدیریت خطا (Error Handling):

خطاهای پایگاه داده می‌توانند به دلایل مختلفی رخ دهند: مشکلات شبکه، عدم دسترسی به سرور، خطاهای SQL، نقض قیدهای پایگاه داده، و غیره. استفاده صحیح از مکانیزم‌های مدیریت خطا به برنامه شما کمک می‌کند تا به graceful (با ظرافت) از این شرایط بازیابی شده یا به کاربر اطلاع‌رسانی کند.

• try-catch-finally blocks:

  این ساختار پایه برای مدیریت خطا در C# است. هر کد تعاملی با پایگاه داده باید در بلوک try قرار گیرد.

  try
  {
      // کد تعامل با پایگاه داده: اتصال، اجرای دستور، خواندن/نوشتن
  }
  catch (SqlException ex) // خطاها را به صورت خاص‌تر مدیریت کنید
  {
      // مدیریت خطاهای خاص SQL Server
      Console.WriteLine($"خطای پایگاه داده SQL: {ex.Message}");
      Console.WriteLine($"شماره خطا: {ex.Number}"); // شماره خطای خاص SQL Server را بررسی کنید
      // ممکن است بخواهید بر اساس شماره خطا، منطق خاصی را اجرا کنید یا پیام متفاوتی نمایش دهید.
  }
  catch (Exception ex) // مدیریت خطاهای عمومی‌تر
  {
      // مدیریت سایر انواع خطاها
      Console.WriteLine($"خطای عمومی: {ex.Message}");
  }
  finally
  {
      // کد پاکسازی منابع، مانند بستن اتصال پایگاه داده (اگر از using استفاده نمی‌کنید)
      // این بلوک همیشه اجرا می‌شود، چه خطا رخ دهد و چه ندهد.
  }
          

• SqlException:

  هنگام کار با SQL Server، می‌توانید از SqlException برای گرفتن خطاهای خاص پایگاه داده استفاده کنید. این کلاس حاوی اطلاعات مفیدی مانند Number (شماره خطای SQL Server)، LineNumber و Procedure است که می‌تواند برای عیب‌یابی بسیار کمک‌کننده باشد.

• Logging errors:

  به جای فقط چاپ خطا به کنسول، از یک سیستم لاگ‌برداری مناسب (مانند Serilog، NLog یا ابزارهای داخلی .NET Core Logging) برای ثبت جزئیات خطاها استفاده کنید. این لاگ‌ها برای عیب‌یابی در محیط تولیدی حیاتی هستند.

  // مثال ساده لاگ
  public void SafeGetEmployees()
  {
      try
      {
          // ... کد GetEmployees
      }
      catch (SqlException ex)
      {
          // Log the full exception details
          _logger.LogError(ex, "خطا در بازیابی کارمندان از پایگاه داده. شماره خطا: {SqlErrorNumber}", ex.Number);
          throw; // پرتاب مجدد خطا تا لایه‌های بالاتر بتوانند آن را مدیریت کنند.
      }
  }
          

جلوگیری از حملات SQL Injection:

SQL Injection یکی از رایج‌ترین و خطرناک‌ترین حملات به برنامه‌های مبتنی بر پایگاه داده است. این حمله زمانی رخ می‌دهد که ورودی کاربر بدون اعتبارسنجی و به درستی Sanitization شده و به عنوان بخشی از دستور SQL به پایگاه داده ارسال می‌شود، که مهاجم را قادر می‌سازد تا کوئری اصلی را تغییر داده و به داده‌های غیرمجاز دسترسی پیدا کند، آن‌ها را تغییر دهد یا حذف کند.

• هرگز رشته‌ها را مستقیماً الحاق نکنید:

  این بدترین کاری است که می‌توانید انجام دهید. به عنوان مثال، کد زیر به شدت آسیب‌پذیر است:

  // کد آسیب‌پذیر
  string username = "admin";
  string password = "123"; // فرض کنید کاربر وارد کرده است.
  // اگر کاربر ورودی " ' OR '1'='1 -- " را برای رمز عبور بدهد، هر کسی می‌تواند وارد شود.
  string query = $"SELECT * FROM Users WHERE Username = '{username}' AND Password = '{password}'";
          

• همیشه از پارامترها استفاده کنید:

  این تنها راه مطمئن و توصیه شده برای ارسال ورودی کاربر به پایگاه داده است. پارامترها مقادیر ورودی را به عنوان داده Literal در نظر می‌گیرند، نه به عنوان بخشی از کد SQL.

  // کد ایمن با پارامترها (ADO.NET)
  string query = "SELECT * FROM Users WHERE Username = @Username AND Password = @Password";
  using (SqlCommand command = new SqlCommand(query, connection))
  {
      command.Parameters.AddWithValue("@Username", username);
      command.Parameters.AddWithValue("@Password", password);
      // ...
  }
  
  // کد ایمن با EF Core (به صورت خودکار از پارامترها استفاده می‌کند)
  var user = _context.Users.FirstOrDefault(u => u.Username == username && u.Password == password);
          

• استفاده از Stored Procedures:

  همانطور که در بخش قبل توضیح داده شد، Stored Procedures به طور ذاتی در برابر SQL Injection مقاوم هستند، به شرطی که پارامترهای آن‌ها به درستی در C# استفاده شوند و خود Stored Procedure نیز کوئری‌های دینامیک ناامن درونی نداشته باشد.

امنیت Connection String:

Connection String حاوی اطلاعات حساسی برای دسترسی به پایگاه داده است. فاش شدن آن می‌تواند منجر به دسترسی غیرمجاز به کل پایگاه داده شود.

• هرگز در کد هاردکد نکنید:

  این کار بسیار ناامن است. Connection String باید از فایل‌های پیکربندی یا سرویس‌های مدیریت راز بارگیری شود.

• ذخیره‌سازی در فایل‌های پیکربندی (App.config/appsettings.json):

  این روش رایج‌تر و ایمن‌تر از هاردکد کردن است. برای امنیت بیشتر در محیط‌های تولیدی، می‌توانید بخش connectionStrings در App.config یا Web.config را رمزگذاری کنید.

  رمزگذاری Web.config (برای ASP.NET Framework):

  // در خط فرمان Developer Command Prompt برای Visual Studio
  aspnet_regiis -pef "connectionStrings" "C:\YourAppPath\YourWebApp"
          

• استفاده از Azure Key Vault/AWS Secrets Manager/HashiCorp Vault:

  برای محیط‌های تولیدی و برنامه‌های ابری، بهترین روش استفاده از سرویس‌های مدیریت راز است. این سرویس‌ها Connection String را به صورت ایمن ذخیره کرده و فقط در زمان اجرا به برنامه شما دسترسی می‌دهند، بدون اینکه رمز عبور در هیچ فایل پیکربندی یا کد منبعی ظاهر شود.

• استفاده از Windows Authentication (Integrated Security):

  در صورت امکان، از Windows Authentication (Integrated Security=True) به جای SQL Server Authentication استفاده کنید. این روش اعتبارنامه را مستقیماً در Connection String قرار نمی‌دهد، بلکه از هویت کاربر یا سرویس ویندوز برای اتصال استفاده می‌کند. این روش به طور کلی ایمن‌تر است.

حداقل دسترسی (Least Privilege Principle):

به کاربری که برنامه شما برای اتصال به پایگاه داده استفاده می‌کند (چه کاربر ویندوز و چه کاربر SQL Server)، فقط حداقل مجوزهای لازم برای انجام وظایفش را بدهید. به عنوان مثال:

• اگر برنامه فقط نیاز به خواندن داده دارد، فقط مجوز SELECT را بدهید.
• اگر برنامه نیاز به خواندن و نوشتن دارد، مجوزهای SELECT, INSERT, UPDATE, DELETE را بدهید، اما نه مجوزهای مدیریتی مانند ALTER یا DROP.
• اگر از Stored Procedures استفاده می‌کنید، به کاربر فقط مجوز EXECUTE بر روی Stored Procedures مربوطه را بدهید، نه دسترسی مستقیم به جداول زیرین.

این اصل، سطح آسیب‌پذیری در صورت به خطر افتادن Connection String یا کد برنامه را به شدت کاهش می‌دهد.

با پیاده‌سازی صحیح این اصول مدیریت خطا و امنیت، می‌توانید برنامه‌های C# قوی و قابل اعتمادی بسازید که با SQL Server به صورت ایمن تعامل دارند.

بهترین روش‌ها و نکات پیشرفته

علاوه بر مفاهیم پایه اتصال، درک و به کارگیری بهترین روش‌ها و تکنیک‌های پیشرفته می‌تواند به طور قابل توجهی عملکرد، مقیاس‌پذیری و قابلیت نگهداری برنامه‌های C# شما را هنگام تعامل با SQL Server بهبود بخشد.

استفاده از Connection Pooling:

Connection Pooling یک تکنیک بهینه‌سازی است که توسط ADO.NET (و در نتیجه EF Core) به صورت خودکار مدیریت می‌شود و به شما کمک می‌کند تا سربار ایجاد و بستن مکرر اتصالات به پایگاه داده را کاهش دهید. باز کردن و بستن یک اتصال پایگاه داده یک عملیات گران‌قیمت است.

• چگونه کار می‌کند:

  زمانی که شما یک اتصال را باز می‌کنید و سپس آن را می‌بندید (با connection.Close() یا با استفاده از using statement)، ADO.NET به جای اینکه اتصال را واقعاً به سرور پایگاه داده ببندد، آن را به یک “Pool” یا “استخر” از اتصالات بازگردانی می‌کند. دفعه بعد که یک اتصال با همان Connection String درخواست می‌شود، ADO.NET یک اتصال موجود و آماده از Pool را برمی‌گرداند، به جای اینکه یک اتصال جدید ایجاد کند. این فرآیند بسیار سریع‌تر است.

• مزایا:
  • افزایش عملکرد: کاهش زمان لازم برای برقراری اتصال.
  • بهره‌وری منابع: تعداد اتصالات باز به پایگاه داده را بهینه می‌کند و از ایجاد تعداد زیادی اتصال اضافی جلوگیری می‌کند.
  • مقیاس‌پذیری: به برنامه‌ها اجازه می‌دهد تا تعداد زیادی از درخواست‌های همزمان را بدون اشباع سرور پایگاه داده مدیریت کنند.
• نکات مهم:
  • همیشه اتصالات را پس از استفاده ببندید (با Close() یا Dispose()). using statement بهترین راه برای اطمینان از این امر است. عدم بستن اتصالات می‌تواند باعث نشت اتصالات در Pool و در نهایت ایجاد مشکل شود.
  • Connection Pooling بر اساس Connection String کار می‌کند. اگر Connection Stringها متفاوت باشند (حتی یک فاصله اضافی)، اتصالات در Poolهای جداگانه قرار می‌گیرند.
  • می‌توانید تنظیمات Connection Pooling را در Connection String خود تغییر دهید (مثلاً Max Pool Size، Min Pool Size، Pooling=False). اما معمولاً تنظیمات پیش‌فرض برای اکثر سناریوها مناسب است.

الگوهای طراحی (Design Patterns):

استفاده از الگوهای طراحی مناسب می‌تواند به سازماندهی، تست‌پذیری و قابلیت نگهداری کد تعامل با پایگاه داده کمک کند.

• الگوی Repository (Repository Pattern):

  این الگو یک لایه انتزاعی بین لایه منطق کسب‌وکار و لایه دسترسی به داده‌ها ایجاد می‌کند. Repository یک مجموعه از موجودیت‌ها را به روشی شبیه به مجموعه‌ها در حافظه (مانند List<T>) نمایش می‌دهد. به جای اینکه منطق کسب‌وکار مستقیماً با DbContext (در EF Core) یا SqlConnection (در ADO.NET) تعامل داشته باشد، با یک رابط Repository تعامل می‌کند. این باعث می‌شود کد کسب‌وکار مستقل از جزئیات پیاده‌سازی پایگاه داده شود و تست‌های واحد (Unit Tests) آسان‌تر انجام شوند.

  // Interface
  public interface IEmployeeRepository
  {
      Employee GetById(int id);
      IEnumerable<Employee> GetAll();
      void Add(Employee employee);
      void Update(Employee employee);
      void Delete(int id);
  }
  
  // Implementation (using EF Core)
  public class EmployeeRepository : IEmployeeRepository
  {
      private readonly CompanyDbContext _context;
  
      public EmployeeRepository(CompanyDbContext context)
      {
          _context = context;
      }
  
      public Employee GetById(int id) => _context.Employees.Find(id);
      public IEnumerable<Employee> GetAll() => _context.Employees.ToList();
      public void Add(Employee employee)
      {
          _context.Employees.Add(employee);
          _context.SaveChanges();
      }
      public void Update(Employee employee)
      {
          _context.Employees.Update(employee);
          _context.SaveChanges();
      }
      public void Delete(int id)
      {
          var employee = _context.Employees.Find(id);
          if (employee != null)
          {
              _context.Employees.Remove(employee);
              _context.SaveChanges();
          }
      }
  }
          

• الگوی Unit of Work (Unit of Work Pattern):

  اغلب با الگوی Repository همراه است. Unit of Work گروهی از عملیات Repository را در یک تراکنش منطقی واحد کپسوله می‌کند. این تضمین می‌کند که تمام عملیات با هم موفق می‌شوند یا با هم شکست می‌خورند (Atomic). به جای فراخوانی SaveChanges() در هر عملیات Repository، شما یک شیء Unit of Work را در اختیار دارید که مسئول مدیریت تراکنش و فراخوانی SaveChanges() یک بار برای همه تغییرات است.

  // Interface
  public interface IUnitOfWork : IDisposable
  {
      IEmployeeRepository Employees { get; }
      int Complete(); // می‌تواند SaveChanges() باشد
  }
  
  // Implementation
  public class UnitOfWork : IUnitOfWork
  {
      private readonly CompanyDbContext _context;
      public IEmployeeRepository Employees { get; private set; }
  
      public UnitOfWork(CompanyDbContext context)
      {
          _context = context;
          Employees = new EmployeeRepository(_context);
      }
  
      public int Complete()
      {
          return _context.SaveChanges();
      }
  
      public void Dispose()
      {
          _context.Dispose();
      }
  }
          

  این الگوها به ویژه در برنامه‌های بزرگ و پیچیده مفید هستند.

برنامه‌نویسی ناهمگام (Asynchronous Programming – Async/Await):

در برنامه‌های مدرن C# (به ویژه برنامه‌های UI و وب)، استفاده از عملیات ناهمگام (asynchronous) برای تعامل با پایگاه داده ضروری است. این کار برنامه شما را واکنش‌پذیر (responsive) نگه می‌دارد و مقیاس‌پذیری آن را بهبود می‌بخشد.

• مزایا:
  • واکنش‌پذیری UI: در برنامه‌های دسکتاپ (WPF, Windows Forms)، عملیات پایگاه داده می‌تواند طولانی باشد. استفاده از async/await باعث می‌شود UI مسدود نشود و کاربر بتواند به کار خود ادامه دهد.
  • مقیاس‌پذیری سرور: در برنامه‌های وب (ASP.NET Core)، عملیات I/O (مانند فراخوانی پایگاه داده) بخش قابل توجهی از زمان درخواست را به خود اختصاص می‌دهد. با استفاده از async/await، threadی که در حال انتظار برای پاسخ پایگاه داده است، آزاد می‌شود تا درخواست‌های دیگر را مدیریت کند. این تعداد درخواست‌هایی که سرور می‌تواند به طور همزمان پردازش کند را افزایش می‌دهد.
• پیاده‌سازی:

  تقریباً تمام متدهای ADO.NET (OpenAsync(), ExecuteReaderAsync(), ExecuteNonQueryAsync(), ExecuteScalarAsync()) و EF Core (ToListAsync(), FirstOrDefaultAsync(), AddAsync(), SaveChangesAsync()) نسخه‌های ناهمگام دارند.

  using System.Data.SqlClient;
  using System.Configuration;
  using System.Threading.Tasks; // برای Task
  
  public class AsyncDataAccess
  {
      private string connectionString;
  
      public AsyncDataAccess()
      {
          this.connectionString = ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;
      }
  
      public async Task<List<Employee>> GetEmployeesAsync()
      {
          List<Employee> employees = new List<Employee>();
          string query = "SELECT EmployeeID, FirstName, LastName, Department, Salary FROM Employees";
  
          using (SqlConnection connection = new SqlConnection(connectionString))
          {
              using (SqlCommand command = new SqlCommand(query, connection))
              {
                  await connection.OpenAsync(); // عملیات ناهمگام
                  using (SqlDataReader reader = await command.ExecuteReaderAsync()) // عملیات ناهمگام
                  {
                      while (await reader.ReadAsync()) // عملیات ناهمگام
                      {
                          employees.Add(new Employee
                          {
                              EmployeeID = reader.GetInt32(reader.GetOrdinal("EmployeeID")),
                              FirstName = reader.GetString(reader.GetOrdinal("FirstName")),
                              LastName = reader.GetString(reader.GetOrdinal("LastName")),
                              Department = reader.IsDBNull(reader.GetOrdinal("Department")) ? null : reader.GetString(reader.GetOrdinal("Department")),
                              Salary = reader.GetDecimal(reader.GetOrdinal("Salary"))
                          });
                      }
                  }
              }
          }
          return employees;
      }
  
      public async Task<int> AddEmployeeAsync(Employee newEmployee)
      {
          string query = "INSERT INTO Employees (FirstName, LastName, Department, Salary) VALUES (@FirstName, @LastName, @Department, @Salary); SELECT SCOPE_IDENTITY();";
          int newEmployeeId = -1;
  
          using (SqlConnection connection = new SqlConnection(connectionString))
          {
              using (SqlCommand command = new SqlCommand(query, connection))
              {
                  command.Parameters.AddWithValue("@FirstName", newEmployee.FirstName);
                  command.Parameters.AddWithValue("@LastName", newEmployee.LastName);
                  command.Parameters.AddWithValue("@Department", newEmployee.Department ?? (object)DBNull.Value);
                  command.Parameters.AddWithValue("@Salary", newEmployee.Salary);
  
                  await connection.OpenAsync();
                  object result = await command.ExecuteScalarAsync();
                  if (result != null && result != DBNull.Value)
                  {
                      newEmployeeId = Convert.ToInt32(result);
                  }
              }
          }
          return newEmployeeId;
      }
  
      // مثال با EF Core:
      // public async Task<List<Employee>> GetAllEmployeesAsync()
      // {
      //     return await _context.Employees.ToListAsync();
      // }
  
      // public async Task AddEmployeeAsync(Employee newEmployee)
      // {
      //     await _context.Employees.AddAsync(newEmployee);
      //     await _context.SaveChangesAsync();
      // }
  }
          

تراکنش‌ها (Transactions):

تراکنش‌ها برای حفظ یکپارچگی داده‌ها (Data Integrity) در پایگاه داده بسیار مهم هستند. یک تراکنش مجموعه‌ای از عملیات پایگاه داده است که به صورت یک واحد اتمی (Atomic) در نظر گرفته می‌شود: یا همه آن‌ها با موفقیت کامل می‌شوند (Commit) یا هیچ‌کدام (Rollback).

• خواص ACID:
  • Atomicity (اتمیک بودن): همه یا هیچ.
  • Consistency (سازگاری): تراکنش پایگاه داده را از یک حالت سازگار به حالت سازگار دیگری می‌برد.
  • Isolation (ایزولاسیون): عملیات یک تراکنش از عملیات تراکنش‌های دیگر جدا به نظر می‌رسند.
  • Durability (پایداری): تغییرات اعمال شده توسط یک تراکنش کامیت شده، دائمی هستند.
• پیاده‌سازی در ADO.NET:

  از کلاس SqlTransaction استفاده کنید.

  using System.Data.SqlClient;
  using System.Configuration;
  
  public class TransactionDataAccess
  {
      private string connectionString;
  
      public TransactionDataAccess()
      {
          this.connectionString = ConfigurationManager.ConnectionStrings["CompanyDBConnection"].ConnectionString;
      }
  
      public void TransferFunds(int fromEmployeeId, int toEmployeeId, decimal amount)
      {
          using (SqlConnection connection = new SqlConnection(connectionString))
          {
              connection.Open();
              SqlTransaction transaction = connection.BeginTransaction(); // شروع تراکنش
  
              try
              {
                  // کسر از حساب اول
                  using (SqlCommand command1 = new SqlCommand("UPDATE Employees SET Salary = Salary - @Amount WHERE EmployeeID = @FromId", connection, transaction))
                  {
                      command1.Parameters.AddWithValue("@Amount", amount);
                      command1.Parameters.AddWithValue("@FromId", fromEmployeeId);
                      int rowsAffected1 = command1.ExecuteNonQuery();
                      if (rowsAffected1 == 0) throw new Exception("کارمند مبدأ یافت نشد یا مقدار کافی در حساب نیست.");
                  }
  
                  // افزودن به حساب دوم
                  using (SqlCommand command2 = new SqlCommand("UPDATE Employees SET Salary = Salary + @Amount WHERE EmployeeID = @ToId", connection, transaction))
                  {
                      command2.Parameters.AddWithValue("@Amount", amount);
                      command2.Parameters.AddWithValue("@ToId", toEmployeeId);
                      int rowsAffected2 = command2.ExecuteNonQuery();
                      if (rowsAffected2 == 0) throw new Exception("کارمند مقصد یافت نشد.");
                  }
  
                  transaction.Commit(); // کامیت کردن تراکنش در صورت موفقیت هر دو عملیات
                  Console.WriteLine($"انتقال {amount:C} از کارمند ID {fromEmployeeId} به ID {toEmployeeId} با موفقیت انجام شد.");
              }
              catch (Exception ex)
              {
                  transaction.Rollback(); // بازگرداندن تغییرات در صورت بروز خطا
                  Console.WriteLine($"خطا در انتقال وجه: {ex.Message}. تراکنش بازگردانده شد.");
              }
          }
      }
  }
          

• پیاده‌سازی در EF Core:

  EF Core به صورت پیش‌فرض از تراکنش‌ها برای SaveChanges() پشتیبانی می‌کند. برای گروه‌بندی چندین عملیات در یک تراکنش صریح، از Database.BeginTransaction() استفاده کنید.

  // using (var transaction = _context.Database.BeginTransaction())
  // {
  //     try
  //     {
  //         _context.Employees.Add(employee1);
  //         _context.Employees.Add(employee2);
  //         _context.SaveChanges();
  //         transaction.Commit();
  //     }
  //     catch (Exception)
  //     {
  //         transaction.Rollback();
  //         throw;
  //     }
  // }
          

لاگین و مانیتورینگ (Logging and Monitoring):

داشتن دید نسبت به عملیات پایگاه داده برای عیب‌یابی، بهینه‌سازی و تشخیص مشکلات امنیتی حیاتی است.

• Logging در برنامه C#:

  از فریم‌ورک‌های لاگ‌برداری (مانند Serilog, NLog, log4net) برای ثبت جزئیات عملیات پایگاه داده (مانند زمان شروع/پایان کوئری، پارامترها، مدت زمان اجرا، خطاها) استفاده کنید. این به شما کمک می‌کند تا مشکلات عملکردی را شناسایی کرده و خطاهای پایگاه داده را ردیابی کنید.

• SQL Server Profiler / Extended Events:

  از ابزارهای مانیتورینگ SQL Server مانند SQL Server Profiler (یا جایگزین مدرن‌تر آن، Extended Events) برای مشاهده کوئری‌هایی که به سرور ارسال می‌شوند، زمان اجرای آن‌ها، استفاده از CPU/Disk و سایر رویدادهای پایگاه داده استفاده کنید. این ابزارها برای تشخیص گلوگاه‌های عملکردی در سمت پایگاه داده ضروری هستند.

• Application Performance Monitoring (APM):

  ابزارهای APM (مانند Application Insights، New Relic، Dynatrace) می‌توانند عملکرد کلی برنامه شما را مانیتور کنند، از جمله زمان پاسخگویی پایگاه داده، تعداد فراخوانی‌ها و خطاهای مرتبط با DB.

با پیاده‌سازی این بهترین روش‌ها، شما نه تنها برنامه‌های C# خواهید ساخت که با SQL Server تعامل دارند، بلکه برنامه‌هایی را خواهید ساخت که قوی، قابل نگهداری، ایمن و با عملکرد بالا هستند.

در این راهنمای جامع، ما سفر خود را از مفاهیم پایه‌ای اتصال C# به SQL Server آغاز کردیم و گام به گام به سمت تکنیک‌های پیشرفته‌تر حرکت کردیم. از آشنایی با پیش‌نیازها و ابزارهای لازم، تا غواصی عمیق در ADO.NET با رویکردهای متصل و قطع‌اتصال، و سپس کشف قدرت Stored Procedures و انقلاب ORM با Entity Framework Core، سعی کردیم تمام جنبه‌های حیاتی این ارتباط را پوشش دهیم.

ما آموختیم که چگونه از SqlConnection برای مدیریت اتصالات استفاده کنیم و چقدر using statement در این زمینه حیاتی است. با SqlDataReader، سرعت بازیابی داده‌ها را تجربه کردیم و با ExecuteNonQuery، عملیات تغییر داده را انجام دادیم. سپس، با DataSet و SqlDataAdapter، با مفهوم کار آفلاین با داده‌ها آشنا شدیم و نحوه مدیریت دسته‌ای تغییرات را فرا گرفتیم. اهمیت Stored Procedures در افزایش امنیت، عملکرد و قابلیت نگهداری را درک کردیم و در نهایت، با Entity Framework Core، گام بزرگی به سمت مدل‌سازی شیءگرای داده‌ها و افزایش بهره‌وری توسعه برداشتیم.

همچنین، تأکید ویژه‌ای بر جنبه‌های حیاتی مدیریت خطا و امنیت داشتیم، از جمله جلوگیری از حملات SQL Injection با استفاده از پارامترها، ذخیره‌سازی ایمن Connection Stringها و رعایت اصل حداقل دسترسی. در نهایت، با بررسی بهترین روش‌ها و نکات پیشرفته مانند Connection Pooling، الگوهای طراحی Repository و Unit of Work، برنامه‌نویسی ناهمگام (Async/Await) و اهمیت تراکنش‌ها و لاگ‌برداری، راه را برای ساخت برنامه‌های کاربردی قدرتمند و مقیاس‌پذیر هموار ساختیم.

دنیای توسعه پایگاه داده همواره در حال تحول است، و این راهنما تنها نقطه شروعی برای سفر یادگیری شماست. با تمرین مستمر، کاوش در مستندات رسمی مایکروسافت، و به‌روز ماندن با آخرین تکنولوژی‌ها و بهترین روش‌ها، می‌توانید به یک توسعه‌دهنده C# ماهر در زمینه پایگاه داده تبدیل شوید. به یاد داشته باشید که انتخاب بهترین روش برای اتصال به پایگاه داده بستگی به نیازهای خاص پروژه، عملکرد مورد انتظار و ترجیحات تیم توسعه شما دارد. با این دانش، شما مجهز به ابزارهای لازم برای ساخت برنامه‌هایی هستید که نه تنها کار می‌کنند، بلکه به صورت بهینه، امن و قابل نگهداری نیز هستند.