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.
