Execute Stored Procedures using ADO.NET

by Jagadish Pulakhandam on 9/6/2011 12:49:53 PM
Rated 0 from 0 votes
Brief: Demonstrates on executing stored procedures using ADO.NET
Posted to: Accessing Stored Procedures & Functions using ADO.NET
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Demonstrates on the following:
  • How to execute a stored procedure which does not return any value
  • How to execute a stored procedure which returns a single value (and display the value returned by stored procedure)
  • How to execute a stored procedure which returns a result set (multiple rows).
  • How to execute a stored procedure with parameters (passing parameters to stored procedure)
  • How to execute a stored procedure and retrieve values from OUTPUT parameters
  • How to execute a stored procedure and fetch RETURN value

Screen shot:



Sample stored Procedure code:

01.CREATE PROCEDURE [dbo].[p_Emp_Insert]
02.(
03.    @empno int,
04.    @ename varchar(50),
05.    @sal float,
06.    @deptno int
07.)
08.AS
09.    SET NOCOUNT OFF;
10.INSERT INTO [emp] ([empno], [ename], [sal], [deptno]) VALUES (@empno, @ename, @sal, @deptno);

1.CREATE PROCEDURE [dbo].[p_Emp_Delete]
2.(
3.    @Original_empno int
4.)
5.AS
6.    SET NOCOUNT OFF;
7.DELETE FROM [emp] WHERE (([empno] = @Original_empno))

01.CREATE PROCEDURE [dbo].[p_Emp_IsExists]
02.(
03.    @Empno  int
04.)
05.AS
06.BEGIN
07.    IF exists (SELECT 1 FROM dbo.emp WHERE empno=@Empno)
08.    BEGIN
09.        return 1
10.    END
11.    ELSE
12.    BEGIN
13.        return 0
14.    END
15.END

1.CREATE PROCEDURE [dbo].[p_Emp_IncreaseSalaries]
2.AS
3.BEGIN
4.    UPDATE emp SET sal = sal + 500
5.END

01.CREATE PROCEDURE [dbo].[p_Emp_GetEname]
02.(
03.    @Empno  int,
04.    @rEname varchar(50) OUTPUT
05.)
06.AS
07.BEGIN
08.    SELECT @rEname=Ename FROM dbo.emp
09.    WHERE empno = @empno
10.END

1.CREATE PROCEDURE [dbo].[p_Emp_GetTotalSal]
2.AS
3.BEGIN
4.    SELECT TotalSal = SUM(sal) FROM dbo.emp
5.END

1.CREATE PROCEDURE [dbo].[p_Emp_GetList]
2.AS
3.    SET NOCOUNT ON;
4.SELECT     empno, ename, sal, deptno
5.FROM         emp

1.CREATE PROCEDURE [dbo].[p_Emp_GetListOfDept]
2.(
3.    @Dptno int
4.)
5.AS
6.BEGIN
7.    SELECT * FROM emp WHERE deptno = @Dptno
8.END

Source Code (.NET):

001.using System;
002.using System.Collections.Generic;
003.using System.ComponentModel;
004.using System.Data;
005.using System.Drawing;
006.using System.Linq;
007.using System.Text;
008.using System.Windows.Forms;
009.using System.Data.SqlClient;
010. 
011.namespace _01_ExecuteStoredProce
012.{
013.    public partial class Form1 : Form
014.    {
015.        string CONNSTR = "Data Source=.\\sql2k8; Initial Catalog=Sample; User Id=sa; Password=eXpress2008";
016. 
017.        public Form1()
018.        {
019.            InitializeComponent();
020.        }
021. 
022.        private void btnWithoutReturns_Click(object sender, EventArgs e)
023.        {
024.            using (SqlCommand cmd = new SqlCommand("p_Emp_IncreaseSalaries", new SqlConnection(CONNSTR)))
025.            {
026.                cmd.Connection.Open();
027.                cmd.ExecuteNonQuery();
028.            }
029.            MessageBox.Show("Successfully completed");
030.        }
031. 
032.        private void btnReturnSingleVal_Click(object sender, EventArgs e)
033.        {
034.            using (SqlCommand cmd = new SqlCommand("p_Emp_GetTotalSal", new SqlConnection(CONNSTR)))
035.            {
036.                cmd.Connection.Open();
037.                double TotalSal = (double) cmd.ExecuteScalar();
038.                MessageBox.Show("Total Salary: " + TotalSal);
039.            }
040.        }
041. 
042.        private void btnReturnResultSet_Click(object sender, EventArgs e)
043.        {
044.            using (SqlDataAdapter da = new SqlDataAdapter("p_Emp_GetList", CONNSTR))
045.            {
046.                DataTable dt = new DataTable();
047.                da.Fill(dt);
048.                this.DataGridView1.DataSource = dt;
049.            }
050.        }
051. 
052.        private void btnWithParams_Click(object sender, EventArgs e)
053.        {
054.            using (SqlCommand cmd = new SqlCommand("p_Emp_GetListOfDept", new SqlConnection(CONNSTR)))
055.            {
056.                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
057.                {
058.                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
059.                    da.SelectCommand.Parameters.Add("@Dptno", SqlDbType.Int).Value = 20;
060.                    DataTable dt = new DataTable();
061.                    da.Fill(dt);
062.                    this.DataGridView1.DataSource = dt;
063.                }
064.            }
065.        }
066. 
067.        private void btnGetOutputParam_Click(object sender, EventArgs e)
068.        {
069.            using (SqlCommand cmd = new SqlCommand("[p_Emp_GetEname]", new SqlConnection(CONNSTR)))
070.            {
071.                cmd.CommandType = CommandType.StoredProcedure;
072. 
073.                SqlParameter pEmpno = cmd.Parameters.Add("@Empno", SqlDbType.Int);
074.                pEmpno.Value = 1004;
075. 
076.                SqlParameter pEname = cmd.Parameters.Add("@rEname", SqlDbType.VarChar, 50);
077.                pEname.Direction = ParameterDirection.Output;
078. 
079.                cmd.Connection.Open();
080.                cmd.ExecuteNonQuery();
081.                MessageBox.Show(pEname.Value.ToString());
082.            }
083.        }
084. 
085.        private void btnReturnValue_Click(object sender, EventArgs e)
086.        {
087.            using (SqlCommand cmd = new SqlCommand("[p_Emp_IsExists]", new SqlConnection(CONNSTR)))
088.            {
089.                cmd.CommandType = CommandType.StoredProcedure;
090. 
091.                SqlParameter pEmpno = cmd.Parameters.Add("@Empno", SqlDbType.Int);
092.                pEmpno.Value = 1004;
093. 
094.                SqlParameter pRetValue = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
095.                pRetValue.Direction = ParameterDirection.ReturnValue;
096. 
097.                cmd.Connection.Open();
098.                cmd.ExecuteNonQuery();
099.                MessageBox.Show(pRetValue.Value.ToString());
100.            }
101.        }
102.    }
103.}

Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout

Attachments / Source Code
You need to Login or Join for FREE to download the following