Creating Transaction Application – ADO.NET

Transaction Database Application Create करने के लिए Follow किए जाने वाले Steps का एक निश्चित क्रम होता है, जिसे निश्चित रूप से Follow करना जरूरी होता है। ये क्रम कुछ निम्नानुसार होता है:

• सबसे पहले हमें Connection Object के लिए Open() Method का प्रयोग करके Underlying Database पर Connection Open करना होता है।
• फिर Connection Object पर BeginTransaction() Method का प्रयोग करके Transaction की शुरूआत करनी होती है। ये Method हमें एक Transaction Object Provide करता है, जिसे जरूरत के अनुसार बाद में Transaction को Rollback अथवा Commit करने के लिए Use किया जाता है। यहां ध्‍यान देने वाली बात ये है कि BeginTransaction() Method को Call करने से पहले Underlying Database में SQL Queries के माध्‍यम से जो Changes Perform किए गए होते हैं, वे सभी Changes इस Method के Execute होने से पहले Permanently Commit हो जाते हैं।
• अब दूसरे Step में Create किए गए Transaction Object को एक नया Command Object Create करके उसकी Transaction Property में Assign करना होता है।
• फिर Command Object के माध्‍यम से SQL Commands को Execute करना होता है। जब तक Command Object की Transaction Property में एक Valid Transaction Object Assigned होता है, तब तक हम एक या एक से अधिक Command Objects को SQL Commands Execute करने के लिए Use कर सकते हैं।
• सभी SQL Commands को Command Object के माध्‍यम से Execute करने के बाद हमें Transaction Object के लिए Commit या Rollback Method को Use करके Transaction को Commit या Rollback करना होता है।
• अन्त में Transaction के Successful या Fail होने के बाद Connection को Close करना होता है।

ध्‍यान देने वाली बात ये है कि जब एक बार किसी Connection पर Transaction Execute होना शुरू हो जाता है, उसके बाद जितने भी SQL Statements उस Connection को Use करते हुए Execute होना चाहते हैं, उन सभी Statements का Transaction का हिस्सा होना जरूरी होता है।

जैसे यदि हम Underlying Database पर दो INSERT Queries व एक DELETE Query Fire करना चाहते हैं, तो ये तीनों ही Queries हमारे Transaction का हिस्सा बनते हुए Execute होंगे। जबकि यदि हम Transaction का प्रयोग किए बिना SELECT Statement Execute करते हैं, तो हमें एक Exception प्राप्त होता है, जो इस बात को Indicate करता है कि Transaction अभी भी Pending है।

क्योंकि एक Connection Object पर एक समय में केवल एक ही Pending Transaction हो सकता है और जब हम Running Transaction में SELECT Statement Fire करते हैं, जो कि Transaction से Associated नहीं होता, तो एक ही Connection Object पर एक ही समय पर दो Pending Transactions Perform होने लगते हैं। परिणामस्वरूप ADO.NET हमें Error Return करता है।

अन्य शब्दों में कहें तो जब एक बार हम BeginTransaction() Method को Call कर लेते हैं, उसके बाद हम तब तक उसी Connection Object पर किसी अन्य Operation को स्वतं= रूप से Perform नहीं कर सकते अथवा तब तक फिर से BeginTransaction() Method को Call नहीं कर सकते, जब तक कि हम उस Transaction को Commit या Rollback नहीं कर देते।

इसलिए समान Connection Object के लिए Parallel Transaction Perform करने पर Error Trigger होता है। इस प्रकार की Error से बचने के लिए हम एक दूसरा Connection Open कर सकते हैं और अपनी Query को उस Newly Opened Connection Object पर Execute कर सकते हैं।

SQL Server में MARS नाम का एक Special Feature भी Provide किया जाता है, जो कि एक Single Connection पर एक से ज्यादा यानी Multiple Active Resultset को Maintain करने की सुविधा Provide करता है। हालांकि ये भी Multiple Transactions को Maintain करने की सुविधा Provide नहीं करता।

Implementing Transactions

Transactional Application की Working को बेहतर तरीके से समझने के लिए हम अग्रानुसार एक Console Application Create कर सकते हैं, जो कि ADO.NET का प्रयोग करते हुए Non-Distributed Single Database Transaction को Demonstrate कर रहा है।

इस Application में हम Northwind Database के Customers, Products, Orders व Order Details Tables को Use कर रहे हैं। जहां Customers Table में किसी Enterprise के सभी Customers की Information है, Products Table में Enterprise द्वारा Sell किए जाने वाले सभी Products की Information है।

चूंकि एक Customer समान Enterprise पर समान या अलग-अलग Products के लिए कई Orders Place कर सकता है, इसलिए Customers व Orders के बीच One-to-Many की Relationship है।

इसी तरह से एक Order में एक से ज्यादा Products का Order हो सकता है, जबकि एक ही प्रकार का Product एक से ज्यादा Customers द्वारा Place किए गए Order में हो सकता है। इसलिए Orders व Products के बीच Many-to-Many की Relationship है।

इन विभिन्न Tables के बीच की Relationship को Represent करने के लिए हम Visual Studio के Strongly Typed DataSet Designer को उपयोग में ले सकते हैं, जिसमें हमारी इन तीनों Tables के बीच Relationship कुछ निम्न चित्रानुसार Reflect होती है:

यदि हम इस Designer का प्रयोग करते हुए ही Products Table के Records को देखना चाहें, तो Products DataTable पर Right Click करने पर Display होने वाले Popup Menu से “Preview Data…” Option को Click करने पर हमें निम्न चित्रानुसार Products Table में Stored सभी Products दिखाई देते हैं:

जैसाकि इस चित्र में Highlight की गई Row में हम देख सकते हैं कि “Chai” नाम का Product का कुछ Stock 39 है। अब यदि किसी Customer द्वारा किसी Order में इस Product के 4 Unit का Order Place किया जाए, तो Order को Fulfill करने के Response में Products Table का Stock Reduce होकर 35 हो जाना चाहिए।

साथ ही जब Order को Fulfill किया जाता है, उस दिन की Date को Order Table के ShippedDate Column में Specify करना भी जरूरी होता है, ताकि इस बात का Record रखा जा सके कि Place किए गए Order को किस Date को Fulfill किया गया है।

हम समझ सकते हैं कि यदि Order को Fulfill किया जाता है, तो ही Orders Table की ShippedDate को Update करना है साथ ही Products Table के UnitsInStock Column के मान को भी 39 से Reduce करके 35 करना है।

यानी यदि Order को Fulfill किया जाता है, तो Products व Orders दोनों ही Tables के Records एक साथ Update होने चाहिए अथवा किसी भी Table का Record Update नहीं होना चाहिए। Products Table के Record को Update करने के लिए हमें निम्न SQL Statement लिखने की जरूरत पडती है:

UPDATE Products SET UnitsInStock = 35 WHERE ProductID = 1

इसी तरह से Orders Table के Record को Update करने के लिए हमें निम्नानुसार SQL Statement Execute करने की जरूरत पड सकती है:

UPDATE Orders SET ShippedDate = “10-Jun-2013” WHERE OrderID = 1

इन दोनों Statements को एक Single Unit के रूप में Execute करने का एक तरीका ये है कि हम इसे Database Level पर एक Transaction के रूप में Specify कर दें और ऐसा करने के लिए हम Database Level पर निम्नानुसार एक Transaction Create कर सकते हैं:

        BEGIN Transaction
                UPDATE Products SET UnitsInStock = 35 WHERE ProductID = 1
                UPDATE Orders SET ShippedDate = "10-Jun-2013" WHERE OrderID = 1
        COMMIT

इस Transaction को हम एक Single Inline SQL Statement के रूप में Wrap कर सकते हैं या फिर हम हमारी सुविधानुसार Stored Procedure Create करके उस Stored Procedure को Call कर सकते हैं। इस Approach को Use करने की एक परेशानी ये है कि इस प्रकार के Code को हम किसी भी तरह से ADO.NET के माध्‍यम से Frontend द्वारा Insert या Update Operation के दौरान Control नहीं कर सकते, जबकि इस प्रकार के Control की जरूरत उस समय हमें बहुत ज्यादा होती है, जब हम Disconnected DataSet Object को Use कर रहे होते हैं।

इसलिए हम ADO.NET Object द्वारा Provide किए जाने वाले Transaction Support को Use कर सकते हैं। क्योंकि जब हम ADO.NET Object द्वारा Provided Transaction Support को Use करते हैं, तो हम हमारी जरूरत के अनुसार Transaction को जिस तरह से चाहें उस तरह से Control कर सकते हैं। ADO.NET का प्रयोग करते हुए यदि हम Transaction Support को Use करना चाहें, तो हमारा Code कुछ निम्नानुसार हो सकता है:

	static void Main(string[] args)
	{
		using( SqlConnection con = new SqlConnection(conString))
		{
			SqlCommand com = con.CreateCommand();
			SqlTransaction tran = null;

			try
			{
				con.Open();
				tran = con.BeginTransaction();
				com.Transaction = tran;

				com.CommandText = "UPDATE Products SET UnitsInStock = 35 WHERE ProductID = 1";
				com.ExecuteNonQuery();

				com.CommandText = " UPDATE Orders SET ShippedDate = "10-Jun-2013" WHERE OrderID = 1";
				com.ExecuteNonQuery();
	
				tran.Commit();
			}
			catch(System.Exception)
			{
				tran.Rollback();
				throw ex;
			}
			finally
			{
				con.Close();
			}
		}
	}

जैसाकि इस Code द्वारा हम समझ सकते हैं कि इस तरीके से ADO.NET के Transaction Support का प्रयोग करते हुए ज्यादा बेहतर तरीके से Transaction को Control कर सकते हैं। साथ ही ADO.NET के Transaction Support को Use करने का ये एक Professional Approach है, जिसमें try Block के बीच Transaction को Enclose किया गया है जबकि Connection को Close करने का काम finally Block में किया गया है और किसी भी तरह की Error की स्थिति में catch Block में Transaction को Rollback किया गया है।

चलिए, एक और Example Code देखते हैं। इस Example Code में भी Connection Object के साथ BeginTransaction() Method का प्रयोग करके ही Transaction को शुरू किया गया है। लेकिन इस Code में BeginTransaction() Method एक Transaction Object Return करता है, जिसे एक SqlTransaction Type के myTransaction नाम के Variable में Hold किया गया है, ताकि जरूरत पडने पर इस Object को फिर से Refer किया जा सके। बाद में इसी Transaction Object को SqlCommand Object की Transaction Property esa Assign किया गया है। जैसे:

myConnection.Open();
myTransaction = myConnection.BeginTransaction();
myCommand.Transaction = myTransaction;

जब उपरोक्तानुसार एक बार Transaction को Setup कर दिया जाता है, उसके बाद हमें केवल Command Object को Appropriate SQL Queries से Fill करके Command Object को Execute करना होता है। जैसे:

myConnection.Open();
myTransaction = myConnection.BeginTransaction();

myCommand.Transaction = myTransaction;
myCommand.CommandText = “UPDATE Products SET UnitsInStock = 35 WHERE ProductID = 1″;
myCommand.ExecuteNonQuery();

myCommand.CommandText =  “ UPDATE Orders SET ShippedDate = “10-Jun-2013” WHERE OrderID = 1″;
myCommand.ExecuteNonQuery();

विभिन्न प्रकार के Different SQL Statements को Execute करने के लिए हम समान Command Object की CommandText Property को उपरोक्तानुसार Setup कर सकते हैं, जहां हमने एक ही myCommand नाम के Object की CommandText Property को दो बार अलग-अलग SQL Query से Setup किया है।

इन Codes को भी हम पिछले Example की तरह ही try…catch Block के रूप में Setup करके Execute कर सकते हैं, ताकि जरूरत के अनुसार Commit या Rollback Methods को Use कर सकें तथा Connection को Close कर सकें। जैसे:

	catch(System.Exception)
	{
		myTransaction.Rollback();
	}
	finally
	{
		myTransaction.Commit();
		myConnection.Close();
	}

जैसाकि उपरोक्त Code Segment द्वारा हम समझ सकते हैं कि हमम एक ही Command Object में जरूरत के अनुसार एक से ज्यादा SQL Commands को ACID Rules को Follow करने वाले Execution Unit के बीच Enclose कर सकते हैं।

हालांकि यदि हम उपरोक्तानुसार Code Use करते हैं और Rollback() Method को Use करना भूल जाते हैं, तब भी .NET Framework स्वयं अपने स्तर पर Rollback() Method को Call कर लेता है। लेकिन इसका मतलब ये नहीं है कि हम हमारे Code में Rollback() Method को Use ही न करें।

क्योंकि .NET Framework किसी Error की स्थिति में तुरन्त Rollback() Method को Call नहीं करता, बल्कि Resources को जरूरत से ज्यादा समय तक Busy रखता है। क्योंकि जब हम Rollback() Method को Use नहीं करते, तब .NET Framework Rollback() Method को तब Call करता है, जब Underlying Database पर Connection Close हो रहा होता है।

जबकि यदि Connection Pooling Enabled हो, तो Rollback() Method को .NET Framework द्वारा तब Call किया जाता है, जब Connection Pool से Physical Connection Cut होता है अथवा जब समान Connection को किसी अन्य SqlConnection Instance द्वारा Reuse किया जाता है। इसलिए किसी भी स्थिति में हमें हमेंशा Rollback() Method को उपयुक्त स्थान पर finally या catch Block में जरूर Use कर लेना चाहिए।

Transactional Code Update व Non-Transactional Code Update दोनों के बारे में कुछ विशेष रूप से ध्‍यान देने वाली बातें हैं। सबसे मुख्‍य बात तो यही है कि हमें Database पर Connection को Command Execution से कुछ Statements पहले Open करना पडता है। क्योंकि वास्तव में हम BeginTransaction() को Open() Method का प्रयोग करने से पहले Call ही नहीं कर सकते।

परिणामस्वरूप हमारा Transaction एक Physical Connection के साथ Bound हो जाता है और जरूरत से ज्यादा समय के लिए Connection Open रहता है। जिसका Negative Impact Database के Connection Pooling Environment पर पडता है।

जिसकी वजह से Database की Performance भी प्रभावित होती है क्योंकि उसे ज्यादा समय तक Opened Connection के साथ काम करना पडता है और एक Multiuser Environment में किसी भी Database के लिए ये एक खराब स्थिति होती है क्योंकि Multiple Requests को Fulfill करने के लिए उसे Multiple Physical Connections Open करने पडते हैं।

हालांकि SQL Server जैसे Database पर Execute होने वाले Statements Auto Commit Mode में Execute होते हैं। इसलिए Underlying Database पर Execute होने वाला हर Statement एक Implicit Transaction के रूप में ही Execute होता है। इसलिए यदि हमें Underlying Database पर बहुत सारे Rows Insert करने हों, तो हर Row को Insert करने वाला SQL Statement स्वयं एक Implicit Transaction के Form में होता है।

इसलिए यदि हम स्वयं BEGIN TRANSACTION Block Create नहीं करते, तो इस तरीके से Auto Commit Mode में Insert होने वाले बहुत सारे Rows Database की Performance को काफी प्रभावित करते हैं। जबकि Manual Transaction Create करने पर जितने भी Changes Underlying Database पर Perform होने होते हैं, वे सभी Changes एक Temporary Log File में Write होते हैं और Commit करने पर इस Temporary File में Stored सभी Updates बहुत ही Efficient तरीके से Underlying Database में Permanently Save हो जाते हैं।

इसलिए हम समझ सकते हैं कि Transactions को Use करने का सबसे पहला Impact तो Database की Performance पर ही पडता है। जहां जब कम Statements Execute करने होते हैं, तब Transactions को Use करने पर Database पर Negative Impact पडता है। लेकिन जब बहुत सारे Statements को एक Batch के रूप में Execute करना होता है, तब Transactions का प्रयोग करने पर Database की Performance तुलनात्मक रूप से देखें, तो Improve होती है।

इसलिए यदि कम SQL Statements का Execution करना हो, तो Transaction के स्थान पर Simple Statement का Execution करना ही बेहतर होता है। लेकिन यदि बहुत सारे SQL Statements Execute करने हों, तो उस स्थिति में Transaction का उपयोग जरूर करना चाहिए। साथ ही Transactions को Use करने पर हमें ACID Features भी प्राप्त होते हैं, जो कि हमारे Application की Working को ज्यादा Reliable बनाते हैं।