String Functions in SQL Server.

Tags : String Functions,Sql Server 2005,Sql Server 2008.

Hi Friends,in this article i would like to explain String Functions in SQL Server.

* LEN (string) - Returns the length of the given string.

For Example :

select len(Address) as Address from Employee


* LEFT (string, length) - Returns the specified number of characters from the beginning of the string.

For Example :

Select LEFT(Address,1) as Address from Employee


* RIGTH (string, length) - Returns the specified number of characters from the end of the string.

For Example :

Select RIGHT(Address,2) as Address from Employee


* REVERSE (string) - Returns the given string in reverse order.

For Example :

Select Reverse(Address) as Address from Employee


* UPPER (string) - Converts the given string into uppercase letters.

For Example :

Select UPPER(Address) as Address from Employee


* LOWER(string) - Converts the given string into lowercase letters.

For Example :

Select LOWER(Address) as Address from Employee


* SPACE (integer) - Returns the string with the specified number of space characters.

For Example :

Select SPACE(1) as Address from Employee


* LTRIM (string) - LTRIM function to return a character expression after removing leading spaces.

For Example :

Select LTRIM(Address) as Address from Employee


* RTRIM (string) - RTRIM function to return a character expression after removing trailing spaces.

For Example :

Select RTRIM(Address) as Address from Employee


* SUBSTRING (string, start, length) - Returns the specified number of characters from the string starting at the specified position.

For Example :

Select SUBSTRING (Address, 1, 4) as Address from Employee


Thank you...

Readmore...

How to start and stop triggers in sql server?

Tags : Enable Trigger,Disable Trigger,SQL Server 2005,SQL Server 2008,Triggers,Start,Stop.

Hi Friends,in this post i would like to explain enabling and disabling triggers in SQL Server.

* By writing Commands we can Start/Stop triggers in SQL Server.


Enabling/Starting Trigger :

Enable Trigger TriggerName ON database.dbo.tablename


Disabling/Stopping Trigger :

Disable Trigger TriggerName ON database.dbo.tablename


Thank You...

Readmore...

SQL Server Triggers.

Tags : SQL Server 2005,SQL Server 2008,Triggers,DML Triggers,DDL Triggers,Logon Triggers.

Hi Friends,in this post i would like to explain Triggers in SQL Server.

* It is one kind of stored procedure, but triggers neither accept nor return any values.

* Trigger is an action which can be execute automatically.

* It is defined to execute automatically when the specified event is occurred.

Types of Triggers :

1) DML Triggers :

These triggers executes automatically when data manipulation language event occurs. DML events are insert,update,delete.

2) DDL Triggers :

These triggers executes when data definition language events such as create,alter,drop occurs.

3) Logon Triggers :

These triggers fires for logon events.


Difference between Trigger & Stored Procedure :

1) Trigger is implicit execution where as Stored Procedure is explicit execution.

2) Triggers doesn't allow any parameters & doesn't return any values.Stored Procedure can allow parameters & return values.


Advantages :

* We can schedule tasks basing on events.

Disadvantage :

* It is burden because triggers run on database.
* It is difficult to track the changes done by triggers on database.


Thank you...

Readmore...

How to insert data into XML file?

Tags : Insert,Data,XML File,Asp.Net,C#.Net

Hi Friends,in this post i would like to explain how to insert data into XML file.

* Here in my example i am stored Name,Location,Email & Gender fields into XML file.For this i took 3 TextBoxes,DropDownList for Gender,Button & a label for displaying message.

* Added XML file with name(InsertData)
For this Go To SolutionExplorer-->Right Click on ApplicationName-->AddNewItem-->Select XML File-->Give Name as InsertData-->Click on Add button.


Design View :









Source Code :

Default.aspx.cs :

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void ButtonInsertXML_Click(object sender, EventArgs e)
{
   // Open the XML doc
   System.Xml.XmlDocument myXmlDocument = new System.Xml.XmlDocument();
   myXmlDocument.Load(Server.MapPath("InsertData.xml"));
   System.Xml.XmlNode myXmlNode = myXmlDocument.DocumentElement.FirstChild;

   // Create new XML element and populate its attributes
   System.Xml.XmlElement myXmlElement = myXmlDocument.CreateElement("entry");
   myXmlElement.SetAttribute("Name", Server.HtmlEncode(TextBoxName.Text));
   myXmlElement.SetAttribute("Location", Server.HtmlEncode(TextBoxLocation.Text));
   myXmlElement.SetAttribute("Email", Server.HtmlEncode(TextBoxEmail.Text));
   myXmlElement.SetAttribute("Gender", Server.HtmlEncode(DropDownListGender.SelectedItem.Text));


   // Insert data into the XML doc and save
   myXmlDocument.DocumentElement.InsertBefore(myXmlElement, myXmlNode);
   myXmlDocument.Save(Server.MapPath("InsertData.xml"));

   // Re-bind data since the doc has been added to
   BindData();

   LabelMessage.Text = "Record inserted Successfully Inside the XML File...";

   TextBoxName.Text = "";
   TextBoxLocation.Text = "";
   TextBoxEmail.Text = "";

}

void BindData()
{
   XmlTextReader myXmlReader = new XmlTextReader(Server.MapPath("InsertData.xml"));
   myXmlReader.Close();

}
}

* Then finally Build & Run the application & insert sample input data.It will automatically insert data into InsertData.xml file.


Thank You...

Readmore...

Example on Windows Service

Hi Friends,in this post i would like to explain windows service for displaying message box for every 3 seconds.

Step 1:

* Open windows service project with projectName WinService1
* Goto ToolBox-->General-->Right Click-->Select choose items-->Select Timer(Systems.Timers)
* Project-->AddReference-->System.Windows.Forms(Message Box is a part of above reference).
* Place a Timer(System.Timers) with interval-3000 & enabled-false.

Code:

{//timer1_Elapsed event.
System.Windows.Forms.MessageBox.Show("This message from Windows Service.");
}

Code for OnStart() event
{
timer1.Enabled=true;
}

Code for OnStop() event
{
timer1.Enabled=false;
}

Step 2:

* Open service1.cs[design]
Right Click
Add installer(Select).
Then 2 controls will be added to the form.
1)ServiceProcessInstaller-->Choose properties-->Account=LocalSystem.
2)ServiceInstaller-->Properties-->ServiceName=MessageBoxDisplay

* Build the project(Build-->Select Build solution)

Note:
* WinService1.exe is created under:
D:\WinService1\bin\debug folder with a service name called as "MessageBoxDisplay"

Step 3:

* open .Net Command prompt.
Start-->Programs-->MSVisualStudio2005-->VSTools-->.Net Command Prompt.
>installutil -i D:\WinService1\bin\debug\WinService1.exe (press enter)
>....
>....
>....
>transaction install has completed.

Step 4:

* Open service(Start-->run-->services.msc)
* MessageBoxDisplay-->RightClick-->Properties-->Logon-->Check "interact with desktop" checkBox-->OK
(Only service contains MessageBox otherwise above step is not required).
* MessageBoxDisplay-->rightClick-->Start.

Then service will be started & MessageBox with message(This message from Windows Service.) will be displayed for every 3 seconds.


Thank You...

Readmore...

Introduction to Windows Services.

Hi friends,in this post i would like to explain some basic things to know about windows services.

* A service which is under control of windows OS is called windows service.
* Windows services are used to develop automated background processes.
* Windows services contains only application logic but not GUI(Graphical User Interface).
* The controls which are not visible at runtime,can be placed in windows service projects.

For example:
Timer Control,Event log control,File system watcher control.

* To develop windows services .Net provided:
File-->NewProject-->VisualC#-->Windows-->WindowsServiceTemplate.
* Windows service project is avilable in VisualStudio .Net 2005 professional edition,but not available in Standard edition.
* To develop windows service .Net provided a name space:
System.ServiceProcess(NameSpace).
* All the windows services will be stored at:
Start-->Settings-->ControlPanel-->AdministrativeTools-->Services
OR
Start-->run-->Services.msc
* While a service is starting OnStart() event will be executed & while stoping OnStop() event will be executed.

Steps for developing Windows Service:

1)Open windows service project & wrie the required logic.
2)Add the installers & build the project to get an exe file as output.
3)Open .Net command prompt & install exefile with following syntax:
installutil -i exefile path //for installing the service.
installutil -u exefile path //for uninstalling the service.
4)Start the service.

Thank you...

Readmore...

Saving images to our application.

Hi Friends,in this post i would like to explain how to save images from our PC to application.

* For this i took one FileUpLoad control, Button control and a Label control for displaying message.

Add the following name space:
using System.IO;

Code for button click event():

protected void Button1_Click(object sender, EventArgs e)
{
try
{
string filePath = FileUpload1.PostedFile.FileName;

string filename = Path.GetFileName(filePath);

FileUpload1.PostedFile.SaveAs(@"F:\\Asp.Net\FileUpload\Images\" + filename);

Label1.Text = "Image uploaded successfully.";
}
catch
{
Label1.Text = "Failed to upload image.";
}
}

Note:
F:\\Asp.Net\FileUpload is the application path.
Created Images folder in my application and saving uploaded images into Images folder.

Thank you...

Readmore...

Unique Key on 2 columns in SQL Server.

Hi friends,in this post i would like to explain how to create Unique Key on 2 columns in SQL Server.

For Example:

Here i am created Unique Key for 2 columns(Name,SurName) on College table.

CREATE TABLE [dbo].[College] ADD CONSTRAINT [UK_College] UNIQUE NONCLUSTERED
(
[Name] ,
[SurName]
)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Thank you...

Readmore...

Set Operators in SQL Server.

Hi Friends,in this post i would like to explain Set Operators in SQL Server.

*Set Operators are used to combined the data from multiple tables.

Set Operators:
1)UNION
2)UNION ALL
3)INTERSECT
4)EXCEPT

Syntax:
Select query1

Select query2

Select query3

......
......
......

1)UNION
*UNION can select only distinct/unique rows & rows can be arranged in ascending order.

2)UNION ALL
*UNION ALL is used to combine all rows from the given select queries.

3)INTERSECT
* UNION ALL is used to select only common rows from the given set of select queries.

4)EXCEPT
* EXCEPT is equalent to Minus operator in Oracle.To select all rows from the first query,except second query output.

Limitations:
* All queries should have equal number of columns.
* Corresponding column data types should be same.

Thank you...

Readmore...