C# Stored Procedure with Parameter

By: Rajesh P.S.

The .NET Data Providers within ADO.NET encompass a collection of classes designed for connecting to data sources, executing commands, and retrieving recordsets. These classes facilitate efficient and flexible data access within .NET applications.

The Command Object in ADO.NET plays a vital role in executing SQL queries against a data source. It provides a range of Execute methods that can be employed to perform SQL queries in various ways. These methods enable developers to execute commands and retrieve results from the data source.

Stored procedure

A stored procedure, on the other hand, is a pre-compiled executable object that contains one or more SQL statements. It is typically stored in a database and can be invoked by name. Stored procedures offer several advantages, including improved performance, encapsulation of complex logic, and security features.

A sample stored procedure with accepting input parameter is given below :

CREATE PROCEDURE SPCOUNTRY @COUNTRY VARCHAR(20) AS SELECT PUB_NAME FROM publishers WHERE COUNTRY = @COUNTRY GO

The above stored procedure is accepting a country name (@COUNTRY VARCHAR(20)) as parameter and return all the publishers from the input country. Once the CommandType is set to StoredProcedure, you can use the Parameters collection to define parameters.

command.CommandType = CommandType.StoredProcedure; param = new SqlParameter("@COUNTRY", "Germany"); param.Direction = ParameterDirection.Input; param.DbType = DbType.String; command.Parameters.Add(param);

Stored procedures can accept input parameters, allowing for dynamic and customizable behavior. Developers can supply parameter values when executing a stored procedure, enabling the procedure to process the provided inputs accordingly. Additionally, stored procedures can return multiple values, making them versatile and capable of delivering various sets of data or results.

The above code passing country parameter to the stored procedure from C# application.

Full Source C#

using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; SqlConnection connection ; SqlDataAdapter adapter ; SqlCommand command = new SqlCommand(); SqlParameter param ; DataSet ds = new DataSet(); int i = 0; connetionString = "Data Source=servername;Initial Catalog=PUBS;User ID=sa;Password=yourpassword"; connection = new SqlConnection(connetionString); connection.Open(); command.Connection = connection; command.CommandType = CommandType.StoredProcedure; command.CommandText = "SPCOUNTRY"; param = new SqlParameter("@COUNTRY", "Germany"); param.Direction = ParameterDirection.Input; param.DbType = DbType.String; command.Parameters.Add(param); adapter = new SqlDataAdapter(command); adapter.Fill(ds); for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++) { MessageBox.Show (ds.Tables[0].Rows[i][0].ToString ()); } connection.Close(); } } }

Conclusion

Utilizing stored procedures, developers can enhance the efficiency and security of their database operations. These pre-compiled objects provide a convenient and reusable means of executing SQL statements, accepting inputs, and returning outputs. They offer flexibility, performance benefits, and an additional layer of abstraction to the application's interaction with the underlying data source.

Next > Stored Procedures in C#

Related Links

Stored Procedures in C#