write stored procedure in sql server in C# code example

Example 1: sql server stored procedure

CREATE PROCEDURE spGetEmployeeCountByGender
@Gender nvarchar(20),
@EmployeeCount int Output
AS
BEGIN
SELECT @EmployeeCount = COUNT(Id) 
FROM tblEmployee 
WHERE Gender = @Gender
END

Example 2: how to use stored procedure in c#

using System;
using System.Data;
using System.Data.SqlClient;

class StoredProcDemo
{
	static void Main()
	{
		StoredProcDemo spd = new StoredProcDemo();

		// run a simple stored procedure
		spd.RunStoredProc();

		// run a stored procedure that takes a parameter
		spd.RunStoredProcParams();
	}

	// run a simple stored procedure
	public void RunStoredProc()
	{
		SqlConnection conn = null;
		SqlDataReader rdr  = null;

		Console.WriteLine("\nTop 10 Most Expensive Products:\n");

		try
		{
			// create and open a connection object
			conn = new 
				SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
			conn.Open();

			// 1. create a command object identifying
			// the stored procedure
			SqlCommand cmd  = new SqlCommand(
				"Ten Most Expensive Products", conn);

			// 2. set the command object so it knows
			// to execute a stored procedure
			cmd.CommandType = CommandType.StoredProcedure;

			// execute the command
			rdr = cmd.ExecuteReader();

			// iterate through results, printing each to console
			while (rdr.Read())
			{
				Console.WriteLine(
					"Product: {0,-25} Price: ${1,6:####.00}",
					rdr["TenMostExpensiveProducts"],
					rdr["UnitPrice"]);
			}
		}
		finally
		{
			if (conn != null)
			{
				conn.Close();
			}
			if (rdr != null)
			{
				rdr.Close();
			}
		}
	}

	// run a stored procedure that takes a parameter
	public void RunStoredProcParams()
	{
		SqlConnection conn = null;
		SqlDataReader rdr  = null;

		// typically obtained from user
		// input, but we take a short cut
		string custId = "FURIB";

		Console.WriteLine("\nCustomer Order History:\n");

		try
		{
			// create and open a connection object
			conn = new 
				SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
			conn.Open();

			// 1. create a command object identifying
			// the stored procedure
			SqlCommand cmd  = new SqlCommand(
				"CustOrderHist", conn);

			// 2. set the command object so it knows
			// to execute a stored procedure
			cmd.CommandType = CommandType.StoredProcedure;

			// 3. add parameter to command, which
			// will be passed to the stored procedure
			cmd.Parameters.Add(
				new SqlParameter("@CustomerID", custId));

			// execute the command
			rdr = cmd.ExecuteReader();

			// iterate through results, printing each to console
			while (rdr.Read())
			{
				Console.WriteLine(
					"Product: {0,-35} Total: {1,2}",
					rdr["ProductName"],
					rdr["Total"]);
			}
		}
		finally
		{
			if (conn != null)
			{
				conn.Close();
			}
			if (rdr != null)
			{
				rdr.Close();
			}
		}	
	}
}