How to search all tables in a SQL Server Database for a specific value

check this site: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Here is the complete stored procedure code:

 CREATE PROC SearchAllTables (  @SearchStr nvarchar(100) ) AS BEGIN  
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. 
-- Purpose: To search all columns of all tables for a given search string 
-- Written by: Narayana Vyas Kondreddi 
-- Site: http://vyaskn.tripod.com 
-- Tested on: SQL Server 7.0 and SQL Server 2000 
-- Date modified: 28th July 2002 22:50 GMT   
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))  
SET NOCOUNT ON  
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) 
SET  @TableName = '' 
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')  
WHILE @TableName IS NOT NULL 
BEGIN   SET @ColumnName = ''
 SET @TableName =    (    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
 FROM  INFORMATION_SCHEMA.TABLES
 WHERE   TABLE_TYPE = 'BASE TABLE'
 AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
 AND OBJECTPROPERTY(       OBJECT_ID(        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
 ), 'IsMSShipped'              ) = 0   )
 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
 BEGIN
 SET @ColumnName =    (
 SELECT MIN(QUOTENAME(COLUMN_NAME))
 FROM  INFORMATION_SCHEMA.COLUMNS
 WHERE   TABLE_SCHEMA = PARSENAME(@TableName, 2)
 AND TABLE_NAME = PARSENAME(@TableName, 1)
    AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
 AND QUOTENAME(COLUMN_NAME) > @ColumnName    )
 IF @ColumnName IS NOT NULL    BEGIN     INSERT INTO #Results
 EXEC     (      'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
 FROM ' + @TableName + ' (NOLOCK) ' +      ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2     )
 END   END   END   SELECT ColumnName, ColumnValue FROM #Results
 END  

How to search all tables in a SQL Server Database for a specific value

Download file HttpHandler

public class getfile : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            try
            {
                int fileId;
                int.TryParse(context.Request.QueryString[Timeline.Constants.QUERYSTRING_FILEID], out fileId);
                MyFileClass file = new MyFileClass(fileId); //retrieve file info

                context.Response.Clear();
                context.Response.ContentType = "application/octet-stream";

                byte[] fileSize = File.ReadAllBytes(file.PhysicalPath);
                context.Response.AddHeader("Content-Length", fileSize.Length.ToString());
                fileSize = null;
                context.Response.AppendHeader("Content-Disposition", string.Format("attachment;filename={0}", file.Filename));
                context.Response.WriteFile(file.PhysicalPath);
                context.ApplicationInstance.CompleteRequest();
            }
            catch (Exception ex)
            {
                context.Response.Write(ex.Message);
            }

            context.Response.End();
        }

        public bool IsReusable
        {
            get
            {
                return true;
            }
        }
    }

Download file HttpHandler

How to get the type of a System.__ComObject in COM Interop

string typename = Microsoft.VisualBasic.Information.TypeName(yourobject);

note: remeber to add the Microsoft.VisualBasic in your reference

How to get the type of a System.__ComObject in COM Interop

How to retrieve Membership's PasswordAnswer

in .net's membership framework, as long as the provider's password format is set to "Encrypted" or "Clear" then developers will be able to retrieve and decode the password. that's ok. but, interestingly, membership does not provide a method to retrieve the "Password Answer" (the answer to the security question when resetting a user's password). one thing to note is that, the PasswordAnswer is encoded using the same algorithm used for the password encryption, thus, if we can decrypt the password then we could probably decrypt the PassswordAnswer using the same function that is being used to decrypt the password(i hope i still make sense). with this, a visit to your friendly reflector will show you MembershipProvider's DecryptPassword and UnEncodePassword methods. This will be all you'll be needing to be able to create your own method of retrieving the PasswordAnswer. The trick is to create a class that derives from the MembershipProvider class to be able to use the DecryptPassword(protected) and check out the implementation of the UnEncodePassword to be able to decode the byte[] returned by DecryptPassword. below is a sample implementation:

public class FalseMembershipProvider : MembershipProvider
{
#region Required Override Properties/Methods
 ...implement required properties/methods here
#endregion

///
/// Retrieves the Password Answer - wfp june 04 2009
///
///
///
public string GetPasswordAnswer(Guid providerUserKey)
{
    Microsoft.Practices.EnterpriseLibrary.Data.Database db = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase();
    using (System.Data.Common.DbCommand cmd = db.GetSqlStringCommand("SELECT PasswordAnswer FROM aspnet_Membership WHERE UserID=@UserID"))
    {
        db.AddInParameter(cmd, "@UserId", DbType.Guid, providerUserKey);

        object answer = db.ExecuteScalar(cmd);
        if (answer != null)
        return ProviderDecryptor(answer.ToString());
        else
        return null;
    }
    db = null;
}

///
/// Generic Decryptor function. Can be used to decrypt Password and Password Answer.
/// Only works if passwordFormat is set to "Encrypted" - wfp june 04 2009
///
///
///
internal string ProviderDecryptor(string encryptedText)
{
    string decrypted = null;

    if (!string.IsNullOrEmpty(encryptedText))
    {
        byte[] encodedbytes = Convert.FromBase64String(encryptedText);
        byte[] decryptedbytes = base.DecryptPassword(encodedbytes);

        if (decryptedbytes != null)
        decrypted = System.Text.Encoding.Unicode.GetString(decryptedbytes, 16, decryptedbytes.Length - 16);
    }

    return decrypted;
}
}

How to retrieve Membership's PasswordAnswer

Resizing image on the fly!

public partial class Thumbnail : System.Web.UI.Page
{
 const string Q_IMG = "img";
 const string Q_WIDTH = "w";
 const string Q_HEIGHT = "h";

 const int DEFAULT_WIDTH = 120;
 const int DEFAULT_HEIGHT = 118;

 int Thumbnail_Width
 {
     get
     {
         if (Request.QueryString[Q_WIDTH] != null)
         return Convert.ToInt32(Request.QueryString[Q_WIDTH]);
         else
         return DEFAULT_WIDTH;
     }
 }

 int Thumbnail_Height
 {
     get
     {
         if (Request.QueryString[Q_HEIGHT] != null)
         return Convert.ToInt32(Request.QueryString[Q_HEIGHT]);
         else
         return DEFAULT_HEIGHT;
     }
 }

 ///
 /// just a dummy delegate for the required GetThumbnailImageAbort delegate
 ///
 ///
 public bool ImgAbort_Callback()
 {
     return false;
 }


 protected void Page_Load(object sender, EventArgs e)
 {
     if (!IsPostBack)
     {
         if (Request.QueryString[Q_IMG] != null)
         {
             try
             {
                 using (System.Drawing.Image img = System.Drawing.Image.FromFile(Server.MapPath(Request.QueryString[Q_IMG])))
                 {
                     using (System.Drawing.Image thumb = img.GetThumbnailImage(
                     this.Thumbnail_Width,
                     this.Thumbnail_Height,
                     new System.Drawing.Image.GetThumbnailImageAbort(ImgAbort_Callback),
                     IntPtr.Zero))
                     {
                         Response.ContentType = "image/jpeg";
                         thumb.Save(Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
                     }
                 }
             }
             catch { }
         }
     }
 }
}

Resizing image on the fly!

Globalization and Localization in ASP.Net 2.0

I would really love to write about this topic that my drool flooded my laptop and broke it. I just implemented this on my new project and its working perfectly compared to the one our company is currently using. So without further ado, i would like to present...............the link, everything you need to know is already in there.

Globalization and Localization in ASP.Net 2.0

Simple Delegate and Event Implementation Sample

class definition:

public class DelegateAndEvent
{
 //it is a good practice to name your delegates with "EventHandler"
 public delegate void ErrorEventHandler(DelegateAndEvent sender, ErrorEventArgs args);

 //declare event
 public event ErrorEventHandler ErrorRaised;

 //a better way of returning values is to create a class which inherits the EventArgs class and add the
 //desired property(ies)
 public class ErrorEventArgs : EventArgs
 {
     private string _errmsg;
     public string ErrorMessage
     {
         get { return _errmsg; }
         set { _errmsg = value; }
     }

     public ErrorEventArgs() { }
     public ErrorEventArgs(string error)
     {
         _errmsg = error;
     }
 }

 /// Method to allow raising of error (can either be public or private)
 public void RaiseError(string error)
 {
     if (!string.IsNullOrEmpty(error))
     {
         //check is event is not null (event has a subscriber)
         if (ErrorRaised != null)
         {
             ErrorRaised(this, new ErrorEventArgs(error));
         }
     }

 }
}


code behind:


public partial class _Default : System.Web.UI.Page
{
 DelegateAndEvent dne = new DelegateAndEvent();

 protected override void OnInit(EventArgs e)
 {
     base.OnInit(e);
     dne.ErrorRaised += new DelegateAndEvent.ErrorEventHandler(dne_ErrorRaised);
 }

 protected void Page_Load(object sender, EventArgs e)
 {
 }

 protected void btnEvent_Click(object sender, EventArgs e)
 {
     dne.RaiseError("Error was forced to be raised!");

 }

 void dne_ErrorRaised(DelegateAndEvent sender, DelegateAndEvent.ErrorEventArgs args)
 {
     lblError.Text = args.ErrorMessage;
 }
}

Simple Delegate and Event Implementation Sample