Handling Stored Procedure with C# and SQL Server

See, stored procedure in DB means storing small database handlers into DB directly. This ensures two things, 1. Make the retrieval/insertion/any db operation faster; 2. security of SQL.

At this stage we do not want to write any SQL inline (means not within C# Code). Now the question is how to write stored procs in SQL Server? How to call the proc from within your code?

1. SQL Server Part:

    i.    Open the respective DB from SS Management Studio
   ii.    Expand the folder named "Programmability"
   iii.   Open "Stored Procedure"
   iv.   Right Click on (iii.)
   v.    Click --> New Stored Procedure
   vi.    Write your Stored Procedure

A sample update query can be written in the following way


USE [DBName]
GO
/****** Object:  StoredProcedure [dbo].[updateCMS]    Script Date: 02/25/2011 10:40:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sourav Maitra
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[updateCMS]

@id int,
@content text

AS
BEGIN
update cms set [content]=@content where [id]=@id

END

............................................................................................................................................................

"updateCMS" is the Procedure Name. cms is the table name, [content] is a col in the table. Add @ to set those fields as params
............................................................................................................................................................

NOW, HOW TO CALL AND PASS PARAMS USING C#
--------------------------------------------------------------------
Add one Method in a class. Put the following code

...........................................................................................................................................................

public int executeStoredProcedureToInsertUpdateORDelete(string storeProcedureName, nameValuePairList objNameValuePairList)
        {
            string connectionStr = obj.getConnectionString();

            SqlConnection conn = new SqlConnection(connectionStr);
            conn.Open();

            SqlCommand cmd = new SqlCommand(storeProcedureName, conn);
            cmd.CommandType = CommandType.StoredProcedure;

            foreach (nameValuePair objNameValuePair in objNameValuePairList)
            {
                cmd.Parameters.Add(createSqlParameter(objNameValuePair.getName, objNameValuePair.getValue));
            }

            int i = cmd.ExecuteNonQuery();
            conn.Close();
            return i;
        }
...........................................................................................................................................................

NOW, you are ready. Just call the method with Params.........

If you need a demonstration of the steps explained, (Sorry. I am helpless) you need to meet me. Happy stored proceduring