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

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

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

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)

Message Box in Web(Asp.Net)

WebMsgBox class is a message box for ASP.NET.


Recently, I needed a Windows MessageBox like class for ASP.NET. Doing some search on Google, I found some code for message box in VB.NET and converted code from VB.NET to C# and made some changes. Unfortunately, I forgot the URL where I found the code.


WebMsgBox Class


WebMsgBox class represents a message box for ASP.NET applications. This class has a static method Show, which is used to display a message box. The Show method takes a single argument of string type, which is the message you want to display.


using System;
using Microsoft.VisualBasic;
using System.Text;
using System.Collections;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace MyWebMsgApp
{
public class WebMsgBox
{
protected static Hashtable handlerPages = new Hashtable();

private WebMsgBox()
{

}

public static void Show(string Message)
{
if (!(handlerPages.Contains(HttpContext.Current.Handler)))
{
Page currentPage = (Page)HttpContext.Current.Handler;
if (!((currentPage == null)))
{
Queue messageQueue = new Queue();
messageQueue.Enqueue(Message);
handlerPages.Add(HttpContext.Current.Handler, messageQueue);
currentPage.Unload += new EventHandler(CurrentPageUnload);
}
}
else
{
Queue queue = ((Queue)(handlerPages[HttpContext.Current.Handler]));
queue.Enqueue(Message);
}
}

private static void CurrentPageUnload(object sender, EventArgs e)
{
Queue queue = ((Queue)(handlerPages[HttpContext.Current.Handler]));
if (queue != null)
{
StringBuilder builder = new StringBuilder();
int iMsgCount = queue.Count;
builder.Append("");
handlerPages.Remove(HttpContext.Current.Handler);
HttpContext.Current.Response.Write(builder.ToString());
}
}
}
}



How to use WebMsgBox?

Copy the attached WebMsgBox.cs and add it to your project. You will have to change the namespace of the class. Change the following line to your project's namespace in WebMsgBox.cs file:

namespace MyWebMsgApp

Now call WebMsgBox.Show method when you need to display a message box. For example, if you want to display a message box on a button click, add the following code to the button click event handler.

WebMsgBox.Show("Your message here");