Difference b/w Stored Procedure and UDF

1. You can use SELECT statement with UDF, while either EXEC or Exec can be used with SP.
2. A UDF can return a Table variable, while with SP you can create a table but can't return a table variable.
3. You can write Update, Delete statements within the SP, but UDF does not allow this.
4. You can use UDF in a JOIN, but SP can not be used.
5. You can write your queries within the transaction in SP but UDF does not allow you to write the queries within the transactions.

Interview Preparation :)

GC.SupressFinalize:
It requests the GC not to call the Finalize method for the specified object. This method takes a single parameter: the pointer to an object. It sets a bit in the object header that GC checks when call the Finalizer method. When this flag is on, the runtime knows not to move this object's pointer to the f-reachable queue, preventing the object's Finalize method from being called. You can call this method within the Dispose method of IDisposable interface and prevent GC to call the Finalize method on the object that does not require it.
Syntax:
Public static void SupressFinalize (Object obj)
Note: If obj is null, it throws the NULL exception

GC.ReRegisterForFinalize:
This method is used to call the finalize method for the object again. This method takes a single parameter: the pointer to an object. When this method is called, it appends the address of the object to the end of the finalization queue. This method is usually used in case of resurrection, when the object’s finalized has been called once but the user wants to call the finalize method again.
Syntax:
Public static void ReRegisterForFinalize (Object obj)

Difference b/w Finalize and Dispose:
Both Finalize and Dispose methods are used by the GC to free the garbage objects and reclaim their memory, still there are some differences in both these methods:
Finalize is an implicit resource cleanup method, used by GC to collect the memory used by the managed as well as unmanaged resources. On the other hand, Dispose is an explicit resource cleanup method that is used by GC to reclaim the memory of both managed as well as unmanaged objects.
The exact time when the Finalize method will run is un-predictable, while the use of Dispose method is totally within the hands of the developer. When and where the developer wants to reclaim the memory of an object there he can use the Dispose method.
The use of Finalize method in the object’s type (i.e. class) is a performance hit as two GC runs are required to reclaim the memory of that object, whereas in calling Dispose there is no such performance hit as you can avoid the call to finalize method (if implemented) by using the GC.SupressFinalize.
The order in which the Finalize will be executed is not predictable. If a finalized object A has reference to another finalized object B, then it might be possible that Finalize for object B gets executed before the object A gets finalized, which may leads to unpredictable results. Since, the use of Dispose is totally within the hands of developer so no such problem exists with Dispose method.

Difference b/w Close and Dispose:
Both Close and Dispose methods are used by the GC to free the garbage objects and reclaim their memory. Close can be used to those objects which can be reopened and reused after they are closed, while Dispose is used to clean up those objects which are no longer required after they have disposed.

www.free-ebooks-download.org
Website to download latest free e-books on C#, vb.net, asp.net, 2.0, ado.net, sql 2005, web services, ajax, .net framework, compact framework, xml, sharepoint, exchange server, seo, crystal reports and more.. All free ebooks download, Hurry!

Article on Garbage Collector -- Good Read

A beautiful article on GC by Abhinaba Basu is explained thoroughly at his blog

http://blogs.msdn.com/abhinaba/

Key-Value Functionality in Combo Box

Combo box control is a control which displays a list of items. It combines the features of a text box and a list box. For the item which is selected in the combo box, we can get the selected index, which is in order of 0, 1, 2..

Today, we will learn how we can assign a key value pair to a combo box. The value will be shown to the end user and on the basis of the value, you can get the key.

E.g.: Consider the following key, value pairs:

Key: 1, Value: Rohit
Key: 2, Value: Suraj

Now, the combo box will show the values to the end user as Rohit, Suraj and for the selected item, you can get the key as either 1 or 2.

It is a simple three step procedure and am explaining it with some code samples.

Step 1: Create a new public class, say, KeyValuePair as

   public class KeyValuePair
   {
       public object m_objKey;
       public string m_strValue;
      
       public KeyValuePair(object NewKey,string NewValue)  
       {
           m_objKey = NewKey;
           m_strValue = NewValue;
       }  
      
       public override string ToString()  
       {
           return m_strValue;
       }       
   }

Step 2: From your Main class, add the items to combo box as

      comboBox1.Items.Add(new KeyValuePair("1", "Rohit"));
     comboBox1.Items.Add(new KeyValuePair("2", "Suraj"));
     comboBox1.Items.Add(new KeyValuePair("3", "Sandeep"));
     comboBox1.Items.Add(new KeyValuePair("4", "Jagdeep"));
     comboBox1.SelectedIndex = 0;

Step 3: Now you can fetch the key-value pair as

          KeyValuePair objKeyValuePair = (KeyValuePair)comboBox1.SelectedItem;
         string strkey = objKeyValuePair.m_objKey.ToString();
         string strvalue = objKeyValuePair.m_strValue.ToString();
         MessageBox.Show("Key: " + strkey + " || Value: " + strvalue);

That’s it. Put the code written in step 3 on some button click event, say button1Click_event. Now, whenever you will click the button, a message box will come that show you the Key and Value of the item which is currenlt selected in the combo box.

If you find any kind of issue/query, please do let me know.


Haapy coding
-Rohit

Computed Column in Sql Server

In this short article, we will discuss how can we alter a computed column:

If you have columns in your sql database tables such that their values depend on other columns, you may use computed columns. Using computed columns or calculated columns will enable you to save the calculation logic of such computed values in database and will save you from extra coding everytime you have to do when you require the computed item.

A computed column can be defined as an expression which can use other columns in the same table as well as using other table values via user-defined functions.

Suppose we have the table schema as:

Create Table Example

(

Col1 Decimal(10, 2),

Col2 Decimal(10, 2),

Col3 Decimal(10, 2),

Col4 Decimal(10, 2),

Col5 Decimal(10, 2),

Col6 Decimal(10, 2),

Col7 Decimal(10, 2),

ComputedCol1 As (Col1 + Col2 - Col3),

ComputedCol2 As (Col4 + Col5 - Col6)

)

Now if you want to alter the computed column (say, ComputedCol1), you can not do it direclty by using ALTER command. Instead, you have to drop the column first and then add the column again with new computed value, as:

--Drop the column First

Alter Table Example

Drop Column ComputedCol2

--Now add the column again with new computed value

Alter Table Example

Add [ComputedCol2] As (Col5 + Col6 - Col7) 

You can add a computed column where a CASE expression is used in the definition of the computed column value as,

Alter Table Example

Add [ComputedCol3] As (Case When Col7 = 0 Then (Col1 + Col2 - Col3) Else (Col4 + Col5 - Col6) End) 

However, you can not use a computed column as a condition expression in the definition of other computed column as,

Alter Table Example

Add [ComputedCol4] As (Case When ComputedCol1 = 0 Then 0 Else (Col5 + Col6 - Col7) End) 

/*

--Sql server will throw the following error:

Msg 1759, Level 16, State 0, Line 1

Computed column 'ComputedCol1' in table 'Example' is not allowed to be used in another computed-column definition.

*/

So to resolve this use the computed column's calculated values as condition expression as,

Alter Table Example

Add [ComputedCol4] As (Case When (Col1 + Col2 - Col3) = 0 Then 0 Else (Col5 + Col6 - Col7) End) 

Regards

Rohit