Managing SQL Server “out” Parameter in 3 layer architecture using ASP.NET and C#

Few days back I got a question and started binging. I didn’t find any, finally started writing this tutorial.

What is the query? To understand the query, you need to consider a scenario. Let me describe that first.

 It is a very common requirement to use “out” parameter in SQL Server. The characteristic of this parameter is to return values to calling functions or more specifically, to front end.

Where to use? Consider a scenario, where your table has got an Auto generated ID field; And you want the generated ID to be returned back in user interface.  Also consider that your organization strictly follows 3 layer architecture.  

  

Question: “Strictly following 3 layer architecture?” What this means?

Answer: In one liner – Maintaining SOC (Separation of Concerns). Layers, namely Presentation (also termed as UI layer), Business Logic and Database Access will maintain their roles. Presentation layer will have only UI specific codes, Business Logic will have only the business logic, means Table names, stored procedure names, ID generation logic etc. but no database specific commands. Whereas, Database Access Layer (DAL) will have only database specific codes.  

Question: What will I achieve out of this? 

Response: Think about Software Engineering best practices. Not clear? Read the “Software Engineering Aspects” section

 Software Engineering Aspect

1. Make any project Highly Cohesive and low on coupling

2. Easy Project maintenance, to be more specific, change management should be easy. Understand the change and hit the particular layer.You will increase efficiency.

OK. Clear. Now, how to achieve? Using out parameter and handling that with 3 layer architecture.

Hands On:  Follow the simple walk-through provided below.

Step 1: Create a table. (Use the best utility ever discovered !!! – Mean to say, Copy and Paste). Just power up SQL Server Query Analyzer and fire the following script to generate the table.

CREATE TABLE [dbo].[ItemMaster]

(

    [Id] INT NOT NULL PRIMARY KEY IDENTITY,

    [ItemName] VARCHAR(50) NULL,

    [ItemDescription] VARCHAR(200) NULL

)

Step 2: Create the Stored Procedure. (If you do not understand any of the syntactical sugar Microsoft provides, please feel free to ping me. Will be happy to respond your query)

N.B. [ Stored Procedure(SP) for Insert. If you are a seasoned professional, decorate your SP with CRUD operation and other parameters]

USE [TestDB]

GO

/****** Object:  StoredProcedure [dbo].[SP_ItemMaster]    Script Date: 30-11-2013 01:33:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SP_ItemMaster]

          @ItemID int out,

          @ItemName varchar(50),

          @ItemDesc varchar(200)

AS

Begin

         

          Insert into ItemMaster(ItemName,ItemDescription) values(@ItemName,@ItemDesc)

          set @ItemID=IDENT_CURRENT('ItemMaster')

         

End

Concept Note: The bordered statement used a special SQL Server (T-SQL) function, IDENT_CURRENT. This means: A Function which returns the last identity value generated for a specific table in a particular session

Step 3: Define a Data Base Access Layer (DAL).

Concept Note: Basically, this is a class file, having code blocks and methods to access database. Keeping word limit in mind, just showing part of my DAL here. Validation or exception handling routines are also not into consideration. I repeat, if you want some enhanced stuff, please feel free to get in touch.

Step 3 (a): Define Connection String in a newly added class file named DB.Cs (or you can add the method in your existing DAL class file)

SqlConnection connectionString = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

Step 3 (b): Define Get Connection and Close Connection Methods… (Highly Cohesive)

        public SqlConnection GetConnection()

        {

            connectionString.Open();

            return connectionString;

        }

        public void CloseConnection()

        {

            connectionString.Close();

        } 

Step 3 (c): Define a special class, which will get and set parameters and corresponding values

public class nameValuePair

    {

        string _name;

        object _value;

        public nameValuePair(string iName, object iValue)

        {

            _name = iName;

            _value = iValue;

        }

        public string getName

        {

            get { return _name; }

            set { _name = value; }

        }

        public object getValue

        {

            get { return _value; }

            set { _value = value; }

        }

    }

Step 3 (d): Define another special class, which will form the object with Name and Value Pair and form a list.

Concept Note: No need to write a single line of code. Why? System.Collections.Generic namespace of Dot Net Framework provides a generic class called List<>. You can specify type, according to your requirement. Follow the following snippet. If you are unfamiliar with Collections and Generics in C#, try to explore the area from any standard book or web.

public class nameValuePairList : List<nameValuePair>

    {

    }

Step 3 (e): Put the following method in that

public int InsertUpdateOrDelete(string StoredProcedureName, nameValuePairList NameValuePairObject, string OutParamName = null)

        {

            SqlCommand cmdObject = new SqlCommand(StoredProcedureName, GetConnection());

            cmdObject.CommandType = CommandType.StoredProcedure;

            foreach (nameValuePair objList in NameValuePairObject)

            {

                cmdObject.Parameters.AddWithValue(objList.getName, objList.getValue);

            }

            //let program understand the Output Parameter

            cmdObject.Parameters.Add(OutParamName, SqlDbType.Int, 10);

            cmdObject.Parameters[OutParamName].Direction = ParameterDirection.Output;

            int status = 0;

            try

            {

                status = cmdObject.ExecuteNonQuery();

                int ID = int.Parse(cmdObject.Parameters[OutParamName].Value.ToString());

                CloseConnection();

                return ID;

            }

            catch (Exception exp)

            {

                CloseConnection();

                return status;

            }

            finally

            {

                CloseConnection();

            }

        }

You are ready with your Database Access Layer(DAL). Now, it’s time to devise your Business Logic Layer (BLL)

Step 4: Add a class file in your project, name it BLLItemMaster. And put the following code.

      
        DB DalObject = new DB();

        public int InsertItem(string itemName,string itemDescription)

        {

         string InsertSPName ="SP_ItemMaster";

      nameValuePairList NameValuePairObject = new nameValuePairList();

      NameValuePairObject.Add(new nameValuePair("@ItemName",itemName));

      NameValuePairObject.Add(new nameValuePair("@ItemDesc", itemDescription));

     int ID = DalObject.InsertUpdateOrDelete(InsertSPName, NameValuePairObject,"@ItemID");

        return ID;

        }

Step 5: Design User Interface. (Please design properly, otherwise your client will not let you sleep properly)

I have one, Without Color/good aesthetic. Your bad luck, refer the following only.

Step 6: Finally, code UI layer. Paste the following code on Save button’s click event.

    protected void btnSave_Click(object sender, EventArgs e)

        {

            BLLItemMaster myObject = new BLLItemMaster();

            int ID =myObject.InsertItem(txtName.Text, txtDescription.Text);

            if (ID != 0)

            {

                lblMsg.Text = "ID generated for " + txtName.Text + " is:" + ID.ToString();

            }

            else

            {

                lblMsg.Text = "OOPS !!!! Something went wrong....";

            }

        }

Step 7: Pray your god and Hit the Debug button of your VS IDE…

 You will get the output as depicted above. When you will add some data and click on Save button, you will get an output like the following.

Showing Generated ID. Coming from Data base Stored Procedure

That is it. Tried to make this as comprehensive as possible. If you have any doubt, please feel free to contact me via any of the coordinates.

Acknowledge: I want to deeply acknowledge my friend and colleague Mr Anshuman Chowdhury, for raising the requirement.


 

Visit: http://souravmaitra.com
Facebook Connect: @tosmaitra
Twitter: @souravmaitra

FaceBook Page: https://www.facebook.com/dexttech

Click to know more About Me