<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Sql Server Stored Procedure in .net

Published on
9,622 Points
3,522 Views
1 Endorsement
Last Modified:
Approved

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
            Open sql server2.       Create database “employee”
           Create database “employee”3.       Create table “emp”
            Create table “emp”            Create table “emp”4.       Insert data to the table
           Insert datas to the table           Insert datas 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

Open in new window

          Create the stored procedure2.      One window is opened, select all and delete
3.      Write your own stored procedure (or copy and paste the code provided above)
           Write you own procedure4.      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();
        }
    }
}

Open in new window


How it works:
The SqlConnnection() will create a new connection to SQL Server.
When you use the “ConnectionString” you have to specify the servername, database name username and password of your SQL Server.
Then we have to open the Connection.
By using SqlCommand we can communicate with the SQL Server.
Then we have to set the connection, command type and command text. The connection is SqlConnection object, the command type is stored procedure and the command text is the procedure name.
Then we have to set the parameters, for that we create SqlParameter and set the parameter value.
By using the reader we can execute the procedure. The ExecuteReader() method is used for execute the stored procedure.
The results stored in the SqlDataReader object. We can use those results depends on our requirements

1
Comment
Author:yogesmani
0 Comments

Featured Post

PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Join & Write a Comment

Please check the video also in regards to recovery of deleted emails from office 365 admin center and through the MFCMAPI tool. I have mentioned each and every step with the proper steps that need to be taken care of.
I previously published an Experts Exchange video Micro Tutorial that describes how to scan documents to a PDF file using an excellent, free product called Foxit Reader: How to scan to a PDF file with free software (https://www.experts-exchange.co…
Next Article:

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month