Hate Code: May 2012

Sunday 27 May 2012

How to install windows Service?

http://msdn.microsoft.com/en-us/library/zt39148a

Wednesday 16 May 2012

Sharepoint adding an item into a Picture Library

private byte [] StreamFile(string filename)
{
FileStream fs = new FileStream(filename, FileMode.Open,FileAccess.Read);
// Create a byte array of file stream length
byte[] ImageData = new byte[fs.Length];
//Read  block of bytes from stream into the byte array
fs.Read(ImageData,0,System.Convert.ToInt32(fs.Length));
//Close the File Stream
fs.Close();
return ImageData;
}

// then use the following to add the file to the list
list.RootFolder.Files.Add(fileName, StreamFile(fileName));

Retrieve the current username for an FBA user in IP2010

have to use fully trusted forms...might as well use administrator approved forms as well so you dont have to mess with certificates...and use some code like this:

int index = 0;
userName = (this.Application.User.LoginName);
if (userName.Contains("sqlmembershipprovider"))

{
index = userName.IndexOf("sqlmembershipprovider|") + 22;
userName = userName.Substring(index, userName.Length - index);
}

Monday 14 May 2012

Exceptions-log4net

log4 net
-----------------------------------------------------
config settings---

<configSections>
<sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
<section name="MonitorVendors.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" />
</sectionGroup>

<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,Log4net"/>
</configSections>

<log4net>
<root>
<level value="DEBUG" />
<appender-ref ref="LogFileAppender" />
</root>
<appender name="LogFileAppender" type="log4net.Appender.RollingFileAppender" >
<param name="File" value="C:\log.txt" />
<param name="AppendToFile" value="true" />
<rollingStyle value="Size" />
<maxSizeRollBackups value="10" />
<maximumFileSize value="2MB" />
<staticLogFileName value="true" />
<layout type="log4net.Layout.PatternLayout">
<param name="ConversionPattern" value="%-5p%d{yyyy-MM-dd hh:mm:ss} – %m%n" />
</layout>
</appender>
</log4net>

private static readonly ILog LogExamp = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
--------------------------------------------------------------------------

Sunday 13 May 2012

Excel

Excel.Application excelApp = default(Excel.Application);
Excel.Workbook excelWorkbook = default(Excel.Workbook);
Excel.Worksheet excelWorksheet = default(Excel.Worksheet);
excelApp = new Excel.Application();
excelWorkbook = excelApp.Workbooks.Open(bbi, 0,
true,
5,
"",
"",
true,
Excel.XlPlatform.xlWindows,
"t",
false,
false,
0,
true,
1,
0);
excelWorksheet = (Excel.Worksheet)excelWorkbook.ActiveSheet;
Excel.Sheets shts = (Excel.Sheets)excelWorkbook.Sheets;
int startingrowId = 23;
foreach (Excel.Worksheet sheet1 in shts)
{
excelWorksheet = (Excel.Worksheet)sheet1;

switch (excelWorksheet.Name)
{
case "***": Samplemethod();
break;
case Samplemethod2: continue;
}
}
}

---------------------------------------------------------------------

object columnA = 2;
object columnB = 3;
object columnC = new object();
object columnD = new object();
object columnE = 11;
while (((Excel.Range)excelWorksheet.Cells[startingRowId, columnA]).Value2 != null)
{
try
{
string description = ((Excel.Range)excelWorksheet.Cells[startingRowId, columnA]).Value2.ToString();
string ppString = ((Excel.Range)excelWorksheet.Cells[startingRowId, columnB]).Value2.ToString();
if (((Excel.Range)excelWorksheet.Cells[startingRowId, columnD]).Value2 != null)
{
}
startingRowId++;
}
--------------------------------------------------------------------------------------

public static void readExcelfile()
{
Excel.Application excelApp = default(Excel.Application);
Excel.Workbook excelWorkbook = default(Excel.Workbook);
Excel.Worksheet excelWorksheet = default(Excel.Worksheet);
excelApp = new Excel.ApplicationClass();

//excelWorkbook = excelApp.Workbooks.Open(@"C:Tempsample.xls", 0,
excelWorkbook = excelApp.Workbooks.Open(@"C:Tempsample.xls", 0,
true,
5,
"",
"",
true,
Excel.XlPlatform.xlWindows,
"t",
false,
false,
0,
true,
1,
0);
excelWorksheet = (Excel.Worksheet)excelWorkbook.ActiveSheet;
int index = 0;
int i = 1;
// This row,column index should be changed as per your need.
// i.e. which cell in the excel you are interesting to read.
object rowIndex = 1;
object colIndex1 = 1;
object colIndex2 = 2;
try
{
while (((Excel.Range)excelWorksheet.Cells[rowIndex, colIndex1]).Value2 != null)
{
//rowIndex = 1+index;
string firstName = ((Excel.Range)excelWorksheet.Cells[rowIndex, colIndex1]).Value2.ToString();
string lastName = ((Excel.Range)excelWorksheet.Cells[rowIndex, colIndex2]).Value2.ToString();
Console.WriteLine("Name : {0},{1} ", firstName, lastName);
rowIndex = 1 + i;
i++;
index++;
}
}
catch (Exception ex)
{
excelApp.Quit();
Console.WriteLine(ex.Message);
}
}

--------------------------------------------------------

http://support.microsoft.com/kb/302084
http://vb.net-informations.com/excel-2007/vb.net_excel_2007_open_file.htm
http://exceldatareader.codeplex.com/
http://www.codeproject.com/KB/cs/Excel_Application_in_C_.aspx
http://stackoverflow.com/questions/193092/c-how-to-add-excel-worksheet-programatically-office-xp-2003

parse csv

private static List<string[]> parseCSVFile(string path)
{
List<string[]> parsedData = new List<string[]>();
List<string> parseInfo = new List<string>();
bool readFirstLine = false;
if (File.Exists(path))
{
StreamReader readFile = new StreamReader(path);
string eachLine;
string[] eachRow;
while ((eachLine = readFile.ReadLine()) != null)
{
if (readFirstLine)
{
eachRow = eachLine.Split(new char[] { ',', '\'' }, StringSplitOptions.RemoveEmptyEntries);
parsedData.Add(eachRow);
}
else
{
readFirstLine = true;
}
}
foreach (string[] str in parsedData)
parseInfo.Add(str[0]);
}
else
{
Log.Error("File does not Exist");
}
return parsedData;
}

custom validator

protected void checkbutnclick(object source,ServerValidateEventArgs args)
{
if (btnB == false )
{
CustomValidatorClick.IsValid = false;
args.IsValid = false;
}
}

Db Connection mysql

----------------------------------------------------------
using (NpgsqlConnection npgSqlCon = new NpgsqlConnection(connectionString))
{
npgSqlCon.Open();
using (NpgsqlCommand npgsqlCmd = new NpgsqlCommand(query, npgSqlCon))
{
try
{
npgsqlCmd.Parameters.Add(new NpgsqlParameter("icu", NpgsqlDbType.Integer));
npgsqlCmd.Parameters[0].Value = 1;
npgsqlCmd.Prepare();
npgsqlCmd.ExecuteNonQuery();
}
--------------------------------------------------
public class FTPCLient
{

private string ftpIP, ftpLogin, ftpPAssword;

private FTPCLient() { }
public FTPCLient(string ip, string login, string pwd)
{

string _ip = "";
IPHostEntry Host = Dns.GetHostEntry(ip);
try
{
foreach (var addr in Host.AddressList)
{
if (addr.AddressFamily == System.Net.Sockets.AddressFamily.InterNetwork)
{
_ip = addr.ToString();
}
}
}
catch
{
_ip = "";
}
if (!string.IsNullOrEmpty(_ip))
ftpIP = _ip;
else
ftpIP = ip;
ftpLogin = login;
ftpPAssword = pwd;

}

public void deleteFromFTP(string targetPath)
{
var req = (FtpWebRequest)WebRequest.Create(ftpIP + "/" + targetPath);
req.Proxy = null;
req.Credentials = new NetworkCredential(ftpLogin, ftpPAssword);
req.UsePassive = true;
req.UseBinary = true;
req.KeepAlive = false;
req.Method = WebRequestMethods.Ftp.DeleteFile;
req.GetResponse().Close(); // Perform the deletion
}

public void UploadToFTP(string localPath, string targetPath)
{

string[] pathSep = targetPath.Split(new char[] { '/' });
StringBuilder stbNewPath = new StringBuilder();
foreach (string s in pathSep)
{
if (!s.Contains("."))
{
if (stbNewPath.Length < 0)
stbNewPath.Append(s);
else
{
stbNewPath.Append("/" + s);
}
}
}

if (stbNewPath.Length > 0)
{
if (!this.CheckDirectory(stbNewPath.ToString()))
{
MakeDirectory(stbNewPath.ToString());
}
}

string s1 = localPath;
byte[] releaseData = File.ReadAllBytes(s1);
FileStream h=File.OpenRead(localPath);

FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create(ftpIP + "/" + targetPath);
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential(ftpLogin, ftpPAssword);
request.UsePassive = true;
request.UseBinary = true;
request.KeepAlive = false;
Stream reqStream = request.GetRequestStream();
int uploadDataCount = releaseData.Length;

int beginOffset = 0;
while (uploadDataCount > 0)
{
if (uploadDataCount >= 2048)
reqStream.Write(releaseData, beginOffset, 2048);
else
reqStream.Write(releaseData, beginOffset, uploadDataCount);
beginOffset += 2048;
uploadDataCount -= 2048;
}
reqStream.Close();
}

public void UploadToFTPString(string data, string targetPath)
{

string[] pathSep = targetPath.Split(new char[] { '/' });
StringBuilder stbNewPath = new StringBuilder();
foreach (string s in pathSep)
{
if (!s.Contains("."))
{
if (stbNewPath.Length < 0)
stbNewPath.Append(s);
else
{
stbNewPath.Append("/" + s);
}
}
}

if (stbNewPath.Length > 0)
{
if (!this.CheckDirectory(stbNewPath.ToString()))
{
MakeDirectory(stbNewPath.ToString());
}
}

Byte[] releaseData = File.ReadAllBytes(data);// ms.ToArray();

FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create("ftp://" + ftpIP + "/" + Uri.EscapeDataString( targetPath ));
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential(ftpLogin, ftpPAssword);
request.UsePassive = true;
request.UseBinary = true;
request.KeepAlive = false;
using (Stream reqStream = request.GetRequestStream())
{
int uploadDataCount = releaseData.Length;

int beginOffset = 0;
while (uploadDataCount > 0)
{
if (uploadDataCount >= 2048)
reqStream.Write(releaseData, beginOffset, 2048);
else
reqStream.Write(releaseData, beginOffset, uploadDataCount);
beginOffset += 2048;
uploadDataCount -= 2048;
}
reqStream.Close();
}
}

public void DownloadFromFTP(string localPath, string targetPath)
{

FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create(ftpIP + "/" + targetPath);
request.Method = WebRequestMethods.Ftp.DownloadFile;
request.Credentials = new NetworkCredential(ftpLogin, ftpPAssword);
request.UsePassive = true;
request.UseBinary = true;
request.KeepAlive = false;
FtpWebResponse response = (FtpWebResponse)request.GetResponse();
Stream ftpStream = response.GetResponseStream();

long cl = response.ContentLength;
int bufferSize = 2048;
int readCount;
byte[] buffer = new byte[bufferSize];
MemoryStream memory = new MemoryStream();
readCount = ftpStream.Read(buffer, 0, bufferSize);
while (readCount > 0)
{
memory.Write(buffer, 0, readCount);
readCount = ftpStream.Read(buffer, 0, bufferSize);
}

ftpStream.Close();
memory.Close();
response.Close();
byte[] filedata = memory.ToArray();

File.WriteAllBytes(localPath, filedata);
}

private bool CheckDirectory(string dirName)
{

try
{
FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create(string.Format("ftp://{0}/{1}", ftpIP, dirName));
request.Method = WebRequestMethods.Ftp.ListDirectory;
request.Credentials = new NetworkCredential(ftpLogin, ftpPAssword);
request.UsePassive = true;
request.UseBinary = true;
request.KeepAlive = false;

using (FtpWebResponse response = (FtpWebResponse)request.GetResponse())
{
return true;
}
}
catch (WebException ex)
{
if (ex.Response != null)
{
FtpWebResponse response = (FtpWebResponse)ex.Response;
if (response.StatusCode == FtpStatusCode.ActionNotTakenFileUnavailable)
{
return false;
}
}
}
return false;
}

public bool MakeDirectory(string dirName)
{

try
{
FtpWebRequest request = (FtpWebRequest)FtpWebRequest.Create(string.Format("ftp://{0}/{1}", ftpIP, dirName));
request.Method = WebRequestMethods.Ftp.MakeDirectory;
request.Credentials = new NetworkCredential(ftpLogin, ftpPAssword);
request.UsePassive = true;
request.UseBinary = true;
request.KeepAlive = false;

using (FtpWebResponse response = (FtpWebResponse)request.GetResponse())
{
return true;
}
}
catch (WebException ex)
{
if (ex.Response != null)
{
FtpWebResponse response = (FtpWebResponse)ex.Response;
if (response.StatusCode == FtpStatusCode.ActionNotTakenFileUnavailable)
{
return false;
}
}
}
return false;
}
}
}
-------------------------------------------------
public void NewFTPFolder(string newSiteName)
{
try
{
string ftpaddress = "";
string ftpusername = "";
string ftppwd = "";
FTPCLient ftpClient = new FTPCLient(ftpaddress,ftpusername,ftppwd);
string folderPath = "hello";
DirectoryInfo srcDirectoryInfo = new DirectoryInfofolderPath
string ftppth = "/";
CopyFilesToFTP(srcDirectoryInfo, ftppth, ftpClient);
}
catch (Exception error)
{
Log.Error(error.Message);
}
}

--------------------------------
public void CopyFilesToFTP(DirectoryInfo srcDirectoryInfo, string ftppath, FTPCLient ftpClient)
{
try
{
foreach (FileInfo fi in srcDirectoryInfo.GetFiles())
{
string targetFtpPath = ftppath + "/" + fi.Name;
ftpClient.UploadToFTPString(fi.FullName, targetFtpPath);
}
foreach (DirectoryInfo di in srcDirectoryInfo.GetDirectories())
{
string subdirftppath = ftppath + "/" + di.Name;
ftpClient.MakeDirectory(subdirftppath);
CopyFilesToFTP(di, subdirftppath, ftpClient);
}
}
catch (Exception error)
{
Log.Error(error.Message);
}
}
}
----------------------------------------------

Thursday 3 May 2012

SharePoint: Don’t Forget to Uninstall the Feature

A question came up today that I thought was interesting and I hadn’t fully examined before.

If you retract and delete a solution in SharePoint 2007 without first uninstalling its features, are turds left behind?

The short answer: If you retract and delete a solution without deactivating and uninstalling the feature for each deployed scope, then turds are left behind both in the file system and in the config database.

To demonstrate this, I created a simple ItemEventReceiver, which uses a feature that is scoped to the web level. I deploy the solution to a web application, and then it is available to the individual web sites to activate. The code for this is unimportant, it’s just a solution that contains a feature scoped to the web level. Here are the commands that I run to add my solution to the solution store, deploy it to a web application, and activate the feature for a web site within the web application.

stsadm -o addsolution -filename featuretester.wsp
stsadm -o deploysolution -name featuretester.wsp -allowGacDeployment -immediate -url http://moss-1
stsadm -o execadmsvcjobs
stsadm -o activatefeature -id fb9375d5-07ae-4835-b64c-83441c806691 -url http://moss-1

This is pretty standard stuff. You probably already know that this creates a folder in the SharePoint root for our feature in the 12/Template/Features folder.

This also deploys our assembly to the Global Assembly Cache.

I wonder, though, does the feature exist only in the file system, or is it now part of the configuration database? We check the configuration database, and yep… there’s an entry in the dbo.Features table for our feature.

Let’s see what happens if we activate our feature for a new web.

stsadm -o activatefeature -id fb9375d5-07ae-4835-b64c-83441c806691 -url http://moss-1/sitedirectory/team

As we expected, there are now two entries in the dbo.Features table.

Cleaning It Up The Right Way

Cleaning this up the right way is simple, we reverse all of our steps until now. We need to deactivate the feature for each scope that we activated it for.

stsadm –o deactivatefeature –id fb9375d5-07ae-4835-b64c-83441c806691 -url http://moss-1
stsadm –o deactivatefeature –id fb9375d5-07ae-4835-b64c-83441c806691 -url http://moss-1/sitedirectory/team
stsadm –o uninstallfeature –id fb9375d5-07ae-4835-b64c-83441c806691

This will remove the entries from the configuration database and remove the entries from the file system in a supported manner.

What Happens if I Don’t Clean It Up The Right Way?

OK, now our real question. Imagine we are still at the point where everything is deployed and activated. We now have artifacts in the configuration database, and we have artifacts in the file system for every server in our farm. What happens if we retract the solution and delete it? Are turds left behind in the database and file system, or does SharePoint get rid of them for us?

stsadm -o retractsolution -name featuretester.wsp -immediate -url http://moss-1
stsadm -o exeadmsvcjobs
stsadm -o deletesolution -name featuretester.wsp

Here, we are retracting the solution from the web application and then deleting the solution from the solution store. Let’s go check our artifacts on the server again to see what remains.

First, we look in the file system. Hmm… the files are still there.

I know what you’re thinking, “so what? they’re just files and folders.” Fair enough, simply adding folders and files to the SharePoint root doesn’t do anything unless we tell SharePoint about it using an stsadm command. Let’s go look at the Global Assembly Cache.

As you can see, the entry for our assembly is no longer in the GAC. When we deleted the solution, the assembly was removed as part of the process (just as it is added when you call addsolution).

OK, so far it left behind files and folders, removed an entry from the GAC… is it clean? Well, no. SharePoint still has pointer records to the features in the configuration database.

At this point you may be thinking, “so what? it left behind turds, but my farm is still running ok.” Effectively, all we did was remove the DLL from the GAC and the WSP from the solution store. The individual web sites still think there’s an ItemEventReceiver deployed. That’s why we find the following types of errors when we go to a list where an ItemEventReceiver was bound

02/02/2011 22:40:54.78 w3wp.exe (0x193C) 0x1EDC Windows SharePoint Services General 75fe High Failed to determine definition for Feature with ID 'fb9375d5-07ae-4835-b64c-83441c806691'. Skipping this feature for element querying consideration.

What’s happening is that there is a feature that is orphaned because its implementation is missing. It shows up in the ULS log to let us know that it thought there was a feature deployed here, but something’s gone wrong.

Let’s look at one more thing… upgrading. After SP2 for SharePoint 2007, a new command was added to stsadm to check for potential upgrade issues. We run the command:

stsadm -o preupgradecheck

This is a benign operation, meaning it makes no changes to your farm at all. It simply runs through a list of known issues that occur when upgrading from SharePoint 2007 to SharePoint 2010 and validates your farm. You can see in the command window output that we have one error, colored red.

After the tool is run, it generates a .HTM file that describes the errors it encountered and provides more information on each potential issue.

Right there at the top, we see that our missing feature has caused a potential blocking issue for upgrade.

What About SharePoint 2010?

Oh, astute reader! You are thinking about the upgrade process and wondering if this has been “fixed” in SharePoint 2010. The first thing to address is that there’s really nothing to fix, as this isn’t a bug, it’s working just as it was intended to. The proper sequence is to deactivate and uninstall the feature, as shown in the section above “Cleaning It Up the Right Way”. This behavior did not change for stsadm commands in SharePoint 2010, they remain unchanged so that administrators can continue managing their farms without completely changing all of their scripts.

But wait, SharePoint 2010 has all these great PowerShell cmdlets! Yes, that’s true, there are a ton of new PowerShell cmdlets that give you even greater flexibility for managing SharePoint. But the simple design fact remains that you still have to explicitly deactivate the feature at the specified scope using the cmdlet Disable-SPFeature. Once the feature has been deactivated at the given scope, you can use the new PowerShell cmdlet “Remove-SPSolution” that collapses the minimum of 3 separate stsadm commands into 1 single PowerShell cmdlet that removes the solution from the solution store.

How to calculate Age using Calculated Value of a Column

Created a calculated column called "Today Date", but this column will not be displayed in any of the views..
the formula is as follow:
=DATE(YEAR(Modified), MONTH(Modified),DAY(Modified)
initially i thought that this formula would auto-update today's date and store it inside this column, but i was wrong *ah wells*

next, i have a calculated column called "Their Age" and "Their Birthday"
the formula is as follow:
=DATEDIF([Their Birthday],Today Date,"y")
although it does return a age, it uses the date difference of "The date when u entered the value" and "Their date of birth"

try this formula:
=FLOOR(DATEDIF(DOB,Modified,"d")/365,1)

hopefully it works :x

How to upload a user profile photo programmatically

Updated No. 1: Changes required by SP 2010 RTM.

Updated No. 2: Method added to set the picture URL property for the user profile.

On the MSDN forum I’ve met a question that ask about how can one upload and set user profile pictures programmatically. I did a little research on the area and found another thread with the same question but I think the accepted answer of Chakkaradeep Chandran really does not answer the question, as it simply sets the image in the profile through the PictureUrl profile property, but does nothing related to upload and image resizing.

I admit the later tasks are not trivial although definitely not impossible through some hacking with Reflection. It is required since the methods we need (LoadPictureLibraryInternal, CreateThumbnail, GetSubfolderForPictures) are internal and private members of the ProfileImagePicker and UserProfilePhotos classes (later class is internal itself as well). So if you don’t mind this kind of dirty code, here is the solution for the question.

You should create a console application and add references to System.Drawing, System.Web, Microsoft.SharePoint, Microsoft.SharePoint.Portal, Microsoft.Office.Server and Microsoft.Office.Server.UserProfiles assemblies.

Next, use the code below:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Drawing;

using System.Reflection;

using Microsoft.SharePoint;

using Microsoft.SharePoint.Portal.WebControls;

using System.IO;

using Microsoft.Office.Server.UserProfiles;

namespace UploadProfileImage

{

class Program

{

static void Main(string[] args)

{

Program prog = new Program();

prog.UploadProfileImages();

}

private void UploadProfileImages()

{

// my site host url

String url = "http://sp2010/profiles/host";

using (SPSite site = new SPSite(url))

{

using (SPWeb web = site.OpenWeb())

{

ProfileImagePicker profileImagePicker = new ProfileImagePicker();

InitializeProfileImagePicker(profileImagePicker, web);

SPFolder subfolderForPictures = GetSubfolderForPictures(profileImagePicker);

// repeat this block if you have more images and users

// your account name

String accountName = @"domain\administrator";

// path to image file

String imageFilePath = @"C:\Temp\AdminAvatar.jpg";

UploadPhoto(accountName, imageFilePath, subfolderForPictures, profileImagePicker);

SetPictureUrl(accountName, subfolderForPictures);

// repeat block finished

}

}

}

private void SetPictureUrl(string accountName, SPFolder subfolderForPictures)

{

Console.WriteLine("Setting profile image for user '{0}'", accountName);

SPSite site = subfolderForPictures.ParentWeb.Site;

UserProfileManager userProfileManager = new UserProfileManager(SPServiceContext.GetContext(site));

UserProfile userProfile = userProfileManager.GetUserProfile(accountName);

string fileNameWithoutExtension = GetFileNameFromAccountName(accountName);

string pictureUrl = String.Format("{0}/{1}/{2}_MThumb.jpg", site.Url, subfolderForPictures.Url, fileNameWithoutExtension);

userProfile["PictureUrl"].Value = pictureUrl;

userProfile.Commit();

}

private void UploadPhoto(string accountName, string imageFilePath, SPFolder subfolderForPictures, ProfileImagePicker profileImagePicker)

{

Console.WriteLine("Uploading image '{0}' for user '{1}'", imageFilePath, accountName);

if (!File.Exists(imageFilePath) || Path.GetExtension(imageFilePath).Equals(".gif"))

{

Console.WriteLine("File '{0}' does not exist or has invalid extension", imageFilePath);

}

else

{

string fileNameWithoutExtension = GetFileNameFromAccountName(accountName);

FileStream file = File.Open(imageFilePath, FileMode.Open);

BinaryReader reader = new BinaryReader(file);

if (subfolderForPictures != null)

{

// try casting length (long) to int

byte[] buffer = reader.ReadBytes((int)file.Length);

int largeThumbnailSize = 0×90;

int mediumThumbnailSize = 0×60;

int smallThumbnailSize = 0×20;

using (MemoryStream stream = new MemoryStream(buffer))

{

using (Bitmap bitmap = new Bitmap(stream, true))

{

CreateThumbnail(bitmap, largeThumbnailSize, largeThumbnailSize, subfolderForPictures, fileNameWithoutExtension + "_LThumb.jpg");

CreateThumbnail(bitmap, mediumThumbnailSize, mediumThumbnailSize, subfolderForPictures, fileNameWithoutExtension + "_MThumb.jpg");

CreateThumbnail(bitmap, smallThumbnailSize, smallThumbnailSize, subfolderForPictures, fileNameWithoutExtension + "_SThumb.jpg");

}

}

}

}

}

private void InitializeProfileImagePicker(ProfileImagePicker profileImagePicker, SPWeb web)

{

Type profileImagePickerType = typeof(ProfileImagePicker);

FieldInfo fi_m_objWeb = profileImagePickerType.GetField("m_objWeb", BindingFlags.NonPublic | BindingFlags.Instance);

fi_m_objWeb.SetValue(profileImagePicker, web);

MethodInfo mi_LoadPictureLibraryInternal = profileImagePickerType.GetMethod("LoadPictureLibraryInternal", BindingFlags.NonPublic | BindingFlags.Instance);

if (mi_LoadPictureLibraryInternal != null)

{

mi_LoadPictureLibraryInternal.Invoke(profileImagePicker, new object[] { });

}

}

public SPFile CreateThumbnail(Bitmap original, int idealWidth, int idealHeight, SPFolder folder, string fileName)

{

SPFile file = null;

// hack to get the Microsoft.Office.Server.UserProfiles assembly

Assembly userProfilesAssembly = typeof(UserProfile).Assembly;

// or assuming you know all the details of the assembly

// Assembly userProfilesAssembly = Assembly.Load(“Microsoft.Office.Server.UserProfiles, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c”);

// UserProfilePhotos is internal class,

// so you cannot get it directly from Visual Studio

Type userProfilePhotosType = userProfilesAssembly.GetType("Microsoft.Office.Server.UserProfiles.UserProfilePhotos");

MethodInfo mi_CreateThumbnail = userProfilePhotosType.GetMethod("CreateThumbnail", BindingFlags.NonPublic | BindingFlags.Static);

if (mi_CreateThumbnail != null)

{

file = (SPFile)mi_CreateThumbnail.Invoke(null, new object[] { original, idealWidth, idealHeight, folder, fileName });

}

return file;

}

private SPFolder GetSubfolderForPictures(ProfileImagePicker profileImagePicker)

{

SPFolder folder = null;

Type profileImagePickerType = typeof(ProfileImagePicker);

MethodInfo mi_GetSubfolderForPictures = profileImagePickerType.GetMethod("GetSubfolderForPictures", BindingFlags.NonPublic | BindingFlags.Instance);

if (mi_GetSubfolderForPictures != null)

{

folder = (SPFolder)mi_GetSubfolderForPictures.Invoke(profileImagePicker, new object[] { });

}

return folder;

}

private string GetFileNameFromAccountName(string accountName)

{

string result = accountName;

string charsToReplace = @"\/:*?""<>|";

Array.ForEach(charsToReplace.ToCharArray(), charToReplace => result = result.Replace(charToReplace, '_'));

return result;

}

}

}

Happy profile image uploading!

You should know that user profile photos are stored in three different resolutions in a folder called Profile Pictures in the User Photos image library on the My Site Host site.

Note, that I found a bit strange dependency of behavior on the size of the photo you set in the PictureUrl profile property when it is about removing the profile image. It seems to be a minor issue, but might have significant impact on your solution.

When you upload the profile photo via the Edit User Profile user interface and then removes that also via the UI, then all of the image versions (I mean the three different resolution) are deleted. and using code. I consider this to be the standard behavior as it is not desired to leave orphaned files in the User Photos image library.

First I’ve tried to set the large image (_LThumb.jpg) from code in the PictureUrl profile property, but when I was to remove the image from the UI then only the large image was deleted, medium and small images remained in the image library. After checking the effect of setting the image from the UI I found, that it sets the PictureUrl property to the medium image (_MThumb.jpg), so I modified the code accordingly. Now all of the images were removed as expected.

Read Text from a File

using System;
using System.IO;

class Test
{
    public static void Main()
    {
        try
        {
            // Create an instance of StreamReader to read from a file.
            // The using statement also closes the StreamReader.
            using (StreamReader sr = new StreamReader("TestFile.txt"))
            {
                String line;
                // Read and display lines from the file until the end of
                // the file is reached.
                while ((line = sr.ReadLine()) != null)
                {
                    Console.WriteLine(line);
                }
            }
        }
        catch (Exception e)
        {
            // Let the user know what went wrong.
            Console.WriteLine("The file could not be read:");
            Console.WriteLine(e.Message);
        }
    }
}

using System;
using System.IO;

public class TextFromFile
{
    private const string FILE_NAME = "MyFile.txt";

    public static void Main()
    {
        if (!File.Exists(FILE_NAME))
        {
            Console.WriteLine("{0} does not exist.", FILE_NAME);
            return;
        }
        using (StreamReader sr = File.OpenText(FILE_NAME))
        {
            String input;
            while ((input = sr.ReadLine()) != null)
            {
                Console.WriteLine(input);
            }
            Console.WriteLine ("The end of the stream has been reached.");
        }
    }
}

Get Current User Email, Login, Display Name Details

working on a requirement on getting the current user's Email id in efficient way.
The normal code which microsoft given fails:

SPWeb site = SPContext.Current.Web;
SPUser user = site.CurrentUser;
string DisplayName = user.Name;
string Login = user.LoginName;
string EMail = user.Email;
string User Notes = user.Notes;

Because most of the users who don't have access to All sites won't give details of their email by the code given. If you are wondering what is all about the difference between All users see below:

SPWeb site = SPContext.Current.Web;
SPUserCollection c1 = site.Users;
SPUserCollection c2 = site.AllUsers;
SPUserCollection c3 = site.SiteUsers;

The code gives 3 types of different user collection so i guess the user who logged in and didn't find his email by the code above belongs to one of this group.
The difference between these SPUserCollection is copied from MSDN.:

The Users collection has the smallest membership of these three collections. This collection includes all the external principals that have been explicitly assigned permissions within the current site.

The AllUsers collection includes all members of the Users collection, plus external users that have accessed objects within the site using implicit permissions through group or role membership. For example, imagine a user named Brian with the login of LITWAREINC\BrianC that has never been given explicit permissions to access a site and view a particular list. However, he might still be able to view the list because of his membership within an Active Directory group that has been configured with list view permissions. When Brian first accesses the site or one of its objects (say, a list using implicit permissions), he is added as a member of the AllUsers collection, but he is not added as a member of the Users collection.

The SiteUsers collection is an aggregation that combines membership for each AllUsers collection within the current site collection. The membership of this collection includes all external principals that have been assigned permissions to any object within the site collection as well as all external users that have been granted access to any of the site collection's objects using implicit permissions.

I used a basic way to get the current user using (context of control HTTPCONTEXT) Context.User.Identity.Name or Page.User.Identity.Name which does the same httpcontext.

After we get the current user login i can pass it to the magic of another Class in sharepoint object Model which brings the user details.

using Microsoft.SharePoint.Utilities;

SPWeb osite = SPContext.Current.Web;

SPPrincipalInfo prin = SPUtility.ResolvePrincipal(osite,Context.User.Identity.Name , SPPrincipalType.All, SPPrincipalSource.All, osite.AllUsers, false);
writer.Write(prin.Email);

using this method u can also search the user by their Email ID, or their Display name. its a cool method who does the search on multiple fields.

The DATEDIF Worksheet Function

The DATEDIF Function

The DATEDIF function computes the difference between two dates in a variety of different intervals, such as the number of years, months, or days between the dates. This function is available in all versions of Excel since at least version 5/95, but is documented in the help file only for Excel 2000. For some reason, Microsoft has decided not to document this function in any other versions. DATEDIF is treated as the drunk cousin of the Formula family. Excel knows it lives a happy and useful life, but will not speak of it in polite conversation. Do not confuse the DATEDIF worksheet function with the DateDiff VBA function.

The syntax for DATEDIF is as follows:

=DATEDIF(Date1, Date2, Interval)

Where:
Date1 is the first date,
Date2 is the second date,
Interval is the interval type to return.

If Date1 is later than Date2, DATEDIF will return a #NUM! error. If either Date1 or Date2 is not a valid date, DATEDIF will return a #VALUE error.
The Interval value should be one of

Interval	Meaning	Description
m	Months	Complete calendar months between the dates.
d	Days	Number of days between the dates.
y	Years	Complete calendar years between the dates.
ym	Months Excluding Years	Complete calendar months between the dates as if they were of the same year.
yd	Days Excluding Years	Complete calendar days between the dates as if they were of the same year.
md	Days Excluding Years And Months	Complete calendar days between the dates as if they were of the same month and same year.

If Interval is not one of the items listed in above, DATEDIF will return a #NUM error.

If you are including the Interval string directly within the formula, you must enclose it in double quotes:

=DATEDIF(Date1,Date2,"m")

If you have the interval in another cell referenced by the formula, that cell should not have quotes around the interval string. For example, with the formula

=DATEDIF(Date1,Date2,A1)

cell A1 should contain m not "m".

Supressing Zero Value Components

In typical usage to calculate the number of years, months, and days between two dates, you would use a formula such as the following, where A1 is the start date and B1 is the end date:

=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&DATEDIF(A1,B1,"md")
&" days"

This will return a string such as

12 years 8 months 14 days

However, if the number of years and/or months is 0, you'll get a string like

0 years 0 months 14 days

0 years 3 months 14 days

If you want to suppress the 0 values, and return a result such as

8 months 14 days
or
14 days

where the 0-valued components are not displayed, use a formula like the following:

=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",
DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"

This will display only the date components whose value is greater than 0. The day value will always be displayed, but the year and/or the month value may be suppresed.

SectionBreak

Examples

The follow are some examples of the DATEDIF function.

Date1: 1-Jan-2007
Date2: 10-Jan-2007
Interval: d
Result: 9
Explanation:
There are 9, not 10, calendar days between these two dates.

Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: m
Result: 0
Explanation:
There 0 complete calendar months between the two dates.

Date1: 1-Jan-2007
Date2: 1-Feb-2007
Interval: m
Result: 1
Explanation:
There are 1 complete months between the two dates.

Date1: 1-Jan-2007
Date2: 28-Feb-2007
Interval: m
Result: 1
Explanation:
There are 1 complete months between the two dates.

Date1: 1-Jan-2007
Date2: 31-Dec-2007
Interval: d
Result: 364
Explanation:
There are 364 days between the two dates.

Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: y
Result: 0
Explanation:
There are 0 complete years between the dates

Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: d
Result: 547
Explanation:
There are 547 days between the two dates.

Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: m
Result: 18
Explanation:
There are 18 months between the two dates.

Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: ym
Result: 6
Explanation:
There are 6 months between the two dates if the dates are considered to have the same year. The year it taken from Date1, not Date2. This makes a difference when one year is a leap year. Since 2007 is not a leap year, 29-Feb is not counted. See DATEDIF And Leap Years below.

Date1: 1-Jan-2007
Date2: 1-July-2008
Interval: yd
Result: 181
Explanation:
There are 181 days between the dates if the dates are considered to have the same year. The year it taken from Date1, not Date2. This makes a difference when one year is a leap year. Since 2007 is not a leap year, 29-Feb is not counted. See DATEDIF And Leap Years below.

Date1: 1-Jan-2008
Date2: 1-July-2009
Interval: yd
Result: 182
Explanation:
There are 182 days between the dates if the dates are considered to have the same year. This result is 182 not 181 since Date1 is a leap year and thus 29-Feb is counted.

Date1: 1-Jan-2007
Date2: 31-Jan-2007
Interval: md
Result: 30
Explanation:
There are 30 days between the 1st and 31st of the dates when both dates' month and years are considered to be the same.

SectionBreak

DATEDIF And Leap Years

When calculating date intervals, DATEDIF uses the year of Date1, not Date2 when calculating the yd, ym and md intervals. For example,

=DATEDIF(Date1,Date2,"md")

returns 28 for Date1 = 1-Feb-2007 and Date2 = 1-March-2009. Since Date1 is not a leap year, the date 29-Feb is not counted. But the same formula with Date1 = 1-Feb-2008 returns 29, since Date1 is a leap year and therefore the date 29-Feb is counted.

SectionBreak

Calculating Age

You can use the DATEDIF to calculate a person's age. For example, the formula below will calculate the age of a person as of the current date, where BirthDate is the person's date of birth.

=DATEDIF(BirthDate,TODAY(),"y")&" years "&DATEDIF(BirthDate,TODAY(),"ym")&" months "&DATEDIF(BirthDate,TODAY(),"md")&" days"

String Functions in MySql

Table 12.7. String Operators

Name	Description
`ASCII()`	Return numeric value of left-most character
`BIN()`	Return a string representation of the argument
`BIT_LENGTH()`	Return length of argument in bits
`CHAR_LENGTH()`	Return number of characters in argument
`CHAR()`	Return the character for each integer passed
`CHARACTER_LENGTH()`	A synonym for CHAR_LENGTH()
`CONCAT_WS()`	Return concatenate with separator
`CONCAT()`	Return concatenated string
`ELT()`	Return string at index number
`EXPORT_SET()`	Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
`FIELD()`	Return the index (position) of the first argument in the subsequent arguments
`FIND_IN_SET()`	Return the index position of the first argument within the second argument
`FORMAT()`	Return a number formatted to specified number of decimal places
`HEX()`	Return a hexadecimal representation of a decimal or string value
`INSERT()`	Insert a substring at the specified position up to the specified number of characters
`INSTR()`	Return the index of the first occurrence of substring
`LCASE()`	Synonym for LOWER()
`LEFT()`	Return the leftmost number of characters as specified
`LENGTH()`	Return the length of a string in bytes
`LIKE`	Simple pattern matching
`LOAD_FILE()`	Load the named file
`LOCATE()`	Return the position of the first occurrence of substring
`LOWER()`	Return the argument in lowercase
`LPAD()`	Return the string argument, left-padded with the specified string
`LTRIM()`	Remove leading spaces
`MAKE_SET()`	Return a set of comma-separated strings that have the corresponding bit in bits set
`MATCH`	Perform full-text search
`MID()`	Return a substring starting from the specified position
`NOT LIKE`	Negation of simple pattern matching
`NOT REGEXP`	Negation of REGEXP
`OCTET_LENGTH()`	A synonym for LENGTH()
`ORD()`	Return character code for leftmost character of the argument
`POSITION()`	A synonym for LOCATE()
`QUOTE()`	Escape the argument for use in an SQL statement
`REGEXP`	Pattern matching using regular expressions
`REPEAT()`	Repeat a string the specified number of times
`REPLACE()`	Replace occurrences of a specified string
`REVERSE()`	Reverse the characters in a string
`RIGHT()`	Return the specified rightmost number of characters
`RLIKE`	Synonym for REGEXP
`RPAD()`	Append string the specified number of times
`RTRIM()`	Remove trailing spaces
`SOUNDEX()`	Return a soundex string
`SOUNDS LIKE`	Compare sounds
`SPACE()`	Return a string of the specified number of spaces
`STRCMP()`	Compare two strings
`SUBSTR()`	Return the substring as specified
`SUBSTRING_INDEX()`	Return a substring from a string before the specified number of occurrences of the delimiter
`SUBSTRING()`	Return the substring as specified
`TRIM()`	Remove leading and trailing spaces
`UCASE()`	Synonym for UPPER()
`UNHEX()`	Convert each pair of hexadecimal digits to a character
`UPPER()`	Convert to uppercase

String-valued functions return NULL if the length of the result would be greater than the value of the max_allowed_packet system variable. See Section 8.9.2, “Tuning Server Parameters”.

For functions that operate on string positions, the first position is numbered 1.

For functions that take length arguments, noninteger arguments are rounded to the nearest integer.

ASCII(str)

Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for 8-bit characters.
```
mysql> SELECT ASCII('2');
        -> 50
mysql> SELECT ASCII(2);
        -> 50
mysql> SELECT ASCII('dx');
        -> 100
```
See also the ORD() function.

BIN(N)

Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL.
```
mysql> SELECT BIN(12);
        -> '1100'
```

BIT_LENGTH(str)

Returns the length of the string str in bits.
```
mysql> SELECT BIT_LENGTH('text');
        -> 32
```

CHAR(N,... [USING charset_name])

CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.

mysql> SELECT CHAR(77,121,83,81,'76');
        -> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
        -> 'MMM'

CHAR() arguments larger than 255 are converted into multiple result bytes. For example, CHAR(256) is equivalent to CHAR(1,0), and CHAR(256*256) is equivalent to CHAR(1,0,0):

mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
+----------------+----------------+
| HEX(CHAR(1,0)) | HEX(CHAR(256)) |
+----------------+----------------+
| 0100           | 0100           |
+----------------+----------------+
mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));
+------------------+--------------------+
| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |
+------------------+--------------------+
| 010000           | 010000             |
+------------------+--------------------+

By default, CHAR() returns a binary string. To produce a string in a given character set, use the optional USING clause:

mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));
+---------------------+--------------------------------+
| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |
+---------------------+--------------------------------+
| binary              | utf8                           |
+---------------------+--------------------------------+

If USING is given and the result string is illegal for the given character set, a warning is issued. Also, if strict SQL mode is enabled, the result from CHAR() becomes NULL.

CHAR_LENGTH(str)

Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

CHARACTER_LENGTH(str)

CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example:
```
SELECT CONCAT(CAST(int_col AS CHAR), char_col);
```
CONCAT() returns NULL if any argument is NULL.
```
mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'
```
For quoted strings, concatenation can be performed by placing the strings next to each other:
```
mysql> SELECT 'My' 'S' 'QL';
        -> 'MySQL'
```

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.
```
mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
        -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
        -> 'First name,Last Name'
```
CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

ELT(N,str1,str2,str3,...)

Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD().
```
mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
        -> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
        -> 'foo'
```

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

Returns a string such that for every bit set in the value bits, you get an on string and for every bit not set in the value, you get an off string. Bits in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (the default being the comma character “,”). The number of bits examined is given by number_of_bits, which has a default of 64 if not specified. number_of_bits is silently clipped to 64 if larger than 64. It is treated as an unsigned integer, so a value of –1 is effectively the same as 64.
```
mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
        -> 'Y,N,Y,N'
mysql> SELECT EXPORT_SET(6,'1','0',',',10);
        -> '0,1,1,0,0,0,0,0,0,0'
```

FIELD(str,str1,str2,str3,...)

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.

If str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT().
```
mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 2
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 0
```

FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.
```
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2
```

FORMAT(X,D)

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.

mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'

HEX(str), HEX(N)

For a string argument str, HEX() returns a hexadecimal string representation of str where each character in str is converted to two hexadecimal digits. The inverse of this operation is performed by the UNHEX() function.

For a numeric argument N, HEX() returns a hexadecimal string representation of the value of N treated as a longlong (BIGINT) number. This is equivalent to CONV(N,10,16). The inverse of this operation is performed by CONV(HEX(N),16,10).
```
mysql> SELECT 0x616263, HEX('abc'), UNHEX(HEX('abc'));
        -> 'abc', 616263, 'abc'
mysql> SELECT HEX(255), CONV(HEX(255),16,10);
        -> 'FF', 255
```

INSERT(str,pos,len,newstr)

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.
```
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
        -> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
        -> 'QuWhat'
```
This function is multi-byte safe.

INSTR(str,substr)

Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.
```
mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0
```
This function is multi-byte safe, and is case sensitive only if at least one argument is a binary string.

LCASE(str)

LCASE() is a synonym for LOWER().

LEFT(str,len)

Returns the leftmost len characters from the string str, or NULL if any argument is NULL.
```
mysql> SELECT LEFT('foobarbar', 5);
        -> 'fooba'
```

LENGTH(str)

Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
```
mysql> SELECT LENGTH('text');
        -> 4
```

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

As of MySQL 5.1.6, the character_set_filesystem system variable controls interpretation of file names that are given as literal strings.
```
mysql> UPDATE t
           SET blob_col=LOAD_FILE('/tmp/picture')
           WHERE id=1;
```

LOCATE(substr,str), LOCATE(substr,str,pos)

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.
```
mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7
```
This function is multi-byte safe, and is case-sensitive only if at least one argument is a binary string.

LOWER(str)

Returns the string str with all characters changed to lowercase according to the current character set mapping. The default is latin1 (cp1252 West European).

mysql> SELECT LOWER('QUADRATICALLY');
        -> 'quadratically'

LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a nonbinary string:

mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
+-------------+-----------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
+-------------+-----------------------------------+
| New York    | new york                          |
+-------------+-----------------------------------+

This function is multi-byte safe.

LPAD(str,len,padstr)

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.
```
mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'
mysql> SELECT LPAD('hi',1,'??');
        -> 'h'
```

LTRIM(str)

Returns the string str with leading space characters removed.
```
mysql> SELECT LTRIM(' barbar');
        -> 'barbar'
```
This function is multi-byte safe.

MAKE_SET(bits,str1,str2,...)

Returns a set value (a string containing substrings separated by “,” characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result.

mysql> SELECT MAKE_SET(1,'a','b','c');
        -> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
        -> 'hello,world'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
        -> 'hello'
mysql> SELECT MAKE_SET(0,'a','b','c');
        -> ''

MID(str,pos,len)

MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).

OCTET_LENGTH(str)

OCTET_LENGTH() is a synonym for LENGTH().

ORD(str)

If the leftmost character of the string str is a multi-byte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:
```
  (1st byte code)
+ (2nd byte code * 256)
+ (3rd byte code * 256
```
²
```
) ...
```
If the leftmost character is not a multi-byte character, ORD() returns the same value as the ASCII() function.
```
mysql> SELECT ORD('2');
        -> 50
```

POSITION(substr IN str)

POSITION(substr IN str) is a synonym for LOCATE(substr,str).

QUOTE(str)

Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash (“\”), single quote (“'”), ASCII NUL, and Control+Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without enclosing single quotation marks.
```
mysql> SELECT QUOTE('Don\'t!');
        -> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
        -> NULL
```
For comparison, see the quoting rules for literal strings and within the C API in Section 9.1.1, “String Literals”, and Section 21.9.3.53, “mysql_real_escape_string()”.

REPEAT(str,count)

Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL.
```
mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL'
```

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.
```
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'
```
This function is multi-byte safe.

REVERSE(str)

Returns the string str with the order of the characters reversed.
```
mysql> SELECT REVERSE('abc');
        -> 'cba'
```
This function is multi-byte safe.

RIGHT(str,len)

Returns the rightmost len characters from the string str, or NULL if any argument is NULL.
```
mysql> SELECT RIGHT('foobarbar', 4);
        -> 'rbar'
```
This function is multi-byte safe.

RPAD(str,len,padstr)

Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.
```
mysql> SELECT RPAD('hi',5,'?');
        -> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
        -> 'h'
```
This function is multi-byte safe.

RTRIM(str)

Returns the string str with trailing space characters removed.
```
mysql> SELECT RTRIM('barbar ');
        -> 'barbar'
```
This function is multi-byte safe.

SOUNDEX(str)

Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All nonalphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.

Important

When using SOUNDEX(), you should be aware of the following limitations:
- This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results.
- This function is not guaranteed to provide consistent results with strings that use multi-byte character sets, including utf-8.
  
  We hope to remove these limitations in a future release. See Bug #22638 for more information.
```
mysql> SELECT SOUNDEX('Hello');
        -> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
        -> 'Q36324'
```
Note

This function implements the original Soundex algorithm, not the more popular enhanced version (also described by D. Knuth). The difference is that original version discards vowels first and duplicates second, whereas the enhanced version discards duplicates first and vowels second.

expr1 SOUNDS LIKE expr2

This is the same as SOUNDEX(expr1) = SOUNDEX(expr2).

SPACE(N)

Returns a string consisting of N space characters.
```
mysql> SELECT SPACE(6);
        -> '      '
```

SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)

SUBSTR() is a synonym for SUBSTRING().

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.
```
mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'
```
This function is multi-byte safe.

If len is less than 1, the result is the empty string.

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.
```
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'
```
This function is multi-byte safe.

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.
```
mysql> SELECT TRIM(' bar ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'
```
This function is multi-byte safe.

UCASE(str)

UCASE() is a synonym for UPPER().

UNHEX(str)

For a string argument str, UNHEX(str) performs the inverse operation of HEX(str). That is, it interprets each pair of characters in the argument as a hexadecimal number and converts it to the character represented by the number. The return value is a binary string.
```
mysql> SELECT UNHEX('4D7953514C');
        -> 'MySQL'
mysql> SELECT 0x4D7953514C;
        -> 'MySQL'
mysql> SELECT UNHEX(HEX('string'));
        -> 'string'
mysql> SELECT HEX(UNHEX('1267'));
        -> '1267'
```
The characters in the argument string must be legal hexadecimal digits: '0' .. '9', 'A' .. 'F', 'a' .. 'f'. If the argument contains any nonhexadecimal digits, the result is NULL:
```
mysql> SELECT UNHEX('GG');
+-------------+
| UNHEX('GG') |
+-------------+
| NULL        |
+-------------+
```
A NULL result can occur if the argument to UNHEX() is a BINARY column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval. For example, '41' is stored into a CHAR(3) column as '41 ' and retrieved as '41' (with the trailing pad space stripped), so UNHEX() for the column value returns 'A'. By contrast '41' is stored into a BINARY(3) column as '41\0' and retrieved as '41\0' (with the trailing pad 0x00 byte not stripped). '\0' is not a legal hexadecimal digit, so UNHEX() for the column value returns NULL.

For a numeric argument N, the inverse of HEX(N) is not performed by UNHEX(). Use CONV(HEX(N),16,10) instead. See the description of HEX().

UPPER(str)

Returns the string str with all characters changed to uppercase according to the current character set mapping. The default is latin1 (cp1252 West European).
```
mysql> SELECT UPPER('Hej');
        -> 'HEJ'
```
See the description of LOWER() for information that also applies to UPPER(), such as information about how to perform lettercase conversion of binary strings (BINARY, VARBINARY, BLOB) for which these functions are ineffective.

This function is multi-byte safe.

Remove all special characters from data stored in MYSQL

Remove all special characters from data stored in MYSQL.: ~!@#$%*()_+{}[];':"<>?

SELECT * FROM Film where REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(Film.name,':',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'*',''),'(',''),')',''),'_',''),'+',''),
'{',''),'}',''),'[',''),']',''),';',''),'''',''),':',''),'"',''),'<',''),'>',''),'?','') = ? or

SELECT * FROM Film where REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(Film.name,':',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'*',''),'(',''),')',''),'_',''),
'+',''),'{',''),'}',''),'[',''),']',''),';',''),'''',''),':',''),'"',''),'<',''),'>',''),'?',''),' ','') = ?

Paging for First, Next, Previous and Last in gridview

1) First fill the gridview with Data from a Table.

2) Drag and Drop 4 Buttons keep it at the bottom of the Gridview.

3) Change the text of a buttons using Properties.

4) Select Gridview Properties and set AllowPaging=True.

5) Expand the PageSetting Properties of Gridview, and set visible property as False.

6) Next we have to set a gridview PageSize property. The Default

PageSize is 10.you can keep how much size you want to display rows per page.

Here I kept 6 rows to display in gridview per page. You can observe how to set

page in 'Pagesetting Picture' marked with yellow color...

7) Under SelectedRowStyle set 'ShowFooter as False'

8) The Ouptput of your gridview Image is shown below.

How to Write the Code for Paging First, Next, Previous and Last.

1) Paging for First Button: Double click on 'First' Button and write the below code.

protected void btnfirst_Click(object sender, EventArgs e)

{

gridview1.PageIndex = 0;

}

Explantion: when you run the Page it contains 6 recors in Gridview.

Suppose the user in last Page.When the user clicks on 'First Button' first 6 records of the page has to be display in gridview.so the first gridview page Index(property) is 'zero'.There is a Property called 'PageIndex' for Gridview.

2) Paging for Next Button: Double click on 'Next Button 'and write the below code.

protectedvoid btnnext_Click(object sender, EventArgs e)

{

int i = gridview1.PageIndex + 1;

if (i <= gridview1.PageCount)

{

gridview1.PageIndex = i;

}

}

Explanation: 'PageCount' is used to count number of pages availble in a Gridview.

3) Paging for Previous Button:Double click on 'Previous Button'and write the below code.

pProtected void btnprevious_Click(object sender, EventArgs e)

{

int i = gridview1.PageCount;

if (gridview1.PageIndex > 0)

{

gridview1.PageIndex = gridview1.PageIndex - 1;

}

}

4) Paging for Last Button:Double click on 'Last Button'and write the below code

proProtectedvoid btnlast_Click(object sender, EventArgs e)

{

gridview1.PageIndex = gridview1.PageCount;

}

In the Above Picture observe on Last Button.it is in Enable=False state,because in gridview the last page records are displaying.So again no need with last Button.In same Senario remaining buttons also made Enable=False where the buttons are not usefull to click the user.you can see the coding fo Enable in .aspx.cs page

The Complete .aspx.cs Page

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

btnfirst.Enabled = false;

btnprevious.Enabled = false;

}

protected void btnnext_Click(object sender, EventArgs e)

{

int i = gridview1.PageIndex + 1;

if (i <= gridview1.PageCount)

{

gridview1.PageIndex = i;

btnlast.Enabled = true;

btnprevious.Enabled = true;

btnfirst.Enabled = true;

}

if (gridview1.PageCount-1 == gridview1.PageIndex)

{

btnnext.Enabled = false;

btnlast.Enabled = false;

}

}

protected void btnprevious_Click(object sender, EventArgs e)

{

int i = gridview1.PageCount;

if (gridview1.PageIndex > 0)

{

gridview1.PageIndex = gridview1.PageIndex - 1;

btnlast.Enabled = true ;

}

if (gridview1.PageIndex == 0)

{

btnfirst.Enabled = false;

}

if (gridview1.PageCount - 1 == gridview1.PageIndex+1)

{

btnnext.Enabled = true;

}

if (gridview1.PageIndex == 0)

{

btnprevious.Enabled = false;

}

}

protected void btnlast_Click(object sender, EventArgs e)

{

gridview1.PageIndex = gridview1.PageCount;

btnlast.Enabled = false;

btnfirst.Enabled = true;

}

protected void btnfirst_Click(object sender, EventArgs e)

{

gridview1.PageIndex = 0;

btnfirst.Enabled = false;

btnprevious.Enabled = false;

btnlast.Enabled = true;

btnnext.Enabled = true;

}

}

This type of Paging is also called as Custom Paging.