, ,

SQL Function Reference:Oracle vs.SQL Server vs. My SQL Part- III

Date Functions

Function - Date addition
Oracle - (use +)
My Sql - Need To Update

Function - Date subtraction
Oracle - (use -)
My Sql - Need To Update

Function - Last day of month
Oracle - LAST_DAY
SQL Server - N/A
My Sql - Need To Update

Function -Time zone conversion
Oracle - NEW_TIME
SQL Server - N/A
My Sql - Need To Update

Function -First weekday after date
Oracle - NEXT_DAY
SQL Server - N/A
My Sql - Need To Update

Function -Convert date to string
Oracle -TO_CHAR
My Sql - Need To Update

Function -Convert date to number
My Sql - Need To Update

Function -Convert string to date
SQL Server - CAST
My Sql - Need To Update

Function -Get current date and time
SQL Server - GETDATE()
My Sql - Now()
Read More
, ,

SQL Function Reference:Oracle vs.SQL Server vs. My SQL Part- II

String Functions

Function - Convert character to ASCII
Oracle - ASCII
SQL Server - ASCII
My Sql - ASCII

Function -String concatenate
Oracle - CONCAT
SQL Server - (expression + expression)

Function -Convert ASCII to character
Oracle - CHR
SQL Server - CHAR

Function -Return starting point of character in character string (from left)
Oracle - INSTR
My Sql - INSTR

Function -Convert characters to lowercase
Oracle - LOWER
SQL Server - LOWER
My Sql - LOWER

Function -Convert characters to uppercase
Oracle - UPPER
SQL Server - UPPER
My Sql - UPPER

Function -Pad left side of character string
Oracle - LPAD
SQL Server - N/A
My Sql - LPAD

Function -Remove leading blank spaces
Oracle - LTRIM
SQL Server - LTRIM
My Sql - LTRIM

Function -Remove trailing blank spaces
Oracle - RTRIM
My Sql - RTRIM

Function -Starting point of pattern in character string
Oracle - INSTR
My Sql - INSTR

Function -Repeat character string multiple times
Oracle - RPAD
My Sql - RPAD

Function -Phonetic representation of character string
Oracle - SOUNDEX

Function -String of repeated spaces
Oracle - RPAD
My Sql - RPAD

Function -Character data converted from numeric data
Oracle - TO_CHAR
SQL Server -STR
My Sql - Need To Update

Function - Substring
Oracle - SUBSTR
My Sql - MID

Function - Replace characters
Oracle - REPLACE

Function - Capitalize first letter of each word in string
Oracle - INITCAP
SQL Server -N/A
My Sql - Need To Update

Function - Translate character string
SQL Server -N/A
My Sql - Need To Update

Function - Length of character string
Oracle -LENGTH

Function - Greatest character string in list
SQL Server - N/A
My Sql - Need To Update

Function - Least character string in list
Oracle -LEAST
SQL Server - N/A
My Sql - Need To Update

Function - Convert string if NULL
Oracle -NVL
My Sql - Need To Update

Visit Part III (Date Functions)
Read More
, ,

SQL Function Reference:Oracle vs.SQL Server vs. My SQL Part- I

Generally the major issue we face while development is the availability of common functions in different database(s).

I am facing this issue from last so many years. Then I had start preparing a table for common functions of different database(s).

The commonly used databases are Oracle, Sql Server and My Sql. Their is so many other database(s) are also available but commonly I works on these database(s) only.

Following is the common list of functions in different database(s):-

Math Functions
Function - Absolute value
Oracle - ABS
SQL Server - ABS
My Sql - ABS

Function - Arc cosine
Oracle - ACOS
SQL Server - ACOS
My Sql - ACOS

Function - Arc sine
Oracle - ASIN
SQL Server - ASIN
My Sql - ASIN

Function - Arc tangent of n
Oracle - ATAN
SQL Server - ATAN
My Sql - ATAN

Function - Arc tangent of n and m
Oracle - ATAN2
SQL Server - ATAN2
My Sql - Please Suggest

Function - Smallest integer >= value
Oracle - CEIL
My Sql - Please Suggest

Function - Cosine
Oracle - COS
SQL Server - COS
My Sql - COS

Function - Hyperbolic cosine
Oracle - COSH
SQL Server - COT
My Sql - Please Suggest

Function - Exponential value
Oracle - EXP
SQL Server - EXP
My Sql -EXP

Function - Round down to nearest integer
Oracle - FLOOR
SQL Server - FLOOR

Function - Natural logarithm
Oracle - LN
SQL Server - LOG
My Sql - Please Suggest

Function - Logarithm, any base
Oracle - LOG(N)
SQL Server - N/A
My Sql - Please Suggest

Function - Logarithm, base 10
Oracle - LOG(10)
SQL Server - LOG10
My Sql - Please Suggest

Function - Modulus (remainder)
Oracle - MOD
My Sql - Please Suggest

Function - Power
Oracle - POWER
SQL Server - POWER
My Sql - POWER

Function - Random number
Oracle - N/A
SQL Server - RAND
My Sql - Please Suggest

Function - Round
Oracle - ROUND
SQL Server - ROUND
My Sql - ROUND

Function - Sign of number
Oracle - SIGN
SQL Server - SIGN
My Sql - SIGN

Function - Sine
Oracle - SIN
SQL Server - SIN
My Sql - SIN

Function - Hyperbolic sine
Oracle - SINH
SQL Server - N/A
My Sql - Please Suggest

Function - Square root
Oracle - SQRT
SQL Server - SQRT
My Sql - SQRT

Function - Tangent
Oracle - TAN
SQL Server - TAN
My Sql - TAN

Function - Hyperbolic tangent
Oracle - TANH
SQL Server - N/A
My Sql - Please Suggest

Function - Truncate
Oracle - TRUNC
SQL Server - N/A
My Sql - Please Suggest

Function - Highest number in list
SQL Server - N/A
My Sql - Please Suggest

Function - Lowest number in list
Oracle - LEAST
SQL Server - N/A
My Sql - Please Suggest

Function - Convert number if NULL
Oracle - NVL
My Sql - Please Suggest

Function - Standard deviation
Oracle - STDDEV
My Sql - Please Suggest

Function - Variance
SQL Server - VAR
My Sql - Please Suggest

This is not the end of functions or post. I will post string and datetime functions in coming articles. :)

Visit Part II (String Functions)
Read More
, , , ,

How to get tables names in MySql (sysobjects in sql server)

In Sql server we have a table sysobjects which holds names of all the tables i.e. system and user tables.

Like the same we have command “Show tables” which show all the tables of specified database.

Show Tables from <databasename>

Here is an example.

If you had already select a database you can only use
Show tables to show all the tables in the database. For ex. Select database MySql and in Sql command window enter following text


And click on Run.

It will show all the tables which is avalible in the list
Read More
, , , , , , ,

How to take back up of my sql database using C#

downloadYou can also download running code.

Taking a back up of my sql using C# is very easy but this also be termed as tricky backup. Like Sql server my sql does not provide built in stored procedures to take back up. For back up my sql use a Exe file named “mysqldump.exe”

You will find this Exe file in “C:\Program Files\MySQL\MySQL Server 5.0\bin” folder of mySql.

This file takes so many parameters for different options. But my task is to simply take a backup of single database

To take a backup of single database following are the parameters

mysqldump -u<usernane> -p <password> -h<servername or ip> <databasename> > <localtion to take backup>

Please let me know if this helps you to perform your task easily.
Read More
, , , , , , , , , ,

Auto Complete Example in Asp.net C# Using JQuery

downloadDownload Running Code Supporting JQuery

Before writing this post I had written one another post which is using a pure JavaScript to show Auto complete or Auto delete example.

My previous example is not using Jquery which is a famous and reliable way to present your data.

One of the drawback of my previous example is it will become slow as I have to load a bulk data for auto complete box.

I had received many comments on this post, some of them had appreciated and some of them had suggested using Jquery.

One of my great readers had given an example of Jquey which I am posting to help you all. So, before posting this example I would like to say thanks to my reader Eng. Mohammed Ibrahim who had suggested and helped me to help others.
Read More
, , , , ,

Auto Complete Example in Asp.net C#

There are too many methods to make an auto complete text box in asp.net

You can use JQuery (Use jquery for Auto Complete), Ajax Auto complete extender and so on. If you will search on internet you will find so many examples which are using JQuery or Ajax Extender to show auto complete on websites.

I had searched on many sites I had tried so many examples to populate auto complete list but, unfortunately, I am not able to find an efficient auto complete example.

Finally after searching for many hours I had decided to write my own code.

My example is using an asp.net C# to show auto complete. Here, code goes
Download Code

Run and try typing some numbers or text. You will find an auto complete list on page
Read More
, , , , , ,

How to use Enterprise Library using C#


The Microsoft Enterprise Library is a collection of reusable software components (application blocks) designed to assist software developers with common enterprise development cross-cutting concerns (such as logging, validation, data access, exception handling, and many others). Application blocks are a type of guidance; they are provided as source code, test cases, and documentation that can be used "as is," extended, or modified by developers to use on complex, enterprise-level line-of-business development projects.

Some Features

While using enterprise library you have to concern about opening and closing connections.
This will automatically handle connection pooling and other important errors of connection and database.

I had used Enterprise Library 2.0 - January 2006.
To use Enterprise Library you required to add reference of following files:-

  • Microsoft.Practices.EnterpriseLibrary.Common.dll
  • Microsoft.Practices.EnterpriseLibrary.Configuration.Design.dll
  • Microsoft.Practices.EnterpriseLibrary.Data.dll
  • Microsoft.Practices.ObjectBuilder.dll
View code below which I had used to connect one of my tables to master database.

#region Using

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;


public class spt_monitor


public DataTable Read()




Database db = DatabaseFactory.CreateDatabase("masterConnection");

DbCommand dbCommand = db.GetSqlStringCommand("Select * from spt_monitor");

DataSet dataSet = db.ExecuteDataSet(dbCommand);

return dataSet.Tables[0];




return null;




This code is simply loading a dataset without much code.

Download Enterprise Library from following links:-

Download another tool which will generate code for Enterprise Library from crococode.com


You can also download code running code which is using master database as example.


Goals for Enterprise Library

Enterprise Library is a collection of application blocks intended for use by developers who build complex, enterprise-level applications.

Enterprise Library is used when building applications that are typically to be deployed widely and to interoperate with other applications and systems. In addition, they generally have strict security, reliability, and performance requirements.

The goals of Enterprise Library are the following:

  • Consistency. All Enterprise Library application blocks feature consistent design patterns and implementation approaches.
  • Extensibility. All application blocks include defined extensibility points that allow developers to customize the behavior of the application blocks by adding their own code.
  • Ease of use. Enterprise Library offers numerous usability improvements, including a graphical configuration tool, a simpler installation procedure, and clearer and more complete documentation and samples.
  • Integration. Enterprise Library application blocks are designed to work well together or individually.
Read More
, , ,

How to create base class in C#


One of the many benefits of object-oriented programming is that it allows for reuse of logic. For example, classes can be created that contain a base level of functionality. These base classes can then be extended through inheritance to create new classes that encompass the functionality of the base class along with any custom logic needed. The end result is that as a developer, once the base class has been created, you can extend and enhance the functionality of the base class with minimal effort.

Since the .NET Framework is built upon the principles of object-oriented programming (OOP), it's no surprise that ASP.NET borrows heavily from the tenets of OOP, one such tenet being inheritance. The base functionality for all ASP.NET pages is spelled out by the Page class in the System.Web.UI namespace. This class defines the essential properties, methods, and
events for an ASP.NET page, including such members as:
  • The intrinsic objects - Response, Request, Session, and so on,
  • Common properties - IsPostBack, IsValid, and others,
  • Methods used throughout the page lifecycle, such as SavePageViewState(),

ProcessRequest(), RaiseChangedEvents(), and others.

While all ASP.NET pages must be derived from the Page class, they need not be directly derived. That is, an ASP.NET page may extend a class that, itself, extends the Page class.

In fact, when using the code-behind model for creating ASP.NET pages the actual ASP.NET page
is derived from the code-behind class, with the code-behind class being derived from the Page class.

/***********Why I had used *****************************/
Let me describe why I had used base class.

I have a website of around 100 pages; most of the pages are only available after login.

To restrict unauthorized access to pages generally we check for session values.

I have a session variable which contains a value, if that variable is null I have to assume this is an unauthorized user.

Now, what I had done to resolve the problem I had made a base class which will contains my code of checking session variables and redirect to login page. Then I had simply inherited page from the page base class to reduce my code and increase efficiently of website

download code which I had written to use base class

In fact, oftentimes it makes sense to create a base class for a particular ASP.NET Web application that extends the Page class and have all code-behind classes derive from this class, rather than directly from the Page class. This universal base class can contain its own properties, methods, or events that are common to all pages in the particular ASP.NET application, or it can extend the functionality of existing methods or properties.

Regardless of whether the code-behind model or inline script model is used, when the .aspx page is visited, the ASP.NET engine translates the markup section into a class and then compiles that class. If the code-behind model is being used, then the auto-generated class is derived from the page's corresponding code-behind class. On the other hand, if the inline script model is being used then the auto-generated class is derived directly from the Page class. The following diagram illustrates the resulting type graph used by both models.

Regardless of whether the code-behind model or inline script model is used, when the .aspx page is visited, the ASP.NET engine translates the markup section into a class and then compiles that class. If the code-behind model is being used, then the auto-generated class is derived from the page's corresponding code-behind class. On the other hand, if the inline script model is being used then the auto-generated class is derived directly from the Page class. The following diagram illustrates the resulting type graph used by both models.

The Basics of a Base Class
As the type graph examined above shows, the auto-generated ASP.NET page class must extend the Page class, but can do so indirectly, through a code-behind class, for example. But there's no reason why there can only be one level of encapsulation between the auto-generated class and the essential Page class. You can optionally create a base class from which all pages will derive from. When creating a base class, you'll typically have this base class extend the Page class. If you are using the code-behind model your code-behind classes will need to be modified to inherit from the new base class as opposed to the Page class. If you are using the inline script block model, you'll need to use the @Page directive's Inherits keyword to specify the type name of the class you want the auto-generated class to extend. With a custom base class the type graph for an ASP.NET page morphs into the one shown below:

As you can see in the type graph, the base class must derive from the Page class. ASP.NET page's that want to utilize the functionality of the base class will derive from this base class rather than from the Page class directly.

When creating a base class you might decide to add additional properties or methods, which are as simple as adding the appropriate properties and methods to the base class itself. Additionally, you'll likely need to extend the functionality of the Page class, adding logic at particular points in the page's lifecycle, such as common code that should run in response to the Page's Load event. To accomplish this, you'll want to override the appropriate method: OnInit to respond to the Init event; OnLoad to respond to the Load event; and OnPreRender to respond to the PreRender event.
Read More
, , , , , , ,

How to send e-mail using C# with Attachment

When I am working in core asp mail sending is one of the biggest task.

For mail sending I have to check for third party on server. I have to according to third party avalibale on different server.

But, now say thanks to Microsoft who had made that task easy.

Sending a email using ASP.NET 2.0 and C# 2.0 is actually very simple.

First, you will need to import the System.Net.Mail namespace.

The System.Net.Mail namespace contains the SmtpClient and MailMessage Classes that we need in order to send the email.

You have to write this code to your page.

public static void Send(string SMTPServerName, string SMTPUserName, string SMTPPassWord, string MailFrom, string MailTo, string CC, string BCC, string Subject, string Body, bool BodyHTML, string Attachment)
MailMessage message = new MailMessage(MailFrom, MailTo, Subject, Body);

//Adding multiple To Addresses
foreach (string sTo in MailTo.Split(",".ToCharArray()))
if (MailTo != sTo)

//Adding multiple CC Addresses
if (CC.Length > 0)
foreach (string sCC in CC.Split(",".ToCharArray()))

//Adding multiple BCC Addresses
if (BCC.Length > 0)
foreach (string sBCC in BCC.Split(",".ToCharArray()))
//Adding multiple BCC Addresses
if (Attachment.Length > 0)
foreach (string sAttachment in Attachment.Split(",".ToCharArray()))
Attachment attachment = new Attachment(sAttachment);
SmtpClient emailClient = new SmtpClient(SMTPServerName);
System.Net.NetworkCredential SMTPUserInfo = new System.Net.NetworkCredential(SMTPUserName, SMTPPassWord);
emailClient.UseDefaultCredentials = false;
emailClient.Credentials = SMTPUserInfo;
message.IsBodyHtml = BodyHTML;

Or you can also download code from.
Read More
, , , , , ,

How to Make Windows Service Using C# 2008

I have an task to run a Stored procedure after every 30 minutes.
My first choice is to create a job on Sql server. But I also have to write a log file along with the stored procedure.

Now, my choice will be Windows service. Creating a windows service is a very easy task but how to deply......

let me explain how to work with windows service.

Creating a program that can run in the background of the operating system has any benefits, this is very important as the program can run without a graphic user interface. Complex service that are to run are longer time, and without the need the user to login the system. Windows services are harder to develop, debug, and test. But if you software are to run in server that require to start up automatically with the server is turn on, Windows service is your choice.

Creating your skeleton project in Visual Studio 2008

To create a Windows Service project in Visual Studio 2008, you must select from the Visual C# Windows Projects. Give your Windows Service a name, and then select OK

As a result Visual Studio 2008 creates you an empty solution with a Windows Service project as a default to the development.

Switch to code view, to start developing.

As you can see that Visual Studio 2008 has simplify the layout and initialization of the code for Windows Service development. Now let’s start by creating the PingJob() method;

public partial class Service1 : ServiceBase
public Service1()

protected override void OnStart(string[] args)
System.Threading.ThreadStart job = new System.Threading.ThreadStart(PingJob);

System.Threading.Thread thread = new System.Threading.Thread(job);


protected override void OnStop()

static void PingJob()
while (true)
string fname = "c:\\a.txt";
FileInfo file1 = new FileInfo(fname);
StreamWriter sw = File.AppendText("c:\\a.txt");
sw.WriteLine("This is a demo for appending text content to a file");
// Writing a string directly to the file
sw.WriteLine(DateTime.Now); // Writing content read from the textbox in the form
//System.Net.IPHostEntry objIPHE = System.Net.Dns.GetHostEntry("www.google.com");
System.Threading.Thread.Sleep(2000); // wait 15 seconds
catch (Exception ex)

EventLog.WriteEntry("PingTest Service", ex.Message, EventLogEntryType.Warning);




Do a Build Solution (Ctrl+Shift+B) to make sure that all your codes don’t have any bugs.

Creating an Installer for you Windows Service

From you solution select File>Add>New Project… from menu

Then from Add New Project, select Other Project Types, and choose Setup Project

Then Visual Studio 2008 will prepare a setup shell for you configure the set require of Solution, and now let add our Windows Service to our installer. From the Application Folder right-click, select Add>Project Output.

From the Add Project Output Group windows select the Primary output and make sure that your Windows Service Project is select has the project, then select OK.

From the Setup properties configure your properties of your installer;

Switch to code mode to start configure the behavior of your windows service application, doing this by selecting ProjectInstaller.Designer.cs.

Modify the service installer as you require, as for me I usually modify account the service will run as, the service startup type, and the service name that will appear in Windows Service MMC.

void InitializeComponent()


this.serviceProcessInstaller1 = new System.ServiceProcess.ServiceProcessInstaller();

this.serviceInstaller1 = new System.ServiceProcess.ServiceInstaller();


// serviceProcessInstaller1


this.serviceProcessInstaller1.Account = System.ServiceProcess.ServiceAccount.LocalSystem;

//this.serviceProcessInstaller1.Password = null;

//this.serviceProcessInstaller1.Username = null;

this.serviceProcessInstaller1.AfterInstall += new System.Configuration.Install.InstallEventHandler(this.serviceProcessInstaller1_AfterInstall);


// serviceInstaller1


this.serviceInstaller1.ServiceName = “PingTestService”;

this.serviceInstaller1.StartType = System.ServiceProcess.ServiceStartMode.Automatic;

// ProjectInstaller


this.Installers.AddRange(new System.Configuration.Install.Installer[] {




Do a Build Solution to test that does your solution contains any bugs, let’s hope the best. Then Build the Installer from the Setup project by right-click on the Setup project, and choose build the project. Therefore you can see that there is Install/Uninstall option if you complete building the installer, and that you right-click again.

Now go to command prompt:-

C:\Windows\Microsoft.NET\Framework\v2.0.50727>installutil C:\WinService\WindowsService1\bin\Debug\WindowsService1.exe

Download Code from this location
Read More
, , , ,

How to Debug JavaScript Using C#

Client Java Script is one of the most important things in web development but not the best and easiest to develop. Building of bigger and more complicated scripts, especially using DOM model or form field values can cause a lot of frustration and head pain. Moreover, JavaScript debugging is not easy and obvious as should be. But there is a hope. Before start, we should ensure that client script debugging is not disabled in IE as it is by default.

Suitable options are located on advanced tab of Internet Options where both script debugging checkboxes should be unchecked.

Go to Tools>>Options >> and refer image below.

You can also download code from http://www.4shared.com/file/106977680/64e2c2b2/DebugJavascript.html

Now open your code and put Debugger; key word in your code. Refer image below

Now run your code, on IE. You will find and window. Refer image below.

Click yes to start debugging. It will take some time to open debugger because you are debugging first time. Refer Image below.

Now, you can easily debug your Java Script code with VS debugger.

Hope this code will help you to come out from hectic task to Java Script Debugging.

You can also use Fire Bug to debug your code on Mozilla Firefox.

You can also refer following links for more help:

Read More