Fill dataset using stored procedure in .Net

 // Within the code body set your variable  
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            DataSet ds = new DataSet();
            try
            {
                string query = "exec GetData  '" + FromDate + "','" + ToDate + "'";
                using (SqlConnection connection = new SqlConnection(connectionString))
                using (SqlCommand command = new SqlCommand(query, connection))
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    adapter.Fill(ds);
                }
                return ds;
             
            }
            catch
            {
                return ds;
            }

How to replace single quote with double quote

In C#

                strComment = ""Manoj tyagi's photo;
                strComment = strComment.Replace("'", "''");


In Sql :

EX 1

DECLARE @sql varchar(max)
SET @sql = ' INSERT INTO ' + @tempTablea + 
       ' SELECT 0 as TypeA, 0 as TypeB, ' + ''''+
         replace( @name ,'''','''''')+''''+' as Name
       FROM #tempTableb tt2'

Ex 2

UPDATE myTable1
SET myField1 = REPLACE(myField1, '''', '"');

What is cursor and how to use in sql server?

Cursor: Cursor is basically a database object to retrieve data row by from resultset. cursor is used to loop through a resultset.

Steps to use cursor:

declare @studentid int

1. Declare Cursor-

DECLARE @MyCursor CURSOR

2. Set Cursor

SET @MyCursor = CURSOR FAST_FORWARD
FOR
select studentid from tbl_students

3.Open Cursor

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @studentid
WHILE @@FETCH_STATUS = 0

WHILE @@FETCH_STATUS = 0//loop till last row

BEGIN
 query you want to execute
END

4. Close Cursor

CLOSE @MyCursor

5. Deallocate Cursor

DEALLOCATE @MyCursor

How to create non clustered index

Create Clustered Index

/*
USE [your db name]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[table_name] ([column1],[column1])
INCLUDE ([column1],[column1],[column1])
GO

How to check dead lock process id and kill that blocking process in sql server database

Query to check blocking process in sql server.

SELECT
sp.spid
, sp.blocked AS BlockingProcess
, DB_NAME(sp.dbid) AS DatabaseName
, sp.loginame,sp.cpu
, CAST(text AS VARCHAR(1000)) AS SqlStatement
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle)   order by BlockingProcess desc,sqlstatement

Kill blocking process

Kill 20 and execute

SQL Server Database Queries Part 1

Select Statement:

select * from Subjects;

Aliasing Column Name:
you can rename column according to your need using AS keyword.
select subjectname as subject from subjects;


Eliminating duplicate records:(Distinct Clause)
you can eliminate duplicate records using DISTINCT Keyword from resultset.
Select distinct subjectname from subjects;

Filtering Data(Where Clause):

you can filter data using WHERE clause with some condition,
Select subject;name from subjects where subjectid=3;
Select subject;name from subjects where subjectid>3;



The 'Not' Clause:

you can use NOT keyword condition
Select subjectname from subjects where subjectname IS NOT NULL;
SELECT SUBJECTNAME FROM SUBJECTS WHERE SUBJECTID NOT IN (2,3);

Filtering String:
you can filter string using LIKE keyword and %.
'H%' will return all the records start with 'H'.
'%H' will return all the records end with 'H'.
'%H%'  will return all the records start and end with 'H'.

Select subjectname from subject where name like('H%')  ;
  
The In and Between Keywords:

you can use IN clause when you have multiple ids you can get all records which are IN();
select * from subjects where subjectid in (2,3);

you can user BETWEEN clause when you want to get records between some range.

select * from subjects where subjectid BETWEEN 2 and 3;

Sorting Data(Order By Clause):
you can sort resultset using ORDER BY clause.
select * from subjects ORDER BY subjectname;

Limiting Records(Top Clause):

you can limit records by using TOP clause in your query.
select TOP 2 * from subjects ORDERBY subjectname;

OFFSET FETCH:

you can use OFFSET to keep and skip records. It is useful in paging concept.
SELECT * FROM SUBJECTS OFFSET 10 ROWS FETCH NEXT 25 ROWS ONLY;

AGGREGATING DATA; GROUP BY AND HAVING CLAUSE
you can use GROUP BY clause to get record set in Group.
SELECT COUNT(*) FROM SUBJECTS GROUP BY SUBJECTNAME;

Select from multiple tables (Using Joins)

Joins are very useful when you want to get data from multiple tables.
select * from subjects , books;

select a.subjectname,b.bookname from subjects a, books b where a.SubjectID=b.Subject;

There are different type of joins in sql.

Cross Join:

It will return Carthesian Product. 

select * from books CROSS JOIN subjects;

Inner Join:

It is the most useful join. It is optional to use Inner Keyword. It is used in place of where and is a
proper way of join. it will return all the matching rows based on conditon.

select * from subjects INNER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT;

Outer Join:

Outer joins are used when we want to matching and unmatching data from tables. it is of two types.

Left Outer Join:

It returns all matching of left and all of right records.


SELECT * FROM SUBJECTS LEFT OUTER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT;

Right Outer Join:

It return all record of left and matching of right.

SELECT * FROM SUBJECTS Right OUTER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT; 

Ranking Functions:
 
 There are four ranking functions in sql server.

1. ROW_NUMBER
2. RANK
3. DENSE_RANK
4. NTILE

ROW_NUMBER simply select the no of current row.
RANK and DENSE_RANK assisgn unique number to each row.
RANK return total no of rows of same rank plus 1.
DENSE_RANK return rank plus 1.