Friday 30 August 2013

Script to find a particular string in Whole SQL Server Database.

Hello guys,

It is very general task to a developer to find something (string) in database either it could be from a table or from a list of tables or sometimes it could be from the whole database. Finding the string from a table, is very simple but when you have to find the string from the whole database, then generally developer tends to think about any alternate way except to go to each table and search the string manually. So i have searched on google and created a Script which finds the references of the string into whole database. You just have to copy the following Script, paste in your database and just click the execute button. This Script takes three parameters:
  1.  TableNames: if you want to search to the a particular table or from a list of tables then just enter the names of table here, or if you want to search the whole database then just enter a empty string.
  2.  SearchStr: the string you want to search.
  3.  GenerateSQLOnly: Provide 1 if you only want to generate the SQL statements without searching the database. By default it is 0 and it will search. 




IF OBJECT_ID('SearchTables','P') IS NOT NULL 
    DROP PROCEDURE SearchTables 
GO 
CREATE PROCEDURE SearchTables 
 @Tablenames VARCHAR(500) 
,@SearchStr NVARCHAR(60) 
,@GenerateSQLOnly Bit = 0 
AS 
 
/* 
    Parameters and usage 
 
    @Tablenames        -- Provide a single table name or multiple table name with comma seperated.  
                        If left blank , it will check for all the tables in the database 
    @SearchStr        -- Provide the search string. Use the '%' to coin the search.  
                        EX : X%--- will give data staring with X 
                             %X--- will give data ending with X 
                             %X%--- will give data containig  X 
    @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.  
                        By default it is 0 and it will search. 
 
    Samples : 
 
    1. To search data in a table 
 
        EXEC SearchTables @Tablenames = 'T1' 
                         ,@SearchStr  = '%TEST%' 
 
        The above sample searches in table T1 with string containing TEST. 
 
    2. To search in a multiple table 
 
        EXEC SearchTables @Tablenames = 'T2' 
                         ,@SearchStr  = '%TEST%' 
 
        The above sample searches in tables T1 & T2 with string containing TEST. 
     
    3. To search in a all table 
 
        EXEC SearchTables @Tablenames = '%' 
                         ,@SearchStr  = '%TEST%' 
 
        The above sample searches in all table with string containing TEST. 
 
    4. Generate the SQL for the Select statements 
 
        EXEC SearchTables @Tablenames        = 'T1' 
                         ,@SearchStr        = '%TEST%' 
                         ,@GenerateSQLOnly    = 1 
 
*/ 
 
    SET NOCOUNT ON 
 
    DECLARE @CheckTableNames Table 
    ( 
    Tablename sysname 
    ) 
 
    DECLARE @SQLTbl TABLE 
    ( 
     Tablename        SYSNAME 
    ,WHEREClause    VARCHAR(MAX) 
    ,SQLStatement   VARCHAR(MAX) 
    ,Execstatus        BIT  
    ) 
 
    DECLARE @sql VARCHAR(MAX) 
    DECLARE @tmpTblname sysname 
 
    IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') 
    BEGIN 
 
        INSERT INTO @CheckTableNames 
        SELECT Name 
          FROM sys.tables 
    END 
    ELSE 
    BEGIN 
 
        SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + '''' 
 
        INSERT INTO @CheckTableNames 
        EXEC(@sql) 
 
    END 
     
    INSERT INTO @SQLTbl 
    ( Tablename,WHEREClause) 
    SELECT SCh.name + '.' + ST.NAME, 
            ( 
                SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) 
                  FROM SYS.columns SC 
                  JOIN SYS.types STy 
                    ON STy.system_type_id = SC.system_type_id 
                   AND STy.user_type_id =SC.user_type_id 
                 WHERE STY.name in ('varchar','char','nvarchar','nchar') 
                   AND SC.object_id = ST.object_id 
                 ORDER BY SC.name 
                FOR XML PATH('') 
            ) 
      FROM  SYS.tables ST 
      JOIN @CheckTableNames chktbls 
                ON chktbls.Tablename = ST.name  
      JOIN SYS.schemas SCh 
        ON ST.schema_id = SCh.schema_id 
     WHERE ST.name <> 'SearchTMP' 
      GROUP BY ST.object_id, SCh.name + '.' + ST.NAME ; 
 
      UPDATE @SQLTbl 
         SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) 
 
      DELETE FROM @SQLTbl 
       WHERE WHEREClause IS NULL 
     
    WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
    BEGIN 
 
        SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement 
          FROM @SQLTbl  
         WHERE ISNULL(Execstatus ,0) = 0 
 
          
 
         IF @GenerateSQLOnly = 0 
         BEGIN 
 
            IF OBJECT_ID('SearchTMP','U') IS NOT NULL 
                DROP TABLE SearchTMP 
            EXEC (@SQL) 
 
            IF EXISTS(SELECT 1 FROM SearchTMP) 
            BEGIN 
                SELECT Tablename=@tmpTblname,* FROM SearchTMP 
            END 
 
         END 
         ELSE 
         BEGIN 
             PRINT REPLICATE('-',100) 
             PRINT @tmpTblname 
             PRINT REPLICATE('-',100) 
             PRINT replace(@sql,'INTO SearchTMP','') 
         END 
 
         UPDATE @SQLTbl 
            SET Execstatus = 1 
          WHERE Tablename = @tmpTblname 
 
    END 
     
    SET NOCOUNT OFF 
 
Go

exec SearchTables '','%abc%',0
drop proc searchtables
drop table SearchTMP


If you will to get script then 
exec SearchTables '','%abc%',1
Hopefully this script saves your precocious time for searching references of a string throughout the Database or specific tables.

Thanks for reading.







Tuesday 6 August 2013

Convert HTML To PDF using itextsharp ASP.NET

It is very general task to convert the html to pdf in web development. There are number of ways available to convert html to pdf, some of them are paid, but as human nature if we get the something free of cost which having a good price in market then we say wow!!! I am too lucky so I got this. In the same sense why we pay for paid tools when we have free ways to convert html to pdf. I have used itextsharp for this game. I have created a function to which just you need to pass the html and the path where you want to save the pdf. Reset all the things will be managed by the function itself.



protected void ConvertHTMLToPDF(string HTMLCode, string FilePath)
    {
        HttpContext context = HttpContext.Current;

        //Render PlaceHolder to temporary stream
        System.IO.StringWriter stringWrite = new StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

        /********************************************************************************/
        //Try adding source strings for each image in content
        string tempPostContent = getImage(HTMLCode);
        /*********************************************************************************/

        StringReader reader = new StringReader(tempPostContent);

        //Create PDF document
        Document doc = new Document(PageSize.A4);
        HTMLWorker parser = new HTMLWorker(doc);
        PdfWriter.GetInstance(doc, new FileStream(FilePath,FileMode.Create));
        doc.Open();

        try
        {
            //Parse Html and dump the result in PDF file
            parser.Parse(reader);
        }
        catch (Exception ex)
        {
            //Display parser errors in PDF.
            Paragraph paragraph = new Paragraph("Error!" + ex.Message);
            Chunk text = paragraph.Chunks[0] as Chunk;
            if (text != null)
            {
                text.Font.Color = BaseColor.RED;
            }
            doc.Add(paragraph);
        }
        finally
        {
            doc.Close();
        }
    }

now after this i faced issue with images, to resolve those i have used the below code:

public string getImage(string input)
    {
        if (input == null)
            return string.Empty;
        string tempInput = input;
        string pattern = @"<img(.|\n)+?>";
        string src = string.Empty;
        HttpContext context = HttpContext.Current;

        //Change the relative URL's to absolute URL's for an image, if any in the HTML code.
        foreach (Match m in Regex.Matches(input, pattern, RegexOptions.IgnoreCase | RegexOptions.Multiline |

RegexOptions.RightToLeft))
        {
            if (m.Success)
            {
                string tempM = m.Value;
                string pattern1 = "src=[\'|\"](.+?)[\'|\"]";
                Regex reImg = new Regex(pattern1, RegexOptions.IgnoreCase | RegexOptions.Multiline);
                Match mImg = reImg.Match(m.Value);

                if (mImg.Success)
                {
                    src = mImg.Value.ToLower().Replace("src=", "").Replace("\"", "");

                    if (src.ToLower().Contains("http://") == false)
                    {
                        //Insert new URL in img tag
                        src = "src=\'" + GetWebUrl() + "/" + src.Substring(1, src.Length - 2) + "\'";
                        try
                        {
                            tempM = tempM.Remove(mImg.Index, mImg.Length);
                            tempM = tempM.Insert(mImg.Index, src);

                            //insert new url img tag in whole html code
                            tempInput = tempInput.Remove(m.Index, m.Length);
                            tempInput = tempInput.Insert(m.Index, tempM);
                        }
                        catch (Exception e)
                        {

                        }
                    }
                }
            }
        }
        return tempInput;
    }

    string getSrc(string input)
    {
        string pattern = "src=[\'|\"](.+?)[\'|\"]";
        System.Text.RegularExpressions.Regex reImg = new System.Text.RegularExpressions.Regex(pattern,
            System.Text.RegularExpressions.RegexOptions.IgnoreCase |

System.Text.RegularExpressions.RegexOptions.Multiline);
        System.Text.RegularExpressions.Match mImg = reImg.Match(input);
        if (mImg.Success)
        {
            return mImg.Value.Replace("src=", "").Replace("\"", ""); ;
        }

        return string.Empty;
    }

    public string GetWebUrl()
    {
        return HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) + HttpRuntime.AppDomainAppVirtualPath;
    }
hope this article will save your a lot time of R&D.