تا %60 درصد تخفیف برای تنها 2 نفر با کد 3X5X38JQ + صدور گواهینامه فقط تا
000000

درج انبوه رکوردها در SQL Server با سی شارپ (C# SQL Bulk Insert)

عملیات درج رکورد ها در SQL Server به تعداد بالا می تونه خیلی زمان بر باشه، البته اگر به صورت عادی این کار انجام بشه. اما بوسیله قابلیت Bulk Insert یا درج انبوه رکوردها مدت زمان این کار به طرز چشم گیری کاهش پیدا می کنه. برای مثال، ممکنه شما قصد داشته باشید 1 میلیون رکورد رو در بانکتون INSERT کنید، گر تمام این رکورد ها رو خط به خط اجرا کنید این عملیات ممکنه ساعت ها طول بکشه، در حالی که بوسیله Bulk Insert این زمان حتی تا کمتر از 1 دقیقه هم کاهش پیدا می کنه. در این مطلب قصد داریم با نحوه Bulk Insert در SQL Server با کمک کلاس های DataTable و SqlBulkCopy آشنا بشیم.

در ابتدا یک بانک و جدول نمونه با اسکریپت زیر ایجاد می کنیم:

create database SampleForBulkInsert;
go

create table SampleTable
(
 [Id] int identity not null primary key,
 [From] nvarchar(200) not null,
 [To] nvarchar(200) not null,
 [Date] datetime,
 [Balance] decimal not null
);
go

قطعه کد زیر عملیات درج رو به صورت رکورد به رکورد و استفاده مستقیم دستور INSERT INTO انجام میده. یعنی عملیات Bulk Insert نداریم:

Stopwatch sw = new Stopwatch();
sw.Start();

using (var cnn = new SqlConnection("data source=.; initial catalog=SampleForBulkInsert; user id=sa; password=1"))
{
    cnn.Open();
    for (int i = 0; i < 100000; i++)
    {
        var command = cnn.CreateCommand();
        command.CommandText = "insert into SampleTable values ('Hossein Ahmadi','Mohammad Nasiri',@date,10000);";
        command.Parameters.Add(new SqlParameter("date", DateTime.UtcNow));
        command.ExecuteNonQuery();
    }
}
sw.Stop();

Console.WriteLine(sw.Elapsed);

ما اینجا از کلاس StopWatch برای بدست آوردن مدت زمان اجرای کد استفاده کردیم که مدت زمان اجرا برای 100000 رکورد حدود 25 ثانیه بود:

00:00:25.7867841
Press any key to continue . . .

حالا عملیات درج رو برای 100000 رکورد با کلاس SqlBulkCopy و DataTable انجام میدیم:

Stopwatch sw = new Stopwatch();
sw.Start();

DataTable table = new DataTable();
table.Columns.Add("From", typeof(string));
table.Columns.Add("To", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
table.Columns.Add("Balance", typeof(decimal));

for (int i = 0; i < 100000; i++)
{
    var dataRow = table.NewRow();
    dataRow["From"] = "Hosein Ahmadi";
    dataRow["To"] = "Mohammad Nasiri";
    dataRow["Date"] = DateTime.UtcNow;
    dataRow["Balance"] = 10000;
    table.Rows.Add(dataRow);
}

using (var cnn = new SqlConnection("data source=.; initial catalog=SampleForBulkInsert; user id=sa; password=1"))
{
    SqlBulkCopy bulkCopy = new SqlBulkCopy(cnn);
    bulkCopy.ColumnMappings.Add("From", "From");
    bulkCopy.ColumnMappings.Add("To", "To");
    bulkCopy.ColumnMappings.Add("Date", "Date");
    bulkCopy.ColumnMappings.Add("Balance", "Balance");
    bulkCopy.DestinationTableName = "SampleTable";
    cnn.Open();
    bulkCopy.WriteToServer(table);
}
sw.Stop();

Console.WriteLine(sw.Elapsed);

مدت زمان اجرای کوئری بالا خیلی سریعتر از حالت درج تک به تک رکورد ها هست، حدود نیم ثانیه که نسبت به 25 ثانیه تفاوت چشم گیری داره:

00:00:00.5161634
Press any key to continue . . .

و درج حدود 1 میلیون رکورد:

00:00:05.8161634
Press any key to continue . . .

اما در کد بالا به ترتیب چه کاری انجام دادیم، در ابتدا تعریف یک DataTable که شامل ستون هایی معادل جدول ما در بانک اطلاعاتی هست:

DataTable table = new DataTable();
table.Columns.Add("From", typeof(string));
table.Columns.Add("To", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
table.Columns.Add("Balance", typeof(decimal));

در مرحله بعد اضافه کردن رکورد ها به DataTable:

for (int i = 0; i < 1000000; i++)
{
    var dataRow = table.NewRow();
    dataRow["From"] = "Hosein Ahmadi";
    dataRow["To"] = "Mohammad Nasiri";
    dataRow["Date"] = DateTime.UtcNow;
    dataRow["Balance"] = 10000;
    table.Rows.Add(dataRow);
}

و در نهایت درج رکورد ها در بانک اطلاعاتی:

using (var cnn = new SqlConnection("data source=.; initial catalog=SampleForBulkInsert; user id=sa; password=1"))
{
    SqlBulkCopy bulkCopy = new SqlBulkCopy(cnn);
    bulkCopy.ColumnMappings.Add("From", "From");
    bulkCopy.ColumnMappings.Add("To", "To");
    bulkCopy.ColumnMappings.Add("Date", "Date");
    bulkCopy.ColumnMappings.Add("Balance", "Balance");
    bulkCopy.DestinationTableName = "SampleTable";
    cnn.Open();
    bulkCopy.WriteToServer(table);
}

دو نکته که باید در کد بالا بهش توجه کنیم:

1. اضافه کردن ColumnMapping برای مشخص کردن ستون های معادل در بانک اطلاعاتی
2. مشخص کردن نام جدول بوسیله DestinationTableName

نویسنده: حسین احمدی
منبع: جزیره برنامه نویسی وب سایت توسینسو
هر گونه نشر و کپی برداری با ذکر نام منبع و نویسنده بلامانع است 

عنوان
1 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 1 : مقدمه رایگان
2 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 2 : کلاس و شئ رایگان
3 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 3 : فیلد و متد رایگان
4 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 4 : Property ها رایگان
5 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 5: سازنده ها رایگان
6 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 6 : وراثت رایگان
7 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 7 : Polymorphism رایگان
8 آموزش برنامه نویسی شی گرا در (#C) قسمت 8 : abstract و sealed رایگان
9 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 9:سازنده در وراثت رایگان
10 برنامه نویسی شی گرا در سی شارپ (#C) قسمت 10: Exntesion Method ها رایگان
11 برنامه نویسی سی شارپ (#C) قسمت 11: Value Type و Reference Type رایگان
12 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 12 : Interface رایگان
13 آموزش برنامه نویسی شی گرا در (#C) قسمت 13: Dependency Injection رایگان
14 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 14 : تبدیل نوع رایگان
15 آموزش برنامه نویسی شی گرا (#C) قسمت 15 : Operator Overloading رایگان
16 برنامه نویسی شی گرا در سی شارپ (#C) قسمت 16 : Boxing و Unboxing رایگان
17 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 17 : Generics رایگان
18 آموزش برنامه نویسی شی گرا در (#C) قسمت 18 : List و Dictionary رایگان
19 آموزش برنامه نویسی شی گرا در سی شارپ (#C) قسمت 19 : مدیریت خطاها رایگان
20 درج انبوه رکوردها در SQL Server با سی شارپ (C# SQL Bulk Insert) رایگان
زمان و قیمت کل 0″ 0
4 نظر
منال مجموعه

سلام. من از data table استفاده کردم. برای داده های کمتر از. شما چطور رکورد بالا میدین که ثبت بشه؟ منظورم کد یا فرم کاربر؟؟؟ 

حسین احمدی

سلام، وقت بخیر

دقیق متوجه منظورتون نشدم، اما برای فرم هایی که کاربر اطلاعات وارد می کنه و اطلاعات زیاد نیست نیازی به استفاده از BulkCopy نیست، این قابلیت زمانی استفاده میشه که تعداد رکوردها خیلی بالا باشه، برای مثال شما قصد داشته باشید حجم زیادی از تراکنش رو به روز رسانی کنید یا از یک جدول به یک جدول دیگه انتقال بدید یا موارد مشابه که می تونید از این قابلیت استفاده کنید.

منال مجموعه

پس اینکار تو خود Sql با کویری انجام میگیره مثلا بین دو جدول از Insert select استفاده کنیم. ببخشید میپرسم که دلیل استفاده و نوعش رو یاد بگیرم

حسین احمدی

یه نکته ای این وسط هست، شما هر دستور SQL که اجرا می کنید، حتی در محیط سی شارپ، کوئری داخل خود SQL Server اجرا میشه. شما وقتی از SqlBulkCopy استفاده می کنید، اطلاعات رو برای درج از داخل سی شارپ به SQL میدید، اما موضوعی که هست، SqlBulkCopy به صورت Batch عملیات درج رو انجام میده و باعث میشه سرعت اجرا بالاتر بره، مثلاً هر 100 عملیات درج رو با یک Operation انجام میده که باعث میشه سرعت اجرا خیلی میره بالاتر. شما وقتی از INSERT SELECT استفاده می کنید اطلاعات داخل SQL از یک جدول به یک جدول دیگه ریخته میشه، اما BulkCopy رو اینطور بگیم بهتره، شما اطلاعات رو از یک جدول می خونید یکسری تغییرات رو اطلاعات انجام میدید و درج رو داخل یک جدول دیگه انجام میدید. البته این مثال هست و خیلی کاربردهای دیگه هست که میشه برای SqlBulkCopy نام برد.

نظر شما
برای ارسال نظر باید وارد شوید.
از سرتاسر توسینسو
تنظیمات حریم خصوصی
تائید صرفنظر