ایجاد یک پکیج SSIS ساده

در این بخش، نگاهی خواهیم انداخت به ایجاد یک پکیج SSIS ساده از scratch. یه عنوان مثال، یک spreadsheet اکسل generate خواهیم کرد که می توان برای وارد کردن پیش بینی فروش در هر شهر و ایالت به طور دستی استفاده کرد. اطلاعات شهر و ایالت را از دیتابیس AdventureWorksDW استخراج می کنیم و به یک spreadsheet اکسل خروجی می کنیم. بخش فروش می تواند از این اطلاعات برای وارد کردن پیش بینی استفاده کند. پکیج SSIS دیگری داده ها را از spreadsheet ایمپورت و جدولی را در دیتابیس AdventureWorksDW آپدیت خواهد کرد.

در این بخش مراحل زیر را مورد بررسی قرار خواهیم داد:

• ایجاد یک پکبج SSIS جدید و بررسی بعضی از پراپرتی های پکیج
• اضافه کردن Connection Managers به منبع و مقصد داده ها
• اضافه کردن تسک به پکیج Control Flow
• اضافه کردن تسک به پکیج Data Flow
• اجرای پکیج در BIDS

ایجاد یک پکیج SSIS جدید

در این قسمت، مروری خواهیم داشت بر ایجاد یک پکیج SSIS جدید و اضافه کردن آن به یک پروژه موجود. در ضمن در مورد بعضی از پراپرتی های مهم پکیج نیز بحث خواهیم کرد.

برای شروع، BIDSرا با انتخاب SQL Server Business Intelligence Development Studio از گروه برنامه Microsoft SQL Server باز کنید. . در منوی بالا، روی File، Open، Project/Solution، کلیک کنید تا پنجره Open Project نمایش داده شود. به مکان سلوشن بروید و روی Open کلیک کنید، مانند شکل زیر:

در Solution Explorer این پنجره را باید ببینید:

برای اضافه کردن پکیج SSIS جدید، روی ند زیر Tutorial-Sample-1 project کلیک راست و New SSIS Package را از منوی popup انتخاب کنید. پکیجی جدید زیر ند SSIS Packages ایجاد خواهد شد و نامش Package1.dtsx (یا چیزی مشابه) خواهد بود. روی Package1.dtsx کلیک راست کنید، گزینه Rename را از منوی popup انتخاب و عبارت CreateSalesForecastInput را بعنوان نام جدید برای پکیج وارد کنید.

حالا designer، یک Control Flow خالی نمایش خواهد داد. در بخش های بعدی، تسک هایی را به این design surface درگ و دراپ خواهیم کرد تا spreadsheet اکسل را generate کنیم. فعلاً در هر جایی از دیزاینر Control Flow که کلیک کنید، پنجره پراپرتی های پکیج ظاهر خواهد شد.

پنجره پراپرتی های پکیج حاوی پراپرتی های زیادی است که می توان برای پکیج تنظیم کرد. شکل زیر، نمونه ای از این پراپرتی هاست:

در ادامه به سه تا از این پراپرتی ها خواهیم پرداخت:

• ProtectionLevel

• IsolationLevel
• TransactionOption

ProtectionLevel، آپشن های مختلفی را برای رمزگذاری (encryption) پکیج و یا بخش های پکیج در اختیار می گذارد. من به EncryptSensitiveWithUserKey می پردازم زیرا مقدار پیش فرض همیشه باعث ایجاد مشکل می شود. بعنوان مثال می توان به رشته کانکشن دیتابیس (database connection string) اشاره کرد که حاوی یک پسورد است. تنظیمات پیش فرض، این اطلاعات را با استفاده از user key شخصی که پکیج را ایجاد کرده، رمزگذاری می کند. وقتی کاربری دیگر پکیج را اجرا می کند، پکیج fail خواهد شد زیرا آن user key قادر به رمزگشایی connection string نخواهد شد. یک راه حل خوب، تغییر دادن ProtectionLevel به DontSaveSensitive است، بدین معنی که قرار نیست اطلاعات حساس را در پکیج بگذارید، پس دیگر نیاز نیست نگران رمزگذاری باشید.

IsolationLevel، سطح Transaction Isolation را تنظیم می کند. توجه داشته باشید که مقدار پیش فرض، Serializable است که ممکن است چیزی نباشد که واقعاً احتیاج دارید. Serializable، سطحی است که قفل های read تا وقتیکه یک تراکنش commit یا rollback شود، در آن نگهداشته می شوند. بعلاوه، قفل های range طوری نگه داری می شوند که هیچ داده ایی را نمی توان insert کرد. مطمئناً زمان هایی وجود دارد که این transaction isolation level گارانتی می شود، اما همیشه این طور نیست. سطح Serializable معمولاً منجر به قفل کردن اضافی دارد و همزمانی (concurrency) را کاهش می دهد، پس باید ببینید آیا واقعاً به آن احتیاج دارید و یکی از سطوح دیگر از قبیل ReadCommitted را انتخاب کنید.

TransactionOption، به شما اجازه انتخاب چگونگی رفتار پکیج در مواجه با تراکنش های دیتابیس را می دهد. مقدار پیش فرض Supported احتمالاً مقدار خوبی است. اگر پکیجی درون تراکنشی اجرا شود که از قبل وجود دارد (مثلاً از پکیجی دیگر) آن پکیج به تراکنشی ملحق خواهد شد که قطعاً بطور پیش فرض، معنی دار است. آپشن های دیگر، Required و NotSupported هستند. Required بدین معنی است که پکیج همیشه درون یک تراکنش اجرا خواهد شد؛ و NotSupported بدین معنی است که پکیج به یک تراکنش موجود ملحق نمی شود.

Connection Managerها

این بخش، مرحله دوم در ایجاد پکیج SSIS ساده از scratch است. پکیج SSIS معمولاً با انواع منبع داده ها در تعامل است. همه منبع داده های رایج، درست بیرون از box ساپورت می شوند. یک ناحیه Connection Managers روی design surface وجود دارد که می توانید هر منبع داده ای را که به آن دسترسی دارید، تعیین کنید. در این بخش، دو منبع داده به پکیج مان اضافه خواهیم کرد- یکی برای دسترسی به دیتابیس AdventureWorksDW و دیگری برای دسترسی به spreadsheet اکسل.

برای شروع، BIDSرا با انتخاب SQL Server Business Intelligence Development Studio از گروه برنامه Microsoft SQL Server باز کنید. . در منوی بالا، روی File، Open، Project/Solution، کلیک کنید تا پنجره Open Project نمایش داده شود. به مکان سلوشن بروید و روی Open کلیک کنید، مانند شکل زیر:

ند پکیج SSIS را در زیر پروژه SSMS-Samples درSolution Explorer بسط دهید و شکل زیر را خواهید دید:

روی پکیج CreateSalesForecastInput.dtsx دابل کلیک کنید تا پکیج باز شود. ناحیه Connection Managers را باید در وسط صفحه ببینید:

ما قصد داریم یک connection manager به دیتابیس AdventureWorksDW و یکی دیگر به spreadsheet اکسل، که ایجاد و به عنوان ورودی پیش بینی فروش خواهیم کرد، اضافه کنیم. برای اضافه کردن کانکشن منیجر AdventureWorksDW، کافیست در ناحیه Connection Managers کلیک و New OLEDB Connection را از منوی popup انتخاب کنیم. پنجره Configure OLEDB Connection Manager نمایش داده خواهد شد؛ روی دکمه New کلیک کنید تا پنجره Connection Manager dialog نمایش داده شود، و آنرا بترتیب زیر پر کنید:

در اینجا، دیتابیس AdventureWorksDW، لوکال ماشین من است، در صورت نیاز، پراپرتی نام Server را برای محیط خودتان عوض کنید. در صورت امکان، گزینه Use Windows Authentication را انتخاب کنید تا از تعیین user id و password بی نیاز شوید، این کار، "اطلاعات حساس" محسوب می شود و باید رمزگذاری شود. روی دکمه Test Connection کلیک کنید تا مطمئن شوید می توانید به دیتابیس وصل شوید. روی OK کلیک کنید تا این مرحله تمام شود.

برای اضافه کردن یک connection manager به spreadsheet اکسل، در ناحیه Connection Managers کلیک راست کنید و New Connection را از منوی popup انتخاب کنید، سپس EXCEL را از پنجره Add SSIS Connection Manager انتخاب کنید. پنجره Excel Connection Manager نمایش داده خواهد شد؛ مانند شکل زیر نام فایل را وارد کنید.

الان، مراحل اضافه کردن Connection Managers لازم به پکیج مان تمام شده است.

Control Flow

این بخش، مرحله سوم در ایجاد ایجاد پکیج SSIS ساده از scratch است. در این بخش نگاهی به Control Flow می اندازیم که حاوی تسک های مختلفی است که پکیج اجرا می کند.

برای شروع، BIDSرا با انتخاب SQL Server Business Intelligence Development Studio از گروه برنامه Microsoft SQL Server باز کنید. . در منوی بالا، روی File، Open، Project/Solution، کلیک کنید تا پنجره Open Project نمایش داده شود. به مکان سلوشن بروید و روی Open کلیک کنید، مانند شکل زیر:

ند پکیج SSIS را در زیر پروژه SSMS-Samples درSolution Explorer بسط دهید و شکل زیر را خواهید دید:

روی پکیج CreateSalesForecastInput.dtsx دابل کلیک کنید تا پکیج باز شود. حالا باید Toolbox را در سمت چپ صفحه ببینید، مانند شکل زیر:

همانطور که می بینید، چند تسک درونی در دسترس است. برای پکیج فعلی، ما فقط به تسک Data Flow نیاز داریم. Data Flow Task را از Toolbox به دیزاینر Control Flow درگ و دارپ کنید. بعد از انجام این کار، دیزاینر Control Flow چیزی شبیه این خواهد بود:

درون مستطیل Data Flow Task کلیک راست کنید، سپس Rename را از منوی popup انتخاب کنید. عبارت Create Sales Forecast Input Spreadsheet را بعنوان نام جدید تسک، انتخاب کنید.

دیزاینر حاوی تب Data Flow است که design surface دیگری را نمایش می دهد که می توانید تسک هایی را که می خواهید data flow اجرا کند، درگ و دراپ کنید.

Data Flow

این بخش، مرحله چهارم در ایجاد ایجاد پکیج SSIS ساده از scratch است. در این بخش نگاهی به دیزاینر Data Flow می اندازیم که حاوی تسک های مختلفی است که توسط تسک Data Flowکه در Control Flow مشخص شده اند، اجرا خواهد شد.

برای شروع، BIDSرا با انتخاب SQL Server Business Intelligence Development Studio از گروه برنامه Microsoft SQL Server باز کنید. . در منوی بالا، روی File، Open، Project/Solution، کلیک کنید تا پنجره Open Project نمایش داده شود. به مکان سلوشن بروید و روی Open کلیک کنید، مانند شکل زیر:

ند پکیج SSIS را در زیر پروژه SSMS-Samples درSolution Explorer بسط دهید و شکل زیر را خواهید دید:

روی پکیج CreateSalesForecastInput.dtsx دابل کلیک کنید تا پکیج باز شود. روی تب Data Flow کلیک کنید، حالا باید دیزاینر خالی Data Flow را ببینید، مانند شکل زیر:

Toilbox، تسک های دردسترس را در Data Flow نمایش خواهد داد:

همانطور که می بینید، چند تسک درونی در دسترس است. برای پکیج فعلی، قصد داریم همان داده ها را از دیتابیس AdventureWorksDW بازیابی کنیم و به spreadsheet اکسل خروجی کنیم. برای انجتم این کار، نیاز به یک OLE DB Source و یک Excel Destination داریم. بعلاوه، باید ستونی را به Data Flow اضافه کنیم؛ این ستون، ستونی عددی در spreadsheet خواهد بود که کاربر مقدار پیش بینی را وارد خواهد کرد.

OLE DB Source، Derived Column، و Excel Destination را از Toolbox به دیزاینر Data Flow درگ و دراپ کنید. بعد از انجام این کار، دیزاینر Data Flow باید مانند شکل زیر شود:

آیکون های قرمز درون تسک ها، نشان می دهند که به کمی پیکربندی نیاز داریم. روی OLE DB Source کلیک راست کنید، سپس Edit را از منوی popup انتخاب کنید. این کار OLE DB Source Editor را روی صفحه Connection Manager باز خواهد کرد. فیلد را مانند شکل زیر پر کنید:

ما OLD DB connection manager را قبلاً در مرحله Connection Managers تنظیم کرده ایم.

SQL command text حاوی یک کوئری ساده برای بازیابی لیست شهرها و ایالت ها در ایالات متحده است.

روی Columns کلیک کنید تا column mappings مانند شکل زیر ظاهر نمایش داده شود:

بطور پیش فرض، همه ستون های بازگشته از کوئری انتخاب می شوند و نام های Output همان ها هستند. نام های Output Column را می توان ویرایش کرد؛ ولی ما آنها را همانطور که هستند، باثی می گذاریم. روی OK کلیک کنید تا OLE DB Source Editor تکمیل شود. حالا یک خط سبز و قرمز می بینید که از تسک OLE DB Source روی دیزاینر Data Flow معلق است. خط سبز، data flow نرمال، و خط قرمز error flow است. خط سبز را درگ کنید و به ستون Derived Column وصل کنید، همانطور که در زیر نشان داده است:

توجه داشته باشید که آیکون قرمز دیگر از تسک OLE DB Source محو شده و حالا بطور صحیحی پیکربندی شده است.

تسک Derived Column به ما اجازه اضافه کردن ستونی به Data Flow را میدهد. روی آن کلیک راست کنید، Edit را انتخاب و سپس Derived Column transformation Editor را مانند زیر پر کنید:

حالا یک ستون currency جدید با نام Forecast و مقدار 0، به data flow اضافه کرده ایم. روی تسک Derived Column کبیک کنید و فلش سبز را درگ کنید تا به تسک Excel Destination متصل شود.

روی تسک Excel Destination کلیک راست کنید و Edit را از منوی popup انتخاب کنید. پنجره Excel Destination Editor نمایش داده خواهد شد. روی دکمه New کنار نام Excel Sheet کلیک کنید تا پنجره Create Table مانند زیر نمایش داده شود:

پنجره Create Table، به ما اجازه ایجاد یک جدول جدید در Excel spreadsheet را میدهد. ستون ها و typeهایشان توسط data flow تعیین میشوند. ما یک تسک OLE DB Source پیکربندی کردیم که یک کوئری و یک تسک Derived Column را، که ستون Forecast را به data flow اضافه کرد، اجرا میکند.در صورت تمایل میتوانید اسکریپت CREATE TABLE را ویرایش کنید. روی پنجره Create Table کلیک کنید و صفحه Excel Destination Editor Connection Manager شبیه تصویر زیر خواهد شد:

ما قبلاً Excel Connection Manager را پیکربندی کرده ایم. روی Mappings کلیک کنید تا صفحه Mappings همانطور که در زیر نشان داد ه شده، نمایش داده شود:

پنجره بالا، نام های Destination Column را بطور پیش فرض به نام های Input Column تبدیل میکند.

تا اینجا، ما Data Flow را تکمیل کرده ایم. توجه داشته باشید که دیگر هیچ آیکون قرمزی در مستطیل های تسک وجود ندارد. در بخش بعدی مقاله، پکیج را اجرا خواهیم کرد.

اجرا کردن پکیج در BIDS

برای شروع، BIDSرا با انتخاب SQL Server Business Intelligence Development Studio از گروه برنامه Microsoft SQL Server باز کنید. . در منوی بالا، روی File، Open، Project/Solution، کلیک کنید تا پنجره Open Project نمایش داده شود. به مکان سلوشن بروید و روی Open کلیک کنید، مانند شکل زیر:

ند پکیج SSIS را در زیر پروژه Tutorial-Sample-1 درSolution Explorer بسط دهید و شکل زیر را خواهید دید:

روی پکیج CreateSalesForecastInput.dtsx کلیک کنید و Execute Package را از منوی popup انتخاب کنید. هنگامیکه پکیج در حال اجراست، خواهید دید که اگر هر تسکی با موفقیت تکمیل شود سبز و در صورت عدم موفقیت قرمز میشود. در زیر، تصویر Control Flow بعد از اینکه پکیج با موفقیت اجرا شد، نشان داده شده:

در زیر، تصویر Data Flow بعد از اینکه پکیج با موفقیت اجرا شد، نشان داده شده:

بعد از تکمیل پکیج، روی Debug در بالای منو کلیک کنید و سپس Stop Debugging را انتخاب کنید.

میتوانیم Excel spreadsheet را که پکیج ایجاد کرد باز کنید و پنجره زیر را ببینید:

Spreadsheet بالا، ستون های کوئری مان بعلاوه ستون Forecast را که با تسک Derived Column اضافه کردیم، نشان میدهد.

نصب (deploy) پکیج SSIS

هنگام نصب پکیج SSIS، آپشن های زیر در اختیار ما هستند:

• نصب روی file system
• نصب روی package store
• نصب روی SQL Server

احتمالاً ساده ترین راه نصب، نصب کردن روی file system است. پکیج SSIS، یک فایل XML است و میتوان بسادگی از موقعیت پروژه اش به پوشه ای در deployment target کپی شود. میتوانید از دستور DOS COPY، یا برنامه Windows Explorer برای اجرای عملیات کپی استفاده کنید. package store، پوشه ای مخصوص در file system است؛ مسیر پیش فرض در SQL Server 2005 عبارت است از:

C:\Program Files\Microsoft SQL Server\90\DTS\Packages

پکیج SSIS نصب شده در SQL Server، در دیتابیس msdb ذخیره میشود.

سه را برای نصب پکیج وجود دارد:

ایجاد یک deployment utility از پروژه مان

استفاده از ابزار خط فرمان DTUTIL

استفاده از SQL Server Management Studio یا (SSMS)

SQL Server Integration Services Deployment Utility

از deployment utility میتوان برای ایجاد instasller پکیج SSIS استفاده کرد. deployment utility، یک ویژگی درونی در پروژه Integration Services است. در این بخش، مراحل فعال سازی آن و ایجاد یک deployment را مرور خواهیم کرد.

برای شروع، BIDSرا با انتخاب SQL Server Business Intelligence Development Studio از گروه برنامه Microsoft SQL Server باز کنید. . در منوی بالا، روی File، Open، Project/Solution، کلیک کنید تا پنجره Open Project نمایش داده شود. به مکان سلوشن بروید و روی Open کلیک کنید، مانند شکل زیر:

به Tutorial-Sample-1 project در Solution Explorer بروید:

روی پروژه Tutorial-Sample-1 کلیک راست کنید و Properties را از منوی popup انتخاب کنید. روی Deployment Utility در لیست Configuration Properties کلیک کنید و پنجره را بترتیب زیر پر کنید:

توجه کنید که پراپرتی CreateDeploymentUtility روی True تنظیم شده؛ که بطور پیش فرض، روی False است. DeploymentOutputPath، تعیین میکند فایل های deployment کجا نصب شوند. مسیر پیش فرض در بالا نشان داده شد و بستگی به پوشه پروژه دارد. روی OK کلیک کنید تا تنظیمات ذخیره شوند.

روی پروژه Tutorial-Sample-1 در Solution Explorer کلیک راست کنید و Build را از منوی popup انتخاب کنید. این کار، پروژه را خواهد ساخت و deployment utility را فراخواهد خواند. اگر همه پکیج های SSIS، در حالت معتبر (valid) باشند، پیام Build Succeeded را در پایین سمت چپ پنجره خواهید دید. به پوشه bin\Deployment زیر پوشه project بروید تا فایل های deployment را مشاهده کنید. فایل های زیر را خواهید دید:

فایل های بالا، نمایانگر deployment است. می توانید آنها را به deployment target کپی کنید و سپس روی فایل Tutorial-Sample-1.SSISDeploymentManifest کلیک کنید تا deployment اجرا شود.

ابزار Command line deployment برای پکیج های SSIS

SQL Server، دارای ابزار خط فرمان DTUTIL.EXE است که می تواند برای نصب پکیج های SSIS مورد استفاده قرار گیرد. هنگامی که میخواهید نصب پکیج های SSIS را اسکریپت کنید، DTUTIL انتخاب خوبی است. میتوان DTUTIL را از یک Command Prompt یا از یک فایل batch اجرا کرد.

برای شروع، Command Prompt را باز کنید و به پوشه Tutorial-Sample-1 project بروید:

در مثال زیر، چگونگی نصب پکیج CreateSalesForecastInput.dtsx روی file system، package store، و SQL Server را نشان خواهم داد.

برای نصب روی file system، می توانید از دستور DOS COPY، Windows Explorer، و غیره یا دستور DTUTIL زیر استفاده کنید (همگی روی یک خط):

DTUTIL /FILE CreateSalesForecastInput.dtsx 

       /COPY 

FILE;C:\temp\CreateSalesForecastInput.dtsx

برای نصب روی package store، دستور زیر را تایپ کنید (همگی روی یک خط):

DTUTIL /FILE CreateSalesForecastInput.dtsx 

       /COPY 

DTS;CreateSalesForecastInput

برای نصب روی SQL Server، دستور زیر را تایپ کنید (همگی روی یک خط):

DTUTIL /FILE CreateSalesForecastInput.dtsx 

       /COPY 

       SQL;CreateSalesForecastInput

همانطور که میبینید، دو node زیر Stored Packages وجود دارد: File System و MSDB وجود دارد. File System در واقع،package store با لوکیشن پیش فرض در SQL Server 2005 در C:\Program Files\Microsoft SQL Server\90\DTS\Packages است. MSDB هم دیتابیس MSDB است.

در مثال زیر، میخواهیم پکیج CreateSalesForecastInput.dtsx را از لوکیشنش در پوشه پروژه روی package store و دیتابیس MSDB نصب کنیم.

برای نصب روی package store، روی ند File System کلیک راست کنید و Import package را از منوی popup انتخاب کنید، و آن را مانند شکل زیر پر کنید:

روی OK کلیک کنید تا پکیج import شود.

برای نصب روی دیتا بیس MSDB، روی ند MSDB کلیک راست کنید و Import package را از منوی popup انتخاب کنید، و آن را مانند شکل زیر پر کنید:

اجرای پکیج های SSIS

سه آپشن برای اجرای پکیج SSIS وجود دارد:

• DTEXEC command line utility
• DTEXECUI windows application
• SQL Server Agent

در ادامه، به این سه آپشن خواهیم پرداخت.

استفاده از ابزار خط فرمان (Command line) برای اجرای پکیج های SSIS

SQL Server، حاوی ابزار خط فرمان DTEXEC.EXE است که می تواند برای اجرای پکیج SSIS مورد استفاده قرار گیرد. DTEXEC را میتوان از Command Prompt یا از یک فایل batch اجرا کرد.

برای شروع، Command Prompt را باز کنید و به پوشه Tutorial-Sample-1 project بروید، مانند تصویر زیر:

نیاز نیست، DTEXEC را از پوشه ای که پکیج SSIS در آن قرار دارد اجرا کنید. فرمان زیر را جهت اجرای پکیج CreateSalesForecastInput.dtsx تایپ کنید:

DTEXEC /FILE CreateSalesForecastInput.dtsx 

برای دیدن لیست کامل آپشن های خط فرمان برای DTEXEC، عبارت زیر را تایپ کنید:

DTEXEC /? 

استفاده از Windows application برای اجرای پکیج های SSIS

SQL Server، حاوی ویندوز اپلیکیشن DTEXECUI.EXE است که میتواند برای اجرای پکیج SSIS مورد استفاده قرار گیرد. DTEXECUI ، یک رابط کاربر گرافیکی (graphical user interface) در احتیار میگذارد که می توان برای تعیین آپشن های مختلف مورد استفاده قرار داد که قرار است هنگام اجرای پکیج SSIS تنظیم شوند. میتوانید با دابل کلیک روی فایل پکیج SSIS آنرا باز کنید. همچنین میتوانید DTEXECUI را از Command Prompt باز کنید و سپس پکیج را اجرا کنید.

برای شروع، Windows Explorer یا My Computer را باز کنید و به پوشه Tutorial-Sample-1 project بروید. روی پکیج CreateSalesForecastInput.dtsx دابل کلیک کنید، پنجره زیر را خواهید دید:

همانطور که میبینید، هنگام استفاده از این utility، تنظیمات زیادی وجود دارد. میتوانید تنها با کلیک روی دکمه Execute، پکیج تان را اجرا کنید. همچنین میتوانید با کلیک در صفحات مختلف و وارد کردن تنظیمات خودتان، پکیج را بهینه تر اجرا کنید. بعد از تغییر تنظیمات، روی Command Line کلیک کنید، که به شما خط فرمان DTEXEC را بر اساس تنظیماتی که انتخاب کرده اید، نشان خواهد داد.

زمانبندی پکیج های SSIS با SQL Server Agent

SQL Server Agent، شامل Agent includes the SQL Server Integration Services Package job step type است که به شما اجازه اجرای پکیج SSIS را در یک SQL Server Agent job step میدهد. این کار میتواند هفید باشد زیرا به شما اجازه زمانبندی اجرای پکیج SSIS را میدهد بطوریکه بدون هیچ تعامل دیگری، اجرا میشود.

برای شروع، SSMS را باز کنید، به Database Engine وصل شوید، و به ند SQL Server Agent در Object Explorer بروید. روی ند Jobs راست کلیک کنید و New Job را از منوی popup انتخاب کنید. به صفحه Steps بروید، روی New کلیک کنید، و پنجره ظاهر شده را مطابق شکل زیر پر کنید:

در مثال بالا، پکیجی که قرار است اجرا شود، در SQL Server، نصب (deploy) میشود. همچنین میتوانید پکیج های نصب شده در file system یا SSIS package store را نیز اجرا کنید.

میتوانید یک Proxy ایجاد کنید که به شما اجازه میدهد پرمیشن اختیاری مخصوصی بدهید تا پکیج SSIS را از یک SQL Server Agent job step اجرا کنید.

مرحله اول برای set up یک پراکسی، ایجاد یک credential است. به Security و سپس به Credentials در SSMS Object Explorer بروید و کلیک راست کنید تا یک credential جدید ایجاد شود، مانند شکل زیر:

به SQL Server Agent و سپس به Proxies در SSMS Object Explorer بروید و راست کلیک کنید تا یک پروکسی جدید، مانند شکل زیر ایجاد کنید:

باید credential را معین کنید و SQL Server Integration Services Package را چک کنید. حالا وقتی که یک SQL Server Agent job step را ایجاد یا ویرایش کردید، میتوانید پروکسی را برای تنظیم Run as، مانند شکل زیر، معین کنید:

مقدمه

SQL Server Integration Services یا SSIS، ابزاری است که برای اجرای عملیات های ETL مانند extract کردن، transform کردن، و load کردن داده ها مورد استفاده قرار می گیرد. درحالیکه فرایند ETL در برنامه های Data Warehousing رایج است، SSIS به هیچ وجه محدود به DW نیست؛ مثلاً وقتی یک با استفاده از SQL Server Management Studio، یک Maintenance Plan ایجاد می کنید، یک پکیج SSIS ایجاد می شود. در سطحی بالاتر، SSIS قابلیت های زیر را در اختیار می گذارد:

• بازیابی داده ها از هر منبعی
• اجرای transformationهای مختلف روی داده ها، مانند تبدیل از نوعی به نوع دیگر، تبدیل حروف بزرگ به کوچک، اجرای محاسبات، و غیره.
• بارگذاری داده ها درون هر منبعی
• تعریف یک جریان کاری (workflow)

نسخه اول SSIS در SQL Server 2005 معرفی شد. SSIS جایگزینی برای Data Transformation Services یا DTS بود که در SQL Server 7.0 و SQL Server 2000 دردسترس بود. SSIS بر اساس قابلیت های معرفی شده در DTS ساخته شده است.

در این مقاله، به موضوعاتی خواهیم پرداخت که برای ساختن موفقیت آمیزیک پکیج SSIS، نیاز به فهم آنها دارید.

مباحث مورد بحث در این مقاله به عبارت زیر است:

• ایجاد پکیج های SSIS با SQL Server Management Studio (SSMS)
• Business Intelligence Development Studio (BIDS)
• ایجاد پکیج ساده در BIDS
• نصب پکیج های SSIS
• اجرای پکیج های SSIS

ایجاد پکیج های SSIS با SQL Server Management Studio

SSMS، تسک های ویزارد Import و Export را، که می توانید برای کپی کردن داده ها از منبعی به منبع دیگر استفاده کنید، در اختیار می گذارد. می توانید از میان چندین گونه مختلف منبع و منبع داده های مقصد، یکی را انتخاب کنید، جداولی را برای کپی کردن انتخاب کنید، یا کوئری خودتان را برای extract کردن داده ها مشخص کنید، و کارتان را بصورت پکیج SSIS ذخیره کنید. استفاده از ویزارد Import و Export، نقطه شروع خوبی برای یادگرفتن پکیج های SSIS است. می توانید پکیج های جنریت شده SSIS را به این صورت اجرا کنید: آنرا زمان بندی کنید بعدا، اجرا شود، یا هر تغییری را که برای برآورده کردن نیازهایتان نیاز است، روی آن ایجاد کنید.

راهی ساده برای export کردن داده ها از SQL Server

SSMS، تسک ویزارد Export را، که می توانید برای کپی کردن داده ها از منبعی به منبع دیگر استفاده کنید، در اختیار می گذارد. می توانید از میان چندین گونه منبع و انواع منبع داده های مقصد، یکی را انتخاب کنید، جداولی را برای کپی کردن انتخاب کنید، یا کوئری خودتان را برای extract کردن داده ها مشخص کنید، و کارتان را بصورت پکیج SSIS ذخیره کنید. در این بخش مروری خواهیم کرد بر Export Wizard و داده ها را از دیتابیس SQL Server به Excel spreadsheet اکسپورت می کنیم.

برای شروع، با کلیک روی SQL Server Management Studio از گروه برنامه Microsoft SQL Server، برنامه SSMS را باز کنید. هنگام باز کردن SSMS، پیامی به شما برای ایجاد یک کانکشن داده می شود، به Database Engine متصل شوید. در این مقاله از دیتابیس AdventureWorksDW استفاده شده است. برای دانلود کردن دیتابیس نمونه AdventureWorksDW، اینجا کلیک کنید.

دیتابیس AdventureWorksDW را در آبجکت اکسپلورر SSMS پیدا کنید؛ اگر Object Explorer را نمی بینید، روی منوی View کلیک و سپس Object Explorer را انتخاب کنید:

در Object Explorer روی دیتابیس AdventureWorksDW کلیک راست کنید، Tasks را، و سپس Export Data را از منوی context انتخاب کنید تا Export Wizard شروع شود. در صورتی که پنجره Welcome نمایش داده شود، روی Next کلیک کنید. در ادامه، مراحل ویزارد را قدم به قدم مرور خواهیم کرد.

یک Data Source انتخاب کنید

پنجره Choose a Data Source به شما اجازه تعیین منبع داده هایتان را می دهد. از آنجاییکه در حال اجرای Export wizard هستیم، این پنجره با مقادیری که از قبل وجود دارد، نمایش داده می شود، مانند شکل زیر:

برای رفتن به مرحله بعد، روی Next کلیک کنید.

یک Destination انتخاب کنید

پنجره Choose a Destination به شما اجازه تعیین منبع داده های مقصد را برای داده هایی که export می کنید، می دهد. تقریباً آپشن های زیادی برای destination وجود دارد، inputهای اضافی بر اساس منبع داده های مقصد انتخاب شده، متنوع خواهد بود. ما برای مثالمان، داده ها را به برنامه اکسل export می کنیم و بعداً از این Excel spreadsheet به عنوان منبع در ویزارد Import استفاده خواهیم کرد. فیلدها را مانند زیر پرکنید:

Table Copy یا Query را مشخص کنید

پنجره Specify Table Copy or Query به شما اجازه انتخاب اکسپورت کردن داده ها با سلکت کردن tables و یا views از منبع داده ها یا مشخص کردن یک کوئری برای extract کردن داده ها را می دهد. Copy data from one or more tables or views را همانطور که در زیر نشان داده شده انتخاب کنید:

برای رفتن به مرحله بعد، روی Next کلیک کنید.

Source Tables and Viewsرا انتخاب کنید

پنجره Select Source Tables and Views به شما اجازه انتخاب tableها و viewهایی را که می خواهید export کنید می دهد. در این اینجا ما جدول DimGeography را همانگونه که در زیر نشان داده شده انتخاب می کنیم:

می توانید روی دکمه Preview کلیک کنید تا 100 ردیف اول داده ها را در منبع داده ها مشاهده کنید:

می توانید روی دکمه Edit Mappings (در پنجره Select Source Tables and Views) کلیک کنید تا column mappings را از منبع داده ها به مقصد داده ها بررسی کنید. اگر جدولی در منبع داده های مقصد وجود نداشته باشد، می توانید mappingها را نیز ویرایش کنید.

می توانید روی Option کلیک کنید تا جدول را در منبع داده های مقصد drop و recreate کنید؛ این آپشن بطور پیش فرض تیک نخورده است. اگر این جدول در منبع داده های مقصد وجود نداشته باشد، دکمه Create destination table radio انتخاب خواهد شد و دکمه های دیگر رادیو غیر فعال خواهند شد. اگر جدول از قبل در منبع داده های مقصد وجود داشته باشد، دکمه Create destination table radio غیرفعال خواهد شد و می توانید یا آپشن Delete یا Append را انتخاب کنید.

می توانید روی دکمه Edit SQL کلیک کنید تا SQL را جهت ایجاد جدول در منبع داده های مقصد بررسی یا ویرایش کنید، مانند شکل زیر:

دوبار روی OK کلیک کنید تا به صفحه Select Source Tables and Views برگردید، سپس برای رفتن به مرحله بعد، روی Next کلیک کنید.

پکیج را Save و Execute کنید

پنجره Save and Execute Package به شما امکان اجرای عملیات اکسپورت و ایجاد پکیج SSIS و ذخیره آن در SQL Server یا file system را می دهد، مانند زیر:

ما پکیج SSIS را طوری ذخیره می کنیم که بتوانیم در بخش های بعدی مقاله مشاهده کنیم. برای رفتن به مرحله بعد، روی Next کلیک کنید.

پکیج SSIS را ذخیره کنید

اگر تصمیم بگیرید عملیات اکسپورت را در پنجره Save and Execute Package بصورت پکیج SSIS ذخیره کنید، Save SSIS Package فراخوانده می شود. پنجره مذکوررا مانند زیر پر کنید:

برای رفتن به مرحله بعد،روی Next کلیک کنید.

ویزارد را کامل کنید

پنجره Complete the Wizard، خلاصه ای از آپشن هایی را نشان می دهد که برای عملیات اکسپورت انتخاب کرده اید، مانند شکل زیر:

روی Finish کلیک کنید تا پکیج SSIS اجرا شود. می توانید Excel spreadsheet را باز کنید و جداولی اکسپورت شده را مشاهده کنید. بخشی از Excel spreadsheet در زیر نشان داده شده:

راهی ساده برای ایمپورت کردن داده ها به SQL Server

SSMS، تسک ویزارد Import را، که می توانید برای کپی کردن داده ها از منبعی به منبع دیگر استفاده کنید، در اختیار می گذارد. می توانید از میان چندین گونه منبع و انواع منبع داده های مقصد، یکی را انتخاب کنید، جداولی را برای کپی کردن انتخاب کنید، یا کوئری خودتان را برای extract کردن داده ها مشخص کنید، و کارتان را بصورت پکیج SSIS ذخیره کنید. در این بخش مروری خواهیم کرد بر Import Wizard و داده ها را از Excel spreadsheet به جدولی در دیتابیس SQL Server ایمپورت می کنیم.

برای شروع، با کلیک روی SQL Server Management Studio از گروه برنامه Microsoft SQL Server، برنامه SSMS را باز کنید. هنگام باز کردن SSMS، پیامی به شما برای ایجاد یک کانکشن داده می شود، به Database Engine متصل شوید.

دیتابیس MSSQLTipsSSISTutorial را در آبجکت اکسپلورر SSMS پیدا کنید (اگر لازم است، این دیتابیس را ایجاد کنید)؛ اگر Object Explorer را نمی بینید، روی منوی View کلیک و سپس Object Explorer را انتخاب کنید:

در Object Explorer روی دیتابیس MSSQLTipsSSISTutorial کلیک راست کنید، Tasks را، و سپس Import Data را از منوی context انتخاب کنید تا Export Wizard شروع شود. در صورتی که پنجره Welcome نمایش داده شود، روی Next کلیک کنید. در ادامه، مراحل ویزارد را قدم به قدم مرور خواهیم کرد.

یک Data Source انتخاب کنید

پنجره Choose a Data Source به شما اجازه تعیین منبع داده هایتان را می دهد. فیلدها را همانطور که در زیر نشان داده شده است، پر کنید تا یک Excel spreadsheet را به عنوان منبع داده هایتان تعیین کنید:

برای رفتن به مرحله بعد، روی Next کلیک کنید.

یک Destination انتخاب کنید

پنجره Choose a Destination به شما اجازه تعیین منبع داده های مقصد را برای داده هایی که import می کنید، می دهد. از آنجاییکه در حال اجرای Import wizard هستیم، این پنجره با مقادیری که از قبل وجود دارد، نمایش داده می شود، مانند شکل زیر:

برای رفتن به مرحله بعد، روی Next کلیک کنید.

Table Copy یا Query را مشخص کنید

پنجره Specify Table Copy or Query به شما اجازه انتخاب ایمپورت کردن داده ها با سلکت کردن tables و یا views از منبع داده ها یا مشخص کردن یک کوئری برای extract کردن داده ها را می دهد. Copy data from one or more tables or views را همانطور که در زیر نشان داده شده انتخاب کنید:

برای رفتن به مرحله بعد، روی Next کلیک کنید.

Source Tables و Views را انتخاب کنید

پنجره Select Source Tables and Views به شما اجازه انتخاب tableها و viewهایی را که می خواهید import کنید می دهد. در این اینجا ما جداول DimGeography را همانگونه که در زیر نشان داده شده انتخاب می کنیم:

می توانید روی دکمه Preview کلیک کنید تا 100 ردیف اول داده ها را در منبع داده ها مشاهده کنید:

می توانید روی دکمه Edit Mappings (در پنجره Select Source Tables and Views) کلیک کنید تا column mappings را از منبع داده ها به مقصد داده ها بررسی کنید. اگر جدولی در منبع داده های مقصد وجود نداشته باشد، می توانید mappingها را نیز ویرایش کنید.

می توانید روی Option کلیک کنید تا جدول را در منبع داده های مقصد drop و recreate کنید؛ این آپشن بطور پیش فرض تیک نخورده است. اگر جدول مقصد دارای ستون identity باشد و منبع داده ها دارای مقادیری باشد که می خواهید برای identity column تخصیص دهید، می توانید روی Enable identity insert کلیک کنید.اگر این جدول در منبع داده های مقصد وجود نداشته باشد، دکمه Create destination table radio انتخاب خواهد شد و دکمه های دیگر رادیو غیر فعال خواهند شد. اگر جدول از قبل در منبع داده های مقصد وجود داشته باشد، دکمه Create destination table radio غیرفعال خواهد شد و می توانید یا آپشن Delete یا Append را انتخاب کنید.

می توانید روی دکمه Edit SQL کلیک کنید تا SQL را جهت ایجاد جدول در منبع داده های مقصد بررسی یا ویرایش کنید، مانند شکل زیر:

دوبار روی OK کلیک کنید تا به صفحه Select Source Tables and Views برگردید، سپس برای رفتن به مرحله بعد، روی Next کلیک کنید.

پکیج را Save و Execute کنید

پنجره Save and Execute Package به شما امکان اجرای عملیات ایمپورت و ایجاد پکیج SSIS و ذخیره آن در SQL Server یا file system را می دهد، مانند زیر:

پکیج SSIS را ذخیره می کنیم. برای رفتن به مرحله بعد، روی Next کلیک کنید.

پکیج SSIS را ذخیره کنید

اگر تصمیم بگیرید عملیات ایمپورت را در پنجره Save and Execute Package بصورت پکیج SSIS ذخیره کنید، Save SSIS Package فراخوانده می شود. پنجره مذکور را مانند زیر پر کنید:

برای رفتن به مرحله بعد، روی Next کلیک کنید.

ویزارد را کامل کنید

پنجره Complete the Wizard، خلاصه ای از آپشن هایی را نشان می دهد که برای عملیات ایمپورت انتخاب کرده اید، مانند شکل زیر:

روی Finish کلیک کنید تا پکیج SSIS اجرا شود. می توانید جدول ایمپورت شده را در SSMS باز و آنها را مشاهده کنید. بخشی از این در زیر نشان داده شده:

Business Intelligence Development Studio (BIDS)

BIDS، ابزاری است که برای develop کردن پکیج های SSIS استفاده می کنیم، که اینترفیسی را بر اساس محصول Visual Studio مایکروسافت در اختیار قرار می دهد که سالهاست مورد استفاده برنامه نویسان قرار گرفته شده است. BIDS، تغییر DTS است که از SQL Server Enterprise Manager بعنوان ابزاری برای طراحی پکیج استفاده می کرد. در حالی که هنوز تفاوتهایی وجود دارد، احتمالاً متوجه خواهید شد که استفاده از BIDS بمراتب آسانتر است، گرچه ممکن است کمی سختی در یادگیری آن وجود داشته باشد. این بخش، مقدمه ایست بر BIDS و مروری بر پکیج SSIS موجود.

BIDS با SQL Server باز می شود. وقتی شما ابزارهای کلاینت را زمان نصب SQL Server انتخاب می کنید، BIDS نیز نصب می شود. بعد از نصب BIDS، می توانید آن را از گروه برنامه های SQL Server باز کنید.

ایجاد projectها و solutionها با SSIS

دیاگرام BIDS برای توسعه پکیج های SSIS براساس مفهوم projectها و solutionهاست. project، دربرگیرنده یک یا چند پکیج SSIS است. می توان از projectها برای ساماندهی پکیج های SSIS استفاده کرد. مثلاً اگر من تعدادی پکیج SSIS داشته باشید که برای بارگذاری data warehouse از یک سیستم منبع مشخص استفاده می شود، ممکن است یک پروژه SSIS برای همه پکیج های SSIS که برای در فرآیند ETL برای آن سیستم منبع استفاده می شود، ایجاد کنم. ممکن است پروژه های SSIS دیگری برای سیستم های منبع مختلف دیگری ایجاد کنم که برای populate کردن data warehouse استفاده شوند.

solution، دربر گیرنده یک یا چندین project است. هنگامی که چندین پروژه SSIS، برای ساماندهی پکیج هایتان ایجاد می کنید، این پکیج ها را با هم به یک یا چند solution ترکیب می کنید. مثلاً اگر من چندین پروژه SSIS داشته باشم که هرکدام نمایانگر یک سیستم منبع هستند، که برای populate کردن data warehouse استفاده می شوند، ممکن است یک solution واحد ایجاد کنم تا همه این پروژه های SSIS مرتبط را نگهداری کنم. در ادامه به چگونگی ایجاد یک project و solution، و اضافه کردن یک پکیج SSIS موجود به پروژه خواهیم پرداخت.

برای شروع، BIDSرا با انتخاب SQL Server Business Intelligence Development Studio از گروه برنامه Microsoft SQL Server باز کنید. روی File، New، Project در منوی بالا کلیک کنید تا پنجره New Project نمایش داده شود. Business Intelligence Projects را بعنوان نوع پروژه، و سپس Integration Services Project را بعنوان template انتخاب کنید؛ فیلدها را مانند شکل زیر پر کنید:

بعد از ایجاد پروژه و سلوشن جدید، می توان به پوشه نمونه (samples folder) در Windows Explorer رفت و شکل زیر را دید:

پوشه نمونه، حاوی فایل سلوشن و پوشه Tutorial-Sample-1 است، که حاوی پروژه من است. پوشه نمونه، در نتیجه کلیک کردن روی Create directory for solution است.

با برگشتن به BIDS، شکل زیر را در Solution Explorerخواهید دید که بالای گوشه راست پنجره وجود دارد:

وقتی یک Integration Services Projectایجاد می کنید، یک پکیج SSIS جدید بطور پیش فرض اضافه می شود؛ می توانید با کلیک راست کردن روی آن نامش را تغییر دهید.

حالا که با موفقیت یک پروژه و سلوشن جدید ایجاد کرده اید، بیایید پروژه جدید دیگری به این سلوشن اضافه کنیم. در منوی File، روی Add، و سپس New Project کلیک کنید، و فیلدها را بصورت زیر پر کنید:

من این پروژه اضافی را ایجاد کرده ام تا پکیج های SSIS را که با اجرای ویزارد Import and Export ایجاد شده است، نگه دارد.این کار، یک پوشه SSMS-Samples ایجاد می کند تا پروژه را نگه دارد؛ این پوشه زیر پوشه نمونه ها است. هنگام ایجاد پروژه های جدید به موارد زیر دقت کنید: مطمئن شوید مکان و نام پروژه همان چیزهایی است که می خواهید. مقادیر پیش فرضی که بطور اتوماتیک در فیلدها وجود دارند، بندرت استفاده می شوند.

حالا وقتی به Solution Explorer درBIDS نگاه می کنیم، شکل زیر را می بینیم:

حالا سلوشن ما حاوی دو پروژه است. ویزاردهای Import و Export، امکان ذخیره کردن کار را بصورت پکیج SSIS می دهد. اگر تصمیم به ذخیره کردن کارتان گرفتید، شاید بخواهید پکیج را به یک پروژه موجود اضافه کنید. روی node پکیج های SSIS، در Solution Explorer کلیک راست کنید، سپس Add Existing Package را انتخاب کنید، و فیلدها را مانند زیر پر کنید:

همانطور که در title پنجره می بینید، شما در حال اضافه کردن یک کپی از پکیج SSIS به پروژه هستید.

مروی بر projectها و solutionها در SSIS

پکیج SSIS شبیه برنامه ایست که بعضی taskهای ویژه را اجرا می کند. یکی از کارهایی که می توانیم با پکیج SSIS دهیم، این است که محتویات جدولی در دیتابیس AdventureWorksDW را به یک Excel spreadsheet کپی کنیم. اینکار را در ابتدای مقاله انجام دادیم و کارمان را بصورت پکیج SSIS ذخیره کردیم. در ادامه آن پکیج را بعنوان راهی برای فهم پکیج SSIS بررسی خواهیم کرد.

برای شروع، BIDSرا با انتخاب SQL Server Business Intelligence Development Studio از گروه برنامه Microsoft SQL Server باز کنید. در منوی بالا، روی File، Open، Project/Solution، کلیک کنید تا پنجره Open Project نمایش داده شود. به مکان سلوشن بروید و روی Open کلیک کنید، مانند شکل زیر:

ند پکیج SSIS را در زیر پروژه SSMS-Samples درSolution Explorer بسط دهید و شکل زیر را خواهید دید:

روی پکیج SSMS-Export-to-Excel.dtsx دابل کلیک کنید و آنرا باز کنید. بیایید نگاهی به جنبه های مختلف پکیج SSIS که در BIDS نمایش داده می شوند، بیندازیم:

• Designer
• Connection Managers
• Toolbox
• Properties Window

designer، ناحیه ای در وسط پنجره است که در زیر نشان داده شدهک

tabهای زیر در designer قابل دسترس هستند:

• تب Control Flow (که در عکس بالا نشان داه شده)، حاوی تسک هاییست که پکیج SSIS اجرا و از تسکی به تسک دیگر flow می کند.
• تب Data Flow (که در عکس پایین نشان داه شده)، designer دیگریست که حاوی جزییات data flow task معینی است؛ مثلاً بازیابی داده ها از منبع داده ها، و در مواردی بعضی transformationها را روی داده ها اجرا می کند، سپس آن را روی منبع داده های دیگر می نویسد.
• تب Event Handlers، نیز designer دیگریست که در آن می توانید هنگامی که event میعنی raise می شود، تسک هایی را برای اجرا شدن معین کنید.
• تب Package Explorer، کل پکیج را بصورت tree-view نمایش می دهد.

شکل زیر، دیزاینر Data Flow برای تسک Data Flow در تب Control Flow است:

پنجره Connection Managers، حاوی منابع داده های مختلف و destinationیی است که از پکیج استفاده می کند:

Connection Managers یکبار تعریف می شوند، و سپس در تسک های مختلفی مانند Execute SQL Task، OLEDB Data، یا OLEDB Destination، ریفرنس می شوند.

Toolbox حاوی تسک هایست که در Control Flow،Data Flow، یا دیزاینرهایEvent Handlers دردسترس هستند. برای ساختن یک پکیج، کافیست تسک هایی را از Toolbox به designer، درگ و آنها را به ترتیبی که می خواهید اجرا کنید، بهم متصل کنید. تسک های Toolbox زیر در تب دیزاینر Control Flow قابل دسترس هستند:

تسک های Toolbox زیر در تب دیزاینر Data Flow قابل دسترس هستند:

Properties Window برای ویرایش و آپدیت کردن پراپرتی های یک تسک در دیزاینر یا یک کانکشن در ناحیه Connection Managers دردسترس است. روی Preparation SQL Task در Control Flow، که در بالا نشان داده شده، کلیک کنید؛ و شکل زیر را در Properties Window خواهید دید:

Preparation SQL Task، یک Execute SQL Task است و برای اجرای عبارات SQL بکار می رود. در اینجا، ما عبارت CREATE TABLE یا stored procedure اجرا می کنیم. وقتی روی یک شی کلیک راست می کنید، یک پنجره popup ظاهر می شود که می توانید برای تنظیم پراپرتی های مختلف استفاده کنید؛ مثلاً روی Preparation SQL Task کلیک راست کنید و Edit را از منو انتخاب کنید و پنجره زیر ظاهر خواهد شد:

فشرده سازی بکاپ در SQL Server 2008 Enterprise معرفی شد. فشرده سازی بکاپ توسط SQL Server 2008 R2 Standard و همه نسخه های بالاتر ساپورت می شود. هر نسخه ای از SQL Server 2008  و بعد، می تواند بکاپ فشرده شده را restore کند.

محدودیت ها

محدودیت های زیر به بکاپ های فشرده شده اعمال می شود:

• بکاپ های فشرده شده و فشرده نشده نمی توانند در یک media set کنار هم باشند.
• نسخه های قبلی SQL Server نمی تواند بکاپ های فشرده شده را بخوانند.
• Ntbackupها نمی توانند یک tape را با بکاپ های فشرده شده SQL Server به اشتراک بگذارند.

تاثیر اجرای بکاپ های فشرده شده

از آنجاییکه بکاپ فشرده شده کوچکتر از بکاپ فشرده نشده همان اطلاعات است، فشرده سازی بکاپ معمولاً ابزار I/O کمتری نیاز دارد و بنابراین سرعت بکاپ گیری گیری را بطور چشمگیری افزایش می دهد.

بطور پیش فرض، فشرده سازی کاربرد CPU را افزایش می دهد، و CPU اضافی که توسط فرآیند فشرده سازی مورد استفاده قرار می گیرد، ممکن است عملیات های همزمان را تحت تاثیر قرار دهد. بنابراین، شاید بخواهید بکاپ های فشرده شده با اولویت پایین، در sessionیی که استفاده از CPU توسط Resource Governor محدود شده است، ایجاد کنید.

برای بدست آوردن تصویری خوب از اجرای I/O تان، می توانید با ارزیابی sortهای performance counters زیر، بکاپ I/O را به یا از ابزارها جدا کنید:

• کانتر اجرای Windows I/O ، از قبیل counterهای physical-disk
• کانتر Device Throughput Bytes/sec شیSQLServe Backup Device
• کانتر Backup/Restore Throughput/sec شی SQLServer:Databases

پیکر بندی

هنگانم نصب، فشرده بکاپ بطور پیش فرض off می شود. رفتار پیش فرض فشرده سازی توسط آپشن پیکربندی در سطح سرور backup compression default Option تعریف می شود. می توانید پیش فرض server-level را هنگام ایجاد بکاپ واحد یا هنگام زمانبندی یک سری از بکاپ های روتین، override کنید.

تغییر دادن پیش فرض در سطح سرور

• Transact-SQL

از استورد پروسیجر sp_configure برای تنظیم مقدار backup compression default استفاده کنید و سپس عبارت RECONFIGURE را اجرا کنید.

• SQL Server Management Studio

از صفحه Database Settings پنجره Server Properties استفاده کنید.

override کردن پیش فرض فشرده سازی بکاپ

می توانید رفتار فشرده سازی بکاپ ر ا برای یک بکاپ مجزا، backup job، یا پیکربندی log shipping عوض کنید.

• Transact-SQL

برای بکاپی معین، می توانید یا از WITH NO_COMPRESSION یا از WITH COMPRESSION در عبارت BACKUP استفاده کنید.

برای پیکربندی log shipping، می توانید رفتار فشرده سازی بکاپ log backupها رابا استفاده از sp_add_log_shipping_primary_databasesp_change_log_shipping_primary_database (Transact-SQL) کنترل کنید.

• SQL Server Management Studio

می توانید پیش فرض فشرده سازی بکاپ سرور را با تعیین Compress backup یا Do not compress backup در هر یک از پنجره های زیر override کنید:

برای محاسبه نسبت فشرده سازی، از مقادیر بکاپ در ستون های backup_size و compressed_backup_size جدول تاریخچه backupset، بشرح زیر استفاده کنید:

backup_size:compressed_backup_size

مثلاً، نسبت فشرده سازی 3:1 نشان می دهد که شما حدود %65 از فضای دیسک را save می کنید. برای کوئری کردن این ستون ها، می توانید از عبارت Transact-SQL زیر استفاده کنید.

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

نسبت فشرده سازی بکاپ های فشرده شده، به داده هایی بستگی دارد که فشرده شده است. فاکتورهای مختلفی می توانند روی نسبت فشرده سازی بدست آمده تاثیر بگذارد:

• نوع داده ها.

داده های کاراکتور بیش از یک نوع داده را فشرده می کند.

• سازگاری داده ها بین ردیف های یک صفحه.

معمولاً، اگر صفحه ای حاوی چندین ردیف باشد که در آن فیلدی حاوی همان مقدار باشد، فشرده سازی ممکن است برای آن مقدار اتفاق می افتد. برعکس، در دیتابیسی که حاوی داده های تصادفی یا فقط یک ردیف بزرگ برای هر صفحه است، بکاپ فشرده شده تقریباً هم اندازه بکاپ فشرده نشده خواهد بود.

• آیا داده ها رمزگذاری می شود.

داده های رمزگذاری شده، بطور چشم گیری کمتر از داده های رمزگذاری نشده معادل، فشرده می کند. اگر رمزگذاری داده های transparent، برای رمزگذاری کل دیتابیس استفاده شود، فشرده سازی بکاپ ممکن است سایز آنها را بسیار کم کند.

• آیا دیتابیس رمزگذاری می شود.

اگر دیتابیس فشرده شود، فشرده سازی بکاپ ها ممکن است اندازه آنها را بسیار کنم کند.

هر مدل ریکاوری اجازه بکاپ گرفتن از کل یا جزیی از دیتابیس SQL Server یا فایل های مستقل یا گروهی از فایل های دیتابیس را می دهد. ولی بکاپ های در سطح جدول (Table-level backups) را نمی توان ایجاد کرد.

بکاپ های داده ها (data backup)

اسکوپ بکاپ داده ها می تواند کل یک دیتابیس یا گروهی از filegroupها یا فایل ها باشد. برای هر یک از اینها، SQL Server، بکاپ های کامل و دیفرانسیلی را ساپورت می کند:

• بکاپ کامل

بکاپ کامل حاوی همه داده ها در دیتابیسی معین یا گروهی از filegroupها یا فایل ها، و همچنین log کافی برای ریکاور کردن آن داده ها است.

• بکاپ دیفرانسیلی

بکاپ دیفرانسیلی بر پایه آخرین بکاپ کامل داده ها است. این موضوع، اساس دیفرانسیل شناخته می شود. پایه دیفرانسیلی، بکاپ کاملی از خواندن/نوشتن دادها است. بکاپ دیفرانسیلی فقط شامل داده هایی است که از پایه دیفرانسیلی تغییر کرده اند. معمولاً، ایجاد کردن بکاپ های دیفرانسیلی که بعد از بکاپ پایه گرفته می شوند، سریعتر و کوچکتر از پایه بکاپ کامل است. بنابراین، استفاده از بکاپ های دیفرانسیلی می تواند سرعت فرآیند درست کردن بکاپ های دوره ای را، بمنظور کاهش خطر از دست دادن داده ها، افزایش دهد. معمولاً، پایه دیفرانسیلی توسط چندین بکاپ دیفرانسیلی متوالی مورد استفاده قرارمی گیرد. هنگام restore کردن، ابتدا بکاپ کامل restore می شود، و سپس آخرین بکاپ دیفرانسیلی.

در طول زمان، همین طور که دیتا بیس آپدیت می شود، مقدار داده موجود در بکاپ های دیفرانسیلی افزایش می یابد. این کار، ایجاد و restore کردن بکاپ را کندتر می کند. نهایتاً، باید بکاپ کامل دیگری ایجاد کرد تا بتوان پایه دیفرانسیلی جدیدی برای سری دیگری از بکاپ های دیفرانسیلی مهیا کرد.

هر بکاپ داده ای شامل قسمتی از transaction log است بطوریکه بکاپ را می توان به انتهای آن بکاپ ریکاور کرد.

در مدل ریکاوری کامل یا مدل ریکاوری bulk-logged، بعد از اولین بکاپ داده ها، بکاپ های منظم transaction log مورد نیاز هستند. هر log backup، بخشی از transaction log را تحت پوشش قرار می دهد که هنگام ایجاد بکاپ فعال بود، و log backup شامل همه رکوردهای log است که در log backup قبلی بکاپ نشدند.

بکاپ های دیتا بیس (database backup)

استفاده از بکاپ های دیتابیس آسان است و توصیه می شود هر وقت سایز دیتابیس اجازه می دهد، گرفته شوند. SQL Server، بکاپ های زیر را ساپورت می کند.

بکاپ های جزیی (partial backup)

بکاپ های جزیی و جزیی دیفرانسیلی در SQL Server معرفی شدند. این بکاپ ها جهت فراهم کردن انعطاف بیشتر برای گرفتن پشتیبان از دیتابیس هایی که شامل تعدادی فایل گروه های فقط خواندنی (read-only filegroups) در مدل ریکاوری ساده است، طراحی شده اند. اما، این بکاپ ها توسط مدل های ریکاوری ساپورت می شود.

SQL Server، بکاپ های زیر را ساپورت می کند.

بکاپ های فایل (file backup)

فایل موجود در دیتابیس را می توان بطور جداگانه backup و restore کرد. استفاده از بکاپ های فایل می تواند سرعت ریکاوری را با restore کردن فایل های آسیب دیده و بدون restore کردن بقیه دیتابیس افزایش دهد. مثلاً اگر دیتابیسی از چندین فایل تشکیل شده است که روی دیسک های مختلف قرار دارند و یک دیسک fail شود، فقط باید فایلی را که روی آن دیسک بود restore کرد. اما، برنامه ریزی و restore کردن بکاپ های فایل می تواند پیچیده باشد؛ بنابراین، بکاپ های فایل فقط باید در جایی استفاده شوند که مقادیری را به برنامه restore شما اضافه می کنند.

SQL Server، بکاپ های زیر را ساپورت می کند.

بکاپ های Transaction Log

در مدل ریکاوری کامل یا مدل ریکاوری bulk-logged، بکاپ های منظم transaction log مورد نیاز هستند. هر log backup، بخشی از transaction log را تحت پوشش قرار می دهد که هنگام ایجاد بکاپ فعال بود، و log backup شامل همه رکوردهای log است که در log backup قبلی بکاپ نشدند. بکاپ های log قطع نشده شامل زنجیره log کاملی از دیتابیس است. در مدل ریکاوری کامل، و بعضی وقتها در مدل ریکاوری bulk-logged، زنجیره log آسیب ندیده به شما اجازه restore کردن دیتابیس تا هر مقطع زمانی را میدهد.

قبل ازاینکه اولین بکاپ log را ایجاد کنید، باید یک بکاپ کامل ایجاد کنید، مانند بکاپ دیتابیس. از آن به بعد، بکاپ گرفتن از transaction log منظم، لازم است؛ نه فقط برای کم کردن احتمال از دست دادن کارتان، بلکه برای کوتاه کردن transaction log.

بکاپ های فقط کپی (copy-only backups)

بکاپ گرفتن از دیتابیس معمولاً آنرا و زمان restore شدن بکاپ ها را دچار تغییر می کند. اما، هرچند وقت یکبار، گرفتن بکاپی بمنظور خاصی، بدون اثر گذاشتن روی بکاپ کلی و فرایندهای restore، برای دیتابیس مفید است. بکاپ های فقط کپی در Server 2005 معرفی شدند. این بکاپ ها مستقل از بکاپ های منظم SQL Server هستند.

ابزارهای بکاپ (backup devices)

بکاپ های SQL Server روی ابزارهای بکاپ از قبیل فایل های دیسک یا tape media ایجاد می شوند. می توانید بکاپ های جدیدی به هر بکاپ موجود روی ابزار پیوست کنید، یا هر بکاپ موجود را overwrite کنید.

زمانبندی بکاپ ها

اجرای عملیات بکاپ گیری اثرات کمی روی transactionهایی دارد که در حال اجرا هستند؛ بنابراین، عملیات های بکاپ را می توان هنگام عملیات های منظم اجرا کرد. هنگام عملیات بکاپ گیری، SQL Server، داده ها را مستقیماً از فایل های دیتابیس به ابزارهای بکاپ کپی می کند. داده ها تغییر نمی کنند، و transactionهایی که هنگام بکاپ گیری در حال اجرا هستند، هرگز به تاخیر نمی افتند. بنابراین، می توانید بکاپ SQL Server را با کمترین تاثیر روی بارکاری production، اجرا کنید.

می توانید بکاپ ها را زمانبندی کنید تا بطور اتوماتیک نیز اجرا شوند.

فشرده سازی بکاپ

SQL Server 2008 Enterprise و نسخه های بعدی، فشرده سازی بکاپ ها را ساپورت می کند، و هر نسخه ای ازSQL Server 2008 و بعد می تواند بکاپ فشرده شده را restore کند.

محدودیت های عملیات های بکاپ گیری در SQL Server

در SQL Server 2005  و نسخه های بعدی، بکاپ می تواند هنگامی که دیتابیس آنلاین و در حال استفاده شدن است، انجام گیرد. اما، محدودیت های زیر وجود دارد.

از داده های آفلاین نمی توان بکاپ گرفت

هر عملیات بکاپ که بطور مستقیم یا غیرمستقیم داده های آفلاین را ریفرنس می کند، fail می شود. بعضی نمونه ها بشرح زیر هستند:

• شما اقدام به گرفتن بکاپ کامل از دیتابیس می کنید، اما یکی از filegroupهای دیتابیس آفلاین است. از آنجاییکه همه filegroupها بطور مستقیم یا غیرمستقیم در بکاپ کامل دیتابیس درگیر هستند، این عملیات fail می شود.

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

• شما اقدام به گرفتن بکاپ جزیی می کنید، اما filegroup خواندن/نوشتن آفلاین است. از آنجاییکه همه filegroupهای خواندن/نوشتن برای بکاپ جزیی لازم هستند، این عملیات fail می شود.

• شما اقدام به بکاپ فایل از فایل های معینی می کنید، اما یکی از این فایل ها آفلاین است. عملیات fail می شود. برای بکاپ گرفتن از فایل های آنلاین، می توانید فایل آفلاین را از لیست فایل ها حذف کنید و عملیات را تکرار کنید.

معمولاً، log backup، حتی اگر یک یا چند فایل آفلاین باشند نیز با موفقیت انجام می شود. اما اگر فایلی حاوی تغییراتی باشد که در مدل ریکاوری bulk-logged ایجاد شده است، همه فایل ها باید آنلاین باشند تا بکاپ گیری با موفقیت انجام شود.

محدودیت های همزمان هنگام بکاپ

SQL Server از یک فرآیند بکاپ گیری آنلاین استفاده می کند تا اجازه بکاپ گیری را، هنگامی که دیتابیس در حال استفاده شدن است، بدهد. هنگام بکاپ گیری، اکثر عملیات ها قابل انجام هستند؛ مثلاً عبارت های INSERT، UPDATE، یا DELETE هنگام عملیات بکاپ گیری مجاز هستند. اما اگر سعی کنید هنگامی که فایل دیتابیس در حال ایجاد شدن یا حذف شدن است، عملیات بکاپ گیری را شروع کنید، عملیات تا وقتی که فرآیند ایجاد یا حذف فایل تمام شود، به تاخیر می افتد.

عملیات هایی که هنگام بکاپ دیتابیس یا log backup اجرا نمی شوند:

• عملیات های مدیریت فایل از قبیل عبارت ALTER DATABASE با آپشن های ADD FILE یا REMOVE FILE.
• عملیات های Shrink database یا shrink file، که شامل عملیاتهای auto-shrink نیز می شود.
• اگر سعی کنید هنگامی که عملیات بکاپ گیری در حال اجرا است فایل دیتابیسی را ایجاد یا حذف کنید، عملیات ایجاد یا حذف فایل fail می شود.

اگر عملیات بکاپ گیری با عملیات مدیریت فایل یا عملیات shrink تداخل پیدا کند، یک تعارض (conflict) روی می دهد. بدون توجه به اینکه کدام عملیات اول شده است، عملیات دوم منتظرlock set می ماند تا time out شود. اگر قفل هنگام time out باز شود، عملیات دوم ادامه پیدا می کند. اگر قفل time out شود، عملیات دوم fail می شود.

این مقاله چگونگی ایجاد یک فایل پشتیبان کامل از بانک اطلاعاتی را توضیح می دهد.

چگونگی پشتیبان گرفتن از بانک اطلاعاتی

1. بعد از اتصال به نمونه مناسب Microsoft SQL Server Database Engine، در Object Explorer، روی نام سرور کلیک کنید تا tree سرور را بسط دهید.

2. Databases را بسط دهید، و بسته به بانک اطلاعاتی، یا یک بانک اطلاعاتی کاربر را انتخاب کنید، یا System Databases را بسط و یک بانک اطلاعاتی سیستم را انتخاب کنید.

3. روی بانک اطلاعاتی کلیک راست کنید، به Tasks اشاره کنید، و سپس روی Back Up کلیک کنید. پنجره Back Up Database ظاهر می شود.

4. در لیست باکس Database، نام بانک اطلاعاتی را تایید کنید. می توانید به طور اختیاری بانک اطلاعاتی دیگری را از لیست انتخاب کنید.

5. می توانید پشتیبان بانک داده ها را برای هر مدل ریکاوری اجرا کنید (FULL، BULK_LOGGED، یا SIMPLE).

6. در لیست باکس Backup type، گزینه Full را انتخاب کنید.

توجه داشته باشید که بعد از ایجاد پشتیبان کامل بانک اطلاعاتی، می توانید پشتیبان بانک اطلاعاتی دیگری را نیز ایجاد کنید.

7. می توانید بطور اختیاری، Copy Only Backup را انتخاب کنید تا یک پشتیبان فقط کپی (copy-only backup) ایجاد کنید. یک پشتیبان فقط کپی، یک بک آپ SQL Server است که از ترتیب بک آپ های قراردادی SQL Server مجزا است.

8. برای Backup component، روی Database کلیک کنید.

9. یا نام بک آپ پیش فرض را که در تکست باکس Name وجود دارد قبول کنید، یا نام دیگری برای پشتیبان وارد کنید.

10. بطور اختیاری در تکست باکس Description، توضیحی از فایل پشتیبان بدهید.

11. تعیین کنید بک آپ کی منقضی می شود و کی می توان بدون رد شدن از تایید انقضای داده ها، overwrite کرد:

• برای منقضی کردن پشتیبان بعد از زمانی مشخص، روی After کلیک کنید، و زمان مورد نظر را تعیین کنید. این مقدار می تواند بین 0 تا 99999 روز باشد؛ مقدار 0 بدین معناست که پشتیبان هرگز منقضی نخواهد شد.

مقدار پیش فرض در آپشن Default backup media retention پنجره Server Properties تنظیم می شود. برای دسترسی به این آپشن، درObject Explorer ، روی نام سرور کلیک راست کنید و properties را انتخاب کنید؛ سپس صفحه Database Settings را انتخاب کنید.

• برای منقضی شدن در تاریخ معینی، روی On کلیک کنید و تاریخ مورد نظر را وارد کنید.

12. نوع مقصد پشتیبان را با کلیک روی Disk یا Tape انتخاب کنید. برای انتخاب مسیرهای تا 64 دیسک یا درایوهای tape که حاوی media set واحد است، روی Add کلیک کنید. مسیرهای انتخابی در لیست باکس Backup to نمایش داده می شوند.

برای حذف مقصد پشتیبان، آن را انتخاب و روی Remove کلیک کنید. برای مشاهده محتویات مقصد پشتیبان، انتخابش کنید و روی Contents کلیک کنید.

13. برای مشاهده یا انتخاب آپشن های پیشرفته، روی Options در پنل Select a page کلیک کنید.

14. آپشن Overwrite Media را با کلیک کردن روی یکی از موارد زیر انتخاب کنید:

• Back up to the existing media set

برای این آپشن، یا روی Append to the existing backup set یا روی Overwrite all existing backup sets کلیک کنید.

بطور اختیاری، Check media set name and backup set expiration را انتخاب کنید تا عملیات بک آپی گیری را مجبور به تایید تاریخ و زمانی کنید که در آنها media set and backup set منقضی می شوند.

بطور اختیاری، نامی را در تکست باکس Media set name وارد کنید. اگر نامی تعیین نشده باشد، یک media set بدون نام ایجاد می شود. اگر نام media set را تعیین کنید، media (tape یا disk)، چک می شود تا معلوم شود آیا نام واقعی با نامی که شما وارد می کنید، هماهنگ است یا خیر.

• Back up to a new media set, and erase all existing backup sets

برای این آپشن، نامی را در تکست باکس Media set name وارد کنید؛ و بطور اختیاری media set را در تکست باکس New media set description توضیح دهید.

15. در قسمت Reliability، درصورت تمایل موارد زیر را چک کنید:

• Verify backup when finished
• Perform checksum before writing to media، و در صورت تمایل، Continue on checksum error.

16. اگر روی یک tape drive (همانطور که در بخش Destination صفحه General توضیح داده شده) پشتیبان می گیرید، آپشن Unload the tape after backup فعال می شود. کلیک روی این آپشن، آپشن Rewind the tape before unloading را فعال می کند.

17. SQL Server 2008 Enterprise  و نسخه های بعدی، backup compression را ساپورت می کنند. بطور پیش فرض، اینکه یک بک آپ فشرده شود یا خیر، بستگی به مقدار آپشن پیکربندی سرور backup-compression default دارد. اما، بدون در نظر گرفتن server-level default کنونی، می توانید یک بک آپ را با چک کردن Compress backup فشرده کنید، و می توانید با کلیک روی Do not compress backupف از فشرده سازی جلوگیری کنید.

همتاسازی (replication)، راه حلی مناسب برای مشکل انتقال داده ها بین سرورهای همیشه متصل است. با استفاده از ویزارد همتاسازی می توانید مکان شناسی (topology) همتاسازی را براحتی پیکربندی و مدیریت کنید. این مقاله به شما چگونگی پیکربندی توپولوژی سرورهای همیشه متصل را نشان می دهد.

آنچه یاد خواهید گرفت

این مقاله چگونگی منتشر کردن داده ها از یک بانک اطلاعاتی به بانک اطلاعاتی دیگر را با استفاده از همتاسازی مبادله ای نشان می دهد. بخش اول چگونگی استفاده از SQL Server Management Studio را برای ایجاد یک publication نشان می دهد. بخش های بعدی چگونگی ایجاد و اعتبار سنجی یک subscription و چگونگی اندازه گیری latency را نشان می دهند.

پیش نیازها

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

برای استفاده از این مقاله، سیستم شما باید اجزای زیر را داشته باشد:

در سرور publisher

هر نسخه ای از SQL Server 2005، به غیر از SQL Server 2005 Express Edition یا SQL Server Compact Edition. ایت نسخه ها نمی توانند publisherهای همتاسازی باشند.

بانک اطلاعاتی نمونه AdventureWorks. برای بالا بردن امنیت، این بانک های اطلاعاتی بطور پیش فرض نصب نمی شوند.

در سرور subscriber

هر نسخه ای از SQL Server 2005، به غیر از SQL Server 2005 Compact Edition. این نسخه نمی تواند در همتاسازی یک subscriber باشد.

بخش اول: publish کردن داده ها با استفاده از Transactional Replication

در این بخش، با استفاده ازSQL Server Management Studio ، یک publication مبادله ای ایجاد خواهیم کرد تا یک subset فیلتر شده از جدول Product در بانک اطلاعاتی نمونه AdventureWorks، منتشر (publish) کنیم. همچنین لوگین SQL Server، را که توسط Distribution Agent استفاده می شود، به لیست دسترسی (access list) اضافه خواهیم کرد.

چگونگی ایجاد یک publication و تعریف articleها

1. در SQL Server Management Studio، به publisher متصل شوید و سپس node سرور را بسط دهید.
2. پوشه Replication را گسترش دهید، روی پوشه Local Publications کلیک راست و سپس روی New Publication کلیک کنید.                              ویزارد Publication Configuration شروع می شود.
3. روی صفحه Publication Database، گزینه AdventureWorks2008R2 را انتخاب و سپس روی Next کلیک کنید.
4. روی صفحه Publication Type، گزینه Transactional publication را انتخاب و سپس روی Next کلیک کنید.
5. روی صفحه Articles، ند Tables را بسط دهید، چک باکس Product را انتخاب کنید، و سپس Product را بسط دهید و علامت تیک ListPrice و StandardCost را بردارید. روی Next. کلیک کنید.
6. روی صفحه Filter Table Rows، روی Add کلیک کنید.
7. در پنجره Add Filter، روی ستون SafetyStockLevel کلیک کنید، روی فلش راست کلیک کنید تا آن ستون به عبارت WHERE کوئری فیلتر اضافه شود، و عبارت WHERE را بشرح زیر اصلاح کنید:

WHERE [SafetyStockLevel] < 500

1. روی OK و سپس روی Next کلیک کنید.
2. چک باکس Create a snapshot immediately and keep the snapshot available to initialize subscriptions را انتخاب و سپس روی Next کلیک کنید.
3. روی صفحه Agent Security، تیک Use the security settings from the Snapshot Agent را بردارید.
4. روی Security Settings برای Snapshot Agent کلیک کنید، عبارت <Machine_Name>\repl_snapshot را در باکس Process account وارد کنید، پسوردی برای این اکانت تعیین کنید، و سپس روی OK کلیک کنید.
5. مرحله قبل را تکرا کنید تا repl_logreader as the process account for the Log Reader Agent را تنطیم کنید، سپس روی Finish کلیک کنید.
6. روی صفحه Complete the Wizard، عبارت AdvWorksProductTrans را در باکس Publication name وارد کنید و سپس روی Finish کلیک کنید.
7. بعد از اینکه publication ایجاد شد، روی Close کلیک کنید تا ویزارد تمام شود.

اضافه کردن Distribution Agent login به PAL

1. در SQL Server Management Studio، به publisher متصل شوید و سپس node سرور را بسط دهید و سپس پوشه Replication را بسط دهید.
2. در پوشه Local Publications، روی AdvWorksProductTrans کلیک راست وسپس روی Properties کلیک کنید.                 پنجره Publication Properties نمایش داده می شود.
3. صفحه Publication Access List را انتخاب و روی Add کلیک کنید.
4. در پنجره Add Publication Access، گزینه <Machine_Name>\repl_distribution را انتخاب و OK کنید.

تا اینجا، شما transactional publication را با موفقیت ایجاد کرده اید. در بخش بعدی، به این پابلیکیشن، subscribe خواهید شد.

بخش دوم: ایجاد Subscription در Transactional Publication

در این بخش، با استفاده از SQL Server Management Studio، یک subscription ایجاد خواهیم کرد.

چگونگی ایجاد subscription

1. در SQL Server Management Studio، به publisher متصل شوید و سپس node سرور را بسط دهید و سپس پوشه Rplication را بسط دهید.
2. در پوشه Local Publications، روی پابلیکیشن AdvWorksProductTrans کلیک راست کنید، و سپس روی New Subscriptions کلیک کنید.                                   ویزارد New Subscription شروع می شود.
3. روی صفحه Publication page، گزینه AdvWorksProductTrans را انتخاب و سپس روی Next کلیک کنید.
4. روی صفحه Distribution Agent Location، گزینه Run all agents at the Distributor را انتخاب و سپس روی Next کلیک کنید.
5. روی صفحه Subscribers، اگر نام نمونه Subscriber نمایش داده نمی شود، روی Add Subscriber، و Add SQL Server Subscriber کلیک کنید، نام subscriber نمونه را در پنجره Connect to Server وارد کنید، و سپس روی Connect کلیک کنید.
6. روی صفحه Subscribers، نام subscriber نمونه را انتخاب کنید، و گزینه <New Database> را از زیر Subscription Database انتخاب کنید.
7. در پنجره New Database، عبارت ProductReplica را در باکس Database name وارد و OK کنید. سپس روی Next کلیک کنید.
8. در پنجره Distribution Agent Security، روی دکمه ellipsis (…) کلیک کنید، عبارت <Machine_Name>\repl_distribution را در باکس Process account وارد کنید، پسورد این اکانت را وارد و OK کنید. سپس روی Next کلیک کنید.
9. روی Finish کلیک کنید تا مقدارهای پیش فرض روی صفحات باقیمانده را قبول کنید و ویزارد را تمام کنید.

تنظیم کردن پرمیشن های بانک اطلاعاتی در Subscriber

1. در SQL Server Management Studio، به subscriber متصل شوید، و به ترتیب Databases، ProductReplica، و Security را بست دهید، سپس روی Users کلیک راست و New Userرا انتخاب کنید.
2. در صفحه General، عبارت <Machine_Name>\repl_distribution را در باکس User name وارد کنید. روی دکمه ellipsis (…) و سپس Browse کلیک کنید، گزینه <Machine_Name>\repl_distribution را انتخاب و به ترتیب روی OK، Check Names، و OK کلیک کنید.
3. در Database role membership، گزینه db_owner را انتخاب کنید و سپس OK کنید تا کاربر ایجاد شود.

مشاهده وضعیت همزمان سازی subscription

1. در SQL Server Management Studio، به publisher متصل شوید و سپس node سرور را بسط دهید و سپس پوشه Replication را بسط دهید.
2. در پوشه Local Publications، پابلیکیشن AdvWorksProductTrans را بسط دهید، در بانک اطلاعاتی ProductReplica، روی subscription کلیک راست کنید، و سپس روی View Synchronization Status را کلیک کنید.                        وضعیت کنونی هوزمان سازی نمایش داده می شود.
3. اگر subscription زیر AdvWorksProductTrans نمایش داده نمی شود، دکمه F5 را فشار دهید تا لیست ریفرش شود.

در این بخش، یک subscription به transactional publication ایجاد کردید. از آنجاییکه Distribution Agent برای این subscription بطور مستمر run می شود، subscription هنگام ایجاد شدن، شروع (initialized) نمی شود.

بخش سوم: اعتبار سنجی subscription و اندازه گیری latency

در ابن بخش، از tracer tokens برای تایید همتا سازی تغییرات در subscriber، و تعیین latency استفاده خواهیم کرد.

insert کردن یک tracer token و مشاهده اطلاعات روی token

1. در SQL Server Management Studio، به publisher متصل شوید و سپس node سرور را بسط دهید و سپس پوشه Replication را بسط دهید، سپس روی Launch Replication Monitor کلیک کنید.                    Replication Monitor نمایش داده می شود.
2. یک گروه Publisher را در پنل چپ بسط دهید، نمونه Publisher را بسط دهید. و سپس روی پابلیکیشن AdvWorksProductTrans کلیک کنید.
3. روی تب Tracer Tokens کلیک کنید.
4. روی Insert Tracer کلیک کنید.
5. زمان باقیمانده برای tracer token را در ستون های زیر مشاهده کنید: Publisher to Distributor, Distributor to Subscriber، و Total Latency. مقدار Pending نشان می دهد که token به نقطه معین شده نرسیده است.

در این بخش، با موفقیت از tracer tokens برای تایید همتاسازی داده ها از Publisher به Subscriber استفاده کردید. همچنین می توانید داده ها را در جدول Product در پابلیشر، insert، update، و delete کنید، و جهت مشاهده این تغییرات بعد از اینکه همتاسازی شدند، جدول Product را در Subscriber، کوئری کنید.

SQL server، ویژگی هایی را در اختیار می گذارد که می توانید برای رهگیری (auditing) فعالیت ها و تغییراتی که روی سیستم روی می دهد استفاده کنید.

این مقاله لینک هایی را در اختیار شما می گذارد که بشما کمک می کنند اطلاعاتی را که برای رهگیری در موتور بانک اطلاعاتی SQL Server نیاز دارید، پیدا کنید.

مقدمه ای بر رهگیری SQL Server

رهگیری نمونه ای از SQL Server یا بانک اطلاعاتی SQL Server، مستلزم tracking و logging کردن رویدادهایی است که در سیستم روی می دهند. می توانید از چندین متد رهگیری برای SQL Server استفاده کنید. هنگام شروع کار با 2008 Enterprise SQL Server، می توانید رهگیری اتوماتیک را با استفاده از SQL Server Audit تنظیم کنید.

رهگیری در SQL Server چندین سطح (level) دارد، بستگی به مقررات و استانداردهای لازم نصب دارد. SQL Server Audit، ابزارها و فرآیندهایی را در اختیار می گذارد که باید برای فعال کردن، ذخیره، مشاهده auditها در سرورهای مختلف و اشیاء بانک اطلاعاتی در اختیار داشته باشید.

می توانید گروه های server audit action برای هر نمونه، و چه گروه هایdatabase audit action ، یا database audit actions برای هر بانک اطلاعاتی را رکورد کنید. رویداد audit، هر بار که اقدامی قابل رهگیری انجام می شود، روی می دهد.

اجزای SQL Server Audit

audit، ترکیبی از چند عنصر درون بسته ای واحد برای گروهی از اکشن های سرور یا بانک اطلاعاتی است. اجزای SQL Server Audit، برای ایجاد خروج ای که audit نامیده می شود، ترکیب می شوند، درست مثل تعریف گزارش ترکیب شده با گرفیک ها عنصرهای داده که گزارشی را ایجاد می کند.

SQL Server Audit از Extended Events برای کمک به ایجاد audit استفاده می کنند.

SQL Server Audit

شی SQL Server Audit، نمونه ای واحد از سرور یا اکشن های در سطح بانک اطلاعاتی و گروهای اکشن ها را جمع آوری می کند تا مانیتور کند. audit در سطح نمونه SQL Server است. می توانید برای هر نمونه SQL Server چندین audit داشته باشید.

وقتی یک audit تعریف می کنید، در واقع موقعیت خروجی نتایج را تعیین می کنید. این، مقصد audit نامیده می شود. audit در حالت disabled ایجاد می شود، و بطور اتوماتیک هیچ اکشنی را رهگیری نمی کند. بعد از اینکه audit فعال می شود، مقصد اودیت (audit destination) داده ها را از audit دریافت می کند.

Server Audit Specification

شی Server Audit Specification، متعلق به audit است. می توانید برای هر audit، یک server audit specification ایجاد کنید، زیرا هر دو در اسکوپ نمونه SQL Server ایجاد می شوند.

server audit specification، گروه های اکشن در سطح سرور زیادی را جمع آوری می کند. می توانید گروه های اکشن را در server audit specification شامل کنید. گروه های Audit action، گروه هایی از پیش تعریف شده اند که رویدادهایی اتمی (atomic events) هستند که درDatabase Engine اتفاق می افتند. این اکشن ها به audit ارسال می شوند، که آنها را در target رکورد می کند.

Database Audit Specification

شی Database Audit Specification نیز متعلق به SQL Server audit است. می توانید یک database audit specification برای هر SQL Server database برای هر audit ایجاد کنید.

Database Audit Specification، اکشن های audit در سطح بانک اطلاعاتی را جمع آوری می کند. می توانید یا گروه های audit action یا رویدادهای audit را به database audit specification اضافه کنید. رویدادهای Audit، اکشن هایی اتمی هستند که می توان توسط موتور SQL Server رهگیری کرد. گروه های اکشن audit، گروهی از اکشن های از پیش تعریف شده هستند. هردو در اسکوپ بانک اطلاعاتی SQL Server هستند. این اکشن ها به audit فرستاده می شوند، که آنها را روی target رکورد می کنند.

Target

نتایج audit به target ارسال می شوند، که می تواند یک فایل، log رویداد Windows Security، یا log رویداد Windows Application باشد. logها باید بطور دوره ای مرور و آرشیو شوند تا مطمئن شویم که target، فضای کافی برای نوشتن رکوردهای اضافی دارد.

برای نوشتن روی Windows Security log، نیاز است که SQL Server service account به سیاست Generate security audits اضافه شود. بطور پیش فرض، Local System، Local Service، و Network Service بخشی از سیاست هستند. این تنظیمات را می توان با استفاده از سیاست امنیتی snap-in یا (secpol.msc) پیکربندی کرد.

وقتی اطلاعات log را روی فایلی ذخیره می کنید، بر ای جلوگیری از دسترسی غیرمجاز به فایل، می توانید دسترسی به موقعیت فایل را به روش های زیر محدود کنید:

• SQL Server Service Account باید پرمیشن Read و Write داشته باشد.

• مدیران audit معمولا به پرمیشن های بالا نیاز دارند. این بدین معناست که مدیران audit، اکانت های ویندوز برای مدیریت فایل های audit می باشند، از قبیل: کپی کردن آنها به shareهای مختلف، گرفتن فایل backup از آنها، و غیره.

• Audit Readerهایی که مجاز به خواندن فایل های audit هستند، باید پرمیشن Read داشته باشند.

حتی موقع نوشتن روی فایل، اگر کاربران دیگر ویندوز هم پرمیشن Read را داشته باشند، می توانند فایل audit را بخوانند. موتور بانک اطلاعاتی روی عملیات های خواندن قفل نمی گذارد.

از آنجاییکه موتور بانک اطلاعاتی به فایل دسترسی دارد، loginهای SQL Server که پرمیشن CONTROL SERVER دارند می توانند از موتور بانک اطلاعاتی برای دسترسی به فایل های audit استفاده کنند. برای رکورد کردن هر کاربری که فایل audit را می خواند، باید یک audit روی فایل master.sys.fn_get_audit_file تعریف کنید. این کار، loginها را با پرمیشن CONTROL SERVER که از طریق SQL Server به فایل audit دسترسی پیدا کرده اند، رکورد می کند.

اگر مدیر audit، فایلی را به جایی دیگر کپی کند، ACLها در جای جدید با به پرمیشن های زیر کاهش پیدا کنند:

• مدیر audit – Read و Write

• خواننده audit – Read

توصیه می شود گزارش های audit از نمونه ای مستقل از SQL Server، از قبیل نمونه ای از SQL Server Express، ایجاد کنیم. با استفاده از نمونه ای از موتور بانک اطلاعاتی برای گزارش گیری، می توانید کاربرها را از دسترسی غیرمجاز به رکورد audit باز دارید.

می توانید با رمزگذاری پوشه ای که فایل audit در آن ذخیره شده است، با استفاده از Windows BitLocker Drive Encryption یا Windows Encrypting File System سد محکمتری در مقابل دسترسی غیر مجاز ایجاد کنید.

مروری بر استفاده از SQL Server Audit

می توانید از SQL Server Management Studio یا Transact-SQL برای تعریف audit استفاده کنید. بعد از اینکه audit ایجاد و فعال شد، تارگت، entryها را دریافت خواهد کرد.

می توانید logهای رویداد ویندوز را با استفاده از یوتیلیتی Event Viewer در ویندوز، بخوانید. برای تارگت های فایل، می توانید یا از Log File Viewer درSQL Server Management Studio  یا از تابع fn_get_audit_file برای خواندن فایل تارگت استفاده کنید.

فرآیند کلی ایجاد و استفاده از audit به شرح زیر است:

1. یک audit ایجاد و تارگت را تعریف کنید.
2. یک server audit specification یا database audit specification ایجاد کنید که به audit مپ شود. audit specification را فعال کنید.
3. audit را فعال کنید.
4. رویدادها را با استفاده از Windows Event Viewer، Log File Viewer، یا تابع fn_get_audit_file بخوانید.

ملاحظات

در صورت بروز مشکلی در شروع audit، سرور شروع بکار نخواهد کرد. در این صورت، می توان سرور را با استفاده از آپشن –f در command line راه انداخت.

از آنجاییکه ON_FAILURE=SHUTDOWN برای audit تعیین شده است، وقتی مشکل ایجاد شده در audit باعث خاموش شدن یا عدم کارکردن سرور شود، رویداد MSG_AUDIT_FORCED_SHUTDOWN، در log نوشته می شود. از آنجاییکه خاموش شدن سرور هنگام اولین رویارویی با این تنظیم روی می دهد، این رویداد فقط یکبار نوشته می شود. این رویداد بعد از پیام مشکل audit که باعث خاموش شدن می شود، نوشته می شود. مدیر می تواند این خاموش شدن را با باز کردن SQL Server در مد Single User و با استفاده از –m flag رفع کند. اگر SQL Server در این مد باز شود، باید هر audit را که ON_FAILURE=SHUTDOWN در آن تعیین شده، downgrade کنید. وقتی SQL Server با استفاده از –m flag باز می شود، پیام MSG_AUDIT_SHUTDOWN_BYPASSED  در error log نوشته می شود.

Database Mirroring و SQL Server Audit

بانک اطلاعاتی ای که دارای database audit specification تعریف شده ای می باشد، و از database mirroring استفاده می کند، database audit specification را شامل خواهد شد. برای درست کارکردن روی نمونه SQL منعکس شده (mirrored SQL instance)، آیتم های زیر باید پیکر بندی شود:

• mirror server باید دارای یک audit با همان GUID باشد تا database audit specification را برای نوشتن رکوردهای audit فعال کند. این را می توان با استفاده از فرمان CREATE AUDIT WITH GUID=<GUID از منبع Server Audit> پیکربندی کرد.

• برای تارگت های فایل باینری، mirror server service account باید دارای پرمیشن های مناسب با جایی باشد که

audit trail در آنجا وشته می شود.

• در تارگت های log رویداد ویندوز، سیاست امنیت که mirror server در آن قرارگرفته، باید به service account اجازه دسترسی به log رویداد امنیت یا اپلیکیشن را بدهد.

LINQ to SQL به برنامه نویسان .NET اجازه نوشتن queryها را درزبان .NET می دهد تا بتوانند داده ها را از بانک اطلاعاتیSQL Server بازیابی و تغییر دهند. به طور عام، LINQ to SQL اجازه ایجاد queryهای SQL در syntax زبان.NET انتخابی مان و کار کردن با مجموعه ای قوی از اشیا به عنوان نتیجه برگشتی را به ما می دهد. می توان تغییراتی را در این اشیا بوجود آورد و سپس دوباره آنها را در database ذخیره کرد.

برای درک مفهوم syntax در LINQ to SQL، ما از schemaی بانک اطلاعاتی SQL زیر استفاده می کنیم که نرم افزاری ساده برای ثبت محصولات و helpdesk است؛ و با داده های نمونه populate شده و دارای روابط کلید خارجی (foreign-key relationship) است که در جای مناسب تعریف می شود.

SQL Database Schema که برای مثالهای LINQ to SQL استفاده می شود:

از شما می خواهم چند دقیقه این واقعیت را فراموش کنید که ما برضد نوع داده ای HookedOnLINQ کد نوییس می کنیم، بعداً توضیح خواهم داد که چگونه آن را در چند صفحه ایجاد کردم، فعلاً مد نظر داشته باشید که این، یک ساختار شی است که از این database schema تقلید می کند.

HookedOnLINQ db = 
     new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ"); 
 
   var q = from c in db.Contact
           where c.DateOfBirth.AddYears(35) > DateTime.Now
           orderby c.DateOfBirth descending
           select c;
 
   foreach(var c in q)
       Console.WriteLine("{0} {1} b.{2}",
                   c.FirstName.Trim(),
                   c.LastName.Trim(),c.DateOfBirth.ToString("dd-MMM-yyyy"));
 

:Output

Mack Kamph b.17-Sep-1977

Armando Valdes b.09-Dec-1973

عبارت LINQ to SQL Query در بانک اطلاعاتی SQL Server – مخاطبان کمتر از 35 سال سن، ابتدا جوانترین.

هنگامی که حلقه foreach را وارد می کنیم، عبارت SQL زیر توسط LINQ فرموله می شود و روی سرور اجرا می شود. (دانستن این نکته مهم است که SQL فقط اولین باری که ما داده ها را request می کنیم اجرا می شود، تا آن موقع، query به صورت یک عبارت در حافظه نگهداری می شود این فرآیند، Deferred Execution نامیده می شود).

SELECT [t0].[ContactId], [t0].[FirstName], [t0].[LastName], [t0].[DateOfBirth],

[t0].[Phone], [t0].[Email], [t0].[State]
FROM [Contact] AS [t0]
WHERE DATEADD(YEAR, @p0, [t0].[DateOfBirth]) > @p1
ORDER BY [t0].[DateOfBirth] DESC

عبارات SQL که توسط LINQ ایجاد شده و مخاطبین بیشتر از تاریخی معین شده را که به عنوان یک پارامتر ارسال شده، باز می گرداند.

عبارت query در C#، به کد SQL پارامتریزه شده تبدیل شد، پارامترها ایجاد شدند و query روی سرور اجرا شد. LINQ to SQL به برنامه نویسان اجازه می دهد به استفاده از stored procedureها به جای SQL ادامه دهند، گرچه حالا مجبورید خودتان کد stored procedure را بنویسید، و در نتیجه بعضی از قابلیت های LINQ را از دست می دهید. بعداً در مورد این موضوع بیشتر بحث می کنیم، فعلاً در نظر داشته باشید که LINQ to SQL ازstored procedure و همچنین از فراخوانی های SQL که به طور دینامیکی ایجاد شده اند، در هر شرایطی ساپورت می کند.

اگر بانک اطلاعاتی شما دارای روابط کلید خارجی است، آنگاه سلسله مراتب آنها در مدلهای شی ایجاد شده منعکس می شود. می توان از طریق تعیین کردن جدول child به داده های رکوردهای مربوط دسترسی پیدا کنید. مثال بعدی نشان می دهد چگونه می توان در زنجیره روابط کلید خارج، بدون یک عبارت Join به طور مستقیم navigate کرد.

HookedOnLINQ db = 
     new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
 
var q = from o in db.Orders
        where o.Products.ProductName.StartsWith("Asset") && 
              o.PaymentApproved == true
        select new { name   = o.Contacts.FirstName + " " +
                              o.Contacts.LastName, 
                     product = o.Products.ProductName, 
                     version = o.Products.Version + 
                              (o.Products.SubVersion * 0.1)
                   };
 
foreach(var x in q)
    Console.WriteLine("{0} - {1} v{2}",
                      x.name, x.product, x.version);

Output:

Barney Gottshall - Asset Blaster v1

Barney Gottshall - Asset Blaster v1.1

Armando Valdes - Asset Blaster Pro v1

Jeffery Deane - Asset Blaster Pro v1.1

Stewart Kagel - Asset Blaster Pro v1.1

Blaine Reifsteck - Asset Blaster Pro v1.1

Ariel Hazelgrove - Asset Blaster v1.1

دسترسی به روابط کلید خارجی ساده است. نیازی به join syntax نیست، مستقیماً به زیر اعضا (sub-members) دسترسی دارید.

این مدل شی سلسله مراتبی (hierarchical)، برای آپدیت ها نیز جواب می دهد. می توانید رکوردها را بوسیله تغییر داده اشیا و اضافه یا حذف کردن اشیا از جداول، در جداول مربوطه assign، اضافه یا حذف کنید. در پشت پرده، LINQ to SQL، فرمان SQL query زیر را ایجاد و آنرا اجرا می کند. از این نتایج برای populate کردن مجموعه شی نتیجه (result object collection) استفاده کرد که مجموعه ای از یک type ناشناس (Anonymous) است.

SELECT ([t2].[FirstName] + @p2) + [t2].[LastName] AS [value], 

[t1].[ProductName], [t1].[Version] + ([t1].[SubVersion] * @p3) AS [value2]
FROM [Orders] AS [t0], [Products] AS [t1], [Contacts] AS [t2]
WHERE ([t2].[ContactId] = [t0].[ContactId]) AND 

([t1].[ProductName] LIKE @p0) AND ([t0].[PaymentApproved] = @p1) 

AND ([t1].[Product_Id] = [t0].[ProductId])

کد SQL که نشان می دهد چگونه joins to related table through foreign-keys، اضافه شدند.

اگر بانک اطلاعاتی شما دارای روابط کلید خارجی نیست که بین دو جدول تعریف می شوند، LINQ to SQL، دسترسی نسبی (relational access) را توسط تعیین Joinها در عبارات query، قبول می کند. Query زیر چگونگی join کردن را در جاییکه یک کلید خارجی بین دو جدول Contacts.Phone و CallLogs.Number تعریف نشده، نشان می دهد.

HookedOnLINQ db = 
    new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
 
var q = from call in db.CallLogs
        join contact in db.Contacts on call.Number equals contact.Phone
        select new {contact.FirstName, contact.LastName, 
                    call.When, call.Duration};
 
foreach(var call in q)
    Console.WriteLine("{0} - {1} {2} ({3}min)",
        call.When.ToString("ddMMM HH:mm"), 
        call.FirstName.Trim(), call.LastName.Trim(), call.Duration);

اگر هیچ کلید خارجی وجود نداشته باشد، می توانید از اپراتور Join در عبارت query استفاده کنید.

جهت تغییر دادن و اضافه کردن رکورد به بانک اطلاعاتی مان، فقط باید تغییراتی رابه اشیای موجود در حافظه اعمال کنید و سپس متد SubmitChanges را فراخوانی کنید (مواظب باشید، من یک بار به اشتباه متد AcceptChanges را فرا خواندم که تغییرات را قبول می کند و همه رکوردها را به صورت اریجینال mark می کند اما در database ذخیره نمی کند. LINQ to SQL، رد تغییرات را نگه می دارد و عبارات SQL را ایجاد می کند تا همه آپدیت ها، insertها، و deleteها را تحت تاثیر قرار دهد. می توانید این رفتار پیش فرض را Override کنید و متدهای پیاده سازی خودتان را تعیین کنید و به جای آن استفاده کنید. LINQ to SQL، یک تراکنش را در اطراف آپدیت های database ایجاد می کند، پس اگر قسمتی دچار اشکال شود، فرصت دارید تا error را capture کنید، آنرا اصلاح و دوباره تلاش کنید. همچنین می توانید کنترل کنید LINQ to SQL چگونه errorهای همزمان را مدیریت کند (وقتی شخص دیگری داده هایی را که قبلاً ویرایش می کردید تغییر می دهد، شما شانس ذخیره کردن را دارید).

HookedOnLINQ db = 
      new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
 
// Change - Get an object, make the change in memory, Call SubmitChanges
Contacts q = (from c in db.Contacts
              where c.FirstName == "Armando" && c.LastName == "Valdes"
              select c).FirstOrDefault();
 
if (q != null) {
       q.Email = "Armando.Valdes@aspiring-technology.com";
}
 
try {
    db.SubmitChanges();
}
catch (OptimisticConcurrencyException e) {
    // You have your choice of RefreshMode to resolve concurrency conflicts. 
    // You can KeepChanges, KeepCurrentValues, OverwriteCurrentValues.
    e.Resolve(RefreshMode.OverwriteCurrentValues);
    db.SubmitChanges();
}

آپدیتی که چگونگی مدیریت کردن errorهای همزمان را نشان می دهد. شما تغییرات را در اشیا ایجاد می کند و سپس SubmitChanges را فرا می خوانید.

Insert کردن رکوردهای جدید به سادگی ایجاد نمونه جدیدی از اشیا و اضافه کردن آن به مجموعه ی مناسب و سپس فراخوانی SubmitChanges است.

HookedOnLINQ db = 
    new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
 
// Adding Records – (1) Create a new object and sub-objects, 
// (2) Add it to the DataContext collection, (3) Call SubmitChanges 
 
// (1)
Contacts newContact    = new Contacts();
newContact.FirstName   = "Troy";
newContact.LastName    = "Magennis";
newContact.Phone       = "425 749 0494";
newContact.Email       = "troy@aspiring-technology.com";
newContact.DateOfBirth = new DateTime(1980, 08, 07);
// Create sub-record and add to this contact
Orders newOrder         = new Orders();
newOrder.Products       = (from p in db.Products 
                           where p.ProductName == "Asset Blaster Pro" 
                           select p).FirstOrDefault();
newOrder.DateOfPurchase = DateTime.Now;
 
// (2)
newContact.Orders.Add(newOrder);
db.Contacts.Add(newContact);
 
// (3)
db.SubmitChanges();

insert کردن یک رکورد جدید و یک زیر رکورد مرتبط. فقط اشیا را ایجاد کنید و به یک collection اضافه کنید.

LINQ to SQL در SubmitChanges، عبارات SQL را به ترتیب صحیحی generate می کند تا رکوردهای جدید را در database ذخیره کند و هر کدام را به طور صحیح reference کند. در این مثال، LINQ to SQL، به insert کردن Contact جدید نیاز دارد تا ابتدا کلید اصلی را بدست بیاورد و سپس آنرا هنگام نوشتن ترتیب جدید در database استفاده کند. کل فرآیند دریک transaction انجام می شود، پس اگر هر مرحله ای دچار اشکال شود، آنگاه کل database به حالتی که قبل از اینکه SubmitChanges فراخوانده شود، بر میگردد.

Start LOCAL Transaction (ReadCommitted)
 
INSERT INTO [Contacts](FirstName, LastName, DateOfBirth, Phone, Email, State)

 VALUES(@p0, @p1, @p2, @p3, @p4, @p5)
SELECT [t0].[ContactId]
FROM [Contacts] AS [t0]
WHERE [t0].[ContactId] = (CONVERT(Int,@ @IDENTITY))
 
INSERT INTO [Orders](ContactId, ProductId, DateOfPurchase, PaymentApproved, Quantity, Discount, AccessCode) 

VALUES(@p0, @p1, @p2, @p3, @p4, @p5, @p6)
SELECT [t0].[OrderId]
FROM [Orders] AS [t0]
WHERE [t0].[OrderId] = (CONVERT(Int,@ @IDENTITY))
 
Commit LOCAL Transaction

SQL هنگام نوشتن یک رکورد و زیر رکورد، اجرا می شود. به wrap کردن کل فرآیند توسط transaction دقت کنید.

این رکوردها بعد از اینکه متد SubmitChanges در مثال شکل 16 فراخوانی شود، اضافه می شوند.

حذف کردن رکوردها بسیار ساده است. می توانید یک شی را از مجموعه اشیایی که در حال حاضر در حافظه هستند و از query قبلی جمع آوری شده اند، حذف کنید.

// Delete the record(s) we just created (do sub-items first)
db.Orders.Remove(newOrder);
db.Contacts.Remove(newContact);
db.SubmitChanges();

مثال هایی ازحذف کردن رکوردها از database

تا اینجا من یک مرحله مهم را حذف کرده ام. ما queryهایی را ضد یک type بنام HookedOnLINQ نوشته ایم که با یک database connection string، و instance types Contacts، و Orders، و Products شروع شده است. این type، از لنگر LINQ to SQL، کلاسی به نام DataContext، ارث می برد. این کلاس، marshalling عبارات query را به عبارات SQL ، و همچنین change tracking را در فراخوانی SubmitChanges مدیریت می کند. به علاوه، ما به داشتن typeهایی برای نمایش جداول داده هایمان و جنبه های mapping اشیا و روابط با معادل های SQLشان و بالعکس، نیاز داریم. گرچه همه این کلاسها را می توان به طور دستی ایجاد کرد، اما این کار اصلاً توصیه نمی شود. یک ساپورت زمان طراحی و درونی در Visual Studio به همراه یک ابزار خط فرمان (command line tool) وجود دارد که کل کارهای سنگین در code generation به جای ما انجام می دهد.

کلاس DataContext سفارشی:

· از System.Data.DLINQ.DataContext type ارث می برد

· مجموعه ای از instance type را آغاز می کند (Table<[type]>) و آنها را قابل دسترس می کند. (مثلاً، می توانیم db.Contacts را از میان عبارات query فرابخوانیم)

کلاس های instance object سفارشی:

· با یک صفت [Table] تغییر شکل می دهد

· حاوی fieldهای رایج یا propertyهایی است که با صفات [Column] تغییر شکل داده اند

· روابط کلید خارجی با صفت [Association] را تعریف می کنند

· رفتار Update، Insert، و Delete را با تعریف متدهایی که با صفات [Update]، [Insert]، و [Delete] علامتگذاری (mark) شده اند را override می کند

· Store Procedure، View، و Function wrapperها را با متدهایی که با صفات [StoredProcedure]، [View]، یا [Function] علامتگذاری (mark) شده اند را تعریف می کند

· تضمین می کند که رویدادهای PropertyChanging و PropertyChanged، هروقت که value تغییر می کند، روی می دهند.

گزینه هایی برای generate کردن کلاس های wrapper و مشتق DataContext، که عملکرد LINQ to SQL را روی جداول و اشیای databaseهای دیگر قبول می کنند، در زیر آورده شده:

· کل کار را به طور دستی انجام دهیم؛

· از built-in designer برای Visual Studio 2005 استفاده کنیم؛

· از ابزار خط فرمان SQLMetal استفاده کنیم؛

· از یک فایل XML mapping برای لینک کردن جداول و ستونهای database به typeها و propertyها استفاده کنیم. این کار به database و تغییرات mapping، اجازه می دهد در recompile کردن یک برنامه روی دهند.

برای generateکردن object wrapper برای بانک اطلاعاتی نمونه، بنام HookedOnLINQ، باید برنامه SqlMetal را با استفاده از ابزار خط فرمان و با argumentهای زیر اجرا کنید:

sqlmetal /server:(local) /database:HookedOnLINQ /code:HookedOnLINQ.cs

این کار، یک HookedOnLINQ.cs ایجاد می کند که برای تمامی مثال هایی که تا الان آورده شده اند، کاملاً کاربردی است. من فقط آن را درون پروژه اصلی کپی کردم و solution را compile کردم.

built-in designer به شما اجازه ایجاد یک DLINQ Object surface را می دهد. می توانید table instanceها را از پنجره Server Explorer، روی آن surface درگ کنید.روابط کلید خارجی به طور اتوماتیک به surface اضافه می شوند، اگر در بانک اطلاعاتی تعریف شوند، یا می توانید به طور دستی آنها را از Toolbox اضافه کنید. هنگام compile کردن، DataContext و instance typeها برای شما ایجاد می شوند. در زیر یک DLINQ Object surface آمرده شده که schemaی HookedOnLINQ را از بانک اطلاعاتی نشان می دهد.

LINQ to SQL Designer Surface. درگ کردن جدول ها از server explorer باعث ایجاد object model می شود و به طور اتوماتیک روابط را تعریف می کند.

متد جایگزین استفاده از صفات که مدل رابطه ای را به مدل شی ای لینک می کند، منتقل کردن mappingها به یک فایل XML است. ابزار خط فرمان SQLMetal، این فایل XML را برای شما ایجاد می کند، اما می توانید generate کردن آنرا هرطور که دوست دارید اتوماتیک کنید. وقتی که DataContext را ایجاد می کنید، می توانید mapping XML را ارسال کنید، و این کار دقیقاً همان تاثیر استفاده از صفات را خواهد داشت، غیر از اینکه، هنگام compile کردن، به برنامه شما hardcode نمی شود.

خیلی ها معتقدند که database access همیشه باید از طریق Stored Procedure اجرا شوند تا امنیت را ارتقا دهند. LINQ to SQL به طور کامل Stored Procedureها را برای فراخوانی های عمومی و عملیات های update، insert، و delete، ساپورت می کند؛ و در موارد زیادی، تجربه شما را با خلاص کردن شما از ایجاد پارامترهای ورودی توسط دست، افزایش می دهد. اما، استفاده محض از Stored Procedureها، مزایای نوشتن عبارت های Query را در زبان برنامه نویسی اصلی برنامه نویس، از بین می برد. می توان از Stored Procedureها برای همه عملیات های Insert، Update، و Delete بکار برد و از عبارات Query برای بازیابی داده ها استفاده کرد. این کار باعث محافظت database در برابر از بین رفتن داده ها می شود، و به برنامه نویسان اجازه می دهد عبارات Query را در VB یا C# بسازند.

فراخوانی stored procedureها بسیار راحت شده. هنگام استفاده از ADO.NET، مجبور بودید قبل از ایجاد یک connection به database، و فراخوانی واقعی stored procedureها، پارامترهایی را به طور دستی بسازید. ابزار generate کردن، به عنوان بخشی از LINQ to SQL، تابع های wrapper را برای stored procedureها ایجاد می کند.

کد stored procedure زیر، لیستی از پرداختهای سررسیده را بازیابی می کند. روزهایی که از موعد پرداخت گذشته، به عنوان یک پارامتر ارسال می شود. نتیجه این کار، یک cursor با تعدادی ستون است، که یقیناً آن type نیست که قبلاً در اشیاء C# تعریف کرده ایم.

ALTER PROCEDURE [dbo].[GetOverdueAccounts] 
    @daysOverdue int = 15 
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT    o.OrderId, o.Quantity, o.DateOfPurchase, o.Discount, 
            c.FirstName + ' ' + c.LastName AS CustomerName,
            c.Phone, c.Email,  
            p.ProductName, p.Price,
            ((p.Price*o.Quantity)*((100-o.Discount)/100)) AS Cost, 
            DATEDIFF(day, o.DateOfPurchase, GETDATE()) AS OverdueDays
    FROM        Orders o, 
            Contacts c, 
            Products p
    WHERE        o.ContactId = c.ContactId
    AND        o.ProductId = p.Product_Id
    AND         o.PaymentApproved = 0
    AND        p.IsBeta = 0
    AND        DATEADD(day, @daysOverdue, o.DateOfPurchase) < GETDATE()
 
END

ابزار generate کردن کد، دارای یک switch است که wrapper و result type را برای stored procedureها، generate می کند.

sqlmetal /server:(local) /database:HookedOnLINQ /sprocs /code:HookedOnLINQ.cs

HookedOnLINQ db = 
    new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
 
var overdue = db.GetOverdueAccounts(30);
 
foreach (GetOverdueAccountsResult c in overdue)
    Console.WriteLine("{0} days - {1:c}: {2}", 
        c.OverdueDays, c.Cost, c.CustomerName);

Output:

215 days - $300.00: Armando Valdes

30 days - $180.00: Adam Gauwain

30 days - $247.50: Adam Gauwain

مقدمه

شرکت ماکروسافت، Language Integrated Query یا LINQ را همراه با.NET Framework 3.5 معرفی کرد. LINQ، برنامه نویسان را قادر به query کردن منبع داده ها با استفاده از یک query مانند syntax با C# و VB.NET می کند. این منبع داده ها می توانند collectionها، بانک های اطلاعاتی SQL Server، XML، و datasetها باشند. به غیر از آنچه که از طرف Microsoft تامین می شود، LINQ گستردنی هم هست. این بدین معناست که شما می توانید منابع داده ها را فراتر از آنچه که مایکروسافت ship میکند، query کنید. مثال هایی از چنین پیاده سازی هایی عبارتند از LINQ To Flickr، LINQ To Amazon، LINQ to Google، و غیره. در این مقاله نشان می دهم چگونه می توان از LINQ To SQL جهت اجرای عملیات های CRUD روی یک بانک اطلاعاتی SQL Server استفاده کرد. من از بانک اطلاعاتی Northwind استفاده می کنم و یک برنامه ASP.NET می سازم تا قابلیت های LINQ To SQL را نشان دهم. شما می توانید بانک اطلاعاتی Northwind را از اینجا دانلود کنید.

ابزار های لازم برای این مقاله:

1. Visual Studio 2008

2. .NET Framework 3.5

3. SQL Server 2005

ساختار راه حل (Solution Structure)

برای این مقاله، به دو پروژه نیاز داریم. یکی data layer است که generate خواهیم کرد، و دیگری یک برنامه تحت وب ASP.NET است. ساختار راه حل در Solution Explorer، شبیه مثال زیر است.

ایجاد Data Layer

قبل از اینکه data layer را generate کنیم، باید یک connection در Server Explorer ایجاد کنیم که به بانک اطلاعاتی Northwind اشاره می کند.

حالا data layerمان را با استفاده LINQ To SQL ایجاد می کنیم. برای انجام این کار، باید یک item جدید به پروژه data layer از نوع کلاسهای LINQ to SQL اضافه کنیم. نام آن را Northwind می گذاریم، همانگونه که در زیر نشان داده شده.

بعد از اضافه کردن یک کلاس LINQ to SQL، با یک designer surface روبرو می شویم. دراینجا تنها جداول را drag می کنیم که بخشی از data layer خواهد شد. در این مقاله، ما همه جداول را از طریق انتخاب کردن همه آنها در یک حرکت، روی designer، drag می کنیم. Designer ما باید بعد از drag کردن همه جداول روی آن شبیه شکل زیر باشد.

حالا باید solution را بسازیم تا مطمئن شویم همه چیز ok است. ما با موفقیت data layer مان را نصب کرده ایم. در Solution Explorer می بینم که دو فایل جدید داریم، یعنی Northwind.dbml.layout و Northwind.designer.cs. همچنین می بینیم که referenceهای لازم برای complie و run کردن کدمان نیز توسط Visual Studio اضافه شده اند.

فایل .cs حاوی کدی برای data layer ماست. بیایید کدی را که برای ما ایجاد شده، امتحان کنیم. نگاهی به کلاس Region خواهیم انداخت.

[Table(Name="dbo.Region")]
public partial class Region : INotifyPropertyChanging, INotifyPropertyChanged

این کلاس با صفت Table، آمیخته شده و برای خصوصیت Name، نام واقعی جدولی که در بانک اطلاعاتی مان موجود است، تخصیص داده شده. کلاس Region نیز interfaceهای InotifyPropertyChangingو INotifyPropertyChanged را پیاده سازی می کند. این interfaceها برای databinding بکار می روند. کلاس Region همچنین حاوی یک خصوصیت (property)برای هر ستون است. بیایید نگاهی به خصوصیت RegionDescription بیاندازیم.

[Column(Storage="_RegionDescription", DbType="NChar(50) NOT NULL",
CanBeNull=false)]
public string RegionDescription
{
  get
  {
    return this._RegionDescription;
  }
  set
  {
    if ((this._RegionDescription != value))
    {
      this.OnRegionDescriptionChanging(value);
      this.SendPropertyChanging();
      this._RegionDescription = value;
      this.SendPropertyChanged("RegionDescription");
      this.OnRegionDescriptionChanged();
    }
  }
}

ستون ها با صفت Column آمیخته شده اند و valueها برای storasge ارسال می شوند، DbType و CanBeNull نشان می دهند آیا ستون null است یا خیر.

استفاده از Data Layer

حالا که data layer را ایجاد کرده ایم، روی برنامه های تحت وب ASP.NET کار خواهیم کرد، یعنی جاییکه data layer را خواهیم دید. در ابتدا یک web form ایجاد می کنیم تا مشتری ها را جستجو کنیم و نتایج را نمایش دهیم. همچنین یک web form ایجاد می کنیم تا مشتری های جدید را وارد کنیم. بیایید با web form برای جستجوی مشتری ها شروع کنیم؛ برای این کار، از صفحه Default.aspx استفاده خواهیم کرد. تعداد کمی کنترل روی web form قرارمی دهیم. این کنترل ها، پارامترهای جستجو و یک دکمه در اختیار ما می گذارد که وقتی رویش کلیک کنیم، جستجو را انجام می دهند و نتایج را نمایش می دهد. شکل زیر نمایی از فرم بعد از قرار دادن کنترل هاست:

همچنین یک کنترل GridView روی فرم مان قرار می دهیم تا نتایج جستجو را نمایش دهد. حالا مقداری کد در event handler کلیک دکمه قرار می دهیم تا جستجو را انجام دهد و نتایج را در GridView نمایش دهد. باید مطمئن شوید که یک reference به پروژه Data Layer، System.Data.Linq و عبارت مناسب وجود دارد. در زیر آنچه که event handler کلیک دکمه دربرخواهد گرفت، آورده شده است:

protected void buttonSearch_Click(object sender, EventArgs e)
{
  using (NorthwindDataContext context = new NorthwindDataContext())
  {
    var customers =
      from c in context.Customers
      select c;
    gridViewCustomers.DataSource = customers;
    gridViewCustomers.DataBind();
  }
}

این کد، جدول مشتریان را در بانک اطلاعاتی northwind، query می کند و همه مشتری ها را باز می گرداند. حالا باید کمی آن را modify کنیم تا نام مشتری ها و نام شرکت ها را به عنوان پارانترهایی برای queryمان قبول کند. event handler بعد از modify کردن به صورت زیر خواهد بود:

protected void buttonSearch_Click(object sender, EventArgs e)
{
  using (NorthwindDataContext context = new NorthwindDataContext())
  {
    var customers =
      from c in context.Customers
      where (
        c.ContactName.Contains(textBoxCustomerName.Text.Trim())
        &&
        c.CompanyName.Contains(textBoxCompanyName.Text.Trim()))
      select c;
      gridViewCustomers.DataSource = customers;
      gridViewCustomers.DataBind();
  }
}

حالا نتایج جستجو فیلتر می شود.

حالا بیایید یک فرم data entry برای مشتری ها ایجاد کنیم. باید یک web form جدید در پروژه ASP.NET مان وارد کنیم و آن را CustomerEntry بنامیم. برای شروع باید مطمئن شویم که فرم ما حاوی fieldهای لازم برای وارد کردن یک مشتری جدید است. فرم ما بعد از تکمیل شبیه نمونه شکل یر خواهد بود.

انتظار ما این است که هنگامی که روی دکمه Save Customer کلیک می کنیم، یک ردیف (row) جدید به جدول مشتری ها اضافه شود. کد زیر این کار را برای ما انجام می دهد.

protected void buttonSave_Click(object sender, EventArgs e)
{
  using (NorthwindDataContext context = new NorthwindDataContext())
  {
    Customer customer = new Customer
    {
      CustomerID = textBoxCustomerID.Text,
      CompanyName = textBoxCompanyName.Text,
      ContactName = textBoxCustomerName.Text,
      ContactTitle = textBoxTitle.Text,
      Address = textBoxAddress.Text,
      City = textBoxCity.Text,
      Region = textBoxRegion.Text,
      PostalCode = textBoxPostalCode.Text,
      Country = textBoxCountry.Text,
      Phone = textBoxPhone.Text,
      Fax = textBoxFax.Text
    };
    context.Customers.InsertOnSubmit(customer);
    context.SubmitChanges();
  }
}

می توان یک ستون که از قبل در بانک اطلاعاتی موجود است را ابتدا توسط بازیابی داده ها و سپس توسط submit کردن آن از طریق DataContext، آپدیت کرد.

نتیجه گیری

در این مقاله، ما فقط یک عبارت SQL مستقل ننوشتیم تا داده ها را به یک بانک اطلاعاتی وارد کنیم یا آن را بازیابی کنیم. این، زیبایی LINQ To SQL است. به علاوه، کد بازیابی ما هنگامی که در C# است بسیار شبیه یک query است. باید از مزایای چنین روش موثر و یکپارچه ای برای کار کردن با داده ها قدردانی کرد.

اگر فرض کنیم که متد GetStrings() تحت عنوان یک TVF با همان نام ثبت شده است، آنگاه مثلا زیر، قطعه ای از T-SQL از یک stored procedure است که از این TVF برای بیرون کشیدن (extract) line items ، در شکل جدولی استفاده می کند.

CREATE PROCEDURE Insert_Order @cust_id int, @lineitems nvarchar(8000)
AS
BEGIN
    ...
    INSERT LineItems
    SELECT * FROM dbo.GetStrings(@lineitems)
    ...
END

کدام را استفاده کنیم؟

تصمیم در مورد اینکه یک stored procedure را در ترکیب با SqlPipe- چه غیر مستقیم در T-SQL و چه مستقیم د ر یک CLR routine- یا یک table-valued function استفاده کنیم، بستگی به فاکتورهای زیادی دارد که باید مد نظر قرار گیرند، که شامل composability requirements، منبع داده ها، نیاز به اثرات جانبی، و typing requirements برای خروجی است. به ترتیب هر کدام از آنها را مورد بحث قرار می دهیم.

Composability Requirements

شاید استفاده دوباره یا تغییر (manipulation) خروجی بدست آمده درون یک TVF یا یک stored procedure، مطلوب باشد. Table-valued functions از نقطه نظر composability، پرکاربردتر هستند، مثلاً نوع بازگشت (return type) یک TVF، یک rowset نسبی است که در هر جاییکه چنین ساختاری مجاز است، قابل استفاده است. مخصوصاً، دربخش FROM در عبارت SELECT، قابل استفاده است، و چنین خروجیی می توانند از composability of SELECT در sub-queryها، عبارات INSERT...SELECT، جدولهای مشتق شده، و جدول عبارات استفاده کنند.

از سوی دیگر، ، می توان stored procedureها را فقط به عنوان بخشی از ترکیب INSERT...EXEC، از درون T-SQL language، compose کرد، که به خروجی بدست آمده اجازه ذخیره شدن در یک جدول دایم یا موقت را می دهد. عملیات INSERT، یک کپی واقعی از داده ها را ارائه می کند که احتمالاً روی performance تاثیر گذار خواهد بود.

اگر composability و استفاده مجدد از خروجی درون سرور یک نیاز باشند، TVFها جایگزینهای بهتری هستند. اگر خروجی بدست آمده فقط نیاز به stream شدن به client یا middle-tier داشته باشند، هر دو روش منطقی است.

منبع داده ها (Source of the Data)

منبع داده هایی که بازگردانده می شود، فاکتور مهم دیگری در انتخاب بین پیاده سازی بر اساس T-SQL یا CLR است. خروجی را میتوان هم از طریق خواندن source در local instance با استفاده از ADO.NET provider یا از طریق یک source خارج از SQL Server، بدست آورد. در منابع خارجی، پیاده سازی بر پایه CLR، انتخاب بهتری از T-SQL است، به علت داشتن سهولتی که logic accessing the external data، توسط آن انجام می شود.

در صورت بدست آوردن خروجی بر اساس یک query که با استفاده از ADO.NET provider روی local instance اجرا شده، یک stored procedure، معمولاً یک query را اجرا می کند، از طریق خروجی تکرار می شود، و بعضی از عملیات ها را روی rowها، قبل از بازگرداندن از طریق یک SqlPipe، انجام می دهد.

با یک TVF ، برنامه نویسان می توانند یک شی data reader را خوانده و در یک کلکسیون در حافظه بارگذاری کنند. اما، SQL Server 2005، به requestها اجازه نمی دهد تا زمانی که یک table-valued function باز می گردد، pending بمانند؛ هر query که از طریق ADO.NET provider اجرا شده است، باید کاملاً اجرا شوند و خروجی باید به طور کامل قبل از اینکه function body بتواند بازگردد، consume شوند. اگر عبارت بازگشت (return statement)، هنگامی که SqlDataReader operationها در ADO.NET provider، معلق (pending) است، اجرا شود، یک error ممکن است روی دهد. این بدین معناست که در اکثر مواردی که داده ها از local database instance بازگردانده می شوند، نمی توان داده ها را از طریق یک CLR TVF، stream کرد. اگر فاکتورهای دیگر از قبیل composability، مستلزم نوشته شدن به عنوان یک TVF باشد، شاید نوشتن آن در T-SQL تنها آپشن باشد. در غیر این صورت، استفاده از یک stored procedure مدیریت شده که از SqlPipe استفاده می کند، ممکن است.

در مواردی که در آنها خروجی قرار است از درون یک stored procedure بر اساس داده ها از local instance، بدست آید، استفاده از APIهای SendResults فقط در مواردی معنا دارد که خروجی نیاز به مقداری اصلاح یا تغییر رویه ای (procedural modification or manipulation) دارد.

عملیاتهایی (operation) با اثرات جانبی (Side-Effect)

بطور کلی، operationهایی که دارای side-effect هستند- operationهایی که حالت database، از قبیل عبارات DML، یا transaction operationها، را تغییر می دهند- از functionهایی (مثلاً functionهای table-valued) که تعریف کاربر است، منع می شوند؛ اما شاید این operationها مطلوب باشند. مثلاً ممکن است کسی بخواهد یک SAVEPOINT transaction را set کند، یا یک operation اجرا کند، و در صورت روی دادن error، آن را به SAVEPOINT، rollback کند.

با فرض اینکه functionهایی که کاربر تعریف می کند، منع می شوند، چنین سناریویی فقط از طریق یک stored procedure قابل پیاده سازی است، و خروجی باید از طریق SqlPipe بازگردانده شوند. اما به یاد داشته باشید، به operationهایی با side-effect، اجازه اجرا شدن از طریق ADO.NET provider، زمانیکه SqlPipe مشغول فرستادن خروجی است، داده نمی شود. این operationها فقط قبل از اینکه خروجی شروع یا بعد از اینکه تکمیل شوند، مجاز هستند.

Typing و تعداد خروجی

توصیف خروجی بدست آمده از طریق یک CLR stored procedure توسط SqlPipe، با خروجی بدست آمده از یک CLR TVF متفاوت، و با همتایانشان در T-SQL یکسان هستند.

از سوی دیگر، یک stored procedure declaration، هیچ عبارتی در مورد خروجی بدست آمده – یا حتی آیا آنها را بدست می آورد- نمی سازد. ممکن است آسان بنظر آید، و گرچه مسلماً انعطاف (flexibility) بیشتری ایجاد می کند، ولی باید در نوشتن applicationهایی که stored procedureها را اجرا می کنند دقت بیشتری کرد، زیرا ممکن است است به طور دینامیکی شکل خروجی را دوباره تعریف کند. اما، اگر لازم باشد که schema برای خروجی در میان فراخوانی ها ()invocation متغیر باشند، باید از یک stored procedure استفاده کرد، زیرا فقط SqlPipe این انعطاف را در اختیار می گذارد.

در حقیقت، نوع داده های ضعیف خروجی ها تولید شده از طریق sqlpipe درون Stored Procedureها، فراتر از Schemaی یک خروجی واحد است که احتمال بازگشت چندین خروجی متغیر را دربر می گیرد. هم typeها و هم تعداد خروجی را می توان توسط stored procedure به طور دینامیکی تعیین کرد.

خلاصه

جدول زیر خلاصه ای از راهنماییهایی در مورد چگونگی تعیین اینکه آیا یک application ویژه باید در T-SQL یا CLR نوشته شود، و یا اینکه آیا یک stored procedure یا یک table-valued function باید استفاده شود، است.

در اکثرجاهای این بخش، ارسال خروجی از طریق SqlPipe، به شدت با procedureها پیوند خورده است. گرچه SqlPipe و امکان بازگرداندن خروجی در بدنه CLR triggerها در دسترس هستند، اما این عمل به شدت نهی می شود، زیرا ممکن است به خروجی غیر منتظره ای برای issuing Data Manipulation Language یا عبارات Data Definition Language statements با triggerهایی که روی اشیاء هدف تعریف شده، منجر شود.

اجرای Aggregationهای سفارشی روی داده ها

سناریوهایی وجود دارند که ممکن است aggregation نیاز به اجرا شدن روی data داشته باشد، که شامل اجرای محاسبات آماری از قبیل یافتن میانگین ها، انحراف از معیار (standard deviation) و غیره است. اگر aggregation function مورد نظر، در داخل ساخته نشود، راههای زیادی برای اضافه کردن (add) functionality در SQL Server 2005 وجود دارد:

· نوشتن aggregation به عنوان یک aggregation تعریف شده توسط کاربر (UDA).

· نوشتن aggregation با استفاده از یک CLR stored procedure.

· استفاده از یک cursor جانبی سرور (server-side) در T-SQL.

بیایید این سه راه را در بافت (context) یک aggregation function ساده که محصول یک دسته از valueهای معین را محاسبه می کند، امتحان کنیم.

مثال: یک Product که به عنوان یک aggregate function تعریف شده توسط کاربر، پیاده سازی شده

در اینجا کدی برای این task آورده شده که به عنوان یک aggregate تعریف شده توسط کاربر نوشته شده. Logic محاسبه این محصول، وقتی که وارد می شود، در متد Accumulate() وجود دارد. متد Merge()، تعریف می کند اگر این دو aggregate ادغام شوند، چه اتفاقی می افتد.

در Visual Basic .NET:

Imports System
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

<Serializable()> _
<SqlUserDefinedAggregate(Format.Native)> _
Public Structure ProductAgg

    Private product As SqlInt32

    Public Sub Init()
        product = 1
    End Sub

    Public Sub Accumulate(ByVal value As SqlInt32)
        product = product * value
    End Sub

    Public Sub Merge(ByVal group As ProductAgg)
        product = product * group.product
    End Sub

    Public Function Terminate() As SqlInt32
        Return product
    End Function

End Structure

در C#:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct ProductAgg
{
    private SqlInt32 product;

    public void Init()
    {
        product = 1;
    }

    public void Accumulate(SqlInt32 value)
    {
        product = product * value;
    }

    public void Merge(ProductAgg group)
    {
        product = product * group.product;
    }

    public SqlInt32 Terminate()
    {
        return product;
    }
}

بعد از اینکه این type، با SQL Server ساخته و ثبت شد، می توان آن را فقط به عنوان یک built-in aggregate از T-SQL استفاده کرد:

SELECT dbo.ProductAgg(intcol) 
FROM tbl
GROUP BY col

مثال: Product، به عنوان یک Stored Procedure مدیریت شده

یک stored procedure را می توان ایجاد کرد که روی data تکرار می شود تا محاسبه (computation) را اجرا کند. این تکرار با استفاده از کلاس SqlDataReader بدست می آید، همانطور که در پایین نشان داده شده.

در Visual Basic .NET:

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Data.SqlClient

Partial Public Class StoredProcedures

    <SqlProcedure()> _
    Public Shared Sub VBProductProc(ByRef value As SqlInt32)

        ' The empty product is 1
        value = 1

        Using conn As New SqlConnection("context connection = true")
            conn.Open()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = conn
            cmd.CommandText = "SELECT intcolumn FROM tbl"
            Dim r As SqlDataReader = cmd.ExecuteReader()
            Using r
                Do While r.Read()
                    value = value * r.GetSqlInt32(0)
                Loop
            End Using
            conn.Close()
        End Using
    End Sub

End Class

در C#:

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Data.SqlClient;
public partial class StoredProcedures
{
    [SqlProcedure]
    public static void ProductProc(out SqlInt32 value)
    {
        // Ensure that we write to value.
        // Empty product is 1.
        value = 1;
        
        using (SqlConnection conn = 
            new SqlConnection("context connection = true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT intcolumn FROM tbl";
            SqlDataReader r = cmd.ExecuteReader();
            using (r)
            {
                while (r.Read()) //skip to the next row
                {
                    value *= r.GetSqlInt32(0);
                }
            }
        }
    }
}

می توان این را با استفاده از عبارت T-SQL EXEC فرا خواند:

EXEC Product @p OUTPU

مثال: Product به عنوان یک T-SQL Stored Procedure که از یک Cursor استفاده می کند

نهایتاً، می توان یک T-SQL stored procedure ایجاد کرد که یک query را اجرا و محاسبه را با استفاده از یک T-SQL cursor انجام می دهد تا data را تکرار کند.

T-SQL:

create procedure TSQL_ProductProc (@product int output)
as
begin
    declare @sales int
    declare c insensitive cursor for select intcolumn from tbl

    set @product = 1

    open c    
    fetch next from c into @sales
   
    while @@FETCH_STATUS = 0
    begin
        set @product = @product * @sales
        fetch next from c into @sales
    end
   
   close c
   deallocate c
end
go

خلاصه

تصمیم گیری در مورد استفاده از یک UDA یا یکی از راه حلهای دیگر برای بدست آوردن خروجی به چند عامل بستگی دارد، که شامل composability requirements، مشخصه های aggregation algorithm، و نیاز به side-effect می شود.

در واقع، یک UDA، یک شی مستقل است که از هر T-SQL query، قابل استفاده است، عموماً در همان مکانی که یک system aggregate استفاده می شود. هیچ تصوری در مورد یک query که روی آن عمل می کند وجود ندارد. مثلاً، می توان آن را در view definitionها (اما نه در indexed viewها) و در sub-queryهای ترازویی (scalar) به شمار آورد.

می توان UDAها را قبل از کلمه ORDER BY در یک query ارزیابی کرد، پس هیچ تضمینی برای ترتیبی که valueها به aggregation function ارائه می شوند وجود ندارد. بنابراین، اگر aggregation algorithm باید valueها را به ترتیب خاصی consume کند، نمی توان از یک UDA استفاده کرد. یک UDA نیز دقیقاً valueهای کل یک گروه را consume می کند و یک value واحد باز می گرداند. اگر مشکل حل نشد، آنگاه باید از یک تکنیک دیگر استفاده کرد.

همچنین یک UDA، هیچ data access را اجرا نمی کند و هیچ side-effect ندارد؛ اگر هر یک از اینها لازم باشند، آنگاه باید از یک stored procedure استفاده کرد.

گرچه UDAها دارای محدودیتهایی هستند، اما احتمال دارد بهترین performance را از آپشنهای ارائه شده انجام دهند، پس aggregation عموماً باید از طریق یک UDA اجرا شود، مگر اینکه الزامات (requirement) دیگر مانع آن شوند.

Typeهای تعریف شده توسط کاربر

حالا به یکی از قدرتمندترین ویژگیهای SQL Server 2005 می رسیم که اغلب مورد سوء تفاهم قرار می گیرد. با Typeهای تعریف شده توسط کاربر (UDTها)، می توان scalar type system را در database، گسترش داد. و این، فراتر از تنها تعریف کردن alias برای یک system type است که در نسخه قبلی SQL Server دردسترس بود. تعریف یک UDT، به سادگی نوشتن یک کلاس در کد مدیریت شده، ایجاد یک اسمبلی، و سپس (register) ثبت کردن type در SQL Server با استفاده از عبارت CREATE TYPE است. مثال زیر، اسکلت یک کد است که شکل ساختاری یک UDT را نشان می دهد.

در Visual Basic .NET:

<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> _
Public Structure SimpleType
    Implements INullable

    Public Overrides Function ToString() As String
        ...
    End Function

    Public ReadOnly Property IsNull() As Boolean Implements _
        INullable.IsNull
        ...
    End Property

    Public Shared ReadOnly Property Null() As SimpleType
        ...
    End Property

    Public Shared Function Parse(ByVal s As SqlString) As SimpleType
        ...
    End Function

End Structure

در C#:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct SimpleUdt : INullable
{
    public override string ToString() { ...  }
    public bool IsNull { ...  }
    public static SimpleUdt Null
    {
        get { ...  }
    }
    public static SimpleUdt Parse(SqlString s) { ...  }
}

می توان این را در T-SQL بارگذاری (load) و استفاده کرد:

CREATE TYPE simpleudt FROM [myassembly].[SimpleUdt]
CREATE TABLE t (mycolumn simpleudt)

کی یک UDT ایجاد کنیم؟

UDTها در SQL Server 2005، یک مکانیزم توسعه پذیر object-relational نیستند؛ بلکه راهی برای گسترش scalar type system در database هستند. scalar type system شامل typeهای ستونی (columnar) است که با SQL Server (int، nvarchar، uniqueidentifier، و غیره) منتقل و استفاده می شوند. با UDTها، می توان یک typeجدید تعریف کرد که میتوان به جای یک built-in scalar type استفاده کرد. اگر type، یک value اتمی است که برای مدل شدن به عنوان یک ستون مناسب است، یک UDT ایجاد کنید.

کاندیدهای خوب جهت پیاده سازی یک UDT، شامل تاریخ custom و typeهای time data در تقویم های مختلف و typeهای currency data است. یک UDT، یک کلاس واحد است که در معرض همه رفتارهای (behavior) دردسترس روی type است و داده های بنیادی ذخیره شده توسط type را خلاصه می کند: کل data access از programmatic interface در UDT استفاده می کنند. اغلب می توان از functionality موجود در .NET framework — از قبیل بین المللی کردن (internationalization) یا calendar functionality — استفاده کرد تا functionality را که به سختی فراهم می شود، فراهم کرد.

کی یک UDT ایجاد نکنیم؟

از یک UDT نباید برای مدل سازی اشیای business از قبیل کارمندان، قراردادها، یا مشتری ها استفاده کرد. SQL Server با یک UDT به عنوان یک واحد (unit) رفتار می کند که به آن opaque است. بعضی از مسائل با UDTهای پیچیده، شامل محدودیت 8 کیلو بایتی برای typeها، محدودیتهای indexing، و این واقعیت است که کل value باید هنگامی که هر value در UDT، update می شود، update شود.

فاکتورهایی که هنگام طراحی یک UDT باید در نظر گرفت

از آنجاییکه UDTها ستونی هستند، می توان index ها را، روی کل valueهای UDT تعریف کرد؛ همانطور که referential integrity constraint، از قبیل unique بودن، را می توان. همچنین می توان از UDTها در مقایسه و مرتب کردن سناریوها استفاده کرد.

مقایسه Valueهای UDT، بوسیله مقایسه binary representation of the type انجام می شود. اگر از Format.Native به عنوان مکانیزم پایداری (persistence mechanism) استفاده شود، آنگاه یک persisted form با استفاده از همان ترتیب field، به عنوان آنچه که در type تعریف می شود، ایجاد می شود؛ پس باید دقت کرد که آنها در جای صحیح قرار می گیرند.

غیر از مقایسه، هر عملیاتی روی یک UDT، نیازمند این است که UDT value، de-serialize شود و یک متد، فراخوانده شود. این الگو (pattern) موجب تحمیل بار اضافی روی server می شود، که باید هنگام تصمیم گیری در مورد اینکه آیا آن را به عنوان یک UDT، مدلسازی کنیم یا نکنیم، مد نظر قرار بگیرد. UDTها زمانی که یک type ،که نیاز به model شدن دارد، رفتارهای پیچیده ای دارد، مفیدتر هستند. اگرtype نسبتاً ساده باشد، آنگاه شاید بهتر باشد از ساختار UDT پرهیز کنیم.

نهایتاً، می توان از متدهای static یک UDT به عنوان یک مکانیزم بسته بندی (packaging) راحت جهت ذخیره سازی library تابعهای مربوط استفاده کرد. متدهای static را می توان از T-SQL با استفاده از syntaz زیر فراخوانی کرد:

select Type::Function(@arg1)

مثال: تاریخ های غیر غربی

می خواهیم valueهای زمان و تاریخ را با استفاده از تقویم "ام القرا" که با تقویم گریگوری که SQL Server datetime data type از آن استفاده می کند، ذخیره کنیم. می خواهیم این data type همان رفتارهای پایه ای را داشته باشد، بویژه string conversion، قابلیت بازیابی اجزای data، و محاسبات تاریخ و غیره.

مثال زیر از یک type تعریف شده توسط کاربر، یک پیاده سازی ساده از این data type است، و از UmAlQuraCalendar type استفاده می کند، که در نسخه 2.0 .NET Framework جدید است. متوجه شدن این مثال کمک زیادی به فراهم کردن متذهای لازم می کند.

UDT ام القرا در C#

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;

[Serializable]
[SqlUserDefinedType(Format.Native, IsByteOrdered = true)]
public struct UmAlQuraDateTime : INullable
{

    /*
     * Private state.
     */ 
    
    private long dtTicks;
    private bool isNull;

    // Calendar object used for all calendar-specific operations
    private static readonly UmAlQuraCalendar s_calendar = 
        new UmAlQuraCalendar();

    // For correct formatting we need to provie a culture code for 
    // a country that uses the Um Al Qura calendar: Saudi Arabia.
    private static readonly CultureInfo ci = 
        new CultureInfo("ar-SA", false);
    

    /*
     * Null-Handling
     */

    // get a null instance
    public static UmAlQuraDateTime Null
    {
        get
        {
            UmAlQuraDateTime dt = new UmAlQuraDateTime();
            dt.isNull = true;
            return dt;
        }
    }

    public bool IsNull
    {
        get
        {
            return this.isNull;
        }
    }

    /*
     * Constructors
     */

    public UmAlQuraDateTime(long ticks)
    {
        isNull = false;
        dtTicks = ticks;
    }

    public UmAlQuraDateTime(DateTime time) : this(time.Ticks)
    {
    }

    /*
     * Factory routines.
     */

    public static UmAlQuraDateTime Parse(SqlString s)
    {
        if (s.IsNull) return Null;
        DateTime t = DateTime.Parse(s.Value);
        return new UmAlQuraDateTime(t);
    }

    public static UmAlQuraDateTime ParseArabic(SqlString s)
    {
        if (s.IsNull) return Null;
        DateTime t = DateTime.Parse(s.Value, ci);
        return new UmAlQuraDateTime(t);
    }

    public static UmAlQuraDateTime FromSqlDateTime(SqlDateTime d)
    {
        if (d.IsNull) return Null;
        return new UmAlQuraDateTime(d.Value);
    }

    public static UmAlQuraDateTime Now
    {
        get
        {
            return new UmAlQuraDateTime(DateTime.Now);
        }
    }

    /*
     * Conversion Routines
     */

    public DateTime DateTime
    {
        get { return new DateTime(this.dtTicks); }
    }

    public SqlDateTime ToSqlDateTime()
    {
        return new SqlDateTime(this.DateTime);
    }

    public override String ToString()
    {
        return this.DateTime.ToString(ci);
    }

    public String ToStringUsingFormat(String format)
    {
        return this.DateTime.ToString(format, ci);
    }

    /*
     * Methods for getting date parts.
     */

    public int Year
    {
        get
        {
            return s_calendar.GetYear(this.DateTime);
        }
    }

    public int Month
    {
        get
        {
            return s_calendar.GetMonth(this.DateTime);
        }
    }

    public int Day
    {
        get
        {
            return s_calendar.GetDayOfMonth(this.DateTime);
        }
    }

    /*
     * Date arithmetic methods.
     */

    public UmAlQuraDateTime AddYears(int years)
    {
        return new 
            UmAlQuraDateTime(s_calendar.AddYears(this.DateTime, years));
    }

    public UmAlQuraDateTime AddDays(int days)
    { 
        return new 
            UmAlQuraDateTime(s_calendar.AddDays(this.DateTime, days));
    }   

    public double DiffDays(UmAlQuraDateTime other)
    {
        TimeSpan diff = DateTime.Subtract(other.DateTime);
        return diff.Days;
    }
}

وقتی این type در SQL Server بارگذاری می شود، می توان این type از طریق T-SQL استفاده کرد. در زیر چند مثال T-SQL آورده شده اند که از این UDT و خروجیی که بدست می آورند، استفاده می کنند.

ابتدا یک تاریخ ام القرا را parse و سپس آن را در دو فرمت با معادلهای غربی چاپ می کنیم:

DECLARE @d UmAlQuraDateTime
SET @d = UmAlQuraDateTime::ParseArabic('01/02/1400')
PRINT @d.ToString()
PRINT @d.ToStringUsingFormat('F')
PRINT @d.ToSqlDateTime()

نتیجه زیر حاصل می آید:

می توانیم تاریخ های غربی را نیز به ام القرا تبدیل کنیم:

DECLARE @n DateTime
SET @n = 'March 20, 2005'
DECLARE @d UmAlQuraDateTime
SET @d = UmAlQuraDateTime::FromSqlDateTime(@n)
PRINT @n
PRINT @d.ToString()

نتیجه:

نهایتاً، بااین type می توانیم جداولی را با ستون، ایجاد و اصلاح کنیم:

CREATE TABLE dates (
   western DateTime,
   umalqura UmAlQuraDateTime
)

INSERT INTO dates(western) VALUES ('June 1, 2005')
INSERT INTO dates(western) VALUES ('July 1, 2005')

UPDATE dates 
SET umalqura = UmAlQuraDateTime::FromSqlDateTime(dates.western)

SELECT western, umalqura.ToString() as umalqura FROM dates

این جدول، نتیجه نهایی است:

نتیجه گیری

این مقاله، راهنماییها، سناریوهای استفاده واقعی، و نمونه هایی را با استفاده از ویژگیهای CLR integration در SQL Server 2005، ارائه کرده است. برنامه نویسان و معماران (Database application developers and architects) باید از این مقاله جهت ترکیب با documentation در ویژگیهای دیگر SQL Server 2005، از قبیل Transact-SQL، XML، و Service Broker استفاده کنند.

حل کردن مشکلات رایج در Database Programming

بخش قبلی، برنامه نویسی بر پایه CLR را در سطحی بالاتر که راجع به T-SQL، extended stored procedures XPها(، و کدی در middle-tier است، قرار داد. در این بخش، ما نگاهی به دسته ای از taskها و الگوهایی (pattern) که یک database application developer ممکن است با آن مواجه شود، خواهم انداخت، و چگونگی استفاده (یا عدم استفاده) از CLR integration جهت حل کردنشان را مورد بحث قرار خواهیم داد. ما چندین code example در اختیار قرار می دهیم، هم در C# و هم در Visual Basic .NET.

اعتبار دهی داده ها با استفاده از NET Framework Library.

CLR integration در SQL Server 2005 به کاربران اجازه می دهد از عملکرد غنی (rich functionality) که توسط class libraryهای .NET Framework فراهم شده، برای حل مشکلات database programmingشان استفاده کنند.

یک مثال، استفاده از expressionهای مرتب است، جهت فراهم کردن الگوهای پیچیده تر نسبت به آنچه که با استفاده از اپراتور LIKE در T-SQL دردسترس است. کد زیر را که چیزی جز یک wrapper ساده اطراف کلاس RegEx در System.Text.RegularExpressions namespace نیست، ملاحظه فرمایید.

در Visual Basic .NET:

Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text.RegularExpressions

Partial Public Class Validation

    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
    Public Shared Function RegExMatch(ByVal pattern As String, _
        ByVal matchString As String) As Boolean

        Dim r1 As Regex = New Regex(pattern.TrimEnd(Nothing))
        Return r1.Match(matchString.TrimEnd(Nothing)).Success
    End Function

    <SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
    Public Shared Function ExtractAreaCode(ByVal matchString As String)_
        As SqlString

        Dim r1 As Regex = New Regex("\((?<ac>[1-9][0-9][0-9])\)")
        Dim m As Match = r1.Match(matchString)

        If m.Success Then
            Return m.Value.Substring(1, 3)
        Else
            Return SqlString.Null
        End If

    End Function
End Class

در C#:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
    
public partial class Validation
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static bool RegExMatch(string pattern, string matchString)
    {
        Regex r1 = new Regex(pattern.TrimEnd(null));
        return r1.Match(matchString.TrimEnd(null)).Success;
    }

    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString ExtractAreaCode(string matchString)
    {

        Regex r1 = new Regex("\\((?<ac>[1-9][0-9][0-9])\\)");
        Match m = r1.Match(matchString);
        if (m.Success)
            return m.Value.Substring(1, 3);
        else return SqlString.Null;
    }
}

بیایید فرض کنیم متدهای RegExMatch() و ExtractAreaCode()، به عنوان تابع های (function) تعریف شده توسط کاربر، در database با آپشن RETURNS NULL ON NULL INPUT ثبت شده اند، به طوری که function، هنگامی که هر یک از ورودی هایش (input) بی اثر هستند، بی اثر باز می گردد. این، به ما اجازه می دهد از نوشتن هر special null handling code درون function بپرهیزیم.

حالا می توانیم constraintهایی روی ستونهای جدولی که از کد بالا برای اعتبار دهی آدرسهایی ایمیل و شماره تلفنها استفاده می کند، به ترتیب زیر تعریف کنیم:

CREATE TABLE contacts 
(
    firstName nvarchar(30),
    lastName  nvarchar(30),
    emailAddress nvarchar(30) CHECK 
    (dbo.RegExMatch('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu)', 
        emailAddress) = 1),
    usPhoneNo nvarchar(30) CHECK 
    (dbo.RegExMatch(
        '\([1-9][0-9][0-9]\) [0-9][0-9][0-9]\-[0-9][0-9][0-9][0-9]', 
        usPhoneNo)=1),
    areaCode AS dbo.ExtractAreaCode(UsPhoneNo) PERSISTED 
)

به یاد داشته باشید که ستون areaCode، یک ستون دایم و محاسبه شده است، که area code را از ستون usPhoneNo با استفاده از تابع ExtractAreaCode() بیرون می کشد. ستون areaCode ممکن است index شود، و queryها را در جدولی که توسط area code دنبال مخاطبین می گردد، تسهیل می کند.

بطور کلی، این مثال نشان می دهد چگونه می توان از libraryهای .NET Framework برای افزایش T-SQL built-in function library با تابع های مفید که در T-SQL به سختی بیان می شوند، استفاده کرد.

بدست آوردن خروجی

نیاز به بدست آوردن خروجی از یک database object (از قبیل یک stored procedure یا یک view) که درون سرور اجرا می شود، یکی از رایج ترین taskهای database programming است. اگر بتوان خروجی را با استفاده از یک query واحد ایجاد کرد، آنگاه می توان این کار را تنها با استفاده از یک view یا یک inline table-valued function انجام داد. اما، اگر برای ایجاد خروجی، نیاز به عبارات چند گانه و procedural logic باشد، آنگاه دو آپشن وجود دارد: stored procedureها و table-valued functionها. در حالیکه SQL Server 2000 دارای table-valued functionها است، اما می توان آنها را فقط در T-SQL نوشت. با CLR integration در SQL Server 2005، می توان این functionها را با استفاده از یک زبان مدیریت شده (managed language) نیز نوشت. در این بخش، ما نگاهی به چگونگی نوشتن stored procedureها و table-valued functionها با استفاده از CLR می اندازیم.

برگرداندن خروجی نسبی (relational results) از T-SQL، چه به عنوان return value یک table-valued function، چه از طریق "caller's pipe" مستقیم و همیشه حاضر، درون یک stored procedure ممکن است: از هرجایی در یک stored procedure –بدون در نظر گرفتن nesting در execution level– یک عبارت SELECT اجرا شده، خروجی را به caller باز می گرداند.

اگر دقیق تر بگوییم، این درست است که عبارات SELECT، مقدار دهی به متغیررا اجرا نمی کنند. عبارات FETCH، READTEXT، PRINT، و RAISERROR، نیز خروجی را مستقیماً به caller بازمیگردانند.

به یاد داشته باشید که " caller" بدرستی تعریف نشده است؛ بستگی به فراخوانی بافت (invocation context) در stored procedure دارد.

اگر یک stored procedure از هر client data access API(ا زقبیل ODBC، OLEDB، یا SQLClient) فراخوانده شود، caller، API واقعی و هر abstraction (مثلاً، hstmt، IRowset، یا SqlDataReader) که برای ارائه خروجی مهیا می کند، است. بطور کلی، این بدین معناست که خروجی بدست آمده از درون یک stored procedure، به API فراخواننده بازگردانده خواهد شد، و همه frameهای T-SQL روی stack را کنار می گذارد، مانند مثال زیر:

 class="csharpcode">CREATE PROC proc1 AS
   SELECT col1 FROM dbo.table1;
CREATE PROC proc2 AS 
   EXEC proc1;

هنگام اجرای رویه proc2، خروجی بدست آمده از proc1، به caller of proc2 می روند. فقط یک راه هست که در آن proc2 می تواند خروجی بدست آمده را capture کند: از طریق stream کردن خروجی به disk، با استفاده از INSERT یا EXEC، به یک جدول دایم یا موقت، یا یک جدول متغیر.

CREATE PROC proc2 AS
   DECLARE @t TABLE(col1 INT);
   INSERT @t (col1) EXEC proc1;
   -- do something with results

Stored procedureهای SQL Server 2005 CLR، نوع جدیدی از caller را معرفی می کنند. وقتی یک query با استفاده از ADO.NET provider از درون یک frame مدیریت شده اجرا می شود، خروجی از طریق شی SqlDataReader قابل دسترسی می شوند و می توانند درون stored procedure مصرف شوند.

در Visual Basic .NET:

...
Using conn As New SqlConnection("context connection = true")
    conn.Open()
    Dim cmd As SqlCommand = new SqlCommand( _
        "SELECT col1 FROM dbo.table1", conn)
    Dim reader As SqlDataReader = cmd.ExecuteReader()
           
    Do While reader.Read()
        ' Do something with current row
    Loop       
End Using
...

در C#:

...
using (SqlConnection conn= new SqlConnection("contect connection = true"))
{
    ...
    SqlCommand cmd = new SqlCommand(
        "SELECT col1 FROM dbo.table1", conn);
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        // do something with current row
    }
    ...
}
...

مسئله ای که باقی می ماند، این است که یک managed routine چگونه خروجیش را به caller باز می گرداند. این کار در stored procedureها و table-valued functionها به طور متفاوتی انجام می شود. یک stored procedure از یک مثال static که از کلاس SqlContext قابل دسترسی است، جهت بازگرداندن data استفاده می کند، در حالیکه یک table-valued function، یک interface را پیاده سازی می کند که به SQL Server اجازه بازیابی خروجی را می دهد. هر دوی اینها در قسمت بعدی مورد بحث قرار می گیرند.

Stored Procedureهای CLR و SqlPipe

در میان متدهای دردسترس در کلاس SqlPipe، ساده ترینشان، ExecuteAndSend() است که یک command object را یک پارامترورودی در نظر می گیرد. این متد، command را اجرا می کند، اما به جای اینکه خروجی برای managed frame قابل دسترس کند، خروجی را به فراخواننده stored procedure ارسال می کند. این، معادل قرار دادن یک عبارت درون یک T-SQL stored procedure است، و در حالیکه ناخوشایندتر است، همراه با معادل T-SQL، روی par است.

یک stored procedure ساده برای اجرای یک SELECT در T-SQL:

CREATE PROC proc1 AS
   SELECT col1 FROM dbo.table1;

معادلش در C#:

...
[Microsoft.SqlServer.Server.SqlProcedure]
public static void proc1()
{
    using (SqlConnection conn =
        new SqlConnection("context connection = true"))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(
            "SELECT col1 FROM dbo.table1", conn);
        SqlContext.Pipe.ExecuteAndSend(cmd);
        conn.Close();
    }
}
...

و در Visual Basic .NET:

...
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub VBproc1()
    Using conn As New SqlConnection("context connection=true")
        conn.Open()
        Dim cmd As SqlCommand = new SqlCommand( _
            "SELECT col1 FROM dbo.table1", conn)
        SqlContext.Pipe.ExecuteAndSend(cmd)
        conn.Close()
    End Using
End Sub
...

SqlPipe.ExecuteAndSend()، در سناریوهایی که داده های در شرف بازگشت، مستقیماً توسط یک query اجرا شده بدست می آیند، خوب جواب می دهد. اما، ممکن است مواردی وجود داشته باشد که در آنها، تغییر (manipulation) داده ها قبل از ارسال آنها، یا ارسال داده هایی که از sourceهای خارج از local SQL Server instance بدست آمده، مطلوب باشد.

SqlPipe، گروهی از متدهایی را که با هم کار می کنند تا applicationها را قادر به باز گرداندن خروجی اختیاری به caller، (SendResultsStart()،SendResultsRow()، و SendResultsEnd()) کنند، در اختیار می گذارد. در اکثر موارد، این APIها، شبیه APIهای srv_describe و srv_sendrow هستند که برای extended stored procedureها دردسترس هستند.

SendResultsStart()، یک SqlDataRecord را یک پارامتر ارسالی (argument) در نظر می گیرد و شروع یک نتیجه جدید را نشان می دهد. این API، اطلاعات metadata را از record object می خواند و آن را به caller ارسال می کند. متعاقباً، می توان Rowها را با فراخواندن SendResultsRow()، یکبار برای هر row که در شرف ارسال است، باز گرداند. بعد از اینکه همه rowهای دلخواه ارسال شدند، فراخوانی SendResultsEnd() جهت نشان دادن پایان خروجی، لازم است.

مثال: بازگرداندن یک RSS Feed در یک CLR Stored Procedure

C# code fragment زیر، بخشی از یک stored procedure تشان می دهد که یک XML document را از Web می خواند- یک Really Simple Syndication (RSS) feed از MSDN- و کلاسهای System.Xml برای parse کردن آن استفاده می کند، و اطلاعات را به شکل نسبی باز می گرداند. به یاد داشته باشید که کد باید در یک EXTERNAL_ACCESS یا UNSAFE assembly نمایش داده شود، زیرا permissionهای Code Access Security (CAS) که برای دسترسی به اینترنت ضروری هستند، فقط در این دسته ازpermission ها قابل دسترس هستند.

...
using (SqlConnection conn = 
    new SqlConnection("context connection = true"))
{
    // Retrieve the RSS feed
    XPathDocument doc = new 
        PathDocument("http://msdn.microsoft.com/sql/rss.xml");
    XPathNavigator nav = doc.CreateNavigator();
    XPathNodeIterator i = nav.Select("//item");

    // create metadata for four columns
    // three of them are string types and one of the is a datetime
    SqlMetaData[] rss_results = new SqlMetaData[4];
    rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
    rss_results[1] = new SqlMetaData("Publication Date", 
        SqlDbType.DateTime);
    rss_results[2] = new SqlMetaData("Description", 
        SqlDbType.NVarChar, 2000);
    rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);

    // construct the record which holds metadata and data buffers
    SqlDataRecord record = new SqlDataRecord(rss_results);

    // cache a SqlPipe instance to avoid repeated calls to  
    // SqlContext.GetPipe()
    SqlPipe sqlpipe = SqlContext.Pipe;
 
    // send the metadata, do not send the values in the data record
    sqlpipe.SendResultsStart(record);

    // for each xml node returned, extract four pieces 
    // of information and send back each item as a row
    while (i.MoveNext())
    {

        record.SetString(0, (string)
                            
        i.Current.Evaluate("string(title[1]/text())"));
            record.SetDateTime(1, DateTime.Parse((string)
                                             
        i.Current.Evaluate("string(pubDate[1]/text())")));
            record.SetString(2, (string)
                                    
        i.Current.Evaluate("string(description[1]/text())"));
            record.SetString(3, (string)
                                     
        i.Current.Evaluate("string(link[1]/text())"));
        sqlpipe.SendResultsRow(record);
    }

    // signal end of results
    sqlpipe.SendResultsEnd();
}
...

به خاطر داشته باشید که بین callها به SendResultsStart() و SendResultsEnd()، عبارت SqlPipe در یک حالت busy تنظیم می شود، وفراخوانی هر متد Send، غیر از SendResultsRow()، منجر به یک error خواهد شد. SendingResults property، در حالیکه SqlPipe در حالت busy است، true تنظیم می شود.

Functionsهای Table-Valued

CLR integration، از functionهای table-valued (TVF) که در managed languageها نوشته شده اند، نیز ساپورت می کند. مانند معادل T-SQL، CLR TVFها نیز ابتداً برای بازگرداندن خروجی جدولی استفاده می شدند. واضح ترین تفاوت این است که T-SQL table-valued function، خروجی را موقتاً در یک جدول ذخیره می کنند، در حالیکه CLR TVF، قابلیت stream کردن خروجی بدست آمده را دارد، بدین معنی که این خروجی نباید قبل از بازگشت از function، materialize شوند.

مثال: یک Table-Valued Function برای بازیابی یک RSS Feed

در اینجا، ما RSS retrieval recast را به عنوان یک table-valued function در C# نشان می دهیم. به تطابق بین SqlFunction annotation در متد RSS_TVF() و امضاء (signature) متد FillTVFRow()، دقت کنید.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml.XPath;
using System.Collections;

public partial class UserDefinedFunctions
{
    [SqlFunction(FillRowMethodName = "FillTVFRow", 
        TableDefinition = "Title nvarchar(250), " +
            "PublicationDate datetime, " +
            "Description nvarchar(2000), " +
            "Link nvarchar(1000)")
    ]
    public static IEnumerable RSS_TVF()
    {
        return new RssReader();
    }

    public static void FillTVFRow(object row, out SqlString str, 
        out SqlDateTime date, out SqlString desc, out SqlString link)
    {
        // split each object array
        object[] rowarr = (object[])row;
        str = (SqlString)(rowarr[0]);
        date = (SqlDateTime)(rowarr[1]);
        desc = (SqlString)(rowarr[2]);
        link = (SqlString)(rowarr[3]);
    }
}

public class RssReader : IEnumerable
{
    XPathDocument doc;
    XPathNavigator nav;

    // Construct helper class, initializing metadata for the results
    // reading from the RSS feed, creating the iterator
    public RssReader(string site)
    {
        // Retrieve the RSS feed
        //doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml");
        doc = new XPathDocument(site);
        nav = doc.CreateNavigator();
    }

    public IEnumerator GetEnumerator()
    {
        return new RSSEnumerator(this);
    }

    private class RSSEnumerator : IEnumerator
    {
        XPathNodeIterator i;
        Object[] current;
        RssReader reader;

        public RSSEnumerator(RssReader reader)
        {
            this.reader = reader;
            Reset();
        }

        public void Reset()
        {
            i = reader.nav.Select("//item");
        }

        public bool MoveNext()
        {
            if (i.MoveNext())
            {
                current = new Object[4];
                current[0] = new SqlString((string)
                    i.Current.Evaluate("string(title[1]/text())"));
                current[1] = new SqlDateTime(DateTime.Parse((string)
                    i.Current.Evaluate("string(pubDate[1]/text())")));
                current[2] = new SqlString((string)
                    i.Current.Evaluate("string(description[1]/text())"));
                current[3] = new SqlString((string)
                i.Current.Evaluate("string(link[1]/text())"));
                return true;
            }
            else return false;

        }

        public Object Current
        {
            get
            {
                return current;
            }
        }
    }
}

یک query ساده برای consume کردن خروجی از این جدول، اینگونه خواهد بود:

SELECT *
FROM RSS_TVF() 

طبیعتاً، queryهای قویتر را می توان روی شکل TVF این داده، بیان کرد. با فرض اینکه ما یک تابع CanonicalURL() داریم که نسخه كانونيك یک URL را باز می گرداند، داده هایی از یک RSS feed را می توان با استفاده از URLهای کانونیک به آسانی بازگرداند:

select title, publicationDate, description, dbo.CanonicalURL(link) 
from dbo.RSS_TVF()
order by publicationDate

توجه داشته باشید که در مثال بالا، ما از قابلیتهای stream کردن TVF استفاده نمی کنیم، زیرا ما کل RSS feed را consume می کنیم، یک navigator در بالا درست می کنیم، و سپس آیتمهای مجزا را هنگامی که MoveNext() فراخوانده می شود، تکرار می کند. اما، consume کردن خروجی از یک Web source و تکرارXML تولید شده با یک XmlReader، با استفاده از streaming API ممکن است. قابل ذکر است که با فرض وجود تفاوت مدل اجرا بین table-valued functions در CLR و T-SQ، تفاوتی بزرگ ممکن است مشاهده شود، مخصوصاً در سناریوهایی که در آنها stream کردن خروجی ممکن است.

مثال: crack کردن Scalarها در Rowها

معمولاً در برنامه نیاز داریم که چندین پارامتر ورودی را به صورت همزمان ارسال کنیم. مثلاً، امکان دارد یک order processing system نیاز به یک stored procedure داشته باشد که یک order را در جدول orderها قرار می دهد. امکان دارد یک پارامتر ورودی مطلوب یک stored procedure، در order، line-items باشد؛ اما، این موضوع با این محدودیت روبرو می شود که T-SQL از پارامترهای چندگانه ساپورت نمی کند، و فاقد collectionها و arrayها است.

یک راه حل،encode کردن collection به عنوان یک scalar value است – مثلاً به عنوان یک nvarchar یا xml- و ارسال آن به عنوان یک پارامتر ورودی به stored procedure. Stored procedure می تواند از table-valued function که scalar input را می گیرد و آن را تبدیل به دسته ای از rowها می کند، استفاده می کند، و سپس می تواند در یک جدول line-items یا تغییر یافته (manipulated) قرار گیرد.

در حالیکه table-valued function را می توان در T-SQL نوشت، اما اگر در CLR نوشته شود، performance بهتری خواهد داشت. زیرا می تواند از string manipulation functionها در System.Text namespace استفاده بهینه ببرد، همچنین انجامش هم بسیار ساده است.

مثال زیر، پیاده سازی یک table-valued function را نشان می دهد که یک input string را که توسط یک نقطه ویرگول جدا شده را می گیرد و قطعه ها را به شکل دسته ای از rowها باز می گرداند.

در Visual Basic .NET:

Imports System.Collections
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

    ' This needs to return an IEnumerable, but since an array
    ' does, in this case we do not need to define a new class
    ' that implements it: we can simply return the array.
    <SqlFunction(FillRowMethodName:="FillRow", _
        TableDefinition:="value nvarchar(60)")> _
    Public Shared Function GetStrings(ByVal str As SqlString) _
        As IEnumerable

        Return str.Value.Split(";"c)
    End Function

    ' This method does the decoding of the row object. Since the
    ' row is already a string, this method is trivial. Note that
    ' this method is pointed to by the annotation on the
    ' GetString method.
    Public Shared Sub FillRow(ByVal row As Object, _
        ByRef str As String)

        str = CType(row, String)
    End Sub

End Class

در C#:

using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    /*
     * This needs to return an IEnumerable, but since an array
     * does, in this case we do not need to define a new class
     * that implements it: we can simply return the array.
     */
    [SqlFunction(FillRowMethodName="FillRow",
        TableDefinition="value nvarchar(60)")]
    public static IEnumerable GetStrings(SqlString str)
    {
        return str.Value.Split(';');
    }

    /*
     * This method does the decoding of the row object. Since the
     * row is already a string, this method is trivial. Note that
     * this method is pointed to by the annotation on the
     * GetString method.
     */
    public static void FillRow(object row, out string str)
    {
        str = (string)row;
    }
}

خلاصه: این مقاله ویژگیهای جدید CLR integration در SQL Server 2005، را توصیف می کند و توضیح می دهد که چگونه یک database application developer و architectها می توانند از آنها جهت نوشتن رویه ها(procedure)، functionها، و triggerهایی که توسط کاربر تعریف شده اند، استفاده کنند. و همچنین typeها و aggregateهای جدیدی را تعریف می کنند.

ما، برنامه نویسی بر پایه CLR را با مدلهای برنامه نویسی از قبیل Transact-SQL و extended stored procedures، را که در SQL Server ساپورت می شوند، مقایسه ، و نقاط قوت و ضعف هر تکنیک را برجسته می کنیم، و لیستی از رهنمودهای سطح بالا در مورد چگونگی انتخاب alternativeهای در دسترس برای برنامه نویسی را در اختیار می گذاریم. همچنین، گروهی از نمونه کدهایی را که خصوصیات CLR integration را نشان میدهند، نیز در اختیار می گذاریم.

مقدمه

Microsoft SQL Server 2005 به طور چشمگیری مدل database programming را با میزبانی از Microsoft .NET Framework 2.0 Common Language Runtime (CLR) بهبود می بخشد. این موضوع، برنامه نویسان را قادر به نوشتن رویه ها (procedure)، functionها، و triggerها در هر زبان CLR، مخصوصاً Microsoft Visual C# .NET, Microsoft Visual Basic و Microsoft Visual C++ می کند. همچنین به برنامه نویسان اجازه می دهد database را با typeها وaggregateهای جدید، گسترش دهند.

این مقاله چگونگی استفاده بهینه از این تکنولوژی را از نگاه یک database application developer توضیح می دهد، و تکنیکهای CLR integration را با programming language support موجود در SQL Server، Transact-SQL (T-SQL) و stored procedure های گسترش یافته، مقایسه می کند. این مقاله، مرجعی برای propertyها نیست، اطلاعات مربوط به مرجع را می توان در SQL Server 2005 Books Online پیدا کرد. مثالهای این مقاله، CTP آوریل سال 2005 از SQL Server 2005 را در نظر می گیرد.

مخاطبین این مقاله شامل database application developerها، architectها و administratorها می شود. این مقاله، شامل آشنایی موثر با .NET Framework-based programming و database programming می شود.

مرور CLR Integration

آنچه در ادامه می آید، مروریست مختصر بر عملکرد SQL Server که توسط CLR integration فعال شده، و اینکه Visual Studio 2005، چگونه این خصوصیات را ساپورت می کند.

نصب (deployment) دستی

ثبت و اجرای کدهای مدیریت شده در database شامل مراحل زیر می شود:

1. برنامه نویس، یک برنامه مدیریت شده را به عنوان مجموعه ای از تعاریف class می نویسد. Routinesهای SQL Server، stored procedureها، functionها، به عنوان متدهای static (یا shared در Microsoft Visual Basic .NET) یک کلاس نوشته می شوند. Type و aggregateهایی که کاربر تعریف کرده به عنوان کلاسهای کلی نوشته می شوند. برنامه نویس کد را compile و یک assembly ایجاد می کند.

2. Assembly به یک SQL Server database، یعنی جاییکه در کاتالوگهای سیستم، با استفاده از عبارات DDL، ذخیره می شود، آپلود می شود.

3. سپس Objectهای Transact-SQL (T-SQL) از قبیل routineها، typeها و aggregateها ایجاد می شوند و به entry pointهایی که قبلاً آپلود شده اند، محدود می شوند. این روند، با استفاده از عبارات CREATE PROCEDURE/FUNCTION/TRIGGER/TYPE/AGGREGATE به دست می آید.

4. بعد از اینکه routineها ایجاد می شوند، applicationها می توانند آنها را مثل routineهای T-SQL مورد استفاده قرار دهند. مثلاً، می توان CLR functionها را از queryهای T-SQL، و CLR procedureها را از یک client application یا از یک T-SQL batch فرا خواند، گویی procedureهای T-SQL هستند.

ساختن، نصب (deployment)، و debug کردن با استفاده از Visual Studio

Visual Studio 2005 از development، deployment و debug کردن کدهای مدیریت شده در SQL Server 2005 ساپورت می کند. یک SQL Server Project جدید، code templateهایی را که شروع نوشتن کدها برای routineها، typeها و aggregateهای CLR-based database را برای برنامه نویسان آسان می کند، در اختیار می گذارد. همچنین این پروژه به برنامه نویسان اجازه می دهد مرجعهایی (reference) را به assemblyهای دیگری در database اضافه کنند.

زمانیکه یک SQL Server Project ساخته می شود، به یک assembly، compile می شود. Deploy کردن پروژه، assembly binary را در SQL Server database که مربوط به پروژه است، آپلود میکند. همچنین عملیات deploy،routine ها، typeها و aggregateهایی را ایجاد می کند که در database assembly که برپایه attributeهای دلخواه (SqlProcedure, SqlFunction, SqlTrigger) موجود در کد است، تعریف می شوند. همچنین deploy، کدهای منبع و سمبولهای debugging (فایلهای .pdb) را که مربوط به اسمبلی هستد، آپلود می کند.

از آنجاییکه debugging، بخش مهمی از تجربه یک برنامه نویس برای هر platform است، SQL Server 2005 و Visual Studio 2005، database programmerهایی را با چنین قابلیتهایی در اختیار می گذارند. یک ویژگی کلیدی در debug کردن اشیاء در SQL Server 2005، سهولت نصب و استفاده است. اتصال به کامپیوترهایی که SQL Server را run می کنند، ممکن است مانند فرایندهایی که تحت یک سیستم عامل قدیمی اجرا می شوند، debug شوند. نوع اتصال به سروری که client، دارد، عملکرد debugger را تحت تاثیر قرار نمی دهد. هم Tabular Data Stream (TDS) و هم HTTP connectionها ممکن است debug شوند. به علاوه، debug کردن، در همه زبانها، کاملاً یکپارچه عمل می کند و به کاربر می دهد از T-SQL به متدهای CLR یا بالعکس، دست یابد.

CLR و Alternativeهایش

در ارزبابی استفاده از CLR integration، یک برنامه نویس باید آنرا با optionهای دردسترس دیگر مقایسه کند. هدف ما در اینجا، فراهم کردن پایه ای برای این مقایسه است، طوری که بتوان آنرا مقابل تکنیکهای برنامه نویسی کنونی از قبیل: Transact-SQL, extended stored procedures و code in the middle-tier، قرارداد. در این قسمت، ما روی routineهایی که کابر تعریف می کند، تمرکز خواهیم کرد .

CLR در برابر Transact-SQL

Transact-SQL (T-SQL) یک زبان برنامه نویسی ذاتی (native) است که توسط SQL Server ساپورت می شود. مثل اکثر نسخه های SQL، شامل ویژگیهای تغییر داده ها (data manipulation features) و ویژگیهای تعریف داده ها (data definition features) می شود. data manipulation features را می توان به دو گروه کلی تقسیم بندی کرد:

یک declarative query language (تشکیل شده از عبارات SELECT/INSERT/UPDATE/DELETE) و یک procedural language (WHILE, assignment, triggers, cursors و غیره). اگر بخواهیم کلی بگوییم، CLR support در SQL Server، جایزگزینی را برای بخش روندی T-SQL (procedural portion) در اختیار می گذارد.

حتی بدون CLR support نیز، مهم است تشخیص دهیم که database applicationها باید تا آنجا که ممکن است از declarative query language استفاده کنند. این قسمت از زبان، قادر به استفاده از قدرت query processor است که می تواند به نحو احسن، operation bulkها را اجرا و بهینه کند. Database applicationها باید فقط از procedural programming برای بیان منطق (logic) که در query language قابل بیان (express) نیست، استفاده کند.

تمامی اینها، با CLR support در SQL Server، نیز صدق می کند: از CLR نباید برای نوشتن procedural code که با استفاده از ویژگیهای زبان T-SQL، قابل نوشتن هستند، استفاده کرد. برنامه نویسان باید آگاه باشند که تعدادی enhancementهای مهم T-SQL query language در SQL Server 2005 وجود دارند که قدرت T-SQL query language را افزایش می دهند، و باید مطمئن شوند که از همه مزایای آنها قبل از نوشتن procedural code بهره می برند، چه در CLR و چه در جای دیگر. بعضی از این ویژگیهای اضافه شده عبارتند از:

· قابلیت نوشتن Queryهای بازگشتی جهت traverse کردن سلسله مراتب (hierarchy) بازگشتی در یک جدول.

· Functionهای تحلیلی (analytical) جدید از قبیل RANK و ROW_NUMBER که اجازه درجه بندی ردیفها (RANKING ROW) را در لیست خروجی می دهد.

· اپراتورهای نسبی (relational) جدید از قبیل EXCEPT, INTERSECT, APPLY, PIVOT و UNPIVOT.

برنامه نویسان باید به CLR به عنوان جایگزینی کارآمد برای منطق (logic) که در query language قابل بیان نیست، نگاه کنند.

بیایید نگاهی به به برخی از سناریوهایی که در آنها برنامه نویسی بر پایه CLR میتواند قدرت T-SQL query language را تکمیل کند بیاندازیم. اغلب، ما نیاز به قراردادن procedural logic درون یک Query که میتوان function نامید، داریم. این، شامل موقعیتهای زیر می شود:

· اجرای محاسبات پیچیده (که باید با استفاده از procedural logic بیان شوند) روی valueهای ذخیره شده در جداول database. این کار مستلزم ارسال خروجی این محاسبات به client، یا استفاده از آنها برای فیلتر کردن دسته ای از ردیفها (row) که به client ارسال می شوند، است؛ مانند مثال زیر:

•    SELECT <complex-calculation>(<column-name>,...)
•    FROM <table>
•    WHERE <complex-calculation>(<column-name>,...) = ... 

· استفاده از procedural logic جهت ارزیابی خروجیی که در قسمت FROM در یک عبارت SELECT یا DML، query می شوند. (DML مخفف Data Manipulation Languaga، به معنای زبان تغییر داده ها به دستورات INSERT، UPDATE، و DELETE است)

SQL Server 2000، (Function) تابع های T-SQL را معرفی کرد که این سناریو هارا فعال می کند. با SQL Server 2005، این تابع ها، با استفاده از زبانهای CLR، راحتتر نوشته می شوند. زیرا برنامه نویسان می توانند از libraryهای بسیار گسترده در .NET Framework API، بهترین استفاده را ببرند. به علاوه، زبانهای برنامه نویسی CLR، ساختارهای قوی data از قبیل (arrayها، listها، و غیره) رادر اختیار می گذارد که در T-SQL وجود ندارد، و می توانند به علت داشتن مدلهای متفاوت execution در CLR و T-SQL، بهتر اجرا شوند.

بطور کلی، تابعها کاندیدهای خوبی برای نوشته شدن، با استفاده از CLR هستند؛ زیرا نیاز به دسترسی به database از درون یک تابع به ندرت پیش می آید؛ valueهای database، معمولاً تحت عنوان پارامتر ارسالی (argument) ارسال می شوند که در taskهای محاسبه ای، بهتر از T-SQL هستند.

Data Access از CLR

حالا نگاهی به CLR به عنوان یک option برای نوشتن routineهایی که data access اجرا میکند، می اندازیم، هم از منظر مدل برنامه نویسی (programming model) وهم ازمنظر اجرا (performance).

در T-SQL، عبارات query language از قبیل SELECT، INSERT، UPDATE و DELETE، فقط در procedural code قرا می گیرند. ار سوی دیگر، کد مدیریت شده، ازADO.NET data access provider برای SQL Server (SqlClient) استفاده می کنند. در این روش، همه عبارات query language توسط stringهای دینامیک که به متدها و خصوصیات (propert) در ADO.NET API تحت عنوان پارامتر ارسالی (argument) ارسال می شوند.

به خاطر این تفاوت، data access code نوشته شده که از CLR استقاده می کند، می تواند از T-SQL طولانی تر باشد. مهمتر اینکه، از آنجاییکه عبارات SQL در stringهای دینامیک رمزگذاری (encode) می شوند، تا وقتی که اجرا شوند، compile و اعتباردهی (validate) نمی شوند ؛ که هم روی debug کردن کد و هم روی اجرایش (performance) تاثیر می گذارد. اما، مدل برنامه نویسیdatabase با ADO.NET بسیار شبیه مدلی است که در client یا middle-tierها استفاده شده، که هم move کردن کد بین tierها و هم استفاده از مهارتهای موجود را آسانتر می کند.

یه یاد داشته باشید که مدلهای برنامه نویسی بر پایه T-SQL- و هم بر پایه CLR، از یک SQL query language استفاده می کنند؛ تنها procedural portionها فرق دارند.

همانطور که قبلاً هم ذکر شد، کد مدیریت شده، با در نظر گرفتن اکثر محاسباث رویه ای (procedural)، از مزیت "اجرا شدن حتمی" در مقایسه با T-SQL بهره می برد؛ اما برای data-access،T-SQL معمولاً بهتر عمل می کند.

بنابراین، یک قانون رایج خوب این است که "کد حساس به محاسبه و منطق" (computation- and logic-intensive)، انتخاب بهتری برای پیاده سازی در CLR نسبت به کد حساس به data-access است.

بیایید نگاهی به بعضی از الگوهای معمولی در data-access programming بیاندازیم، که توضیح می دهد T-SQL و برنامه نویسی مدیریت شده (managed programming)، که از integrated CLR و ADO.NET استفاده می کند، چگونه در این سناریوها اجرا می شوند.

ارسال خروجی به Client

سناریوی اول ما، مستلزم ارسال دسته ای از ردیفها (row) به Client بدون consume کردن آنها در سرور است. مثلاً، هیچ ردیفی خارج از routine، navigate نمی شود. با T-SQL، وارد کردن یک عبارت SELECT در T-SQL procedure، به اندازه ارسال ردیفهای ایجاد شده توسط SELECT به cleint، موثر است. با کدهای مدیریت شده، شی SqlPipe، برای ارسال خروجی به client استفاده می شود. T-SQL و ADO.NET این سناریو را تقریباً به یک صورت اجرا می کنند.

Submit کردن عبارات SQL

Submit کردن عبارات SQLاز CLR مستلزم traverse کردن لایه های اضافی کد، به منظور switch بین کد مدیریت شده و کد SQL، است. به همین دلیل، T-SQL دارای مزیت performance، هنگام صدور (issue) یک SQL query است. توجه داشته باشید که بعد از submit شدن این عبارت در query processor، فرقی در performance که بر پایه منبع عبارت است، وجود ندارد؛ (چه در T-SQL، چه در کد مدیریت شده). اگر query پیچیده باشد و ارزیابی اش (evaluate) طول بکشد، آنگاه تفاوتها در performance بین T-SQL و کد مدیریت شده، کم اهمیت خواهد بود. به طور خلاصه، queryهای ساده، overhead لایه های اضافی کد می تواند performance یک procedure مدیریت شده را تحت تاثیر قرار دهد.

احتمال دارد stored procedureهای معمولی و حساس به data-access، درگیر submit کردن یک سری (sequence) از عبارات SQL شود. اگر عبارات SQL ساده باشند و زیاد طول نکشند تا اجرا شوند، آنگاه فراخواندن overhead از کد مدیریت شده می تواند بر زمان اجرا تسلط پیدا کند؛ چنین procedureهایی نوشتن در T-SQL را بهتر اجرا می کنند.

Forward-Only, Read-Only Row Navigation (خواندن رکوردها، فقط رو به جلو و فقط خواندنی)

در T-SQL، forward-only, read-only navigation با استفاده از یک cursor پیاده می شود. در کد CLR، این کار با یک SqlDataReader پیاده می شود. معمولاً، مقداری پردازش برای هر قطعه از DATA انجام می شود. اگر این موضوع را نادیده بگیریم، T-SQL یک مزیت دارد، زیرا Row Navigation که از CLR استفاده می کند، کمی آهسته تر از T-SQL است. اما، از آنجاییکه CLR در هر پردازشی که روی data انجام می شود، به طور برجسته ای اجرای (performance) بهتری نسبت به T-SQL دارد، CLR performance، هنگامی که مقدار پردازش افزایش می یابد، جای اجرای T-SQL را می گیرد.

به علاوه، شخص هنگام استفاده از cursorهای T-SQL، باید از پتانسیل امکانات اضافی (latency) آگاه باشد. گرچه بعضی از queryها، الزاماً مقداری latency نشان می دهند، زیرا باید خروجی میانی (intermediate) را ظاهر کنند، cursorهای STATIC و KEYSET همیشه خروجی نهایی را در یک جدول موقت قبل از بدست آوردن هر نتیجه دیگری، ظاهر می کنند. یک cursor می تواند یا مستقیماً با STATIC با KEYSET، تعریف (declare) شود و یا غیر مستقیماً ، به علت برخی ویژگیهای query و data، به یک تبدیل شود. CLR SqlDataReader همیشه خروجی را هنگامی که در دسترس هستند و با پرهیز از این latency تولید می کند.

Row-Navigation با updateها

اگر این مشکل مستلزم update کردن ردیفها (row) برپایه موقعیت کنونی cursor باشد، آنگاه هیچ مقایسه performance مرتبطی وجود نخواهد داشت. این عملکرد در ADO.NET ساپورت نمی شود و باید از طریق cursorهای قابل update در T-SQL انجام شود. اما به خاطر داشته باشید که معمولاً استفاده از عبارات UPDATE برای update کردن ردیفهای خالی بهتر است، و modificationهایی را که بر پایه cursor هستند، برای زمانیکه نمی توان با declarative SQL بیان کرد، ذخیره می کند.

خلاصه

در زیر، خلاصه ای از راهنماییهایی که دیده ایم می توانند در انتخاب CLR یا T-SQL استفاده شوند، آورده شده است:

· هر گاه ممکن است، از عبارات SELECT، INSERT، UPDATE، و DELETE استفاده کنید. پردازش رویه ای (procedural) و پردازش بر پایه row باید فقط هنگامی که logic با استفاده از declarative language قابل بیان نیست، استفاده شود.

· اگر رویه (procedure) ،فقط یک wrapper برای فرمانهای declarative T-SQL است، باید در T-SQL نوشته شود.

· اگر رویه (procedure)، ابتداً مستلزم forward-only, read-only row navigation از طریق یک دسته از خروجی با همان پردازش هر row باشد، احتمالاً استفاده از CLR، بازده بیشتری دارد.

· اگر رویه (procedure)، هم مستلزم data access و هم محاسبه باشد، تقسیم کردن کد رویه ای (procedural code) را در CLR portion که یک T-SQL procedure را جهت اجرای data access یا یک T-SQL procedure را که CLR را جهت اجرای محاسبه فرا می خواند، در نظر بگیرید. راه حل دیگر، استفاده از یک T-SQL batch واحد است که شامل یک سری query است که یک بار از managed code جهت کاهش تعداد round tripهای submit کردن عبارات T-SQL اجرا شده است.

قسمت های بعدی بیشتر به بحث درباره زمان و چگونگی استفاده صحیح از T-SQL و CLR هنگام کار با خروجی می پردازد.

CLR در مقابل XPها

در نسخه های قبلی SQL Server، extended stored procedureها (XPها) تنها جایگزین T-SQL بودند که نوشتن کد سمت سرور (server-side code) به آن سبک بسیار سخت بود. CLR integration، یک جایگزین قویتر برای XPها در اختیار می گذارد. به علاوه، با CLR integration، خیلی از stored procedureها بهتر بیان می شوند و به آنها، با استفاده از query language، اجازه فراخوانده شدن و تغییر (manipulation) می دهد.

بعضی از مزیتهای استفاده از CLR procedureها نسبت به XPها عبارتند از:

· کنترل دانه ای (Granular control): administratorهای SQL Server کنترل کمی روی کارهایی که XPها می تواند یا نمی تواند انجام دهند، دارند. با استفاده از مدل Code Access Security، یک SQL Server administrator می تواند یکی از سه permission bucketهای SAFE، EXTERNAL_ACCESS، یا UNSAFE، را جهت بکار گیری درجات مختلف کنترل عملیاتهایی که کد مدیریت شده اجازه دارد اجرا کند، تعیین کند.

· قابلیت اعتماد (Reliability): کد مدیریت شده، مخصوصاً در permissionهای SAFE و EXTERNAL_ACCESS، مدل برنامه نویسی قابل اعتماد تری را نسبت به XPها در اختیار می گذارد. کدهای مدیریت شدهِ قابل تایید، تضمین می کنند که دسترسی به اشیا از طریق interfaceهایی که احتمال اینکه برنامه به memory bufferهایی که متعلق به SQL Server است، دسترسی پیدا کند یا آنها را مختل کند، کاهش می دهد.

· Data access: با XPها، باید اتصالی مستقیم (loop-back connection) به database ، جهت دسترسی به SQL Server database محلی (local) ایجاد شود. به علاوه، این loop-back connection باید مستقیماً به transaction context های جلسه اصلی (original session) محدود شود تا تضمین کند XP در transaction که در آن فراخوانده می شود، شرکت دارد. کد مدیریت شده CLR می تواند با استفاده از یک مدل برنامه نویسی طبیعی و کارآمدارتر که از connection و transaction context حاضر بیشترین بهره را ببرد، به داده های محلی (local data) دسترسی پیدا کند.

· Data Typeهای اضافی: APIهای مدیریت شده از data typeهای جدید (از قبیل XML، (n)varchar(max)، و varbinary(max)) که در SQL Server 2005 معرفی شدند، ساپورت می کند؛ در حالیکه ODS APIها جهت ساپورت از این typeهای جدید، گسترش نیافته اند.

· قابلیت صعود پذیری (Scalability): APIهایی که در معرض منابعی (resource) از قبیل حافظه، threadها، و همسان سازی (synchronization) هستند، در بالای SQL Server resource manager پیاده می شوند، و به SQL Server اجازه مدیریت این منابع برای کد CLR را می دهد. در مقابل، SQL Server هیچ دید یا کنترلی روی منبع استفاده از یک XP ندارد. اگر یک XP، زمان و حافظه زیادی از CPU را بگیرد، راهی برای شناسایی یا کنترل آن از درون SQL Server وجود ندارد. با کد CLR، SQL Server می تواند پی ببرد که یک thread تعیین شده، به مدت طولانی بازگشتی نداشته و task را مجبور به بازگشت می کند، طوریکه بتوان کار دیگری را برنامه ریزی کرد. در نتیجه، استفاده از کد مدیریت شده، قابلیت صعود (scalability) و قوت (robustness) بهتری را در اختیار می گذارد.

همانطور که در بالا اشاره شد، برای data access و ارسال خروجی به client CLR، routineها بهتر از XPها عمل می کنند. برای کدی که مستلزم data access یا ارسال خروجی نیست، مقایسه اجرای XPها (performance) و کد مدیریت شده، مانند مقایسه کد مدیریت شده با native code است. معمولاً، کد کنترل شده نمی تواند بر اجرای native code در این سناریوها فایق آید. علاوه بر این، یک cost اضافی نیز در زمان انتقال از کد مدیریت شده به کد native، هنگام run شدن درون SQL Server وجود دارد، زیرا SQL Server نیاز به انجام book-keeping روی تنظیمات مخصوص thread هنگام انتقال به native code و بالعکس دارد. در نتیجه، XPها می توانند اجرای (running) کد مدیریت شده را درون SQL Server برای مواردی که انتقالهای (transition) مکرر بین کد مدیریت شده و کد native وجود دارد، بهتر انجام دهد.

در اکثر procedureها، مزیتهای کد مدیریت شده می تواند procedureهای CLR را جایگزین جذابتری نسبت به XPها کند. در مواردی که performance، ابتداً توسط پردازش حساس به محاسبه (computationally-intensive processing) و انتقالات مکرر بین کد مدیریت شده و کد native تعیین شده، مزایای CLR باید در مقابل مزایای اجرای خام XPها (raw performance) سنجیده شود.

کد در Middle Tier

گزینه دیگر برنامه نویسان، قرار دادن logicشان بیرون از database است. این کار به آنها اجازه نوشتن کدشان را در زبانهای انتخابیشان می دهد. با در اختیار گذاشتن یک مدل برنامه نویسی غنی در database، CLR integration به برنامه نویسان امکان منتقل کردن چنین logic را به database می دهد. البته، این بدین معنا نیست که همه یا اکثر کدها باید به database منتقل شوند.

انتقال logic به database tier می تواند مقدار داده ای را که روی network جاری است کاهش دهد، اما باری اضافی روی resourceهای باارزش سی پی یوی سرور می گذارد. این موضوع باید قبل از تصمیم گیری درباره قراردادن کد برای یک application، با دقت مد نظر قرار گیرد. ملاحظات زیر می تواند database را تبدیل به code location ارجح کند:

· اعتبار دهی داده ها (Data validation): نگه داشتن data validation logic در database، امکان بهتر خلاصه کردن این logic را با داده ها می دهد، و از تکثیر شدن validation logic در data touch pointها، از قبیل back-end processing، bulk upload و update کردن داده ها از middle tier و غیره جلوگیری می کند.

· کاهش ترافیک شبکه (Network traffic reduction): ممکن است قراردادن logic در database برای taskهای پردازش داده ها که مستلزم پردازش مقدار زیادی داده است، هنگام تولید درصد کمی از آن داده ها، مناسب باشد. نمونه های معمولی شامل applicationهای تحلیل داده ها از قبیل demand forecasting (پیش بینی تقاضا) و scheduling production (زمانبندی تولید) بر اساس forecast demandها و غیره باشد.

البته این ملاحظات بدون CLR integration هم مهم هستند؛ CLR integration فقط به تضمین اینکه انتخاب زبان برنامه نویسی در تعیین موقعیت مناسب کد دخالت نمی کند، کمک می کند.

مثال: زمانبندی تولید (Production Scheduling)

Production scheduling یک کار عادی در کارخانجات صنعتی است. در سطحی بالا، این کار مستلزم ارائه طرحی برای زمان تولید محصولات به منظور برآورده کردن تقاضا و در عین حال، به حداقل رساندن هزینه کل تولید و انبار کردن محصولات است. الگوریتمهای متعددی وجود دارند که پیش بینی تقاضا، هزینه های نگهداری انبار کالا، و هزینه های راه اندازی خط تولید را به عنوان ورودی، و استراتژی تولید را خروجی در نظر می گیرند.

با فرض اینکه پیش بینی تقاضاهای آینده در جدولی در SQL Server ذخیره می شوند، پیاده سازی چنین الگوریتمی دارای مشخصه های زیر است:

1. مقدار زیادی داده (پیش بینی تقاضا) را ورودی در نظر می گیرد.

2. نتیجه کمی را تولید می کند، مثلاً تعداد واحدهایی که باید در یک زمان یا زمانهای مشخص تولید شوند.

3. مستلزم محاسبات زیاد جهت مشتق کردن خروجی از ورودی است.

پیاده سازی چنین الگوریتمی در middle tier اختیاری است، اما انتقال داده های تقاضا از database، هزینه زیادی دربر دارد. انجام آن در T-SQL به عنوان یک stored procedure نیز شدنی است، اما عدم وجود data typeهای پیچیده پیاده سازی را مشکل می کند، و performance در T-SQL، به علت کمیت و پیچیدگی محاسبات لازم، مسئله ساز خواهد بود. البته، مشخصه های اجرا (performance characteristics) بسته به مقدار واقعی داده ها و پیچیدگی الگوریتم، متنوع خواهد بود.

جهت تایید تطابق CLR integration با چنین سناریویی، ما یک الگوریتم برنامه ریزی تولید—الگوریتم Wagner-Whitin — را در نظر گرفتیم و آنرا با استفاده از هم CLR و هم T-SQL پیاده کنیم. همانطور که انتظار می رفت، CLR integration پیاده سازی خیلی بهتری نسبت به T-SQL داشت. پیاده سازی در C# راحتتر بود، زیرا این الگوریتم از arrayهای تک و چند بعدی که در T-SQL وجود ندارند استفاده می کند. روی هم رفته، نسخه CLR چندین orders of magnitude را بهتر از T-SQL پیاده می کند.

بیایید شکل database ساده زیر را که لیست محصولاتی را که تولید می شوند را نگه می دارد، در نظر بگیریم.

جدول t_products:

به علاوه، جدول زیر پیش بینی تقاضا برای هر محصول در هر روز را ذخیره می کند. ما فرض می کنیم که ستون pid یک کلید خارجی در جدول t_products است.

جدول t_salesForecast:

ما یک stored procedure ایجاد کردیم که علاوه بر داده های محصول، پارامترهایی را در نظر می گیرد که دامنه تاریخ هایی را که باید برنامه ریزی تولید ارائه کرد، بیان می کند.

این stored procedure، یک rowset را با schema در جدول زیر باز می گرداند.

نسخه C# این کد، در زیر آورده شده تا نوع ستاریویی را که می تواند از CLR integration سود ببرد نشان دهد:

using System; 
using System.Data;
using System.Collections;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class ProductionScheduler
{
    const int MAXNAME = 256;                   // The maximum name size

    [Microsoft.SqlServer.Server.SqlProcedure]  // Flag as a SQL procedure 
    public static void Schedule(SqlDateTime start, SqlDateTime end)
    {
        // Guarantee that we have a valid connection while we run
        using (SqlConnection conn = 
            new SqlConnection("context connection=true"))
        {
            conn.Open();                       // open the connection
            SqlPipe pipe = SqlContext.Pipe;    // get the pipe

            // Find all the products in the database with any demand
            // whatsoever along with data about their production costs.
            // Make sure they are ordered.
            ArrayList items = new ArrayList();
            SqlCommand cmd = new SqlCommand(
                " SELECT DISTINCT tp.pid, pname, startupCost,"
                "     inventoryCost" +
                " FROM t_products tp" +
                " JOIN t_salesForecast ts" +
                " ON tp.pid = ts.pid" + 
                " ORDER BY pid",
                conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                items.Add(new Item(
                    reader.GetInt32(0), reader.GetSqlChars(1),
                    reader.GetInt32(2), reader.GetInt32(3)));
            }
            reader.Close();

            // Now get all the production schedule information, ordered
            // by PID and demand date
                "SELECT pid, demandDate, demandQty" + 
                " FROM t_salesForecast" + 
                "  WHERE demandDate >= @start" +
                "  AND demandDate <= @end" +
                " ORDER BY pid, demandDate",
                conn);
            cmd.Parameters.AddWithValue("@start", start);
            cmd.Parameters.AddWithValue("@end", end);
            reader = cmd.ExecuteReader();

            // Read each section of schedule information into the items.
            reader.Read();
            for (int i = 0; (i < items.Count) && (!reader.IsClosed); i++)
            {
                ((Item)(items[i])).readData(reader);
            }

            // ensure the reader is closed
            if (!reader.IsClosed) reader.Close();


            foreach (Item item in items)
            {
                // Compute the schedule and report it
                item.ComputeSchedule();
                item.OutputSchedule(pipe);
            }
        }
    }

    class Item
    {
        // Information about the product we are scheduling. These will
        // be pulled from the database.
        private int pid;
        private SqlChars name;
        private int startCost;
        private int holdCost;

        // Store how many dates we have.
        private int size = 0;

        // The dates on which we have demand. These are guaranteed to
        // be unique by the database, and we will load them in order.
        private ArrayList dates = new ArrayList();
        // Store what the demand was on each date.
        private ArrayList quantities = new ArrayList();

        // Our schedule, which we have not yet computed.
        int[] schedule = null;

        // Set up the metadata for the return
        SqlMetaData[] metadata = new SqlMetaData[] {
            new SqlMetaData("product", SqlDbType.NVarChar, MAXNAME),
            new SqlMetaData("period", SqlDbType.DateTime),
            new SqlMetaData("quantity", SqlDbType.Int)
        };

        public Item(int pid, SqlChars name, int startCost, int holdCost)
        {
            this.pid = pid;
            this.name = name;
            this.startCost = startCost;
            this.holdCost = holdCost;
        }

        /*
         * Read data from the stream until the PID does not match
         * ours anymore. We assume the reader is cued up to our
         * information and we leave it cued to the next item's
         * information UNLESS there is no more information, in which
         * case we close the reader to indicate as much.
         */
        public void readData(SqlDataReader reader)
        {
            size = 0;
            do
            {
                if (reader.GetInt32(0) == pid)
                {
                    size++;
                    dates.Add(reader.GetDateTime(1));
                    quantities.Add(reader.GetInt32(2));
                }
                else
                {
                    return;
                }
            } 
            while (reader.Read());

            // reader ran out. close it.
            reader.Close();
        }

        /*
         * This method is called to compute the production schedule
         * for the item. It does no I/O, but puts in motion the
         * dynamic programming algorithm which produces the schedule.
         */
        public void ComputeSchedule()
        {
            int[] days = ComputeProductionDays();
            schedule = new int[size];
            for (int i = 0; i < size; i++)
            {
                schedule[days[i]] += (Int32)(quantities[i]);
            }
        }

        /* 
         * Pipe the schedule to the user, computing it if need be.
         */
        public void OutputSchedule(SqlPipe pipe)
        {
            // Ensure that the schedule has been computed.
            if (schedule == null)
            {
                ComputeSchedule();
            }

            // Make a record in which to store the data.
            SqlDataRecord record = new SqlDataRecord(metadata);
            record.SetSqlChars(0, name);

            // Start up the output pipe.
            pipe.SendResultsStart(record);
            for (int i = 0; i < size; i++)
            {
                // Pipe each day of production. Omit zero production
                // days.
                if (schedule[i] != 0)
                {
                    record.SetDateTime(1, (DateTime)(dates[i]));
                    record.SetInt32(2, schedule[i]);
                    pipe.SendResultsRow(record);
                }
            }
            pipe.SendResultsEnd();
        }

        /* 
         * Compute the table and then walk it to find the best
         * days to produce the item.
         */
        private int[] ComputeProductionDays()
        {
            // We fill this in. It says when each day's quota is
            // actually produced.
            int[] productionDays = new int[size];
            
            // First, compute the table.
            int[][] table = ComputeTable();

            // Then walk the table, creating a second table which encodes
            // the best production days.
            int[] optimal = new int[size + 1];
            int[] optimalLoc = new int[size];
            optimal[size] = 0;
            for (int i = size - 1; i >= 0; i--)
            {
                int min = table[i][i] + optimal[i + 1];
                int minloc = i;
                for (int j = i+1; j < size; j++)
                {
                    int temp = table[i][j] + optimal[j + 1];
                    if (temp < min)
                    {
                        min = temp;
                        minloc = j;
                    }
                }
                optimal[i] = min;
                optimalLoc[i] = minloc;
            }

            // Finally, decode the optimal values into production days.
            int pday = 0;
            int until = optimalLoc[0] + 1;
            for (int i = 0; i < size; i++)
            {
                if (until == i)
                {
                    pday = i;
                    until = optimalLoc[i] + 1;
                }
                productionDays[i] = pday;
            }

            // We now have a list of days which we will produce the good.
            return productionDays;
        }

        /* 
         * The main part of the dynamic programming solution. Each entry
         * table[i,j] stores the cost of producing enough of the good on
         * day i to meet needs through day j. This table is only half-
         * filled when complete.
         */
        private int[][] ComputeTable()
        {
            int[][] table = new int[size][];
            for (int i = 0; i < size; i++) table[i] = new int[size];
            for (int i = 0; i < size; i++)
            {

                // If we produce the good on the same day we ship it we
                // incur a startup cost.
                table[i][i] = startCost;
                
                // For other days, we have the cost for the previous
                // cell plus the cost of storing the good for this long.
                for (int j = i + 1; j < size; j++)
                {
                    table[i][j] = table[i][j - 1] + 
                        (((int)quantities[j]) * holdCost *
                        diff((DateTime)(dates[i]), (DateTime)(dates[j])));
                }
            }
            return table;
        }

        /* 
         * A utility to compute the difference between two days. 
         */
        private int diff(DateTime start, DateTime end)
        {
            TimeSpan diff = end.Subtract(start);
            return diff.Days;
        }

    }
};

چگونگی مشاهده Dependencyهای یک Stored Procedure (SQL Server Management Studio)

این مقاله چگونگی مشاهده Dependencyهای یک Stored Procedure را با استفاده از Explorer در SQL Server Management Studio توضیح می دهد.

قبل از اینکه یک stored procedure را اصلاح (modify)، تغییر نام (rename)، یا حذف (delete) کنید، مهم است بدانید که کدام objectها وابسته به stored procedure هستند. مثلاً، اگر objectهای مستقل برای انعکاس تغییراتی که روی stored procedure ایجاد شده، update نشده باشند، تغییر نام یا definition یک stored procedure، ممکن است به fail شدن objectهای مستقل منجر شود.

مشاهده Dependencyهای یک Stored Procedure

1. در Object Explorer، به نمونه ای از Database Engine وصل شوید و سپس آن نمونه را بسط دهید.
2. Databases را بسط دهید، Databaseهایی را بسط دهید که stored procedure متعلق به آنها است، و سپس Programmability را بسط دهید.
3. Stored Procedures را بسط دهید، روی procedure راست کلیک کنید، و سپس View Dependencies را کلیک کنید.
4. لیست objectهایی را که وابسته به Stored Procedures هستند، مشاهده کنید.
5. لیست objectهایی را که Stored Procedures به آنها وابسته است نیز مشاهده کنید.
6. OK را کلیک کنید.

چگونگی حذف کردن یک Stored Procedure (SQL Server Management Studio)

این مقاله چگونگی حذف کردن یک stored procedure را با استفاده از Explorer در SQL Server Management Studio توضیح می دهد.

حذف کردن یک Stored Procedure

1. در Object Explorer، به نمونه ای از Database Engine وصل شوید و سپس آن نمونه را بسط دهید.
2. Databaseها را بسط دهید، Databaseهایی را بسط دهید که stored procedure متعلق به آن است، و سپس Programmability را بسط دهید.
3. Stored Procedures را بسط دهید، برای حذف کردن، روی procedure راست کلیک کنید، و سپس Delete را کلیک کنید.
4. جهت مشاهده objectهایی که وابسته به stored procedure هستند، Show Dependencies را کلیک کنید.
5. the correct stored procedure is selected را تائید و سپس OK را کلیک کنید.
6. نام stored procedure را از objectها و scriptهای مستقل حذف کنید.

چگونگی تغییر نام یک Stored Procedure (SQL Server Management Studio)

این مقاله چگونگی تغییر نام یک Stored Procedure را با استفاده از Explorer در SQL Server Management Studio توضیح می دهد.

تغییر نام یک Stored procedure

1. در Object Explorer، به نمونه ای از Database Engine وصل شوید و سپس آن نمونه را بسط دهید.
2. Databaseها را بسط دهید، Databaseهایی را بسط دهید که Stored procedure متعلق به آن است، و سپس Programmability را بسط دهید.
3. Dependencyهای Stored Procedures را تعیین کنید.
4. Stored Procedures را بسط دهید، برای تغییر دادا نام، روی procedure راست کلیک کنید، و سپس Rename را کلیک کنید.
5. نام stored procedure را تغییر دهید.
6. نام stored procedure را که در objectها و scriptهای مستقل ارجاع شده، تغییر دهید.

چگونگی اصلاح کردن (modify) یک Stored Procedure (SQL Server Management Studio)

این مقاله چگونگی اصلاح کردن یک Transact-SQL Stored procedure را با استفاده از Object Explorer در SQL Server Management Studio توضیح می دهد. وقتی شما عبارات یا پارامترهای یک Stored procedure را با استفاده از این متد تغییر می دهید، permissionهایی که برای Stored procedure تعریف شده اند، حفظ می شوند، و هیچ Stored procedure مستقلی یا trigger تحت تاثیر قرار نمی گیرد.

اصلاح یک Stored procedure

• در Object Explorer، به نمونه ای از Database Engine وصل شوید و سپس آن نمونه را بسط دهید.
• Databaseها را بسط دهید، Databaseهایی را بسط دهید که Stored procedure متعلق به آن است، و سپس Programmability را بسط دهید.
• Stored Procedures را بسط دهید، برای اصلاح کردن، روی procedure راست کلیک کنید، و سپس Modify را کلیک کنید.
• متن Stored procedure را اصلاح کنید.
• برای تست کردن syntax، در منوی Query، روی Parse کلیک کنید.
• جهت اصلاح کردن Stored procedure، در منوی Query، روی Execute کلیک کنید.
• جهت ذخیره script، در منوی File، روی Save کلیک کنید. نام فایل را قبول کنید یا آن را با نامی جدید جایگزین کنید، و سپس روی Save، کلیک کنید.

این مقاله چگونگی ایجاد یک Transact-SQL stored procedure را با استفاده از Object Explorer در SQL Server Management Studio توضیح می دهد و مثالی آورده که یک stored procedure ساده را در AdventureWorks database ایجاد میکند.

ایجاد یک Stored Procedure

1. در Object Explorer، به نمونه ای از Database Engine وصل شوید و سپس آن نمونه را بسط دهید.
2. Databaseها را بسط دهید، Databaseهایی را بسط دهید که stored procedure متعلق به آن است، و سپس Programmability را بسط دهید.
3. روی Stored Procedures کلیک راست، و سپس روی New Stored Procedure کلیک کنید.
4. در منوی Query، روی Specify Values for Template Parameters کلیک کنید.
5. در Specify Values for Template Parameters، ستون Value، محتوی valueهای پیشنهادی برای پارامترها است. Valueها را قبول کنید یا آنها را با valueهای جدید جایگزین کنید، و سپس OK را کلیک کنید.
6. در query editor، عبارت SELECT را با عبارتی برای procedure خودتان جایگزین کنید.
7. برای تست کردن syntax، در منوی ،Query روی Parse کلیک کنید.
8. جهت ایحاد stored procedure، در منوی Query، روی Execute کلیک کنید.
9. جهت ذخیره script، در منوی File، روی Save کلیک کنید. نام فایل را قبول کنید یا آن را با نامی جدید جایگزین کنید، و سپس روی Save، کلیک کنید.

ایجاد یک نمونه از Stored procedure

1. در Object Explorer، به نمونه ای از Database Engine وصل شوید و سپس آن نمونه را بسط دهید.

2. Databases را بسط دهید، AdventureWorks database را بسط دهید، و سپس Programmability را بسط دهید.

3. روی Stored Procedures کلیک راست، و سپس روی New Stored Procedure کلیک کنید.

4. در منوی Query، روی Specify Values for Template Parameters کلیک کنید.

5. در Specify Values for Template Parameters، Valueهای زیر را برای پارامترهایی که در زیر نشان داده شده، وارد کنید.

6. روی OK کلیک کنید.

7. در query editor، عبارت SELECT را با عبارات زیر جایگزین کنید.

SELECT FirstName, LastName, JobTitle, Department
ROM HumanResources.vEmployeeDepartment
HERE FirstName = @FirstName AND LastName = @LastName;

8. برای تست کردن syntax، در منوی Query روی Parse کلیک کنید. اگر پیام error دریافت کردید، عبارات را با اطلاعات بالا مقایسه کنید و در صورت لزوم تصحیح کنید.

9. جهت ایحاد stored procedure، در منوی Query، روی Execute کلیک کنید.

10. جهت ذخیره script، در منوی File، روی Save کلیک کنید.

11. جهت اجرا کردن stored procedure، روی نوار ابزار (toolbar)، روی New Query کلیک کنید.

12. در پنجره query، عبارات زیر را وارد کنید:

USE AdventureWorks;
GO
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Diane', @LastName = N'Margheim';
GO

13. روی منوی Query، Execute را کلیک کنید.