Net-informations.com
SiteMap  | About    

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 :

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

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()
		{
			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();
		}
	}
}






net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.