Handle multiple records in SQL server parameters

2 Comments | Feb 18, 2010
Last week one of my colleague asked me that how we can send multiple records to SQL server procedure parameters. Let us discuss the scenario:-
Application user has selected multiple product and now you want to send Customer details and corresponding order details data to SQL Server.
To resolve this Microsoft has provided multiple approaches  in SQL Server like Table and XML Parameter. I read somewhere that  if you want to be a architect, try to solve each software problem with xml framework.
Let us play with XML:
DECLARE @idoc int
DECLARE @doc xml
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
--In OPENXML the flag value is set to 1, indicating attribute-centric mapping
--In OPENXML the flags set to 2, indicating element-centric mapping
--In OPENXML the flags set to 3, indicating text-centric mapping
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
         WITH (OrderID       int         '../@OrderID',
               CustomerID varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity')
 
EXEC sp_xml_removedocument @idoc --don't forget to remove the document
 
 
The above example will give you a table resultset (OrderID,CustomerID,OrderDate,ProductID,Quantity) and you can process the resultset  in desired output by using #temp table.
 
How it works:
 
The above example creates an internal representation of the XML image by using sp_xml_preparedocument.
 
 
 
 
 
First, sp_xml_preparedocument is called to obtain a document handle. This document handle is passed to OPENXML. A SELECT statement that uses an OPENXML rowset provider is then executed against the internal representation of the XML document.
 
OPENXML Parameters include the following:
An XML document handle (idoc)
An XPath expression to identify the nodes to be mapped to rows (rowpattern)
A description of the rowset to be generated
Mapping between the rowset columns and the XML nodes
 
Hope this artical will help those who has similar problem in sending multiple records to SQL Server. Read more on Open XML on MSDN
 
Reference :MSDN and SQL Server 2005 Book Online
 
 

Destructors in .Net

One Comment | Jan 19, 2010

Destructors in .Net

  • Destructors cannot be defined in structs. They are only used with classes.
  • A class can only have one destructor.
  • Destructors cannot be inherited or overloaded.
  • Destructors cannot be called. They are invoked automatically.
  • A destructor does not take modifiers or have parameters.

class Student
{
    ~ Student()  // destructor
    {
        // cleanup statements...
    }
}


The destructor implicitly calls Finalize on the object's base class. Therefore, the preceding destructor code is implicitly translated to:


protected override void Finalize()
{
    try
    {
        // cleanup statements...
    }
    finally
    {
        base.Finalize();
    }
}


This means the Finalize method is called recursively for all of the instances in the inheritance chain, from the most-derived to the least-derived.


Empty destructors should not be used. When a class contains a destructor, an entry is created in the Finalize queue. When the destructor is called, the garbage collector is invoked to process the queue. If the destructor is empty, this simply results in a needless loss of performance.

The programmer has no control over when the destructor is called because this is determined by the garbage collector. The garbage collector checks for objects that are no longer being used by the application. If it considers an object eligible for destruction, it calls the destructor (if any) and reclaims the memory used to store the object. Destructors are also called when the program exits.

It is possible to force garbage collection by calling Collect, but in most cases, this should be avoided because it may result in performance issues


Using Destructors to Release Resources


In general, C# does not require as much memory management as is needed when developing with a language that does not target a runtime with garbage collection. This is because the .NET Framework garbage collector implicitly manages the allocation and release of memory for your objects. However, when your application encapsulates unmanaged resources such as windows, files, and network connections, you should use destructors to free those resources. When the object is eligible for destruction, the garbage collector runs the object's Finalize method.

Explicit Release of Resources


If your application is using an expensive external resource, it is also recommended that you provide a way to explicitly release the resource before the garbage collector frees the object. You do this by implementing a Dispose method from the IDisposable interface that performs the necessary cleanup for the object. This can considerably improve the performance of the application. Even with this explicit control over resources, the destructor becomes a safeguard to clean up resources if the call to the Dispose method failed.


A Dispose method should call the GC.SuppressFinalize method for the object it is disposing. If the object is currently on the finalization queue, GC.SuppressFinalize prevents its Finalize method from being called. Remember that executing a Finalize method is costly to performance. If your Dispose method has already done the work to clean up the object, then it is not necessary for the garbage collector to call the object's Finalize method.


Dispose(bool disposing) executes in two distinct scenarios. If disposing equals true, the method has been called directly or indirectly by a user's code and managed and unmanaged resources can be disposed. If disposing equals false, the method has been called by the runtime from inside the finalizer and only unmanaged resources can be disposed. When an object is executing its finalization code, it should not reference other objects, because finalizers do not execute in any particular order. If an executing finalizer references another object that has already been finalized, the executing finalizer will fail.


If you want the garbage collector to perform cleanup operations on your object before it reclaims the object's memory, you must override the Finalize method in your class.

The garbage collector keeps track of objects that have Finalize methods, using an internal structure called the finalization queue. Each time your application creates an object that has a Finalize method, the garbage collector places an entry in the finalization queue that points to that object. The finalization queue contains entries for all the objects in the managed heap that need to have their finalization code called before the garbage collector can reclaim their memory.

A Finalize method should not throw exceptions, because they cannot be handled by the application and can cause the application to terminate.

Implementing Finalize methods or destructors can have a negative impact on performance and you should avoid using them unnecessarily. Reclaiming the memory used by objects with Finalize methods requires at least two garbage collections. When the garbage collector performs a collection, it reclaims the memory for inaccessible objects without finalizers. At this time, it cannot collect the inaccessible objects that do have finalizers. Instead, it removes the entries for these objects from the finalization queue and places them in a list of objects marked as ready for finalization. Entries in this list point to the objects in the managed heap that are ready to have their finalization code called. The garbage collector calls the Finalize methods for the objects in this list and then removes the entries from the list. A future garbage collection will determine that the finalized objects are truly garbage because they are no longer pointed to by entries in the list of objects marked as ready for finalization. In this future garbage collection, the objects' memory is actually reclaimed.

There is no way to call finalize method using code in C#.

 

 

Reference: MSDN