C# Stored Procedure with Parameter

The .NET Data Providers consist of a number of classes used to connect to a data source, execute commands, and return recordsets. The Command Object in ADO.NET provides a number of Execute methods that can be used to perform the SQL queries in a variety of fashions.

A stored procedure is a pre-compiled executable object that contains one or more SQL statements. In many cases stored procedures accept input parameters and return multiple values . Parameter values can be supplied if a stored procedure is written to accept them. A sample stored procedure with accepting input parameter is given below :

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.

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

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
	public partial class Form1 : Form
		public Form1()

		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);

			command.Connection = connection;
			command.CommandType = CommandType.StoredProcedure;
			command.CommandText = "SPCOUNTRY";

			param = new SqlParameter("@COUNTRY", "Germany");
			param.Direction = ParameterDirection.Input;
			param.DbType = DbType.String;

			adapter = new SqlDataAdapter(command);

			for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
				MessageBox.Show (ds.Tables[0].Rows[i][0].ToString ());

} (C) 2019    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.
SiteMap  | Terms  | About