how to use SQL Server Stored Procedure in C#, VB6

Using Stored Procedure to insert data is sometimes proved worthy in case of robust applications. More appropriately, it is at least better than creating a long SQL insert query string for database interaction. However, using Stored Procedure in C# is very simple and effective as explained below.


CREATE PROCEDURE udp_InsertGuest
@guest_Name varchar50),
@guest_Address varchar(50),
@guest_PhoneNumber varchar(50),
@guest_VisitPurpose varchar(50),
@guest_Id int output
as
BEGIN
  insert into
t_Guest
 (guest_Name,guest_Address,guest_PhoneNumber,
  guest_VisitPurpose,guest_EntryDateAndTime)
  values
 (@guest_Name,@guest_Addresss,@guest_PhoneNumber,
  @guest_VisitPurpose, getdate ())
  set @guest_Id = SCOPE_IDENTITY ()
END


First of all, I have written Stored Procedure udp_InsertGuest to insert data in t_Guest table that returns identity of last inserted row as well.


sqlConnection = DbConnector.GetConnection;
sqlConnection.Open();
sqlCommand = new SqlCommand(“udp_InsertGuest”,sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;


Here, I have created sqlCommand object of SqlCommand class using Stored Procedure and Database Connection as parameters as well as assigned Command Type to it.


sqlCommand.Parameters.Add(“@guest_Name”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_Name”].Value = guestObj.Name;

sqlCommand.Parameters.Add(“@guest_Address”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_Address”].Value = guestObj.Address;

sqlCommand.Parameters.Add(“@guest_PhoneNumber”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_PhoneNumber”].Value = guestObj.Phone;

sqlCommand.Parameters.Add(“@guest_VisitPurpose”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_VisitPurpose”].Value = guestObj.VisitCause;

sqlCommand.Parameters.Add(“@guest_Id”, SqlDbType.Int);
sqlCommand.Parameters[“@guest_Id”].Direction = ParameterDirection.Output;


This code snippet shows how to add parameters and assign values in sqlCommand object. Please follow the output parameter requires a direction assignment.


sqlCommand.ExecuteNonQuery();
int guestId = (int)sqlCommand.Parameters[“@guest_Id”].Value;


Finally, after sqlCommand’s execution, it’s quite easy to retrieve the value of output parameter.



Dim guestId As Integer

'Creating Command
Dim command As ADODB.command
Set command = New ADODB.command
command.CommandType = adCmdStoredProc
command.ActiveConnection = ProjectConnection
command.CommandText = "udp_InsertGuest"

'Adding Input Parameters
command.Parameters.Append command.CreateParameter("guest_Name", adVarChar, adParamInput, 50, guestName)
command.Parameters.Append command.CreateParameter("guest_Address", adVarChar, adParamInput, 50, guestAddress)
command.Parameters.Append command.CreateParameter("guest_PhoneNumber", adVarChar, adParamInput, 50, guestPhoneNumber)
command.Parameters.Append command.CreateParameter("guest_VisitPurpose", adVarChar, adParamInput, 50, guestVisitPurpose)

'Adding Output Parameters
command.Parameters.Append command.CreateParameter("guest_id", adInteger, adParamOutput)

'Executing Command
command.Execute

'Retrieving Output Parameters
guestId = command("guest_Id")


This code snippet shows how to use stored procedure with VB6.

Thanks
A Rahim Khan

Advertisements
    • Russel
    • December 26th, 2009

    Rahim Bha U’ve have done a excellent Job.
    If U Have time Call Me

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: