DEVCON Event :-

The biggest event DEVCON is here once again in Pune..Its going to be held on 8-9 August 09..Industry experts speakers going to give sessions on interesting topics like .Net 4.0,Silverlight,Windows Azure,Visual Studio 2010 etc..Do not miss it :-) For FREE registration and other details visit site http://www.puneusergroup.org/events/devcon2009/

ADO.NET output parameter in VB

This is used to give a direction to output parameter. When we write a stored procedure and when we declare output parameter in it in which we have to store output. This output parameter we have to declare in code also. While adding this parameter in code we have to use ParameterDirection.Output.

Example : -

Stored Procedure : -

CREATE PROCEDURE [dbo].[USP_AuthenticateUsers]

@username nvarchar(255),
@password varbinary(MAX),
@userId int output,

AS
BEGIN

SET @userId=0

SELECT @userId=userId , @roleId=roleId FROM Users WHERE username=@username and [password]=@password
END


Code : -

Public Function AuthenticateUser(ByVal username As String, ByVal password As Byte(), ByVal userIPAddr As String) As structUserSession

Dim objCom As New System.Data.SqlClient.SqlCommand
objCom.CommandType = CommandType.StoredProcedure
objCom.CommandText = "USP_AuthenticateUsers"
objCom.Parameters.Add("userName", SqlDbType.NVarChar, 255).Value = username
objCom.Parameters.Add("password", SqlDbType.VarBinary, password.Length).Value = password
objCom.Parameters.Add("userId", SqlDbType.Int).Direction = ParameterDirection.Output

DB.ExecuteNonQuery(objCom)

Dim objStructUserSession As New structUserSession
objStructUserSession.userId = objCom.Parameters.Item("userId").Value

Return objStructUserSession

End Function

If Condition in SQL

If...Else statement is used to test a condition. If we have more than 1 condition we can use If...Else statement. We can also have nested If...Else statements also. We have nested If...Else statements when we have to include 1 or more If...Else statements in 1 If...Else statement. If...Else Statement in SQL is used in execution os Stored Procedures.

Syntax for simple If...Else statement is,

IF ( Condition )
BEGIN
Sql Statements
END
ELSE
BEGIN
Sql Statements
END

Syntax for nested If...Else statements is,

IF ( Condition )
BEGIN
Sql Statements
IF ( Condition )
BEGIN
Sql Statements
END
ELSE
BEGIN
Sql Statements
END
END

ELSE
BEGIN
Sql Statements
END


Example for simple If...Else statement is,

CREATE PROCEDURE [dbo].[USP_InsertUserSession]
@userId int,
@userSession nvarchar(255),
@userDate datetime,

AS
BEGIN
declare @tmpuserId int
SELECT @tmpuserId=userId from UsersSessions WHERE userId=@userId

if @tmpuserId > 0
begin
UPDATE UsersSessions SET userSession=@userSession, userDate=@userDate WHERE userId=@tmpuserId
end

else
begin
INSERT INTO UsersSessions(userId,userSession,userDate) VALUES (@userId,@userSession,@userDate)
end