PadLeft Function

CREATE FUNCTION dbo.PadLeft

(@String varchar(100),

--Input string to be padded

@Length int,

--Length of final string

@PadChar char(1) --Padding character

) RETURNS varchar(100)

AS

BEGIN

WHILE LEN(@String + 'z') <= @Length

SET @String = @PadChar + @String

RETURN @String

END

GO

Store and retrieve file with SQL Server

// Store file in SQL Server

FileStream objFileStream = new FileStream("[Path of File]", FileMode.Open);
byte[] Data = new byte[objFileStream.Length];
objFileStream.Read(Data, 0, Convert.ToInt32(objFileStream.Length));

SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["My"].ToString());
objConnection.Open();
SqlCommand objCommand = new SqlCommand("Bytes_Insert");
objCommand.Connection = objConnection;
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add(new SqlParameter("@Data", Data));
objCommand.ExecuteNonQuery();
objConnection.Close();
objFileStream.Close();

// Retrieve file from SQL Server

SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["My"].ToString());
objConnection.Open();
SqlCommand objCommand = new SqlCommand("Bytes_ListAll");
objCommand.Connection = objConnection;
objCommand.CommandType = CommandType.StoredProcedure;

SqlDataAdapter adpt = new SqlDataAdapter(objCommand);
DataSet ds = new DataSet();
adpt.Fill(ds);

byte[] Data = (byte[]) ds.Tables[0].Rows[0]["Data"];
File.WriteAllBytes("[Path to store File]", Data);

Convert a string to Proper Case

Convert a string to Proper Case
===========================

// Use this Namespace
using System.Globalization;

// Code
string myString = "thIS is tHE sAmple tExt to shoW tHIs examPle !!";

TextInfo TI = new CultureInfo("en-US",false).TextInfo;

Response.Write (TI.ToTitleCase( myString ));

SQL Server function for Count the Occurrence of Character in the string

SQL Server function for Count the Occurrence of Character in the string
============================================================
-- Author : Milind Kansagara
-- Date : 30 July, 2007
-- Desc : Count the Occurrence of Character in the string
-- Usage : select dbo.GetOccurrence('Abhishek Joshi','a')

CREATE FUNCTION [dbo].[GetOccurrence] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

DECLARE @vInputLength INT
DECLARE @vIndex INT
DECLARE @vCount INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = LEN(@pInput)

WHILE @vIndex <= @vInputLength
BEGIN
IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchChar
SET @vCount = @vCount + 1

SET @vIndex = @vIndex + 1
END

RETURN @vCount

END

Use Index and Cursor in SQL Server 2000

Using Index
================
select *from ZIPCodes where StateName = 'New York'

-- Create Index
create nonclustered index idxStateName on ZIPCodes(StateName)
create nonclustered index idxZIPType on ZIPCodes(ZIPType)

-- Use Index
select *from ZIPCodes with(INDEX(idxZIPType)) where ZIPType = 'S'

-- List of Indexes on Perticular Table
exec sp_helpindex 'ps_client_master'

-- Drop Index
drop index ps_client_master.ps_client_master_Index_1

Using Cursors
===============

DECLARE @ClientID char(11)

declare cl CURSOR FOR
select int_id from ps_client_master

open cl

FETCH NEXT FROM cl into @ClientID
while @@FETCH_STATUS = 0
begin
print @ClientID
fetch next from cl into @ClientID
end

close cl
DEALLOCATE cl