Simple gridview to pdf using iTextSharp

public void GridToPdf()
    {

        GridView gv = new GridView();
        DataTable Data = new DataTable();
        Data.Columns.Add("Sno");
        Data.Columns.Add("Name");
        DataRow DR = Data.NewRow();
        DR["Sno"] = "1";
        DR["Name"] = "Name of a person";

        Data.Rows.Add(DR);

        Response.ContentType = "application/pdf";
        Response.AddHeader("content-disposition", "attachment;filename=" + lblTitle.Text + ".pdf");
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        System.IO.StringWriter sw = new System.IO.StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        gv.BorderStyle = BorderStyle.None;
        gv.DataSource = Data;
        gv.HeaderStyle.Font.Bold = true;
        gv.DataBind();
       
        gv.RenderControl(hw);
        string str1 = sw.ToString();
        System.IO.StringReader sr = new System.IO.StringReader(str1);
        iTextSharp.text.Document pdfDoc = new iTextSharp.text.Document(iTextSharp.text.PageSize.A4.Rotate(), 40f, 10f, 40f, 2f);
        iTextSharp.text.html.simpleparser.HTMLWorker htmlparser = new iTextSharp.text.html.simpleparser.HTMLWorker(pdfDoc);
        iTextSharp.text.pdf.PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
        iTextSharp.text.pdf.PdfWriter wrt = iTextSharp.text.pdf.PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
        pdfDoc.Open();
        htmlparser.Parse(sr);
        pdfDoc.Close();
        Response.End();
    }

Password Encryption and Compare in SQL Server 2008

declare @r varchar(50)
declare @t varbinary(255)
set @r='Sa'
select @t=PWDENCRYPT('sa')

select PWDCOMPARE('sa',@t)-- it returns 1(Password correct!)

From  http://msmvps.com/blogs/gladchenko/archive/2005/04/06/41083.aspx
 

How to convert number into words in c# code.

Code get from 
public string retWord(int number)
  {

      if (number == 0) return "Zero";
      if (number == -2147483648) return "Minus Two Hundred and Fourteen Crore Seventy Four Lakh Eighty Three Thousand Six Hundred and Forty Eight";
      int[] num = new int[4];
      int first = 0;
      int u, h, t;
      System.Text.StringBuilder sb = new System.Text.StringBuilder();

      if (number < 0)
      {
          sb.Append("Minus");
          number = -number;
      }
      string[] words0 = { "", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight", "Nine " };
      string[] words = { "Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen " };

      string[] words2 = { "Twenty", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty", "Ninety " };

      string[] words3 = { "Thousand ", "Lakh ", "Crore " };

      num[0] = number % 1000; // units

      num[1] = number / 1000;

      num[2] = number / 100000;

      num[1] = num[1] - 100 * num[2]; // thousands

      num[3] = number / 10000000; // crores

      num[2] = num[2] - 100 * num[3]; // lakhs
      for (int i = 3; i > 0; i--)
      {
          if (num[i] != 0)
          {
              first = i;
              break;
          }
      }
      for (int i = first; i >= 0; i--)
      {
          if (num[i] == 0) continue;

          u = num[i] % 10; // ones

          t = num[i] / 10;

          h = num[i] / 100; // hundreds

          t = t - 10 * h; // tens

          if (h > 0) sb.Append(words0[h] + "Hundred ");

          if (u > 0 || t > 0)
          {
              if (h > 0 || i == 0) sb.Append("and ");
              if (t == 0)
                  sb.Append(words0[u]);

              else if (t == 1)
                  sb.Append(words[u]);
              else
                  sb.Append(words2[t - 2] + words0[u]);
          }

          if (i != 0) sb.Append(words3[i - 1]);
      }
      return sb.ToString().TrimEnd();

  }  

select whole year date as data in sql server

DECLARE @FromDate DATETIME, @ToDate DATETIME;
SET @FromDate = '1900-01-01';
SET @ToDate = '2040-12-31';

-- all days in that period
SELECT TOP (DATEDIFF(DAY, @FromDate, @ToDate)+1)
  TheDate = DATEADD(DAY, number, @FromDate)
  FROM [master].dbo.spt_values
  WHERE [type] = N'P' ORDER BY number;

-- just the months in that period
SELECT TOP (DATEDIFF(MONTH, @FromDate, @ToDate)+1)
  TheDate  = DATEADD(MONTH, number, @FromDate),
  TheMonth = MONTH(DATEADD(MONTH, number, @FromDate)),
  TheYear  = YEAR(DATEADD(MONTH, number, @FromDate))
  FROM [master].dbo.spt_values
  WHERE [type] = N'P' ORDER BY number;
 
also
http://stackoverflow.com/questions/4273723/what-is-the-purpose-of-system-table-table-master-spt-values-and-what-are-the-me

Set Tooltip for each items in DropdownList asp.net C#

ddlRelatedPGMA.Attributes.Add("title", ddlRelatedPGMA.SelectedItem.Text);
foreach (ListItem li in ddlRelatedPGMA.Items)
 {
   li.Attributes.Add("title", li.Text);
 }


public void setToolTip(DropDownList ddl)
{
 ddl.Attributes.Add("title", ddl.SelectedItem.Text);
foreach (ListItem li in ddl.Items)
 {
   li.Attributes.Add("title", li.Text);
 }
}

Text ellipsis in gridview.

.textEllipsis
{
    overflow: hidden;
    max-width: 130px;/* Number of text to be appear before three dots (...)  */
    text-overflow: ellipsis;
     white-space: nowrap;
     width:60px;
/*
     This will used in gridview, it shows text with followed by three dots. For example if
      mrn value is  MRN-XX-0001 and it looks like MRN-XX-...
*/
}
Step-1:
 Add the above css style into style sheet like "kaizen-style.css"

Step-2:
 <asp:TemplateField HeaderText="PO Number" ItemStyle-Width="6%">
    <ItemTemplate>
       <div class="textEllipsis">
          <asp:Label ID="lblPONum" runat="server" Text='<%# Bind("PONumber") %>'></asp:Label>
       </div>
    </ItemTemplate>
</asp:TemplateField>


Then we may use tooltip to show the PONnumber as full.

SQL Server Automatic backp and delete old backup files.

Note: All files should be saved in folder E:\SQL_Backup\scripts.  This can be changed, but this example is setup for this folder.  If you save to a different folder you will need to update the scripts accordingly.
Step 1 - Create the TSQL scriptThe TSQL script below generates a database backup similar to the formatting generated by the database maintenance plan, taking into account the date and time the backup files were generated.  We save the script as a .sql file, E:\SQL_Backup\scripts\backupDB.sql, which we will call from a batch file using sqlcmd.

DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2) --month variable IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2
   SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2)) ELSE
   SET @monthSTR= '0' + CAST(MONTH(GETDATE()) AS CHAR(2)) --day variable IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2
   SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2)) ELSE
   SET @daySTR='0' + CAST(DAY(GETDATE()) AS CHAR(2)) --hour variable IF (SELECT LEN(DATEPART(hh, GETDATE())))=2
   SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2)) ELSE
   SET @hourStr= '0' + CAST(DATEPART(hh, GETDATE()) AS CHAR(2)) --minute variable IF (SELECT LEN(DATEPART(mi, GETDATE())))=2
   SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2)) ELSE
   SET @minStr= '0' + CAST(DATEPART(mi, GETDATE()) AS CHAR(2)) --name variable based on time stamp SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + @hourStr + @minStr --================================================================= DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200) SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND NAME NOT IN ('TEMPDB') WHILE @IDENT IS NOT NULL BEGIN
   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT /*Change disk location here as required*/
   SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''E:\SQL_Backup\'+@DBNAME+'_db_' + @dateString +'.BAK'' WITH INIT'
   EXEC (@SQL)
   SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND database_id>@IDENT AND NAME NOT IN ('TEMPDB') END 


Step 2 - Create the VBScript fileNext, we will need to create a VBScript file which will be responsible for cleaning up old copies of the database backups. The script also writes to a log file which records the database backup files.

• You do need to create an empty file named  E:\SQL_Backup\scripts\LOG.txt to save a log of the deleted files.
• Also copy the below script and save as E:\SQL_Backup\scripts\deleteBAK.vbs
• On Error Resume Next  
Dim fso, folder, files, sFolder, sFolderTarget     Set fso = CreateObject("Scripting.FileSystemObject")  
'location of the database backup files sFolder = "E:\SQL_Backup\"
Set folder = fso.GetFolder(sFolder)   Set files = folder.Files    
'used for writing to textfile - generate report on database backups deleted Const ForAppending = 8
'you need to create a folder named "scripts" for ease of file management & 
'a file inside it named "LOG.txt" for delete activity logging Set objFile = fso.OpenTextFile(sFolder & "\scripts\LOG.txt", ForAppending)
objFile.Write "================================================================" & VBCRLF & VBCRLF
objFile.Write "                     DATABASE BACKUP FILE REPORT                " & VBCRLF
objFile.Write "                     DATE:  " &    FormatDateTime(Now(),1)   & "" & VBCRLF
objFile.Write "                     TIME:  " &    FormatDateTime(Now(),3)   & "" & VBCRLF & VBCRLF
objFile.Write "================================================================" & VBCRLF 
'iterate thru each of the files in the database backup folder For Each itemFiles In files 
   'retrieve complete path of file for the DeleteFile method and to extract 
        'file extension using the GetExtensionName method
   a=sFolder & itemFiles.Name

   'retrieve file extension 
   b = fso.GetExtensionName(a)
       'check if the file extension is BAK
       If uCase(b)="BAK" Then

           'check if the database backups are older than 3 days
           If DateDiff("d",itemFiles.DateCreated,Now()) >= 3 Then

               'Delete any old BACKUP files to cleanup folder
               fso.DeleteFile a 
               objFile.WriteLine "BACKUP FILE DELETED: " & a
           End If
       End If
Next  
objFile.WriteLine "================================================================" & VBCRLF & VBCRLF

objFile.Close

Set objFile = Nothing
Set fso = Nothing
Set folder = Nothing
Set files = Nothing

Step 3 - Create the batch file that will call the TSQL script and the VBScript fileWe need to create the batch file which will call both the TSQL script and the VBScript file. The contents of the batch file will be a simple call to the sqlcmd.exe and a call to the VBScript file using either wscript.exe or simply calling the file. Save the file as E:\SQL_Backup\scripts\databaseBackup.cmd and save it in the scripts subfolder

REM Run TSQL Script to backup databases
sqlcmd -S<INSTANCENAME>-E -i"E:\SQL_Backup\scripts\backupDB.sql" REM Run database backup cleanup script
E:\SQL_Backup\scripts\deleteBAK.vbs

Step 4 - Create a task in Windows Task SchedulerCreate a daily task in Windows Task Scheduler that will call the batch file created in the previous step. This can be found in the Control Panel -> Scheduled Tasks or under Start -> All Programs -> Accessories -> System Tools -> Scheduled Tasks.

Database cannot be opened due to inaccessible files or insufficient memory or disk space

sqltrends.blogspot.in/2013/03/database-cannot-be-opened-due-to.html
Here is Solution/Fix/workaround of this problem.

1. check the DB status, most of the time , it will return 1

use master
select databaseproperty('dbname','isShutdown')

2. Change the database to offline to clear the db status

use master
alter database dbname set offline

3. Now change the database to online, at this step log file and data files will be verified by sql server


use master
alter database dbname set online


This solution solved the problem.

OUTPUT Clause in sql server

create table z(id int, name varchar(200))
insert into z values(1,'First')
insert into z values(2,'Second')
insert into z values(3,'Third')

select * from z

GO
DELETE FROM z
OUTPUT deleted.ID, deleted.name
WHERE (z.id=1)

GO
update z set id=10 output inserted.id,inserted.name
where id=1

GO
insert into z
output inserted.id
values (5,'five')
http://stackoverflow.com/questions/121243/hidden-features-of-sql-server

DataBase Restore History - SQL Server

SELECT MAX(restore_date) AS LastRestore

      ,COUNT(*) AS CountRestores

      ,destination_database_name

FROM msdb.dbo.restorehistory

GROUP BY destination_database_name

From this link