Understanding Stored procedure

Understanding Stored procedure

Writing Stored Procedures doesn't have to be hard Normally, you would call a database with a query like:
Select column1, column2 From Table1

To make this into a stored procedure, you simple execute this code:

CREATE PROCEDURE sp_myStoredProcedure
AS
Select column1, column2 From Table1
Go

To execute the stored procedure. You can simply call it by name like this

Exec sp_myStoredProcedure


Let me go for little complex things now

Select column1, column2 From Table1 Where column1 = ‘1’ and column2 = ‘secondthing’
The stored procedure we can write is

CREATE PROCEDURE sp_myStoredProcedure @myInput int , @mysecondInput Varchar(1000)ASSelect column1, column2 From Table1Where column1 = @myInput and column2 = @ mysecondInput
Go

Exec sp_myStoredProcedure ‘1’ , ‘secondthing’

Stored procedure To check whether record exist or not If doesnot exist go for Insertion

CREATE procedure Sp_InsertLogin
@username varchar(100) ,
@password varchar(100)

As
declare @RollbackRequired int
set @RollbackRequired = 0

BEGIN TRANSACTION INSERTLOGIN
declare @count int
select @count = Count(username) from users where username = @username and password = @password
--If record doesnot exist go for inserting it.
if (@count = 0)
Insert into users (username , password , Active ,IsAdmin)values (@username , @password , '0','0')
--Check for error, If error occurred go for rollback
if @@Error <> 0
begin
set @RollbackRequired = 1
goto End_Transaction
end
End_Transaction:
IF @RollbackRequired = 1
BEGIN
ROLLBACK TRAN INSERTLOGIN
RETURN -1
END
ELSE
BEGIN
COMMIT TRAN INSERTLOGIN
RETURN 0
END

To execure this call

Exec InsertLogin ‘raj’ , ‘password’



About CURSORS

CURSORS are very similar to an ADO recordset

I chose the FAST_FORWARD option to optimize performance because it creates a forward only, read only cursor
Be aware that CURSORS are somewhat resource intensive. So use it when necessary but always look for a better way.
Also remember to CLOSE and DEALLOCATE your CURSORS as soon as possible to free up resources. Here's the code sample:

To display Course detail and course id records from coursedetail tabel

Create procedure Sp_CourseDetail
As
declare @cname varchar(50) , @cid varchar(50)
declare @mycursor cursor
set @mycursor = CURSOR FAST_FORWARD

for
select coursename , courseId from coursedetail

Open @mycursor
fetch next from @mycursor into
@cname , @cid

while @@fetch_status = 0
begin
print @cname
print @cid
fetch next from @mycursor into
@cname , @cid
end

close @mycursor
deallocate @mycursor

Go

exec Sp_CourseDetail


C-Sharp Code sample to execute Storred procedure


conn.ConnectionString = GetConnection() ;
conn.Open() ;
strSql = "Exec InsertLogin ‘raj’ , ‘password’ " ;
SqlCommand cmd = new SqlCommand(strSql ,conn) ;
cmd.ExecuteNonQuery() ;

My notes on c#

Methods of Navigation and Data Transfer
1. Sessions (data transfer only)
2. Response.Redirect
3. Server.Transfer
4. Server.Execute

1. Sessions : The traditional method which could contain almost any object and could be retrieved on any page during the session of a user. Limitation : If there is a large amount of data involved then using Sessions will considerably slow down your system, or heavyweight objects, such as DataSets.

2. Response.Redirect : This is primarily a navigation technique rather than a data transfer one. But you could append your data with the Page name in order to transfer your data. Like, Response.Redirect(“TestPage.aspx?TextBox1=” & textBox1.text). Frankly speaking this isn’t such a good technique. What if you put 20 text fields and some more dropdownlists etc. on your forms ? In such a situation, I surely wouldn’t use Response.Redirect to transfer data. e.g., Response.Redirect(“NextPage.aspx?Text1=” & textbox1.text & ”&Text2=” & textbox2.text)

3. Server.Transfer : This is one of the two technique offered by ASP.NET to transfer data. Server.Transfer transfers the user to a new page but retains values of ViewState and QueryString of the previous page. While using Server.Transfer you must keep few things in mind. Firstly, Server.Transfer can only be used between WebForms. Second, when using it make sure you set the EnableViewStateMac of the source webpage to False to disable hashing. e.g., Server.Transfer(“TargetPage.aspx”, True) The first parameter is obviously a Target Page where you want to transfer. The second parameter is PreserveForm which could be set to True or False. True will let you preserve the information for the next page.

4 Server.Execute : This method merges two form’s data into one form. As opposed to Server.Transfer which finishes dealing with one form before moving on to the next, Server.Execute processes a second form without leaving the the first one.
Server.Execute("Transfer.aspx", swrTarget) Label2.Text = swrTarget.ToString You only need to write Server.Execute(“TargetPage.aspx”) to get it working. Using this command, the next page’s content will be displayed right on top of the current page. But if you supply the second parameter - which is an object of the StringWriter class - then the output will go to this object and you can assign this output to any object. In my example I’m assigning to a label (label2). You are able to show your target page anywhere you want.

Note :

Response.Redirect(http://www.google.com) will work
but Server.Transfer(http://www.google.com) will not work

----------------------------------- End of this topic ----------------------------------------------

Operators

Unary Operators
unary operators affect a single expression. In many instances, the unary operators enable operations with simpler syntax than a comparable binary operation. The unary operators include + (plus), - (minus), ++ (increment), -- (decrement), ! (logical negation), and ~ (bitwise complement).

Binary Operators
Binary operators are those operators that work with two operands. For example, a common binary expression would be a + b —the addition operator (+) surrounded by two operands. The binary operators are further subdivided into arithmetic, relational, logical, and assignment operators.


Operator Overloading In C#
All unary and binary operators have pre-defined implementations, that are automatically available in any expressions. In addition to this pre-defined implementations, user defined implementations can also be introduced in C#. The mechanism of giving a special meaning to a standard C# operator with respect to a user defined data type such as classes or structures is known as operator overloading. Remember that it is not possible to overload all operators in C#.
The following table shows the operators and their overloadability in C#.

Operators

+, -, *, /, %, &, , <<, >> All C# binary operators can be overloaded.
+, -, !, ~, ++, --, true, false All C# unary operators can be overloaded.
==, !=, <, >, <= , >= All relational operators can be overloaded, but only as pairs.
&&, They can't be overloaded
() (Conversion operator) They can't be overloaded
+=, -=, *=, /=, %= These compound assignment operators can be overloaded.
But in C#, these operators are automatically overloaded when the respective binary operator is overloaded.

=, . , ?:, ->, new, is, as, sizeof These operators can't be overloaded

Note :
· A special function called operator function is used for overloading purpose.
· These special function or method must be public and static.
· They can take only value arguments.
· The ref and out parameters are not allowed as arguments to operator functions.
public static return_type operator op (argument list)