مقدمه:
یکی از کمبود هایی که در SQL Server 2005 و نسخه های قبل از آن وجود داشت، عدم امکان ارسال پارامتر ها با ساختار جدولی به یک پروسیجر (Stored Procedure) بود.
به طور مثال فرض کنید که قصد دارید تعداد 20 رکورد را در یک جدول در دیتابیس ذخیره کنید. یکی از راه های انجام این کار، این بود که به تعداد 20 مرتبه و در هر مرتبه یک رکورد را به دیتابیس ارسال نمایید تا در آن ذخیره شود.
راه های دیگری نیز وجود داشت که کمی پیچیده تر بود. به طور مثال می توانستید داده های مربوطه را با فرمت XML برای یک پروسیجر ، بفرستید و XML را داخل پروسیجر ، Parse نموده و عملیات مورد نظر را انجام دهید.
اما در SQL Server 2008 نوع جدیدی از داده معرفی شده است که ما آن را پارامترهای جدولی یا ساخت یافته (Table-Valued Parameter) می نامیم.
در این مقاله به معرفی این نوع پارامتر و نحوه استفاده از آن می پردازم.
شروع:
یک دیتابیس جدید به نام Test ایجاد کنید.
ایجاد و استفاده از پارامتر های جدولی در نگاه اول، ممکن است کمی پیچیده به نظر آید ولی با کمی دقت ملاحظه می کنید که این کار بسیار ساده می باشد.
در این مقاله ما جدولی به نام Users ایجاد می کنیم که مشخصات کاربران را نگهداری می نماید. این جدول دارای سه فیلد می باشد که در قطعه کد زیر آن را ملا حظه می نمایید.
CREATE TABLE [dbo].[Users]
(
[UserID] [int] NOT NULL,
[FirstName] [nvarchar](100) NOT NULL,
[LastName] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED([UserID] ASC) ON [PRIMARY]
)
اکنون قصد داریم یک نوع داده جدولی ایجاد نماییم که قابل ارسال به یک پروسیجر به صورت یک پارامتر باشد. (در این مقاله قصد داریم که تعداد 20 رکورد را به یک پروسیجر برای ذخیره شدن در جدول کاربران بفرستیم)
به مسیر نشان داده شده در شکل زیر بروید. روی User-Defined Table Types کلیک راست نموده و سپس روی New User-Defined Table Type کلیک کنید تا یک نوع جدید ایجاد نمایید.
اکنون قصد داریم که یک نوع داده جدولی جدید به نام MyType برای جدول Users ایجاد نماییم. قطعه کد زیر را در پنجره باز شده بنویسید.
CREATE TYPE dbo.MyType AS TABLE
(
UserID int NOT NULL,
FirstName NVARCHAR(100) NULL,
LastName NVARCHAR(100) NULL,
PRIMARY KEY (UserID)
)
عملکرد قطعه کد بالا کاملا واضح می باشد. ما یک نوع داده به نام MyType از نوع جدول ایجاد نموده ایم که دارای سه فیلد به نام های UserID و FirstName و LastName می باشد. این نوع داده، عملکردی شبیه یک جدول معمولی دارد.
برای اینکه عملکرد نوع داده MyType را ببینید، دستورات زیر را نوشته و اجرا نمایید.
DECLARE @MyTable MyType
INSERT INTO @MyTable(UserID,FirstName,LastName)
VALUES ('1','Morteza 1','Sahragard 1'),
('2','Morteza 2','Sahragard 2'),
('3','Morteza 3','Sahragard 3'),
('4','Morteza 4','Sahragard 4'),
('5','Morteza 5','Sahragard 5')
SELECT * FROM @MyTable
در اسکریپت بالا، ما یک متغیره از نوع داده MyType ایجاد نموده ایم و سپس 5 رکورد به آن اضافه نموده ایم. نتیجه اجرای اسکریپت بالا در زیر مشاهده می نمایید.
استفاده از نوع داده جدولی به عنوان پارامتر :
اکنون قصد داریم یک پروسیجر بنویسم که داده جدولی ما را به عنوان پارامتر دریافت نموده و تمام رکورد های موجود در آن را در جدول Users وارد نماید.
Create PROCEDURE [dbo].[uspInsertUsers]
(
@MyTable MyType READONLY
)
AS
INSERT INTO Users(UserID, FirstName,LastName)
SELECT UserID,FirstName, LastName FROM @MyTable
در اسکریپت بالا، یک پروسیجر را مشاهده می کنید که یک پارامتر از نوع MyType دریافت نموده و رکورد های موجود در آن را در جدول Users ذخیره می نماید.
تذکر:
همانطور که ملاحظه می نمایید، هنگام تعریف پارامتر در اسکریپ بالا، از عبارت READONLY استفاده شده است. ذکر این عبارت الزامی می باشد، زیرا هنگام ارسال داده های جدولی، در حقیقت یک کپی از داده ها به پروسیجر ارسال نمی شود و برای کارایی بیشتر، فقط یک اشاره گر، به پروسیجر ارسال می شود که به داده جدولی اشاره می کند. هدف ما این است که تغییر در داده جدولی، در داخل پروسیجر، خود داده را در بیرون از پروسیجر تحت تاثیر قرار ندهد و لذا باید آن را به صورت READONLY تعریف کنیم.
فراخوانی پروسیجر در برنامه های کاربردی:
اکنون قصد داریم از پروسیجر بالا در یک برنامه کاربردی استفاده نماییم.
در قطعه کد زیر، ما یک DataTable ایجاد نموده ایم که دارای 20 رکورد می باشد و این DataTable را برای ذخیره نمودن رکورد ها به پروسیجر مربوطه ارسال می کنیم.
private void Form1_Load(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection("Connection String"))
{
//Call Function to populate user's data
DataTable usersData = PopulateData();
SqlCommand cmd = new SqlCommand("uspInsertUsers", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MyTable", usersData);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
//populate DataTable with 20 records from users
private DataTable PopulateData()
{
DataTable tb = new DataTable();
DataColumn col = new DataColumn("UserId", typeof(int));
tb.Columns.Add(col);
col = new DataColumn("FirstName", typeof(string));
tb.Columns.Add(col);
col = new DataColumn("LastName", typeof(string));
tb.Columns.Add(col);
for (int i = 0; i < 20; i++)
{
DataRow row = tb.NewRow();
row["UserId"] = i;
row["FirstName"] = "FirstName " + i.ToString();
row["LastName"] = "LastName " + i.ToString();
tb.Rows.Add(row);
}
return tb;
}
پس از اجرای قطعه کد بالا، مقادیر زیر وارد جدول Users می شوند.
همانطور که ملاحظه می نمایید، استفاده از داده های جدولی می تواند به مقدار زیادی راندمان و کارایی برنامه را بالا ببرد.
منابع : 30sharp.com
sqlteam