Stored Procedures in C#

ADO.NET is comprised of a comprehensive set of classes that collaborate to deliver the required functionality for database interactions. Among these classes, stored procedures play a crucial role. A stored procedure is a collection of Transact-SQL statements that are compiled into a single execution plan, allowing for efficient and optimized execution. A sample Stored Procedure is given below :

CREATE PROCEDURE SPPUBLISHER AS SELECT PUB_NAME FROM publishers GO

The above code create a procedure named as 'SPPUBLISHER' and it execute SQL statement that select all publisher name from publishers table from the PUB database. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.

Command object

The Command object in ADO.NET provides various Execute methods that can be utilized to execute SQL queries in different ways. When calling a stored procedure from C#, it is important to set the CommandType property of the Command object to StoredProcedure. This instructs the Command object that the provided command text represents the name of a stored procedure rather than a direct SQL query.

command.CommandType = CommandType.StoredProcedure;

Setting the CommandType to StoredProcedure, you can effectively invoke the stored procedure and execute its associated Transact-SQL statements. This allows you to use the power and benefits of stored procedures in your database operations, such as encapsulation, modularity, and performance optimization.

From the following source code you can see how to call a stored procedure from a 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(); 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 = "SPPUBLISHER"; 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

The basic steps to call a stored procedure involve creating a Command object, setting the CommandType to StoredProcedure, providing the stored procedure name as the command text, and executing the command using one of the Execute methods available on the Command object.






Related Topics
  1. C# ADO.NET Connection
  2. C# SQL Server Connection
  3. C# OLEDB Connection
  4. C# ODBC Connection
  1. C# ADO.NET Command
  2. C# ADO.NET SqlCommand - ExecuteNonQuery
  3. C# ADO.NET OleDbCommand - ExecuteNonQuery
  4. C# ADO.NET SqlCommand - ExecuteScalar
  5. C# ADO.NET OleDbCommand - ExecuteScalar
  6. C# ADO.NET SqlCommand - ExecuteReader
  7. C# ADO.NET OleDbCommand - ExecuteReader
  8. C# ADO.NET DataReader
  9. C# ADO.NET SqlDataReader
  10. C# ADO.NET OleDbDataReader
  11. C# Multiple Result Sets
  12. C# Table Schema from SqlDataReader
  13. C# Table Schema from OleDbDataReader
  14. C# ADO.NET DataAdapter
  15. C# ADO.NET SqlDataAdapter
  16. C# ADO.NET OleDbDataAdapter
  17. C# ExecuteReader and ExecuteNonQuery
  18. System.Data.SqlClient.SqlException: Login failed for user

Related Links
  1. C# Stored Procedure with Parameter