SUJOY SANTRA: September 2013

Monday 30 September 2013

Difference between Close() and Dispose()

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 (unmanaged) from memory for cleanup and the resource no longer exists for any further processing.

Example :

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().

Friday 27 September 2013

PRIMARY KEY AND UNIQUE KEY

PRIMARY KEY AND UNIQUE KEY are similar except it has different functions. Primary key makes the table row unique (i.e, there cannot be 2 row with the exact same key). You can only have 1 primary key in a database table.

Unique key makes the table column in a table row unique (i.e., no 2 table row may have the same exact value). You can have more than 1 unique key table column (unlike primary key which means only 1 table column in the table is unique).

Primary Key	Unique Key
It will not accept null values	One and only one Null values are accepted.
There will be only one primary key in a table	More than one unique key will be there in a table.
Clustered index is created in Primary key	Non-Clustered index is created in unique key.
Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist.	Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values.

Wednesday 4 September 2013

Get value from popup page Javascript

sample1.html:

<html>

<head>

    <title>Sample 1</title>

</head>

<body>

<script language="JavaScript">

    function windowOpen() {

        var myWindow=window.open('sample2.html','windowRef','width=200,height=200');

       if (!myWindow.opener) myWindow.opener = self;

    }

</script>

<form name="frmMain" method="post" action="">

    <input type="text" value="" name="txtVol" id="txtVol">

    <input name="openPopup" type="button" id="openPopup" onClick="Javascript:windowOpen();" value="Get Value">

</form>

</body>

</html>

sample2.html:

<html>

<head>

   <title>Sample 2</title>

</head>

<body>

<script language="JavaScript">

    function updateOpener() {

        window.opener.document.frmMain.txtVol.value = document.frmMain.txtInput.value;

        window.close();

    }

</script>

Value :<form name="frmMain" method="post" action="">

    <input type="text" name="txtInput" value="">    

     <input name="Close" type="submit" id="Close"onClick="Javascript:updateOpener()" value="Close">

</form>

</body>

</html>

Get the column values as comma separated values in SQL

Get the column values as comma separated values

Table citymaster
cityid cityname
1       A
2       B
3       C
4       D
SELECT DISTINCT STUFF((SELECT ',' + cast(s.cityid as varchar)FROM citymaster s
FOR XML PATH('')),1,1,'') AS CSV
FROM citymaster AS t

output
1,2,3,4
=========================================================
Table CompanyMaster

Company Location
A Chennai
A Pune
B Delhi

B Mumbai
A Bangalore
B Hyderabad

B Kolkata

SELECT t.Company, STUFF((SELECT ',' + s.Location FROM CompanyMaster s

WHERE s.Company = t.Company FOR XML PATH('')),1,1,'') AS Locations
FROM CompanyMaster AS t GROUP BY t.Company

output:

Company  Locations

A        Chennai,Pune,Bangalore

B        Delhi,Mumbai,Hydrabad,Kolkata

Get Last 10 Database name in SQL Server

Last 10 database name:

Select top 10 name,database_id,create_date FROM sys.databases ORDER BY database_id desc

Get List of Stored Procedures Where a Table Name is Used in SQL Server

Following code will get procedures name which contains particular table name

SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableName%'