Thursday 30 August 2012

Split by Delimiter In sqlserver


DECLARE @sInputList VARCHAR(8000)='7089:38:150'
,@sDelimiter VARCHAR(8000) = ':'
CREATE TABLE #List(ID INT IDENTITY(1,1),ITEM VARCHAR(1000))
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO #List SELECT @sItem
 END
IF LEN(@sInputList) > 0
   INSERT INTO #List SELECT @sInputList
------------------------------------------
SELECT * FROM #List
DROP TABLE #List

Sunday 1 July 2012

Search text from all PROCEDURE In sqlserver


SELECT DISTINCT object_name(id) FROM syscomments WHERE text LIKE '%Saved Successfully%' ORDER BY 1

Friday 20 April 2012

Find Min/Max Value FROM datatable


private void populateMaxOrMinValue(DataTable dtRef)
{
 if (dtRef.Rows.Count > 0)
        {
        //////////PICK THE MAX VALUE FROM DATATABLE USING ENUMERABLE
         maxValue = dtRef.AsEnumerable()
               .Where(ss => double.TryParse(ss["value"].ToString(), out val))
               .Select(ss => Convert.ToDouble(ss["value"])).Max();
         //////////PICK THE MIN VALUE FROM DATATABLE USING ENUMERABLE
         minValue = dtRef.AsEnumerable()
                .Where(ss => double.TryParse(ss["value"].ToString(), out val))
                .Select(ss => Convert.ToDouble(ss["value"])).Min();
         //////////////////////////////////////////////
 }
}

Tuesday 3 April 2012

Update Table by Joining In sqlserver

UPDATE X SET X.cloumnNanme = s.columnName ,X.cloumnNanme = s.cloumnNanme
FROM UpdatedTableName X INNER JOIN
ValueTableName s
ON(s.cloumnNanme = X.cloumnNanme)

Tuesday 13 March 2012

While loop in sqlserver

create table #tempWhile
(
[Id] int
,[name] nvarchar(200)
)
insert into #tempWhile values (1,'test1')
insert into #tempWhile values (3,'test3')
insert into #tempWhile values (4,'test4')
insert into #tempWhile values (5,'test5')
-----------------------------------
Declare @id nvarchar(200)
Declare @name nvarchar(200)
-----------------------------------
select Identity(int,1,1) id1, *
into #temp2
from #tempWhile ORDER BY id
----
Declare @count Int
Declare @LoopCount Int
set @count = @@rowcount
set @LoopCount = 1
-----
while @LoopCount <= @count
begin
select @id=id1,@name=name from #temp2 where id = @LoopCount
print 'Name: '+@name+' || ID: '+ @id
-------
set @LoopCount = @LoopCount + 1
end
--------------------------------
Drop table #tempWhile
Drop table #temp2

Sunday 19 February 2012

Captch In Asp.net

///////////////////////CaptchaImage.cs//////////////
using System;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using System.Drawing.Text;
namespace CaptchaImage
{
 public class CaptchaImage
 {
  public string Text
  {
   get { return this.text; }
  }
  public Bitmap Image
  {
   get { return this.image; }
  }
  public int Width
  {
   get { return this.width; }
  }
  public int Height
  {
   get { return this.height; }
  }
  private string text;
  private int width;
  private int height;
  private string familyName;
  private Bitmap image;
  private Random random = new Random();
  public CaptchaImage(string s, int width, int height)
  {
   this.text = s;
   this.SetDimensions(width, height);
   this.GenerateImage();
  }
  public CaptchaImage(string s, int width, int height, string familyName)
  {
   this.text = s;
   this.SetDimensions(width, height);
   this.SetFamilyName(familyName);
   this.GenerateImage();
  }
  ~CaptchaImage()
  {
   Dispose(false);
  }
  public void Dispose()
  {
   GC.SuppressFinalize(this);
   this.Dispose(true);
  }
  protected virtual void Dispose(bool disposing)
  {
   if (disposing)
    this.image.Dispose();
  }
  private void SetDimensions(int width, int height)
  {
   if (width <= 0)
    throw new ArgumentOutOfRangeException("width", width, "Argument out of range, must be greater than zero.");
   if (height <= 0)
    throw new ArgumentOutOfRangeException("height", height, "Argument out of range, must be greater than zero.");
   this.width = width;
   this.height = height;
  }
  private void SetFamilyName(string familyName)
  {
   try
   {
    Font font = new Font(this.familyName, 12F);
    this.familyName = familyName;
    font.Dispose();
   }
   catch (Exception ex)
   {
    this.familyName = System.Drawing.FontFamily.GenericSerif.Name;
   }
  }
  private void GenerateImage()
  {
   Bitmap bitmap = new Bitmap(this.width, this.height, PixelFormat.Format32bppArgb);
   Graphics g = Graphics.FromImage(bitmap);
   g.SmoothingMode = SmoothingMode.AntiAlias;
   Rectangle rect = new Rectangle(0, 0, this.width, this.height);
   HatchBrush hatchBrush = new HatchBrush(HatchStyle.SmallConfetti, Color.LightGray, Color.White);
   g.FillRectangle(hatchBrush, rect);
   SizeF size;
   float fontSize = rect.Height + 1;
   Font font;
   do
   {
    fontSize--;
    font = new Font(this.familyName, fontSize, FontStyle.Bold);
    size = g.MeasureString(this.text, font);
   } while (size.Width > rect.Width);
   StringFormat format = new StringFormat();
   format.Alignment = StringAlignment.Center;
   format.LineAlignment = StringAlignment.Center;
   GraphicsPath path = new GraphicsPath();
   path.AddString(this.text, font.FontFamily, (int) font.Style, font.Size, rect, format);
   float v = 4F;
   PointF[] points =
   {
    new PointF(this.random.Next(rect.Width) / v, this.random.Next(rect.Height) / v),
    new PointF(rect.Width - this.random.Next(rect.Width) / v, this.random.Next(rect.Height) / v),
    new PointF(this.random.Next(rect.Width) / v, rect.Height - this.random.Next(rect.Height) / v),
    new PointF(rect.Width - this.random.Next(rect.Width) / v, rect.Height - this.random.Next(rect.Height) / v)
   };
   Matrix matrix = new Matrix();
   matrix.Translate(0F, 0F);
   path.Warp(points, rect, matrix, WarpMode.Perspective, 0F);
   hatchBrush = new HatchBrush(HatchStyle.DarkDownwardDiagonal, Color.Gray, Color.DarkGray);
   g.FillPath(hatchBrush, path);
   int m = Math.Max(rect.Width, rect.Height);
   for (int i = 0; i < (int) (rect.Width * rect.Height / 30F); i++)
   {
    int x = this.random.Next(rect.Width);
    int y = this.random.Next(rect.Height);
    int w = this.random.Next(m / 50);
    int h = this.random.Next(m / 50);
    g.FillEllipse(hatchBrush, x, y, w, h);
   }
   font.Dispose();
   hatchBrush.Dispose();
   g.Dispose();
   this.image = bitmap;
  }
 }
}
//////////////////////////////////////////////
///////////////////////captchaCreate.aspx.cs//////////////
protected void Page_Load(object sender, EventArgs e)
    {
        CaptchaImage.CaptchaImage ci = new CaptchaImage.CaptchaImage(Session["CaptchaImageText"].ToString(), 180, 40, "Century Schoolbook");
        this.Response.Clear();
        this.Response.ContentType = "image/jpeg";
        ci.Image.Save(Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);
        ci.Dispose();
    }
//////////////////////////////////////////////
///////////////////////valiadateCaptcha.aspx.cs//////////////
 private Random random = new Random();
    private string GenerateRandomCode()
    {
        string s = "";
        for (int i = 0; i < 6; i++)
        s = String.Concat(s, this.random.Next(12).ToString());
        return s;
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.Request.Form["mode"].ToString() == "validate")
        {
            if (Page.Request.Form["text"].ToString() == Session["CaptchaImageText"].ToString())
            {
                Response.Write("y");
            }
            else
            {
                Response.Write("n");
            }
        }
        else if (Page.Request.Form["mode"].ToString() == "change")
        {
            Session["CaptchaImageText"] = GenerateRandomCode();
        }
    }
//////////////////////////////////////////////
///////////////////////DoCallBack.js//////////////
function processReqChange()
{
    if(req.readyState==4){
    if(req.status==200){
    eval(what)
    }else{
    alert("There was a problem retrieving the XML data:\n"+req.responseText)
    }
    }
}
    function DoCallback(a,b){
    if(window.XMLHttpRequest)
    {  
    req=new XMLHttpRequest;req.onreadystatechange=processReqChange;req.open("POST",url,false);
    req.setRequestHeader("Content-Type","application/x-www-form-urlencoded");req.send(a);
    var c=req.responseText.substring(0,1);
    document.getElementById(b).innerHTML=c}
    else if(window.ActiveXObject)
    {
    req=new ActiveXObject("Microsoft.XMLHTTP");
    if(req){
    req.onreadystatechange=processReqChange;
    req.open("POST",url,false);
    req.setRequestHeader("Content-Type","application/x-www-form-urlencoded");req.send(a);
    var c=req.responseText.substring(0,1);
    document.getElementById(b).innerHTML=c;
    }
    }
}
//////////////////////////////////////////////
///////////////////////Default.aspx//////////////
<script src="js/DoCallBack.js" type="text/javascript"></script>
    <script src="js/jquery-1.6.4.min.js" type="text/javascript"></script>
    <div class="top-bar">
        <h1>
            Example : Create Captcha By asp.net</h1>
    </div>
    <div style="width: 100%">
        <div style="width: 300px; padding-left: 20px;">
            Enter Captcha Text
            <input id="txtCaptcha" type="text" style="width: 70%" runat="server" />
        </div>
        <div style="height: 10px">
            &nbsp;
        </div>
        <div style="width: 300px; padding-left: 20px; height: 50px">
            <div style="float: left; width: 180px; height: 40px">
                <img src="captchaCreate.aspx" id="imgcap" />
            </div>
            <div style="float: left; padding-left: 10px; padding-top: 10px;">
                <img src="images/captchaRefress.ico" alt="loading.." style="height: 16px; width: 16px;
                    cursor: pointer" onclick="reloadCaptcha();" />
            </div>
            <div style="clear: both">
            </div>
        </div>
        <div style="width: 300px; padding-left: 20px;">
            <input id="btnValidate" type="button" value="Validate Captcha" onclick="validateCaptcha();" />
        </div>
        <div id="spanValidUserName" style="display: none">
        </div>
    </div>
    <script language="javascript" type="text/javascript">
        function reloadCaptcha() {
            url = "valiadateCaptcha.aspx";
            what = "";
            DoCallback("mode=change", "spanValidUserName");
            document.getElementById('imgcap').src = "captchaCreate.aspx?i=" + Math.floor(Math.random() * 11);
            return false;
        }
        function validateCaptcha() {
            var a = document.getElementById("<%=txtCaptcha.ClientID %>").value;
            if (a == "") {
                alert("Please enter captcha text!");
                return false;
            }
            url = "valiadateCaptcha.aspx";
            what = "";
            DoCallback("mode=validate&text=" + a, "spanValidUserName");
            if (document.getElementById('spanValidUserName').innerHTML == "y") {
                alert("correct captcha ");
            }
            else {
                alert("Please enter correct captcha !");
            }
            return false;
        }
    </script>
//////////////////////////////////////////////
///////////////////////Default.aspx.cs//////////////
 private Random random = new Random();
    private string GenerateRandomCode()
    {
        string s = "";
        for (int i = 0; i < 6; i++)
            s = String.Concat(s, this.random.Next(12).ToString());
        return s;
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            Session["CaptchaImageText"] = GenerateRandomCode();
        else
        {
        }
    }
///////////////////////////////////////////////

Monday 9 January 2012

Generate Random Password

-----RandomPasswordCrate.cs
public class RandomPasswordCrate
{
public static string GeneratePassword(int PasswordLength)
{
string _allowedChars = "abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ0123456789_";
Random randNum = new Random();
char[] chars = new char[PasswordLength];
int allowedCharCount = _allowedChars.Length;
for (int i = 0; i < PasswordLength; i++)
{
chars[i] = _allowedChars[(int)((_allowedChars.Length) * randNum.NextDouble())];
}
return new string(chars);
}
}
------In cs Page
protected void Page_Load(object sender, EventArgs e)
{
generatePassword();
}
private void generatePassword()
{
string password = "Your password is: " + RandomPasswordCrate.GeneratePassword(8);
}

Get all non-clustered indexes

DECLARE cIX CURSOR FOR     SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID         FROM Sys.Indexes SI             ...