Introduction -
Hi everyone, in this post I will explore one of the way of passing multiple parameters to the SQL Stored procedure.
Description -
When there are too many parameters to be passed to the stored procedure from the application, one of the easiest way is to pass it as an array object of SqlParameter class.
(The advantages of the above method is that apart from passing multiple values to the stored procedure we can also catch the return value from stored procedure)
Important properties
1)Direction - Gets or sets a value that indicates whether the parameter is input or output.
The permitted values are of type ParameterDirection enum
ParameterDirection.Input - indicates its an input parameter to the sp
ParameterDirection.Output - indicates its an output parameter from the sp
ParameterDirection.InputOutput - indicates it can behave as an input as well as an output paramter
sqlParameterObject.Direction = ParameterDirection.Input;
sqlParameterObject.Direction =ParameterDirection.Output;
2)Value - This property is used to set or get the value of the parameter i.e. using this property we pass the value and access the value.
sqlParameterObject.Value = "EmpName";
Example:
Note -
The sp to be called in the below example is sp_Insert_Details
The sp has three input parameter and one output parameter
//Creating an array of SqlParameter class objects
SqlParameter[] paramComplaint = new SqlParameter[4];
//Initialising the individual SqlParameter class objects, also by default Direction property is Input hence no need to specify it explicitly for input parameters.
paramComplaint[0] = new SqlParameter();
paramComplaint[0].Name = "@EmpName";
paramComplaint[0].SqlDbType = SqlDbType.VarChar;
paramComplaint[0].Size = 10;
paramComplaint[0].Value = EmployeeName;
paramComplaint[1] = new SqlParameter();
paramComplaint[1].Name = "@EmpAddress";
paramComplaint[1].SqlDbType = SqlDbType.VarChar;
paramComplaint[1].Size = 100;
paramComplaint[1].Value = "SJP Road";
paramComplaint[2] = new SqlParameter();
paramComplaint[2].Name = "@EmpAge";
paramComplaint[2].SqlDbType = SqlDbType.Int;
paramComplaint[2].Size = 10;
paramComplaint[2].Value = 24;
//Here paramComplaint[3] is of output type hence we are not setting any value using the Value property
paramComplaint[3] = new SqlParameter();
paramComplaint[3].Name = "@GeneratedEmpID";
paramComplaint[3].SqlDbType = SqlDbType.Int;
paramComplaint[3].Direction = ParameterDirection.Output;
paramComplaint[3].Size = 10;
//Executing using the command Object
commandObject..ExecuteNonQuery(Trans, CommandType.StoredProcedure, "sp_Insert_Details", paramComplaint);
//The values returned from the stored procedure can be accessed as follows
int generatedID = parameter[3].value;
Further Reading -
http://msdn.microsoft.com/en-us/library/System.Data.SqlClient.SqlParameter%28v=vs.90%29.aspx
Hi everyone, in this post I will explore one of the way of passing multiple parameters to the SQL Stored procedure.
Description -
When there are too many parameters to be passed to the stored procedure from the application, one of the easiest way is to pass it as an array object of SqlParameter class.
(The advantages of the above method is that apart from passing multiple values to the stored procedure we can also catch the return value from stored procedure)
Important properties
1)Direction - Gets or sets a value that indicates whether the parameter is input or output.
The permitted values are of type ParameterDirection enum
ParameterDirection.Input - indicates its an input parameter to the sp
ParameterDirection.Output - indicates its an output parameter from the sp
ParameterDirection.InputOutput - indicates it can behave as an input as well as an output paramter
sqlParameterObject.Direction = ParameterDirection.Input;
sqlParameterObject.Direction =ParameterDirection.Output;
2)Value - This property is used to set or get the value of the parameter i.e. using this property we pass the value and access the value.
sqlParameterObject.Value = "EmpName";
Example:
Note -
The sp to be called in the below example is sp_Insert_Details
The sp has three input parameter and one output parameter
//Creating an array of SqlParameter class objects
SqlParameter[] paramComplaint = new SqlParameter[4];
//Initialising the individual SqlParameter class objects, also by default Direction property is Input hence no need to specify it explicitly for input parameters.
paramComplaint[0] = new SqlParameter();
paramComplaint[0].Name = "@EmpName";
paramComplaint[0].SqlDbType = SqlDbType.VarChar;
paramComplaint[0].Size = 10;
paramComplaint[0].Value = EmployeeName;
paramComplaint[1] = new SqlParameter();
paramComplaint[1].Name = "@EmpAddress";
paramComplaint[1].SqlDbType = SqlDbType.VarChar;
paramComplaint[1].Size = 100;
paramComplaint[1].Value = "SJP Road";
paramComplaint[2] = new SqlParameter();
paramComplaint[2].Name = "@EmpAge";
paramComplaint[2].SqlDbType = SqlDbType.Int;
paramComplaint[2].Size = 10;
paramComplaint[2].Value = 24;
//Here paramComplaint[3] is of output type hence we are not setting any value using the Value property
paramComplaint[3] = new SqlParameter();
paramComplaint[3].Name = "@GeneratedEmpID";
paramComplaint[3].SqlDbType = SqlDbType.Int;
paramComplaint[3].Direction = ParameterDirection.Output;
paramComplaint[3].Size = 10;
//Executing using the command Object
commandObject..ExecuteNonQuery(Trans, CommandType.StoredProcedure, "sp_Insert_Details", paramComplaint);
//The values returned from the stored procedure can be accessed as follows
int generatedID = parameter[3].value;
Further Reading -
http://msdn.microsoft.com/en-us/library/System.Data.SqlClient.SqlParameter%28v=vs.90%29.aspx
