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



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.


 

Comments (0)

Skip to main content