Sql Server Stored Procedures

Stored Procedures and block of code which does one or more actions on a database. 

CREATE PROCEDURE spHello as
 PRINT 'Hello welcome to T-Sql'
GO

Executing a stored Procedure

EXEC spHello

Stored Procedure with varchar parameter and string concatenation

create procedure spHelloName (@Name varchar(20))
as 
 print 'Hello ' + @Name
go

Procedure with integer Parameter and addition
Memory variables must preceded with @ symbol

create procedure spAdd(@p1 int, @p2 int)
as
 print @p1 + @p2
go

If

create procedure spIf (@pCity varchar(40))
as
if @pCity = 'Kochi'
 print 'Industrial capital  of Kerala'
go

While Loop

create or alter procedure spWhile
as
DECLARE @cnt INT = 0;

WHILE @cnt < 10
BEGIN
   PRINT 'Hello '
   SET @cnt = @cnt + 1;
END;
GO

While Loop with Cast

create or alter procedure spWhileCounter
as
DECLARE @cnt INT = 0;

WHILE @cnt < 10
BEGIN
   PRINT 'Hello ' + cast(@cnt as varchar(3))
   SET @cnt = @cnt + 1;
END;
GO 

Stored Procedure for Selecting values from a Table

CREATE PROCEDURE spAllCategories
AS
 select * from Categories
GO

spAllCategories

Stored Procedure with Joins
create procedure spAllProductsCategory
as
SELECT C.CategoryName, P.ProductName, P.UnitPrice FROM Categories C , Products P WHERE C.CategoryId = P.CategoryId
go

Stored Procedure with Where Clause
create procedure spProductsCategorySearch(@pCategoryId int)
as
SELECT C.CategoryName, P.ProductName, P.UnitPrice FROM Categories C , Products P WHERE C.CategoryId = P.CategoryId and C.CategoryId = @pCategoryId
go
spProductsCategorySearch 1
spProductsCategorySearch 2

Stored Procedure for Inserting values into a Table
create procedure spInsertCategories (@pCategoryName varchar(40), @pDescription varchar(60))
as
insert into Categories (CategoryName, Description) values (@pCategoryName, @pDescription)
GO

EXEC spInsertCategories @pCategoryName = 'Condiments', @pDescription = 'Sweets and savory sauces, relishes, spreads, and seasonings'spInsertCategories 'Confections', 'Desserts, candies, and sweet breads'


User Managament Scripts
create database UserManagement

use UserManagement

create table Roles(RoleId int identity(1,1) primary key, Role varchar(40))

create table Users (UserId int identity(1,1) primary key,
RoleId int References Roles(RoleId), UserName varchar(40),
Password varchar(40))

create procedure InsertRole(@pRoleName varchar(40))
as
insert into Roles values (@pRoleName)
go

insertRole 'Admin'

create procedure spAllRoles as
select * from Roles
go

create procedure spInsertUser(@pGroupId int,
@pUserName varchar(40), @pPassword varchar(40))
as
insert into Users values (@pGroupId, @pUserName, @pPassword )
go

spInsertUser 1, 'Greorge', 'george'
spAllRoles

create procedure spAllUsers
as
select * from Users
go

spAllUsers

create procedure spUserCount (@pUserName varchar(40), @pPassword varchar(40), @userCount int output)
as
select @userCount = COUNT(*) from Users where UserName = @pUserName and Password =@pPassword
return
go

spUserCount 'George', 'george', ''

create procedure GetUserRole (@pUserName varchar(40))
as
select R.Role from Roles R, Users U where R.RoleId = U.RoleId and U.UserName = @pUserName
go

GetUserRole 'Shalvin'

GetUserRole 'Manu'

AppDomain : Listing Assemblies within the Current AppDomain and Creating an new AppDomain (Code Snippet)

using System.Reflection;

private void Form1_Load(object sender, EventArgs e)
{
AppDomain dom = AppDomain.CurrentDomain;

Assembly[] loadedAssembly = dom.GetAssemblies();
foreach (Assembly a in loadedAssembly)
listBox1.Items.Add(a.GetName().Name );
}

private void btnCreateAppDomain_Click(object sender, EventArgs e)
{
AppDomain aDo = AppDomain.CreateDomain("Shalvin");

Assembly[] loadedAssembly = aDo.GetAssemblies();
foreach (Assembly a in loadedAssembly)
listBox2.Items.Add(a.GetName().Name);
}










Related Blogs
Reflection in .Net
.Net Intermediate Language

.Net Remoting Walkthrough

Remoting is the process of two pieces of software communicating across application domains.
Start a Class Library call it say ShalvinObject. Create a class that inherits from MarshalByRefObject.

namespace ShalvinObject
{
class RemoteObject : MarshalByRefObject
{
public int Mul(int i, int j)
{
return i * j;
}
}

Now we are going to host the service. This can be done with Console, Windows Forms or Web Service.
Letsuse Windows Forms.
Add to a reference to the previously created dll, ie. ShalvinObject.dll and System.Runtime.Remoting.


using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Tcp;

private void Form1_Load(object sender, EventArgs e)
{
TcpChannel c = new TcpChannel(900);
ChannelServices.RegisterChannel(c);
RemotingConfiguration.RegisterWellKnownServiceType(typeof(ShalvinObject.RemoteObject), "Shalvin", WellKnownObjectMode.Singleton);
}

Now for testing the service lets create another Windows Application. Here too set references to ShalvinObject.dll and System.Runtime.Remoting.


using ShalvinObject;
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Tcp;


private void Form1_Load(object sender, EventArgs e)
{
TcpChannel c = new TcpChannel();
ChannelServices.RegisterChannel(c);
object obj = Activator.GetObject(typeof(ShalvinObject.RemoteObject), "tcp://localhost:900/Shalvin");
RemoteObject ro = (RemoteObject)obj;
int intRes = ro.Mul(56, 45);
MessageBox.Show(intRes.ToString());
}

Related Blog
Currency Conversion Web Service with Asp .Net

Creating and Consuming Class Library in C#, Object Initializer

Hashtable and DictionaryEntry

HashTable Class of System.Collections namespace represents a collection of key/value pairs that are organized based on the hash code of the key.

using System.Collections;

Hashtable ht = new Hashtable();
private void Form1_Load(object sender, EventArgs e)
{
	ht.Add("Am", "Ambily");
	ht.Add("Di", "Divya");
	foreach (DictionaryEntry de in ht)
	{
		listBox1.Items.Add(de.Key);
		listBox2.Items.Add(de.Value);
	}
}

private void button1_Click(object sender, EventArgs e)
{
	ht.Add(textBox1.Text, textBox2.Text);
	ShowHashTable();
}

private void ShowHashTable()
{
	listBox1.Items.Clear();
	listBox2.Items.Clear();
	foreach (DictionaryEntry de in ht)
	{
		listBox1.Items.Add(de.Key);
		listBox2.Items.Add(de.Value);
	}
}

VB .Net

Dim ht As New Hashtable
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
	ht.Add("Am", "Ambily")
	ht.Add("Di", "Divya")
	ShowHT()
End Sub
Private Sub ShowHT()
	ListBox1.Items.Clear()
	ListBox2.Items.Clear()
	For Each de As DictionaryEntry In ht
		ListBox1.Items.Add(de.Key)
		ListBox2.Items.Add(de.Value)
	Next
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
	ht.Add(txtKey.Text, txtValue.Text)
	ShowHT()
End Sub

Related Blog ReadOnlyCollectionBase Class Generics and Collection Initializer in .Net

ReadOnlyCollectionBase Class

Provides the abstract base class for a strongly typed non-generic read-only collection.This class makes the underlying collection available through the InnerList property.
Since ReadOnlyCollectionBase implements IEnumerable Interface you can iterate through the collection.

//class
using System.Collections;
class ShalvinBlogs : ReadOnlyCollectionBase
{
public void Add(String value)
{
InnerList.Add(value);
}
public string this[int index]
{
get
{
return (string)InnerList[index];
}
}
}

//Forms
ShalvinBlogs sb = new ShalvinBlogs();

private void Form1_Load(object sender, EventArgs e)
{
sb.Add("Shalvinpd.blogspot.com");
sb.Add("DotNetShalvin.blogspot.com");
foreach (string str in sb)
listBox1.Items.Add(str);
}
private void button1_Click(object sender, EventArgs e)
{
textBox1.Text = sb[0];
}

Related Blogs
Generics and Collection Initializer in .Net
Hashtable and DictionaryEntry

ServiceController Class for Managing a service

You can use the ServiceController class (System.ServiceProcess) to connect to and control the behavior of existing services.

Enumerating services

using System.ServiceProcess;

private void Form1_Load(object sender, EventArgs e)
{
foreach (ServiceController s in ServiceController.GetServices())
listBox1.Items.Add(s.DisplayName );
}

You can then use the class to start, stop, and otherwise manipulate the service.

Here I am using ServiceController class to start and stop Sql Server 2000 Service.

using System.ServiceProcess;
ServiceController sc = new ServiceController();
private void Form1_Load(object sender, EventArgs e)
{
sc.ServiceName = "MSSqlServer";
}

private void btnStart_Click(object sender, EventArgs e)
{
if (sc.Status == ServiceControllerStatus.Stopped sc.Status == ServiceControllerStatus.Paused )
{
sc.Start();
MessageBox.Show("Service started");
}
else
MessageBox.Show("Service already started");
}

private void btnStop_Click(object sender, EventArgs e)
{
if (sc.Status == ServiceControllerStatus.Running )
{
sc.Stop();
MessageBox.Show("Service stopped");
}
else
MessageBox.Show("Service alredy in stopped state");
}
}

Isolated Storage (Code Snippets)

using System.IO;
using System.IO.IsolatedStorage;

private void button1_Click(object sender, EventArgs e)
{
this.Text = textBox1.Text;
IsolatedStorageFile f = IsolatedStorageFile.GetMachineStoreForDomain();
IsolatedStorageFileStream i = new IsolatedStorageFileStream("co.col", System.IO.FileMode.Create, f);
StreamWriter sw = new StreamWriter(i);
sw.Write(this.Text);
sw.Flush();
sw.Close();
}


private void Form1_Load(object sender, EventArgs e)
{
IsolatedStorageFile f1 = IsolatedStorageFile.GetMachineStoreForDomain();
IsolatedStorageFileStream i1 = new IsolatedStorageFileStream("co.col", FileMode.Open, f1);
StreamReader sr = new StreamReader(i1);
this.Text = sr.ReadToEnd();
sr.Close();
}

Courtesy : Saji P Babu, IndiaOptions, Kochi.

Related Blog
System.IO Namespace in .Net

Linq to Xml (Code Snippets)

Linq to Xml enables you to query an Xml document using the familiar LINQ syntax. Linq to Xml features and found within System.Xml.Linq namespace.
System.Xml.Linq introduces a series of objects such as XDocument, XElement and XAttributes which makes creating XML document a breeze.

XAttribute
private void btnXElement_Click(object sender, RoutedEventArgs e)
{
XElement xe = new XElement(new XElement("Shalvin",
new XElement("Students",
new XElement("Name", "Shalvin"))));
textBox1.Text = xe.ToString();

}


XElement

using System.Xml.Linq;

private void Form1_Load(object sender, EventArgs e)
{
XElement xml = new XElement("contacts",
new XElement("contact",
new XAttribute("contactId", "2"),
new XElement("firstName", "Shalvin"),
new XElement("lastName", "PD")
),
new XElement("contact",
new XAttribute("contactId", "3"),
new XElement("firstName", "Praseed"),
new XElement("lastName", "Pai")
)
);
textBox1.Text = xml.ToString();
}

XDocument

using System.Xml.Linq;

protected void Page_Load(object sender, EventArgs e)
{
XDocument xd = new XDocument();
XElement contacts =
new XElement("contacts",
new XElement("contact",
new XElement("name", "Shalvin"),
new XElement("Location", "Kochi")
),
new XElement("contact",
new XElement("name", "Viju"),
new XElement("Location", "Trichur")
)
);
xd.Add(contacts);
xd.Save("c:\\Shalvin.xml");
System.Diagnostics.Process.Start("iexplore", "c:\\Shalvin.xml");
}

Output

<?xml version="1.0" encoding="utf-8"?>
<contacts>
<contact contactId="2">
<firstName>Shalvin</firstName>
<lastName>P D</lastName>
</contact>
<contact contactId="3">
<firstName>Praseed</firstName>
<lastName>Pai</lastName>
</contact>
</contacts>

XDocument Parse

private void button3_Click(object sender, RoutedEventArgs e)
{
XDocument xd = XDocument.Parse(@"<contacts>
<contact contactId='2'>
<firstName>Shalvin</firstName>
<lastName>P D</lastName>
</contact>
<contact contactId='3'>
<firstName>Praseed</firstName>
<lastName>Pai</lastName>
</contact>
</contacts>");

textBox1.Text = xd.ToString();

}

Querying an Xml Document














using System.Xml.Linq;
private void Form1_Load(object sender, EventArgs e){
XDocument xmlFile = XDocument.Load("c:\\Shalvin.xml");
var query = from c in xmlFile.Elements("contacts").Elements("contact").Elements("name")
select c;
foreach (XElement nam in query)
{
listBox1.Items.Add(nam);
}
}

Related Blogs
Linq to Objects
Linq to Sql
.Net Working with Xml