Generic Trigger in SQL

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_TABLENAME]'))
DROP TRIGGER trg_TABLENAME
GO

Create TRIGGER [dbo].[trg_TABLENAME] ON [dbo].[TABLENAME] FOR INSERT, UPDATE, DELETE
AS

DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000),
@PKSelect VARCHAR(1000),
@FieldName varchar(100),
@OldValue varchar(5000),
@NewValue varchar(5000),
@ModifiedBy int, --1
@ModifiedDate VARCHAR(21), --Get the value from same table
@OperationType varchar(1),
@TableName varchar(100),
@ModuleName varchar(100)

--You will need to change @TableName to match the table to be audited
SELECT @TableName = 'TABLENAME'
set @ModuleName='ModuleName'

-- date and user
--SELECT @UserName = SYSTEM_USER ,
select @ModifiedDate = CONVERT(VARCHAR(8), GETDATE(), 112)
+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

-- Action
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @OperationType = 'U'
ELSE
SELECT @OperationType = 'I'
ELSE
SELECT @OperationType = 'D'

-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
+ ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','')
+ '''<' + COLUMN_NAME
+ '=''+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

IF @PKCols IS NULL
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
END

--SELECT @PKCols = ' on i.WOC_ID = d.WOC_ID'
--SELECT @PKSelect = '''<WOC_ID' + '=''+convert(varchar(100),
-- coalesce(i.WOC_ID' +',d.WOC_ID' + '))+''>'''


SELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
OR @OperationType IN ('I','D')
BEGIN


SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field

if(@OperationType='U')
set @OperationType='I'

SELECT @sql = ' insert Log_TransactionDetails ( OperationType,
TableName,
PK_ID,
FieldName,
OldValue,
NewValue,
ModifiedDate,
ModifiedBy,
ModuleName)
select ''' + @OperationType + ''','''
+ @TableName + ''','
+ 'convert(varchar(1000),' + @OperationType + '.CBI_BillingInfoId)' --Change the name Primary ID(I.PrimaryKeyName)
+ ',''' + @fieldname + ''''
+ ',convert(varchar(1000),d.' + @fieldname + ')'
+ ',convert(varchar(1000),i.' + @fieldname + ')'
+ ',''' + @ModifiedDate + ''''
+ ',convert(varchar(1000),i.' + 'UAID' + ')'
+ ',''' + @ModuleName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
+ ' where i.' + @fieldname + ' <> d.' + @fieldname
+ ' or (i.' + @fieldname + ' is null and d.'
+ @fieldname
+ ' is not null)'
+ ' or (i.' + @fieldname + ' is not null and d.'
+ @fieldname
+ ' is null)'
EXEC (@sql)


END
END

--Note: Replace < & ;> for HTML

go

Validation of TextBox

using System.Text.RegularExpressions;

if (ValidateTextBox(txtabc.Text) == "")
{
//Do Something
}
else
{
lblMessage.Text = "Please enter Valid Text";
}


//Function
//Validation for the input start with alphabet
//Number can be added at last or middle
//Only _ is allowed in the string without any special character
private string ValidateTextBox(string strInput)
{
string strValid = "";
string strPattern = @"^[a-zA-Z]{1,20}[0-9a-zA-Z_]*$";
Match objMatch = Regex.Match(strInput, strPattern);
if (!objMatch.Success)
{
strValid = "Something";
}
return strValid;
}

Export to Excel directly from grid

code for export data in excel from Gridview directly with header

protected void BindToExcel()
{

//Please check AutoEventWireup="true" EnableEventValidation="false" and VerifyRenderingInServerForm(Control control)
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
//Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=Client.xls");
Response.Charset = "";
this.EnableViewState = false;

StringWriter sw = new StringWriter();
StringWriter headWrite = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

//Make some colum visible/not visible
dgDetailsTicketStatus.Columns[7].Visible = false;
dgDetailsTicketStatus.Columns[8].Visible = true;
dgDetailsTicketStatus.RenderControl(htw);

//Add Excel Report header
//Write "Details Report" in table tag.
headWrite.Write("Details Report");
headWrite.WriteLine(sw);

Response.Write(headWrite.ToString());
Response.End();
}

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
BindToExcel();
}

public override void VerifyRenderingInServerForm(Control control)
{
}

Disable the button when Clicked

When there is no Attribute added for onclick the code will be:

btnSubmit.Attributes.Add("onclick", "this.disabled=true;" + GetPostBackEventReference(btnSubmit).ToString());

If onclick Attribute exists then:

var btn = document.getElementById('< %=btnSubmit.ClientID% >');
btn.disabled=true;
btn.style.color='#ADAA9C';
__doPostBack('btnSubmit','');

--We can add this on link/button
OnClientClick="this.disabled = true; this.value = 'Genrating Rep..';__doPostBack(this.name,'');"