آیا تا بحال خواسته اید که یک DataTable یا یک آرایه که شامل ده ها، ده ها هزار ( و یا حتی میلیون ها ) رکورد می باشد را در فقط یک فراخوانی پایگاه داده به یکStored Procedure در SQL Server 2000 بفرستید ؟ و یا تا بحال خواسته اید که لیست شناسه های رکوردها را برای انتخاب یا حذف به یک Stored Procedure بفرستید ؟ اگر این کارها را انجام نداده اید و یا نتوانستید که برای انجام آن راه حلی پیدا کنید پس راه حل این کار را می توانید در اینجا ببینید .
پاسخ : فرستادن یک آرایه یا DataTable به یک Stored Procedure حقه انجام این کار تبدیل لیست های داده به آرایه ای از بایت می باشد ، که این آرایه ها را سپس به عنوان پارامتر از نوع image به Stored Procedure می فرستیم . سپس در Stored Procedure این تصویر به یک جدول متغیر که شامل داده های اصلی می باشد تبدیل می شود . تابع SQL Server زیر یکی از توابع تبدیل پارامتر از نوع image به یک جدول متغیر می باشد . این تابع یک جدول از نوع متغیر varchar ایجاد می کند . CREATE FUNCTION dbo.GetTableVarchar(@Data image) RETURNS @DataTable TABLE (RowID int primary key IDENTITY , Value Varchar(8000)) AS BEGIN –First Test the data is of type Varchar. IF(dbo.ValidateExpectedType(103, @Data)<>1) RETURN –Loop thru the list inserting each – item into the variable table. DECLARE @Ptr int, @Length int, @VarcharLength smallint, @Value Varchar(8000) SELECT @Length = DataLength(@Data), @Ptr = 2 WHILE(@Ptr<@Length) BEGIN –The first 2 bytes of each item is the length of the –varchar, a negative number designates a null value. SET @VarcharLength = SUBSTRING(@Data, @ptr, 2) SET @Ptr = @Ptr + 2 IF(@VarcharLength<0) SET @Value = NULL ELSE BEGIN SET @Value = SUBSTRING(@Data, @ptr, @VarcharLength) SET @Ptr = @Ptr + @VarcharLength END INSERT INTO @DataTable (Value) VALUES(@Value) END RETURN END من همچنین تعدادی کلاس C# برای ایجاد آرایه هایی از نوع بایت از نوع داده های مختلف ایجاد کرده ام . توجه : کد هایی که در ادامه می آید بخشی از یک برنامه برای مزرعه پرورش صدف می باشد .
پاسخ : فرستادن یک آرایه یا DataTable به یک Stored Procedure در این مثال برای فراخوانی پایگاه داده از کلاس SQLHelper که یکی از بلاک های برنامه مایکروسافت می باشد استفاده کرده ام . شما می توانید این بلاک برنامه را در MSDN مشاهده کنید. فرستادن یک لیست از شناسه های integer برای انتخاب رکوردهای مربوطه در متد زیر اقلام چک شده ی یک لیست باکس را گرفته و شناسه های آنها را به یک Stored Procedure می فرستیم . Stored Procedure سپس یک مجموعه از نتایج را که شامل جزییات هر شناسه انتخاب شده می باشد را بر می گرداند . private DataSet GetSelectedSnailDetails() { SQLIntListCreator snailIDs = new SQLIntListCreator(); foreach(Snail aSnail in mySelectionCheckedListBox.CheckedItems) snailIDs.AddValue(aSnail.SnailID); return SQLHelper.ExecuteDataset(CONN_STRING, “GetSnailDetails”, snailIDs.GetList()); }
پاسخ : فرستادن یک آرایه یا DataTable به یک Stored Procedure از Stored Procedure زیر برای گرفتن لیستی از نوع داده int که به فرم image ( آرایه ای از بایت) می باشد ؛ استفاده می شود . این روال لیست را به جدول متغیری از نوع int تبدیل می کند سپس این جدول را به ستون SnailID از جدول Snails متصل می نماید ، تا بتواند جزییات را برای هر صدف انتخاب شده برگرداند . CREATE PROCEDURE GetSnailDetails @SnailIDs image AS SELECT Snail.* FROM dbo.GetTableInt(@SnailIDs) SIDs INNER JOIN Snails ON SIDs.Value = Snails.SnailID GO
پاسخ : فرستادن یک آرایه یا DataTable به یک Stored Procedure روال ذخیره شده برای حذف همه صدف ها در لیست @Snails از نوع image به فرم زیر می باشد . CREATE PROCEDURE DeleteSnails @SnailIDs image AS DELETE Snail FROM dbo.GetTableInt(@SnailIDs) SIDs INNER JOIN Snails ON SIDs.Value = Snails.SnailID GO
پاسخ : فرستادن یک آرایه یا DataTable به یک Stored Procedure متد فرستادن مجموعه ای از حالات صدف به Stored Procedure برای بهنگام سازی به فرم زیر می باشد . private void UpdateSnailStatuses(Snail[] snails) { SQLIntListCreator snailIDs = new SQLIntListCreator(); SQLVarcharListCreator statuses = new SQLVarcharListCreator(); SQLRowversionListCreator rowVersions = new SQLRowversionListCreator(); foreach (Snail aSnail in snails) { snailIDs.AddValue(aSnail.SnailID); statuses.AddValue(aSnail.Status); rowVersions.AddValue(aSnail.RowVersion); } SQLHelper.ExecuteNonquery(CONN_STRING, “UpdateSnailStatuses”, snailIDs.GetList(), statuses.GetList(), rowVersions.GetList()); }
پاسخ : فرستادن یک آرایه یا DataTable به یک Stored Procedure از روال زیر نیز برای بهنگام سازی حالات صدف ها استفاده می شود . CREATE PROCEDURE dbo.UpdateSnailStatuses @SnailIDs image, @Statuses image, @RowVersions image AS UPDATE Snail SET Snail.Status = Statuses.Value FROM Snail INNER JOIN GetTableInt(@SnailIDs) SnailIDs ON Snail.SnailID = SnailIDs.Value INNER JOIN GetTableVarchar(@Statuses) Statuses ON SnailIDs.RowID = Statuses.RowID INNER JOIN GetTableRowversion(@RowVersions) RowVersions ON Statuses.RowID = RowVersions.RowID WHERE TSEQUAL(Snail.RowVersion, RowVersions.Value) RETURN @@ERROR
پاسخ : فرستادن یک آرایه یا DataTable به یک Stored Procedure فرستادن یک DataTable به یک Stored Procedure برای درج در یک جدول از کد زیر برای تبدیل داده های یک DataTable به پنج آرایه از نوع بایت استفاده می شود . این آرایه ها سپس به Stored Procedure ی AddSnails برای درج در جدول Snail فرستاده می شوند . private void AddSnails(DataTable snails) { SQLVarcharListCreator species = new SQLVarcharListCreator(); SQLDatetimeListCreator dOB = new SQLDatetimeListCreator(); SQLNvarcharListCreator location = new SQLNvarcharListCreator(); SQLBitListCreator isMale = new SQLBitListCreator(); SQLMoneyListCreator value = new SQLMoneyListCreator(); foreach(DataRow row in snails.Rows) { species.AddValue(row["Species"]); dOB.AddValue(row["DOB"]); location.AddValue(row["Location"]); isMale.AddValue(row["IsMale"]); value.AddValue(row["Value"]); } // A command is explicitly created here // instead of using SQLHelper as we need to be // able to set the CommandTimeout property // big enough to insert 1 000 000 records. using (SqlConnection cnn = new SqlConnection(CONN_STRING)) { cnn.Open(); SqlCommand comInst = new SqlCommand(“AddSnails”, cnn); comInst.CommandType = CommandType.StoredProcedure; comInst.CommandTimeout = 10000; comInst.Parameters.Add(“@Species”, species.GetListAndReset()); comInst.Parameters.Add(“@DOB”, dOB.GetListAndReset()); comInst.Parameters.Add(“@Location”, location.GetListAndReset()); comInst.Parameters.Add(“@IsMale”, isMale.GetListAndReset()); comInst.Parameters.Add(“@Value”, value.GetListAndReset()); comInst.ExecuteNonQuery(); } }
پاسخ : فرستادن یک آرایه یا DataTable به یک Stored Procedure کارایی استفاده از این روش برای مقایسه سرعت درج با استفاده از روش گفته شده در بالا (و بیان کارایی آن) با روش استاندارد وارد کردن یکی یکی رکورد ها به پایگاه داده ها مقایسه زیر با استفاده از یک کامپیوتر پنتیوم ۴ همراه با ۵۱۲ مگا بایت حافظه رم انجام شده است .در این مقایسه بخش کلاینت و سرور هر دو بر روی یک ماشین قرار داشته است . و در صورتیکه بخواهیم این آزمایش را بر روی یک شبکه سریع انجام دهیم زمان به مراتب کمتری خواهد داشت . Records Inserted Standard one stored procedure call per record (msec) One stored procedure call for all records (msec) 1 2 10 10 22 14 100 219 60 1000 2 259 505 10 000 22 593 4 083 100 000 301 368 44 645 1 000 000 3 094 385 571 020
