sone useful sql functions

Last day of year
DATEADD(yyyy,datediff(yyyy,0,GETDATE()),0) -1
First day of year
DATEADD(yyyy,datediff(yyyy,0,GETDATE()),0)
Last day of quarter
DATEADD(QQ,datediff(QQ,0,GETDATE()),0) -1

Last day of week
DATEADD(week,datediff(week,0,GETDATE()),0) -1

BulkCopy using .Net

Using bulkCopyEOM As SqlBulkCopy = New SqlBulkCopy(sqlConn)
bulkCopyEOM.BatchSize = dtODT.Rows.Count
bulkCopyEOM.BulkCopyTimeout = 6000

bulkCopyEOM.DestinationTableName = sqlTableName
bulkCopyEOM.WriteToServer(dtODT)
End Using

Find email from LDAP

Imports System.DirectoryServices
Sub GetEmailAddress()
Dim EMailAddress As String = String.Empty
Dim Root As New DirectoryEntry("LDAP://RootDSE")
Dim DomainNC As String = Root.Properties("defaultNamingContext").Value
Root = New DirectoryEntry("LDAP://" & DomainNC)
Dim Searcher As New DirectorySearcher(Root)
With Searcher
.Filter = "(sAMAccountName=" & Environment.UserName & ")"
.SearchScope = SearchScope.Subtree
.PropertiesToLoad.Add("mail")
End With
Dim Result As SearchResult = Searcher.FindOne
If Not Result Is Nothing Then
EMailAddress = Result.GetDirectoryEntry.Properties("mail").Value
End If
txtRequestorName.Text = Environment.UserName.ToString()
txtEmail.Text = EMailAddress
End Sub

Export Crystal report to pdf file without showing report

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Dim daPref As New SqlDataAdapter
Dim dsPref As New DataSet
Dim clsCom As New clsCommon
Dim PDFFilename As String = String.Empty
Dim SqlConn As New SqlConnection
Dim crtableLogoninfos As New CrystalDecisions.Shared.TableLogOnInfos
Dim crtableLogoninfo As New CrystalDecisions.Shared.TableLogOnInfo
Dim crConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo
Dim CrTables As Tables
Dim CrTable As Table
Dim crpReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
'Try
crpReport.Load(Server.MapPath("~") & "\PDFView.rpt")
With crConnectionInfo
.ServerName = System.Configuration.ConfigurationManager.AppSettings("ServerAddress")
.DatabaseName = System.Configuration.ConfigurationManager.AppSettings("ServerDB")
.UserID = System.Configuration.ConfigurationManager.AppSettings("ServerUser")
.Password = System.Configuration.ConfigurationManager.AppSettings("ServerPassword")
End With
CrTables = crpReport.Database.Tables
For Each CrTable In CrTables
crtableLogoninfo = CrTable.LogOnInfo
crtableLogoninfo.ConnectionInfo = crConnectionInfo
CrTable.ApplyLogOnInfo(crtableLogoninfo)
CrTable.Location = System.Configuration.ConfigurationManager.AppSettings("ServerDB") & ".dbo." & CrTable.Location.Substring(CrTable.Location.LastIndexOf(".") + 1)
Next
crpReport.SetParameterValue(0, ProjectId)
'CRViewer.ReportSource = crpReport
crpReport.ExportOptions.ExportFormatType = CrystalDecisions.[Shared].ExportFormatType.PortableDocFormat
crpReport.ExportOptions.ExportDestinationType = CrystalDecisions.[Shared].ExportDestinationType.DiskFile
PDFFilename = Server.MapPath("~") & "\" & ProjectId & " - Project Request - " & txtProjectTitle.Text.Trim() & ".pdf"
crpReport.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, PDFFilename)

Downloading file from server using Save as dialouge

Dim strRequest As String = Request.QueryString(0) '
If strRequest <> "" Then
Dim path As String = Request.QueryString(0)
Dim file As System.IO.FileInfo = New System.IO.FileInfo(path)
If file.Exists Then
Response.Clear()
Response.AddHeader("Content-Disposition", "attachment; filename=" & file.Name)
Response.AddHeader("Content-Length", file.Length.ToString())
Response.ContentType = "application/octet-stream"
Response.WriteFile(file.FullName)
Response.End()
Else
Response.Write("This file does not exist.")
End If
Else
Response.Write("Please provide a file to download.")
End If

Simple way to export data to excel

Protected Sub ExportToExcel(ByVal MyDataSet As DataSet)
Dim sw As New StreamWriter(Server.MapPath("~/ProjectReport.csv"), False)
' First we will write the headers.
Dim dt As DataTable = MyDataSet.Tables(0)
Dim iColCount As Integer = dt.Columns.Count
For i As Integer = 0 To iColCount - 1
sw.Write(dt.Columns(i))
If i < iColCount - 1 Then
sw.Write(",")
End If
Next
sw.Write(sw.NewLine)
sw.Write(sw.NewLine)
' Now write all the rows.
For Each dr As DataRow In dt.Rows
For i As Integer = 0 To iColCount - 1
If Not Convert.IsDBNull(dr(i)) Then
sw.Write(dr(i).ToString())
End If
If i < iColCount - 1 Then
sw.Write(",")
End If

sw.Write(sw.NewLine)
Next
sw.Close()
System.Diagnostics.Process.Start(Server.MapPath("~/ProjectReport.csv"))
End Sub

Get all columns of a table in SQL

SELECT column_name 'Column Name',
data_type 'Data Type',
CHARacter_maximum_length 'Maximum Length'
FROM information_schema.columns
WHERE table_name =

Also u can run this Query too
select * from information_schema.columns
This view will give you the list of tables and all the columns in those tables with some good information.

A nice gridView Css

.mGrid {
width: 100%;
background-color: #fff;
margin: 5px 0 10px 0;
border: solid 1px #525252;
border-collapse:collapse;
}
.mGrid td {
padding: 2px;
border: solid 1px #c1c1c1;
color: #717171;
}
.mGrid th {
padding: 4px 2px;
color: #fff;
background: #424242 url(grd_head.png) repeat-x top;
border-left: solid 1px #525252;
font-size: 0.9em;
}
.mGrid .alt { background: #fcfcfc url(grd_alt.png) repeat-x top; }
.mGrid .pgr { background: #424242 url(grd_pgr.png) repeat-x top; }
.mGrid .pgr table { margin: 5px 0; }
.mGrid .pgr td {
border-width: 0;
padding: 0 6px;
border-left: solid 1px #666;
font-weight: bold;
color: #fff;
line-height: 12px;
}
.mGrid .pgr a { color: #666; text-decoration: none; }
.mGrid .pgr a:hover { color: #000; text-decoration: none; }




PagerStyle-CssClass="pgr" AlternatingRowStyle-CssClass="alt" PageSize="5" GridLines="None"
AllowPaging="true">


SortExpression="Project Title" />
SortExpression="Domain Owner" />



SortExpression="Sub-Assignment" />

Some grid view code snipets

private string ConvertSortDirectionToSql(SortDirection sortDirection){ string newSortDirection = String.Empty;
switch (sortDirection) { case SortDirection.Ascending: newSortDirection = "ASC"; break;
case SortDirection.Descending: newSortDirection = "DESC"; break; }
return newSortDirection;}
protected void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e){ gridView.PageIndex = e.NewPageIndex; gridView.DataBind();}
protected void gridView_Sorting(object sender, GridViewSortEventArgs e){ DataTable dataTable = gridView.DataSource as DataTable;
if (dataTable != null) { DataView dataView = new DataView(dataTable); dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection);
gridView.DataSource = dataView; gridView.DataBind(); }}

Java script function for CheckAll

function checkAll(ref) {
var chkAll = document.getElementById('checkAll');
var checks = document.getElementsByName('del[]');
var removeButton = document.getElementById('removeChecked');
var boxLength = checks.length;
var allChecked = false;
var totalChecked = 0;
if ( ref == 1 ) {
if ( chkAll.checked == true ) {
for ( i=0; i < boxLength; i++ ) {
checks[i].checked = true;
}
}
else {
for ( i=0; i < boxLength; i++ ) {
checks[i].checked = false;
}
}
}
else {
for ( i=0; i < boxLength; i++ ) {
if ( checks[i].checked == true ) {
allChecked = true;
continue;
}
else {
allChecked = false;
break;
}
}
if ( allChecked == true ) {
chkAll.checked = true;
}
else {
chkAll.checked = false;
}
}
for ( j=0; j < boxLength; j++ ) {
if ( checks[j].checked == true ) {
totalChecked++;
}
}
removeButton.value = "Remove ["+totalChecked+"] Selected";
}