CommandBuilder Object – ADO.NET

CommandBuilder Object: पिछले Section में हमने Drag-and-Drop Technique का प्रयोग करते हुए एक Single Table के लिए Fully-Functional Application Create करने के बारे में जानाए जिसमें हमनें किसी तरह का कोई Code नहीं लिखा था। हालांकि हम हमारी जरूरत के अनुसार SQL Statement को Modify कर सकते थे।

साथ ही हमने ये भी देखा कि किस प्रकार से केवल एक SELECT SQL Command के आधार पर Visual Studio स्वयं ही INSERT, UPDATE व DELETE SQL Commands के लिए Automatically Codes Generate कर देता है, जिसे हम हमारी Table के Data को Insert, Delete या Update करने के लिए Use कर सकते हैं। इन Automatically Generate होने वाले INSERT, UPDATE व DELETE Related Command Codes को SqlCommandBuilder Object द्वारा ही Invisibly या Behind the Scene Create किया जाता है।

लेकिन यदि हम इन Automatically Generate होने वाले Commands के Codes को ध्‍यान से देखें] तो हमें पता चलता है कि ये Codes Most Efficient Command Codes नहीं हैं। उदाहरण के लिए यदि हम हमारी Customers Table के लिए Visual Studio द्वारा UpdateCommand.CommandText के लिए Generate किए गए Command Text को Visual Studio के Solution Explorer में दिखाई देने वाली dsNW.Designer.cs File में देखें] तो ये कुछ निम्नानुसार दिखाई देता है:

this._adapter.UpdateCommand.CommandText = 
	@"UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, 
	[CompanyName] = @CompanyName, 
	[ContactName] = @ContactName, 
	[ContactTitle] = @ContactTitle, 
	[Address] = @Address, [City] = @City, 
	[Region] = @Region, 
	[PostalCode] = @PostalCode, 
	[Country] = @Country, 
	[Phone] = @Phone, 
	[Fax] = @Fax 
WHERE 
(
  ([CustomerID] = @Original_CustomerID) 
  AND ([CompanyName] = @Original_CompanyName) 
  AND ((@IsNull_ContactName = 1 AND [ContactName] IS NULL) 
  OR ([ContactName] = @Original_ContactName)) 
  AND ((@IsNull_ContactTitle = 1 AND [ContactTitle] IS NULL) 
  OR ([ContactTitle] = @Original_ContactTitle)) 
  AND ((@IsNull_Address = 1 AND [Address] IS NULL) 
  OR ([Address] = @Original_Address)) 
  AND ((@IsNull_City = 1 AND [City] IS NULL) OR ([City] = @Original_City)) 
  AND ((@IsNull_Region = 1 AND [Region] IS NULL) OR ([Region] = @Original_Region)) 
  AND ((@IsNull_PostalCode = 1 AND [PostalCode] IS NULL) 
  OR ([PostalCode] = @Original_PostalCode)) 
  AND ((@IsNull_Country = 1 AND [Country] IS NULL) OR ([Country] = @Original_Country)) 
  AND ((@IsNull_Phone = 1 AND [Phone] IS NULL) OR ([Phone] = @Original_Phone)) 
  AND ((@IsNull_Fax = 1 AND [Fax] IS NULL) OR ([Fax] = @Original_Fax)));

  SELECT 
	CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region,
	PostalCode, Country, Phone, Fax 
  FROM Customers 
  WHERE (CustomerID = @CustomerID)
)";

ये Automatically Generated SQL Query काफी अजीब दिखाई पडता है। ये SQL Query न केवल Primary Key Column को Update करने की कोशिश करता है, बल्कि WHERE Clause में हर Column के मान को Compare भी करता है।

इसलिए हालांकि हमारी SQL Query में केवल 11 Columns Involve हैं, फिर भी Automatically Generate होने वाली Query पूरे एक Page के बराबर है और ऐसा इसीलिए है, ताकि Visual Studio द्वारा Concurrency Related Conflicts का ध्‍यान रखा जा सके।

यानी Auto-Generated Codes, Record को Update करते समय हर Column की Value को Original Values से Compare करते हुए Extra-Safe Approach Use करता है, ताकि Record Update करते समय जिन Columns में नई Value को Set नहीं किया गया है, उनमें उनकी पुरानी Value ही फिर से Update हो जाए, जिसकी वास्तव में कोई जरूरत नहीं है।

हालांकि हम TableAdapter की Properties में अपना स्वयं का CommandText SQL Statement Specify कर सकते हैं। लेकिन फिर भी ऐसी Complicated Queries] जिनमें Multiple Tables Involve होते हैं, हमें हमारे CommandText को Manually ही Specify करना जरूरी होता है, क्योंकि Visual Studio का Auto-Generated SQL Query इस प्रकार की SQL Queries को बेहतर तरीके से Generate व Control नहीं कर सकता।

SqlCommandBuilder Object, SelectCommand को Use करते हुए Underlying Database से Directly METADATA Retrieve करता है। जबकि यदि हम SelectCommand Change करते हैं, तो हमें SqlCommandBuilder Object के साथ RefreshSchema() Method को Call करना होता है, ताकि अन्य Commands (INSERT, UPDATE, DELETE) भी Refresh हो जाऐं।

Command Builder के काम करने की केवल यही शर्त है कि हम इस Object के साथ केवल एक ही Table के लिए SelectCommand Specify कर सकते हैं और Primary Key या Unique Key Column का Return होने वाले Resultset का हिस्सा होना जरूरी होता है। यदि ये दोनों Criteria Match न हों] तो इस प्रकार का CommandBuilder Object InvalidOperationException नाम का Exception Trigger करता है।

इसलिए यदि हम इस Object का प्रयोग करते हुए Complex SQL Queries को उपयोग में लेना चाहते हैं, तो हमें कुछ Extra काम करना पडता है। यानी हमें Database Schema पर एक Extra Query Fire करना पडता है।

Default रूप से CommandBuilder स्वयं Automatically जो Queries Generate करता है, वे ऊपर दिखाई गई SQL Query के समान होती हैं, जो कि Efficient SQL Queries नहीं हैं, हालांकि ये Queries Super-Safe जरूर हैं, क्योंकि UPDATE, INSERT या DELETE Operation Perform करते समय ये Auto-Generated SQL Queries हर Column को Compare करते हैं। साथ ही ये Auto-Generated Queries किसी Particular Database Specific नहीं होने की वजह से Portable भी होते हैं। इस प्रकार की Queries के सबसे ज्यादा Inefficient होने का मुख्‍य कारण इसके WHERE Clause का Complicated होना ही होता है।

हम CommandBuilder Object की ConflictOption Property को उपयोग में लेकर इस Safe लेकिन Inefficient SQL Query को Use होने से रोक सकते हैं और अपनी स्वयं की SQL Query Specify करते हुए उस Specified SQL Query के आधार पर CommandBuilder की Default Working को Modify कर सकते हैं। कैसे\

सबसे पहले हम एक नया Console Application Create करते हैं और इस Application में एक नया CommandBuilder Object Create करते हैं। CommandBuilder Object Create करने के लिए हम दो तरीके उपयोग में ले सकते हैं।

पहले तरीके के अन्तर्गत हम CommandBuilder के Default Constructor को Use करते हैं, जिसमें DataAdapter को एक Property के रूप में Setup करते हैं। जबकि दूसरे तरीके के अन्तर्गत हम DataAdapter को Constructor में एक Parameter की तरह Pass करते हैं। यदि हम दूसरे तरीके को Use करते हुए अपना CommandBuilder Create करना चाहें, तो हमारा Code कुछ निम्नानुसार होता है:

 SqlDataAdapter da4ComBuilder = new SqlDataAdapter(“Select * From Customers“, conStr);
SqlCommandBuilder comBuilder = new SqlCommandBuilder(da4ComBuilder);

जब एक बार हम इस प्रकार से CommandBuilder Object को Setup कर देते हैं, उसके बाद हम उस CommandBuilder द्वारा INSERT, UPDATE व DELETE SQL Statements के लिए Automatically Generate होने वाले Methods की CommandText Property को आसानी से इस बात को Check कर सकते हैं कि Command Builder ने हमारे द्वारा Specified SQL Statement को किस प्रकार से Convert किया है। जैसे:

 Console.WriteLine(“Update Command: “);
Console.WriteLine(comBuilder.GetUpdateCommand().CommandText);

यदि हम इन Codes एक Program के रूप में Create करें, तो हमारा Program कुछ निम्नानुसार होगा:

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

namespace CommandBuilder
{
    class Program
    {
        static void Main(string[] args)
        {
            string conString = "Data Source=KULDEEP\\SQLSERVEREXPRESS;Initial Catalog=Northwind;Integrated Security=True";

            using (SqlConnection conStr = new SqlConnection(conString))
            {
                 SqlDataAdapter da4ComBuilder = new SqlDataAdapter("Select * From Customers", conStr);
                 SqlCommandBuilder comBuilder = new SqlCommandBuilder(da4ComBuilder);
                 Console.WriteLine(comBuilder.GetUpdateCommand().CommandText);
            }
        }
    }
}

जिसका Output कुछ निम्नानुसार होता है:

UPDATE [Customers] SET 
  [CustomerID] = @p1, 
  [CompanyName] = @p2, 
  [ContactName] = @p3, 
  [ContactTitle] = @p4, 
  [Address] = @p5, 
  [City] = @p6, 
  [Region] = @p7, 
  [PostalCode] = @p8, 
  [Country] = @p9, 
  [Phone] = @p10, 
  [Fax] = @p11 
WHERE 
(
  ([CustomerID] = @p12) AND ([CompanyName] = @p13) 
  AND ((@p14 = 1 AND [ContactName] IS NULL) 
  OR ([ContactName] = @p15)) AND ((@p16 = 1 AND [ContactTitle] IS NULL) 
  OR ([ContactTitle] = @p17)) AND ((@p18 = 1 AND [Address] IS NULL) 
  OR ([Address] = @p19)) AND ((@p20 = 1 AND [City] IS NULL) 
  OR ([City] = @p21)) AND ((@p22 = 1 AND [Region] IS NULL) 
  OR ([Region] = @p23)) AND ((@p24 = 1 AND [PostalCode] IS NULL) 
  OR ([PostalCode] = @p25)) AND ((@p26 = 1 AND [Country] IS NULL) 
  OR ([Country] = @p27)) AND ((@p28 = 1 AND [Phone] IS NULL) 
  OR ([Phone] = @p29)) AND ((@p30 = 1 AND [Fax] IS NULL) 
  OR ([Fax] = @p31))
)

जैसाकि हम CommandBuilder Object के इस Output CommandText द्वारा समझ सकते हैं कि WHERE Clause के बाद Specified विभिन्न @p.. Parameters केवल इसीलिए Check किए जा रहे हैं, ताकि किसी भी स्थिति में Concurrency Conflict न हो। जबकि इन्हीं Extra Checking को Modify करते हुए हम इनके स्थान पर Efficient Codes Specify कर सकते हैं।

हम चाहते हैं कि CommandBuilder इस प्रकार के Extra Checking Codes Create न करे, इसलिए हमें CommandBuilder Object की ConflictOption Property को CompareAllSearchableValues के स्थान पर CompareRowVersion Value Set करना होता है। यानी यदि हम हमारे पिछले Example Program को ही इस Property के साथ Modify करें, तो हमारा Modified Program कुछ निम्नानुसार होगा:

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

namespace CommandBuilder
{
    class Program
    {
        static void Main(string[] args)
        {
            string conString = "Data Source=KULDEEP\\SQLSERVEREXPRESS;Initial Catalog=Northwind;Integrated Security=True";

            using (SqlConnection conStr = new SqlConnection(conString))
            {
                 SqlDataAdapter da4ComBuilder = new SqlDataAdapter("Select * From Customers", conStr);
                 SqlCommandBuilder comBuilder = new SqlCommandBuilder(da4ComBuilder);

                 comBuilder.ConflictOption = ConflictOption.CompareRowVersion;

                 Console.WriteLine(comBuilder.GetUpdateCommand().CommandText);
            }
        }
    }
}

इस Modified Program का Output हमें कुछ निम्नानुसार प्राप्त होता है:

UPDATE [Customers] SET 
  [CustomerID] = @p1, 
  [CompanyName] = @p2, 
  [ContactName] = @p3, 
  [ContactTitle] = @p4, 
  [Address] = @p5, 
  [City] = @p6, 
  [Region] = @p7, 
  [PostalCode] = @p8, 
  [Country] = @p9, 
  [Phone] = @p10, 
  [Fax] = @p11 
WHERE 
  (([CustomerID] = @p12))

जैसाकि इस Program के Output द्वारा हम समझ सकते हैं कि इस बार CommandBuilder Object की CommandText Property में Create होने वाले SQL Statement में WHERE Clause में पिछले Program की तरह Extra Checking नहीं हो रही है और ऐसा इसलिए हो रहा है, क्योंकि हमने इस Program में CommandBuilder Object के साथ ConflictOption Property को निम्नानुसार तरीके से Set किया है:

comBuilder.ConflictOption = ConflictOption.CompareRowVersion;

जबकि यदि हम चाहें तो इस Property को निम्नानुसार भी Set कर सकते हैं:

comBuilder.ConflictOption = ConflictOption.OverwriteChanges;

जब हम इस प्रकार से OverwriteChanges मान को Specify करते हैं, तब भी हमें उपरोक्तानुसार Efficient Output ही प्राप्त होता है, लेकिन इस Option को Specify करके हम CommandBuilder Object को इस बात का Instruction देते हैं कि उसे Concurrency Conflict के बारे में चिन्ता करने की जरूरत नहीं है, बल्कि वह Primary Key की Value को छोडकर अन्य सभी Values को Overwrite कर सकता है।

इस प्रकार से उपरोक्त Discussion द्वारा हम समझ सकते हैं कि किस तरह से हम CommandBuilder Object को SQL Queries Generate करने के लिए Use कर सकते हैं। साथ ही हमें ConflictOption Property के रूप में कुछ Extra Functionalities भी प्राप्त होती हैं, जिसका प्रयोग करके जिन चीजों को हम Pure Drag-and-Drop Operation में Include करना नहीं चाहते, उन्हें Ignore कर सकते हैं।

यानी CommandBuilder का प्रयोग करके हम हमारे SQL Statement को उतना Efficient बना सकते हैं, जितनी Efficiency सामान्यत: हमें Manual Code द्वारा प्राप्त होती है, जबकि इससे ज्यादा Efficiency प्राप्त करने के लिए Manual SQL Statement लिखना ही आखिरी ऊपाय होता है।