Using SqlParameter Direction in C#.Net with with Stored Procedure

jelo pasiolan

Rate me:

3.07/5 (10 votes)

12 Oct 2014CPOL

94.7K

Basic way on using SqlParameter Directions (Input, InputOutput,Output,ReturnValue)

Introduction

When using the Microsoft Data Access in your Application and accessing your Microsoft SQL Server 2008 database and the CommandType is a stored procedure, there may be a need to pass input parameters to the stored procedure and get an output or a return value coming from the stored procedure. This article will show you the basic way on how to use the SqlParameter Direction (Input, InputOutput, Output and RetrunValue)

Direction	Description
Input (Default)	The parameter is an input parameter.
Output	The parameter is an output parameter.
InputOutput	The parameter is capable of both input and output.
ReturnValue	The parameter represents a return value from an operation such as a stored procedure

Code

C++

private int Execute(ref object outputParam,ref object inoutParam)
    {
            string oUsername = "Jelo";
            int InOutParamValue = 3;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
            conn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = "MyStoredProcedure";
                cmd.CommandType = CommandType.StoredProcedure;

                // Set Input Parameter
                SqlParameter oParam = new SqlParameter("@Username", oUsername);
                oParam.SqlDbType = SqlDbType.VarChar; 
                cmd.Parameters.Add(oParam);
                
                // Set Output Paramater
                SqlParameter OutputParam = new SqlParameter("@OutputParam", SqlDbType.VarChar);
                OutputParam.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(OutputParam);

                // Set Input Output Parameter
                SqlParameter InOutParam = new SqlParameter("@InputOutputParam", InOutParamValue);
                InOutParam.SqlDbType = SqlDbType.Int;
                InOutParam.Direction = ParameterDirection.InputOutput;
                cmd.Parameters.Add(InOutParam);

                // Set ReturnValue Parameter
                SqlParameter RetParam = new SqlParameter("ReturnValue", DBNull.Value);
                RetParam.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(RetParam);

                cmd.ExecuteNonQuery();
                 
                // Start getting the RetrunValue and Output from Stored Procedure
                int IntReturn = Convert.ToInt32(cmd.Parameters["ReturnValue"].Value);
                if (IntReturn == 1)
                {
                    outputParam = cmd.Parameters["@OutputParam"].Value;
                    inoutParam = cmd.Parameters["@InputOutputParam"].Value;
                }
                return IntReturn;
           }
           conn.Close();
    }

Stored Procedure

C++

ALTER PROCEDURE MyStoredProcedure
    @Username VARCHAR(100),
    @OutputParam VARCHAR(100) OUTPUT,
    @InputOutputParam INT OUTPUT
AS
BEGIN
    IF EXISTS(SELECT * FROM dbo.Table1 WHERE MemberCode = @Username AND id < @InputOutputParam)
    BEGIN                
        SELECT @OutputParam = MAX(ReferenceNo),@InputOutputParam = MAX(id) FROM dbo.Table1
        WHERE MemberCode = @Username    
        RETURN 1
    END
    ELSE
    BEGIN
        RETURN -1
    END        
END

Hope it helps someone!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

jelo pasiolan

Software Developer

Philippines

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

my code with database is here

database code:

create table addpinrequest(Appmstregno varchar(100),
                           Qty varchar(100),
                           Amount int,
                           paymode varchar(100),
                           trans varchar(200), 
                           bankname varchar(200), 
                           branchname varchar(200), 
                           accountno varchar(200), 
                           ifsc varchar(100), 
                           filesname varchar(200), 
                           msg varchar(200), 
                           pinid int identity(1,1) primary key); 

go 
alter procedure spaddpinrequest(@Appmstregno varchar(100),
                                @Qty varchar(100),
                                @Amount int,
                                @paymode varchar(100),
                                @transaction varchar(200),
                                @Bankname varchar(200),
                                @Branchname varchar(200),
                                @Accountno varchar(200),
                                @Ifsc varchar(100),
                                @FileName varchar(200),
                                @msg varchar(200),
                                @id int output)
as
begin
--set @id=scope_identity();
insert into addpinrequest(Appmstregno,Qty,Amount,paymode,trans, bankname,branchname,accountno,ifsc,filesname,msg,pinid) values(@Appmstregno,@Qty,@Amount,@paymode,@transaction,@Bankname,@Branchname,@Accountno,@Ifsc,@FileName,@msg,@id);
select @id=pinid from addpinrequest where @appmstregno=appmstregno;
end

C# code in asp.net:

protected void Button1_Click(object sender, EventArgs e)
  {
      if (datacheck())
      {
          int totalpackamount = Convert.ToInt32(((Label)gvPins.FooterRow.FindControl("lblTotalAmount")).Text);

          if (totalpackamount == Int32.Parse(txtAmount.Text))
          {
              string fileName = string.Empty;

              if (uploadFile.HasFile)
              {
                  fileName = "PAN/" + uploadFile.FileName;
                  uploadFile.SaveAs(Server.MapPath(fileName));
              }

              Int32 count = 0;
              DbCommand cmd = DataAccess.CreateCommand();
              cmd.CommandText = SP.AddPinRequest;

              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "@Appmstregno", DbType.String, _master.UserMemberId));
              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "@Qty", DbType.String, ((Label)gvPins.FooterRow.FindControl("Label1")).Text));
              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "@Amount", DbType.Int32, txtAmount.Text));
              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "@paymode", DbType.String, ddlPaymentMode.SelectedItem.Text));
              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "@transaction", DbType.String, txtTransaction.Text));
              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "@Bankname", DbType.String, txtBankName.Text));
              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "@Accountno", DbType.String, txtAccountNo.Text));
              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "@Ifsc", DbType.String, txtIFSC.Text));
              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "@FileName", DbType.String, fileName));
              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "@msg", DbType.String, txtComment.Text));
              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "@Id", DbType.Int32, DBNull.Value, ParameterDirection.Output));

              int i = DataAccess.ExecuteNonQuery(cmd);
              //string id = (cmd.Parameters["@Id"].Value.ToString());
              int id = Convert.ToInt32(cmd.Parameters["@Id"].Value);

              if (i > 0)
              {
                  foreach (GridViewRow gr in gvPins.Rows)
                  {
                      if (gr.RowType == DataControlRowType.DataRow)
                      {
                          if (((CheckBox)gr.FindControl("chkSelectPin")).Checked)
                          {
                              cmd = DataAccess.CreateCommandText();
                              cmd.CommandText = "insert into PinDetail (RequestId, Qty, PlanName, TotalAmount, PlanId, Appmstregno) values (@RequestId, @Qty, @PinValue, @PinValue, @PlanId, @Appmstregno)";
                              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "PinValue", DbType.String, gr.Cells[2].Text));
                              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "PlanId", DbType.String, gr.Cells[1].Text));
                              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "Qty", DbType.String, ((TextBox)gr.FindControl("txtNoOfPins")).Text));
                              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "Appmstregno", DbType.String, _master.UserMemberId));
                              cmd.Parameters.Add(DataAccess.CreateParameter(cmd, "RequestId", DbType.String, id));

                              count += DataAccess.ExecuteNonQuery(cmd);
                              cmd.Parameters.Clear();
                          }
                      }
                  }
              }

              if (count > 0)
              {
                  reset();
                  Message.Show("Thanks, Request Send successfully. We will contact you soon..");
              }
              else
              {
                  Message.Show("Sorry!! There is something wrong please contact Administrator.");
              }
          }
          else
          {
              Message.Show("Sorry!!! Amount is less Then or Not equal to Total pin cost");
          }
      }
  }

sp page code(for call stored procedure):

public static string AddPinRequest = "spaddpinrequest";

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.