How to use SqlDataSource SelectCommand in the backend

This Tutorial is not for those who need to plugin the sqldatasource in the front end, but rather how to use the SqlDataSource in the backend codes.

There are tons of tutorial of how to write SqlDataSource for Gridview, DataList, Repeater and other objects in the front end (aspx file), but not many tutorials out there on how to simplify things for using SqlDataSource in the backend (aspx.cs file).

Example:

you have a gridview called “gvStudents”, and you have a sql query “SELECT * FROM Students”. you want to bind the query to the gridview but you want to do it in the backend. How do you do it?

here is how you do it.

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “SELECT * FROM Students”;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;

gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();

5 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL Query to the Select Command
3) Assign the ConnectionString to the SqlDataSource ConnectionString
4) Assign the SqlDataSource to the gridview DataSource
5) DataBind the gridview

I know your next question is “What about Stored Procedure? Can I do the same thing with a stored procedure?”

The answer is Yes, you can do the same thing with a stored procedure. here is how

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “Stored Procedure Name”;
SDSStudents.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;

gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();

Instead of 5 Simple Steps, it is now 6 simple steps
6 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL stored procedure name to the Select Command
3) Set the SqlCommandType to be a StoredProcedure
4) Assign the ConnectionString to the SqlDataSource ConnectionString
5) Assign the SqlDataSource to the gridview DataSource
6) DataBind the gridview


I bet your next answer is “What about SelectCommand Parameters? Can I Add Parameters to the SqlDataSource?”

*Updated*
Instead of writing another entry, I decided to continue with this entry and answer the question about the Parameters inside the SqlDataSource.

Yes you can add parameter in the SelectCommand, either the select command is sql query or stored procedure.
This is how you do it.

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “SELECT * FROM Students WHERE ID=@ID”;
SDSStudents.SelectParameters.Add(“ID”, TypeCode.Int32, “1″);
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;
gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();


If using with Stored Procedure use this,

SqlDataSource SDSStudents = new SqlDataSource();
SDSStudents.SelectCommand = “Stored Procedure Name”;
SDSStudents.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
SDSStudents.ConnectionString = ConfigurationManager.ConnectionStrings["YourConnectStringName"].ConnectionString;

SDSStudents.SelectParameters.Clear();
SDSStudents.SelectParameters.Add("Param1", strParamVal1);
SDSStudents.SelectParameters.Add("Param2", strParamVal2);

if (SDSStudents.SelectParameters["Param1"].DefaultValue == null || SDSStudents.SelectParameters["Param1"].DefaultValue == "")
SDSStudents.SelectParameters["Param1"].DefaultValue = " ";

if (SDSStudents.SelectParameters["Param2"].DefaultValue == null || SDSStudents.SelectParameters["Param2"].DefaultValue == "")
SDSStudents.SelectParameters["Param2"].DefaultValue = " ";


gvStudents.DataSource = SDSStudents;
gvStudents.DataBind();

Instead of 5 Simple Steps for the sqlquery, it is now 6 simple steps
6 Simple Steps
1) Create a new SqlDataSource object
2) Assign the SQL Query to the Select Command
3) using SelectParamters.Add(“ParameterName”, DBType or TypeCode, “ParameterValue”) to add new parameter to the selectCommand
4) Assign the ConnectionString to the SqlDataSource ConnectionString
5) Assign the SqlDataSource to the gridview DataSource
6) DataBind the gridview

Crystal Report Binding With Parameters From C#

This script can be used when you want to bind a Crystal Report with Stored Procedure and want to pass some parameters to the report. These parameters can be in SubReport also. The parameters get assigned for the respective subreports and main report if the Parameter name differs.

The major use of this code is it would not ask for the login details while showing the report. The Login details are set based on the App.Config connection which is Global across the Application.


TableLogOnInfos myTableLogOnInfos = new TableLogOnInfos();
TableLogOnInfo myTableLogOnInfo = new TableLogOnInfo();
ConnectionInfo myConnectionInfo = new ConnectionInfo();

//Set Database Connection Details
string[] arrConn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].Split(';');

for (int i = 0; i < arrConn.Length; i++)
{
string[] arrServer = arrConn[i].Split('=');
if (arrServer[0].ToLower().Trim() == "server" || arrServer[0].ToLower().Trim() == "data source")
myConnectionInfo.ServerName = arrServer[1];
if (arrServer[0].ToLower().Trim() == "database" || arrServer[0].ToLower().Trim() == "initial catalog")
myConnectionInfo.DatabaseName = arrServer[1];
if (arrServer[0].ToLower().Trim() == "userId" || arrServer[0].ToLower().Trim() == "user id")
myConnectionInfo.UserID = arrServer[1];
if (arrServer[0].ToLower().Trim() == "Password" || arrServer[0].ToLower().Trim() == "pwd")
myConnectionInfo.Password = arrServer[1];
}

//This Code is used to Get the Reportname based on the namespace. This code can be used globally across the application. If you can pass the Report Name directly then this piece of code wont be necessary

//Get the Report Source Name
String sFormName = UIGlobal.ReportName;
Assembly objAssembly = AppDomain.CurrentDomain.Load(UIGlobal.RptNamespaceName); ;
object objForm = null;
foreach (Type t in objAssembly.GetTypes())
{
if (t.Name.ToUpper() == sFormName.ToUpper())
{
objForm = Activator.CreateInstance(t);
break;
}
}
if (objForm != null)
{
rptDoc = (ReportDocument)objForm;

foreach (Table t in rptDoc.Database.Tables)
{
TableLogOnInfo tl = t.LogOnInfo;
tl.ConnectionInfo = myConnectionInfo;
t.ApplyLogOnInfo(tl);
}

rptDoc.Refresh();

//Set Log On Info
myTableLogOnInfo.ConnectionInfo = myConnectionInfo;
myTableLogOnInfos.Add(myTableLogOnInfo);
rptStorage.LogOnInfo = myTableLogOnInfos;
rptStorage.Refresh();

if (strParamName != null && strParamName.Length > 0 && strParamValue != null && strParamValue.Length > 0)
{
int cnt = rptDoc.DataDefinition.ParameterFields.Count;
for (int i = 0; i < cnt; i++)
{
ParameterValues myvals = new ParameterValues();
ParameterDiscreteValue myDiscrete = new ParameterDiscreteValue();

ArrayList arParams = new ArrayList();
arParams.AddRange(strParamName);

if (arParams.Contains(rptDoc.DataDefinition.ParameterFields[i].ParameterFieldName.ToUpper()))
{
for (int iCnt = 0; iCnt < strParamName.Length; iCnt++)
{
if (strParamName[iCnt] == rptDoc.DataDefinition.ParameterFields[i].ParameterFieldName.ToUpper())
{
myDiscrete.Value = strParamValue[iCnt];
myvals.Add(myDiscrete);
rptDoc.DataDefinition.ParameterFields[i].ApplyCurrentValues(myvals);
}
}
}
}
}
rptStorage.ReportSource = rptDoc;
}

Disable Buttons in a Button Column in the Windows Forms DataGridView

public void Form1_Load(object sender, EventArgs e)
{
DataGridViewCheckBoxColumn column0 =
new DataGridViewCheckBoxColumn();
DataGridViewDisableButtonColumn column1 =
new DataGridViewDisableButtonColumn();
column0.Name = "CheckBoxes";
column1.Name = "Buttons";
dataGridView1.Columns.Add(column0);
dataGridView1.Columns.Add(column1);
dataGridView1.RowCount = 8;
dataGridView1.AutoSize = true;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment =
DataGridViewContentAlignment.MiddleCenter;

// Set the text for each button.
for (int i = 0; i < dataGridView1.RowCount; i++)
{
dataGridView1.Rows[i].Cells["Buttons"].Value =
"Button " + i.ToString();
}

dataGridView1.CellValueChanged +=
new DataGridViewCellEventHandler(dataGridView1_CellValueChanged);
dataGridView1.CurrentCellDirtyStateChanged +=
new EventHandler(dataGridView1_CurrentCellDirtyStateChanged);
dataGridView1.CellClick +=
new DataGridViewCellEventHandler(dataGridView1_CellClick);

this.Controls.Add(dataGridView1);
}

// This event handler manually raises the CellValueChanged event
// by calling the CommitEdit method.
void dataGridView1_CurrentCellDirtyStateChanged(object sender,
EventArgs e)
{
if (dataGridView1.IsCurrentCellDirty)
{
dataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit);
}
}

// If a check box cell is clicked, this event handler disables
// or enables the button in the same row as the clicked cell.
public void dataGridView1_CellValueChanged(object sender,
DataGridViewCellEventArgs e)
{
if (dataGridView1.Columns[e.ColumnIndex].Name == "CheckBoxes")
{
DataGridViewDisableButtonCell buttonCell =
(DataGridViewDisableButtonCell)dataGridView1.
Rows[e.RowIndex].Cells["Buttons"];

DataGridViewCheckBoxCell checkCell =
(DataGridViewCheckBoxCell)dataGridView1.
Rows[e.RowIndex].Cells["CheckBoxes"];
buttonCell.Enabled = !(Boolean)checkCell.Value;

dataGridView1.Invalidate();
}
}

// If the user clicks on an enabled button cell, this event handler
// reports that the button is enabled.
void dataGridView1_CellClick(object sender,
DataGridViewCellEventArgs e)
{
if (dataGridView1.Columns[e.ColumnIndex].Name == "Buttons")
{
DataGridViewDisableButtonCell buttonCell =
(DataGridViewDisableButtonCell)dataGridView1.
Rows[e.RowIndex].Cells["Buttons"];

if (buttonCell.Enabled)
{
MessageBox.Show(dataGridView1.Rows[e.RowIndex].
Cells[e.ColumnIndex].Value.ToString() +
" is enabled");
}
}
}
}

public class DataGridViewDisableButtonColumn : DataGridViewButtonColumn
{
public DataGridViewDisableButtonColumn()
{
this.CellTemplate = new DataGridViewDisableButtonCell();
}
}

public class DataGridViewDisableButtonCell : DataGridViewButtonCell
{
private bool enabledValue;
public bool Enabled
{
get
{
return enabledValue;
}
set
{
enabledValue = value;
}
}

// Override the Clone method so that the Enabled property is copied.
public override object Clone()
{
DataGridViewDisableButtonCell cell =
(DataGridViewDisableButtonCell)base.Clone();
cell.Enabled = this.Enabled;
return cell;
}

// By default, enable the button cell.
public DataGridViewDisableButtonCell()
{
this.enabledValue = true;
}

protected override void Paint(Graphics graphics,
Rectangle clipBounds, Rectangle cellBounds, int rowIndex,
DataGridViewElementStates elementState, object value,
object formattedValue, string errorText,
DataGridViewCellStyle cellStyle,
DataGridViewAdvancedBorderStyle advancedBorderStyle,
DataGridViewPaintParts paintParts)
{
// The button cell is disabled, so paint the border,
// background, and disabled button for the cell.
if (!this.enabledValue)
{
// Draw the cell background, if specified.
if ((paintParts & DataGridViewPaintParts.Background) ==
DataGridViewPaintParts.Background)
{
SolidBrush cellBackground =
new SolidBrush(cellStyle.BackColor);
graphics.FillRectangle(cellBackground, cellBounds);
cellBackground.Dispose();
}

// Draw the cell borders, if specified.
if ((paintParts & DataGridViewPaintParts.Border) ==
DataGridViewPaintParts.Border)
{
PaintBorder(graphics, clipBounds, cellBounds, cellStyle,
advancedBorderStyle);
}

// Calculate the area in which to draw the button.
Rectangle buttonArea = cellBounds;
Rectangle buttonAdjustment =
this.BorderWidths(advancedBorderStyle);
buttonArea.X += buttonAdjustment.X;
buttonArea.Y += buttonAdjustment.Y;
buttonArea.Height -= buttonAdjustment.Height;
buttonArea.Width -= buttonAdjustment.Width;

// Draw the disabled button.
ButtonRenderer.DrawButton(graphics, buttonArea,
PushButtonState.Disabled);

// Draw the disabled button text.
if (this.FormattedValue is String)
{
TextRenderer.DrawText(graphics,
(string)this.FormattedValue,
this.DataGridView.Font,
buttonArea, SystemColors.GrayText);
}
}
else
{
// The button cell is enabled, so let the base class
// handle the painting.
base.Paint(graphics, clipBounds, cellBounds, rowIndex,
elementState, value, formattedValue, errorText,
cellStyle, advancedBorderStyle, paintParts);
}
}
}

SQL Script for creating a New Database with Security Login and Password

////Create a new database, if does not exist
if db_id('DBNAME') is null
begin
create database [DBNAME]
end

=====================================================================================
////Once the Database is Created we can create the User login for this Database

////If user does not exist
if DATABASE_PRINCIPAL_ID('[DBNAME]') is null
begin
use [DBNAME]
if DATABASE_PRINCIPAL_ID('USERNAME') is null
CREATE LOGIN [USERNAME]
WITH PASSWORD='PASSWORD',
DEFAULT_DATABASE=[DBNAME],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
end

if DATABASE_PRINCIPAL_ID('USERNAME') is null
begin
use [DBNAME]
CREATE USER USERNAME
Grant Insert,Update,Delete,Execute,Select,Create Procedure to USERNAME
end


DBNAME = Database Name
USERNAME = User Login Name
PASSWORD = Password for the DB Login

Creating Word Document from Byte Array and Viceversa

//Reading a Word Document and Converting to Byte Array (Can Be Saved to DB)
byte[] myByteArray = System.IO.File.ReadAllBytes(Request.PhysicalApplicationPath + "\\document\\Test.doc");

//Converting the Byte Array to a WordDocument Again (Retrieved from DB)
FileStream fs = new FileStream(Request.PhysicalApplicationPath + "\\document\\Test2.doc", FileMode.Create, FileAccess.ReadWrite);
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(myByteArray);
bw.Close();

Convert DataTable Column into String Array without Looping

Here ColumnName is the Specific Column in the Datatable

String[] rowValuesForColumn = Array.ConvertAll(dataTable.Select(),
delegate(DataRow row) { return (String) row[columnName]; });

Getting the Numerics from a String Value

Declare @sVal varchar(100)
Select @sVal= 'Here is where15234Numbers'
Select @sVal= SubString(@sVal,PATINDEX('%[0-9]%',@sVal),Len(@sVal))
Select @sVal= SubString(@sVal,0,PATINDEX('%[^0-9]%',@sVal))
Select @sVal

OutPut
-------
15234

if the string is 'Here is where15.234Numbers'
Then use
Select @sVal= SubString(@sVal,0,PATINDEX('%[^0-9,.]%',@sVal))

OutPut
------
15.234

Getting the Date Difference in Years, Months and Days

CREATE PROCEDURE dbo.CalculateAge
@dayOfBirth datetime
AS

DECLARE @today datetime, @thisYearBirthDay datetime
DECLARE @years int, @months int, @days int

SELECT @today = GETDATE()

SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth)

SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END)

SELECT @months = MONTH(@today - @thisYearBirthDay) - 1

SELECT @days = DAY(@today - @thisYearBirthDay) - 1

select @thisYearBirthDay
SELECT @years [Years], @months [Months], @days [Days]

Getting Integer value from Varchar Column

Declare @str varchar(20)
Set @str = 'SARAN01'
select Substring(@str,PATINDEX('%[0-9]%',@str),len(@str))

OUTPUT
-------
01

Adding CSS effect to AJAX Calendar Extender Control

Calendar Stylesheet
====================
.Cal_Theme .ajax__calendar_container
{
background-color: #EDCF81; border:solid 1px #cccccc;
}

.Cal_Theme .ajax__calendar_header
{
background-color: #FFFFEA; margin-bottom: 4px;
}

.Cal_Theme .ajax__calendar_title,
.Cal_Theme .ajax__calendar_next,
.Cal_Theme .ajax__calendar_prev
{
color: #004080; padding-top: 3px;
}

.Cal_Theme .ajax__calendar_body
{
background-color: #FFFFEA; border: solid 1px #cccccc;
}

.Cal_Theme .ajax__calendar_dayname
{
text-align:center; font-weight:bold; margin-bottom: 4px; margin-top: 2px;
}

.Cal_Theme .ajax__calendar_day
{
text-align:center;
}

.Cal_Theme .ajax__calendar_hover .ajax__calendar_day,
.Cal_Theme .ajax__calendar_hover .ajax__calendar_month,
.Cal_Theme .ajax__calendar_hover .ajax__calendar_year,
.Cal_Theme .ajax__calendar_active
{
color: #FFFFFF; font-weight:bold; background-color: #4A89B9;
}

.Cal_Theme .ajax__calendar_today
{
font-weight:bold;
}

.Cal_Theme .ajax__calendar_other,
.Cal_Theme .ajax__calendar_hover .ajax__calendar_today,
.Cal_Theme .ajax__calendar_hover .ajax__calendar_title
{
color: #000000;
}


---------------------------------------------------------------------------
Copy and paste the above stylesheet in one CSS file and Call to the control.

For Ex:
Add the below line in Head Section
<link href="../CSS/Calendar.css" rel="stylesheet" type="text/css" />


<cc1:CalendarExtender ID="txtdate_CalendarExtender" runat="server" Enabled="True"
CssClass="Cal_Theme" TargetControlID="txtdate">



OR


Use this Method to set the style for all Calendar extenders in the application

private void CheckExtenderControls(ControlCollection controlColl)
{
foreach (Control ctrl in controlColl)
{
if (ctrl.HasControls())
{
CheckExtenderControls(ctrl.Controls);
}
else
{
if (ctrl is AjaxControlToolkit.CalendarExtender)
{
AjaxControlToolkit.CalendarExtender calExt = (AjaxControlToolkit.CalendarExtender)ctrl;
calExt.CssClass = "Cal_Theme";
}
}
}
}

Crystal Report Binding using C#

For Crystal Report binding we can use this below code.

Parameters to be Passed
1. dsGlobal - Containing the Data for the Main and SubReport.
(We need to pass a Dummy Datatable if we dont have Databinding in Main Report apart from MainReport)
2. SFormName - The Crystal Report Name
3. sNamespace - To get the Full Path of the Crystal Report from a Project. If not remove this and give the full path in sFormName

This will create a temporary report and bind it with the Crystal report viewer.


private void ViewReport(DataSet dsGlobal, string sFormName, string sNamespace)
{
CrystalDecisions.Windows.Forms.CrystalReportViewer lobjReportViewer;
try
{
CrystalDecisions.Shared.DiskFileDestinationOptions lobjDFDO = new CrystalDecisions.Shared.DiskFileDestinationOptions();
CrystalDecisions.Shared.ExportOptions lobjOptions = new CrystalDecisions.Shared.ExportOptions();
ReportDocument lobjReport = new ReportDocument();

Assembly objAssembly = AppDomain.CurrentDomain.Load(sNamespace); ;
object objForm = null;
foreach (Type t in objAssembly.GetTypes())
{
if (t.Name.ToUpper() == sFormName.ToUpper())
{
objForm = Activator.CreateInstance(t);
break;
}
}
ReportDocument lobjDriverReport = (ReportDocument)objForm;
lobjReportViewer = new CrystalReportViewer();

if (lobjDriverReport.Subreports.Count > 0)
{
foreach (ReportDocument subrep in lobjDriverReport.Subreports)
{
if (dsGlobal.Tables[subrep.Name] != null)
{
lobjReport = lobjDriverReport.OpenSubreport(subrep.Name);
lobjReport.Database.Tables[0].SetDataSource(dsGlobal.Tables[subrep.Name]);
}
}
}

if (dsGlobal != null && dsGlobal.Tables.Count > 0 && dsGlobal.Tables[0] != null && dsGlobal.Tables.Count > 0 && dsGlobal.Tables[0].Rows.Count > 0)
{
lobjDriverReport.SetDataSource(dsGlobal.Tables[0]);
}

lobjDFDO.DiskFileName = System.Environment.CurrentDirectory + "\\" + System.DateTime.Now.ToLongDateString() + "Report.rpt";

lobjOptions.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile;
lobjOptions.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.CrystalReport;
lobjOptions.ExportDestinationOptions = lobjDFDO;
lobjDriverReport.Export(lobjOptions);

lobjReport = new ReportDocument();
lobjReport.Load(System.Environment.CurrentDirectory + "\\" + System.DateTime.Now.ToLongDateString() + "Report.rpt");

rptStorage.ReportSource = lobjReport;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}

Mail Sending Source using System.Net.Mail

private void SendMail()
{
try
{
MailMessage mail = new MailMessage();
SmtpClient smtp = new SmtpClient();
mail.Body = "Hello";
mail.IsBodyHtml = true;
mail.From = new MailAddress(FROMMailID, FROMNAME);
mail.Subject = "Subject";
mail.To.Add("saravanan@dotnetsnippets.com");
if (Txtccaddr.Text.Trim().Length > 0)
mail.CC.Add(Txtccaddr.Text.Trim());
if (Txtbccaddr.Text.Trim().Length > 0)
mail.Bcc.Add(Txtbccaddr.Text.Trim());
mail.Priority = MailPriority.High;

smtp.Host = HOST IP;
smtp.Port = 25;
smtp.Send(mail);
}
catch (Exception ex)
{

}
}

Centralise Date Format based on Regional and Language Setting

Datetime Format issues are the main thing that comes with Asp.Net application because of the Hosting Environment. If you have configured your application to run with default format of (MM/dd/yyyy) this would work fine with default setting in Regional & Language settings. If the format is changed the whole application ends up with Date Format errors. To overcome this we have a option which can be used to host the application in any environment without worrying for Date Format especially with LAPTOPS

using System.Globalisation;

public DateTime GetDateValue(string sDate, bool IsLongDate)
{
DateTime dt;
if (IsLongDate)
dt = DateTime.ParseExact(sDate, CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern.ToString() + " " + CultureInfo.CurrentCulture.DateTimeFormat.LongTimePattern.ToString(), CultureInfo.CurrentCulture, DateTimeStyles.NoCurrentDateDefault);
else
dt = DateTime.ParseExact(sDate, CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern.ToString(), CultureInfo.CurrentCulture, DateTimeStyles.NoCurrentDateDefault);
return dt;
}

public string GetDBDateValue(string sDate, bool IsLongDate)
{
DateTime dtDBDate;
DateTime dt = GetDateValue(sDate, IsLongDate);
if (!IsLongDate)
{
dtDBDate = new DateTime(dt.Year, dt.Month, dt.Day);
return dtDBDate.ToString("d", CultureInfo.CreateSpecificCulture("en-us"));
}
else
{
dtDBDate = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second);
return dtDBDate.ToString("G", CultureInfo.CreateSpecificCulture("en-us"));
}
}



In these the First Function is used to retrieve the Date as per the format specified in Regional Settings.

EX: string sDate = "22/03/2010";
Datetime dtFormatedDate = GetDateValue(sDate , true)
OUTPUT : [dtFormatedDate = 22/03/2010 12:00:00 a.m;] In a Datetime format even if the day comes first

This can be used anywhere in your source and the C# predicts the Day, Month and Year respectively.

But in case of SQL if you pass the same format that would throw an error of Invalid Date format. In such case use the Second function which just makes the DATE FORMAT appropriate to SQL Server format.

EX: string sDate = "22/03/2010";
string sFormatedDate = GetDBDateValue(sDate , false)
OUTPUT : [sFormatedDate = 03/22/2010;]

Create dynamically a User to Local User and Groups in C#

public void CreateUserAccount(string login, string password, string fullName, bool isAdmin)
{
try
{
DirectoryEntry dirEntry = new DirectoryEntry("WinNT://" + Environment.MachineName + ",computer");
DirectoryEntries entries = dirEntry.Children;
DirectoryEntry newUser = entries.Add(login, "user");
newUser.Properties["FullName"].Add(fullName);
newUser.Invoke("SetPassword", password);
newUser.CommitChanges();

// Remove the if condition along with the else to create user account in "user" group.
DirectoryEntry grp;
if (isAdmin)
{
grp = dirEntry.Children.Find("Administrators", "group");
if (grp != null) { grp.Invoke("Add", new object[] { newUser.Path.ToString() }); }
}
else
{
grp = dirEntry.Children.Find("Guests", "group");
if (grp != null) { grp.Invoke("Add", new object[] { newUser.Path.ToString() }); }
}

}
catch (Exception ex)
{

}
}


To Check for the User exists please refer the Prev posts on [checking Logon User Exists]

For Further Scripts on Logon User/Group management
Click Here
Click Here

Check dynamically if the Logon User already exists

public bool UserExists(string UserName)
{

DirectoryEntry directoryEntry = new DirectoryEntry("WinNT://" + Environment.MachineName);
foreach (DirectoryEntry child in directoryEntry.Children)
{
if (child.SchemaClassName == "User")
{
if (child.Name == UserName)
return false;
}
}
return true;
}

Accessing a Network Shared file from IIS/Local in C#

Steps To Be Followed To Open/Copy the Network Shared File
---------------------------------------------------------

1. Create a New User in Local Users and Groups by Right-clicking on MyComputer-->Manage





Create a User as Mentioned above in the System which hosts the Application and the System which shares the Files on a network.

The Username and password should be the same in both the systems.

2. Then use the below code and access the required File. The option of saving the network shared file is also available, If not you can just show the file in the same web page that requests for the File.

using System.Runtime.InteropServices;
using System.Security.Principal;
using System.IO;

public partial class PDFTest : System.Web.UI.Page
{
int LOGON32_LOGON_INTERACTIVE = 2;
int LOGON32_PROVIDER_DEFAULT = 0;
WindowsImpersonationContext impersonationContext;
[DllImport("advapi32.dll", CharSet = CharSet.Auto)]
public static extern int LogonUser(String lpszUserName, String lpszDomain, String lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken);
[DllImport("advapi32.dll", CharSet = System.Runtime.InteropServices.CharSet.Auto, SetLastError = true)]
public extern static int DuplicateToken(IntPtr hToken, int impersonationLevel, ref IntPtr hNewToken);
private bool impersonateValidUser(String userName, String domain, String password)
{
WindowsIdentity tempWindowsIdentity;
IntPtr token = IntPtr.Zero;
IntPtr tokenDuplicate = IntPtr.Zero;
if (LogonUser(userName, domain, password, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, ref token) != 0)
{
if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)
{
tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);
impersonationContext = tempWindowsIdentity.Impersonate();
if (impersonationContext != null)
return true;
else
return false;
}
else
return false;
}
else
return false;
}

private void undoImpersonation()
{
impersonationContext.Undo();
}

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

private bool doDownload()
{
try
{
string strPath = @"\\REMOTEMACHINE\SHAREDFOLDER";
bool bFileExists = false;
if (strPath.Substring(strPath.Length) != "\\")
{
strPath = strPath + "\\";
}
string strFileLocation = strPath + "FILENAME";
if (impersonateValidUser("TestAcc", Environment.MachineName, "TESTAccPASSWORD"))
{
bFileExists = System.IO.File.Exists(strFileLocation);
}
if (!bFileExists)
{
//File does not exist!
undoImpersonation();
Response.Redirect("error.aspx?m=There was an error while attempting to access the file you selected. Please re-try your download.");
return;
}
//Writing to the Same Web Page
long lCurrentPosition = 0;
int intBytesRead = 0;
FileStream objFileStream = System.IO.File.Open(strFileLocation, FileMode.Open, FileAccess.Read, FileShare.Read);
byte[] arrBuffer = new byte[objFileStream.Length+20];
objFileStream.Position = lCurrentPosition;
intBytesRead = objFileStream.Read(arrBuffer, 0, arrBuffer.Length);
lCurrentPosition = objFileStream.Position;
objFileStream.Close();

if (arrBuffer != null)
{
Response.Clear();
Response.ContentType = "application/pdf";
Response.AddHeader("content-length", arrBuffer.Length.ToString());
Response.AddHeader("Content-Type", "application/pdf");
Response.BinaryWrite(arrBuffer);
//Response.End();
}


//Saving/Opening the Network PDF
//Response.ClearContent();
//Response.ClearHeaders();
//Response.ContentType = "application/octet-stream";
//Response.AddHeader("Content-Disposition", "attachment;filename=" + "sss.pdf");
//Response.AddHeader("Content-Length", new FileInfo(strFileLocation).Length.ToString());
//long lCurrentPosition = 0;
//byte[] arrBuffer = new byte[524288];
//int intBytesRead = 0;
//FileStream objFileStream = System.IO.File.Open(strFileLocation, FileMode.Open, FileAccess.Read, FileShare.Read);
//objFileStream.Position = lCurrentPosition;
//intBytesRead = objFileStream.Read(arrBuffer, 0, arrBuffer.Length);
//lCurrentPosition = objFileStream.Position;
//objFileStream.Close();
//while (intBytesRead > 0 && Response.IsClientConnected)
//{
// Response.OutputStream.Write(arrBuffer, 0, intBytesRead);
// Response.Flush();
// try
// {
// objFileStream = System.IO.File.OpenRead(strFileLocation);
// objFileStream.Position = lCurrentPosition;
// intBytesRead = objFileStream.Read(arrBuffer, 0, arrBuffer.Length);
// lCurrentPosition = objFileStream.Position;
// }
// catch (Exception ex)
// {
// //catch puke here
// }
// finally
// {
// objFileStream.Close();
// }
//}
undoImpersonation();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
}

If you find any difficulties in this code let me know..

Creating a CSV Text from Table Columns

Select Name from Users


Name
----
Ramu
Saju
Nattu


SELECT SUBSTRING((SELECT ',' + s.Name FROM Users s ORDER BY s.Name FOR XML PATH('')),2,200000) AS CSV

Splitting Text with Extra Characters to a Table

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function fn_ParseText2Table
(
@p_SourceText varchar(8000)
,@p_Delimeter varchar(100) = ',' --default to comma delimited.

)
RETURNS @retTable TABLE
(
Position int identity(1,1)
,Int_Value int
,Num_value Numeric(18,3)
,txt_value varchar(2000)
)
AS

BEGIN
DECLARE @w_Continue int
,@w_StartPos int
,@w_Length int
,@w_Delimeter_pos int
,@w_tmp_int int
,@w_tmp_num numeric(18,3)
,@w_tmp_txt varchar(2000)
,@w_Delimeter_Len tinyint
if len(@p_SourceText) = 0
begin
SET @w_Continue = 0 -- force early exit

end
else
begin
-- parse the original @p_SourceText array into a temp table

SET @w_Continue = 1
SET @w_StartPos = 1
SET @p_SourceText = RTRIM( LTRIM( @p_SourceText))
SET @w_Length = DATALENGTH( RTRIM( LTRIM( @p_SourceText)))
SET @w_Delimeter_Len = len(@p_Delimeter)
end
WHILE @w_Continue = 1
BEGIN
SET @w_Delimeter_pos = CHARINDEX( @p_Delimeter
,(SUBSTRING( @p_SourceText, @w_StartPos
,((@w_Length - @w_StartPos) + @w_Delimeter_Len)))
)

IF @w_Delimeter_pos > 0 -- delimeter(s) found, get the value

BEGIN
SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos
,(@w_Delimeter_pos - 1)) ))
if isnumeric(@w_tmp_txt) = 1
begin
set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
end
else
begin
set @w_tmp_int = null
set @w_tmp_num = null
end
SET @w_StartPos = @w_Delimeter_pos + @w_StartPos + (@w_Delimeter_Len- 1)
END
ELSE -- No more delimeters, get last value

BEGIN
SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos
,((@w_Length - @w_StartPos) + @w_Delimeter_Len)) ))
if isnumeric(@w_tmp_txt) = 1
begin
set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
end
else
begin
set @w_tmp_int = null
set @w_tmp_num = null
end
SELECT @w_Continue = 0
END
INSERT INTO @retTable VALUES( @w_tmp_int, @w_tmp_num, @w_tmp_txt )
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



select * from dbo.fn_ParseText2Table('101, 201, 223, 443', ',')

Position Int_Value Num_value txt_value
----------- ----------- -------------------- ----------
1 NULL NULL 101
2 NULL NULL 201
3 NULL NULL 223
4 NULL NULL 443

Date Formats Available in SQL Server

ID	Date Formats
0 or 100	mon dd yyyy hh:miAM (or PM)
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
13 or 113	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
20 or 120	yyyy-mm-dd hh:mi:ss(24h)
21 or 121	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

Encoding and Decoding Using UnicodeEncoding in C#

//Include this line in Declaration

UnicodeEncoding Unicode = new UnicodeEncoding();

To Encode a String
-------------------

string inputString = "This string contains the unicode character";
byte[] FilterBytes = Unicode.GetBytes(inputString);
string FilterASCII = System.Text.ASCIIEncoding.ASCII.GetString(FilterBytes);
byte[] AsciiBytes = Unicode.GetBytes(FilterASCII);
string sEncodedText = Convert.ToBase64String(AsciiBytes);

To Decode the Above Encoded String
----------------------------------

byte[] FileTextBytes = Convert.FromBase64String(sEncodedText);
string ASCIItext = Unicode.GetString(FileTextBytes);
byte[] ASCIIBytes = System.Text.ASCIIEncoding.ASCII.GetBytes(ASCIItext);
string sDecodedText = Unicode.GetString(ASCIIBytes);

Export Gridview to PDF using C#.Net for Both AutoGenerate Column and Template Columns

public void ExportToPDF(string ReportName, GridView grdPDF, bool isLandscape,string Filename)
{
int noOfColumns = 0, noOfRows = 0;
DataTable tbl = null;
if (grdPDF.AutoGenerateColumns)
{
tbl = grdPDF.DataSource as DataTable; // Gets the DataSource of the GridView Control.
noOfColumns = tbl.Columns.Count;
noOfRows = tbl.Rows.Count;
}
else
{
noOfColumns = grdPDF.Columns.Count;
noOfRows = grdPDF.Rows.Count;
}
float HeaderTextSize = 8;
float ReportNameSize = 10;
float ReportTextSize = 8;
float ApplicationNameSize = 7;

// Creates a PDF document
Document document = null;
if (isLandscape == true)
{
// Sets the document to A4 size and rotates it so that the orientation of the page is Landscape.
document = new Document(PageSize.A4.Rotate(), 0, 0, 15, 5);
}
else
{
document = new Document(PageSize.A4, 0, 0, 15, 5);
}

PdfPTable mainTable = new iTextSharp.text.pdf.PdfPTable(noOfColumns);
// Sets the first 4 rows of the table as the header rows which will be repeated in all the pages.
mainTable.HeaderRows = 4;

// Creates a PdfPTable with 2 columns to hold the header in the exported PDF.
PdfPTable headerTable = new iTextSharp.text.pdf.PdfPTable(2);
iTextSharp.text.Image imgLogo = iTextSharp.text.Image.GetInstance(HttpContext.Current.Server.MapPath("../Images/medtech_logo.jpg"));
imgLogo.ScaleToFit(100, 25);

PdfPCell clApplicationName = new PdfPCell(imgLogo);
clApplicationName.Border = PdfPCell.NO_BORDER;
clApplicationName.HorizontalAlignment = Element.ALIGN_LEFT;

// Creates a phrase to show the current date at the right hand side of the header.
Phrase phDate = new Phrase(DateTime.Now.Date.ToString("dd-MMM-yyyy"), FontFactory.GetFont("Arial", ApplicationNameSize, iTextSharp.text.Font.NORMAL));
// Creates a PdfPCell which accepts the date phrase as a parameter.
PdfPCell clDate = new PdfPCell(phDate);
// Sets the Horizontal Alignment of the PdfPCell to right.
clDate.HorizontalAlignment = Element.ALIGN_RIGHT;
// Sets the border of the cell to zero.
clDate.Border = PdfPCell.NO_BORDER;

headerTable.AddCell(clApplicationName);
headerTable.AddCell(clDate);
headerTable.DefaultCell.Border = PdfPCell.NO_BORDER;

// Creates a PdfPCell that accepts the headerTable as a parameter and then adds that cell to the main PdfPTable.
PdfPCell cellHeader = new PdfPCell(headerTable);
cellHeader.Border = PdfPCell.NO_BORDER;
// Sets the column span of the header cell to noOfColumns.
cellHeader.Colspan = noOfColumns;
// Adds the above header cell to the table.
mainTable.AddCell(cellHeader);

// Creates a phrase which holds the file name.
Phrase phHeader = new Phrase(sApplicationnName, FontFactory.GetFont("Arial", ReportNameSize, iTextSharp.text.Font.BOLD));
PdfPCell clHeader = new PdfPCell(phHeader);
clHeader.Colspan = noOfColumns;
clHeader.Border = PdfPCell.NO_BORDER;
clHeader.HorizontalAlignment = Element.ALIGN_CENTER;
mainTable.AddCell(clHeader);

// Creates a phrase for a new line.
Phrase phSpace = new Phrase("\n");
PdfPCell clSpace = new PdfPCell(phSpace);
clSpace.Border = PdfPCell.NO_BORDER;
clSpace.Colspan = noOfColumns;
mainTable.AddCell(clSpace);

// Sets the gridview column names as table headers.
for (int i = 0; i < noOfColumns; i++)
{
Phrase ph = null;
if (grdPDF.AutoGenerateColumns)
ph = new Phrase(tbl.Columns[i].ColumnName, FontFactory.GetFont("Arial", HeaderTextSize, iTextSharp.text.Font.BOLD));
else
ph = new Phrase(grdPDF.Columns[i].HeaderText, FontFactory.GetFont("Arial", HeaderTextSize, iTextSharp.text.Font.BOLD));
mainTable.AddCell(ph);
}

// Reads the gridview rows and adds them to the mainTable
for (int rowNo = 0; rowNo < noOfRows; rowNo++)
{
for (int columnNo = 0; columnNo < noOfColumns; columnNo++)
{
if (grdPDF.AutoGenerateColumns)
{
string s = grdPDF.Rows[rowNo].Cells[columnNo].Text.Trim();
Phrase ph = new Phrase(s, FontFactory.GetFont("Arial", ReportTextSize, iTextSharp.text.Font.NORMAL));
mainTable.AddCell(ph);
}
else
{
string s = "";
if (grdPDF.Columns[columnNo] is TemplateField)
{
DataBoundLiteralControl lc = grdPDF.Rows[rowNo].Cells[columnNo].Controls[0] as DataBoundLiteralControl;
if (lc != null)
{
s = lc.Text.Trim();
}
else
{
for (int i = 0; i < grdPDF.Rows[rowNo].Cells[columnNo].Controls.Count; i++)
{
if (grdPDF.Rows[rowNo].Cells[columnNo].Controls[i].GetType() == typeof(TextBox))
{
s = (grdPDF.Rows[rowNo].Cells[columnNo].Controls[i] as TextBox).Text;
}
else if (grdPDF.Rows[rowNo].Cells[columnNo].Controls[i].GetType() == typeof(Label))
{
s = (grdPDF.Rows[rowNo].Cells[columnNo].Controls[i] as Label).Text;
}
else if (grdPDF.Rows[rowNo].Cells[columnNo].Controls[i].GetType() == typeof(System.Web.UI.WebControls.Image))
{
s = (grdPDF.Rows[rowNo].Cells[columnNo].Controls[i] as System.Web.UI.WebControls.Image).ToolTip;
}
}
}
Phrase ph = new Phrase(s, FontFactory.GetFont("Arial", ReportTextSize, iTextSharp.text.Font.NORMAL));
mainTable.AddCell(ph);
}
else
{
s = grdPDF.Rows[rowNo].Cells[columnNo].Text.Trim();
Phrase ph = new Phrase(s, FontFactory.GetFont("Arial", ReportTextSize, iTextSharp.text.Font.NORMAL));
mainTable.AddCell(ph);
}
}
}
mainTable.CompleteRow();
}

PdfWriter.GetInstance(document, HttpContext.Current.Response.OutputStream);
HeaderFooter pdfFooter = new HeaderFooter(new Phrase(), true);
pdfFooter.Alignment = Element.ALIGN_CENTER;
pdfFooter.Border = iTextSharp.text.Rectangle.NO_BORDER;
document.Footer = pdfFooter;
document.Open();
document.Add(mainTable);
document.Close();

HttpContext.Current.Response.ContentType = "application/pdf";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename= "+ Filename +"_"+ DateTime.Now.ToString("dd_MMM_yyyy") +".pdf");
HttpContext.Current.Response.End();
}


ITextSharp Dll Download link

Opening a PDF from ASP.NET

To open PDF i have mentioned two methods as below,

1. Process[] processes = System.Diagnostics.Process.GetProcessesByName("AcroRd32");
foreach (Process p in processes)
{
p.Kill();
p.Refresh();
}
System.Threading.Thread.Sleep(1000);
System.Diagnostics.Process.Start(strPDFPath);

2. System.Net.WebClient client = new System.Net.WebClient();
Byte[] buffer = client.DownloadData(strPDFPath);
Session["BUFFER"] = buffer;
ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "PDF", "window.open('../pdf.aspx','_blank');", true);


In "pdf.aspx.cs" file use this code

protected void Page_Load(object sender, EventArgs e)
{
try
{
if (Session["BUFFER"] != null)
{
byte[] buffer = (Byte[])Session["BUFFER"];

if (buffer != null)
{
Response.Clear();
Response.ContentType = "application/pdf";
Response.AddHeader("content-length", buffer.Length.ToString());
Response.AddHeader("Content-Type", "application/pdf");
Response.BinaryWrite(buffer);
Response.End();
}
}
}
catch (Exception)
{

}
}

Setting permission to the folder programmatically using C#.Net

using System.IO;
using System.Security.AccessControl;
using System.Security.Principal;



string dirpath = "C:\\Web\\DirPath";
DirectoryInfo dirinfo = new DirectoryInfo(dirpath);
string users = @"NETWORK SERVICE"; //@"ASPNET" // @"EVERYONE"
if (dirinfo.Exists)
{
DirectorySecurity dirsec = dirinfo.GetAccessControl();
FileSystemAccessRule fsar = new FileSystemAccessRule(new NTAccount(users), FileSystemRights.FullControl, InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit, PropagationFlags.None, AccessControlType.Allow);
dirsec.AddAccessRule(fsar);
dirinfo.SetAccessControl(dirsec);
}

AutoComplete Extender Without WebService

1. Creating a Common class file to GetAutoCompleteList items

public string[] GetAutoCompleteList(string strPrefix)
{
DataTable dt = new DataTable();
//Create [SP/Query] to Get the Items from Database
//Assume "dt" has got Records from Database
string[] items = new string[dt.Rows.Count];
int i = 0;
foreach (DataRow dr in dt.Rows)
{
items.SetValue(dr["COLNAME"].ToString(), i);
i++;
}
return items;
}


2. This Web Method can be created within the "page.aspx.cs" file.

[WebMethod]
public static string[] GetAutoCompleteItems(string prefixText)
{
ClsFile objFile = new ClsFile();
return objFile.GetAutoCompleteList(prefixText);
}
3. This following code is to be used in the "page.aspx" file

(Start Tag)asp:TextBox ID="txtListItems" AutoComplete="off" style="text-transform:capitalize" ToolTip="Enter List Item Prefix" Width="130px" runat="server" OnTextChanged=" txtListItems _TextChanged" AutoPostBack="True"(End Tag)
(Start Tag)cc1:AutoCompleteExtender ID=" txtListItems _AutoCompleteExtender" runat="server" ServiceMethod=" GetAutoCompleteItems " DelimiterCharacters=";, :" Enabled="True" TargetControlID=" txtListItems " MinimumPrefixLength="1" CompletionInterval="10" FirstRowSelected="true" EnableCaching="true" CompletionSetCount="12" (End Tag)

Using Collapsible Panel Inside a DataList

SQL convert number to text

CREATE FUNCTION fnMoneyToEnglish(@Money AS money)

RETURNS VARCHAR(1024)

AS

BEGIN

DECLARE @Number as BIGINT

SET @Number = FLOOR(@Money)

DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

INSERT @Below20 (Word) VALUES

( 'Zero'), ('One'),( 'Two' ), ( 'Three'),

( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),

( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),

( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),

( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),

('Eighteen' ), ( 'Nineteen' )

INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),

('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

DECLARE @English varchar(1024) =

(

SELECT Case

WHEN @Number = 0 THEN ''

WHEN @Number BETWEEN 1 AND 19

THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

WHEN @Number BETWEEN 20 AND 99

-- SQL Server recursive function

THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' +

dbo.fnMoneyToEnglish( @Number % 10)

WHEN @Number BETWEEN 100 AND 999

THEN (dbo.fnMoneyToEnglish( @Number / 100))+' Hundred '+

dbo.fnMoneyToEnglish( @Number % 100)

WHEN @Number BETWEEN 1000 AND 999999

THEN (dbo.fnMoneyToEnglish( @Number / 1000))+' Thousand '+

dbo.fnMoneyToEnglish( @Number % 1000)

WHEN @Number BETWEEN 1000000 AND 999999999

THEN (dbo.fnMoneyToEnglish( @Number / 1000000))+' Million '+

dbo.fnMoneyToEnglish( @Number % 1000000)

ELSE ' INVALID INPUT' END

)

SELECT @English = RTRIM(@English)

SELECT @English = RTRIM(LEFT(@English,len(@English)-1))

WHERE RIGHT(@English,1)='-'

IF @@NestLevel = 1

BEGIN

SELECT @English = @English+' Dollars and '

SELECT @English = @English+

convert(varchar,convert(int,100*(@Money - @Number))) +' Cents'

END

RETURN (@English)

END

GO


SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 67)

-- Sixty-Seven Dollars and 0 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 947.54)

-- Nine Hundred Forty-Seven Dollars and 54 Cents

SELECT NumberInEnglish=dbo.fnMoneyToEnglish ( 1266.04)

-- One Thousand Two Hundred Sixty-Six Dollars and 4 Cents