How do I get the IDENTITY / AUTONUMBER value for the row I inserted?

To get the indentity value of table we can use any of these:

1) INDENT_CURRENT : - IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
 
SELECT IDENT_CURRENT(’tablename’):- It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

2) @@IDENTITY :- @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
       
SELECT @@IDENTITY :- It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.



3) SCOPE_IDENTITY :-  SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

SELECT SCOPE_IDENTITY() :- It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.


Examaple of each:

Create Table #Table
(
    Id int identity(1,1),
    [name] varchar(10)
)
insert into #Table
select 'P'

insert into #Table
select 'S'

insert into #Table
select 'G'

insert into #Table
select 'PP'


select * From #Table

Select  @@Identity As InsertId,IDENT_CURRENT('#Table') As InsertId,SCOPE_IDENTITY() As InsertId

Drop table #Table


------------------------------------------
Note : To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Basic Differences in .Net

1. Difference between == and .Equals Method?
2. Difference between Close() and Dispose() Method
3. Difference between Copy and Clone Method
4. Value Type Vs Reference Type
5. Struct Vs Class
6. Static Member, Static Method, Static Constructor
7. Difference between ADO and ADO.net Technology
8. Difference between ADO.net Dataset and ADO Recordset
9. Localization Vs Globalization
10. Difference Between String and StringBuilder
11. Response.Redirect(url,true) Vs Response.Redirect(url,false)
12. Finalize Method Vs Destructor
13. Difference between ExecuteReader, ExecuteNonQuery and ExecuteScalar.
14. Difference between Dataset and DataReader
15. “out” keyword Vs “ref” keyword

1.

What is Difference between == and .Equals() Method?

For Value Type: == and .Equals() method usually compare two objects by value.

For Example:

int x = 10;

int y = 10;

Console.WriteLine( x == y);

Console.WriteLine(x.Equals(y));

Will display:

True

True

For Reference Type: == performs an identity comparison, i.e. it will only return true if both references point to the same object. While Equals() method is expected to perform a value comparison, i.e. it will return true if the references point to objects that are equivalent.

For Example:

StringBuilder s1 = new StringBuilder(“Yes”);

StringBuilder s2 = new StringBuilder(“Yes”);

Console.WriteLine(s1 == s2);

Console.WriteLine(s1.Equals(s2));

Will display:

False

True

In above example, s1 and s2 are different objects hence “==” returns false, but they are equivalent hence “Equals()” method returns true. Remember there is an exception of this rule, i.e. when you use “==” operator with string class it compares value rather than identity.

When to use “==” operator and when to use “.Equals()” method?

For value comparison, with Value Tyep use “==” operator and use “Equals()” method while performing value comparison with Reference Type.

2.

Difference between Close() and Dispose() Method

Close() Vs Dispose Method

The basic difference between Close() and Dispose() is, when a Close() method is called, any managed resource can be temporarily closed and can be opened once again. It means that, with the same object the resource can be reopened or used. Where as Dispose() method permanently removes any resource ((un)managed) from memory for cleanup and the resource no longer exists for any further processing.

Example showing difference between Close() and Dispose() Method:


using System;
using System.Data;
using System.Data.SqlClient;
public class Test
{
private string connString = "Data Source=COMP3;Initial Catalog=Northwind;User Id=sa;Password=pass";
private SqlConnection connection;
public Test()
{
connection = new SqlConnection(connString);
}
private static void Main()
{
Test t = new Test();
t.ConnectionStatus();
Console.ReadLine();
}
public void ConnectionStatus()
{
try
{
if(connection.State == ConnectionState.Closed)
{
connection.Open();
Console.WriteLine("Connection opened..");
}

if(connection.State == ConnectionState.Open)
{
connection.Close();
Console.WriteLine("Connection closed..");
}
// connection.Dispose();

if(connection.State == ConnectionState.Closed)
{
connection.Open();
Console.WriteLine("Connection again opened..");
}
}
catch(SqlException ex)
{
Console.WriteLine(ex.Message+"\n"+ex.StackTrace);
}
catch(Exception ey)
{
Console.WriteLine(ey.Message+"\n"+ey.StackTrace);
}
finally
{
Console.WriteLine("Connection closed and disposed..");
connection.Dispose();
}
}
}

In the above example if you uncomment the "connection.Dispose()" method and execute, you will get an exception as, "The ConnectionString property has not been initialized.".This is the difference between Close() and Dispose().

3.

What is the difference between Copy and Clone Method.

Copy Vs Clone Method

Clone will copy the structure of a data where as
Copy will copy the complete structure as well as data .

4.

Value Type and Reference Type Concept in .Net

A variable is value type or reference type is solely determined by its data type.
Eg: int, float, char, decimal, bool, decimal, struct, etc are value types, while object type such as class, String, Array, etc are reference type.


Value Type

1) As name suggest Value Type stores “value” directly.
2) For eg:
//I and J are both of type int
I = 20;
J = I;
int is a value type, which means that the above statements will results in two locations in memory.
3) For each instance of value type separate memory is allocated.
4) Stored in a Stack.
5) It Provides Quick Access, because of value located on stack.

Reference Type

1) As name suggest Reference Type stores “reference” to the value
2) For e.g.:
Vector X, Y; //Object is defined. (No memory is allocated.)
X = new Vector(); //Memory is allocated to Object. //(new is responsible for allocating memory)
X.value = 30; //Initialising value field in a vector class
Y = X; //Both X and Y points to same memory location. //No memory is created for Y.
Console.writeline(Y.value); //displays 30, as both points to same memory
Y.value = 50;
Console.writeline(X.value); //displays 50
Note: If a variable is reference it is possible to indicate that it does not refer to any object by setting its value to null;
3) Reference type are stored on Heap
4) It provides comparatively slower access, as value located on heap


Note:
The behavior of strings is different, strings are immutable (unchangeable) if we alter a strings value, we create an entirely new string), so strings don’t display the typical reference-type behavior. Any changes made to a string within a method call won’t affect the original string.

5.
Difference between Struct and Class

Struct are Value type and are stored on stack, while Class are Reference type and are stored on heap.

Struct “do not support” inheritance, while class supports inheritance. However struct can implements interface.

Struct should be used when you want to use a small data structure, while Class is better choice for complex data structure.

Choosing between struct and class

Use of structure in following condition is desirable.
When you have small data structure
Data Structure does not require to extent the functionality.
When you want to perform faster operation on data structure. (As structure are stored on stack, operation performed on it are faster.)

Use of class in following condition is desirable.
When you have complex data structure
Data Structure requires to extend functionality.
When you want to optimize memory usage. (As class is stored on heap, they are about to be garbage collected when no reference pointing to an object.)


What is the difference between instantiating structures with and without using the new Keyword?
When a structure is instantiated using the new keyword, a constructor (no-argument or custom, if provided) is called which initializes the fields in the structure. When a structure is instantiated without using the new keyword, no constructor is called. Hence, one has to explicitly initialize all the fields of the structure before using it when instantiated without the new keyword.

6.

Static Members of the class
“static members” belong to the whole class rather than to individual object

How to access static member of class?
Static members are accessed with the name of class rather than reference to objects. Example: className.StaticMemberName

Example of Static Member

class Test
{
public int rollNo;
public int mathsMarks;
public static int totalMathMarks;
}

class TestDemo
{
public static void main()
{
Test stud1 = new Test();
stud1.rollNo = 1;
stud1.mathsMarks = 40;

stud2.rollNo = 2;
stud2.mathsMarks = 43;

Test.totalMathsMarks = stud1.mathsMarks + stud2.mathsMarks;
}
}



Static Method of the class
- Static Method is Method that you can call directly without first creating an instance of a class. Example: Main() Method, Console.WriteLine()
- You can use static fields, methods, properties and even constructors which will be called before any instance of the class is created.
- As static methods may be called without any reference to object, you can not use instance members inside static methods or properties, while you may call a static member from a non-static context. The reason for being able to call static members from non-static context is that static members belong to the class and are present irrespective of the existence of even a single object.


Static Constructor
In C# it is possible to write a static no-parameter constructor for a class. Such a class is executed once, when first object of class is created.

One reason for writing a static constructor would be if your class has some static fields or properties that need to be initialized from an external source before the class is first used.
Example
Class MyClass
{
static MyClass()
{
//Initialization Code for static fields and properties.
}
}

7.

Difference between ADO and ADO.net
1. ADO used connected data usage, while ADO.net used disconnected data environment.
2. ADO used OLE DB to access data and is COM-based, while ADO.net uses XML as the format for transmitting data to and from your database and web application.
3. In ADO, Record set, is like a single table or query result, while in ADO.net Dataset, can contain multiple tables from any data source.
4. In ADO, it is sometime problematic because firewall prohibits many types of request, while in ADO.net there is no such problem because XML is completely firewall-proof.

8.

Difference between ADO.net Dataset and ADO Recordset
1 A DataSet can represent an entire relational database in memory, complete with tables, relations, and views.
2 A DataSet is designed to work without any continuing connection to the original data source.
3 Data in a DataSet is bulk-loaded, rather than being loaded on demand.
There's no concept of cursor types in a DataSet.
4 DataSets have no current record pointer You can use For Each loops to move through the data.
5 You can store many edits in a DataSet, and write them to the original data source in a single operation.
6 Though the DataSet is universal, other objects in ADO.NET come in different versions for different data sources.

10.

Difference Between String and StringBuilder

String Class	StringBuilder
Once the string object is created, its length and content cannot be modified.	Even after object is created, it can be able to modify length and content.
Slower	Faster

11.

To avoid ThreadAbortException while Redirect

You don't need to put the redirect in a try-catch block. Just replace all calls to Response.Redirect(url) with the following two lines:

Response.Redirect(url, false);

That will avoid the exception being thrown and gracefully end execution of the thread and event chain. See http://www.c6software.com/CodeSolutions/dotnet/ThreadAbortException.aspx for a full solution explanation.

12.

What is difference between ExecuteReader, ExecuteNonQuery and ExecuteScalar.

• ExecuteReader : Use for accessing data. It provides a forward-only, read-only, connected recordset.
• ExecuteNonQuery : Use for data manipulation, such as Insert, Update, Delete.
• ExecuteScalar : Use for retriving 1 row 1 col. value., i.e. Single value. eg: for retriving aggregate function. It is faster than other ways of retriving a single value from DB.

13.

Difference between Dataset and DataReader : Points to be consider while choosing between the DataSet and DataReader objects

DataSet object	DataReader object
Read/Write access	Read-only access
Supports multiple tables from different databases	Supports a single table based on a single SQL query of one database
Disconnected mode	Connected mode
Bind to multiple controls	Bind to a single control
Forward and backward scanning of data	Forward-only scanning of data
Slower access to data	Faster access to data
Greater overhead to enable additional features	Lightweight object with very little overhead
Supported by Visual Studio .NET tools	Must be manually coded

14.

What is “out” keyword in C# ??

“out Keyword” in C#.

out keyword is used for passing a variable for output purpose. It has same concept as ref keyword, but passing a ref parameter needs variable to be initialized while out parameter is passed without initialized.
It is useful when we want to return more than one value from the method.

Point to be kept in mind while using out keyword.
You must assigned value to out parameter in method body, otherwise the method won’t compiled.

Example of “out keyword”:
class Test
{
public static void Main()
{
int a; // may be left un-initialized
DoWork(out a); // note out
Console.WriteLine("The value of a is " + a);
}

public static void DoWork(out int i) // note out
{
i=4; //must assigned value.
}
}

The program will result : The value of a is 4

15. ---------------------------

.Net managed code enjoy's benefits of CLR, which automatically checks for object scope and if it is not referenced by any object than it is removed from memory.

But you can also manually frees memory....

Finalize() Method of Object class
Each class in C# is automatically (implicitly) inherited from the Object class which contains a method Finalize(). This method is guaranteed to be called when your object is garbage collected (removed from memory). You can override this method and put here code for freeing resources that you reserved when using the object.
Example of Finalize Method
Protected override void Finalize()

{

try

{

Console.WriteLine(“Destructing Object….”);//put some code here.

}

finally

{

base.Finalize();

}

}



Destructor

1) A destructor is just opposite to constructor.
2) It has same as the class name, but with prefix ~ (tilde).
3) They do not have return types, not even void and therefore they cannot return values.
4) destructor is invoked whenever an object is about to be garbage collected

Example of Destructor in .Net
class person

{

//constructor

person()

{

}



//destructor

~person()

{

//put resource freeing code here.

}

}



What is the difference between the destructor and the Finalize() method? When does the Finalize() method get called?

Finalize() corresponds to the .Net Framework and is part of the System.Object class. Destructors are C#'s implementation of the Finalize() method. The functionality of both Finalize() and the destructor is the same, i.e., they contain code for freeing the resources when the object is about to be garbage collected. In C#, destructors are converted to the Finalize() method when the program is compiled. The Finalize() method is called by the .Net Runtime and we can not predict when it will be called. It is guaranteed to be called when there is no reference pointing to the object and the object is about to be garbage collected.


Garbage Collection Concept

1) Garbage collection is the mechanism that reclaims the memory resources of an object when it is no longer referenced by a variable.
2) .Net Runtime performs automatically performs garbage collection, however you can force the garbage collection to run at a certain point in your code by calling System.GC.Collect().
3) Advantage of Garbage collection : It prevents programming error that could otherwise occur by incorrectly deleting or failing to delete objects.

The Finally block is not really always guaranteed to get executed.

The Finally block is not really always guaranteed to get executed.


If any of these 3 special exceptions do happen, code in Finally won't be executed:

1) OutOfMemoryException,
2) StackOverFlowException
3) ExecutionEngineException

Those people fortunate who experience all of these exceptions.

That means you had created some global kernel objects, they will indeed hang around and may interfere when user restarts your app. BTW, if you see a code
like catch(Exception ex) {...} or catch{...}, tell the developer that he has committed a sin.


The idea and content taken from this site http://www.shitalshah.com/blog/SomeCoolNetNuggets.aspx
-------------------
Happy programming

Getting The Real IP Of Your Users(Track IP Address)

I have already written on post how to track the Visior information.here i am going to explore how to track the IP Address of Visitor/Client.


The Following code will get the IP Address of visitor/ client.

------------------------------------------------------
C# code to track IP Address
------------------------------------------------------

string ipaddress;

// Look for a proxy address first
ipaddress = Request.ServerVariables["HTTP_X_FORWARDED_FOR"];

// If there is no proxy, get the standard remote address
if (ipaddress == "" || ipaddress == null)

ipaddress = Request.ServerVariables["REMOTE_ADDR"];


-----------------------------------------------
VB Code to track IP Address
-----------------------------------------------

' Look for a proxy address first
Dim _ip As String = Request.ServerVariables("HTTP_X_FORWARDED_FOR")

' If there is no proxy, get the standard remote address
If (_ip = "" Or _ip.ToLower = "unknown") Then _
_ip = Request.ServerVariables("REMOTE_ADDR")

-----------------------------------------------------------
Explaination of above Code
-----------------------------------------------------------

The HTTP_X_FORWARDED_FOR variable is to check that there isn't a non-transparent proxy in the way.When users are behind any proxies or routers the REMOTE_ADDR returns the IP Address of the router and not the client user’s machine. Hence first we need to check HTTP_X_FORWARDED_FOR, since when client user is behind a proxy server his machine’s IP Address the Proxy Server’s IP Address is appended to the client machine’s IP Address. If there are multiple proxy servers the IP Addresses of all of them are appended to the client machine IP Address.

Hence we need to first check HTTP_X_FORWARDED_FOR and then REMOTE_ADDR.


Note: While running this application on your machine it will show IP Address 127.0.0.1 since your client and server is the same machine. when you will run this application on live server it will display the right information.

------------------------
Happy Programming

Bulk Insert From CSV OR Text File In SQL SERVER 2005

One of my friends contacted me recently and said they needed some help creating a stored procedure that imported data from a text file. They wanted the procedure to accept three parameters: PathFileName, ColumnDelimter, and RowDeliter. The PathFileName is simply the name and physical location of the source file on the hard drive, the Delimer are used to identify Columns and rows respectively.
So that i will have give her following store procedure.


Create Proc BulkInsertFromFile
(
@FileName Varchar(500), -- depends on path
@ColumnDelimier Varchar(10), -- delimiter for columns
@RowDelimier Varchar(10)--, -- delimier for Rows  
--@Output varchar(50)  -- used to specify error or success msg
)
As

DECLARE @doesExist INT

SET NOCOUNT ON
EXEC xp_fileexist @FileName, @doesExist OUTPUT
SET NOCOUNT OFF

-- i m creating Temp Table to demonstrate the example
-- Temp table holds the Results copies from FIle

Create Table #Temp
(
[CategoryId] [int],
[UserId] [int] NULL,
[ParentId] [int] NULL,
[CategoryName] [varchar](500),
[Description] [varchar](1000),
[Active] [bit] NULL,
[ImagePath] [varchar](max),
[CreatedDate] [datetime] NULL,
[DeleteStatus] [bit] NULL,
)

IF @doesExist = 1
BEGIN

Declare @SQL  nVarchar(max)  
Declare @Param nVarchar(500)
Set @Param='@FileName Varchar(500),@ColumnDelimier Varchar(20),@RowDelimier Varchar(20)'
SET @SQL = 'BULK INSERT #Temp FROM '''+@FileName+'''
WITH
(
FIELDTERMINATOR ='''+@ColumnDelimier+''',
ROWTERMINATOR ='''+@RowDelimier+''',
FIRE_TRIGGERS
)'

print @SQL
EXEC sp_executesql @SQL,@param,@FileName,@ColumnDelimier,@RowDelimier
--    Set @Output='Bulk insert Completed'
END
--Else
--Begin
--    Set @Output='File does not exist'
--End

---- u can also use the output  param to specify the error msg


Select * From #Temp

Drop table #Temp

What are the difference between DDL, DML and DCL commands

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

* CREATE - to create objects in the database
* ALTER - alters the structure of the database
* DROP - delete objects from the database
* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
* COMMENT - add comments to the data dictionary
* RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

* SELECT - retrieve data from the a database
* INSERT - insert data into a table
* UPDATE - updates existing data within a table
* DELETE - deletes all records from a table, the space for the records remain
* MERGE - UPSERT operation (insert or update)
* CALL - call a PL/SQL or Java subprogram
* EXPLAIN PLAN - explain access path to data
* LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

* GRANT - gives user's access privileges to database
* REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

How to trace visitor information in ASP.NET?

It is often required to trace or gather the details of the visitor for maintaining website statistics. This can

be easily done in ASP.NET using the Server variables and the Request information available. Various attributes

such as remote host name, IP address, browser type and version etc can be known using the Server variables.

Source Code to trace Visitor:

<%
Response.Write("<b>Name:</b> " + Request.ServerVariables["REMOTE_HOST"] + "<br />");
Response.Write("<b>IP:</b> " + Request.ServerVariables["REMOTE_ADDR"] + "<br />");
Response.Write("<b>User agent:</b> " + Request.ServerVariables["HTTP_USER_AGENT"] + "<br />");
Response.Write("<b>Language:</b> " + Request.ServerVariables["HTTP_ACCEPT_LANGUAGE"] + "<br />");
Response.Write("<b>Browser:</b> " + Request.Browser.Browser + "<br />");
Response.Write("<b>Type:</b> " + Request.Browser.Type + "<br />");
Response.Write("<b>Version:</b> " + Request.Browser.Version + "<br />");
Response.Write("<b>Major version:</b> " + Request.Browser.MajorVersion + "<br />");
Response.Write("<b>Minor version:</b> " + Request.Browser.MinorVersion + "<br />");
Response.Write("<b>Beta:</b> " + Request.Browser.Beta + "<br />");
Response.Write("<b>Cookies:</b> " + Request.Browser.Cookies + "<br />");
Response.Write("<b>Frames:</b> " + Request.Browser.Frames + "<br />");
Response.Write("<b>Tables:</b> " + Request.Browser.Tables + "<br />");
Response.Write("<b>ActiveX:</b> " + Request.Browser.ActiveXControls + "<br />");
Response.Write("<b>Java Applets:</b> " + Request.Browser.JavaApplets + "<br />");
Response.Write("<b>JavaScript:</b> " + Request.Browser.JavaScript + "<br />");
Response.Write("<b>VBScript:</b> " + Request.Browser.VBScript + "<br />");
Response.Write("<b>Platform:</b> " + Request.Browser.Platform + "<br />");
Response.Write("<b>Crawler:</b> " + Request.Browser.Crawler + "<br />");
%>


Output of above Code:
Name: 119.160.194.218
IP: 119.160.194.218
User agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3
Language: en-us,en;q=0.5
Browser: Firefox
Type: Firefox3.5.3
Version: 3.5.3
Major version: 3
Minor version: 0.5
Beta: False
Cookies: True
Frames: True
Tables: True
ActiveX: False
Java Applets: True
JavaScript: True
VBScript: False
Platform: WinXP
Crawler: False 


The output will vary depending on your browser selection.
Thnx.
Happy programming!