Share via

Calling SQL Server Stored Procedures with ADO.NET in 5 minutes

  • Article

A stored procedure is an already written SQL statement that is saved in the database. It can take parameters; return objects you specified, just like what happens in any other programming languages you are familiar with.

Why stored procedures instead of random SQL? For me:

1. Modular Programming- Stored procedures allow developers to encapsulate business functionality and provide callers with a simple interface. Once interfaces are settled down, caller and callee coding work can be assigned to different team/persons.

2. Security Enhancement- Users can be granted permission to execute a stored procedure. Use parameterized queries—not string concatenation—to build queries.

3. Reduce network traffic – Benefits can be easily noticed if you have frequently called T-SQL code of hundreds of lines.

4. Performance – Stored procedures are registered at servers, and as a result DBAs/servers get more change to optimize them.

 

Coding time now. Firstly, you need run below SQL script in SQL2005.

 

SQL Script

-- Create a test DB

USE [master]

GO

CREATE DATABASE SpTestDB

GO

USE SpTestDB

GO

-- Create a test Table

CREATE TABLE dbo.TestTable (

id [nvarchar](50) NULL,

SomeValue [int] NULL

)

GO

-- Create read SP

CREATE PROCEDURE dbo.ReadData

@id nvarchar (50)

AS

BEGIN

SELECT * FROM dbo.TestTable

WHERE id = @id;

END

GO

-- Create write SP

CREATE PROCEDURE dbo.WriteData

@id nvarchar (50),

@SomeValue int

AS

BEGIN

INSERT INTO dbo.TestTable (id, SomeValue)

VALUES (@id, @SomeValue);

END

GO

Next, you can call in ADO.NET.

 

ADO.NET C# code

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

namespace ConsoleApplication4

{

    class Program

    {

        // NOTICE: You MUST replace 'localhost\baligoal' with your own DB instance name

        const string ConnString = @"Data Source=localhost\baligoal;Initial Catalog=SpTestDB;Integrated Security=True";

        /// <summary>

        /// Write a record to DB with stored procedure "WriteData",

        /// and then read it out with stored procedure "ReadData"

        /// </summary>

        /// <param name="args"></param>

        static void Main(string[] args)

        {

            const string TestID = "firstid";

            const int TestValue = 500;

            // Firstly, write a record with store procedure

           using (SqlConnection conn = new SqlConnection(ConnString))

            {

                // Specify 'WriteData' procedure in the params

                using (SqlCommand cmd = new SqlCommand("WriteData", conn))

                {

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    // The param names are exactly the same with SP WriteData's

                    cmd.Parameters.AddWithValue("@id", TestID);

                    cmd.Parameters.AddWithValue("@SomeValue", TestValue);

                    cmd.Connection.Open();

                    cmd.ExecuteNonQuery();

                }

            }

            // If you set BP here, and check you DB table, you should find 'firstid, 500' there

            Console.WriteLine("Write: done.");

            // Next, read it out with store procedure

            using (SqlConnection conn = new SqlConnection(ConnString)) {

                // Specify 'ReadData' procedure in the params

                using (SqlCommand cmd = new SqlCommand("ReadData", conn)) {

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                   

                    // The param names is exactly the same with SP ReadData's

                    cmd.Parameters.AddWithValue("@id", TestID);

                    cmd.Connection.Open();

                    using (SqlDataReader reader = cmd.ExecuteReader())

                    {

                        if (reader.Read()) {

                            Console.WriteLine("read: id - " + Convert.ToString(reader[0]));

                            Console.WriteLine("read: SomeValue - " + Convert.ToInt32(reader[1]));

                        }

                    }

                }

            }

            // End for bp

  Console.WriteLine("exit");

        }

    }

}

That is it.