Sql Server Transactions - ADO.NET 2.0 - Commit and Rollback

SqlConnection MySqlConnection = new SqlConnection("Connection String");
MySqlConnection.Open();
SqlTransaction MyTransaction = MySqlConnection.BeginTransaction("MyTrans");

SqlCommand MyCommand = new SqlCommand();
MyCommand.Transaction = MyTransaction;
MyCommand.Connection = MySqlConnection;

try
{
MyCommand.CommandText ="Delete From Employe where Empid = 100";
MyCommand.ExecuteNonQuery();
MyCommand.CommandText ="Delete From Salary where Empid = 100";
MyCommand.ExecuteNonQuery();
MyTransaction.Commit();
}
catch
{
MyTransaction.Rollback();
}
finally
{
MySqlConnection.Close();
}

How to select the last inserted record from Table

CREATE PROCEDURE myProc
@param1 INT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO someTable
(
intColumn
)
VALUES
(
@param1
)
SELECT NEWID = SCOPE_IDENTITY()
END

How to select the last inserted record from the identity column

SELECT @@IDENTITY FROM MyTable;

String Split in SQL Query

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

------------------------------Result----------------------------------------
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

Chennai
Bangalore,
Mumbai

Split the string in SQL

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

------------------------------Result----------------------------------------
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

Chennai
Bangalore,
Mumbai

String Separation in SQLQuery

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

------------------------------Result----------------------------------------
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

Chennai
Bangalore,
Mumbai

SQl Insert

SYNTAX :

INSERT INTO "table_name" ("column1", "column2", ...)

VALUES ("value1", "value2", ...)

---------------------------------------------------------------------------------------
INSERT INTO Employee (EmpName, EmpNo)

VALUES ("Sunjaiy","234");

SQL INTERSECT

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

 
Table Internet_Sales

Date	Sales
Jan-07-1999	$250
Jan-10-1999	$535
Jan-11-1999	$320
Jan-12-1999	$750

and we want to find out all the dates where there are both store sales and internet sales. To do so, we use the following SQL statement:

SELECT Date FROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales
Result:

Date

Jan-07-1999

SQL Alter Table Modify

Syntax:

ALTER TABLE "table_name"

ALTER COLUMN "column 1" "New Data Type"

SQL IS NOT NULL

SQL IS NOT NULL

Look at the following "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola		Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari		Stavanger

How do we select only the records with no NULL values in the "Address" column?
We will have to use the IS NOT NULL operator:

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL

The result-set will look like this:

LastName	FirstName	Address
Svendson	Tove	Borgvn 23

SQL NULL Values

SQL NULL Values

NULL values represent missing unknown data.
By default, a table column can hold NULL values.
This chapter will explain the IS NULL and IS NOT NULL operators.

SQL NULL ValuesIf a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.

NULL values are treated differently from other values.
NULL is used as a placeholder for unknown or inapplicable values.


SQL Working with NULL Values
Look at the following "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola		Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari		Stavanger

Suppose that the "Address" column in the "Persons" table is optional. This means that if we insert a record with no value for the "Address" column, the "Address" column will be saved with a NULL value.

How can we test for NULL values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

SQL IS NULLHow do we select only the records with NULL values in the "Address" column?
We will have to use the IS NULL operator:

SELECT LastName,FirstName,Address  FROM Persons      WHERE Address IS NULL

The result-set will look like this:

LastName	FirstName	Address
Hansen	Ola
Pettersen	Kari

Note: It is not possible to compare NULL and 0; they are not equivalent. 

SQL replication

SQL replication is a process for sharing/distributing data between different databases and synchronizing between those databases. You can use SQL replication to distribute data to a variety of network points like other database servers, mobile users, etc. You can perform the replication over many different kinds of networks and this won�t affect the end result.
In every SQL replication there are 2 main players called Publisher and Subscriber. The Publisher is the replication end point that supplies the data and the replication Subscriber is the replication end point that uses the data from the Publisher. Depending on the replication architecture a replication can have one or more Publishers and of course any replication will have one or more Subscribers.
MS SQL Server offers several main replication types. The Transactional replication is usually used when there�s need to integrate data from several different locations, offloading batch processing, and in data warehousing scenarios.
Another replication type is the Snapshot replication. The Snapshot replication is commonly performed when a full database refresh is appropriate or as a starting point for transactional or merge replications.
The third important SQL replication type is the Merge replication. The Merge replication is used whenever there is a possibility for a data conflicts across distributed server applications.

SQL NOT IN

SELECT * FROM Employees
WHERE [Last Name] NOT IN (N'Anderson', N'Shepherd');

SQL String Functions

String Functions

Some of the String Functions comes very handy at times. Let us discuss them one by one.

ASCII()

Returns the ASCII code value of the leftmost character of a character expression.

Syntax

ASCII ( character_expression ) 

SELECT ASCII('A') 

SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position
-- and for the character string.
DECLARE @position int, @string char(15)
-- Initialize the variables.
SET @position = 1
SET @string = 'The Csharp'
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)),
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
    SET @position = @position + 1
   END
SET NOCOUNT OFF

Output:

-----------
65
----------- ----
84          T
----------- ----
104         h
----------- ----
101         e
----------- ----
and so on..... 

SQL DateName

DATENAME()

DATENAME() is very common and most useful function to find out the date name from the datetime value.
Example

 

-- Get Today
SELECT DATENAME(dw, getdate()) AS 'Today Is'
-- Get Mont name
SELECT DATENAME(month, getdate()) AS 'Month'

Output :

 Today Is Saturday
   Month IsAugust

Update Trigger

Create TRIGGER Tgr_Update  ON  LeaveDetails


AFTER UPDATE
AS
BEGIN
Select  Top 1 @Empid = Empid from LeaveDetails 


  IF( @Empid > 0 AND @Empid IS NOT NULL)
 
   BEGIN
          update dbo.MasterLeave set TotalLeaveWithoutPay = 0, RemainingLeave = 1
          where Empid =@Empid
  END
END

SQl Dateadd

select dateadd(dd, 30, getdate())

SQl First day of the Month

------------------Take First day of the Month ---------------

DECLARE @pInputDate          DATETIME
DECLARE @vOutputDate        DATETIME


set @pInputDate='4/25/2011' April Month


    SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +   CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'
    SET @vOutputDate = DATEADD(DD,0, DATEADD(MM, 0, @vOutputDate))
o/p= 1 April 2011

Sql ISDate

select isdate(getdate())

How Take First day of the Month in SQL

------------------Take First day of the Month ---------------

DECLARE @pInputDate          DATETIME
DECLARE @vOutputDate        DATETIME


set @pInputDate='4/25/2011' April Month


    SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +   CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'
    SET @vOutputDate = DATEADD(DD,0, DATEADD(MM, 0, @vOutputDate))
o/p= 1 April 2011