Wednesday, May 26, 2010

Create Parameterize Query in Sql

Create your query as usewal and pass parameters in a querys


DECLARE @Query NVARCHAR(max)
DECLARE @Parameters NVARCHAR(max)

SET @Query = N'Select * from dbSeminar.dbo.indigo_tech Where Gender = @Gender And EmpGroup = @EmpGroup And FirstName = @FirstName'

SET @Parameters = N'@Gender varchar(3),@EmpGroup int,@FirstName varchar(25)'

EXECUTE sp_executesql @Query, @Parameters, @Gender = 'M' ,@FirstName = 'Murli', @EmpGroup = 1


OR
Exec Sp_executesql N'Select * from Deepak_PC.mydb.dbo.Branch Where RegionID = @RegionID',N'@RegionID int',@RegionID = 1

Friday, May 21, 2010

Find Rowindex in Row_Command event

you don't need to set anything in e.CommandSource


VB

Dim selectedRow As GridViewRow = DirectCast(DirectCast(e.CommandSource, LinkButton).NamingContainer, GridViewRow)
Dim intRowIndex As Integer = Convert.ToInt32(selectedRow.RowIndex)
GridView.Rows(intRowIndex).BackColor = System.Drawing.Color.Blue

C#

GridViewRow selectedRow = (GridViewRow)((ImageButton)e.CommandSource).NamingContainer;
int intRowIndex = Convert.ToInt32(selectedRow.RowIndex);
GridView.Rows[intRowIndex].BackColor = System.Drawing.Color.Blue;

Insert table to another table in sql

Insert tbltech(FirstName,Gender,EmpGroup)
Select FirstName,Gender,EmpGroup from indigo_tech

--===========================
INSERT INTO Members ( memberID, memberareaID )
VALUES(@memberID,(SELECT memberareaID FROM MemberAreas WHERE areadescription = '@areadescription'))
--====================

Select FirstName,Gender,EmpGroup Into Newtbl from indigo_tech

Thursday, May 20, 2010

Simple script to backup all SQL Server databases

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

For More Details : http://www.mssqltips.com/tip.asp?tip=1599

Example uses a filter expression to return an array of DataRow objects.

private void GetRowsByFilter()
{
DataTable table = DataSet1.Tables["Orders"];
// Presuming the DataTable has a column named Date.
string expression;
expression = "Date > #1/1/00#";
DataRow[] foundRows;

// Use the Select method to find all rows matching the filter.
foundRows = table.Select(expression);

// Print column 0 of each returned row.
for(int i = 0; i < foundRows.Length; i ++)
{
Console.WriteLine(foundRows[i][0]);
}
}

Thursday, May 13, 2010

Check Column value is null

Returns the same type as the first expression.

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

Using NullIF(ColName,Expretion)