Sql Server Stored Procedure in .net

Stored Procedure Tutorial

This article covers the basic of writing a stored procedure and how to call that from .net.  A stored procedure is written using Transact-SQL (T-SQL). T-SQL is a subset of ANSI SQL-92 that has extensions to the Standard. T-SQL includes variables, conditional logic, loops and flow control.  A stored procedure allows you to put code or business logic on the database server.

A stored procedure allows for more modular programming. You can create reusable, discrete pieces of functionality using stored procedures. Stored procedures have the parsing and execution plan built at the time they are created so they execute faster than dynamic SQL. If you have a stored procedure that manipulates large amounts of data, that data will remain on the server and not be transported across the network. You can also use stored procedures for security. You can grant users execute permissions on stored procedures without granting those permissions on objects manipulated by those stored procedures.


TABLE CREATION:

1.      Open SQL Server
            2.       Create database “employee”
            3.       Create table “emp”
                        4.       Insert data to the table
                               

PROCEDURE:

1.       Create the stored procedure shown below:

create procedure getempproc @city varchar(20)
                      as
                      begin
                      select * from emp where city=@city
                      end
                      

Select all Open in new window

          2.      One window is opened, select all and delete
3.      Write your own stored procedure (or copy and paste the code provided above)
            4.      Select all and press f5 the stored procedure will be created.

 
PROGRAM:

1.      Open Visual Studio
2.      Create a new console application (note: C# project / code used as an example)
3.      Type the following code into Program.cs, compile and run

using System;
                      using System.Collections.Generic;
                      using System.Text;
                      using System.Data.SqlClient;
                      using System.Data;
                      
                      
                      namespace ConsoleApplication1
                      {
                          class Program
                          {
                              static void Main(string[] args)
                              {
                                  SqlConnection cn = new SqlConnection();
                                  cn.ConnectionString = "server=MCASYS13\\SQLEXPRESS;database=employee;user id=sa;pwd=admin123"; 
                                  cn.Open();
                                  SqlCommand cmd = new SqlCommand();
                                  cmd.Connection = cn;
                                  cmd.CommandType = CommandType.StoredProcedure;
                                  cmd.CommandText = "getempproc";
                                  SqlParameter p = new SqlParameter("@city", SqlDbType.VarChar, 50);
                                  p.Direction = ParameterDirection.Input;
                                  p.Value = "Chennai";
                                  cmd.Parameters.Add(p);
                                  SqlDataReader dr = cmd.ExecuteReader();
                                  while (dr.Read())
                                  {
                                      Console.WriteLine(dr[0].ToString());
                                      Console.WriteLine(dr[1].ToString());
                      
                                  }
                                  Console.Read();
                              }
                          }
                      }
                      

Select all Open in new window

How it works: