SQL Injection Example – Parameterized Queries

Parameterized Queries: String Formatted SQL Queries, Frontend Database से External Data Source पर Pass होता है। इस SQL Query String में न केवल Essential Command Keywords व Syntactical Elements होते हैं, बल्कि हर Query द्वारा Return होने वाले Resultset को Limit करने के लिए Data Values भी होते हैं, जिन्हें WHERE Clause के साथ Condition या Criteria के रूप में Specify किया जाता है।

String Formatted SQL Queries Create करना लगभग सभी प्रकार के Data Sources से Data Access व Manipulate करने के लिए Use किया जाने वाला एक जरूरी Step है। लेकिन String Format के रूप में SQL Query को Use करना न केवल काफी असुविधाजनक होता है, बल्कि Risky भी होता है। क्योंकि इस तरह की SQL Queries Use करने पर SQL Injection Attack होने की सम्भावना रहती है।

SQL Server Provider में Parameters System.Data.SqlClient.SqlParameter Class के रूप में प्राप्त होते हैं। इस Class के Objects Create करके उन्हें SqlCommand Object के साथ Add करके हम Parameterized Queries Define कर सकते हैं। जबकि यदि हमें ODBC या OLEDB को Use करते हुए Parameterized Query Create करनी हो, तो हमें System.Data.OleDb.OleDbParameter या System.Data.Odbc.OdbcParameter Class के Object Create करके OleDbCommand या OdbcCommand Object में Add करना होता है।

SQL Injection Attack

Parameterized Query Define करने का मूल कारण SQL Injection Attack से अपने Application को Secure करना ही होता है। जिसमें User किसी ऐसे SQL Statement को Input करता है, जिससे या तो Underlying Database पूरी तरह से Unstable हो जाता है या User Unauthorized तरीके से Application को Access करने में सक्षम हो जाता है। उदाहरण के लिए निम्न SQL Statement देखते हैं:

UPDATE Employee SET Salary = XXX WHERE ID = 1;

मानलो कि जिस Application में इस SQL Query को Specify किया गया है, उसके Frontend Form पर एक Textbox है, जिसमें Specified मान इस SQL Query के XXX के स्थान पर Replace होता है और उस Employee की Salary को Update कर देता है, जिस का ID 1 है।

मानलो कि Employee ID 1 वाले Employee की Salary 15000 थी, जिसे इस साल के Increment के कारण 20000 कर दिया गया है। परिणामस्वरूप उस Employee की Salary को Underlying Database में भी Update करना पडेगा ताकि अगली बार जब उसे Salary Pay की जाए, तो होने वाली सारी Account Related Calculations 20000 Salary के आधार पर Calculate हो।

इस Employee की Salary Update करने के लिए हमें Frontend Form पर स्थित Textbox में मान 20000 Specify करके Update Button पर Click करना होगा, जिससे इस Textbox में Specified मान 20000, Underlying SQL Query को निम्नानुसार Modify करेगा:

UPDATE Employee SET Salary = 20000 WHERE ID = 1;

परिणामस्वरूप उस Employee ID 1 वाले Employee की Salary 15000 से 20000 हो जाएगी। लेकिन यदि हम Frontend Form पर दिखाई देने वाले Textbox में मान 20000 Specify करने के स्थान पर निम्नानुसार एक और Statement को String के रूप में Input कर दे:

150000 WHERE FirstName=’Kuldeep’ AND LastName = ‘Mishra’;
UPDATE Employee SET Salary = 1000

तो इस बार जब हम Form पर दिखाई देने वाले Update Button पर Click करेंगे, तो इस बार XXX के स्थान पर ये SQL Query Specify हो जाएगा। परिणामस्वरूप इस बार Underlying Database पर वास्तव में Execute होने वाली Internal SQL Query कुछ निम्नानुसार होगी:

UPDATE Employee SET Salary = 150000 WHERE FirstName=’Kuldeep’ AND LastName = ‘Mishra’; UPDATE Employee SET Salary = 1000 WHERE ID = 1;

जब ये Modified SQL Statement Execute होगा, तो हम देख सकते हैं कि इस बार Execute होने वाली SQL Query का Meaning, उस SQL Query से बिल्कुल भिन्न है, जिसे Originally Execute होना चाहिए था।

यानी पिछली Query के आधार पर जिस Employee का ID 1 है, उसकी Salary को Change करके 20000 करना था। लेकिन इस Modified SQL Query को यदि हम ध्‍यान से देखें तो इसमें अब एक नहीं बल्कि निम्नानुसार दो SQL Queries हैं:

UPDATE Employee SET Salary = 150000 WHERE FirstName=’Kuldeep’ AND LastName =’Mishra’;
UPDATE Employee SET Salary = 1000 WHERE ID = 1;

इसलिए जब इस Modified SQL Statement का पहला SQL Query Execute होगा, तो जिस Employee का FirstName Kuldeep व LastName Mishra है, उस Employee की Salary Update होकर 150000 हो जाएगी, जबकि वास्तव में जिस ID 1 वाले Employee की Salary को Update करके 15000 से 20000 करना था, अगली SQL Query के Execute होते ही उसकी Salary 1000 हो जाएगी।

हम देख सकते हैं कि वास्तव में जिस जरूरत को पूरा करने के लिए String Formatted SQL Query को Frontend Application में Internally Define किया गया था, वह काम न करके उसी SQL Query को Frontend के माध्‍यम से एक और SQL Code Inject करके एकदम विपरीत काम करने के लिए उपयोग में लिया जा सकता है। इस प्रकार से Frontend द्वारा एक नया SQL Statement Insert करके Internal SQL Query को Modify कर देने की प्रक्रिया को SQL Injection Attack के नाम से जाना जाता है।

Parameters का प्रयोग करके हम इस तरह के SQL Injection Attack से अपने Application को सुरिक्षत करने की क्षमता प्राप्त करते हैं। जहां SQL Query के उस हिस्से को Parameters द्वारा Standard तरीके से Handle किया जाता है, जिसमें User Supplied Data Store होता है और इन Parameters को @ के साथ Specified Placeholder द्वारा Access व Manipulate किया जाता है।

क्योंकि इन Placeholders में किस प्रकार का मान Store हो सकता है, इस बात को Programmatically त; कर दिया जाता है। परिणामस्वरूप User किसी भी Placeholder में उसी प्रकार का Data Insert कर सकता है, जिसके लिए उसे Configure किया गया है।

साथ ही बार-बार Change होने वाले Data Values को SQL Statement से हटाकर एक अलग Object में Store करने से SQL Statement के Execute होने की Speed भी तेज हो जाती है, क्योंकि Underlying Data Source हर SQL Statement को Exactly उसी Format में प्राप्त करता है, जिस Format में वह SQL Query उसे प्राप्त होनी चाहिए। यानी उसे किसी प्रकार का String to DataType Conversion नहीं करना पडता।

साथ ही जब SQL Server को Execute होने वाला Statement ऐसे Format में प्राप्त होता है, जिसे String to SQL Statement में Parse करने की जरूरत नहीं होती, तो ऐसी SQL Queries के बार-बार Execute होने की स्थिति में SQL Server बार-बार ऐसी Duplicate SQL Queries को Underlying Database पर Execute नहीं करता, बल्कि पहली बार Execute करने पर जो Resultset Generate होता है, उसी Resultset को तब तक Return करता रहता है, जब तक कि Underlying Database में Stored Resultset में किसी प्रकार का कोई परिवर्तन नहीं होता। जबकि समान प्रकार की SQL Queries को SQL Server स्वयं अपने Cache में Save कर देता है।

परिणामस्वरूप अलग-अलग Parameters के लिए अलग-अलग SQL Statements Execute नहीं होते बल्कि सभी SQL Statements के लिए एक Generic SQL Statement Create होकर Execute होता है, जिसकी वजह से SQL Statements के Execution की Speed भी काफी तेज हो जाती है।

अभी तक हमने जितने भी Examples Create किए हैं, उन सभी में INSERT, UPDATE व DELETE Methods के लिए जिन SQL Queries को Specify किया है, वे सभी Hard-Coded String Format में है। जबकि हम Parameterized Query का प्रयोग करके SQL Parameters को Objects की तरह Treat कर सकते हैं न कि BLOB Text की तरह। SQL Queries को ज्यादा Object Oriented Manner में Treat करके हम विभिन्न प्रकार की Typing Related परेशानियों को कम कर सकते हैं, क्योंकि हम Strongly Typed Properties को Use कर सकते हैं।

साथ ही Parameterized Queries तुलनात्मक रूप से Text Formatted Query की तुलना में ज्यादा तेजी से Execute होते हैं, क्योंकि ये केवल एक ही बार Parse होते हैं, जबकि Text Formatted SQL Queries को Command Object की CommandText Property में बार-बार Assign करना पडता है और हर बार Assigned SQL Query का अलग Execution होता है।

Parameterized Queries को Support करने के लिए ADO.NET Command Object, Individual Parameters Objects का एक Collection Maintain करता है। Default रूप से ये Collection Empty होता है, लेकिन हम इस Collection में जितने चाहें, उतने Parameter Objects Add कर सकते हैं, जो कि SQL Query में एक Placeholder Parameter की तरह Map होते हैं।

जब हम Command Object के Parameters Collection में किसी Member को SQL Query के किसी Parameter से Associate करना चाहते हैं, तब हम SQL Text Parameter को @ Symbol Prefix के साथ Specify करते हैं। जब हम Underlying Data Source के रूप में SQL Server Use करते हैं, तब तो Parameter को Represent करने के लिए Place Holder के साथ हमें @ Symbol को Use करना ही होता है, हालांकि सभी Data Source इस Symbol को Support नहीं करते।

DbParameter Members

इससे पहले कि हम Parameterized Query Create करें, हमें DbParameter Type से Familiar होना जरूरी है, जो कि Data Provider के Parameter Object की Base Class होता है। इस Class की कई Properties होते हैं, जो हमें हमारे Parameter की Name, Size, Data Type Properties को Configure करने की सुविधा Provide करते हैं। इन Properties का Short Description निम्नानुसार है:

DbType or SqlDbType Property

इस Property का प्रयोग करके हम CLR Data Type द्वारा Represented Parameter के Native Data Type को Get/Set कर सकते हैं। उदाहरण के लिए SqlDbType.VarChar नाम का SqlDbType, SQL Server के varchar Type के Column को Map करता है, यानी Parameter को varchar Type का Define कर देता है। परिणामस्वरूप Parameter के रूप में Specified Placeholder में SQL Server के varchar Type का मान ही Store हो सकता है। DbType व SqlDbType दोनों समान Property को Represent करते हैं।

Direction Property

इस Property का प्रयोग करके हम Parameter के Direction को Get/Set कर सकते हैं, जो कि इस बात को Represent करता है कि Parameter को Input-Only, Output-Only, Bidirectional या एक Return Value Parameter में से किस रूप में Set किया गया है।

IsNullable Property

इस Property का प्रयोग करके हम Parameter को Null Values Accept करने के लिए Get/Set कर सकते हैं। यानी इस बात का पता लगा सकते हैं कि Parameter Null Value को Accept कर सकता है या नहीं अथवा Parameter को Null Value Accept करने के लिए Set कर सकते हैं।

ParameterName Property

इस Property का प्रयोग करके हम DbParameter के Name को Get/Set कर सकते हैं। यहां हम जो नाम Specify करते हैं, उसे @ Symbol के साथ ही Specify किया जाता है।

Size Property

इस Property का प्रयोग करके हम Parameter की Size को Bytes के रूप में Get/Set कर सकते हैं। ये Property केवल तब उपयोगी होती है, जब हम Textual Data को Parameter के रूप में Use कर रहे होते हैं। ये Property केवल तभी Data Amount को Affect करता है, जबकि Data केवल Underlying Database पर Pass हो रहा होता है। जबकि यदि Underlying Database फिर से Data Send कर रहा हो, तो ये Property Ignore हो जाता है।

Value and SqlValue Property

इस Property का प्रयोग करके हम Parameter के Value को Get/Set कर सकते हैं। ये Parameter उस Actual Value को Represent करता है, जिसे SQL Statement के Placeholder के स्थान पर Replace किया जाता है। Value का प्रयोग तब करना चाहिए, जब हम .NET Data Types के साथ प्रक्रिया कर रहे होते हैं। जबकि SqlValue Property को तब Use करना चाहिए, जब हमारा Data, SQL Server के Data Types से Match करता हो व System.Data.SqlTypes Namespace द्वारा Represent किया जा रहा हो। 

यदि हम हमारी Library में Define किए गए InsertProduct() Method को ही Parameterized Query को Use करते हुए Modify करना चाहें, तो इस Method को हम निम्नानुसार Modify कर सकते हैं:

public void InsertProduct(
	int ProductID, string ProductName, int SupplierID, int CategoryID, int QuantityPerUnit, 
	float UnitPrice, int UnitsInStock, int UnitsOnOrder, int ReorderLevel, int Discontinued)
    {
    	// Format SQL statement with Parameters.
        string sql = string.Format
		(
			"INSERT INTO Products
			(
				ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, 
				UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
			) 
			VALUES
			(
				@ProductID, @ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, 
				@UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued"
			)
		);
            
            // Execute using our connection.
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                // Fill params collection.
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@ProductID";
                param.Value = ProductID;
                param.SqlDbType = SqlDbType.Int;
                cmd.Parameters.Add(param);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@ProductName";
                param.Value = ProductName;
                param.SqlDbType = SqlDbType.VarChar;
                cmd.Parameters.Add(param);

                ...
                cmd.ExecuteNonQuery();
            }
        }

जिस तरह से हमने इस Modified Code Segment में @ProductID व @ProductName Parameter Placeholders के लिए SqlParameter Type का param नाम का Object Create किया है, उसी तरह से हमें अन्य Parameters के लिए भी Parameter Object Create करना होता है और हर Newly Created Parameter को निम्न Statement के माध्‍यम से Command Object के Parameters Collection में Add() Method का प्रयोग करना होता है:

cmd.Parameters.Add(param);

सभी Parameters को Command Object में Add करने के बाद जब हम Command Object को Execute करते हैं, तो Command Object Execute होते समय सभी Placeholders के स्थान पर इस Parameters Collection में Stored Parameter Objects Replace हो जाते हैं। परिणामस्वरूप Execute होने वाली Query Dynamically Parameters के आधार पर Change होकर Execute होती है।

हालांकि हम देख सकते हैं कि Parameterized Query Use करने पर हमें बहुत सारा Extra Code लिखना पडता है, लेकिन क्योंकि Parameterized Query की Performance ज्यादा अच्छी होती है, इसलिए हम हमारी सुविधानुसार इसे Use कर सकते हैं। Parameterized Query मूल रूप से Stored Procedures को Trigger करने के लिए बहुत उपयोगी साबित होता है।

Implementing Standard SQL Queries

जब हम Parameterized SQL Query Define करना चाहते हैं, तब हमें केवल इस बात का पता लगाना होता है कि हमारी SQL Query के कौनसे Parts को Parameter के रूप में User से Accept करना है। जिन Parts को हमें Frontend के माध्‍यम से या किसी भी अन्य तरीके से User द्वारा Accept करना होता है, उन सभी Parts को Parameter के रूप में Define कर लिया जाता है। जैसे:

UPDATE Employee SET Salary = 20000 WHERE ID = 1;

यदि हम इस SQL Statement को Parameterized SQL Query के रूप में Specify करना चाहें, तो हमारा Parameterized SQL Statement कुछ निम्नानुसार बन सकता है:

UPDATE Employee SET Salary = @NewSalary WHERE ID = @EmployeeID;

Stored Procedures को छोडकर सभी Standard SQL Statements में हम जो नाम Specify करते हैं, उन सभी नामों को @ Sign के साथ एक Placeholder की तरह Use करते हुए Parameterized SQL Query Define किया जा सकता है, जबकि Parameter Names, Case Sensitive नहीं होते। इस तरह से जब हम हमारे SqlCommand Object में ये Parameterized SQL Query Assign करना चाहते हैं, तब हमें निम्नानुसार Statement लिखना होता है:

string sqlText = @”UPDATE Employee SET Salary = @NewSalary WHERE ID = @EmployeeID”;
SqlCommand salaryUpdate = new SqlCommand(sqlText, linkToDB);

SqlCommand Class में Parameters नाम का एक Collection होता है, जिसमें हमें उन Placeholders को Add करना होता है, जिन्हें हमने अपने SqlCommand Object में Specified Parameterized SQL Query में Specify किया होता है।

चूंकि हमारे Current Code Segment में SqlCommand Object salaryUpdate में Assigned SQL Statement में @NewSalary व @EmployeeID नाम के दो Placeholders हैं, इसलिए हमें इन्हीं दोनों Placeholders के लिए Parameter Object Create करना होता है और इन Parameter Objects को SqlCommand Object के Parameters Collection में Add करना होता है।

हर Parameter की कुछ Basic Properties होती हैं, जिन्हें Set करना जरूरी होता है। इन Properties में भी Parameter Name सबसे महत्वपूर्ण Property होता है, जिसमें हम वह नाम Specify करते हैं, जिसे हमने हमारे Parameterized SQL Statement में Placeholder की तरह Set किया होता है। जब हम हमारे Placeholder के Name को Specify करते हैं, तभी हमें उसके DataType को भी Specify करना होता है, ताकि ADO.NET इस बात को निि”चत कर सके कि उस Particulars Parameter में Exactly किस प्रकार का मान Store होना चाहिए। जैसे:

SqlParameter prm = new SqlParameter(“@NewSalary”, “SqlDbType.Money);
prm.Value = 20000m;
salaryUpdate.Parameters.Add(prm);

SqlParameter हमें कई Constructors Provide करता है, जिनका प्रयोग करके हम Appropriate Type का Parameter Object Create कर सकते हैं और उसे Appropriate Command Object में Add कर सकते हैं। जबकि यदि हमारी जरूरतें Simple हों, तो हम SqlCommand.Parameters Collection को अपने Parameter का Type Decide करने के लिए छोड सकते हैं।

जबकि AddWithValue() Method का प्रयोग करके हम हमारे Parameter को Command Object के Collections में Add करने के साथ ही किसी Specific Constant या Variable Value से Assign कर सकते हैं। जैसे:

salaryUpdate.Parameters.AddWithValue(“@NewSalary”, 20000m);
salaryUpdate.Parameters.AddWithValue(“@EmployeeID”, txtEmployeeID.Text);

Parameters को Setup करने के बाद जब हम हमारे Command Object के किसी भी Execute() Method (ExecuteScalar(), ExecuteNonQuery(), etc…) को Call करते हैं, तो उस Command Object में Specified Parameterized SQL Statement विभिन्न Parameters को Use करते हुए एक Non-Parameterized SQL Query में Convert होने के बाद Underling Database पर Execute होता है।

परिणामस्वरूप Underlying Database पर एक Standard SQL Statement Execute होता है, जिसकी वजह से SQL Statement के Execution की Speed भी अधिक होती है और हमारा Application SQL Injection Attack से भी पूरी तरह से सुरिक्षत हो जाता है।

Parameters with Other Data Providers

जब हम SQL Server Use करते हैं, तब बिना किसी परेशानी के हम @ के साथ Placeholders का प्रयोग करके Parameterized Query Create करते हैं। लेकिन जब हम OleDb या ODBC Data Providers Use करते हैं, तब हम @ के साथ Placeholders को Specify नहीं कर सकते, बल्कि इसके स्थान पर ? (Question Mark) Symbol का प्रयोग किया जाता है।

हालांकि OleDbCommand या OdbcCommand Object के Parameters Collection में हमें Parameters को उसी तरह से Specify करना होता है, जिस तरह से SqlCommand Object के लिए किया जाता है। यानी Parameters Create करते समय हमें @ Symbol के साथ ही Placeholder Name को Specify करना होता है। जैसे:

string sqlText = @”UPDATE Employee SET Salary = ? WHERE ID = ?”;
SqlCommand salaryUpdate = new SqlCommand(sqlText, linkToDB);
salaryUpdate.Parameters.AddWithValue(“@NewSalary”, 20000m);
salaryUpdate.Parameters.AddWithValue(“@EmployeeID”, txtEmployeeID.Text);

Executing Stored Procedures

हम जानते हैं कि Stored Procedures वास्तव में SQL Codes का एक Named Block होता है, जो कि Database में ही Stored रहता है। हम हमारी जरूरत के अनुसार एक ऐसा Stored Procedure Create कर सकते हैं, जो कि Scalar Type अथवा Rows का एक Set Return कर सकता है या फिर INSERT, UPDATE या DELETE Operation के लिए भी हम Stored Procedure Create कर सकते हैं। ये Stored Procedures किसी भी संख्‍या में Optional Parameters Accept कर सकते हैं। ये Procedures एक प्रकार से Function की तरह काम करते हैं। अन्तर केवल इतना होता है कि ये Functions Frontend Application में नहीं बल्कि Backend Database में Stored होते हैं।

उदाहरण के लिए यदि हम हमारी Products Table के ProductName Column के Data को Get करने के लिए एक Stored Procedure Create करना चाहें, तो हमारा Stored Procedure कुछ निम्नानुसार हो सकता है:

CREATE PROCEDURE [dbo].[spGetProductName]
@ProductID int,
@ProductName varchar(20) output

AS

  SELECT
@ProductName = ProductName FROM Products WHERE ProductID = @ProductID

जब हम SQL Server को अपने Data Source की तरह Use कर रहे होते हैं, तब अपने Database को Manage करने के लिए हम Microsoft द्वारा Provided Microsoft SQL Server Management Studio Use कर सकते हैं, जो कि Microsoft की Website द्वारा Free Download किया जा सकता है और हम इस Studio को किसी भी SQL Server (Standard, Enterprise, Express, etc…) के साथ Use कर सकते हैं।

इस Studio की मुख्‍य विशेषता ये है कि हम हमारे पूरे Database को इस Studio के माध्‍यम से बिना Frontend Application Create किए हुए Create, Test, Debug व Maintain कर सकते हैं। इसलिए यदि हम हमारा Stored Procedure Create करने के लिए इस Studio को Use करते हैं, तो हम हमारे Stored Procedure को इसी Studio के माध्‍यम से Execute करके उसकी Working को Test भी कर सकते हैं।

जबकि Stored Procedure Create करते समय यदि उसमें किसी तरह का कोई Syntactic Error हो, तो इस Studio द्वारा हम उस Error को Track करके Stored Procedure के Design Time में ही Resolve कर सकते हैं साथ ही Stored Procedure को Underlying Database पर Execute करके Generate होने वाले Resultset को भी इसी Studio में Check कर सकते हैं।

मानलो कि हमने उपरोक्तानुसार spGetProductName नाम का Stored Procedure Create करके अपने SQL Server में Store कर दिया है, जो कि Execute होने पर उस Product का नाम दूसरे Parameter के रूप में Specify किए गए Variable में Return करता है, जिसका ID इस Stored Procedure को Call करते समय पहले Parameter के रूप में Pass किया जाता है।

अपने इस Stored Procedure को Call करने के लिए हम हमारी Library में निम्नानुसार एक अन्तिम Method Add कर सकते हैं, जो कि एक Stored Procedure को Invoke करता है:

        public string LookUpProductName(int prodID)
        {
            string ProductName = string.Empty; 

            // Establish name of stored proc.
            using (SqlCommand cmd = new SqlCommand("spGetProductName", this.sqlCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                // Input param.
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@ProductID";
                param.SqlDbType = SqlDbType.Int;
                param.Value = prodID;

                // The default direction is in fact Input, but to be clear:
                param.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(param);

                // Output param.
                param = new SqlParameter();
                param.ParameterName = "@ProductName";
                param.SqlDbType = SqlDbType.Char;
                param.Size = 20;
                param.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(param);

                // Execute the stored proc.
                cmd.ExecuteNonQuery();

                // Return output param.
                ProductName = (string)cmd.Parameters["@ProductName"].Value;
            }
            return ProductName;
        }

जब हम Stored Procedures को Invoke करना चाहते हैं, तब एक बात हमें विशेष रूप से ध्‍यान में रखनी होती है कि Command Object किसी SQL Statement अथवा Stored Procedure के नाम को ही Default रूप से Represent कर सकता है।

इसलिए जब हमें किसी Stored Procedure को Command Object के माध्‍यम से Invoke करना होता है, तब हमें Command Object Create करते समय Specify किए जाने वाले Constructor में अथवा Command Object Create करने के बाद उसकी CommandText Property में उस Stored Procedure का नाम Specify करना होता है, जिसे हम Execute करना चाहते हैं।

इसीलिए उपरोक्त Method में हमने निम्नानुसार Statement के माध्‍यम से Command Object Create करते समय उसके Constructor के पहले Argument के रूप में अपने spGetProductName नाम के Stored Procedure का नाम Specify किया है:

using (SqlCommand cmd = new SqlCommand(“spGetProductName“, this.sqlCn))

हालांकि जब हम Pure SQL Query को String Format में Specify करते हैं, तब हमें कुछ Extra करने की जरूरत नहीं होती, क्योंकि उस स्थिति में हमारे Command Object की CommandType Property का Default मान CommandType.Text ही होता है।

लेकिन जब हम किसी Stored Procedure को उपरोक्तानुसार Specify करते हैं, तब हमें हमारे Command Object की CommandType Property को CommandType.StoredProcedure Value से Set करना जरूरी होता है और यदि हम ऐसा नहीं करते, तो हमें Runtime Exception प्राप्त होता है, क्योंकि हमारा Command Object, Default Value के आधार पर CommandText Property में एक SQL Statement को Expect करता है।

इसीलिए हमने हमारे उपरोक्त Code में निम्नानुसार Statement के माध्‍यम से Create होने वाले Command Object की CommandType Property को Stored Procedure Value से Set किया है:

cmd.CommandType = CommandType.StoredProcedure;

इसके अलावा Stored Procedures को Use करते समय एक और ध्‍यान में रखने वाली बात ये होती है कि यदि हमारा Stored Procedure किसी तरह का कोई Parameter Accept करता है, तो उस Parameter को हमें हमेंशा Frontend Application में एक Parameter Object के माध्‍यम से ही Represent करता होता है।

उदाहरण के लिए हमारा spGetProductName नाम का Stored Procedure एक Input Type का Parameter Accept करता है, जबकि दूसरा Output Type का Parameter है। इसलिए इस Stored Procedure को Execute करने के लिए हमें इसके दोनों Parameters को भी Specify करना होता है। इसीलिए इस Method के Code में हमने हमारे Command Object में निम्नानुसार दो Parameter Objects को Add करने के लिए Code लिखा है:

                // Input param.
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@ProductID";
                param.SqlDbType = SqlDbType.Int;
                param.Value = prodID;

                // The default direction is in fact Input, but to be clear:
                param.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(param);

                // Output param.
                param = new SqlParameter();
                param.ParameterName = "@ProductName";
                param.SqlDbType = SqlDbType.Char;
                param.Size = 20;
                param.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(param);

हम देख सकते हैं कि पहला Parameter एक Input Type का Parameter है, जो कि Stored Procedure में किसी Value को Pass करने का काम करता है और क्योंकि किसी भी Stored Procedure के Parameters का Default Direction Input ही होता है, इसलिए, यदि हम चाहें तो उपरोक्त Code में से निम्नानुसार Specify किए गए Statement को Remove भी कर सकते हैं:

param.Direction = ParameterDirection.Input;

हालांकि यदि हम इस Statement को Specify करते हैं, तो Program की Working पर कोई प्रभाव नहीं पडता। लेकिन जब हम दूसरा Parameter Specify करते हैं, तो हमें Parameter Direction को Compulsory रूप से Specify करना जरूरी होता है, क्योंकि Stored Procedure इस दूसरे Parameter के रूप में Specify किए गए Variable में जो Value Store करता है, उस Value को फिर से Frontend Application द्वारा Access किया जाता है।

यानी दूसरा Parameter एक प्रकार से Frontend Application के लिए Output या Return Value की तरह काम करता है। साथ ही हम ये भी देख सकते हैं कि निम्नानुसार Statement द्वारा जो ParameterName Specify किया गया है:

param.ParameterName = “@ProductID“;
. . .
param.ParameterName = “@ProductName“;

ये वही Parameter Names हैं, जिन्हें हमने हमारे spGetProductName नाम के Stored Procedure में निम्नानुसार Specify किया है:

CREATE PROCEDURE [dbo].[spGetProductName]
@ProductID int,
@ProductName varchar(20) output
AS
SELECT
@ProductName = ProductName FROM Products WHERE ProductID = @ProductID

साथ ही हम जो Parameter Specify करते हैं, उनका Stored Procedure में Specify किए गए Parameters के Types के समान होना जरूरी होता है। इसीलिए हमने दोनों Parameters के Type को निम्नानुसार Specify किया है:

param.SqlDbType = SqlDbType.Int;
. . .
param.SqlDbType = SqlDbType.Char;

हमने @ProductID Parameter का SqlDbType Integer Type का इसलिए रखा है, क्योंकि हमने हमारे Stored Procedure में इस Parameter को int Type का रखा है।

इसी तरह से दूसरे Parameter यानी @ProductName का SqlDbType Character Type का इसलिए रखा है, क्योंकि हमने हमारे Stored Procedure में इस दूसरे Parameter को varchar Type का रखा है, जो कि SQL Server Database में Variable Length Character String को ही Represent करता है।

इस तरह से Stored Procedure के विभिन्न Parameters को Define करके Command Object की Parameters नाम की Collection Property में Add करने के बाद अन्त में निम्नानुसार Statement द्वारा ExecuteNonQuery() Method को Execute किया है:

// Execute the stored proc.
cmd.ExecuteNonQuery();

परिणामस्वरूप जैसे ही हमारा Command Execute होता है, Underlying Database पर Stored spGetProductName नाम का Stored Procedure Execute हो जाता है और इस Stored Procedure के Execute होते ही, @ProductName नाम के Parameter में उस Product का नाम Store हो जाता है, जिसका ID इस Stored Procedure के First Parameter में Pass किया गया होता है।

इसलिए इस Stored Procedure द्वारा Return होने वाले मान को @ProductName नाम के Parameter से Retrieve करने के लिए अन्त में हमने निम्नानुसार Statement Use किया है:

// Return output param.
ProductName = (string)cmd.Parameters[“@ProductName”].Value;

जैसे ही ये Statement Execute होता है, Command Object की Parameters नाम की Collection Property में Stored @ProductName नाम के Parameter की Value एक String के रूप में Convert होकर ProductName नाम के String Variable में Store हो जाता है, जिसे Method के अन्त में Return कर दिया जाता है।

इस प्रकार से हम समझ सकते हैं कि यदि हम केवल Products Table के लिए Common रूप से Use होने वाले Methods की Library Create करते हैं, तो इस Chapter में अभी तक Create किए गए सभी Methods इस एक Product Table को पूरी तरह से Access व Manipulate करने से सम्बंधित Library के Methods होंगे। जबकि यदि हमारे Database में 10 Tables हों, तो हर Table के लिए हमें उपरोक्तानुसार सभी Methods अलग-अलग Create करने होंगे, तभी हमारी Library Complete होगी।

हालांकि यदि हम Microsoft द्वारा Develop किए गए Entity Framework को Use करते हैं, तो हमें ये Library Create करने की जरूरत नहीं होती, क्योंकि Entity Framework द्वारा ये काम Automatically कर दिया जाता है और हम हमारे Database की सभी Tables को Strongly Typed Object के रूप में Access व Manipulate करने में ठीक उसी तरह से सक्षम हो जाते हैं, जिस तरह से इस प्रकार की Library Create करने के बाद होते हैं।