Tuesday 28 June 2016

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;
            }

Tuesday 7 June 2016

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, '''', '"');

Monday 6 June 2016

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